oms/backend/database/orderStatusTables.js
liufumingren 6c03a20364 feat(backend): 订单状态分表、WMS 推送与归档
- 新增 order_index 索引表 + 6 张同构状态表(待审核/异常/委外中/配送/已作废/已取消)与历史表,
  订单 ID 全局唯一,状态由业务事务推动:审核、驳回、取消、作废、恢复、归档
- 明细表 order_status_items、收件人文本智能解析、批量切换仓库/店铺并刷新快照
- WMS deliveryorder.create 推送(单条/批量)、推送失败定时重试、订单归档定时任务
- 迁移:建表、旧发货单数据迁入新表、菜单合并与迁移记录清理
- routes / swagger contract 补充订单状态接口

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-10-30 23:06:18 +08:00

389 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 {
ORDER_TABLE_PREFIX,
ORDER_INDEX_TABLE,
ORDER_ITEM_TABLE,
ORDER_TABLE_BY_STATUS,
ORDER_STATUS_ORDER,
ORDER_STATUS_LABELS,
ORDER_STATUS,
} = require('../app/lib/orderStatus');
const {
businessFields,
historyFields,
itemFields,
legacyExtraColumnNames,
} = require('../app/lib/orderFields');
/**
* 订单(状态分表)建表 / 改表 DDL(唯一来源,供迁移复用)。
*
* 表结构:
* - order_index 订单索引表:订单 ID 以本表为准,current_table/current_status 记录当前归属表与状态
* - order_status_pending 待审核
* - order_status_queued 待推送(审核通过,等推送仓库)
* - order_status_exception 异常
* - order_status_outsourcing 委外中
* - order_status_delivery 配送
* - order_status_void 已作废(侧路)
* - order_status_canceled 已取消(侧路)
* - order_status_history 历史(多 archived_at / transferred_at)
* - order_status_items 明细表(各状态表共用)
*
* 结构口径:各状态表字段完全一致,且是「原订单表 orders + 发货单 order_deliveries(含明细)」
* 的**超集**(字段定义见 app/lib/orderFields.js),老数据可无损迁入。
* 流转 = 来源表删除 + 目标表写入 + 更新索引表 current_table/current_status(明细按订单 ID 关联,不搬迁)。
*/
/** 状态表的建表定义(由状态枚举派生,新增状态自动包含) */
const STATUS_TABLES = ORDER_STATUS_ORDER.map(status => ({
table: ORDER_TABLE_BY_STATUS[status],
status,
label: status === ORDER_STATUS.HISTORY ? `订单-${ORDER_STATUS_LABELS[status]}(归档)` : `订单-${ORDER_STATUS_LABELS[status]}`,
}));
/** 状态值 → 中文(建表注释用) */
const STATUS_COMMENT = `状态:${ORDER_STATUS_ORDER.map(status => `${status} ${ORDER_STATUS_LABELS[status]}`).join(' / ')}`;
/**
* 当前数据库已存在的表名集合(小写)
* @param {Object} queryInterface sequelize queryInterface
* @return {Promise<Set<String>>} 表名集合
*/
async function existingTables(queryInterface) {
const list = await queryInterface.showAllTables();
return new Set(
(list || []).map(item => String(item && item.tableName ? item.tableName : item).toLowerCase())
);
}
/**
* 索引表字段定义
* @param {Object} Sequelize sequelize 构造函数
* @return {Object} 字段定义
*/
function indexFields(Sequelize) {
const { BIGINT, STRING, DATE } = Sequelize;
return {
id: {
type: BIGINT.UNSIGNED,
allowNull: false,
autoIncrement: true,
primaryKey: true,
comment: '主键(订单全局唯一 ID)',
},
order_no: { type: STRING(50), allowNull: false, comment: '订单号' },
parent_order_no: { type: STRING(50), comment: '来源订单号(上游/平台订单号)' },
principal_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属主体ID(主体隔离)' },
current_table: {
type: STRING(16),
allowNull: false,
defaultValue: 'pending',
comment: '当前所在表:pending/queued/exception/outsourcing/delivery/void/canceled/history(取值同 current_status)',
},
current_status: {
type: STRING(16),
allowNull: false,
defaultValue: 'pending',
comment: STATUS_COMMENT,
},
created_at: {
type: DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
comment: '创建时间(用于超 3 个月自动归档判断)',
},
updated_at: {
type: DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
comment: '更新时间',
},
};
}
/**
* 建表公共选项
* @return {Object} createTable 选项
*/
function tableOptions() {
return { charset: 'utf8mb4', collate: 'utf8mb4_unicode_ci' };
}
/**
* 补齐已存在表的缺失列(幂等):新增字段只需加到 app/lib/orderFields.js 即可,无需手写 ALTER
* @param {Object} queryInterface sequelize queryInterface
* @param {String} table 表名
* @param {Object} defs 字段定义(name → 定义)
* @return {Promise<Array<String>>} 本次新增的列名
*/
async function syncTableColumns(queryInterface, table, defs) {
let columns;
try {
columns = await queryInterface.describeTable(table);
} catch (e) {
return []; // 表不存在,由建表逻辑负责
}
const added = [];
for (const [ name, def ] of Object.entries(defs)) {
if (columns[name]) continue;
await queryInterface.addColumn(table, name, def);
added.push(name);
}
if (added.length) {
console.log(`[order] ${table} 补齐 ${added.length} 列:${added.join(', ')}`);
}
return added;
}
/**
* 清理「最早一版设计」的残留表(单活动表 + bucket 索引表)
* 注:拆单命名时代的状态表(split_order_pending 等)**不在此清理**,由重命名逻辑就地改名,避免丢数据。
* @param {Object} queryInterface sequelize queryInterface
* @return {Promise<void>} 无
*/
async function dropLegacyTables(queryInterface) {
const tables = await existingTables(queryInterface);
const legacyHints = [
{ table: 'split_order_active', dropAlways: true },
{ table: 'split_order_index', keys: [ 'current_table', 'current_status' ] },
{ table: 'split_order_history', keys: [ 'archived_at', 'transferred_at' ] },
];
for (const hint of legacyHints) {
if (!tables.has(hint.table)) continue;
if (!hint.dropAlways) {
const columns = await queryInterface.describeTable(hint.table);
const isNewStructure = hint.keys.every(key => columns[key]);
if (isNewStructure) continue;
}
const [[ count ]] = await queryInterface.sequelize.query(
`SELECT COUNT(*) AS total FROM \`${hint.table}\``
);
await queryInterface.dropTable(hint.table);
console.log(`[order] 已删除旧设计残留表 ${hint.table}(原有 ${count ? count.total : 0} 条数据,旧设计与新结构不兼容)`);
}
}
/**
* 旧命名表名(拆单命名时代):order_status_pending → split_order_pending
* @param {String} table 新表名
* @return {String} 旧表名
*/
function legacyTableNameOf(table) {
return table.startsWith(ORDER_TABLE_PREFIX)
? table.replace(ORDER_TABLE_PREFIX, 'split_order_')
: table;
}
/**
* 把状态表在「旧命名(拆单命名时代)」与「当前命名」之间就地改名(保留数据与结构)
* @param {Object} queryInterface sequelize queryInterface
* @param {Object} [options] 选项
* @param {Boolean} [options.reverse] true 表示反向改名(新 → 旧,供迁移 down 使用)
* @return {Promise<void>} 无
*/
async function renameLegacyTables(queryInterface, options = {}) {
const reverse = Boolean(options.reverse);
const tables = await existingTables(queryInterface);
for (const def of STATUS_TABLES) {
const from = reverse ? def.table : legacyTableNameOf(def.table);
const to = reverse ? legacyTableNameOf(def.table) : def.table;
if (tables.has(from) && !tables.has(to)) {
await queryInterface.renameTable(from, to);
console.log(`[order] 已重命名表 ${from} → ${to}`);
} else if (tables.has(to) && tables.has(from)) {
console.log(`[order] 表 ${to} 与 ${from} 同时存在,跳过重命名(请人工确认)`);
} else if (!tables.has(to)) {
continue;
}
await renameLegacyIndexes(queryInterface, to, reverse);
}
}
/**
* 把表上仍是「旧命名」的索引改名(索引名同样带状态表名前缀)
* 说明:不同 Sequelize 小版本不一定有 queryInterface.renameIndex,这里统一用 remove + add 实现
* @param {Object} queryInterface sequelize queryInterface
* @param {String} table 表名
* @param {Boolean} reverse true 表示反向改名(新 → 旧)
* @return {Promise<void>} 无
*/
async function renameLegacyIndexes(queryInterface, table, reverse) {
const fromToken = reverse ? '_order_status_' : '_split_order_';
const toToken = reverse ? '_split_order_' : '_order_status_';
const indexes = await queryInterface.showIndex(table);
const names = new Set(indexes.map(item => item.name));
for (const idx of indexes) {
if (!idx.name || idx.name.indexOf(fromToken) === -1) continue;
const newName = idx.name.replace(fromToken, toToken);
if (names.has(newName)) continue;
const fields = (idx.fields || []).map(field => field.attribute).filter(Boolean);
if (!fields.length) continue;
await queryInterface.removeIndex(table, idx.name);
await queryInterface.addIndex(table, { fields, unique: Boolean(idx.unique), name: newName });
names.delete(idx.name);
names.add(newName);
console.log(`[order] 已重命名索引 ${idx.name} → ${newName}(表 ${table})`);
}
}
/**
* 创建订单索引表、5 张状态表与明细表(已存在则跳过,可重复执行)
* @param {Object} queryInterface sequelize queryInterface
* @param {Object} Sequelize sequelize 构造函数
* @return {Promise<void>} 无
*/
async function createTables(queryInterface, Sequelize) {
const tables = await existingTables(queryInterface);
const options = tableOptions();
// 1) 索引表
if (!tables.has(ORDER_INDEX_TABLE)) {
await queryInterface.createTable(ORDER_INDEX_TABLE, indexFields(Sequelize), {
...options,
comment: '订单索引表(订单 ID 以此为准)',
});
await queryInterface.addIndex(ORDER_INDEX_TABLE, {
fields: [ 'principal_id', 'order_no' ],
unique: true,
name: 'uk_order_index_principal_no',
});
await queryInterface.addIndex(ORDER_INDEX_TABLE, {
fields: [ 'principal_id', 'current_table', 'current_status' ],
name: 'idx_order_index_principal_table',
});
}
// 2) 5 张状态表
for (const def of STATUS_TABLES) {
if (tables.has(def.table)) continue;
// 兼容:旧命名表已存在(拆单命名时代)时跳过创建,交由重命名逻辑就地改名,避免与重命名冲突
if (tables.has(legacyTableNameOf(def.table))) {
console.log(`[order] 检测到旧命名表 ${legacyTableNameOf(def.table)},跳过创建 ${def.table}(由重命名处理)`);
continue;
}
const fields = { ...businessFields(Sequelize, def.status) };
if (def.table === ORDER_TABLE_BY_STATUS.history) Object.assign(fields, historyFields(Sequelize));
await queryInterface.createTable(def.table, fields, { ...options, comment: def.label });
await queryInterface.addIndex(def.table, {
fields: [ 'principal_id', 'status' ],
name: `idx_${def.table}_principal_status`,
});
await queryInterface.addIndex(def.table, {
fields: [ 'principal_id', 'order_no' ],
unique: true,
name: `uk_${def.table}_principal_no`,
});
await queryInterface.addIndex(def.table, {
fields: [ 'principal_id', 'created_at' ],
name: `idx_${def.table}_principal_created`,
});
}
// 3) 明细表(5 张状态表共用)
if (!tables.has(ORDER_ITEM_TABLE)) {
await queryInterface.createTable(ORDER_ITEM_TABLE, itemFields(Sequelize), {
...options,
comment: '订单明细(5 张状态表共用;字段为发货单明细 ∪ 原订单明细的超集)',
});
await queryInterface.addIndex(ORDER_ITEM_TABLE, {
fields: [ 'principal_id', 'order_id' ],
name: 'idx_order_status_items_principal_order',
});
await queryInterface.addIndex(ORDER_ITEM_TABLE, {
fields: [ 'order_id', 'order_line_no' ],
unique: true,
name: 'uk_order_status_items_line',
});
}
}
/**
* 补齐所有订单表的缺失列(表存在但字段是旧结构时用;新建字段只需改字段定义)
* @param {Object} queryInterface sequelize queryInterface
* @param {Object} Sequelize sequelize 构造函数
* @return {Promise<Array<String>>} 本次新增的列名合计
*/
async function syncColumns(queryInterface, Sequelize) {
const added = [];
for (const def of STATUS_TABLES) {
const fields = { ...businessFields(Sequelize, def.status) };
if (def.table === ORDER_TABLE_BY_STATUS.history) Object.assign(fields, historyFields(Sequelize));
added.push(...await syncTableColumns(queryInterface, def.table, fields));
}
added.push(...await syncTableColumns(queryInterface, ORDER_ITEM_TABLE, itemFields(Sequelize)));
return added;
}
/**
* 删除订单索引表、全部状态表与明细表
* @param {Object} queryInterface sequelize queryInterface
* @return {Promise<void>} 无
*/
async function dropTables(queryInterface) {
for (const table of [
ORDER_ITEM_TABLE,
...ORDER_STATUS_ORDER.map(status => ORDER_TABLE_BY_STATUS[status]).reverse(),
ORDER_INDEX_TABLE,
]) {
await queryInterface.dropTable(table);
}
}
/**
* 回退「为容纳老表而扩展的字段」:删除扩展列并移除明细表(保留状态流程核心字段)
* @param {Object} queryInterface sequelize queryInterface
* @param {Object} Sequelize sequelize 构造函数
* @return {Promise<void>} 无
*/
async function dropLegacyColumns(queryInterface, Sequelize) {
const tables = await existingTables(queryInterface);
const extras = legacyExtraColumnNames(Sequelize);
for (const def of STATUS_TABLES) {
if (!tables.has(def.table)) continue;
const columns = await queryInterface.describeTable(def.table);
for (const name of extras) {
if (!columns[name]) continue;
await queryInterface.removeColumn(def.table, name);
}
}
if (tables.has(ORDER_ITEM_TABLE)) await queryInterface.dropTable(ORDER_ITEM_TABLE);
}
/**
* 建立订单表结构(幂等,顺序很关键):
* 1. 旧命名(拆单命名时代)状态表就地在改名到当前命名(保留数据);
* 2. 清理更早的「单活动表」设计残留表;
* 3. 建缺失的表;
* 4. 补齐已存在表缺失的列(含为容纳老表而扩展的字段)。
* @param {Object} queryInterface sequelize queryInterface
* @param {Object} Sequelize sequelize 构造函数
* @return {Promise<void>} 无
*/
async function migrate(queryInterface, Sequelize) {
await renameLegacyTables(queryInterface);
await dropLegacyTables(queryInterface);
await createTables(queryInterface, Sequelize);
await syncColumns(queryInterface, Sequelize);
}
module.exports = {
STATUS_TABLES,
migrate,
createTables,
syncColumns,
syncTableColumns,
dropTables,
dropLegacyColumns,
renameLegacyTables,
legacyTableNameOf,
indexFields,
};