客户最新联系日期根据联系记录和服务单一起计算

一、应用场景

客户最新联系日期根据联系记录和服务单一起计算

二、demo脚本

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

    IF @disable_triggers IS NULL THEN
        IF length(new.questionid)>0 THEN
                call up_faq_byid(new.questionid);
        END IF;

        IF NEW.accountid>0 THEN
            UPDATE ld_account SET lastcontactdate=NOW() WHERE accountid=NEW.accountid;
        END IF;
    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 @disable_triggers IS NULL THEN
        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;

        UPDATE ld_account SET lastcontactdate=NOW() WHERE accountid IN (NEW.accountid,OLD.accountid);
    END IF;

END
;;
DELIMITER ;

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 IFNULL(NEW.lastcontactdate,'0000-00-00')<>IFNULL(OLD.lastcontactdate,'0000-00-00') THEN
            SET NEW.lastcontactdate=(
                SELECT
                    MAX(a.contactdate) AS maxcontactdate
                FROM (
                    SELECT
                        a.accountid,
                        MAX(a.contactdate) AS contactdate
                    FROM ld_contactrecords a
                    WHERE a.deleted=0
                        AND a.approvestatus<>'已拒绝'
                        AND a.accountid>0
                        AND a.accountid=NEW.accountid
                    GROUP BY a.accountid
                    UNION
                    SELECT
                        a.accountid,
                        MAX(a.createdtime) contactdate
                    FROM ld_troubletickets a
                    WHERE a.deleted=0
                        AND a.approvestatus<>'已拒绝'
                        AND a.accountid>0
                        AND a.accountid=NEW.accountid
                    GROUP BY a.accountid
                ) a
                GROUP BY a.accountid
                );
            SET NEW.nolinkdays=DATEDIFF(CURDATE(),IFNULL(NEW.lastcontactdate,DATE_FORMAT(NEW.createdtime,'%Y-%m-%d')));
        END IF;
    END IF;

END
;;
DELIMITER ;

SET    @disable_triggers='abcd';
UPDATE ld_account a
LEFT JOIN (
    SELECT
        a.accountid,
        MAX(a.contactdate) AS maxcontactdate
    FROM (
        SELECT
            a.accountid,
            MAX(a.contactdate) AS contactdate
        FROM ld_contactrecords a
        WHERE a.deleted=0
            AND a.approvestatus<>'已拒绝'
            AND a.accountid>0
        GROUP BY a.accountid
        UNION
        SELECT
            a.accountid,
            MAX(a.createdtime) contactdate
        FROM ld_troubletickets a
        WHERE a.deleted=0
            AND a.approvestatus<>'已拒绝'
            AND a.accountid>0
        GROUP BY a.accountid
    ) a
    GROUP BY a.accountid
) b ON b.accountid=a.accountid
SET a.lastcontactdate=b.maxcontactdate;
SET    @disable_triggers=NULL;

UPDATE ld_account SET nolinkdays=DATEDIFF(CURDATE(),IF(lastcontactdate>'1900-01-01',DATE_FORMAT(lastcontactdate,'%Y-%m-%d'),DATE_FORMAT(createdtime,'%Y-%m-%d')));

客户最新联系日期根据联系记录和服务单一起计算.sql