WMS_MAIN/script/backfill-pack-status-fin.cjs

174 lines
6.6 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

'use strict';
/**
* 回补历史"打包完成但订单仍为打包中(padding)"的存量数据。
*
* 背景:workbench.finishPack / order_packing.setEndTime 曾只结束 wms_pack_task,
* 不更新订单 pack_status,导致大量订单停留在 padding(打包中)。
*
* 回补规则(以 wms_pack_task 为基准,status=2 视为打包已完成):
* - 团购任务(pack_mode=2):按 wave_no 批量更新对应仓库分表中
* pack_status='padding' 且未被拦截(intercept_status IN ('none', NULL))的订单 → 'fin'
* - 散单任务(pack_mode=1):按 order_id 更新单个订单(同样只改 padding 且未拦截)
* - 仅更新任务已完成(status=2)对应订单;仍在打包(status=1)或拦截中的订单不动
*
* 用法:
* node script/backfill-pack-status-fin.cjs # 预览模式:只统计不修改
* node script/backfill-pack-status-fin.cjs --apply # 实际执行
*
* 连接配置:默认 script/db-query-prod-config.js(生产库)。
*/
const path = require('path');
const { createRequire } = require('module');
const APPLY = process.argv.includes('--apply');
async function main() {
const projectRoot = path.resolve(__dirname, '..');
const projectRequire = createRequire(path.join(projectRoot, 'package.json'));
const mysql = projectRequire('mysql2/promise');
const configPath = path.join(__dirname, 'db-query-prod-config.js');
const cfg = require(configPath);
const client = cfg && cfg.mysql && cfg.mysql.clients && cfg.mysql.clients.wms;
if (!client) {
throw new Error(`未找到 mysql clients.wms 配置: ${configPath}`);
}
const conn = await mysql.createConnection({
host: client.host,
port: Number(client.port),
user: client.user,
password: client.password,
database: client.database,
charset: client.charset || 'utf8mb4',
timezone: 'Z',
multipleStatements: true,
});
try {
// 1. 读取所有已完成的打包任务
const [ tasks ] = await conn.query(
'SELECT warehouse_id, pack_mode, order_id, wave_no FROM wms_pack_task WHERE status = 2 AND deleted_at IS NULL'
);
const byWarehouse = new Map();
for (const t of tasks) {
const wh = Number(t.warehouse_id || 0);
if (!byWarehouse.has(wh)) byWarehouse.set(wh, { groupWaves: new Set(), singleOrders: new Set() });
const bucket = byWarehouse.get(wh);
if (Number(t.pack_mode) === 2 && t.wave_no) {
bucket.groupWaves.add(String(t.wave_no));
} else if (t.order_id) {
bucket.singleOrders.add(Number(t.order_id));
}
}
console.log(`任务统计: ${tasks.length} 条已完成任务,涉及仓库 ${byWarehouse.size} 个`);
for (const [ wh, b ] of byWarehouse.entries()) {
console.log(` 仓库 ${wh}: 团购波次 ${b.groupWaves.size} 个,散单订单 ${b.singleOrders.size} 笔`);
}
const chunk = list => {
const out = [];
for (let i = 0; i < list.length; i += 500) out.push(list.slice(i, i + 500));
return out;
};
let totalGroupAffected = 0;
let totalSingleAffected = 0;
const detail = [];
// 2. 逐仓库处理其 current / dispatch 分表
for (const [ wh, b ] of byWarehouse.entries()) {
const tables = [];
const [ tableRows ] = await conn.query('SHOW TABLES LIKE \'wms_order_delivery%\'');
const names = tableRows.map(r => Object.values(r)[0]);
for (const n of names) {
if (n.includes('_history') || n.includes('_outsourced') || n.includes('_bak')) continue;
// current 表(无后缀或带仓库后缀)与 dispatch 表都纳入
tables.push(n);
}
const waveList = [ ...b.groupWaves ];
const orderList = [ ...b.singleOrders ];
for (const tbl of tables) {
// 团购:按 wave_no 批量更新
for (const waves of chunk(waveList)) {
const waveIn = waves.map(w => conn.escape(w)).join(',');
const sql = `UPDATE ${tbl} SET pack_status = 'fin'
WHERE pack_status = 'padding'
AND intercept_status IN ('none', NULL)
AND wave_no IN (${waveIn})`;
if (APPLY) {
const [ r ] = await conn.query(sql);
if (r.affectedRows > 0) {
totalGroupAffected += r.affectedRows;
detail.push({ tbl, kind: 'group', waves: waves.length, affected: r.affectedRows });
}
} else {
const [ preview ] = await conn.query(
`SELECT COUNT(1) AS cnt FROM ${tbl}
WHERE pack_status = 'padding'
AND intercept_status IN ('none', NULL)
AND wave_no IN (${waveIn})`
);
const cnt = Number(preview[0].cnt || 0);
if (cnt > 0) {
totalGroupAffected += cnt;
detail.push({ tbl, kind: 'group', waves: waves.length, affected: cnt });
}
}
}
// 散单:按 order_id 更新
for (const orders of chunk(orderList)) {
const orderIn = orders.join(',');
if (APPLY) {
const sql = `UPDATE ${tbl} SET pack_status = 'fin'
WHERE pack_status = 'padding'
AND intercept_status IN ('none', NULL)
AND id IN (${orderIn})`;
const [ r ] = await conn.query(sql);
if (r.affectedRows > 0) {
totalSingleAffected += r.affectedRows;
detail.push({ tbl, kind: 'single', orders: orders.length, affected: r.affectedRows });
}
} else {
const [ preview ] = await conn.query(
`SELECT COUNT(1) AS cnt FROM ${tbl}
WHERE pack_status = 'padding'
AND intercept_status IN ('none', NULL)
AND id IN (${orderIn})`
);
const cnt = Number(preview[0].cnt || 0);
if (cnt > 0) {
totalSingleAffected += cnt;
detail.push({ tbl, kind: 'single', orders: orders.length, affected: cnt });
}
}
}
}
}
console.log('');
console.log(`==== ${APPLY ? '已执行' : '预览(加 --apply 执行)'} ====`);
console.log(`团购订单将更新: ${totalGroupAffected}`);
console.log(`散单订单将更新: ${totalSingleAffected}`);
console.log(`合计: ${totalGroupAffected + totalSingleAffected}`);
if (detail.length > 0) {
console.log('明细:');
for (const d of detail) console.log(` ${d.tbl} [${d.kind}] ${d.kind === 'group' ? d.waves + ' 波次' : d.orders + ' 订单'} → ${d.affected} 单`);
}
} finally {
await conn.end();
}
}
main().catch(err => {
console.error('回补脚本执行失败:', err.message);
process.exit(1);
});