/**
|
* 工序流转数据补录生成器（只读快照 -> 补录 SQL）
|
*
|
* 目标库：36.213.150.237:10000/mom-hm
|
* 依据：生产报工记录（mes_pro_feedback）
|
*
|
* 口径（已与用户确认）：
|
* - 只补单据 + 批次，不写 mes_wm_transaction、不改 stock
|
* - 投入 = 工序间 WIP 流转 + 有 BOM 时按 BOM 补原料投料
|
*
|
* 规则：
|
* - 对每个老工单，按工序 sort 升序处理其全部报工
|
* - 末道工序：迁移已插入产出单(批次号 SC…)，复用其批次，只补消耗单
|
* - 其余工序：生成 1 个新批次 + 1 张产出单(头/行/明细，状态=已完成，质量=合格)
|
* - 消耗单：工序间消耗上一道工序产出的批次(WIP)；再有 BOM 时按 BOM 行补原料投料(线边库 FIFO)
|
* - 无任何消耗行时，不生成消耗单头（与代码 generateItemConsume 行为一致）
|
*/
|
import fs from 'node:fs';
|
import path from 'node:path';
|
import { fileURLToPath } from 'node:url';
|
|
const DIR = path.dirname(fileURLToPath(import.meta.url));
|
const read = (f) => JSON.parse(fs.readFileSync(path.join(DIR, f), 'utf8'));
|
|
const meta = read('before-meta.json');
|
const workOrders = read('before-workorders.json');
|
const feedbacks = read('before-feedbacks.json');
|
const woProcess = read('before-wo-process.json');
|
const boms = read('before-bom.json');
|
const wipStock = read('before-wip-stock.json') || [];
|
const existingCodes = new Set(read('before-batch-codes.json'));
|
// 老工单现有产出单（迁移已插入：每个工单仅"末道工序"1 条），复用其批次，避免重复产出
|
const existingProduce = read('before-existing-produce.json');
|
|
// ---- 常量 ----
|
const WIP_WAREHOUSE = 23;
|
const WIP_LOCATION = 8;
|
const WIP_AREA = 8;
|
const STATUS_FINISHED = 4;
|
const QUALITY_PASS = 1;
|
const CREATOR = '1';
|
const BIZ = 'backfill-20261015';
|
|
// ---- 索引 ----
|
const woMap = new Map(workOrders.map((w) => [w.woId, w]));
|
const sortMap = new Map(woProcess.map((p) => [`${p.woId}#${p.processId}`, p.sort]));
|
const bomMap = new Map();
|
for (const b of boms) {
|
const k = `${b.woId}#${b.processId}`;
|
if (!bomMap.has(k)) bomMap.set(k, []);
|
bomMap.get(k).push(b);
|
}
|
const existingProduceMap = new Map(existingProduce.map((p) => [p.feedbackId, p]));
|
|
// 线边库按物料分组，FIFO（receipt_time 升序）
|
const stockMap = new Map();
|
for (const s of wipStock) {
|
if (!stockMap.has(s.itemId)) stockMap.set(s.itemId, []);
|
stockMap.get(s.itemId).push({ ...s });
|
}
|
for (const arr of stockMap.values()) {
|
arr.sort((a, b) => String(a.receiptTime).localeCompare(String(b.receiptTime)));
|
}
|
|
// ---- ID 分配器 ----
|
const seq = {
|
batch: meta.max_batch,
|
produce: meta.max_produce,
|
produceLine: meta.max_produce_line,
|
produceDetail: meta.max_produce_detail,
|
consume: meta.max_consume,
|
consumeLine: meta.max_consume_line,
|
consumeDetail: meta.max_consume_detail,
|
};
|
const nextId = (k) => ++seq[k];
|
|
// ---- 批次号生成：BT_<yyyyMMdd><3位序号>，避开已有编码 ----
|
const dateSeq = new Map();
|
function genBatchCode(time) {
|
const d = String(time).slice(0, 10).replaceAll('-', '');
|
let n = dateSeq.get(d) ?? 0;
|
let code;
|
do {
|
n += 1;
|
code = `BT_${d}${String(n).padStart(3, '0')}`;
|
} while (existingCodes.has(code));
|
dateSeq.set(d, n);
|
existingCodes.add(code);
|
return code;
|
}
|
|
// ---- 工具 ----
|
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));
|
|
// ---- 生成 ----
|
const ins = {
|
batch: [],
|
produce: [],
|
produceLine: [],
|
produceDetail: [],
|
consume: [],
|
consumeLine: [],
|
consumeDetail: [],
|
};
|
const plan = [];
|
|
// 报工按工单分组
|
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 woIds = [...byWo.keys()].sort((a, b) => a - b);
|
for (const woId of woIds) {
|
const wo = woMap.get(woId) ?? {};
|
const list = byWo.get(woId);
|
// 按工序 sort 升序（sort 缺失用大值兜底），再按报工 id
|
list.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;
|
});
|
|
let prevBatch = null;
|
for (const f of list) {
|
const t = f.time;
|
// 1) 产出：老工单"末道工序"已有迁移插入的产出单，复用其批次，不重复产出；
|
// 其余工序生成新批次 + 产出单(头/行/明细，已完成、合格，入虚拟线边库)
|
let batchId;
|
let batchCode;
|
let reused = false;
|
const existing = existingProduceMap.get(f.id);
|
if (existing) {
|
batchId = existing.batchId;
|
batchCode = existing.batchCode;
|
reused = true;
|
} else {
|
batchId = nextId('batch');
|
batchCode = genBatchCode(t);
|
ins.batch.push(
|
`(${batchId},${q(batchCode)},${f.itemId},${dt(t)},${dt(f.expireDate)},${q(wo.clientId)},${q(wo.salesOrderCode)},${woId},${f.taskId},${f.workstationId},${q(f.lotNumber)},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
const produceId = nextId('produce');
|
ins.produce.push(
|
`(${produceId},${woId},${f.id},${f.taskId},${f.workstationId},${f.processId},${dt(t)},${STATUS_FINISHED},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
const lineId = nextId('produceLine');
|
ins.produceLine.push(
|
`(${lineId},${produceId},${f.id},${f.itemId},${num(f.qty)},${batchId},${q(batchCode)},${dt(f.expireDate)},${q(f.lotNumber)},${QUALITY_PASS},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
const detailId = nextId('produceDetail');
|
ins.produceDetail.push(
|
`(${detailId},${produceId},${lineId},${f.itemId},${num(f.qty)},${batchId},${q(batchCode)},${WIP_WAREHOUSE},${WIP_LOCATION},${WIP_AREA},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
}
|
|
// 5) 消耗行：BOM 原料 + 工序间 WIP
|
const consumeItems = [];
|
for (const bom of bomMap.get(`${woId}#${f.processId}`) ?? []) {
|
consumeItems.push({
|
itemId: bom.itemId,
|
qty: Number(bom.qty) * Number(f.qty),
|
allocations: allocateStock(bom.itemId, Number(bom.qty) * Number(f.qty)),
|
});
|
}
|
if (prevBatch) {
|
consumeItems.push({
|
itemId: f.itemId,
|
qty: Number(f.qty),
|
allocations: [{ batchId: prevBatch.batchId, batchCode: prevBatch.batchCode, qty: Number(f.qty) }],
|
});
|
}
|
|
if (consumeItems.length > 0) {
|
const consumeId = nextId('consume');
|
ins.consume.push(
|
`(${consumeId},${woId},${f.taskId},${f.workstationId},${f.processId},${f.id},${dt(t)},${STATUS_FINISHED},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
for (const ci of consumeItems) {
|
const cLineId = nextId('consumeLine');
|
const first = ci.allocations[0];
|
ins.consumeLine.push(
|
`(${cLineId},${consumeId},${ci.itemId},${num(ci.qty)},${first ? first.batchId : 'NULL'},${first ? q(first.batchCode) : 'NULL'},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
for (const a of ci.allocations) {
|
const cDetailId = nextId('consumeDetail');
|
ins.consumeDetail.push(
|
`(${cDetailId},${consumeId},${cLineId},${a.materialStockId ?? 'NULL'},${ci.itemId},${num(a.qty)},${a.batchId ?? 'NULL'},${a.batchCode ? q(a.batchCode) : 'NULL'},${WIP_WAREHOUSE},${WIP_LOCATION},${WIP_AREA},${q(CREATOR)},${dt(t)},${q(CREATOR)},${dt(t)},b'0')`,
|
);
|
}
|
}
|
}
|
|
plan.push({ feedbackId: f.id, woId, processId: f.processId, qty: Number(f.qty), batchId, batchCode, reused, consumeLines: consumeItems.length });
|
prevBatch = { batchId, batchCode };
|
}
|
}
|
|
// BOM 原料按线边库 FIFO 分配（全局扣减，模拟消耗）
|
function allocateStock(itemId, qty) {
|
const arr = stockMap.get(itemId);
|
if (!arr || arr.length === 0) return [{ batchId: null, batchCode: null, qty, materialStockId: null }];
|
const out = [];
|
let remaining = qty;
|
for (const s of arr) {
|
if (remaining <= 0) break;
|
const avail = Math.max(0, Number(s.qty));
|
if (avail <= 0) continue;
|
const take = Math.min(avail, remaining);
|
out.push({ batchId: s.batchId, batchCode: s.batchCode, qty: take, materialStockId: null });
|
s.qty = avail - take;
|
remaining -= take;
|
}
|
if (remaining > 0) out.push({ batchId: null, batchCode: null, qty: remaining, materialStockId: null });
|
return out;
|
}
|
|
// ---- 输出 SQL ----
|
const cols = {
|
batch: '(id,code,item_id,produce_date,expire_date,client_id,sales_order_code,work_order_id,task_id,workstation_id,lot_number,creator,create_time,updater,update_time,deleted)',
|
produce: '(id,work_order_id,feedback_id,task_id,workstation_id,process_id,produce_date,status,creator,create_time,updater,update_time,deleted)',
|
produceLine: '(id,produce_id,feedback_id,item_id,quantity,batch_id,batch_code,expire_date,lot_number,quality_status,creator,create_time,updater,update_time,deleted)',
|
produceDetail: '(id,produce_id,line_id,item_id,quantity,batch_id,batch_code,warehouse_id,location_id,area_id,creator,create_time,updater,update_time,deleted)',
|
consume: '(id,work_order_id,task_id,workstation_id,process_id,feedback_id,consume_date,status,creator,create_time,updater,update_time,deleted)',
|
consumeLine: '(id,consume_id,item_id,quantity,batch_id,batch_code,creator,create_time,updater,update_time,deleted)',
|
consumeDetail: '(id,consume_id,line_id,material_stock_id,item_id,quantity,batch_id,batch_code,warehouse_id,location_id,area_id,creator,create_time,updater,update_time,deleted)',
|
};
|
const table = {
|
batch: 'mes_wm_batch',
|
produce: 'mes_wm_product_produce',
|
produceLine: 'mes_wm_product_produce_line',
|
produceDetail: 'mes_wm_product_produce_detail',
|
consume: 'mes_wm_item_consume',
|
consumeLine: 'mes_wm_item_consume_line',
|
consumeDetail: 'mes_wm_item_consume_detail',
|
};
|
|
const lines = [];
|
lines.push(`-- 工序流转数据补录 | 目标库 mom-hm | 生成于快照 after ${BIZ}`);
|
lines.push('-- 回滚：DELETE FROM 各表 WHERE id > before-meta.json 中对应 max_* 值');
|
lines.push('START TRANSACTION;');
|
for (const k of Object.keys(ins)) {
|
const rows = ins[k];
|
if (rows.length === 0) continue;
|
lines.push(`\n-- ${table[k]} : ${rows.length} 行`);
|
for (let i = 0; i < rows.length; i += 200) {
|
lines.push(`INSERT INTO ${table[k]} ${cols[k]} VALUES\n${rows.slice(i, i + 200).join(',\n')};`);
|
}
|
}
|
lines.push('\nCOMMIT;');
|
|
fs.writeFileSync(path.join(DIR, 'backfill-executed.sql'), lines.join('\n'), 'utf8');
|
fs.writeFileSync(path.join(DIR, 'backfill-plan.json'), JSON.stringify({ meta, counts: Object.fromEntries(Object.entries(ins).map(([k, v]) => [k, v.length])), newMax: seq, plan }, null, 1), 'utf8');
|
|
console.log('rows:', Object.fromEntries(Object.entries(ins).map(([k, v]) => [k, v.length])));
|
console.log('new id max:', seq);
|
