/**
|
* 生产管理工作记录（上下工流水）补录生成器（只读快照 -> 补录 SQL）
|
*
|
* 目标库：36.213.150.237:10000/mom-hm
|
* 表：mes_pro_work_record_log
|
*
|
* 口径（已与用户确认）：
|
* - 人员：仅报工记录中出现过的 15 人（mes_pro_feedback.feedback_user_id）
|
* 工位取该人报工出现次数最多的工作站（并列取 id 小者）
|
* - 日期：2026-09-17 ~ 2026-10-15（含今天），只取工作日
|
* 工作日 = 周一~周五 且 非法定节假日（与「生产白班组」排班一致；节假日来自 mes_cal_holiday type=2）
|
* - 时间：上工 08:40~09:10、下工 17:50~18:20 之间随机（班次 09:00–18:00 小幅浮动）
|
* - creator/updater = 该员工本人 userId
|
* - 先删除既有补录行（id > 2）再写入；不维护当前绑定表 mes_pro_work_record
|
*/
|
import fs from 'node:fs';
|
import path from 'node:path';
|
import { fileURLToPath } from 'node:url';
|
|
const DIR = path.dirname(fileURLToPath(import.meta.url));
|
const readLines = (f) =>
|
fs.readFileSync(path.join(DIR, f), 'utf8').trim().split('\n').filter(Boolean).map((l) => l.split('\t'));
|
|
// ---- 常量 ----
|
const TYPE_CLOCK_IN = 1;
|
const TYPE_CLOCK_OUT = 2;
|
const MAX_EXISTING_ID = 2; // 库内既有最大 id（保留）
|
const RANGE_START = '2026-09-17';
|
const RANGE_END = '2026-10-15';
|
const IN_MIN = 8 * 60 + 40; // 08:40
|
const IN_MAX = 9 * 60 + 10; // 09:10
|
const OUT_MIN = 17 * 60 + 50; // 17:50
|
const OUT_MAX = 18 * 60 + 20; // 18:20
|
|
// ---- 快照 ----
|
// 人 -> 工位（按出现次数取众数，并列取 id 小者）
|
const wsCount = new Map(); // userId -> Map(wsId -> cnt)
|
for (const c of readLines('user-ws.tsv')) {
|
const userId = Number(c[0]);
|
const wsId = Number(c[1]);
|
const cnt = Number(c[2]);
|
if (!wsCount.has(userId)) wsCount.set(userId, new Map());
|
wsCount.get(userId).set(wsId, cnt);
|
}
|
// 分配：按报工总量从多到少排序用户，每人取"自己用过且尚未被占用"的最常用工位（并列取 id 小者）；
|
// 都被占用时退回自身最常用工位。保证 15 人尽量分散在不同工位。
|
const userWs = new Map();
|
{
|
const users = [...wsCount.keys()].sort((a, b) => {
|
const ta = [...wsCount.get(a).values()].reduce((s, v) => s + v, 0);
|
const tb = [...wsCount.get(b).values()].reduce((s, v) => s + v, 0);
|
return tb - ta || a - b;
|
});
|
const taken = new Set();
|
for (const userId of users) {
|
const cands = [...wsCount.get(userId)].sort((x, y) => y[1] - x[1] || x[0] - y[0]).map(([wsId]) => wsId);
|
const pick = cands.find((wsId) => !taken.has(wsId)) ?? cands[0];
|
taken.add(pick);
|
userWs.set(userId, pick);
|
}
|
}
|
const holidays = new Set(readLines('holidays.tsv').map((c) => c[0]));
|
|
// ---- 工作日：区间内 周一~周五 且 非节假日 ----
|
const pad = (x) => String(x).padStart(2, '0');
|
const fmt = (d) => `${d.getUTCFullYear()}-${pad(d.getUTCMonth() + 1)}-${pad(d.getUTCDate())}`;
|
const workDays = [];
|
{
|
const [y1, m1, d1] = RANGE_START.split('-').map(Number);
|
const [y2, m2, d2] = RANGE_END.split('-').map(Number);
|
const cur = new Date(Date.UTC(y1, m1 - 1, d1));
|
const end = new Date(Date.UTC(y2, m2 - 1, d2));
|
while (cur <= end) {
|
const dow = cur.getUTCDay(); // 0=Sun 6=Sat
|
const s = fmt(cur);
|
if (dow !== 0 && dow !== 6 && !holidays.has(s)) workDays.push(s);
|
cur.setUTCDate(cur.getUTCDate() + 1);
|
}
|
}
|
|
// ---- 确定性随机（mulberry32），保证可复现 ----
|
let seed = 20261015;
|
const rnd = () => {
|
seed |= 0;
|
seed = (seed + 0x6d2b79f5) | 0;
|
let t = Math.imul(seed ^ (seed >>> 15), 1 | seed);
|
t = (t + Math.imul(t ^ (t >>> 7), 61 | t)) ^ t;
|
return ((t ^ (t >>> 14)) >>> 0) / 4294967296;
|
};
|
const randMinute = (lo, hi) => lo + Math.floor(rnd() * (hi - lo + 1));
|
const toTime = (day, min) => `${day} ${pad(Math.floor(min / 60))}:${pad(min % 60)}:${pad(Math.floor(rnd() * 60))}`;
|
|
// ---- 生成行：每个工作日 × 每人 一上一下 ----
|
let id = MAX_EXISTING_ID;
|
const rows = [];
|
const plan = [];
|
for (const day of workDays) {
|
for (const [userId, wsId] of userWs) {
|
const inTime = toTime(day, randMinute(IN_MIN, IN_MAX));
|
const outTime = toTime(day, randMinute(OUT_MIN, OUT_MAX));
|
const inId = ++id;
|
const outId = ++id;
|
rows.push(`(${inId},${userId},${wsId},${TYPE_CLOCK_IN},NULL,'${userId}','${inTime}','${userId}','${inTime}',b'0')`);
|
rows.push(`(${outId},${userId},${wsId},${TYPE_CLOCK_OUT},NULL,'${userId}','${outTime}','${userId}','${outTime}',b'0')`);
|
plan.push({ day, userId, wsId, inTime, outTime });
|
}
|
}
|
|
// ---- 输出 SQL ----
|
const out = [];
|
out.push(`-- 生产管理工作记录(上下工流水)补录 | 目标库 mom-hm`);
|
out.push(`-- 范围：${RANGE_START} ~ ${RANGE_END}（工作日 ${workDays.length} 天）× ${userWs.size} 人`);
|
out.push(`-- 回滚：DELETE FROM mes_pro_work_record_log WHERE id > ${MAX_EXISTING_ID};`);
|
out.push('START TRANSACTION;');
|
out.push(`DELETE FROM mes_pro_work_record_log WHERE id > ${MAX_EXISTING_ID};`);
|
out.push(`\n-- mes_pro_work_record_log : ${rows.length} 行（${plan.length} 组上/下工）`);
|
for (let i = 0; i < rows.length; i += 200) {
|
out.push(
|
`INSERT INTO mes_pro_work_record_log (id,user_id,workstation_id,type,remark,creator,create_time,updater,update_time,deleted) VALUES\n${rows.slice(i, i + 200).join(',\n')};`,
|
);
|
}
|
out.push('\nCOMMIT;');
|
|
fs.writeFileSync(path.join(DIR, 'workrecord-executed.sql'), out.join('\n'), 'utf8');
|
fs.writeFileSync(
|
path.join(DIR, 'workrecord-plan.json'),
|
JSON.stringify({ rangeStart: RANGE_START, rangeEnd: RANGE_END, users: userWs.size, workDays, userWs: Object.fromEntries(userWs), sessions: plan.length, rows: rows.length, newMaxId: id }, null, 1),
|
'utf8',
|
);
|
console.log('users:', userWs.size, 'workDays:', workDays.length, 'sessions:', plan.length, 'rows:', rows.length, 'newMaxId:', id);
|
console.log('workDays:', workDays.join(', '));
|
