根据邮件是否回复实现客户自动回收

一、demo脚本

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

    
    DECLARE tmp_hour INT(19) DEFAULT 72;        -- 72 小时未回复，自动回收
    
    DECLARE tmp_emaildate DATETIME DEFAULT NULL;
    DECLARE tmp_fromemail VARCHAR(100) DEFAULT '';
    DECLARE tmp_accountid INT(19) DEFAULT 0;
    DECLARE tmp_smownerid INT(19) DEFAULT 0;

    -- 01.获取过去72小时有邮件的非公海客户
    DECLARE MYCUSOR CURSOR FOR 
        SELECT
            MAX(a.emaildate) AS emaildate,
            a.from_email,
            a.accountid,
            b.smownerid
        FROM (
            SELECT
                emaildate,
                from_email,
                (SELECT x.accountid FROM ld_contactdetails x LEFT JOIN ld_contactscf y ON y.contactid=x.contactid WHERE x.deleted=0 AND y.cf_5304=a.from_email LIMIT 1) AS accountid
            FROM (
            SELECT 
                    userid,
                    CASE
                        WHEN LOCATE('<',mfrom)>0 AND LOCATE('>',mfrom)>0 THEN SUBSTR(mfrom FROM LOCATE('<',mfrom)+1 FOR LOCATE('>',mfrom)-LOCATE('<',mfrom)-1)
                        ELSE mfrom
                    END AS from_email,
                    mto,
                    MAX(mdate) AS emaildate,
                    msubject
                FROM ld_mailmanager_mailrecord
                WHERE mailtype='pop'
                    AND mdate<DATE_SUB(NOW(),INTERVAL tmp_hour HOUR)
                GROUP BY mfrom
            ) a
            WHERE (SELECT x.accountid FROM ld_contactdetails x LEFT JOIN ld_contactscf y ON y.contactid=x.contactid WHERE x.deleted=0 AND y.cf_5304=a.from_email LIMIT 1)>0
            ORDER BY a.emaildate
        ) a
        INNER JOIN ld_account b ON b.accountid=a.accountid
        WHERE b.publicaccount IN (0,2,3)
        GROUP BY a.accountid;

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

    OPEN MYCUSOR; 
    FETCH MYCUSOR INTO tmp_emaildate,tmp_fromemail,tmp_accountid,tmp_smownerid;
    WHILE(tmp_accountid IS NOT NULL) DO
        -- 02.查询客户自收到邮件后72小时内，是否给对应邮箱发送过邮件（只计算抄送给 ld@idealhouseware.com 的）
        SET @email_cnt=(
            SELECT 
                COUNT(1)
            FROM ld_mailmanager_mailrecord 
            WHERE (mailaccountname LIKE '%ld@idealhouseware.com%' OR mcc LIKE '%ld@idealhouseware.com%')
                AND mdate BETWEEN tmp_emaildate AND DATE_ADD(tmp_emaildate,INTERVAL tmp_hour HOUR)
                AND (
                        CASE
                            WHEN LOCATE('<',mto)>0 AND LOCATE('>',mto)>0 THEN SUBSTR(mto FROM LOCATE('<',mto)+1 FOR LOCATE('>',mto)-LOCATE('<',mto)-1)
                            ELSE mto
                        END)=tmp_fromemail
            );

        IF @email_cnt=0 THEN
            -- 没有回复过邮件，客户自动转公海
            UPDATE ld_account SET publicaccount=1,pool_id=1 WHERE accountid=tmp_accountid;

            INSERT INTO ld_logpublicaccount(username,operation,opdate,accountid,userid,pool_id)
            SELECT '系统',CONCAT(tmp_hour,'小时未回复邮件，客户自动转公海'),NOW(),tmp_accountid,1,1;

            CALL ldcrm_add_reminder(CONCAT(tmp_hour,'小时未回复邮件，客户自动转公海'),tmp_smownerid,'Accounts',tmp_accountid);
        END IF;

        FETCH MYCUSOR INTO tmp_emaildate,tmp_fromemail,tmp_accountid,tmp_smownerid;
    END WHILE;
    CLOSE MYCUSOR;

END ||
delimiter;

CALL xiangcheng_huishou_by_email();

DROP EVENT IF EXISTS `xiangcheng_huishou_by_email_event`;
delimiter ||
CREATE  EVENT `xiangcheng_huishou_by_email_event` ON SCHEDULE EVERY 1 DAY STARTS '2023-07-07 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO 
CALL xiangcheng_huishou_by_email();
||
delimiter;

根据邮件自动回收.sql