/**
|
* 生产任务「生产时长(h)」补录生成器（只读快照 -> 补录 SQL）
|
*
|
* 目标库：36.213.150.237:10000/mom-hm
|
* 表：mes_pro_task.duration
|
*
|
* 口径：完全复刻后端 MesProTaskServiceImpl#calculateDuration
|
* - 取任务工作站绑定的设备（mes_md_workstation_machine）
|
* - 总效率 = Σ(生产设备.production_efficiency × max(conveyor_belt,1))；效率为空按 0、皮带为空按 1
|
* - duration = 无设备或总效率≤0 ? 0 : CEIL(排产数量 / 总效率) （效率单位为每小时产量）
|
*/
|
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 num = (v) => (v === '\\N' || v === '' || v === undefined ? null : Number(v));
|
|
// ---- 快照 ----
|
const tasks = lines('task.tsv').map((c) => ({ id: Number(c[0]), wsId: num(c[1]), qty: num(c[2]) }));
|
const wsm = lines('wsm.tsv').map((c) => ({ wsId: Number(c[0]), machineryId: Number(c[1]) }));
|
const machMap = new Map(lines('machinery.tsv').map((c) => [Number(c[0]), { eff: num(c[1]), belt: num(c[2]) }]));
|
|
// 工位 -> 绑定的设备 id 列表
|
const wsMachines = new Map();
|
for (const w of wsm) {
|
if (!wsMachines.has(w.wsId)) wsMachines.set(w.wsId, []);
|
wsMachines.get(w.wsId).push(w.machineryId);
|
}
|
|
function calcDuration(wsId, qty) {
|
if (qty === null || qty <= 0) return 0;
|
const ids = wsMachines.get(wsId);
|
if (!ids || ids.length === 0) return 0;
|
let totalEff = 0;
|
for (const mid of ids) {
|
const m = machMap.get(mid);
|
if (!m) continue; // 设备不存在（被删除）不计
|
const eff = m.eff ?? 0;
|
const belt = m.belt !== null && m.belt > 0 ? m.belt : 1;
|
totalEff += eff * belt;
|
}
|
if (totalEff <= 0) return 0;
|
return Math.ceil(qty / totalEff);
|
}
|
|
// ---- 生成 ----
|
const rows = [];
|
for (const t of tasks) {
|
rows.push({ id: t.id, wsId: t.wsId, qty: t.qty, duration: calcDuration(t.wsId, t.qty) });
|
}
|
|
const zero = rows.filter((r) => r.duration === 0).length;
|
const nonZero = rows.length - zero;
|
const dist = {};
|
for (const r of rows) dist[r.duration] = (dist[r.duration] ?? 0) + 1;
|
|
// ---- 输出 SQL ----
|
const out = [];
|
out.push(`-- 生产任务 生产时长(h) 补录 | 目标库 mom-hm | mes_pro_task.duration`);
|
out.push(`-- 口径：CEIL(排产数量 / 工作站总效率)，复刻后端 calculateDuration`);
|
out.push(`-- 回滚：UPDATE mes_pro_task SET duration=NULL WHERE id IN (${rows.map((r) => r.id).join(',')});`);
|
out.push('START TRANSACTION;');
|
for (let i = 0; i < rows.length; i += 200) {
|
const chunk = rows.slice(i, i + 200);
|
out.push(
|
`UPDATE mes_pro_task SET duration = CASE id\n` +
|
chunk.map((r) => ` WHEN ${r.id} THEN ${r.duration}`).join('\n') +
|
`\n ELSE duration END WHERE id IN (${chunk.map((r) => r.id).join(',')});`,
|
);
|
}
|
out.push('COMMIT;');
|
|
fs.writeFileSync(path.join(DIR, 'duration-executed.sql'), out.join('\n'), 'utf8');
|
fs.writeFileSync(
|
path.join(DIR, 'duration-plan.json'),
|
JSON.stringify({ total: rows.length, zero, nonZero, dist, rows }, null, 1),
|
'utf8',
|
);
|
console.log('tasks:', rows.length, 'zero:', zero, 'nonZero:', nonZero);
|
console.log('dist:', JSON.stringify(dist));
|
