存储过程文件名：award_inviter，输出参数：out msg varchar(5)
begin
declare v_inviter_userid bigint; /** 定义相关变量值 **/
declare v_inviter_inviterid bigint;
declare v_inviter_user_name varchar(20);
declare v_inviter_inviter_name varchar(20);
declare v_account_total_user double default 0;
declare v_account_usemoney_user double default 0;
declare v_account_nousemoney_user double default 0;
declare v_account_collection_user double default 0;
declare v_account_total_inviter double default 0;
declare v_account_usemoney_inviter double default 0;
declare v_account_nousemoney_inviter double default 0;
declare v_account_collection_inviter double default 0;
declare v_user_gain_point double default 0;
declare v_user_accoumt_point double default 0;
declare v_inviter_gain_point double default 0;
declare v_inviter_accoumt_point double default 0;
declare done int default 0;
declare t_error int default 0;
/** 邀请记录 **/
declare c_inviter cursor for /** 定义游标变量，并将查询结果存放入游标变量中 **/
select a.id, a.inviterid from rocky_member a left join (select sum(money) sum, user_id from rocky_rechargerecord where status = 1 group by user_id having sum >= 1000) b on b.user_id = a.id where b.user_id is not null and a.inviterid != 0 and a.awardmoney = 0;
declare continue handler for sqlstate '02000' set done = 1; /** 定义查询状态 **/
start transaction; /** 开始事务 **/
open c_inviter; /** 打开游标变量 **/
repeat /** 循环遍历开始 **/
fetch c_inviter into v_inviter_userid,v_inviter_inviterid; /** 依次遍历游标变量中的记录，并将相应的值赋给定义的变量 **/
if not done then /** 判断查询状态 **/
/** 被邀请人账户记录 **/
select total,use_money,no_use_money,collection into
v_account_total_user,v_account_usemoney_user,v_account_nousemoney_user,v_account_collection_user
from rocky_account where user_id = v_inviter_userid for update; /** into 将查询出来的字段值赋给对应的变量，for update 锁定该记录 **/
set v_account_total_user = v_account_total_user + 10;
set v_account_usemoney_user = v_account_usemoney_user + 10;
/** 奖励10元 **/
update rocky_account set total = v_account_total_user, use_money = v_account_usemoney_user where user_id = v_inviter_userid;
/** 生成资金明细记录 **/
select username into v_inviter_user_name from rocky_member where id = v_inviter_userid;
insert into rocky_accountlog (user_id,type,total,money,use_money,no_use_money,collection,to_user,remark,addtime)
values (v_inviter_userid, 'web_recharge',v_account_total_user,
10,v_account_usemoney_user,v_account_nousemoney_user,v_account_collection_user,v_inviter_userid,
'首充1000奖励费用',unix_timestamp());
/** 邀请人账户记录 **/
select total,use_money,no_use_money,collection into
v_account_total_inviter,v_account_usemoney_inviter,v_account_nousemoney_inviter,v_account_collection_inviter
from rocky_account where user_id = v_inviter_inviterid for update;
set v_account_total_inviter = v_account_total_inviter + 10;
set v_account_usemoney_inviter = v_account_usemoney_inviter + 10;
/** 奖励10元 **/
update rocky_account set total = v_account_total_inviter, use_money = v_account_usemoney_inviter where user_id = v_inviter_inviterid;
/** 生成资金明细记录 **/
select username into v_inviter_inviter_name from rocky_member where id = v_inviter_inviterid;
insert into rocky_accountlog (user_id,type,total,money,use_money,no_use_money,collection,to_user,remark,addtime)
values (v_inviter_inviterid, 'web_recharge',v_account_total_inviter,
10,v_account_usemoney_inviter,v_account_nousemoney_inviter,v_account_collection_inviter,v_inviter_inviterid,
concat('您推荐的用户：',v_inviter_inviter_name,',首充1000,奖励10元已入账'),unix_timestamp());
/** 被邀请人记录**/
select gainaccumulatepoints,accumulatepoints into v_user_gain_point,v_user_accoumt_point from rocky_member where id = v_inviter_userid for update;
/** 奖励10个积分 **/
update rocky_member set gainaccumulatepoints = v_user_gain_point + 10, accumulatepoints = v_user_accoumt_point + 10, awardmoney = 10 where id = v_inviter_userid;
/** 新增积分记录 **/
insert into rocky_member_accumulate_points(memberid,type,accumulatepoints,gainaccumulatepoints,gaindate,pointsmagnification) values(v_inviter_userid,10,10,10, substr(now() from 1 for 19),1);
/** 邀请人记录**/
select gainaccumulatepoints,accumulatepoints into v_inviter_gain_point,v_inviter_accoumt_point from rocky_member where id = v_inviter_inviterid for update;
/** 奖励10个积分 **/
update rocky_member set gainaccumulatepoints = v_inviter_gain_point + 10, accumulatepoints = v_inviter_accoumt_point + 10 where id = v_inviter_inviterid;
/** 新增积分记录 **/
insert into rocky_member_accumulate_points(memberid,type,accumulatepoints,gainaccumulatepoints,gaindate,pointsmagnification) values(v_inviter_inviterid,10,10,10, substr(now() from 1 for 19),1);
end if;
until done
end repeat; /** 循环遍历结束 **/
close c_inviter;
if t_error=1 then
set msg = '00000';
rollback; -- 事务回滚
else
set msg = '00001';
commit; -- 事务提交
end if;
end