oms/backend/app/model/shopProduct.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

49 lines
3.1 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';
/**
* 店铺商品模型,对应 shop_products 表
* 采集自平台店铺的商品,可单向绑定系统商品(products)或组合商品(combo_products):
* bind_type:0未绑定 1系统商品 2组合商品;未绑定时两个商品ID均为空
* @param {Egg.Application} app egg application
* @return {object} shopProduct model
*/
module.exports = app => {
const { BIGINT, STRING, TINYINT, DECIMAL, INTEGER, JSON, DATE } = app.Sequelize;
const ShopProduct = app.model.define('shop_product', {
id: { type: BIGINT.UNSIGNED, primaryKey: true, autoIncrement: true, comment: '主键' },
principal_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属主体ID' },
subsidiary_id: { type: BIGINT.UNSIGNED, comment: '所属货主(下属公司ID),跟随店铺' },
shop_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属店铺ID' },
outer_item_id: { type: STRING(100), allowNull: false, comment: '平台商品ID' },
outer_sku_id: { type: STRING(100), comment: '平台规格ID(SKU),无规格时为空' },
outer_sku_code: { type: STRING(64), comment: '店管家规格编码(平台 SKU 的 SkuCode),SkuId 不可用时的匹配键' },
title: { type: STRING(255), comment: '商品标题' },
outer_code: { type: STRING(100), comment: '商家编码,用于与系统商品匹配' },
spec: { type: STRING(255), comment: '规格' },
image: { type: STRING(255), comment: '图片' },
price: { type: DECIMAL(12, 2), comment: '店铺售价' },
stock: { type: INTEGER, allowNull: false, defaultValue: 0, comment: '采集到的库存' },
status: { type: TINYINT, allowNull: false, defaultValue: 1, comment: '店铺商品状态:1在售 0下架' },
platform_type: { type: STRING(32), comment: '平台类型:系统预设平台编码(PDD/DYXD/SPH…)' },
external_shop_id: { type: STRING(64), comment: '外部平台的店铺ID(如店管家 ShopId)' },
bind_type: { type: TINYINT, allowNull: false, defaultValue: 0, comment: '绑定类型:0未绑定 1系统商品 2组合商品' },
product_id: { type: BIGINT.UNSIGNED, comment: '绑定的系统商品ID,未绑定为空' },
combo_product_id: { type: BIGINT.UNSIGNED, comment: '绑定的组合商品ID,未绑定为空' },
raw: { type: JSON, comment: '采集到的原始数据' },
synced_at: { type: DATE, comment: '最近一次采集时间' },
created_at: { type: DATE, comment: '创建时间' },
updated_at: { type: DATE, comment: '更新时间' },
});
ShopProduct.associate = () => {
app.model.ShopProduct.belongsTo(app.model.Principal, { foreignKey: 'principal_id', as: 'principal' });
app.model.ShopProduct.belongsTo(app.model.PrincipalSubsidiary, { foreignKey: 'subsidiary_id', as: 'subsidiary' });
app.model.ShopProduct.belongsTo(app.model.Shop, { foreignKey: 'shop_id', as: 'shop' });
app.model.ShopProduct.belongsTo(app.model.Product, { foreignKey: 'product_id', as: 'product' });
app.model.ShopProduct.belongsTo(app.model.ComboProduct, { foreignKey: 'combo_product_id', as: 'comboProduct' });
};
return ShopProduct;
};