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>
323 lines
13 KiB
JavaScript
323 lines
13 KiB
JavaScript
'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,
|
||
};
|