oms/backend/app/lib/orderItemExpand.js
liufumingren 20147fb391 fix(shop-product): 1688 单靠店管家规格编码匹配店铺商品
1688(Alibaba)订单的 SubOrders[].SkuId 被店管家塞成了商品标题,
平台规格ID 这条路永远匹配不到店铺商品(旧系统 Dianguanjia.php 的 get_sku_bind
只按 SkuId 严格等值,同样匹配不上)。规格的真实唯一键是店管家规格编码:
商品库 Skus[].SkuCode(如 10000446302877)= 订单 PtProductInfo.SkuCode,
抖店/拼多多的 SubOrders[].SkuCode 也是这个值,跨平台通用。

- shop_products 增列 outer_sku_code(迁移 20261109100000),商品同步时写入
  Skus[].SkuCode,并加入 upsert 字段
- 订单明细归一化增补 outer_sku_code(PtProductInfo.SkuCode 优先,退 SkuCode),
  随 platform_snapshot 落库;历史明细由 resolveItemSkuCode 回落到 raw.PtProductInfo.SkuCode
- 匹配口径改为「平台规格ID → 店管家规格编码 → (仅当完全无规格信息)商品ID 兜底」,
  带了规格信息却都没命中时不退化成按商品ID取一条,避免绑错规格
- 拉单逐条匹配、明细解析、商品同步后批量重匹配三处共用 shopProductScopes/createShopProductMatcher
- 实测店铺 19 同步 1438 行后 rematched=44,1688 明细全部指向正确规格
  (10000446302877→420ml*1瓶+排骨梳、…878→99ML黑色、…881→420ML)

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-08 18:01:24 +08:00

