/*
|
Navicat Premium Data Transfer
|
|
Source Server : 118.145.136.116
|
Source Server Type : MySQL
|
Source Server Version : 80041
|
Source Host : 118.145.136.116:3306
|
Source Schema : oying
|
|
Target Server Type : MySQL
|
Target Server Version : 80041
|
File Encoding : 65001
|
|
Date: 22/10/2025 16:46:07
|
*/
|
|
SET NAMES utf8mb4;
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
-- ----------------------------
|
-- Table structure for sys_invitations
|
-- ----------------------------
|
DROP TABLE IF EXISTS `sys_invitations`;
|
CREATE TABLE `sys_invitations` (
|
`invitations_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
`inviter_id` bigint(0) NULL DEFAULT NULL COMMENT '邀请人ID',
|
`inviter_phone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邀请人电话',
|
`inviter_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邀请人姓名',
|
`inviter_open_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邀请人openid',
|
`new_user_open_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '新用户openid',
|
`new_user_id` bigint(0) NULL DEFAULT 0 COMMENT '新用户ID',
|
`new_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '新用户姓名',
|
`new_user_phone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '新用户电话',
|
`type` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '记录类型',
|
`user_type` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户类型',
|
`status` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '1success 0pending 2failed',
|
`reward_value` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '奖励数值',
|
`reward_type` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '奖励类型',
|
`create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者',
|
`update_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '更新者',
|
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',
|
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
|
PRIMARY KEY (`invitations_id`) USING BTREE
|
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '邀请新用户记录' ROW_FORMAT = Dynamic;
|
|
-- ----------------------------
|
-- Records of sys_invitations
|
-- ----------------------------
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
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');
|
