From 365373f577d430e1625d03e8390e43a24984d6e9 Mon Sep 17 00:00:00 2001
From: xin <1099200748@qq.com>
Date: Wed, 05 Nov 2025 16:10:31 +0800
Subject: [PATCH] 基础运费-sql
---
oying-system/src/main/resources/sql/update-2025年9月29日.sql | 215 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 215 insertions(+), 0 deletions(-)
diff --git "a/oying-system/src/main/resources/sql/update-2025\345\271\2649\346\234\21029\346\227\245.sql" "b/oying-system/src/main/resources/sql/update-2025\345\271\2649\346\234\21029\346\227\245.sql"
new file mode 100644
index 0000000..6214b7e
--- /dev/null
+++ "b/oying-system/src/main/resources/sql/update-2025\345\271\2649\346\234\21029\346\227\245.sql"
@@ -0,0 +1,215 @@
+DROP TABLE IF EXISTS fee_city_levels;
+CREATE TABLE fee_city_levels
+(
+ city_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '城市ID',
+ city_name VARCHAR(500) NOT NULL UNIQUE comment '城市名称 (唯一)',
+ level CHAR(1) NOT NULL CHECK (level IN ('C', 'D', 'E')) comment '城市等级 (C/D/E)',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '城市等级表';
+
+DROP TABLE IF EXISTS fee_base_fees;
+CREATE TABLE fee_base_fees
+(
+ fee_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '运费ID',
+ level CHAR(1) NOT NULL CHECK (level IN ('C', 'D', 'E')) comment '城市等级',
+ category_type ENUM ('normal', 'special') NOT NULL comment '品类类型 (普通、特殊)',
+ fee_amount DECIMAL(6, 2) NOT NULL comment '基础运费金额',
+ effective_date DATE NOT NULL comment '生效日期',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '基础运费表';
+
+DROP TABLE IF EXISTS fee_distance_surcharge_rules;
+CREATE TABLE fee_distance_surcharge_rules
+(
+ rule_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '规则ID',
+ min_distance DECIMAL(6, 1) NOT NULL comment '最小距离 (公里)',
+ max_distance DECIMAL(6, 1) NOT NULL comment '最大距离 (公里)',
+ fee_per_km DECIMAL(6, 2) NOT NULL comment '每公里加价金额',
+ effective_date DATE NOT NULL comment '生效日期',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '距离加价规则表';
+
+DROP TABLE IF EXISTS fee_time_surcharge_rules;
+CREATE TABLE fee_time_surcharge_rules
+(
+ time_rule_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '时段规则ID',
+ start_time TIME NOT NULL comment '时段开始时间',
+ end_time TIME NOT NULL comment '时段结束时间',
+ surcharge_amount DECIMAL(6, 2) NOT NULL comment '加价金额',
+ effective_date DATE NOT NULL comment '生效日期',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '时段加价规则表';
+
+DROP TABLE IF EXISTS fee_special_categories;
+CREATE TABLE fee_special_categories
+(
+ category_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '品类ID',
+ category_name VARCHAR(500) NOT NULL UNIQUE comment '特殊品类名称(唯一)',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '特殊品类表';
+
+DROP TABLE IF EXISTS fee_weight_surcharge_rules;
+CREATE TABLE fee_weight_surcharge_rules
+(
+ weight_rule_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '重量规则ID',
+ base_weight DECIMAL(6, 1) NOT NULL comment '基础重量 (公斤)',
+ fee_per_kg DECIMAL(6, 2) NOT NULL comment '每公斤加价金额',
+ effective_date DATE NOT NULL comment '生效日期',
+ create_by varchar(255) null comment '创建者',
+ update_by varchar(255) null comment '更新者',
+ create_time datetime DEFAULT CURRENT_TIMESTAMP comment '创建日期',
+ update_time datetime DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
+) comment '重量加价规则表';
+
+DROP TABLE IF EXISTS fee_order_shipping_fees;
+CREATE TABLE fee_order_shipping_fees
+(
+ order_id bigint(20) PRIMARY KEY AUTO_INCREMENT comment '订单ID',
+ order_num VARCHAR(64) NOT NULL comment '订单编号',
+ city_id bigint(20) NOT NULL comment '城市ID',
+ city_name VARCHAR(500) NOT NULL comment '城市名称',
+ category_id bigint(20) comment '品类ID ',
+ category_name VARCHAR(500) NULL comment '特殊品类名称',
+ weight DECIMAL(6, 1) NOT NULL comment '重量 (公斤)',
+ distance DECIMAL(6, 1) NOT NULL comment '距离 (公里)',
+ order_time DATETIME NOT NULL comment '下单时间',
+ is_special_conditions BOOLEAN DEFAULT FALSE comment '是否特殊条件',
+ base_fee DECIMAL(6, 2) NOT NULL comment '基础运费',
+ weight_surcharge DECIMAL(6, 2) NOT NULL comment '重量加价',
+ distance_surcharge DECIMAL(6, 2) NOT NULL comment '距离加价',
+ time_surcharge DECIMAL(6, 2) NOT NULL comment '时段加价',
+ special_condition_surcharge DECIMAL(6, 2) DEFAULT 0.00 comment '特殊条件加价',
+ total_fee DECIMAL(6, 2) NOT NULL comment '总运费',
+ created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '创建时间'
+) comment '订单运费表';
+
+-- 1. 插入城市等级数据
+INSERT INTO fee_city_levels (city_name, level)
+VALUES ('西安', 'C'),
+ ('福州', 'C'),
+ ('上海', 'C'),
+ ('哈尔滨', 'C'),
+ ('杭州', 'C'),
+ ('北京', 'C'),
+ ('长春', 'C'),
+ ('珠海', 'C'),
+ ('重庆', 'C'),
+ ('舟山', 'C'),
+ ('郑州', 'D'),
+ ('大连', 'D'),
+ ('天津', 'D'),
+ ('长沙', 'D'),
+ ('中山', 'D'),
+ ('东莞', 'D'),
+ ('广州', 'D'),
+ ('贵阳', 'D'),
+ ('兰州', 'D'),
+ ('温州', 'D'),
+ ('厦门', 'D'),
+ ('惠州', 'D'),
+ ('深圳', 'D'),
+ ('青岛', 'E'),
+ ('邯郸', 'E'),
+ ('宿迁', 'E'),
+ ('湖州', 'E'),
+ ('绍兴', 'E'),
+ ('金华', 'E'),
+ ('衢州', 'E'),
+ ('台州', 'E'),
+ ('丽水', 'E'),
+ ('阜阳', 'E'),
+ ('三明', 'E'),
+ ('南平', 'E'),
+ ('龙岩', 'E'),
+ ('宁德', 'E'),
+ ('淄博', 'E'),
+ ('济宁', 'E'),
+ ('威海', 'E'),
+ ('十堰', 'E'),
+ ('泸州', 'E'),
+ ('绵阳', 'E'),
+ ('德阳', 'E'),
+ ('汉阳', 'E'),
+ ('襄阳', 'E'),
+ ('武汉', 'E'),
+ ('常德', 'E'),
+ ('沈阳', 'E');
+
+-- 2. 插入基础运费数据
+INSERT INTO fee_base_fees (level, category_type, fee_amount, effective_date)
+VALUES ('C', 'normal', 5.20, '2023-01-01'),
+ ('C', 'special', 6.70, '2023-01-01'),
+ ('D', 'normal', 4.70, '2023-01-01'),
+ ('D', 'special', 6.70, '2023-01-01'),
+ ('E', 'normal', 4.50, '2023-01-01'),
+ ('E', 'special', 6.00, '2023-01-01');
+
+-- 3. 插入距离加价规则数据
+INSERT INTO fee_distance_surcharge_rules (min_distance, max_distance, fee_per_km, effective_date)
+VALUES (0.0, 1.0, 0.00, '2023-01-01'),
+ (1.1, 3.0, 1.00, '2023-01-01'),
+ (3.1, 5.0, 2.00, '2023-01-01'),
+ (5.1, 7.0, 3.00, '2023-01-01'),
+ (7.1, 99999.9, 4.00, '2023-01-01');
+
+-- 4. 插入时段加价规则数据
+INSERT INTO fee_time_surcharge_rules (start_time, end_time, surcharge_amount, effective_date)
+VALUES ('00:00:00', '06:00:00', 4.00, '2023-01-01'),
+ ('11:00:00', '13:00:00', 2.00, '2023-01-01'),
+ ('21:00:00', '23:59:59', 3.00, '2023-01-01');
+
+-- 5. 插入特殊品类数据
+INSERT INTO fee_special_categories (category_name)
+VALUES ('鲜花'),
+ ('蛋糕'),
+ ('电商'),
+ ('高端餐饮');
+
+-- 6. 插入重量加价规则数据
+INSERT INTO fee_weight_surcharge_rules (base_weight, fee_per_kg, effective_date)
+VALUES (5.0, 2.00, '2023-01-01');
+
+DROP TABLE IF EXISTS tool_pay_config;
+CREATE TABLE tool_pay_config
+(
+ config_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
+ config_name varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '支付名称',
+ config_type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '支付类型',
+ oying bit(1) NOT NULL DEFAULT b'0' COMMENT '环境',
+ mch_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '门店编号',
+ enabled bit(1) NOT NULL DEFAULT b'0' COMMENT '是否启用',
+ create_by varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者',
+ create_time timestamp NULL DEFAULT NULL COMMENT '创建时间',
+ update_by varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '更新者',
+ update_time timestamp NULL DEFAULT NULL COMMENT '更新时间',
+ PRIMARY KEY (config_id) USING BTREE
+) ENGINE = InnoDB
+ AUTO_INCREMENT = 6
+ CHARACTER SET = utf8mb4
+ COLLATE = utf8mb4_0900_ai_ci COMMENT = '支付配置'
+ ROW_FORMAT = Dynamic;
+
+INSERT INTO tool_pay_config
+VALUES (1, '汇旺财1721415002', 'HWC', b'1', '1030238092', b'1', 'admin', '2025-02-07 17:48:19', 'admin',
+ '2025-02-07 17:48:29');
+INSERT INTO tool_pay_config
+VALUES (2, '汇旺财1721753752', 'HWC2', b'0', '1030240931', b'0', 'admin', '2025-02-07 17:48:19', 'admin',
+ '2025-02-07 17:48:29');
+INSERT INTO tool_pay_config
+VALUES (3, '汇旺财1728293110', 'HWC3', b'0', '1030238093', b'0', 'admin', '2025-02-07 17:48:19', 'admin',
+ '2025-02-07 17:48:29');
--
Gitblit v1.9.3