/**
|
* 生产流转卡数据补录生成器（只读快照 -> 补录 SQL）
|
*
|
* 目标库：36.213.150.237:10000/mom-hm
|
* 依据：生产报工记录（mes_pro_feedback）
|
*
|
* 规则（已与用户确认）：
|
* - 范围：工单 30-101 中全部有报工的 60 个工单
|
* - 每个工单 1 张流转卡（mes_pro_card），单据状态 = 已完成(2)
|
* - 每张卡的工序记录（mes_pro_card_process）按工序顺序逐条来自报工：
|
* 进入时间 = 上道工序出工序时间（首道 = 本序报工时间）
|
* 出工序时间 = 报工时间
|
* 投入/产出/不合格 = 按工单工序收率倒推（口径见 docs/流转卡投入产出按收率重算方案.md）：
|
* 产出(pᵢ) = CEIL( Q / (rᵢ₊₁ × … × rₙ) ) -- 与生产任务数量同源
|
* 投入(p₁) = CEIL( 产出(p₁) / r₁ ) -- 首道
|
* 投入(pᵢ) = 产出(pᵢ₋₁) -- i ≥ 2
|
* 不合格品(pᵢ) = 0 -- 固定为 0
|
* 工位/操作人 = 报工工位/报工人
|
* - 批次号：优先取工单 batch_code；老工单为空时取末道工序产出行批次号
|
*/
|
import fs from 'node:fs';
|
import path from 'node:path';
|
import { fileURLToPath } from 'node:url';
|
|
const DIR = path.dirname(fileURLToPath(import.meta.url));
|
const lines = (f) =>
|
fs.readFileSync(path.join(DIR, f), 'utf8').trim().split('\n').filter(Boolean).map((l) => l.split('\t'));
|
const n = (v) => (v === '\\N' || v === '' || v === undefined ? null : Number(v));
|
const s = (v) => (v === '\\N' || v === undefined ? null : v);
|
|
// ---- 常量 ----
|
const STATUS_FINISHED = 2; // MesProWorkOrderStatusEnum.FINISHED
|
const CREATOR = '1';
|
const BIZ = 'pro-card-backfill-20261015';
|
|
// ---- 快照 ----
|
const woMap = new Map(lines('wo.tsv').map((c) => [Number(c[0]), { productId: n(c[1]), qty: n(c[2]), batchCode: s(c[3]), status: n(c[4]) }]));
|
// 工单工序配置：sort + 收率（wop.tsv = work_order_id, process_id, sort, yield_rate），按 sort 升序
|
const wopByWo = new Map();
|
for (const c of lines('wop.tsv')) {
|
const woId = Number(c[0]);
|
if (!wopByWo.has(woId)) wopByWo.set(woId, []);
|
wopByWo.get(woId).push({ processId: Number(c[1]), sort: n(c[2]), yieldRate: n(c[3]) });
|
}
|
for (const arr of wopByWo.values()) arr.sort((a, b) => (a.sort ?? 0) - (b.sort ?? 0));
|
const sortMap = new Map(lines('wop.tsv').map((c) => [`${c[0]}#${c[1]}`, n(c[2])]));
|
// 末道工序产出行批次（按 process sort 取最大）
|
const lastBatch = new Map();
|
for (const c of lines('prodline.tsv')) {
|
const woId = Number(c[0]);
|
const sort = n(c[2]) ?? 0;
|
const cur = lastBatch.get(woId);
|
if (!cur || sort > cur.sort) lastBatch.set(woId, { sort, batchCode: s(c[3]) });
|
}
|
const feedbacks = lines('feedback.tsv').map((c) => ({
|
id: Number(c[0]), woId: Number(c[1]), processId: Number(c[2]), time: c[3],
|
qty: n(c[4]), qUnq: n(c[6]), workstationId: n(c[7]), userId: n(c[8]),
|
}));
|
|
const byWo = new Map();
|
for (const f of feedbacks) {
|
if (!byWo.has(f.woId)) byWo.set(f.woId, []);
|
byWo.get(f.woId).push(f);
|
}
|
|
// ---- 工具 ----
|
const q = (v) => (v === null || v === undefined ? 'NULL' : `'${String(v).replaceAll("'", "''")}'`);
|
const num = (v) => (v === null || v === undefined ? 'NULL' : Number(v).toFixed(6));
|
const dt = (v) => (v === null || v === undefined ? 'NULL' : q(v));
|
|
// 按收率倒推各道产出量：产出(pᵢ) = CEIL( Q / (rᵢ₊₁ × … × rₙ) )，与生产任务/流转卡后端口径一致
|
function calcOutputs(qty, wops) {
|
const outputs = new Array(wops.length);
|
let cum = 1;
|
for (let i = wops.length - 1; i >= 0; i--) {
|
outputs[i] = Math.ceil(qty / cum);
|
const y = wops[i].yieldRate;
|
if (y != null && y > 0) cum *= y;
|
}
|
return outputs;
|
}
|
|
// ---- 卡号：PC_<yyyyMMdd><3位序号> ----
|
const dateSeq = new Map();
|
function genCardCode(time) {
|
const d = String(time).slice(0, 10).replaceAll('-', '');
|
const seq = (dateSeq.get(d) ?? 0) + 1;
|
dateSeq.set(d, seq);
|
return `PC_${d}${String(seq).padStart(3, '0')}`;
|
}
|
|
const cards = [];
|
const cardProcesses = [];
|
const plan = [];
|
let cardId = 0;
|
let cpId = 0;
|
|
for (const woId of [...byWo.keys()].sort((a, b) => a - b)) {
|
const wo = woMap.get(woId);
|
if (!wo) continue;
|
const list = byWo.get(woId).sort((a, b) => {
|
const sa = sortMap.get(`${woId}#${a.processId}`) ?? 9_999_999;
|
const sb = sortMap.get(`${woId}#${b.processId}`) ?? 9_999_999;
|
return sa - sb || a.id - b.id;
|
});
|
|
const lastTime = list[list.length - 1].time;
|
const batchCode = wo.batchCode || lastBatch.get(woId)?.batchCode || null;
|
const id = ++cardId;
|
const code = genCardCode(lastTime);
|
cards.push(`(${id},${q(code)},${woId},${wo.productId},${q(batchCode)},${num(wo.qty)},${STATUS_FINISHED},NULL,${q(CREATOR)},${dt(lastTime)},${q(CREATOR)},${dt(lastTime)},b'0')`);
|
|
const wops = wopByWo.get(woId) ?? [];
|
const outputs = wops.length && wo.qty != null ? calcOutputs(wo.qty, wops) : [];
|
const outByProcess = new Map(wops.map((w, i) => [w.processId, outputs[i]]));
|
const yieldByProcess = new Map(wops.map((w) => [w.processId, w.yieldRate]));
|
|
let prev = null;
|
let prevOutput = null;
|
list.forEach((f, idx) => {
|
const sort = sortMap.get(`${woId}#${f.processId}`) ?? (idx + 1) * 10;
|
const inputTime = prev ? prev.time : f.time;
|
const outputQty = outByProcess.get(f.processId) ?? f.qty;
|
let inputQty;
|
if (prevOutput !== null) {
|
inputQty = prevOutput; // i ≥ 2：本道投入 = 上道产出
|
} else {
|
const y = yieldByProcess.get(f.processId); // 首道：投入 = 产出 / 收率，向上取整
|
inputQty = y != null && y > 0 ? Math.ceil(outputQty / y) : outputQty;
|
}
|
const unq = 0; // 不合格品数量固定为 0（2026-10-15 按用户要求）
|
const cpid = ++cpId;
|
cardProcesses.push(
|
`(${cpid},${id},${sort},${f.processId},${dt(inputTime)},${dt(f.time)},${num(inputQty)},${num(outputQty)},${num(unq)},${f.workstationId ?? 'NULL'},${f.userId ?? 'NULL'},NULL,NULL,${q(CREATOR)},${dt(f.time)},${q(CREATOR)},${dt(f.time)},b'0')`,
|
);
|
plan.push({ woId, code, sort, processId: f.processId, inputTime, outputTime: f.time, inputQty, outputQty, unq });
|
prev = f;
|
prevOutput = outputQty;
|
});
|
}
|
|
// ---- 输出 SQL ----
|
const out = [];
|
out.push(`-- 生产流转卡数据补录 | 目标库 mom-hm | ${BIZ}`);
|
out.push('-- 回滚：表内原本为空，DELETE FROM mes_pro_card; DELETE FROM mes_pro_card_process;');
|
out.push('START TRANSACTION;');
|
out.push(`\n-- mes_pro_card : ${cards.length} 行`);
|
out.push(
|
`INSERT INTO mes_pro_card (id,code,work_order_id,item_id,batch_code,transfered_quantity,status,remark,creator,create_time,updater,update_time,deleted) VALUES\n${cards.join(',\n')};`,
|
);
|
out.push(`\n-- mes_pro_card_process : ${cardProcesses.length} 行`);
|
for (let i = 0; i < cardProcesses.length; i += 200) {
|
out.push(
|
`INSERT INTO mes_pro_card_process (id,card_id,sort,process_id,input_time,output_time,input_quantity,output_quantity,unqualified_quantity,workstation_id,user_id,ipqc_id,remark,creator,create_time,updater,update_time,deleted) VALUES\n${cardProcesses.slice(i, i + 200).join(',\n')};`,
|
);
|
}
|
out.push('\nCOMMIT;');
|
|
fs.writeFileSync(path.join(DIR, 'card-executed.sql'), out.join('\n'), 'utf8');
|
fs.writeFileSync(path.join(DIR, 'card-plan.json'), JSON.stringify({ cardCount: cards.length, processCount: cardProcesses.length, plan }, null, 1), 'utf8');
|
console.log('cards:', cards.length, 'processes:', cardProcesses.length);
|
