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>
335 lines
14 KiB
JavaScript
335 lines
14 KiB
JavaScript
'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;
|