oms/backend/app/lib/orderSnCodes.js
liufumingren 61309c430f feat(order): 留存 WMS 回传的 SN 列表并支持按 SN 反查订单
- 新增 order_sn_codes 关联表(order_id = order_index.id,一行一个 SN,唯一键兜底去重)
- deliveryorder.confirm 解析 orderLines[].snList / 包裹内商品 snList,幂等写入(仅委外中/已配送的订单)
- 订单列表新增 sn_codes 多值筛选(逗号/换行分隔,最多 200 个,精确匹配反查)
- 订单详情返回 sn_codes;订单导出支持「序列号」筛选
- 前端订单页新增「序列号」批量筛选与详情序列号明细
- 补充 WMS 回传报文说明与 lib 单测

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-13 22:42:57 +08:00

266 lines
9.7 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';
/**
* 订单序列号(SN)关联表的解析与写入
*
* 背景:WMS 回传发货确认(deliveryorder.confirm)时,明细行可能带 `snList`:
* { request: { orderLines: [ { orderLineNo: '1', itemCode: 'SKU-A', snList: [ 'SN1', 'SN2' ] } ],
* packages: { package: [ { expressCode, packageCode, items: [ { itemCode, snList } ] } ] } } }
* 这些 SN 原先只留在 `wms_push_result.confirm` 的 JSON 摘要里,无法反查订单;
* 现在统一落 `order_sn_codes`(一行一个 SN,`order_id` = order_index.id),
* 订单列表即可按「主体 + SN(支持多个)」反查出订单。
*
* 幂等:同一订单重复回传同一报文时不新增行(先查已存在的 SN,再由唯一键 `uk_osc_order_sn` 兜底)。
*/
/** SN 列宽(order_sn_codes.sn_code),超长截断避免异常报文撑爆索引 */
const SN_MAX_LEN = 255;
/** 单次回传最多留存的 SN 条数,防止异常报文写入过多数据 */
const SN_MAX_COUNT = 2000;
/**
* 字段取值并截断(去空白;空值返回 '')
* @param {Object} obj 源对象
* @param {Array<String>} keys 候选字段名
* @param {Number} max 最大长度
* @return {String} 值
*/
function pickText(obj, keys, max) {
if (!obj || typeof obj !== 'object') return '';
for (const key of keys) {
const value = obj[key];
if (value === undefined || value === null) continue;
if (typeof value === 'object') continue;
const text = String(value).trim();
if (text) return text.slice(0, max);
}
return '';
}
/**
* 数字ID归一化(非正整数返回 null)
* @param {*} value 原值
* @return {Number|null} 数值
*/
function toId(value) {
const num = Number(value);
return Number.isInteger(num) && num > 0 ? num : null;
}
/**
* 从明细行的 SN 字段取 SN 列表(兼容数组 / 逗号分隔字符串 / 对象数组)
* 支持字段:snList / sn_list / snCodes / sn_codes / snCode / sn_code / sn
* @param {Object} obj 明细行或包裹内商品
* @return {Array<String>} SN 列表(原值去空白,未去重)
*/
function extractSnValues(obj) {
if (!obj || typeof obj !== 'object') return [];
const values = [];
const push = raw => {
if (raw === undefined || raw === null) return;
if (Array.isArray(raw)) {
raw.forEach(push);
return;
}
if (typeof raw === 'object') {
// 兼容 [{ snCode: 'xxx' }, { sn: 'xxx' }, { code: 'xxx' }]
push(raw.snCode ?? raw.sn_code ?? raw.sn ?? raw.code ?? raw.value);
return;
}
const text = String(raw).trim();
if (!text) return;
// 一个字段里塞了多个 SN("SN1,SN2" / 换行分隔)时拆开
if (/[,,;;\r\n]/.test(text)) {
text.split(/[,,;;\r\n]+/).forEach(item => push(item));
return;
}
values.push(text.slice(0, SN_MAX_LEN));
};
for (const key of [ 'snList', 'sn_list', 'snCodes', 'sn_codes', 'snCode', 'sn_code', 'sn' ]) {
if (obj[key] !== undefined && obj[key] !== null) push(obj[key]);
}
return values;
}
/**
* 包裹内商品数组归一化(兼容 items 数组 / items.item 数组)
* @param {Object} pkg 包裹
* @return {Array<Object>} 商品数组
*/
function packageItems(pkg) {
const raw = pkg && pkg.items;
if (Array.isArray(raw)) return raw.filter(item => item && typeof item === 'object');
if (raw && Array.isArray(raw.item)) return raw.item.filter(item => item && typeof item === 'object');
return [];
}
/**
* 从发货确认报文的明细行 / 包裹中解析 SN 条目
*
* 行级 SN 与包裹内商品 SN 都收:行级带 orderLineNo/itemCode,包裹级额外带 packageCode/expressCode;
* 同一 SN 只保留首次出现的条目(MySQL 比较不区分大小写,这里也按小写去重)。
* @param {Object} p 参数
* @param {Array<Object>} [p.lines] 明细行数组(request.orderLines)
* @param {Array<Object>} [p.packages] 包裹数组(request.packages.package)
* @return {Array<Object>} SN 条目:{ sn_code, order_line_no, item_id, item_code, item_name, package_code, express_code }
*/
function extractSnEntries({ lines = [], packages = [] } = {}) {
const entries = [];
const seen = new Set();
const add = (sn, meta) => {
const text = String(sn == null ? '' : sn).trim().slice(0, SN_MAX_LEN);
if (!text) return false;
const key = text.toLowerCase();
if (seen.has(key)) return false;
seen.add(key);
entries.push({
sn_code: text,
order_line_no: meta.order_line_no || null,
item_id: meta.item_id || null,
item_code: meta.item_code || null,
item_name: meta.item_name || null,
package_code: meta.package_code || null,
express_code: meta.express_code || null,
});
return entries.length >= SN_MAX_COUNT;
};
for (const line of Array.isArray(lines) ? lines : []) {
if (!line || typeof line !== 'object') continue;
const meta = {
order_line_no: pickText(line, [ 'orderLineNo', 'order_line_no' ], 20),
item_id: toId(line.itemId ?? line.item_id),
item_code: pickText(line, [ 'itemCode', 'item_code' ], 100),
item_name: pickText(line, [ 'itemName', 'item_name' ], 255),
};
for (const sn of extractSnValues(line)) {
if (add(sn, meta)) return entries;
}
}
for (const pkg of Array.isArray(packages) ? packages : []) {
if (!pkg || typeof pkg !== 'object') continue;
const packageCode = pickText(pkg, [ 'packageCode', 'package_code' ], 50);
const expressCode = pickText(pkg, [ 'expressCode', 'express_code' ], 50);
for (const item of packageItems(pkg)) {
const meta = {
order_line_no: pickText(item, [ 'orderLineNo', 'order_line_no' ], 20),
item_id: toId(item.itemId ?? item.item_id),
item_code: pickText(item, [ 'itemCode', 'item_code' ], 100),
item_name: pickText(item, [ 'itemName', 'item_name' ], 255),
package_code: packageCode,
express_code: expressCode,
};
for (const sn of extractSnValues(item)) {
if (add(sn, meta)) return entries;
}
}
}
return entries;
}
/**
* 把回传报文里的 SN 落到 order_sn_codes(幂等:已存在的 SN 跳过)
* @param {Object} ctx egg context
* @param {Object} p 参数
* @param {Number} p.principalId 主体ID
* @param {Number} p.orderId 订单ID(order_index.id)
* @param {String} [p.orderNo] 订单号(冗余留痕)
* @param {Number} [p.warehouseId] 仓库ID(app_key 定位到的仓库)
* @param {Array<Object>} [p.lines] 明细行数组
* @param {Array<Object>} [p.packages] 包裹数组
* @param {Object} [p.transaction] 事务(传入时随调用方事务写入)
* @return {Promise<{inserted: Number, total: Number}>} inserted 新增条数 / total 报文里的 SN 条数
*/
async function saveOrderSnCodes(ctx, { principalId, orderId, orderNo, warehouseId, lines, packages, transaction } = {}) {
const entries = extractSnEntries({ lines, packages });
if (!entries.length) return { inserted: 0, total: 0 };
const Op = ctx.app.Sequelize.Op;
const existing = await ctx.model.OrderSnCode.findAll({
where: {
principal_id: principalId,
order_id: orderId,
sn_code: { [Op.in]: entries.map(entry => entry.sn_code) },
},
attributes: [ 'sn_code' ],
raw: true,
transaction,
});
const stored = new Set(existing.map(row => String(row.sn_code || '').trim().toLowerCase()));
const rows = entries
.filter(entry => !stored.has(entry.sn_code.toLowerCase()))
.map(entry => ({
principal_id: principalId,
order_id: orderId,
order_no: orderNo || null,
warehouse_id: warehouseId || null,
order_line_no: entry.order_line_no,
item_id: entry.item_id,
item_code: entry.item_code,
item_name: entry.item_name,
package_code: entry.package_code,
express_code: entry.express_code,
sn_code: entry.sn_code,
source: 'wms',
}));
if (rows.length) {
// ignoreDuplicates:并发/重推时由唯一键 uk_osc_order_sn 兜底,不报错
await ctx.model.OrderSnCode.bulkCreate(rows, { ignoreDuplicates: true, transaction });
ctx.logger.info(
`[order-sn] 订单 ${orderNo || orderId} 留存序列号 ${rows.length} 个(报文共 ${entries.length} 个)`
);
}
return { inserted: rows.length, total: entries.length };
}
/**
* 订单的 SN 列表(详情展示用,按行号 / 自增ID排序)
* @param {Object} ctx egg context
* @param {Object} p 参数
* @param {Number} p.principalId 主体ID
* @param {Number} p.orderId 订单ID(order_index.id)
* @return {Promise<Array<Object>>} SN 行
*/
async function listOrderSnCodes(ctx, { principalId, orderId } = {}) {
if (!orderId) return [];
const rows = await ctx.model.OrderSnCode.findAll({
where: { principal_id: principalId, order_id: orderId },
order: [[ 'order_line_no', 'ASC' ], [ 'id', 'ASC' ]],
raw: true,
});
return rows;
}
/**
* 按 SN 反查订单ID(订单列表筛选用)
* @param {Object} ctx egg context
* @param {Object} p 参数
* @param {Number} p.principalId 主体ID
* @param {Array<String>} p.snCodes SN 列表(精确匹配,最多 200 个由调用方限制)
* @return {Promise<Array<Number>>} 命中的订单ID(去重)
*/
async function findOrderIdsBySnCodes(ctx, { principalId, snCodes } = {}) {
const list = Array.isArray(snCodes) ? snCodes : [];
if (!list.length) return [];
const Op = ctx.app.Sequelize.Op;
const rows = await ctx.model.OrderSnCode.findAll({
where: { principal_id: principalId, sn_code: { [Op.in]: list } },
attributes: [ 'order_id' ],
raw: true,
});
return [ ...new Set(rows.map(row => Number(row.order_id)).filter(id => Number.isInteger(id) && id > 0)) ];
}
module.exports = {
SN_MAX_LEN,
SN_MAX_COUNT,
extractSnEntries,
saveOrderSnCodes,
listOrderSnCodes,
findOrderIdsBySnCodes,
};