客户的实施负责人和收费负责人自动添加到团队成员并共享

一、业务需求

1、客户的实施负责人和收费负责人自动添加到团队成员并共享

2、共享后提醒(根据需要)

二、demo脚本

-- 客户的实施负责人和收费负责人自动添加到团队成员并共享

-- 1、编写相应的存储过程
DROP PROCEDURE IF EXISTS `suzhould_auto_share_account`;
delimiter ||
CREATE  PROCEDURE `suzhould_auto_share_account`(
    tmp_billid INT(19)
)
BEGIN

    SET @tmp_billid=tmp_billid;

    UPDATE ld_account 
    SET team_memberid=(
        SELECT
            GROUP_CONCAT(DISTINCT shareto ORDER BY shareto)
        FROM (
            SELECT
                substring_index(substring_index(a.team_memberid,',', b.seq+1 ),',',- 1) shareto
            FROM ld_account a
            LEFT JOIN sequence b ON b.seq<(length(a.team_memberid)-length(REPLACE(a.team_memberid,',',''))+1) 
            WHERE substring_index(substring_index(a.team_memberid,',', b.seq+1 ),',',- 1)>0
                AND a.accountid=@tmp_billid
            UNION
            SELECT cf_4737 AS shareto FROM ld_accountscf WHERE accountid=@tmp_billid AND cf_4737>0
            UNION
            SELECT cf_4741 AS shareto FROM ld_accountscf WHERE accountid=@tmp_billid AND cf_4741>0
        ) a
        WHERE shareto>0
    )
    WHERE accountid=@tmp_billid;

    INSERT INTO ld_tabshare(crmid,viewuserid,Shareuserid,createdtime,viewuserremark)
    SELECT 
        a.accountid,
        b.cf_4737,
        a.modifiedby,
        NOW(),
        '自动共享实施负责人'
    FROM ld_account a
    LEFT JOIN ld_accountscf b ON b.accountid=a.accountid
    WHERE a.accountid=@tmp_billid
        AND NOT EXISTS (SELECT 1 FROM ld_tabshare WHERE crmid=a.accountid AND viewuserid=b.cf_4737)
        AND b.cf_4737>0
    UNION
    SELECT 
        a.accountid,
        b.cf_4741,
        a.modifiedby,
        NOW(),
        '自动共享服务收费人'
    FROM ld_account a
    LEFT JOIN ld_accountscf b ON b.accountid=a.accountid
    WHERE a.accountid=@tmp_billid
        AND NOT EXISTS (SELECT 1 FROM ld_tabshare WHERE crmid=a.accountid AND viewuserid=b.cf_4737)
        AND b.cf_4741>0;

end ||
delimiter;

-- 2、通过系统定制->工作流程定制->执行SQL
CALL suzhould_auto_share_peoject('{Record.ID}');

-- 3、共享后提醒(根据需要)
DROP TRIGGER IF EXISTS `trigger_after_tabshare_insert`;
DELIMITER ;;
CREATE TRIGGER `trigger_after_tabshare_insert` BEFORE INSERT ON `ld_tabshare` FOR EACH ROW BEGIN

    IF EXISTS (SELECT 1 FROM ld_account WHERE accountid=NEW.crmid) THEN
        CALL ldcrm_add_reminder('有新客户共享给你，请及时跟进!',NEW.viewuserid,'Accounts',NEW.crmid);
    END IF;

END
;;
DELIMITER ;

客户的实施负责人和收费负责人自动添加到团队成员及共享.sql