oms/backend/app/model/shopDatabaseConfig.js
liufumingren 797bbbcd82 feat(shop): 数据库店铺商品表/订单表支持拉取状态过滤(可填多个状态值)
- shop_database_configs 增加 product_filter_column/values、order_filter_column/values
- 配置弹窗在商品表、订单表下新增「状态字段 + 状态值」:状态字段选源表列,状态值可填多个(回车追加)
- 拉取时按状态列 IN (多个状态值) 过滤,订单表与时间窗条件同时生效;未配置状态列表示不过滤
- 保存时校验状态列存在、填了状态列必须至少填一个状态值(最多 100 个)
- 修复 DATASETS 数据集缺失 key 导致过滤校验窜到订单明细表的问题

Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
2026-11-05 16:54:36 +08:00

58 lines
3.8 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';
/**
* 数据库店铺(shops.type=3)连接配置与表/字段映射,一店一条
*
* 密码存储明文(与仓库接口 app_secret 口径一致),查询接口不回显。
* 字段映射以「OMS 字段 → 源表列名」的形式存放在三个 JSON 列中,
* 具体可选字段见 app/lib/shopDatabaseFields.js。
* @param {Egg.Application} app egg application
* @return {object} shop_database_config model
*/
module.exports = app => {
const { BIGINT, STRING, TINYINT, INTEGER, DATE, JSON: JSONTYPE } = app.Sequelize;
const ShopDatabaseConfig = app.model.define('shop_database_config', {
id: { type: BIGINT.UNSIGNED, primaryKey: true, autoIncrement: true, comment: '主键' },
principal_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '所属主体ID' },
shop_id: { type: BIGINT.UNSIGNED, allowNull: false, comment: '店铺ID(shops.id,type=3 数据库店铺)' },
db_type: { type: STRING(20), allowNull: false, defaultValue: 'mysql', comment: '数据库类型:mysql' },
host: { type: STRING(255), comment: '数据库地址(IP 或域名)' },
port: { type: INTEGER, comment: '数据库端口' },
database_name: { type: STRING(100), comment: '数据库名' },
username: { type: STRING(100), comment: '数据库账号' },
password: { type: STRING(255), comment: '数据库密码(明文,接口不回显)' },
product_table: { type: STRING(100), comment: '商品表名' },
order_table: { type: STRING(100), comment: '订单表名' },
order_item_table: { type: STRING(100), comment: '订单明细表名' },
product_fields: { type: JSONTYPE, comment: '商品表字段映射:{ oms字段: 源列名 }' },
order_fields: { type: JSONTYPE, comment: '订单表字段映射:{ oms字段: 源列名 }' },
order_item_fields: { type: JSONTYPE, comment: '订单明细表字段映射:{ oms字段: 源列名 }' },
product_filter_column: { type: STRING(100), comment: '商品表拉取过滤:状态列名(空表示不过滤)' },
product_filter_values: { type: JSONTYPE, comment: '商品表拉取过滤:允许的状态值(数组)' },
order_filter_column: { type: STRING(100), comment: '订单表拉取过滤:状态列名(空表示不过滤)' },
order_filter_values: { type: JSONTYPE, comment: '订单表拉取过滤:允许的状态值(数组)' },
callback_mode: { type: TINYINT.UNSIGNED, allowNull: false, defaultValue: 0, comment: '回传单号方式:0不回传 1写回字段 2写回行' },
callback_table: { type: STRING(100), comment: '回传单号的目标表名' },
callback_field: { type: STRING(100), comment: '写回字段方式:运单号写入的列名' },
callback_key_field: { type: STRING(100), comment: '写回字段方式:定位行的列名(一般是订单号列)' },
callback_separator: { type: STRING(10), comment: '写回字段方式:多包裹运单号的间隔符' },
callback_fields: { type: JSONTYPE, comment: '写回行方式:{ oms回传值: 目标列名 }' },
last_test_at: { type: DATE, comment: '最近一次测试连通时间' },
last_test_success: { type: TINYINT, comment: '最近一次测试结果:1成功 0失败' },
last_test_message: { type: STRING(500), comment: '最近一次测试结果说明' },
created_at: { type: DATE, comment: '创建时间' },
updated_at: { type: DATE, comment: '更新时间' },
}, {
tableName: 'shop_database_configs',
comment: '数据库店铺连接与表/字段映射配置',
});
ShopDatabaseConfig.associate = () => {
app.model.ShopDatabaseConfig.belongsTo(app.model.Principal, { foreignKey: 'principal_id', as: 'principal' });
app.model.ShopDatabaseConfig.belongsTo(app.model.Shop, { foreignKey: 'shop_id', as: 'shop' });
};
return ShopDatabaseConfig;
};