定时任务每天22点执行

一、业务需求

1、每天22点：把『客户类型』=『普通型合作伙伴』的客户，自动共享给角色『销售人员』和『事业部/大区总经理』

2、新增用户的时候，如果用户角色是『销售人员』或『事业部/大区总经理』

二：demo脚本

-- 1、编写存储过程：实现根据客户类型自动共享
DROP PROCEDURE IF EXISTS `autoshare_accounts_byprofile`;
delimiter ||
CREATE  PROCEDURE `autoshare_accounts_byprofile`(
)
BEGIN

    DECLARE tmp_accountid INT(19) DEFAULT 0;
    DECLARE MYCUSOR CURSOR FOR 
        SELECT 
            accountid 
        FROM ld_account 
        WHERE deleted=0
            AND account_type='普通型合作伙伴';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmp_accountid = NULL;

    OPEN MYCUSOR; 
    FETCH MYCUSOR INTO tmp_accountid;
    WHILE(tmp_accountid IS NOT NULL) DO

        INSERT INTO ld_tabshare(crmid,viewuserid,Shareuserid,createdtime)
        SELECT 
            tmp_accountid,
            a.id,
            1,
            NOW()
        FROM ld_users a
        LEFT JOIN ld_profile b ON a.profileid=b.profileid
        WHERE b.profilename IN ('销售人员','事业部/大区总经理')
            AND a.`status`='Active'
            AND a.id NOT IN (SELECT viewuserid FROM ld_tabshare WHERE crmid=tmp_accountid);

        FETCH MYCUSOR INTO tmp_accountid;
    END WHILE;
    CLOSE MYCUSOR;

END ||
delimiter;

DROP EVENT IF EXISTS `event_autoshare_accounts_byprofile`;
CREATE EVENT `event_autoshare_accounts_byprofile` ON SCHEDULE EVERY 1 DAY STARTS '2022-07-31 22:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 
CALL autoshare_accounts_byprofile();
delimiter;

-- 新增用户时判断，如果角色是 销售人员 或者 事业部/大区总经理 自动共享
DROP TRIGGER IF EXISTS `trigger_after_users_insert`;
delimiter ||
CREATE TRIGGER `trigger_after_users_insert` AFTER INSERT ON `ld_users` FOR EACH ROW 
BEGIN 

    IF EXISTS (SELECT 1 FROM ld_profile WHERE profileid=NEW.profileid AND profilename IN ('销售人员','事业部/大区总经理')) THEN
        INSERT INTO ld_tabshare(crmid,viewuserid,Shareuserid,createdtime)
        SELECT 
            accountid,
            NEW.id,
            1,
            NOW()
        FROM ld_account 
        WHERE deleted=0
            AND account_type='普通型合作伙伴';
    END IF;

END ||
delimiter;

-- 调用存储过程共享一次数据
CALL autoshare_accounts_byprofile();

定时任务每天22点执行.sql