重算审批中心数据

一、demo脚本

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

    DECLARE tmp_tabname VARCHAR(100) DEFAULT '';
    DECLARE MYCUSOR CURSOR FOR 
                    SELECT
                        b.setype
                    FROM (SELECT crmid FROM ld_approvelog GROUP BY crmid) a
                    INNER JOIN ld_crmentity b ON b.crmid=a.crmid
                    WHERE IFNULL(b.setype,'') NOT IN ('','SalesOrder','Quotes','ServiceContracts','Reminds')
                    GROUP BY b.setype;

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

    OPEN MYCUSOR; 
    FETCH MYCUSOR INTO tmp_tabname;
    WHILE(tmp_tabname IS NOT NULL) DO
        SET @this_tablename=(SELECT tablename FROM ld_tab a INNER JOIN ld_field b ON b.tabid=a.tabid WHERE a.`name`=tmp_tabname AND b.columnname='smownerid');
        SET @this_tableid=(SELECT entityidfield FROM ld_entityname WHERE modulename=tmp_tabname LIMIT 1);

        SET @this_sql_01="UPDATE ld_servicecontracts
SET 
    currentapprover=NULL,
    currentapprovestepid=NULL
WHERE deleted=0
    AND approvestatus IN ('未提交','已驳回','已批准')";

        SET @this_sql_02="UPDATE ld_servicecontracts
SET 
    currentapprover=(
        CASE
            WHEN EXISTS (SELECT 1 FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid AND isfinished='no') THEN
                (SELECT GROUP_CONCAT(nextstepapproveby) FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid AND isfinished='no')
            ELSE
                (SELECT nextstepapproveby FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid ORDER BY seqtime DESC,id DESC LIMIT 1)
        END),
    currentapprovestepid=(
        CASE
            WHEN EXISTS (SELECT 1 FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid AND isfinished='no') THEN
                (SELECT nextstepid FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid AND isfinished='no' AND nextstepid>0 ORDER BY id DESC LIMIT 1)
            ELSE
                (SELECT nextstepid FROM ld_approvelog WHERE crmid=ld_servicecontracts.servicecontractsid AND nextstepid>0 ORDER BY seqtime DESC,id DESC LIMIT 1)
        END)
WHERE deleted=0
    AND approvestatus IN ('审批中')";

        SET @this_execsql_01=REPLACE(@this_sql_01,'ld_servicecontracts',@this_tablename);
        SET @this_execsql_01=REPLACE(@this_execsql_01,'servicecontractsid',@this_tableid);
        SET @this_execsql_02=REPLACE(@this_sql_02,'ld_servicecontracts',@this_tablename);
        SET @this_execsql_02=REPLACE(@this_execsql_02,'servicecontractsid',@this_tableid);

        SET @disable_triggers=1;
        PREPARE this_stmt FROM @this_execsql_01;
        EXECUTE this_stmt;
        DEALLOCATE PREPARE this_stmt;

        PREPARE this_stmt FROM @this_execsql_02;
        EXECUTE this_stmt;
        DEALLOCATE PREPARE this_stmt;
        SET @disable_triggers=NULL;

        FETCH MYCUSOR INTO tmp_tabname;
    END WHILE;
    CLOSE MYCUSOR;

END ||
delimiter;

CALL reset_bill_approveinfo_20210120();
DROP PROCEDURE IF EXISTS `reset_bill_approveinfo_20210120`;
delimiter;

重算审批中心数据.sql