这个也是做的项目，保存做个备份先。
UCENTER里的 兑换
ucenter_reducelfb //调用数据 call ucenter_reducelfb(参数1,参数2，....)
DELIMITER $
USE `root`$
DROP PROCEDURE IF EXISTS `ucenter_reducelfb`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ucenter_reducelfb`(in_appid INT,in_tel VARCHAR(11),in_num INT,in_oreason VARCHAR(1000))
/*by 夜色紫宸風 2011-10-25
*/
BEGIN
DECLARE t_tel VARCHAR(11) DEFAULT '';
DECLARE t_uid INT DEFAULT 0;
DECLARE t_uid2 INT DEFAULT 0;
DECLARE t_extcredits2 INT DEFAULT 0;
/*来福币设置*/
DECLARE t_tablename VARCHAR(100) DEFAULT 0;/*来福币表名称*/
DECLARE t_month VARCHAR(10) DEFAULT '';/*本月初*/
DECLARE t_month_next VARCHAR(10) DEFAULT '';/*下月初*/
DECLARE t_day VARCHAR(10) DEFAULT '';/*今天*/
DECLARE t_day_next VARCHAR(10) DEFAULT '';/*明天*/
DECLARE t_total INT DEFAULT 0;/*累计*/
/*兑换来福币设置*/
DECLARE t_appid INT DEFAULT 0;
DECLARE t_lfbeachmax INT DEFAULT 0;/*单次兑换数值大于配置的值*/
DECLARE t_lfblimit INT DEFAULT 0;/*剩余来福币*/
DECLARE t_lfbpersondaymax INT DEFAULT 0;/*用户当天累计大于配置的值*/
DECLARE t_lfbpersonmonthmax INT DEFAULT 0;/*用户当月累计大于配置的值*/
DECLARE t_lfbappdaymax INT DEFAULT 0;/*应用当天累计大于配置的值*/
DECLARE t_lfbappmonthmax INT DEFAULT 0;/*应用当月累计大于配置的值*/
DECLARE t_operate VARCHAR(10);
DECLARE t_check INT DEFAULT 0;
DECLARE out_return INT DEFAULT 0;
DECLARE tmp INT DEFAULT 0;
DECLARE t_ym INT DEFAULT 0;
SET t_tablename=CONCAT('sz_app_credit_log_',DATE_FORMAT(NOW() ,'%Y%m'));
SET @sqlstr = CONCAT('CREATE TABLE IF NOT EXISTS `',t_tablename,'` (',
'`lid` int(11) NOT NULL AUTO_INCREMENT,',
'`uid` int(11) NOT NULL DEFAULT 0,',
'`operate` enum(''query'',''add'',''reduce'') NOT NULL,',
'`appid` int(11) NOT NULL DEFAULT 0,',
'`ovalue` int(11) NOT NULL DEFAULT 0,',
'`ostatus` int(11) NOT NULL DEFAULT 0,',
'`oreason` varchar(255) NOT NULL,',
'`dateline` int(11) NOT NULL DEFAULT 0,',
'`credittype` tinyint(4) NOT NULL,',
'PRIMARY KEY (`lid`)',
') ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;');
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT uid,tel INTO t_uid,t_tel FROM sz_ucenter_members WHERE tel=in_tel;
IF t_uid>0 THEN
/*检测主站是否存在该用户*/
SELECT uid,extcredits2 INTO t_uid2,t_extcredits2 FROM sz_common_member_count WHERE uid=t_uid;
IF t_uid2>0 THEN
/*兑换来福币检测开始-----------------------------------------------*/
SET t_operate='reduce';
SELECT appid,lfbeachmax,lfblimit,lfbpersondaymax,lfbpersonmonthmax,lfbappdaymax,lfbappmonthmax INTO t_appid,t_lfbeachmax,t_lfblimit,t_lfbpersondaymax,t_lfbpersonmonthmax,t_lfbappdaymax,t_lfbappmonthmax FROM sz_app_credit_config WHERE appid = in_appid;
IF t_appid>0 THEN
/*单次兑换数值大于配置的值*/
IF in_num>t_lfbeachmax THEN
SET t_check=-12;
ELSE
IF t_operate='reduce' THEN
/*用户来福币不足兑换*/
IF (t_extcredits2-in_num)<t_lfblimit THEN
SET t_check=-12;
ELSE
IF (t_extcredits2-in_num)<0 THEN
SET t_check=-12;
END IF;
END IF;
END IF;
SET t_day=UNIX_TIMESTAMP(DATE_FORMAT(NOW() ,'%Y-%m-%d'));
SET t_day_next=UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-%d'),INTERVAL 1 DAY));
SET t_month=UNIX_TIMESTAMP(DATE_FORMAT(NOW() ,'%Y-%m-01'));
SET t_month_next=UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'),INTERVAL 1 MONTH));
LABLEpersonday : BEGIN
IF t_check<0 THEN
LEAVE LABLEpersonday;
END IF;
/*比较当天累计*/
SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid=',in_appid,' and credittype =1 and uid=',t_uid,' and operate=''',t_operate,''' and dateline>=',t_day,' AND dateline<',t_day_next);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET t_total=IFNULL(@total,0);
/*用户当天累计大于配置的值*/
IF (t_total+in_num)>t_lfbpersondaymax THEN
SET t_check=-13;
END IF;
END LABLEpersonday;
/*比较当月累计*/
LABLEpersonmonth : BEGIN
IF t_check<0 THEN
LEAVE LABLEpersonmonth;
END IF;
SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid =',in_appid,' and credittype =1 and uid=',t_uid,' and operate =''',t_operate,''' and dateline>=',t_month,' and dateline <',t_month_next);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET t_total=IFNULL(@total,0);
/*用户当月累计大于配置的值*/
IF (t_total+in_num)>t_lfbpersonmonthmax THEN
SET t_check=-14;
END IF;
END LABLEpersonmonth;
/*比较应用当天累计*/
LABLEappday : BEGIN
IF t_check<0 THEN
LEAVE LABLEappday;
END IF;
SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid =',in_appid,' and credittype = 1 and operate =''',t_operate,''' and dateline>=',t_day,' and dateline <',t_day_next);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET t_total=IFNULL(@total,0);
/*应用当天累计大于配置的值*/
IF (t_total+in_num)>t_lfbappdaymax THEN
SET t_check=-15;
END IF;
END LABLEappday;
/*比较当月累计*/
LABLEappmonth : BEGIN
IF t_check<0 THEN
LEAVE LABLEappmonth;
END IF;
SET @sqlstr = CONCAT('select sum(ovalue) INTO @total FROM ',t_tablename,' where appid=',in_appid,' and credittype =1 and operate =''',t_operate,''' and dateline>=',t_month,' and dateline<',t_month_next);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET t_total=IFNULL(@total,0);
/*应用当月累计大于配置的值*/
IF (t_total+in_num)>t_lfbappmonthmax THEN
SET t_check=-16;
ELSE
/*符合兑换条件*/
SET t_check=1;
END IF;
END LABLEappmonth;
END IF;
ELSE
/*该应用没有配置积分兑换限制或者没有该应用*/
SET t_check=-11;
END IF;
/*兑换来福币检测结束-----------------------------------------------*/
IF t_check=1 THEN
/*判断用户积分是否足够*/
IF t_extcredits2<in_num THEN
/*来福币操作日志*/
SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,4);
SET t_check=-3;
SET out_return= -3;
ELSE
/*更新用户积分*/
UPDATE sz_common_member_count SET extcredits2 =extcredits2-in_num WHERE uid=t_uid;
/*查询用户积分*/
SELECT extcredits2 INTO t_extcredits2 FROM sz_common_member_count WHERE uid=t_uid;
/*写更新日志*/
SET @sqlstr=CONCAT('INSERT INTO ',t_tablename,' (`uid`,`operate`,`credittype`,`appid`,`ovalue`,`oreason`,`dateline`,`ostatus`) values (',t_uid,',''reduce'',1,',in_appid,',',in_num,',''',in_oreason,''',',UNIX_TIMESTAMP(NOW()),',',t_extcredits2,')');
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/*来福币操作日志*/
SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,0);
SET out_return= t_extcredits2;
END IF;
ELSE
/*来福币操作日志*/
SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,t_check);
SET out_return= t_check;
END IF;
/*苏州生活网不存在该用户*/
ELSE
/*来福币日志*/
SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,2);
SET out_return=-1;
END IF;
/*ucenter 中不存在该用户*/
ELSE
/*来福币日志*/
SET tmp= common_lfbchange_log(in_appid,in_tel,in_num,2,in_oreason,1);
SET out_return= -1;
END IF;
SELECT out_return;
END$
DELIMITER ;
common_lfbchange_log
DELIMITER $
USE `root`$
DROP FUNCTION IF EXISTS `common_lfbchange_log`$
CREATE DEFINER=`root`@`localhost` FUNCTION `common_lfbchange_log`(in_appid INT,in_tel VARCHAR(11),in_num INT,in_type INT,in_oreason VARCHAR(500),in_result INT) RETURNS INT(11)
NO SQL
COMMENT 'type 1:添加 2:减少\r\nresult 0:成功 1:ucenter中没有该用户 2:苏州生活网中不存在该用户'
BEGIN
/*by 夜色紫宸風 2011-10-25
*/
INSERT INTO sz_common_lfbchange_log (`appid`,`tel`,`num`,`type`,`oreason`,`result`,`dateline`) VALUES (in_appid,in_tel,in_num,in_type,in_oreason,in_result,UNIX_TIMESTAMP(NOW()));
RETURN 0;
END$
DELIMITER ;
