重算银行账户余额

一、demo脚本

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

UPDATE ld_bankaccountset a
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.Total) AS total
    FROM ld_cashbill a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        AND a.approvestatus<>'已拒绝'
        AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
    GROUP BY a.bankaccountsetid
) sk ON sk.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.inaccount,
        SUM(a.cashmoney) AS total
    FROM ld_cashdeposit a
    WHERE a.deleted=0
        AND a.inaccount>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.inaccount
) cx_in ON cx_in.inaccount=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.inaccount,
        SUM(a.cashmoney) AS total
    FROM ld_putcash a
    WHERE a.deleted=0
        AND a.inaccount>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.inaccount
) tx_in ON tx_in.inaccount=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.total) AS total
    FROM ld_purchasecashback a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        -- AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
    GROUP BY a.bankaccountsetid
) tk_cg ON tk_cg.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.Total) AS total
    FROM ld_sendcashbill a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        AND a.approvestatus<>'已拒绝'
        AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
    GROUP BY a.bankaccountsetid
) fk ON fk.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.Outaccount,
        SUM(a.cashmoney) AS total
    FROM ld_cashdeposit a
    WHERE a.deleted=0
        AND a.inaccount>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.Outaccount
) cx_out ON cx_out.Outaccount=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.Outaccount,
        SUM(a.cashmoney) AS total
    FROM ld_putcash a
    WHERE a.deleted=0
        AND a.Outaccount>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.inaccount
) tx_out ON tx_out.Outaccount=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.total) AS total
    FROM ld_cashback a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        -- AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
    GROUP BY a.bankaccountsetid
) tk_xs ON tk_xs.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.Total) AS total
    FROM ld_expense a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.bankaccountsetid
) fy ON fy.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
    SELECT
        a.bankaccountsetid,
        SUM(a.Total) AS total
    FROM ld_commission a
    WHERE a.deleted=0
        AND a.bankaccountsetid>0
        AND a.approvestatus<>'已拒绝'
        AND a.approvestatus='已批准'
    GROUP BY a.bankaccountsetid
) yj ON yj.bankaccountsetid=a.Bankaccountsetid
SET
    a.curbalancemoney=IFNULL(a.InitMoney,0)+IFNULL(sk.total,0)+IFNULL(cx_in.total,0)+IFNULL(tx_in.total,0)+IFNULL(tk_cg.total,0)-IFNULL(fk.total,0)-IFNULL(cx_out.total,0)-IFNULL(tx_out.total,0)-IFNULL(tk_xs.total,0)-IFNULL(fy.total,0)-IFNULL(yj.total,0)
WHERE IFNULL(a.curbalancemoney,0)<>IFNULL(a.InitMoney,0)+IFNULL(sk.total,0)+IFNULL(cx_in.total,0)+IFNULL(tx_in.total,0)+IFNULL(tk_cg.total,0)-IFNULL(fk.total,0)-IFNULL(cx_out.total,0)-IFNULL(tx_out.total,0)-IFNULL(tk_xs.total,0)-IFNULL(fy.total,0)-IFNULL(yj.total,0);

END ||
delimiter;

CALL ldcrm_reset_bankaccount_balancemoney();

SET GLOBAL event_scheduler=ON;
DROP EVENT IF EXISTS `ldcrm_reset_bankaccount_balancemoney_event`;
delimiter ||
CREATE  EVENT `ldcrm_reset_bankaccount_balancemoney_event` ON SCHEDULE EVERY 10 MINUTE STARTS '2023-01-04 10:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 
CALL ldcrm_reset_bankaccount_balancemoney();
||
delimiter;

重算银行账户余额.sql