set time_zone = '+8:00';
set GLOBAL event_scheduler = 1;
drop event if exists commission_ms_ever_month;
DELIMITER $$
create event commission_ms_ever_month
on schedule every 1 MONTH starts '2018-07-01 03:00:00'
do
begin
CALL proc_commission_ms();
end $$
DELIMITER ;
DROP PROCEDURE IF EXISTS proc_commission_ms;
CREATE PROCEDURE proc_commission_ms()
BEGIN
DECLARE txTime1 datetime;
DECLARE orderCode1 CHAR(100);
DECLARE receiveId1 int(11);
DECLARE receiveName1 CHAR(100);
DECLARE xhTime1 datetime;
DECLARE submitRole1 CHAR(255);
DECLARE orderCode2 CHAR(100);
DECLARE doneTime2 CHAR(100);
DECLARE done INT DEFAULT FALSE;
DECLARE edone INT DEFAULT FALSE;
DECLARE _outerForEach CURSOR FOR
SELECT
MIN(tx.finish_time) AS txTime,
tx.order_code AS orderCode,
tx.receive_id AS receiveId,
tx.receive_name AS receiveName,
MIN(sh.finish_time) AS xhTime,
tx.submit_role AS submitRole
FROM
v_mytask_txmsxx tx,
v_mytask_zgsh sh
WHERE
tx.order_code = sh.order_code
AND tx.order_code in(SELECT order_code FROM v_mytask_zs)
AND
tx.order_code IN (
SELECT order_code FROM v_order_lastmonth_reject_loaned
)
GROUP BY
tx.order_code;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN _outerForEach;
read_loop: LOOP
FETCH _outerForEach INTO txTime1, orderCode1, receiveId1, receiveName1, xhTime1, submitRole1;
IF done THEN
LEAVE read_loop;
END IF;
BEGIN
DECLARE _innerForEach CURSOR FOR
SELECT order_code as orderCode,finish_time AS doneTime FROM v_order_reject
WHERE order_code not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1)
UNION ALL
SELECT orderCode as orderCode,finishTime AS doneTime FROM v_task_loaned
WHERE orderCode not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;
OPEN _innerForEach;
inner_loop: LOOP
FETCH _innerForEach INTO orderCode2,doneTime2;
IF edone THEN
LEAVE inner_loop;
ELSE
IF (orderCode2 = orderCode1 AND xhTime1 IS NOT NULL AND doneTime2 IS NOT NULL AND TIMESTAMPDIFF(MONTH,xhTime1,doneTime2) <= 3) THEN
INSERT INTO `commission_inquiry_collection` (
`user_id`,
`order_code`,
`execute_time`,
`task_code`,
`count_time`,
`commission_time`,
`type`,
`submit_role`,
`city`,
`ext3`
) VALUES (receiveId1,orderCode1,xhTime1,"T_TXMSXX_0002",NOW(),DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'1',submitRole1,NULL,NULL);
END IF;
END IF;
END LOOP;
CLOSE _innerForEach;
SET edone = FALSE;
END;
END LOOP;
CLOSE _outerForEach;
COMMIT;
END;