-- 流转卡工序记录 投入/产出/不合格 按工序收率重算 | 目标库 mom-hm | 2026-10-15
|
--
|
-- 口径（与 docs/流转卡投入产出按收率重算方案.md 一致，与生产任务数量同源）：
|
-- 产出(pᵢ) = CEIL( Q / (rᵢ₊₁ × … × rₙ) )
|
-- 投入(p₁) = CEIL( 产出(p₁) / r₁ ) -- 首道
|
-- 投入(pᵢ) = 产出(pᵢ₋₁) -- i ≥ 2，取 sort 升序上一道产出
|
-- 不合格品(pᵢ) = 投入(pᵢ) - 产出(pᵢ)
|
--
|
-- 备份（重算前）：
|
-- SELECT id,card_id,sort,process_id,input_quantity,output_quantity,unqualified_quantity
|
-- FROM mes_pro_card_process WHERE deleted=0 ORDER BY card_id,sort; -> task-before.tsv
|
--
|
-- 说明：因 MySQL 不允许 UPDATE 与自身子查询同时出现（errno 1093），用临时表暂存重算结果。
|
|
CREATE TEMPORARY TABLE tmp_card_recalc AS
|
SELECT t.cp_id,
|
t.out_qty,
|
COALESCE(LAG(t.out_qty) OVER (PARTITION BY t.work_order_id ORDER BY t.sort ASC),
|
CEIL(t.out_qty / COALESCE(t.yield_rate, 1))) AS in_qty
|
FROM (
|
SELECT cp2.id AS cp_id, c.work_order_id, cp2.sort, wop.yield_rate,
|
CEIL(wo.quantity / COALESCE(EXP(SUM(LN(COALESCE(wop.yield_rate, 1)))
|
OVER (PARTITION BY c.work_order_id ORDER BY cp2.sort DESC
|
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1)) AS out_qty
|
FROM mes_pro_card_process cp2
|
JOIN mes_pro_card c ON c.id = cp2.card_id AND c.deleted = 0
|
JOIN mes_pro_work_order wo ON wo.id = c.work_order_id
|
JOIN mes_pro_work_order_process wop
|
ON wop.work_order_id = c.work_order_id AND wop.process_id = cp2.process_id AND wop.deleted = 0
|
WHERE cp2.deleted = 0
|
) t;
|
|
UPDATE mes_pro_card_process cp
|
JOIN tmp_card_recalc x ON x.cp_id = cp.id
|
SET cp.input_quantity = x.in_qty,
|
cp.output_quantity = x.out_qty,
|
cp.unqualified_quantity = x.in_qty - x.out_qty;
|
|
DROP TEMPORARY TABLE tmp_card_recalc;
|
