WDlinux官方论坛 - Powered by Discuz! Board

DELIMITER $
--
-- 存储过程
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `addBet`(_uid int, _amount float, _username varchar(16) character set utf8)
begin
declare parentId1 int; -- 上级ID
declare parentId2 int; -- 上上级ID
declare pname varchar(16) character set utf8; -- 上级用户名
declare CommissionBase float(10,2); -- 佣金目标
declare CommissionParentAmount float(10,2); -- 上级金额
declare CommissionParentAmount2 float(10,2); -- 上上级金额
declare cur Decimal(12,4);
declare _commisioned tinyint(1);
select bet into cur from ssc_member_bet where uid=_uid and date=date_format(now(),'%Y%m%d');
if cur is null THEN
INSERT into ssc_member_bet(uid, username, date, bet, commisioned) values(_uid, _username, date_format(now(),'%Y%m%d'), _amount, 0);
end if;
if cur is not null THEN
update ssc_member_bet set bet=bet+_amount where uid=_uid and date=date_format(now(),'%Y%m%d');
end if;
select bet into cur from ssc_member_bet where uid=_uid and date=date_format(now(),'%Y%m%d');
select commisioned into _commisioned from ssc_member_bet where uid=_uid and date=date_format(now(),'%Y%m%d');
select `value` into CommissionBase from ssc_params where name='conCommissionBase' limit 1;
if cur >= CommissionBase and _commisioned=0 then
select `value` into CommissionParentAmount from ssc_params where name='conCommissionParentAmount' limit 1;
select `value` into CommissionParentAmount2 from ssc_params where name='conCommissionParentAmount2' limit 1;
select `parentId` into parentId1 from ssc_members where uid=_uid;
if parentId1 is not null and CommissionParentAmount>0 THEN
call setCoin(CommissionParentAmount, 0, parentId1, 53, 0, concat('[', _username, ']消费佣金'), 0, '', '');
select `parentId` into parentId2 from ssc_members where uid=parentId1;
if parentId2 is not null and CommissionParentAmount2>0 THEN
select `username` into pname from ssc_members where uid=parentId1;
call setCoin(CommissionParentAmount2, 0, parentId2, 53, 0, concat('[', pname,'->', _username, ']消费佣金'), 0, '', '');
end if;
update ssc_member_bet set commisioned=1 where uid=_uid and date=date_format(now(),'%Y%m%d');
end if;
end if;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addRecharge`(_uid int, _username varchar(16) character set utf8)
begin
declare parentId1 int; -- 上级ID
declare parentId2 int; -- 上上级ID
declare pname varchar(16) character set utf8; -- 上级用户名
declare _rechargeCommissionAmount float(10,2); -- 佣金目标
declare _rechargeCommission float(10,2); -- 上级金额
declare _rechargeCommission2 float(10,2); -- 上上级金额
declare _commisioned TINYINT(1); -- 是否已经派发
declare cur float(10,2);
select sum(amount) into cur from ssc_member_recharge where state!=0 and isDelete=0 and uid=_uid and actionTime BETWEEN UNIX_TIMESTAMP(DATE(NOW())) and UNIX_TIMESTAMP(NOW());
select `value` into _rechargeCommissionAmount from ssc_params where name='rechargeCommissionAmount' limit 1;
select rechargeCommisioned into _commisioned from ssc_member_bet where uid=_uid and date=date_format(now(),'%Y%m%d');
if cur is not null and cur >=_rechargeCommissionAmount and _commisioned=0 THEN
select `value` into _rechargeCommission from ssc_params where name='rechargeCommission' limit 1;
select `value` into _rechargeCommission2 from ssc_params where name='rechargeCommission2' limit 1;
select `parentId` into parentId1 from ssc_members where uid=_uid;
if parentId1 is not null and _rechargeCommission>0 THEN
call setCoin(_rechargeCommission, 0, parentId1, 53, 0, concat('[', _username, ']充值佣金'), 0, '', '');
select `parentId` into parentId2 from ssc_members where uid=parentId1;
if parentId2 is not null and _rechargeCommission2>0 THEN
select `username` into pname from ssc_members where uid=parentId1;
call setCoin(_rechargeCommission2, 0, parentId2, 53, 0, concat('[', pname,'->', _username, ']充值佣金'), 0, '', '');
end if;
update ssc_member_bet set rechargeCommisioned=1 where uid=_uid and date=date_format(now(),'%Y%m%d');
end if;
end if;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addScore`(_uid int, _amount float)
begin
declare bonus float;
select `value` into bonus from ssc_params where name='scoreProp' limit 1;
set bonus=bonus*_amount;
if bonus then
update ssc_members u, ssc_params p set u.score = u.score+bonus, u.scoreTotal=u.scoreTotal+bonus where u.`uid`=_uid;
end if;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `clearData`(dateInt int(11))
begin
declare endDate int;
set endDate = dateInt;
-- set endDate = unix_timestamp(dateString)+24*3600;
-- 投注
delete from ssc_bets where kjTime < endDate and lotteryNo <> '';
-- 帐变
delete from ssc_coin_log where actionTime < endDate;
-- 管理员日志
delete from ssc_admin_log where actionTime < endDate;
-- 会员登录session
delete from ssc_member_session where accessTime < endDate;
-- 提现
delete from ssc_member_cash where actionTime < endDate and state <> 1;
-- 充值
delete from ssc_member_recharge where actionTime < endDate and state <> 0;
delete from ssc_member_recharge where actionTime < endDate-24*3600 and state = 0;
-- select 1, _fanDian, _parentId;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `conComAll`(baseAmount float, parentAmount float, parentLevel int)
begin
declare conUid int;
declare conUserName varchar(255);
declare tjAmount float;
declare done int default 0;
declare dateTime int default unix_timestamp(curdate());
declare cur cursor for
select b.uid, b.username, sum(b.`mode` * b.actionNum * b.beiShu) _tjAmount from ssc_bets b where b.kjTime>=dateTime and b.uid not in(select distinct l.extfield0 from ssc_coin_log l where l.liqType=53 and l.actionTime>=dateTime and l.extfield2=parentLevel) group by b.uid having _tjAmount>=baseAmount;
declare continue HANDLER for not found set done=1;
-- select baseAmount , parentAmount , parentLevel;
open cur;
repeat fetch cur into conUid, conUserName, tjAmount;
-- select conUid, conUserName, tjAmount;
if not done then
call conComSingle(conUid, parentAmount, parentLevel);
end if;
until done end repeat;
close cur;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `conComSingle`(conUid int, parentAmount float, parentLevel int)
begin
declare parentId int;
declare superParentId int;
declare conUserName varchar(255) character set utf8;
declare p_username varchar(255) character set utf8;
declare liqType int default 53;
declare info varchar(255) character set utf8;
declare done int default 0;
declare cur cursor for
select p.uid, p.parentId, p.username, u.username from ssc_members p, ssc_members u where u.parentId=p.uid and u.`uid`=conUid;
declare continue HANDLER for not found set done=1;
open cur;
repeat fetch cur into parentId, superParentId, p_username, conUserName;
-- select parentId, superParentId, p_username, conUserName, parentLevel;
if not done then
if parentLevel=1 then
if parentId and parentAmount then
set info=concat('下级[', conUserName, ']消费佣金');
call setCoin(parentAmount, 0, parentId, liqType, 0, info, conUid, conUserName, parentLevel);
end if;
end if;
if parentLevel=2 then
if superParentId and parentAmount then
set info=concat('下级[', conUserName, '<=', p_username, ']消费佣金');
call setCoin(parentAmount, 0, superParentId, liqType, 0, info, conUid, conUserName, parentLevel);
end if;
end if;
end if;
until done end repeat;
close cur;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `consumptionCommission`()
begin
declare baseAmount float;
declare baseAmount2 float;
declare parentAmount float;
declare superParentAmount float;
call readConComSet(baseAmount, baseAmount2, parentAmount, superParentAmount);
-- select baseAmount, baseAmount2, parentAmount, superParentAmount;
if baseAmount>0 then
call conComAll(baseAmount, parentAmount, 1);
end if;
if baseAmount2>0 then
call conComAll(baseAmount2, superParentAmount, 2);
end if;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delUser`(_uid int)
begin
-- 投注
delete from ssc_bets where `uid`=_uid;
-- 帐变
delete from ssc_coin_log where `uid`=_uid;
-- 管理员日志
delete from ssc_admin_log where `uid`=_uid;
-- 会员登录session
delete from ssc_member_session where `uid`=_uid;
-- 提现
delete from ssc_member_cash where `uid`=_uid;
-- 充值
delete from ssc_member_recharge where `uid`=_uid;
-- 银行
delete from ssc_member_bank where `uid`=_uid;
-- 用户
delete from ssc_members where `uid`=_uid;
end$
CREATE DEFINER=`root`@`localhost` PROCEDURE `delUsers`(_coin float(10,2), _date int)
begin
declare uid_del int;
declare done int default 0;
declare cur cursor for
select distinct u.uid from ssc_members u, ssc_member_session s where u.uid=s.uid and (u.coin+u.fcoin)<_coin and s.accessTime<_date and not exists(select u1.`uid` from ssc_members u1 where u1.parentId=u.`uid`)
union
select distinct u2.uid from ssc_members u2 where (u2.coin+u2.fcoin)<_coin and u2.regTime<_date and not exists (select s1.uid from ssc_member_session s1 where s1.uid=u2.uid);
declare continue HANDLER for not found set done = 1;

复制代码