WMS_MAIN/script/backfill-batch-no-array.cjs
liufumingren 95067aeb8a feat(wms): 工作台成员、波次范围、库存明细与报表修复等多项更新
- workbench: 工作台成员拆分 wms_workbench_member 表,下线 barcode 字段,新增绑定/移除成员接口

- order: 订单配送新增 wave_scope 波次范围字段与历史回填,自动分配快递排除备注与储位分配限制修复

- storage.transaction.list: 重写为 wms_storage_transaction 逐条明细查询,消除聚合慢查询

- stock.report: 分页默认生效、排序白名单扩充、where.stock_id 数组兼容修复

- storage.entry: quickInbound/commit 新增 noNeedNotice 通知开关

- storage.inventory.list: stock_query 支持数组多关键字并集匹配

- store: 手工单回传通知策略 notify_manual_sync

- docs: 同步接口文档与变更记录、路由表;test: 补充快递排除与储位分配限制测试
2026-09-05 22:43:38 +08:00

92 lines
3.1 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';
/**
* 回填 wms_wave_pick_task.batch_no_array。
*
* 从 wms_wave_pick_task_wave 按 task_id 聚合 wave_no(逗号分隔,按 id 升序),
* 只更新 batch_no_array 为空的记录,幂等可重复执行。
*
* 用法:
* 开发:node script/backfill-batch-no-array.cjs
* 生产:$env:NODE_ENV='production'; node script/backfill-batch-no-array.cjs
*/
const dbConfig = require('../database/config.js');
const Sequelize = require('sequelize');
(async () => {
const cfg = dbConfig.production;
const s = new Sequelize(cfg.database, cfg.username, cfg.password, {
host: cfg.host,
port: cfg.port,
dialect: cfg.dialect || 'mysql',
logging: false,
});
// 放大 GROUP_CONCAT 上限(任务最多 20 波次,避免截断)
await s.query('SET SESSION group_concat_max_len = 1048576');
const [ updateResult ] = await s.query(`
UPDATE wms_wave_pick_task t
JOIN (
SELECT
tw.task_id,
GROUP_CONCAT(tw.wave_no ORDER BY tw.id ASC SEPARATOR ',') AS wave_nos
FROM wms_wave_pick_task_wave tw
WHERE tw.wave_no IS NOT NULL AND tw.wave_no <> ''
GROUP BY tw.task_id
) w ON w.task_id = t.id
SET t.batch_no_array = w.wave_nos,
t.update_time = NOW()
WHERE t.batch_no_array IS NULL OR t.batch_no_array = ''
`);
console.log('1) task_wave 聚合回填行数:', JSON.stringify(updateResult && updateResult.affectedRows !== undefined
? updateResult.affectedRows
: null));
// 回退 2:task_wave 无记录的任务,用主表 wave_id 关联 wms_wave_batch.wave_no
const [ updateResult2 ] = await s.query(`
UPDATE wms_wave_pick_task t
JOIN wms_wave_batch wb ON wb.wave_id = t.wave_id
SET t.batch_no_array = wb.wave_no,
t.update_time = NOW()
WHERE (t.batch_no_array IS NULL OR t.batch_no_array = '')
AND t.wave_id > 0
AND wb.wave_no IS NOT NULL AND wb.wave_no <> ''
`);
console.log('2) wave_id→wms_wave_batch 回填行数:', JSON.stringify(updateResult2 && updateResult2.affectedRows !== undefined
? updateResult2.affectedRows
: null));
// 回退 3:仍为空的散单老数据,用主表 batch_no 兜底
const [ updateResult3 ] = await s.query(`
UPDATE wms_wave_pick_task t
SET t.batch_no_array = t.batch_no,
t.update_time = NOW()
WHERE (t.batch_no_array IS NULL OR t.batch_no_array = '')
AND t.batch_no IS NOT NULL AND t.batch_no <> ''
`);
console.log('3) batch_no 兜底回填行数:', JSON.stringify(updateResult3 && updateResult3.affectedRows !== undefined
? updateResult3.affectedRows
: null));
const [ remaining ] = await s.query(
"SELECT COUNT(*) AS c FROM wms_wave_pick_task WHERE batch_no_array IS NULL OR batch_no_array = ''"
);
console.log('回填后仍为空的任务数:', JSON.stringify(remaining));
const [ sample ] = await s.query(
"SELECT id, task_no, batch_no_array FROM wms_wave_pick_task "
+ "WHERE id IN (259, 258, 251, 249) ORDER BY id DESC"
);
console.log('抽样核验:', JSON.stringify(sample, null, 2));
await s.close();
})().catch(e => {
console.error('ERROR:', e.message);
process.exit(1);
});