一、demo脚本
DROP EVENT IF EXISTS `jingmin_reset_paymentplaninfo_by_contactrecords_event`;
delimiter ||
CREATE EVENT `jingmin_reset_paymentplaninfo_by_contactrecords_event` ON SCHEDULE EVERY 1 DAY STARTS '2023-01-06 04:00:00' ON COMPLETION NOT PRESERVE ENABLE DO
UPDATE (
SELECT
a.customform03_id,
MAX(a.nextContactDate) AS nextContactDate
FROM ld_contactrecords a
LEFT JOIN ld_contactrecordscf b ON b.contactrecordid=a.contactrecordID
WHERE a.deleted=0
AND a.createdtime>DATE_SUB(CURDATE(),INTERVAL 1 DAY)
AND a.nextContactDate>'1900-01-01'
AND a.customform03_id>0
AND b.cf_4972='回款逾期跟进任务'
GROUP BY a.customform03_id
) a
LEFT JOIN ld_customform03 b ON b.customform03id=a.customform03_id
LEFT JOIN ld_paymentplan c oN c.accountid=b.accountid AND c.smownerid=b.smownerid
LEFT JOIN ld_paymentplanscf d ON d.paymentplanid=c.paymentplanid
SET d.cf_4813=a.nextContactDate
WHERE b.cf_4839='回款逾期跟进任务'
AND d.cf_4814>=0 -- 逾期天数大于等于0
AND IFNULL(d.cf_4812,'')<>'完全核销' -- 核销状态不包含完全核销
AND c.deleted=0;
||
delimiter;