订单自动共享审批人

一、demo脚本

DROP PROCEDURE IF EXISTS `yinghua_auto_share_order`;
delimiter ||
CREATE  PROCEDURE `yinghua_auto_share_order`(
    tmp_billid INT(19)
)
BEGIN

    SET @tmp_billid=tmp_billid;
    SELECT smownerid,approvestatus INTO @tmp_ownerid,@tmp_approvestatus FROM ld_salesorder WHERE salesorderid=@tmp_billid;
 
    IF @tmp_approvestatus IN ('未提交','已驳回') THEN
        DELETE FROM ld_tabshare WHERE crmid=@tmp_billid AND viewuserremark='自动共享审批人';
    ELSEIF @tmp_approvestatus='审批中' THEN
        INSERT INTO ld_tabshare(crmid,viewuserid,Shareuserid,createdtime,viewuserremark)
        SELECT
            @tmp_billid,
            b.id,
            1,
            NOW(),
            '自动共享审批人'
        FROM (
            SELECT 
                c.id
            FROM ld_users2group a
            LEFT JOIN ld_users2group b ON b.groupid=a.groupid
            LEFT JOIN ld_users c ON c.id=b.userid
            WHERE a.userid=@tmp_ownerid
                AND c.userlevel IN ('主管','经理','总监','副总')
                AND c.id>0
            UNION
            SELECT
                b.id
            FROM ld_users2group a
            LEFT JOIN ld_users b ON b.id=a.userid
            WHERE a.groupid IN (SELECT parentid FROM ld_groups WHERE groupid IN (SELECT groupid FROM ld_users2group WHERE userid=@tmp_ownerid))
                AND b.userlevel IN ('总监','副总')
                AND b.id>0
            UNION
            SELECT
                a.id
            FROM ld_users a
            LEFT JOIN ld_profile b ON b.profileid=a.profileid
            WHERE a.deleted=0
                AND a.`status`='Active'
                AND b.profilename IN('财务经理','总经理','董事长')
            UNION
            SELECT
                substring_index(substring_index(a.team_memberid,',', b.seq+1 ),',',- 1) AS id
            FROM (
                SELECT
                    CASE
                        WHEN b.cf_7777='上海仓' THEN (SELECT approvevalue FROM ld_approvestep WHERE id=1998)
                        ELSE (SELECT approvevalue FROM ld_approvestep WHERE id=2001)
                    END AS team_memberid
                FROM ld_salesorder a
                LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
                WHERE a.salesorderid=@tmp_billid
            ) a
            LEFT JOIN sequence b ON b.seq<(length(a.team_memberid)-length(REPLACE(a.team_memberid,',',''))+1) 
            INNER JOIN ld_users u ON u.id=substring_index(substring_index(a.team_memberid,',', b.seq+1 ),',',- 1)
            WHERE u.deleted=0
            UNION
            SELECT smcreatorid FROM ld_salesorder WHERE salesorderid=@tmp_billid
            UNION
            SELECT 
                substring_index(substring_index(a.nextstepapproveby,',', b.seq+1 ),',',- 1) AS id
            FROM ld_approvelog a
            LEFT JOIN sequence b ON b.seq<(length(a.nextstepapproveby)-length(REPLACE(a.nextstepapproveby,',',''))+1) 
            WHERE a.crmid=@tmp_billid
                AND a.id>=(SELECT MAX(id) FROM ld_approvelog WHERE crmid=@tmp_billid AND stepid=-1)
                AND a.nextstepapproveby<>-1
        ) a
        INNER JOIN ld_users b ON b.id=a.id
        WHERE b.deleted=0
            AND b.status='Active'
            AND NOT EXISTS (SELECT 1 FROM ld_tabshare WHERE crmid=@tmp_billid AND viewuserid=b.id);
    ELSEIF @tmp_approvestatus IN ('已批准','已拒绝') THEN
        DELETE FROM ld_tabshare WHERE crmid=@tmp_billid AND viewuserremark='自动共享审批人';
        SELECT
            @tmp_billid,
            b.id,
            1,
            NOW(),
            '自动共享审批人'
        FROM (
            SELECT smcreatorid AS id FROM ld_salesorder WHERE salesorderid=@tmp_billid
            UNION
            SELECT 
                substring_index(substring_index(a.nextstepapproveby,',', b.seq+1 ),',',- 1) AS id
            FROM ld_approvelog a
            LEFT JOIN sequence b ON b.seq<(length(a.nextstepapproveby)-length(REPLACE(a.nextstepapproveby,',',''))+1) 
            WHERE a.crmid=@tmp_billid
                AND a.id>=(SELECT MAX(id) FROM ld_approvelog WHERE crmid=@tmp_billid AND stepid=-1)
                AND a.nextstepapproveby<>-1
        ) a
        INNER JOIN ld_users b ON b.id=a.id
        WHERE b.deleted=0
            AND b.status='Active'
            AND NOT EXISTS (SELECT 1 FROM ld_tabshare WHERE crmid=@tmp_billid AND viewuserid=b.id);

    END IF;

end ||
delimiter;

DROP TRIGGER IF EXISTS `trigger_after_salesorder_update`;
DELIMITER ;;
CREATE TRIGGER `trigger_after_salesorder_update` AFTER UPDATE ON `ld_salesorder` FOR EACH ROW BEGIN

    IF @disable_triggers IS NULL THEN
        IF IFNULL(NEW.approvetime,'')<>IFNULL(OLD.approvetime,'') THEN
            CALL yinghua_auto_share_order(NEW.salesorderid); 
        END IF;
    END IF;

END
;;
DELIMITER ;

SELECT CONCAT('CALL yinghua_auto_share_order( ',salesorderid,');') AS mysql FROM ld_salesorder WHERE deleted=0;

订单自动共享审批人.sql