时间：2020-07-28 21:12:41
自动撤消存储过程
DELIMITER $$
-- 调用例子
-- call Auto_UndoOrder(@sumss);
-- SELECT @sumss
CREATE DEFINER=`root`@`localhost` PROCEDURE `Auto_UndoOrder`(OUT txt varchar(100))
COMMENT '自动撤消任务'
BEGIN
#Routine body goes here...
-- 游标所使用变量需要在定义游标之前申明
declare counter int ;
declare rwid int(11);
declare kf_role_id int default 4; -- 客服发单者
declare myrole_id int; -- 客服发单者
declare Result int(11);
DECLARE renwu_number_total int DEFAULT 0;
declare order_sn varchar(30);
declare userid int(11);
declare renwu_oyalties FLOAT(8,2);
declare renwu_bond FLOAT(8,2);
declare renwu_scharge FLOAT(8,2);
declare inputtime int(11);
declare inputtime2 int(11);
declare is_renwu_bond int(1);
declare is_renwu_scharge int(1);
declare mystatus int(2);
declare renwu_undo FLOAT(8,2);
declare return_amount FLOAT(8,2) default 0;# 要退的费用合计
DECLARE nowtimestamp int DEFAULT UNIX_TIMESTAMP(); -- 当前时间戳
declare xsuserid int(11);
declare xs_amount FLOAT(10,2);
#查询已超交稿时间的任务总数
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare _Renwu CURSOR FOR SELECT a.* FROM (SELECT
rwt.id,
rwt.status,
rwt.order_sn,
rwt.userid,
rwt.renwu_oyalties,
rwt.renwu_bond,
rwt.renwu_scharge,
rwt.inputtime,
case when rwt.renwu_undo > 0 THEN (rwt.inputtime + rwt.renwu_undo*3600) ELSE rwt.inputtime END as inputtime2,
rwt.renwu_undo,
rwt.is_renwu_bond,
rwt.is_renwu_scharge
FROM
springcms_renwu rwt
WHERE
`status` in ('10','30')) a where a.inputtime2 < nowtimestamp;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #如果读取完，设置done为True
set counter = 0;# 计数，循环了几次
open _Renwu;
repeat
set counter = counter +1;
fetch _Renwu into rwid,mystatus,order_sn,userid,renwu_oyalties,renwu_bond,renwu_scharge,inputtime,inputtime2,renwu_undo,is_renwu_bond,is_renwu_scharge;
-- select rwid ;
-- concat_ws函数用逗号后面的参数隔开 concat_ws(',','12','33','332') --> 12,33,332
-- 如果不加判断，会多循环一次。最后一次读取游标时，done已经是True，但是进入循环之前是0，故可以进入循环，会多循环一次；
if done<>TRUE then
-- 调试用
SET txt=concat_ws(',',rwid,mystatus,order_sn,userid,renwu_oyalties,renwu_bond,renwu_scharge,inputtime,inputtime2,renwu_undo,is_renwu_bond,is_renwu_scharge);
SELECT txt;
-- 处理自己代码
-- 查询当前所在组
select u.role_id into myrole_id FROM springcms_user u where id=userid;
-- 生成自动撤消任务退费记录
if myrole_id<>kf_role_id then
-- 客服发单者不退稿费，因为就没交稿费
set return_amount = renwu_oyalties;
end if;
if is_renwu_bond<>1 THEN
set return_amount = return_amount + renwu_bond;
end if;
if is_renwu_scharge<>1 THEN
set return_amount = return_amount + renwu_scharge;
end if;
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
userid,
'11',
return_amount,
order_sn,
'127.0.0.1',
'系统自动撤消任务，退还费用',
nowtimestamp,
'1'
);
-- 更新发单者用户余额
UPDATE `springcms_user` SET `amount`=amount+return_amount WHERE `id` = userid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'费用退还！',
concat('系统自动撤消任务，退还',return_amount,'元，任务订单号是',order_sn,'，点击<a href=\"http://20180308.aaa.com/index.php?g=Renwu&m=Renwu&a=detail&order_sn=',order_sn,'\" target=\"_blank\">查看</a>'),
'1',
'0',
userid,
'1',
'2',
nowtimestamp,
'0'
);
SET Result=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Result, userid, '0', '0', '0', '0');
if mystatus = 30 THEN
SELECT rco.userid,rco.amount INTO xsuserid,xs_amount FROM `springcms_renwu_consult` rco WHERE rco.`rwid` = rwid limit 1;
-- 有协商信息
SELECT xsuserid;
if xsuserid THEN
UPDATE `springcms_renwu_consult` SET `returntype`='2',`returntime`=nowtimestamp WHERE `rwid` = rwid;
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
xsuserid,
'52',
xs_amount,
order_sn,
'127.0.0.1',
'系统自动撤消任务，退还协商费',
nowtimestamp,
'1'
);
UPDATE `springcms_user` SET `amount`=amount+xs_amount WHERE `id` = xsuserid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'费用退还！',
concat('系统自动撤消任务，退还协商费',xs_amount,'元，任务订单号是',order_sn,'，点击<a href=\"http://20180308.aaa.com/index.php?g=Renwu&m=Renwu&a=detail&order_sn=',order_sn,'\" target=\"_blank\">查看</a>'),
'1',
'0',
xsuserid,
'1',
'2',
nowtimestamp,
'0'
);
SET Result=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Result, xsuserid, '0', '0', '0', '0');
end if;
end if;
-- 生成任务变更日志
INSERT INTO `springcms_renwu_log` (`inputtime`,`ip`,`userid`,`type`,`rwid`,`info`) VALUES (nowtimestamp,'127.0.0.1','1','60',rwid,'系统自动撤消');-- 以管理员操作
-- 更新任务状态
UPDATE `springcms_renwu` SET `status`='70',`undo_time`=nowtimestamp WHERE `id` = rwid;
end if;
until done
end repeat;
-- 注意关闭游标
close _Renwu;
END $$
DELIMITER ;
自动撤消存储过程-不发站内信
DELIMITER $$
-- 调用例子
-- call Auto_UndoOrder(@sumss);
-- SELECT @sumss
CREATE DEFINER=`root`@`localhost` PROCEDURE `Auto_UndoOrder`(OUT txt varchar(100))
COMMENT '自动撤消任务'
BEGIN
#Routine body goes here...
-- 游标所使用变量需要在定义游标之前申明
declare counter int ;
declare rwid int(11);
declare kf_role_id int default 4; -- 客服发单者
declare myrole_id int; -- 客服发单者
declare Result int(11);
DECLARE renwu_number_total int DEFAULT 0;
declare order_sn varchar(30);
declare userid int(11);
declare renwu_oyalties FLOAT(8,2);
declare renwu_bond FLOAT(8,2);
declare renwu_scharge FLOAT(8,2);
declare inputtime int(11);
declare inputtime2 int(11);
declare is_renwu_bond int(1);
declare is_renwu_scharge int(1);
declare renwu_undo FLOAT(8,2);
declare return_amount FLOAT(8,2) default 0;# 要退的费用合计
DECLARE nowtimestamp int DEFAULT UNIX_TIMESTAMP(); -- 当前时间戳
#查询已超交稿时间的任务总数
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare _Renwu CURSOR FOR SELECT a.* FROM (SELECT
rwt.id,
rwt.order_sn,
rwt.userid,
rwt.renwu_oyalties,
rwt.renwu_bond,
rwt.renwu_scharge,
rwt.inputtime,
case when rwt.renwu_undo > 0 THEN (rwt.inputtime + rwt.renwu_undo*3600) ELSE rwt.inputtime END as inputtime2,
rwt.renwu_undo,
rwt.is_renwu_bond,
rwt.is_renwu_scharge
FROM
springcms_renwu rwt
WHERE
`status` = '10') a where a.inputtime2 < nowtimestamp;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #如果读取完，设置done为True
set counter = 0;# 计数，循环了几次
open _Renwu;
repeat
set counter = counter +1;
fetch _Renwu into rwid,order_sn,userid,renwu_oyalties,renwu_bond,renwu_scharge,inputtime,inputtime2,renwu_undo,is_renwu_bond,is_renwu_scharge;
-- select rwid ;
-- concat_ws函数用逗号后面的参数隔开 concat_ws(',','12','33','332') --> 12,33,332
-- 如果不加判断，会多循环一次。最后一次读取游标时，done已经是True，但是进入循环之前是0，故可以进入循环，会多循环一次；
if done<>TRUE then
-- 调试用
SET txt=concat_ws(',',rwid,order_sn,userid,renwu_oyalties,renwu_bond,renwu_scharge,inputtime,inputtime2,renwu_undo,is_renwu_bond,is_renwu_scharge);
SELECT txt;
-- 处理自己代码
-- 查询当前所在组
select u.role_id into myrole_id FROM springcms_user u where id=userid;
-- 生成自动撤消任务退费记录
if myrole_id<>kf_role_id then
-- 客服发单者不退稿费，因为就没交稿费
set return_amount = renwu_oyalties;
end if;
if is_renwu_bond<>1 THEN
set return_amount = return_amount + renwu_bond;
end if;
if is_renwu_scharge<>1 THEN
set return_amount = return_amount + renwu_scharge;
end if;
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
userid,
'11',
return_amount,
order_sn,
'127.0.0.1',
'系统自动撤消任务(order_sn)，退还费用',
nowtimestamp,
'1'
);
-- 更新发单者用户余额
UPDATE `springcms_user` SET `amount`=amount+return_amount WHERE `id` = userid;
-- 生成任务变更日志
INSERT INTO `springcms_renwu_log` (`inputtime`,`ip`,`userid`,`type`,`rwid`,`info`) VALUES (nowtimestamp,'127.0.0.1','1','70',rwid,'系统自动撤消');-- 以管理员操作
-- 更新任务状态
UPDATE `springcms_renwu` SET `status`='70',`undo_time`=nowtimestamp WHERE `id` = rwid;
end if;
until done
end repeat;
-- 注意关闭游标
close _Renwu;
END $$
DELIMITER ;
自动结算存储过程
DELIMITER $$
-- 调用例子
-- call Auto_Settlement(@sumss);
-- SELECT @sumss
CREATE DEFINER=`root`@`localhost` PROCEDURE `Auto_Settlement`(OUT txt varchar(100))
COMMENT '自动结算超时任务'
BEGIN
#Routine body goes here...
declare kf_role_id int default 4; -- 客服发单者 客服发单组结算时需要扣稿费
DECLARE rwid int;
declare order_sn varchar(30);
declare userid int(11);
declare myrole_id int(11);
declare Resultid int;
declare renwu_xs_oyalties FLOAT(8,2);
declare renwu_xs_bond FLOAT(8,2);
declare xsfdzxs FLOAT(8,2);
declare renwu_oyalties FLOAT(8,2);
declare is_renwu_bond int(1);
declare renwu_bond FLOAT(8,2);
declare pay_xs_amount FLOAT(8,2) default 0;# 写手
declare pay_fdz_amount FLOAT(8,2) default 0;# 发单者
declare return_fdz_bond FLOAT(8,2) default 0;# 发单者保证金
DECLARE nowtimestamp int DEFAULT UNIX_TIMESTAMP(); -- 当前时间戳
declare my_oyalties FLOAT(8,2);
declare my_userid int(11);
declare my_order_id int(11);
declare my_is_bond int(1);
declare my_bond FLOAT(8,2);
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare _Renwu_S CURSOR FOR SELECT
rw.id,
rw.userid,
rw.order_sn,
rw.xsfdzxs,
rw.renwu_xs_oyalties,
rw.renwu_xs_bond,
rw.renwu_oyalties,
rw.is_renwu_bond,
rw.renwu_bond
FROM
`springcms_renwu` as rw
WHERE
rw.renwu_settle <= nowtimestamp
AND rw.`status` IN (20, 50);
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #如果读取完，设置done为True
open _Renwu_S;
repeat
fetch _Renwu_S into rwid,userid,order_sn,xsfdzxs,renwu_xs_oyalties,renwu_xs_bond,renwu_oyalties,is_renwu_bond,renwu_bond;
-- select rwid ;
-- concat_ws函数用逗号后面的参数隔开 concat_ws(',','12','33','332') --> 12,33,332
-- 如果不加判断，会多循环一次。最后一次读取游标时，done已经是True，但是进入循环之前是0，故可以进入循环，会多循环一次；
if done<>TRUE then
-- 调试用
SET txt=concat_ws(',',rwid,order_sn,userid,xsfdzxs,renwu_xs_oyalties,renwu_xs_bond,renwu_oyalties,is_renwu_bond,renwu_bond);
SELECT txt;
-- 查询当前所在组
select u.role_id into myrole_id FROM springcms_user u where u.id = userid;
-- 处理发单者支付稿费问题
if myrole_id=kf_role_id THEN
-- 客服发单者因为就没交稿费,所以需要扣除
if renwu_xs_oyalties>0 THEN
-- 协商费用
set pay_fdz_amount = ceil(renwu_xs_oyalties*xsfdzxs); -- 需要乘系数
ELSE
set pay_fdz_amount = renwu_oyalties;
end if;
-- 扣费记录
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
userid,
'20',
pay_fdz_amount,
order_sn,
'127.0.0.1',
'结算任务稿费扣除',
nowtimestamp,
'1'
);
-- 更新发单者用户余额
UPDATE `springcms_user` SET `amount`=amount-pay_fdz_amount WHERE `id` = userid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'结算消费',
concat('结算任务(',order_sn,')支付写手稿费',pay_fdz_amount,'元'),
'1',
'0',
userid,
'1',
'2',
nowtimestamp,
'0'
);
SET Resultid=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Resultid, userid, '0', '0', '0', '0');
end if;
-- 退给发单者保证金
if is_renwu_bond<>1 THEN
-- 更新发单者用户余额
if renwu_xs_bond>0 THEN
-- set return_fdz_bond=ceil(renwu_xs_bond*xsfdzxs); -- 需要乘系数
set return_fdz_bond=renwu_bond;
ELSE
set return_fdz_bond=renwu_bond;
end if;
UPDATE `springcms_user` SET `amount`=amount+return_fdz_bond WHERE `id` = userid;
-- 生成退保证金记录
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
userid,
'30',
return_fdz_bond,
order_sn,
'127.0.0.1',
'结算任务退保证金',
nowtimestamp,
'1'
);
-- 更新发单者用户余额
UPDATE `springcms_user` SET `amount`=amount+return_fdz_bond WHERE `id` = userid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'退回保证金',
concat('结算任务(',order_sn,')退回保证金',return_fdz_bond,'元'),
'1',
'0',
userid,
'1',
'2',
nowtimestamp,
'0'
);
SET Resultid=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Resultid, userid, '0', '0', '0', '0');
end if;
-- 处理写手部分--------------------------------------------------------------------------------------------------------------------------
SELECT xso.order_id as my_order_id,xso.user_id,xso.oyalties,xso.bond,xso.is_bond INTO my_order_id,my_userid,my_oyalties,my_bond,my_is_bond FROM `springcms_xsorder` xso where xso.rwid=rwid;
-- 获得稿费记录
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
my_userid,
'21',
my_oyalties,
order_sn,
'127.0.0.1',
'结算稿费应得',
nowtimestamp,
'1'
);
-- 更新写手用户余额
UPDATE `springcms_user` SET `amount`=amount+my_oyalties WHERE `id` = my_userid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'结算任务所得',
concat('结算任务(',order_sn,')所得稿费',my_oyalties,'元'),
'1',
'0',
my_userid,
'1',
'2',
nowtimestamp,
'0'
);
SET Resultid=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Resultid, my_userid, '0', '0', '0', '0');
-- 退保证金处理
if my_is_bond<>1 THEN
-- 退还写手保证金记录
INSERT INTO `springcms_user_xiaofei` (
`id`,
`uid`,
`type`,
`amount`,
`order_id`,
`ip`,
`remark`,
`create_time`,
`status`
)
VALUES
(
NULL,
my_userid,
'21',
my_bond,
order_sn,
'127.0.0.1',
'结算任务退保证金',
nowtimestamp,
'1'
);
-- 更新写手用户余额
UPDATE `springcms_user` SET `amount`=amount+my_bond WHERE `id` = my_userid;
-- 发送站内信
INSERT INTO `springcms_user_msgtext` (
`type`,
`title`,
`note`,
`senduserid`,
`rwid`,
`userids`,
`issysadd`,
`islock`,
`addtime`,
`mestype`
)
VALUES
(
'8',
'退回保证金',
concat('结算任务(',order_sn,')退回保证金',my_bond,'元'),
'1',
'0',
my_userid,
'1',
'2',
nowtimestamp,
'0'
);
SET Resultid=LAST_INSERT_ID(); -- 获取插入的ID值
INSERT INTO `springcms_user_msg` (
`msgid`,
`userid`,
`status`,
`rwid`,
`datetime`,
`readtime`
)
VALUES
(Resultid, my_userid, '0', '0', '0', '0');
end if;
UPDATE `springcms_xsorder` SET `order_status`='40' WHERE `order_id` = my_order_id;-- 更新状态
UPDATE `springcms_renwu` SET `status`='60' WHERE `id` = rwid;-- 更新任务状态
-- 生成任务日志
INSERT INTO `springcms_renwu_log`(`userid`,`inputtime`,`ip`,`type`,`rwid`,`info`) VALUES(userid,nowtimestamp,'127.0.0.1',60,rwid,'确认结算');
end if;
until done
end repeat;
-- 注意关闭游标
close _Renwu_S;
END $$
DELIMITER ;
自动处理事件(通过事件调用存储过程)
DELIMITER $$
CREATE DEFINER=`root`@`localhost` EVENT `event_minute` ON SCHEDULE EVERY 2 MINUTE STARTS '2018-01-17 14:49:43' ON COMPLETION NOT PRESERVE ENABLE COMMENT '一分钟一次。' DO BEGIN
-- INSERT INTO USER(name, address,addtime) VALUES('test2','test2',now());
CALL Auto_Settlement(@txt);
CALL Auto_UndoOrder(@txt2);
INSERT INTO springcms_event_log(txt1, txt2,addtime) VALUES(@txt,@txt2,now());
END $$
DELIMITER ;
1955
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
