oms/backend/app/service/orderItemResolve.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

335 lines
14 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';
const Service = require('egg').Service;
const { foldPlatformLines, normalizeSnapshot, expandPlatformLine, collapseUniqueGiftRows, resolveItemSkuCode } = require('../lib/orderItemExpand');
const { createShopProductMatcher } = require('../lib/shopProductMatch');
const { ORDER_STATUS } = require('../lib/orderStatus');
/**
* 订单明细解析服务
*
* 平台订单明细按「店铺商品 → 系统商品 / 组合商品」的绑定关系解析为最小单位:
* - 未采集到店铺商品 / 店铺商品未绑定系统商品:保留平台原样一行(parse_status=2)
* - 绑定系统商品:一行对一行
* - 绑定组合商品:拆成组合内每个子商品一行(数量 = 平台数量 × 组合内数量,单价取子商品零售价)
*
* 明细行始终在 platform_snapshot 里保留平台原样,因此绑定关系变化时可以折叠回平台行再重新展开,
* 行数变化(1 行 ↔ N 行)不会丢失原始数量与单价。
*/
class OrderItemResolveService extends Service {
/**
* 批量载入组合商品的子商品明细
* @param {Array<number>} comboIds 组合商品ID
* @param {Object} [transaction] 事务(组合商品刚在同事务内改动时必须传,否则读不到未提交的构成)
* @return {Promise<Map<number, Array>>} comboId → 子商品数组
*/
async loadComboItems(comboIds, transaction) {
const { ctx } = this;
const map = new Map();
const ids = [ ...new Set((comboIds || []).map(Number).filter(id => id > 0)) ];
if (!ids.length) return map;
const Op = this.app.Sequelize.Op;
const rows = await ctx.model.ComboProductItem.findAll({
where: { combo_product_id: { [Op.in]: ids } },
include: [{
model: ctx.model.Product,
as: 'product',
attributes: [ 'id', 'item_code', 'item_name', 'sku_property', 'image', 'retail_price' ],
required: false,
}],
order: [[ 'id', 'ASC' ]],
transaction,
});
for (const row of rows) {
const plain = row.get({ plain: true });
const product = plain.product || {};
const comboId = Number(plain.combo_product_id);
if (!map.has(comboId)) map.set(comboId, []);
map.get(comboId).push({
product_id: Number(plain.product_id),
quantity: Number(plain.quantity) || 1,
item_code: product.item_code || null,
item_name: product.item_name || null,
sku_property: product.sku_property || null,
image: product.image || null,
retail_price: product.retail_price,
});
}
return map;
}
/**
* 批量载入店铺商品的绑定上下文
* @param {Array<number>} shopProductIds 店铺商品ID
* @param {Object} [transaction] 事务
* @return {Promise<Map<number, Object>>} shopProductId → { shopProduct, product, combo, comboItems }
*/
async loadBindings(shopProductIds, transaction) {
const { ctx } = this;
const map = new Map();
const ids = [ ...new Set((shopProductIds || []).map(Number).filter(id => id > 0)) ];
if (!ids.length) return map;
const Op = this.app.Sequelize.Op;
const shopProducts = await ctx.model.ShopProduct.findAll({
where: { id: { [Op.in]: ids } },
raw: true,
transaction,
});
if (!shopProducts.length) return map;
const productIds = shopProducts
.filter(row => Number(row.bind_type) === 1 && row.product_id)
.map(row => Number(row.product_id));
const comboIds = shopProducts
.filter(row => Number(row.bind_type) === 2 && row.combo_product_id)
.map(row => Number(row.combo_product_id));
const products = productIds.length
? await ctx.model.Product.findAll({ where: { id: { [Op.in]: productIds } }, raw: true, transaction })
: [];
const combos = comboIds.length
? await ctx.model.ComboProduct.findAll({ where: { id: { [Op.in]: comboIds } }, raw: true, transaction })
: [];
const comboItems = await this.loadComboItems(comboIds, transaction);
const productMap = new Map(products.map(row => [ Number(row.id), row ]));
const comboMap = new Map(combos.map(row => [ Number(row.id), row ]));
for (const shopProduct of shopProducts) {
const bindType = Number(shopProduct.bind_type);
const product = bindType === 1 ? productMap.get(Number(shopProduct.product_id)) : null;
const combo = bindType === 2 ? comboMap.get(Number(shopProduct.combo_product_id)) : null;
map.set(Number(shopProduct.id), {
shopProduct,
product: product || null,
combo: combo || null,
comboItems: combo ? comboItems.get(Number(combo.id)) || [] : [],
});
}
return map;
}
/**
* 商品同步/补拉后,把「未采集到店铺商品」的订单明细按最新店铺商品重新匹配一次
*
* 拉单早于商品入库是新店铺/新平台的常态:明细会停在 shop_product_id=null(未采集),
* 之后商品补齐也不会自动变化,订单页就一直显示未绑定、还被「仅已绑定」过滤掉。
* 这里在商品写入后按与拉单相同的口径(店铺 + 平台商品ID + 平台规格ID)补一次匹配,
* 并重建命中订单的明细(有绑定关系的直接变成已解析)。
* @param {number} shopId 店铺ID
* @param {Object} [options] { transaction }
* @return {Promise<{matched: number, rebuilt: number}>} 补上的明细行数与重建后的明细行数
*/
async rematchUnboundItems(shopId, options = {}) {
const { ctx } = this;
const Op = this.app.Sequelize.Op;
const shopKey = Number(shopId);
if (!shopKey) return { matched: 0, rebuilt: 0 };
// 以库内为准(不用平台快照的中间态),按 id 升序保证「无规格时取第一条」稳定
const products = await ctx.model.ShopProduct.findAll({
where: { shop_id: shopKey },
attributes: [ 'id', 'outer_item_id', 'outer_sku_id', 'outer_sku_code' ],
order: [[ 'id', 'ASC' ]],
raw: true,
transaction: options.transaction,
});
if (!products.length) return { matched: 0, rebuilt: 0 };
const pick = createShopProductMatcher(products);
const items = await ctx.model.OrderStatusItem.findAll({
where: { shop_id: shopKey, shop_product_id: { [Op.is]: null } },
attributes: [ 'id', 'order_id', 'outer_item_id', 'outer_sku_id', 'platform_snapshot', 'raw' ],
raw: true,
transaction: options.transaction,
});
if (!items.length) return { matched: 0, rebuilt: 0 };
// 只补「待审核 / 异常」的订单:其余状态明细已随审核锁定(甚至已下发仓库),不跟着商品数据变化
const indexRows = await ctx.model.OrderIndex.findAll({
where: {
id: { [Op.in]: [ ...new Set(items.map(item => Number(item.order_id)).filter(id => id > 0)) ] },
current_status: { [Op.in]: [ ORDER_STATUS.PENDING, ORDER_STATUS.EXCEPTION ] },
},
attributes: [ 'id' ],
raw: true,
transaction: options.transaction,
});
const editableIds = new Set(indexRows.map(row => Number(row.id)));
if (!editableIds.size) return { matched: 0, rebuilt: 0 };
const orderIds = new Set();
let matched = 0;
for (const item of items) {
if (!editableIds.has(Number(item.order_id))) continue;
// 店管家规格编码在平台快照(历史数据回落到平台原始报文)里:
// 1688 单的平台规格ID 是商品标题,只能靠它匹配到指定规格
const shopProductId = pick({
outer_item_id: item.outer_item_id,
outer_sku_id: item.outer_sku_id,
outer_sku_code: resolveItemSkuCode(item),
});
if (!shopProductId) continue;
await ctx.model.OrderStatusItem.update(
{ shop_product_id: shopProductId },
{ where: { id: item.id }, transaction: options.transaction }
);
orderIds.add(Number(item.order_id));
matched++;
}
if (!matched) return { matched: 0, rebuilt: 0 };
const rebuilt = await this.rebuildOrderItems(Array.from(orderIds), { transaction: options.transaction });
return { matched, rebuilt };
}
/**
* 折叠「订单唯一赠品」明细行
*
* 展开成最小单位后,把标记了 `is_order_unique_gift` 的商品整单收敛为一行、数量 1:
* 平台数量 3 → 1;两份组合各带同一赠品 → 1;不同组合都含该赠品 → 1。
* @param {Array<Object>} payload 展开后的明细行
* @param {Object} transaction 事务
* @return {Promise<Array<Object>>} 收敛后的明细行
*/
async collapseUniqueGifts(payload, transaction) {
const { ctx } = this;
const rows = Array.isArray(payload) ? payload : [];
const productIds = [ ...new Set(rows.map(row => Number(row && row.product_id)).filter(id => id > 0)) ];
if (!productIds.length) return rows;
const Op = this.app.Sequelize.Op;
const gifts = await ctx.model.Product.findAll({
where: { id: { [Op.in]: productIds }, is_order_unique_gift: 1 },
attributes: [ 'id' ],
raw: true,
transaction,
});
const giftIds = new Set(gifts.map(row => Number(row.id)));
if (!giftIds.size) return rows;
return collapseUniqueGiftRows(rows, giftIds);
}
/**
* 按店铺商品批量重建其订单明细(绑定关系或商品资料变化后调用)
* @param {Array<number>} shopProductIds 店铺商品ID
* @param {Object} [options] { transaction }
* @return {Promise<number>} 重建的明细行数
*/
async rebuildOrdersByShopProducts(shopProductIds, options = {}) {
const { ctx } = this;
const ids = [ ...new Set((shopProductIds || []).map(Number).filter(id => id > 0)) ];
if (!ids.length) return 0;
const Op = this.app.Sequelize.Op;
const items = await ctx.model.OrderStatusItem.findAll({
where: { shop_product_id: { [Op.in]: ids } },
attributes: [ 'order_id' ],
group: [ 'order_id' ],
raw: true,
transaction: options.transaction,
});
const orderIds = items.map(row => Number(row.order_id)).filter(id => id > 0);
if (!orderIds.length) return 0;
return this.rebuildOrderItems(orderIds, options);
}
/**
* 按当前绑定关系重建若干订单的明细(折叠为平台行 → 重新展开为最小单位)
*
* 默认只处理「待审核 / 异常」:这两类允许维护明细;
* 待推送及之后的订单明细已随审核锁定库存、甚至已下发仓库,改动会与仓内流程不一致。
* 不含平台溯源信息(手工录入)的订单整单跳过。
* @param {Array<number>} orderIds 订单ID
* @param {Object} [options] { transaction, statuses }
* @return {Promise<number>} 重建后的明细行数合计
*/
async rebuildOrderItems(orderIds, options = {}) {
const { ctx } = this;
const ids = [ ...new Set((orderIds || []).map(Number).filter(id => id > 0)) ];
if (!ids.length) return 0;
const Op = this.app.Sequelize.Op;
const statuses = options.statuses || [ ORDER_STATUS.PENDING, ORDER_STATUS.EXCEPTION ];
const transaction = options.transaction || await ctx.model.transaction();
const ownedTransaction = !options.transaction;
// 必须在事务内查索引行:首次拉单建单时 OrderIndex 与明细在同一事务里刚写入,
// 事务外读不到 → 会误判「无需重建」而保留平台原值(表现为已绑定却显示平台商品名)
const indexRows = await ctx.model.OrderIndex.findAll({
where: { id: { [Op.in]: ids }, current_status: { [Op.in]: statuses } },
attributes: [ 'id' ],
raw: true,
transaction,
});
const targetIds = indexRows.map(row => Number(row.id));
if (!targetIds.length) {
if (ownedTransaction) await transaction.rollback();
return 0;
}
let affected = 0;
try {
for (const orderId of targetIds) {
const rows = await ctx.model.OrderStatusItem.findAll({
where: { order_id: orderId },
order: [[ 'order_line_no', 'ASC' ], [ 'id', 'ASC' ]],
raw: true,
transaction,
});
if (!rows.length) continue;
// 只重建「平台行」:手工录入的明细没有平台溯源信息(快照 / 平台商品ID / 店铺商品),
// 一旦按平台口径重排会被抹成未解析并丢掉已选商品,因此整单跳过,保持原样
const isPlatformOrder = rows.every(row => row.platform_snapshot
|| row.shop_product_id != null
|| String(row.outer_item_id || '').trim());
if (!isPlatformOrder) continue;
const lines = foldPlatformLines(rows);
const bindings = await this.loadBindings(lines.map(line => line.shop_product_id), transaction);
const payload = [];
for (const line of lines) {
const snapshot = normalizeSnapshot(line);
const binding = line.shop_product_id ? bindings.get(Number(line.shop_product_id)) : null;
const parts = expandPlatformLine({ ...line, platform_snapshot: snapshot }, binding || {});
for (const part of parts) payload.push(part);
}
// 「订单唯一赠品」:整单只留一行、数量 1(多数量、多组合命中同一赠品都收敛成一条)
const collapsed = await this.collapseUniqueGifts(payload, transaction);
await ctx.model.OrderStatusItem.destroy({ where: { order_id: orderId }, transaction });
if (collapsed.length) {
await ctx.model.OrderStatusItem.bulkCreate(
collapsed.map((row, idx) => ({
...row,
principal_id: rows[0].principal_id,
order_id: orderId,
order_line_no: String(idx + 1),
})),
{ transaction }
);
}
await ctx.service.orderStatus.refreshItemSummary(orderId, rows[0].principal_id, transaction);
affected += collapsed.length;
}
if (ownedTransaction) await transaction.commit();
} catch (err) {
if (ownedTransaction) await transaction.rollback();
throw err;
}
return affected;
}
}
module.exports = OrderItemResolveService;