wuweihua
61 lines
4.3 KiB
SQL
61 lines
4.3 KiB
SQL
-- biz_rule: one row per product/country/customer dimension
|
||
SET NAMES utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS biz_rule (
|
||
rule_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '规则ID',
|
||
rule_name varchar(100) NOT NULL COMMENT '规则名称',
|
||
rule_type varchar(32) NOT NULL COMMENT '规则类型（字典 bms_biz_rule_type）',
|
||
product_code varchar(64) DEFAULT NULL COMMENT '产品代码（空=全部）',
|
||
country_code varchar(10) DEFAULT NULL COMMENT '国家ISO2（空=全部）',
|
||
customer_code varchar(64) DEFAULT NULL COMMENT '客户代码（空=全部）',
|
||
weight_min decimal(10,3) DEFAULT NULL COMMENT '重量段最小KG（含）',
|
||
weight_max decimal(10,3) DEFAULT NULL COMMENT '重量段最大KG（含）',
|
||
weight_source varchar(32) NOT NULL COMMENT '取值字段（字典 bms_biz_weight_source）',
|
||
side_sum_max decimal(10,2) DEFAULT NULL COMMENT '三边和上限（三边规则）',
|
||
status char(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1停用）',
|
||
remark varchar(500) DEFAULT NULL COMMENT '备注',
|
||
create_by varchar(64) DEFAULT '' COMMENT '创建者',
|
||
create_time datetime DEFAULT NULL COMMENT '创建时间',
|
||
update_by varchar(64) DEFAULT '' COMMENT '更新者',
|
||
update_time datetime DEFAULT NULL COMMENT '更新时间',
|
||
del_flag char(1) DEFAULT '0' COMMENT '删除标记（0存在 1删除）',
|
||
PRIMARY KEY (rule_id),
|
||
KEY idx_biz_rule_dim (product_code, country_code, customer_code),
|
||
KEY idx_biz_rule_type_status (rule_type, status),
|
||
KEY idx_biz_rule_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务规则配置表（产品/国家/客户维度）';
|
||
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
|
||
SELECT N'业务规则类型', 'bms_biz_rule_type', '0', 'admin', sysdate(), N'计费/业务规则类型'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'bms_biz_rule_type');
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 1, N'三边规则', 'THREE_SIDE', 'bms_biz_rule_type', '', 'primary', 'Y', '0', 'admin', sysdate(), N'三边和小于阈值时按取值字段取重'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'bms_biz_rule_type' AND dict_value = 'THREE_SIDE');
|
||
|
||
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
|
||
SELECT N'规则取重字段', 'bms_biz_weight_source', '0', 'admin', sysdate(), N'实重(收货重)/计费重'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'bms_biz_weight_source');
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 1, N'实重', 'RECEIVE_WEIGHT', 'bms_biz_weight_source', '', 'success', 'Y', '0', 'admin', sysdate(), N'收货重 TAB_ORDER.WEIGHT'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'bms_biz_weight_source' AND dict_value = 'RECEIVE_WEIGHT');
|
||
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 2, N'计费重', 'CHARGE_WEIGHT', 'bms_biz_weight_source', '', 'warning', 'N', '0', 'admin', sysdate(), N'上游计费重/体积重因子'
|
||
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'bms_biz_weight_source' AND dict_value = 'CHARGE_WEIGHT');
|
||
|
||
-- seed: one row per product
|
||
INSERT INTO biz_rule (rule_name, rule_type, product_code, country_code, customer_code, weight_min, weight_max, weight_source, side_sum_max, status, remark, create_by, create_time, del_flag)
|
||
SELECT N'WSP三边和<90取收货重', 'THREE_SIDE', v.product_code, NULL, NULL, NULL, NULL, 'RECEIVE_WEIGHT', 90.00, '0',
|
||
N'产品三边和小于90时以收货重为计费重', 'admin', sysdate(), '0'
|
||
FROM (
|
||
SELECT 'WSP081' AS product_code UNION ALL SELECT 'WSP075' UNION ALL SELECT 'WSP069'
|
||
UNION ALL SELECT 'WSP068' UNION ALL SELECT 'WSP067' UNION ALL SELECT 'WSP066'
|
||
) v
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM biz_rule r
|
||
WHERE r.del_flag = '0' AND r.rule_type = 'THREE_SIDE' AND r.product_code = v.product_code
|
||
AND r.country_code IS NULL AND r.customer_code IS NULL
|
||
);
|