客户未联系天数计算

一、demo脚本

DROP TRIGGER IF EXISTS `up_account_nolinkdays`;
DELIMITER ;;
CREATE TRIGGER `up_account_nolinkdays` BEFORE UPDATE ON `ld_account` FOR EACH ROW BEGIN

    IF @disable_triggers IS NULL THEN
        IF @reset_nolinkdays='yes' OR IFNULL(NEW.lastcontactdate,'0000-00-00')<>IFNULL(OLD.lastcontactdate,'0000-00-00') THEN
            SET @this_max_linkdate=(
                SELECT
                    MAX(max_lxdate)
                FROM (
                    SELECT accountid,MAX(contactdate) AS max_lxdate FROM ld_contactrecords WHERE deleted=0 AND approvestatus<>'已拒绝' AND accountid=NEW.accountid
                    UNION
                    SELECT accountid,MAX(createdtime) AS max_lxdate FROM ld_troubletickets WHERE deleted=0 AND approvestatus<>'已拒绝' AND accountid=NEW.accountid
                    UNION
                    SELECT accountid,MAX(createdtime) AS max_lxdate FROM ld_servicereturn WHERE deleted=0 AND approvestatus<>'已拒绝' AND accountid=NEW.accountid
                ) a
            );

            SET NEW.nolinkdays=DATEDIFF(CURDATE(),DATE_FORMAT(IFNULL(@this_max_linkdate,NEW.createdtime),'%Y-%m-%d'));
            SET @reset_nolinkdays=NULL;
        END IF;

    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `up_faq_pinci1`;
DELIMITER ;;
CREATE TRIGGER `up_faq_pinci1` AFTER INSERT ON `ld_troubletickets` FOR EACH ROW BEGIN 

    IF length(new.questionid)>0 THEN
            call up_faq_byid(new.questionid);
    END IF;

    IF NEW.accountid>0 THEN
        SET @reset_nolinkdays='yes';
        UPDATE ld_account SET nolinkdays=NULL WHERE accountid=NEW.accountid;
        SET @reset_nolinkdays=NULL;
    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `up_faq_pinci2`;
DELIMITER ;;
CREATE TRIGGER `up_faq_pinci2` AFTER UPDATE ON `ld_troubletickets` FOR EACH ROW BEGIN 

    IF length(new.questionid)>0 THEN
            call up_faq_byid(new.questionid);
    END IF;

    IF length(old.questionid)>0 and old.questionid<>new.questionid THEN
            call up_faq_byid(old.questionid);
    END IF;

    IF NEW.accountid>0 OR NEW.deleted<>OLD.deleted OR NEW.approvestatus='已拒绝' THEN
        SET @reset_nolinkdays='yes';
        UPDATE ld_account SET nolinkdays=NULL WHERE accountid=NEW.accountid;
        SET @reset_nolinkdays=NULL;
    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `reset_account_nolinkdays_huifanginsert`;
DELIMITER ;;
CREATE TRIGGER `reset_account_nolinkdays_huifanginsert` AFTER INSERT ON `ld_servicereturn` FOR EACH ROW BEGIN 

    IF NEW.accountid>0 THEN
        SET @reset_nolinkdays='yes';
        UPDATE ld_account SET nolinkdays=NULL WHERE accountid=NEW.accountid;
        SET @reset_nolinkdays=NULL;
    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `reset_account_nolinkdays_huifangupdate`;
DELIMITER ;;
CREATE TRIGGER `reset_account_nolinkdays_huifangupdate` AFTER UPDATE ON `ld_servicereturn` FOR EACH ROW BEGIN 

    IF NEW.accountid>0 THEN
        SET @reset_nolinkdays='yes';
        UPDATE ld_account SET nolinkdays=NULL WHERE accountid=NEW.accountid;
        SET @reset_nolinkdays=NULL;
    END IF;

END
;;
DELIMITER ;

DROP PROCEDURE IF EXISTS `reset_account_nolinkdays`;
delimiter ||
CREATE PROCEDURE `reset_account_nolinkdays`(
)
BEGIN

    SET @disable_triggers=1;
    UPDATE ld_account a
    LEFT JOIN (
        SELECT
            accountid,
            MAX(max_lxdate) AS max_lxdate
        FROM (
            SELECT accountid,MAX(contactdate) AS max_lxdate FROM ld_contactrecords WHERE deleted=0 AND approvestatus<>'已拒绝' GROUP BY accountid
            UNION
            SELECT accountid,MAX(createdtime) AS max_lxdate FROM ld_troubletickets WHERE deleted=0 AND approvestatus<>'已拒绝' GROUP BY accountid
            UNION
            SELECT accountid,MAX(createdtime) AS max_lxdate FROM ld_servicereturn WHERE deleted=0 AND approvestatus<>'已拒绝' GROUP BY accountid 
        ) a
        GROUP BY accountid
    ) b ON b.accountid=a.accountid
    SET a.nolinkdays=DATEDIFF(CURDATE(),DATE_FORMAT(IFNULL(b.max_lxdate,a.createdtime),'%Y-%m-%d'));
    SET @disable_triggers=NULL;

end ||
delimiter;

DROP EVENT IF EXISTS `update_account_nolinkdays`;
DELIMITER ;;
CREATE  EVENT `update_account_nolinkdays` ON SCHEDULE EVERY 1 DAY STARTS '2021-07-20 00:30:00' ON COMPLETION NOT PRESERVE ENABLE 
DO 
    CALL reset_account_nolinkdays();
;;
DELIMITER ;

CALL reset_account_nolinkdays();

客户未联系天数计算.sql