一、demo脚本
UPDATE ld_products p
LEFT JOIN (
SELECT
a.wareid,
a.productid,
a.batchtext,
SUM(a.inventory_qty) AS inventory_qty
FROM (
SELECT
a.warehouseid AS wareid,
a.productid,
a.batchtext,
a.lastqty AS inventory_qty
FROM ld_warehousecondtions a
LEFT JOIN ld_products c ON c.productid=a.productid
WHERE a.modulename='库存初始化'
AND a.type='确认初始库存'
UNION ALL
SELECT
a.wareid,
c.productid,
c.batchtext,
c.quantity
FROM ld_stock a
LEFT JOIN ld_stockdetail c ON c.id=a.stock_id
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.approvestatus='已批准'
END)
UNION ALL
SELECT
a.wareid,
c.productid,
c.batchtext,
c.quantity
FROM ld_salesback a
LEFT JOIN ld_salesbackdetail c ON c.id=a.salesbackid
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND a.salesbackstatus='已入库'
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.approvestatus='已批准'
END)
UNION ALL
SELECT
c.wareid,
c.productid,
c.batchtext,
0-c.quantity
FROM ld_outboundorder a
LEFT JOIN ld_outboundordercf b ON a.out_id=b.out_id
LEFT JOIN ld_outboundorderdetail c ON c.id=a.out_id
LEFT JOIN ld_outboundorderdetailscf d ON d.lineitem_id=c.lineitem_id
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND a.approvestatus<>'已拒绝'
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.approvestatus='已批准'
END)
UNION ALL
SELECT
a.wareid,
c.productid,
c.batchtext,
0-c.quantity
FROM ld_purchaseback a
LEFT JOIN ld_purchasebackdetail c ON c.id=a.purchasebackid
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND a.purchasebackstatus='已出库'
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.purchasebackstatus='结束'
END)
UNION ALL
SELECT
a.inwarehouse,
c.productid,
c.batchtext,
c.quantity
FROM ld_warehousetransfer a
LEFT JOIN ld_warehousetransferdetail c ON c.id=a.warehousetransferid
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.transferstatus='已审核'
END)
UNION ALL
SELECT
a.outwarehouse,
c.productid,
c.batchtext,
0-c.quantity
FROM ld_warehousetransfer a
LEFT JOIN ld_warehousetransferdetail c ON c.id=a.warehousetransferid
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.transferstatus='已审核'
END)
UNION ALL
SELECT
a.wareid,
c.productid,
c.batchtext,
c.profitloss
FROM ld_checks a
LEFT JOIN ld_checksdetail c ON c.id=a.id
LEFT JOIN ld_products p ON p.productid=c.productid
WHERE a.deleted=0
AND (
CASE
WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=48 AND deploy_value=1) THEN 1=1
ELSE a.presence='盘点结束'
END)
) a
WHERE a.productid>0
GROUP BY a.productid
) a ON a.productid=p.productid
SET p.qtyinstock=IFNULL(a.inventory_qty,0)
WHERE p.deleted=0
AND IFNULL(p.qtyinstock,0)<>IFNULL(a.inventory_qty,0);
/* 算法
库存初始化数量（确认初始化，不计暂存）
+ 入库单数量（根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
+ 销售退货单数量（单据状态不等于“中止”，根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
- 出库单数量（根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
- 采购退货单数量（单据状态不等于“中止”，根据库存流水记录触发点判断统计“全部”单据还是“退货状态”为“结束”的单据）
+ 入库仓库调拨单数量（根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
- 出库仓库调拨单数量（根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
+ 盘点单盈亏数量（根据库存流水记录触发点判断统计“全部”单据还是“审批状态”为“已批准”的单据）
*/