323 lines
13 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';
/**
* 订单明细「平台行 ↔ 最小单位行」互转。
*
* 平台订单一条明细在绑定组合商品后需要拆成组合内的子商品行(数量 = 平台数量 × 组合内数量,
* 单价取子商品自身零售价),这样出库推送、导出、统计都是最小单位;
* 解绑或变更绑定时再按 platform_snapshot 折叠回平台行重新展开。
*
* 可逆的前提:明细行的 platform_snapshot 保留平台原样
* (编码 / 名称 / 规格 / 图片 + 原行号 / 原数量 / 原单价),拆开的子行共用同一份快照。
*/
/**
* 截断字符串,避免超出表字段长度
* @param {*} value 原始值
* @param {number} [max] 最大长度
* @return {string} 处理后的字符串
*/
function text(value, max) {
const str = value == null ? '' : String(value).trim();
return max ? str.slice(0, max) : str;
}
/**
* 数值化(空值按 0)
* @param {*} value 原始值
* @return {number} 数值
*/
function number(value) {
const num = Number(value);
return Number.isFinite(num) ? num : 0;
}
/**
* 数量保留 3 位小数(与 plan_qty DECIMAL(12,3) 一致)
* @param {*} value 原始数量
* @return {number} 数量
*/
function roundQty(value) {
return Number(number(value).toFixed(3));
}
/**
* 金额保留 2 位小数(空值保持 null,表示平台未给价)
* @param {*} value 原始金额
* @return {number|null} 金额
*/
function roundMoney(value) {
if (value == null || value === '') return null;
const num = Number(value);
return Number.isFinite(num) ? Number(num.toFixed(2)) : null;
}
/**
* 由平台明细生成快照(写入 order_status_items.platform_snapshot)
* @param {Object} line 平台明细(统一结构:sku_code/name/spec/image/quantity/price/outer_item_id)
* @param {String|Number} lineNo 原行号
* @return {Object} 快照
*/
function buildSnapshot(line, lineNo) {
const data = line || {};
// 店管家规格编码:平台规格ID为标题/缺失时(1688 单)靠它重新匹配店铺商品
const outerSkuCode = text(data.outer_sku_code, 64);
return {
item_code: text(data.sku_code || data.outer_item_id, 100) || null,
item_name: text(data.name, 255) || null,
sku_code: text(data.sku_code, 100) || null,
...(outerSkuCode ? { outer_sku_code: outerSkuCode } : {}),
spec: text(data.spec, 255) || null,
image: text(data.image, 255) || null,
line_no: text(lineNo == null ? '' : lineNo, 20),
plan_qty: roundQty(data.quantity),
actual_price: roundMoney(data.price),
};
}
/**
* 明细行折叠回平台行:拆开的子行按快照的原行号归并回一行
* @param {Array} rows 订单明细行(order_status_items,任意顺序)
* @return {Array} 平台行(含 template 原行用于复制其它字段)
*/
function foldPlatformLines(rows) {
const groups = new Map();
for (const row of rows || []) {
const snapshot = row.platform_snapshot && typeof row.platform_snapshot === 'object' ? row.platform_snapshot : null;
const snapshotLineNo = snapshot && snapshot.line_no ? String(snapshot.line_no) : '';
// 没有快照行号的历史数据按行自身 id 独立成行,避免把不同平台行误并
const key = snapshotLineNo ? `L${snapshotLineNo}` : `R${row.id}`;
if (groups.has(key)) continue;
groups.set(key, {
line_no: snapshotLineNo || text(row.order_line_no, 20) || '',
item_code: snapshot ? snapshot.item_code : row.item_code,
item_name: snapshot ? snapshot.item_name : row.item_name,
sku_code: snapshot ? snapshot.sku_code : row.sku_code,
spec: snapshot ? snapshot.spec : row.spec,
image: snapshot ? snapshot.image : row.image,
plan_qty: snapshot && snapshot.plan_qty != null ? number(snapshot.plan_qty) : number(row.plan_qty),
actual_price: roundMoney(snapshot && snapshot.actual_price != null ? snapshot.actual_price : row.actual_price),
shop_product_id: row.shop_product_id == null ? null : Number(row.shop_product_id),
outer_item_id: row.outer_item_id || null,
outer_sku_id: row.outer_sku_id || null,
outer_sku_code: (snapshot && snapshot.outer_sku_code) || null,
platform_snapshot: snapshot,
template: row,
});
}
return Array.from(groups.values()).sort((a, b) => {
const left = Number(a.line_no);
const right = Number(b.line_no);
if (Number.isFinite(left) && Number.isFinite(right) && left !== right) return left - right;
return String(a.line_no).localeCompare(String(b.line_no));
});
}
/**
* 展开平台行为明细行(最小单位)
* @param {Object} line foldPlatformLines 产出的平台行
* @param {Object} binding 绑定上下文 { shopProduct, product, combo, comboItems }
* @return {Array} 明细行(不含 principal_id / order_id / order_line_no)
*/
function expandPlatformLine(line, binding = {}) {
const template = line.template || {};
const base = {
shop_id: template.shop_id == null ? null : Number(template.shop_id),
shop_product_id: line.shop_product_id || null,
outer_item_id: line.outer_item_id || null,
outer_sku_id: line.outer_sku_id || null,
raw: template.raw || null,
platform_snapshot: line.platform_snapshot || null,
ext_code: template.ext_code == null ? null : template.ext_code,
inventory_type: template.inventory_type || 'ZP',
source_order_code: template.source_order_code == null ? null : template.source_order_code,
extend_props: template.extend_props && typeof template.extend_props === 'object' ? template.extend_props : null,
item_id: null,
stock_id: null,
};
const qty = roundQty(line.plan_qty);
const price = roundMoney(line.actual_price);
const { shopProduct, product, combo, comboItems } = binding;
// 绑定组合:拆成组合内每个子商品一行,数量 = 平台数量 × 组合内数量,单价取子商品零售价
if (combo) {
const children = (comboItems || []).filter(item => item && item.product_id);
if (children.length) {
return children.map((child, idx) => ({
...base,
item_code: text(child.item_code, 100) || null,
item_name: text(child.item_name, 255) || null,
sku_code: text(child.item_code, 100) || null,
spec: text(child.sku_property, 255) || null,
image: text(child.image, 255) || null,
plan_qty: roundQty(qty * (number(child.quantity) || 1)),
quantity: roundQty(qty * (number(child.quantity) || 1)),
actual_price: roundMoney(child.retail_price) == null ? 0 : roundMoney(child.retail_price),
retail_price: roundMoney(child.retail_price) == null ? 0 : roundMoney(child.retail_price),
product_id: Number(child.product_id),
combo_product_id: combo.id,
item_type: 1,
parse_status: 1,
parse_msg: null,
sub_source_order_code: childSubCode(base.source_order_code, idx + 1),
}));
}
// 组合没有配置子商品:保留组合行并提示,避免把组合编码推给仓库
return [{
...base,
item_code: text(combo.code, 100) || null,
item_name: text(combo.name, 255) || null,
sku_code: text(combo.code, 100) || null,
spec: line.spec || null,
image: line.image || null,
plan_qty: qty,
quantity: qty,
actual_price: price,
retail_price: price,
product_id: null,
combo_product_id: combo.id,
item_type: 2,
parse_status: 1,
parse_msg: '组合商品未配置子商品,无法拆分为最小单位',
}];
}
// 绑定系统商品:一行对一行,商品信息取系统商品
if (product) {
return [{
...base,
item_code: text(product.item_code, 100) || null,
item_name: text(product.item_name, 255) || null,
sku_code: text(product.item_code, 100) || null,
spec: text(product.sku_property, 255) || null,
image: text(product.image, 255) || null,
plan_qty: qty,
quantity: qty,
actual_price: price,
retail_price: price,
product_id: Number(product.id),
combo_product_id: null,
item_type: 1,
parse_status: 1,
parse_msg: null,
}];
}
// 未采集到店铺商品 / 店铺商品未绑定系统商品:保留平台原样一行
return [{
...base,
item_code: line.item_code || null,
item_name: line.item_name || null,
sku_code: line.sku_code || null,
spec: line.spec || null,
image: line.image || null,
plan_qty: qty,
quantity: qty,
actual_price: price,
retail_price: price,
product_id: null,
combo_product_id: null,
item_type: 0,
parse_status: 2,
parse_msg: shopProduct
? '店铺商品未绑定系统商品,已保留平台原样数据'
: '未采集到该店铺商品,已保留平台原样数据',
}];
}
/**
* 取明细的店管家规格编码(匹配店铺商品的第二键)
*
* 优先用写入时的平台快照(拉单/重建时写入),历史数据没有快照字段时回落到平台原始报文:
* 1688 的编码在 `PtProductInfo.SkuCode`,抖店/拼多多直接放在 `SkuCode`。
* @param {Object} row 明细行(order_items / order_status_items,含 platform_snapshot 与 raw)
* @return {string} 店管家规格编码,取不到时返回空字符串
*/
function resolveItemSkuCode(row) {
const data = row || {};
const snapshot = data.platform_snapshot && typeof data.platform_snapshot === 'object' ? data.platform_snapshot : null;
if (snapshot && snapshot.outer_sku_code) return text(snapshot.outer_sku_code, 64);
const raw = data.raw && typeof data.raw === 'object' ? data.raw : null;
if (!raw) return '';
const ptInfo = raw.PtProductInfo && typeof raw.PtProductInfo === 'object' ? raw.PtProductInfo : {};
return text(ptInfo.SkuCode || raw.SkuCode, 64);
}
/**
* 子行来源编码(保留原来源 + 序号,便于溯源)
* @param {string} [sourceCode] 原行来源编码
* @param {number} seq 子行序号
* @return {string|null} 子行来源编码
*/
function childSubCode(sourceCode, seq) {
const base = text(sourceCode, 40);
if (!base) return null;
return `${base}#${seq}`.slice(0, 50);
}
/**
* 补齐平台快照:保证每行都带完整快照(原行号 / 原数量 / 原单价),便于下次折叠回平台行
* @param {Object} line foldPlatformLines 产出的平台行
* @return {Object} 快照
*/
function normalizeSnapshot(line) {
const source = line.platform_snapshot && typeof line.platform_snapshot === 'object' ? line.platform_snapshot : {};
const snapshot = { ...source };
if (!snapshot.item_code) snapshot.item_code = line.item_code || null;
if (!snapshot.item_name) snapshot.item_name = line.item_name || null;
if (snapshot.sku_code === undefined) snapshot.sku_code = line.sku_code || null;
if (!snapshot.outer_sku_code && line.outer_sku_code) snapshot.outer_sku_code = text(line.outer_sku_code, 64);
if (snapshot.spec === undefined) snapshot.spec = line.spec || null;
if (snapshot.image === undefined) snapshot.image = line.image || null;
if (!snapshot.line_no) snapshot.line_no = text(line.line_no, 20);
if (snapshot.plan_qty == null) snapshot.plan_qty = roundQty(line.plan_qty);
if (snapshot.actual_price == null) snapshot.actual_price = roundMoney(line.actual_price);
return snapshot;
}
/**
* 折叠「订单唯一赠品」行:同一赠品商品整单只保留一行,数量固定 1
*
* 场景:赠品在平台上可能随购买数量重复(数量 3 就是 3 件),也可能同时出现在**多个组合**里
* (两份组合、甚至不同组合都含同一赠品)。业务上赠品每个订单只送一份,所以展开成最小单位之后
* 统一收敛:第一次出现的那行数量改 1,其余同商品的整行丢弃。
* @param {Array<Object>} rows 展开后的明细行(含 product_id / plan_qty / quantity)
* @param {Set<number>|Array<number>} giftProductIds 标记为「订单唯一赠品」的商品ID
* @return {Array<Object>} 收敛后的明细行
*/
function collapseUniqueGiftRows(rows, giftProductIds) {
const gifts = giftProductIds instanceof Set
? giftProductIds
: new Set((Array.isArray(giftProductIds) ? giftProductIds : []).map(Number));
if (!gifts.size) return Array.isArray(rows) ? rows : [];
const seen = new Set();
const out = [];
for (const row of Array.isArray(rows) ? rows : []) {
const productId = row && row.product_id != null ? Number(row.product_id) : null;
if (!productId || !gifts.has(productId)) {
out.push(row);
continue;
}
if (seen.has(productId)) continue;
seen.add(productId);
// 赠品不按购买数量累加:整单固定 1 件
out.push({ ...row, plan_qty: 1, quantity: 1 });
}
return out;
}
module.exports = {
buildSnapshot,
normalizeSnapshot,
foldPlatformLines,
expandPlatformLine,
collapseUniqueGiftRows,
resolveItemSkuCode,
};