一、应用场景
1.1、销售发票的关联流程：销售订单关联出库单，出库单再关联销售发票时，将开票总额反写到订单上
1.2、收款单的关联流程：销售订单关联出库单，出库单关联销售发票，销售发票关联收款计划，收款计划再关联收款单时，将已收总额反写到订单上
二、demo脚本
DROP PROCEDURE IF EXISTS `yingdian_reset_orderinfo`;
delimiter ||
CREATE PROCEDURE `yingdian_reset_orderinfo`(
)
BEGIN
SET @disable_triggers=1;
UPDATE ld_salesorder a
LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
LEFT JOIN (
SELECT
b.id,
SUM(c.tax_amount) AS amount_fp
FROM ld_salesorderdetail b
LEFT JOIN (
SELECT
b.parent_lineitem_id,
SUM(c.tax_amount) AS tax_amount
FROM ld_outboundorder a
LEFT JOIN ld_outboundorderdetail b ON b.id=a.out_id
LEFT JOIN (
SELECT
SUM(b.tax_amount) AS tax_amount,
b.parent_lineitem_id
FROM ld_invoice a
LEFT JOIN ld_invoicedetail b ON b.id=a.invoiceid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
GROUP BY b.id
) c ON c.id=a.salesorderid
SET
b.cf_8388=c.amount_fp,
b.cf_8389=a.total-IFNULL(c.amount_fp,0)
WHERE IFNULL(b.cf_8388,0)<>IFNULL(c.amount_fp,0)
OR IFNULL(b.cf_8389,0)<>a.total-IFNULL(c.amount_fp,0);
UPDATE ld_salesorder a
LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
LEFT JOIN (
SELECT
b.id,
SUM(c.tax_amount) AS amount_sk
FROM ld_salesorderdetail b
LEFT JOIN (
SELECT
SUM(c.tax_amount) AS tax_amount,
b.parent_lineitem_id
FROM ld_outboundorder a
LEFT JOIN ld_outboundorderdetail b ON b.id=a.out_id
LEFT JOIN (
SELECT
SUM(c.tax_amount) AS tax_amount,
b.parent_lineitem_id
FROM ld_invoice a
LEFT JOIN ld_invoicedetail b ON b.id=a.invoiceid
LEFT JOIN (
SELECT
SUM(c.tax_amount) AS tax_amount,
b.parent_lineitem_id
FROM ld_customform16 a
LEFT JOIN ld_customform16detail b ON b.id=a.customform16id
LEFT JOIN (
SELECT
SUM(b.tax_amount) AS tax_amount,
b.parent_lineitem_id
FROM ld_cashbill a
LEFT JOIN ld_cashbill2paymentplan b ON b.CashBillID=a.cashbillid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
AND b.entitytype='CustomForm16'
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND b.parent_lineitem_id>0
GROUP BY b.parent_lineitem_id
) c ON c.parent_lineitem_id=b.lineitem_id
GROUP BY b.id
) c ON c.id=a.salesorderid
SET
b.cf_8391=IFNULL(c.amount_sk,0)+IFNULL(cf_8396,0),
b.cf_8390=IFNULL(a.total,0)-IFNULL(c.amount_sk,0)-IFNULL(cf_8396,0)
WHERE IFNULL(b.cf_8391,0)<>IFNULL(c.amount_sk,0)+IFNULL(cf_8396,0)
OR IFNULL(b.cf_8390,0)<>IFNULL(a.total,0)-IFNULL(c.amount_sk,0)-IFNULL(cf_8396,0);
SET @disable_triggers=NULL;
end ||
delimiter;
CALL yingdian_reset_orderinfo();
DROP EVENT IF EXISTS `yingdian_reset_orderinfo_event`;
delimiter ||
CREATE EVENT `yingdian_reset_orderinfo_event` ON SCHEDULE EVERY 30 MINUTE STARTS NOW() ON COMPLETION NOT PRESERVE ENABLE DO
CALL yingdian_reset_orderinfo();
||
delimiter;