N天未跟进提醒

一、demo脚本

DROP PROCEDURE IF EXISTS `ldcrm_remindaccount_ndays`;
delimiter ||
CREATE  PROCEDURE `ldcrm_remindaccount_ndays`(
    tmp_days INT(4)
)
BEGIN

    DECLARE tmp_billid INT(19) DEFAULT 0;
    DECLARE tmp_ownerid INT(19) DEFAULT 0;
    DECLARE tmp_weekday INT(4) DEFAULT 0;
    DECLARE tmp_thismonday DATE DEFAULT NULL;
    DECLARE tmp_content TEXT DEFAULT '';

    DECLARE REMIND_CUSOR CURSOR FOR 
            SELECT
                a.accountid,
                a.smownerid,
                WEEKDAY(CURDATE()) AS weekday,
                DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) AS this_monday,
                CASE
                    WHEN IFNULL(a.rating,'')='' THEN
                        CONCAT(
                            '客户【',
                            IFNULL(a.accountname,''),
                            '】已经',
                            LEAST(DATEDIFF(NOW(),a.createdtime),IFNULL(b.nolx_days,10000),IFNULL(c.nofw_days,1000)),
                            '天没有回访跟进了,请定期回访您的重要客户。'
                        )
                ELSE
                        CONCAT(
                            '【',a.rating,'】'
                            '客户【',
                            IFNULL(a.accountname,''),
                            '】已经',
                            LEAST(DATEDIFF(NOW(),a.createdtime),IFNULL(b.nolx_days,10000),IFNULL(c.nofw_days,1000)),
                            '天没有回访跟进了,请定期回访您的重要客户。'
                        )    
                END AS remind_text
            FROM ld_account a
            LEFT JOIN (
                SELECT 
                    accountid,
                    MAX(contactdate) AS max_lxdate,
                    DATEDIFF(NOW(),MAX(contactdate)) AS nolx_days
                FROM ld_contactrecords 
                WHERE accountid>0 
                    AND approvestatus<>'已拒绝' 
                GROUP BY accountid
            ) b ON b.accountid=a.accountid
            LEFT JOIN (
                SELECT 
                    accountid,
                    MAX(IF(realtime>'1900-01-01',realtime,createdtime)) AS max_fwdate,
                    DATEDIFF(NOW(),MAX(IF(realtime>'1900-01-01',realtime,createdtime))) AS nofw_days
                FROM ld_troubletickets 
                WHERE accountid>0 
                    AND approvestatus<>'已拒绝' 
                GROUP BY accountid
            ) c ON c.accountid=a.accountid
            WHERE a.deleted=0
                AND IFNULL(a.rating,'') IN ('旗舰版客户90天拜访')            
                AND LEAST(DATEDIFF(NOW(),a.createdtime),IFNULL(b.nolx_days,10000),IFNULL(c.nofw_days,1000))>tmp_days;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmp_billid = NULL;

    OPEN REMIND_CUSOR; 
    FETCH REMIND_CUSOR INTO tmp_billid,tmp_ownerid,tmp_weekday,tmp_thismonday,tmp_content;
    WHILE(tmp_billid IS NOT NULL) DO
        IF tmp_weekday=0 THEN
            -- 如果是周一就提醒
            -- 提醒3个人 1：'刘志芳'     3：'卢伟其'     13：'谢蕾'
            CALL ldcrm_add_reminder(tmp_content,1,'Accounts',tmp_billid);
            CALL ldcrm_add_reminder(tmp_content,3,'Accounts',tmp_billid);
            CALL ldcrm_add_reminder(tmp_content,13,'Accounts',tmp_billid);
        ELSE
            -- 如果不是周一，判断是否有周一的提醒
            IF NOT EXISTS (SELECT 1 FROM ld_reminds WHERE reltab='Accounts' AND related_to=tmp_billid AND DATE_FORMAT(date_time,'%Y-%m-%d')=tmp_thismonday AND content LIKE '%天没有回访跟进了,请定期回访您的重要客户%' AND recieverid IN (1,3,13)) THEN
                -- 提醒3个人 1：'刘志芳'     3：'卢伟其'     13：'谢蕾'
                CALL ldcrm_add_reminder(tmp_content,1,'Accounts',tmp_billid);
                CALL ldcrm_add_reminder(tmp_content,3,'Accounts',tmp_billid);
                CALL ldcrm_add_reminder(tmp_content,13,'Accounts',tmp_billid);
            END IF;
        END IF;
        FETCH REMIND_CUSOR INTO tmp_billid,tmp_ownerid,tmp_weekday,tmp_thismonday,tmp_content;
    END WHILE;
    CLOSE REMIND_CUSOR;

end ||
delimiter;

DROP EVENT IF EXISTS `ldcrm_remindaccount_everyday`;
delimiter ||
CREATE EVENT `ldcrm_remindaccount_everyday` ON SCHEDULE EVERY 1 DAY STARTS '2021-06-25 08:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 
    CALL ldcrm_remindaccount_ndays(90);            -- 90天未跟进
||
delimiter;

N天未跟进提醒_.sql