oms/backend/app/lib/orderFields.js
liufumingren 2d2f781f09 feat(order): 订单关联货主与越界操作拦截
- order_index 与各订单状态表新增 subsidiary_id(跟随店铺货主),历史数据按店铺回填
- 订单列表/详情/导出按货主范围过滤,货主权限仅可见自己所属公司的订单
- 订单 id 级操作统一在 _loadIndexRows 校验货主:审核/推送/驳回/取消/作废/恢复/改仓库物流/删除/明细维护越界返回 403
- 新建订单货主跟随店铺,货主权限只能为自己公司店铺建单;平台拉单写入时同步店铺货主
- 内部任务/平台回调无登录态时按系统操作处理,不受货主范围限制
- 订单列表与导出新增货主列

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-05 02:15:46 +08:00

300 lines
18 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';
/**
* 订单状态表 / 历史表 / 明细表的字段口径(唯一来源,供模型与迁移共用)。
*
* 设计口径:新表结构是「原订单表 orders + 发货单 order_deliveries(含其明细表)」的**超集**——
* 老表任一个字段都能在新表找到同名、同类型的落点,保证老数据可以无损迁入(不允许再出现字段丢失)。
*
* 字段分三组:
* 1) CORE 状态流程核心字段(order_no/parent_order_no/shop_id/product_info/receiver/address/status/remark)
* 2) LEGACY_ORDER 原订单表 orders 的字段(order_type、wms_ 系列、submit_ 系列、total_amount、pay_amount)
* 3) LEGACY_DELIVERY 发货单 order_deliveries 的字段(仓库/物流/收件人 12 项/金额 6 项/推送与审核作废痕迹等)
*
* 老字段 → 新字段的映射(不能同名落地的):
* - orders.order_no、order_deliveries.delivery_order_code → order_no
* - orders.receiver(JSON { name, phone, address })→ receiver / tel / mobile / address
* - orders.status(1待处理 2处理中 3已完成 4已取消)与 order_deliveries.status
* (1待处理 2已审核 3已推送 4已完成 5已作废 9已取消)→ status(订单流程状态,见 app/lib/orderStatus.js)
* - orders.id、order_deliveries.id → id(= order_index.id)
* - order_deliveries.name → receiver;detail_address 保留同名列,address 为省市区镇+详细地址的汇总快照
* - 明细:order_items.name → item_name,order_delivery_items.order_delivery_id / order_items.order_id → order_id
*/
/**
* 状态流程核心字段(状态表与历史表共用)
* @param {Object} Sequelize sequelize 构造函数
* @param {String} status 该表的默认状态
* @return {Object} 字段定义
*/
function coreColumns(Sequelize, status) {
const { BIGINT, STRING, JSON: JsonType } = Sequelize;
return {
order_no: { type: STRING(50), allowNull: false, comment: '订单号(老 order_no / delivery_order_code)' },
parent_order_no: { type: STRING(50), comment: '来源订单号(外部平台唯一单号;店管家为系统编号 LogicOrderId)' },
principal_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属主体ID(主体隔离)' },
subsidiary_id: { type: BIGINT.UNSIGNED, comment: '所属货主(下属公司ID),跟随店铺' },
shop_id: { type: BIGINT.UNSIGNED, comment: '店铺ID' },
product_info: { type: JsonType, comment: '商品明细快照(JSON 数组;结构化明细见 order_status_items)' },
receiver: { type: STRING(50), comment: '收货人(老 orders.receiver.name / order_deliveries.name)' },
address: { type: STRING(255), comment: '收货地址(省市区镇 + 详细地址的汇总快照)' },
status: {
type: STRING(16),
allowNull: false,
defaultValue: status,
comment: '流程状态:pending 待审核 / exception 异常 / outsourcing 委外中 / delivery 配送 / history 历史',
},
remark: { type: STRING(255), comment: '备注 / 异常原因' },
};
}
/**
* 原订单表 orders 的字段(超集,保证老数据可无损迁入)
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 字段定义
*/
function orderLegacyColumns(Sequelize) {
const { STRING, TINYINT, DECIMAL, DATE } = Sequelize;
return {
order_type: { type: STRING(50), allowNull: false, defaultValue: 'JYCK', comment: '订单类型(老 orders.order_type 数值转为字符串;发货单口径为 WMS 编码 JYCK/HHCK/BFCK)' },
wms_status: { type: STRING(50), comment: '外部WMS状态(如 CREATED/PICKING/SHIPPED,由回调回写)' },
wms_order_no: { type: STRING(50), comment: '外部WMS单号' },
wms_synced_at: { type: DATE, comment: '最近一次 WMS 同步时间' },
submit_status: { type: TINYINT, allowNull: false, defaultValue: 0, comment: '上交状态:0未上交 1上交中 2已上交 3上交失败' },
submit_order_no: { type: STRING(50), comment: '上交后返回的单号' },
submit_at: { type: DATE, comment: '上交时间' },
submit_msg: { type: STRING(255), comment: '上交结果 / 失败原因' },
total_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '订单总金额' },
pay_amount: { type: DECIMAL(12, 2), comment: '实付金额' },
legacy_status: { type: TINYINT.UNSIGNED, comment: '迁移来源的老状态原值(orders.status / order_deliveries.status);流程状态见 status' },
};
}
/**
* 发货单 order_deliveries 的字段(超集,保证老数据可无损迁入)
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 字段定义
*/
function deliveryLegacyColumns(Sequelize) {
const { BIGINT, STRING, TEXT, TINYINT, INTEGER, DECIMAL, DATE, ENUM, JSON: JsonType } = Sequelize;
return {
warehouse_id: { type: BIGINT.UNSIGNED, comment: '仓库ID(关联 warehouses)' },
warehouse_code: { type: STRING(50), comment: '仓库编码(报文 warehouseCode 快照)' },
order_flag: { type: TEXT, comment: '订单标记' },
pre_delivery_order_code: { type: STRING(50), comment: '预发货单号' },
pre_delivery_order_id: { type: STRING(50), comment: '预发货单ID' },
is_urgency: { type: ENUM('Y', 'N'), defaultValue: 'N', comment: '是否紧急:Y是 N否(默认 N)' },
invoice_flag: { type: ENUM('Y', 'N'), defaultValue: 'N', comment: '是否需要发票:Y是 N否(默认 N)' },
source_platform_code: { type: STRING(50), comment: '来源平台编码' },
source_platform_name: { type: STRING(200), comment: '来源平台名称' },
shop_nick: { type: STRING(200), comment: '店铺名称' },
seller_nick: { type: STRING(200), comment: '卖家名称' },
buyer_nick: { type: STRING(200), comment: '买家名称' },
buyer_message: { type: STRING(500), comment: '买家留言' },
seller_message: { type: STRING(500), comment: '卖家留言' },
place_order_time: { type: DATE, comment: '店铺创建订单时间' },
pay_time: { type: DATE, comment: '支付时间' },
pay_no: { type: STRING(50), comment: '支付单号' },
operate_time: { type: DATE, comment: 'ERP 审核时间' },
item_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '商品总金额(按明细派生)' },
discount_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '订单折扣金额' },
freight: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '快递费用' },
ar_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '应收金额' },
got_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '已收金额' },
logistics_code: { type: STRING(50), comment: '物流公司编码(WMS 标准编码,如 SF/YTO)' },
logistics_name: { type: STRING(200), comment: '物流公司名称' },
express_code: { type: STRING(50), comment: '运单号(面单号)' },
express_codes: { type: JsonType, comment: '运单号集合(多包裹)' },
logistics_area_code: { type: STRING(50), comment: '快递区域编码' },
operator_name: { type: STRING(50), comment: '操作人 / 审核人(奇门 operatorName)' },
oaid_order_source_code: { type: STRING(50), comment: 'OAID 订单来源编码' },
source_order_codes: { type: TEXT, comment: '来源订单号集合' },
extend_props: { type: JsonType, comment: '请求级扩展属性(如 ewaybill_order_code)' },
need_oms_waybill: { type: TINYINT, allowNull: false, defaultValue: 0, comment: '是否需要回 OMS 取号:0否 1是' },
tel: { type: STRING(50), comment: '收货人电话' },
mobile: { type: STRING(50), comment: '收货人手机号' },
email: { type: STRING(100), comment: '收货人邮箱' },
country_code: { type: STRING(50), comment: '国家编码' },
province: { type: STRING(100), comment: '省份' },
city: { type: STRING(100), comment: '城市' },
area: { type: STRING(100), comment: '区县' },
town: { type: STRING(100), comment: '街道/镇' },
detail_address: { type: STRING(500), comment: '详细地址' },
zip_code: { type: STRING(20), comment: '邮编' },
oaid: { type: STRING(100), comment: 'OAID' },
stock_locked: { type: TINYINT.UNSIGNED, allowNull: false, defaultValue: 0, comment: '库存锁定标记:1=当前占用锁定库存 0=未锁定' },
shop_sync_status: { type: TINYINT.UNSIGNED, allowNull: false, defaultValue: 0, comment: '回传平台店铺状态:0未同步 1同步中 2同步成功 3同步失败' },
shop_sync_retry_count: { type: INTEGER.UNSIGNED, allowNull: false, defaultValue: 0, comment: '回传平台店铺的重试次数' },
created_by: { type: BIGINT.UNSIGNED, comment: '创建人ID(关联 users)' },
pushed_at: { type: DATE, comment: 'WMS 推送时间' },
wms_push_result: { type: JsonType, comment: 'WMS 推送结果(外部单号/返回码/消息/原始报文)' },
legacy_order_id: { type: BIGINT.UNSIGNED, comment: '迁移来源主键(orders.id / order_deliveries.id;非迁移数据为 NULL)' },
legacy_source: { type: STRING(30), comment: '迁移来源表:orders / order_deliveries(非迁移数据为 NULL)' },
item_total: { type: DECIMAL(12, 3), allowNull: false, defaultValue: 0, comment: '商品总数(明细计划数量合计)' },
item_kind: { type: INTEGER.UNSIGNED, allowNull: false, defaultValue: 0, comment: '商品种类数(不重复商品数)' },
audit_by: { type: BIGINT.UNSIGNED, comment: '审核人ID(关联 users)' },
audit_time: { type: DATE, comment: '审核时间' },
void_at: { type: DATE, comment: '作废时间' },
void_by: { type: BIGINT.UNSIGNED, comment: '作废人ID(关联 users)' },
void_reason: { type: STRING(255), comment: '作废原因' },
};
}
/**
* 外部打单平台「同步发货」状态字段(进入配送后需要把发货结果同步回店管家等平台)
*
* 与 `shop_sync_*`(回传平台店铺)分开:这里是**发货结果**同步,
* 失败按次数重试,超过上限转「异常」等人工批量重试。
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 字段定义
*/
function externalSendColumns(Sequelize) {
const { STRING, TINYINT, INTEGER, DATE } = Sequelize;
return {
external_send_status: {
type: TINYINT.UNSIGNED,
allowNull: false,
defaultValue: 0,
comment: '同步外部发货状态:0待同步 1已同步 2同步失败待重试 3异常(重试超限,等人工处理)',
},
external_send_times: { type: INTEGER.UNSIGNED, allowNull: false, defaultValue: 0, comment: '同步外部发货已尝试次数' },
external_send_error: { type: STRING(500), comment: '最近一次同步失败原因' },
external_send_at: { type: DATE, comment: '最近一次同步尝试时间' },
external_sent_at: { type: DATE, comment: '同步成功时间' },
};
}
/**
* 状态表 / 历史表的业务字段(CORE + 两套老表字段 = 老表超集)
* @param {Object} Sequelize sequelize 构造函数(模型文件里的 app.Sequelize)
* @param {String} status 该表的默认状态(pending/exception/outsourcing/delivery/history)
* @return {Object} 模型字段定义
*/
function businessFields(Sequelize, status) {
const { BIGINT, DATE } = Sequelize;
return {
id: { type: BIGINT.UNSIGNED, primaryKey: true, comment: '主键(= order_index.id,全局唯一,订单 ID 以此为准)' },
...coreColumns(Sequelize, status),
...orderLegacyColumns(Sequelize),
...deliveryLegacyColumns(Sequelize),
...externalSendColumns(Sequelize),
created_at: { type: DATE, comment: '创建时间(用于超 3 个月自动归档判断)' },
updated_at: { type: DATE, comment: '更新时间' },
};
}
/**
* 历史表额外字段:归档时间与转入时间
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 模型字段定义
*/
function historyFields(Sequelize) {
const { DATE } = Sequelize;
return {
archived_at: { type: DATE, comment: '归档时间(转入历史表时写入)' },
transferred_at: { type: DATE, comment: '转入时间(状态流转进入历史的时间)' },
};
}
/**
* 明细表 order_status_items 的字段(order_delivery_items ∪ order_items 的超集)
*
* 5 张状态表共用这一张明细表(按 order_id 关联,订单 ID 取自 order_index),
* 老的两套明细字段都保留,保证无字段丢失;落地时按需填充(发货单口径字段优先)。
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 模型字段定义
*/
function itemFields(Sequelize) {
const { BIGINT, STRING, TINYINT, DECIMAL, DATE, JSON: JsonType } = Sequelize;
return {
id: { type: BIGINT.UNSIGNED, primaryKey: true, autoIncrement: true, comment: '主键' },
principal_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属主体ID(主体隔离)' },
order_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '订单ID(= order_index.id / 状态表 id)' },
shop_id: { type: BIGINT.UNSIGNED, comment: '店铺ID(老 order_items.shop_id)' },
order_line_no: { type: STRING(20), allowNull: false, defaultValue: '', comment: '行号(老 order_delivery_items.order_line_no,缺省按行序号)' },
item_id: { type: BIGINT.UNSIGNED, comment: 'WMS 内部商品ID(取自 warehouse_products.wms_item_id)' },
item_code: { type: STRING(100), comment: '商品编码' },
item_name: { type: STRING(255), comment: '商品名称(老 order_items.name)' },
plan_qty: { type: DECIMAL(12, 3), allowNull: false, defaultValue: 0, comment: '计划数量(发货单口径)' },
actual_price: { type: DECIMAL(12, 2), comment: '实际单价(发货单口径)' },
retail_price: { type: DECIMAL(12, 2), comment: '零售价' },
ext_code: { type: STRING(100), comment: '外部编码(extCode)' },
inventory_type: { type: STRING(10), allowNull: false, defaultValue: 'ZP', comment: '库存类型:ZP=正品 CC=残次' },
source_order_code: { type: STRING(50), comment: '来源订单编码' },
sub_source_order_code: { type: STRING(50), comment: '子来源订单编码' },
stock_id: { type: BIGINT.UNSIGNED, comment: 'WMS 仓库库存ID(stockId)' },
extend_props: { type: JsonType, comment: '扩展属性(extendProps)' },
legacy_order_item_id: { type: BIGINT.UNSIGNED, comment: '迁移来源明细主键(order_items.id / order_delivery_items.id)' },
legacy_source: { type: STRING(30), comment: '迁移来源表:order_items / order_delivery_items(非迁移数据为 NULL)' },
product_id: { type: BIGINT.UNSIGNED, comment: '系统商品ID(老 order_items.product_id)' },
combo_product_id: { type: BIGINT.UNSIGNED, comment: '组合商品ID(老 order_items.combo_product_id)' },
shop_product_id: { type: BIGINT.UNSIGNED, comment: '命中的店铺商品ID(老 order_items.shop_product_id)' },
item_type: { type: TINYINT, allowNull: false, defaultValue: 0, comment: '明细类型(老 order_items.item_type):0未识别 1普通商品 2组合商品' },
parse_status: { type: TINYINT, allowNull: false, defaultValue: 0, comment: '解析状态(老 order_items.parse_status):0未解析 1已解析 2解析失败' },
parse_msg: { type: STRING(255), comment: '解析失败原因(老 order_items.parse_msg)' },
outer_item_id: { type: STRING(100), comment: '平台商品ID(老 order_items.outer_item_id)' },
outer_sku_id: { type: STRING(100), comment: '平台规格ID(老 order_items.outer_sku_id)' },
sku_code: { type: STRING(100), comment: '商家编码(老 order_items.sku_code)' },
spec: { type: STRING(255), comment: '规格(快照)' },
image: { type: STRING(255), comment: '图片(快照)' },
quantity: { type: DECIMAL(12, 3), allowNull: false, defaultValue: 0, comment: '数量(老 order_items.quantity)' },
price: { type: DECIMAL(12, 2), comment: '单价(老 order_items.price)' },
total_amount: { type: DECIMAL(12, 2), allowNull: false, defaultValue: 0, comment: '明细金额(老 order_items.total_amount)' },
raw: { type: JsonType, comment: '平台返回的明细原始数据(老 order_items.raw)' },
platform_snapshot: { type: JsonType, comment: '平台明细快照(绑定前的编码/名称/规格/图片,用于解绑还原)' },
created_at: { type: DATE, comment: '创建时间' },
updated_at: { type: DATE, comment: '更新时间' },
};
}
/** 流转搬运时排除的字段(id 由索引表决定,status 由目标状态决定,时间戳单独处理) */
const COPY_EXCLUDE = [ 'id', 'status', 'created_at', 'updated_at' ];
/**
* 流转时需要整体搬运的业务列名(除 id/status/created_at/updated_at 外的全部业务列)
* 由字段定义派生,避免新增字段时漏搬。
* @param {Object} Sequelize sequelize 构造函数
* @return {Array<String>} 列名数组
*/
function copyColumnNames(Sequelize) {
return Object.keys(businessFields(Sequelize, 'pending')).filter(name => !COPY_EXCLUDE.includes(name));
}
/**
* 相对「状态流程核心字段」扩展出来的列名(= 为容纳老表而新增的字段),供迁移 down 精确回退
* @param {Object} Sequelize sequelize 构造函数
* @return {Array<String>} 列名数组
*/
function legacyExtraColumnNames(Sequelize) {
return [
...Object.keys(orderLegacyColumns(Sequelize)),
...Object.keys(deliveryLegacyColumns(Sequelize)),
].filter((name, index, arr) => arr.indexOf(name) === index);
}
/**
* 非空且有默认值的业务列名(如 total_amount / item_amount / discount_amount / freight 等金额列)
*
* 用途:请求里显式传 null 时跳过该列,落库由默认值兜底(否则会触发 notNull Violation)。
* @param {Object} Sequelize sequelize 构造函数
* @return {Array<String>} 列名数组
*/
function notNullDefaultColumnNames(Sequelize) {
const fields = businessFields(Sequelize, 'pending');
return Object.keys(fields).filter(name => fields[name].allowNull === false && fields[name].defaultValue !== undefined);
}
module.exports = {
coreColumns,
orderLegacyColumns,
deliveryLegacyColumns,
externalSendColumns,
businessFields,
historyFields,
itemFields,
copyColumnNames,
legacyExtraColumnNames,
notNullDefaultColumnNames,
COPY_EXCLUDE,
};