dili-cashier-mall.sql
10.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
# 订单数据
CREATE TABLE `mall_biz_order` (
`id` bigint unsigned NOT NULL,
`order_no` varchar(50) NOT NULL COMMENT '订单单号-系统生成',
`order_id` varchar(64) NOT NULL COMMENT '业务侧订单号',
`trade_id` varchar(64) DEFAULT NULL COMMENT '业务侧支付单号',
`channel` varchar(20) NOT NULL COMMENT 'dili侧渠道(中瑞 地利)',
`firm_id` bigint DEFAULT NULL COMMENT '市场id',
`mch_id` varchar(20) DEFAULT NULL COMMENT 'dili商户code',
`source` tinyint unsigned NOT NULL COMMENT '订单来源(10大润发 2山姆)-换取回调地址',
`order_type` tinyint NOT NULL DEFAULT '3' COMMENT '支付单类型: 1线下扫码购，2 接口扫码购，3小程序',
`user_code` varchar(64) NOT NULL COMMENT '用户编号',
`username` varchar(64) NOT NULL COMMENT '用户名称',
`company_code` varchar(64) DEFAULT NULL COMMENT '企业组编号',
`shop_code` varchar(32) DEFAULT NULL COMMENT '门店编号',
`shop_name` varchar(100) DEFAULT NULL COMMENT '门店名称',
`total_amount` bigint unsigned NOT NULL COMMENT '总金额分',
`freight_fee` bigint unsigned DEFAULT NULL COMMENT '运费分',
`discount_fee` bigint DEFAULT NULL COMMENT '优惠金额, 单位：分',
`state` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '订单状态(0-notpay-未支付 1-payed-已支付 2-notpaycancel-已取消 3-payedcancel-已退款 4-done-订单完成 5-buyer_confirm-待用户收货)',
`rtmart_state` tinyint DEFAULT NULL COMMENT '配送状态(1-transfer-转单 2-package-打包 3-collect-揽件 4-delivery-配达)',
`order_time` datetime NOT NULL COMMENT '下单时间',
`order_expire` int DEFAULT '10' COMMENT '订单有效期分钟-超时自动取消',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
`created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modifier_name` varchar(50) DEFAULT NULL COMMENT '修改人',
`modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除0未删',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_order_id` (`order_id`) USING BTREE,
KEY `idx_user_code` (`user_code`) USING BTREE,
KEY `idx_order_time` (`order_time`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-订单';
CREATE TABLE `mall_biz_order_address` (
`id` bigint unsigned NOT NULL COMMENT '订单主单id',
`receiver_name` varchar(100) DEFAULT NULL COMMENT '接收名称',
`receiver_mobile` varchar(20) DEFAULT NULL COMMENT '接收电话',
`receiver_address` varchar(250) DEFAULT NULL COMMENT '接收地址',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-订单地址';
CREATE TABLE `mall_biz_order_item` (
`id` bigint unsigned NOT NULL,
`biz_order_id` bigint NOT NULL COMMENT '订单主单id',
`sub_order_id` varchar(64) NOT NULL COMMENT '业务侧子订单号',
`order_id` varchar(64) NOT NULL COMMENT '业务侧订单号',
`trade_id` varchar(64) DEFAULT NULL COMMENT '业务侧支付单号',
`shop_code` varchar(32) DEFAULT NULL COMMENT '门店编号',
`shop_name` varchar(100) DEFAULT NULL COMMENT '门店名称',
`item_bn` varchar(100) NOT NULL COMMENT '商品SKU编号',
`item_name` varchar(255) NOT NULL COMMENT '商品名称',
`num` int unsigned NOT NULL COMMENT '数量',
`price` bigint NOT NULL COMMENT '单价分',
`amount` bigint NOT NULL COMMENT '小计金额分',
`discount_fee` bigint DEFAULT NULL COMMENT '优惠金额分',
`pic` varchar(500) DEFAULT NULL COMMENT '商品图片',
`tax_output_rate` varchar(10) DEFAULT NULL COMMENT '销项税率',
`tax_classification_code` varchar(50) DEFAULT NULL COMMENT '税则分类编码',
`tax_classification_name` varchar(100) DEFAULT NULL COMMENT '税则分类名称',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_sub_order_id` (`sub_order_id`,`trade_id`) USING BTREE,
KEY `idx_order_id` (`order_id`) USING BTREE,
KEY `idx_biz_order_id` (`biz_order_id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-订单商品明细';
CREATE TABLE `mall_biz_payment` (
`id` bigint unsigned NOT NULL,
`pay_trade_no` varchar(50) NOT NULL COMMENT '支付单号-系统生成',
`biz_order_id` varchar(250) NOT NULL COMMENT '订单id集合(mall_biz_order.id)',
`order_id` varchar(500) NOT NULL COMMENT '业务侧订单号(多个)',
`trade_id` varchar(500) NOT NULL COMMENT '业务侧支付单号(多个)',
`user_code` varchar(50) NOT NULL COMMENT '用户code',
`pay_trade_id` varchar(50) NOT NULL COMMENT 'dili交易流水号',
`firm_id` bigint DEFAULT NULL COMMENT '市场id',
`mch_id` varchar(20) NOT NULL COMMENT 'dili商户code',
`card_no` varchar(50) DEFAULT NULL COMMENT 'dili卡号',
`username` varchar(100) DEFAULT NULL COMMENT 'dili用户名',
`user_id` bigint DEFAULT NULL COMMENT 'dili用户id',
`account_id` bigint DEFAULT NULL COMMENT 'dili账户id',
`fund_account_id` bigint DEFAULT NULL COMMENT 'dili资金账户id',
`open_id` varchar(50) DEFAULT NULL COMMENT '第三方openId',
`pay_fee` bigint NOT NULL COMMENT '支付金额分',
`pay_state` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '1:待支付 2:支付中 4:支付成功 6:支付失败',
`pay_time` datetime DEFAULT NULL COMMENT 'dili支付成功时间',
`channel_id` tinyint unsigned DEFAULT NULL COMMENT '支付渠道(微信 园区卡)',
`cashier_url` varchar(500) DEFAULT NULL COMMENT '收银台跳转链接-创建订单时返回',
`payment_callback` varchar(255) DEFAULT NULL COMMENT '支付成功回调业务侧接口',
`ext` varchar(1000) DEFAULT NULL COMMENT '扩展json',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
`created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '变更时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_pay_trade_no` (`pay_trade_no`) USING BTREE,
UNIQUE KEY `unq_pay_trade_id` (`pay_trade_id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-结算(N订单:1)';
CREATE TABLE `mall_biz_payment_order` (
`id` bigint unsigned NOT NULL,
`biz_payment_id` bigint NOT NULL COMMENT '结算id(mall_biz_paymen.id)',
`pay_trade_no` varchar(50) NOT NULL COMMENT '支付单号(mall_biz_paymen.pay_trade_no)',
`biz_order_id` bigint NOT NULL COMMENT '订单id(mall_biz_order.id)',
`order_id` varchar(64) NOT NULL COMMENT '业务侧订单号',
`trade_id` varchar(64) NOT NULL COMMENT '业务侧支付单号',
`pay_trade_id` varchar(50) NOT NULL COMMENT 'dili交易流水号(mall_biz_payment.pay_trade_id)',
`pay_fee` bigint NOT NULL COMMENT '支付金额分',
`pay_state` tinyint NOT NULL DEFAULT '1' COMMENT '1:待支付 2:支付中 4:支付成功 6:支付失败',
`pay_time` datetime DEFAULT NULL COMMENT 'dili支付成功时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_order_id` (`order_id`,`trade_id`) USING BTREE,
KEY `idx_biz_order_id` (`biz_order_id`) USING BTREE,
KEY `idx_pay_trade_id` (`pay_trade_id`) USING BTREE,
KEY `idx_pay_trade_no` (`pay_trade_no`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-结算订单(N:N)';
CREATE TABLE `mall_biz_refund` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`biz_payment_id` bigint NOT NULL COMMENT '结算id(mall_biz_payment.id)',
`biz_order_id` bigint NOT NULL COMMENT '订单id(mall_biz_order.id)',
`refund_trade_no` varchar(50) NOT NULL COMMENT '退款单号-系统生成',
`refund_bn` varchar(64) DEFAULT NULL COMMENT '业务侧退款单号',
`order_id` varchar(64) NOT NULL COMMENT '业务侧订单号',
`trade_id` varchar(64) DEFAULT NULL COMMENT '业务侧流水号',
`firm_id` bigint DEFAULT NULL COMMENT '市场id',
`mch_id` varchar(20) NOT NULL COMMENT '商户id',
`pay_trade_no` varchar(50) DEFAULT NULL COMMENT '支付单号(mall_biz_payment.pay_trade_no)',
`refund_trade_id` varchar(50) NOT NULL COMMENT 'dili退款流水号',
`pay_trade_id` varchar(50) DEFAULT NULL COMMENT 'dili交易流水号(mall_biz_payment.pay_trade_id)',
`refund_card_no` varchar(255) DEFAULT NULL COMMENT 'dili卡号',
`refund_user_id` bigint DEFAULT NULL COMMENT 'dili用户id',
`refund_username` varchar(50) DEFAULT NULL COMMENT 'dili用户名称',
`refund_account_id` bigint DEFAULT NULL COMMENT 'dili账号id',
`refund_time` datetime DEFAULT NULL COMMENT 'dili退款成功时间',
`refund_fee` bigint NOT NULL COMMENT '退款金额分',
`freight_fee` bigint DEFAULT '0' COMMENT '退运费分',
`refund_state` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '1:待支付 2:支付中 4:支付成功 6:支付失败',
`refund_reason` varchar(255) DEFAULT NULL COMMENT '退款原因',
`refund_callback` varchar(255) DEFAULT NULL COMMENT '退款成功回调业务侧接口',
`version` int unsigned NOT NULL DEFAULT '1' COMMENT '版本号',
`creater_name` varchar(50) NOT NULL COMMENT '申请人',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '退款申请时间',
`refuse_reason` varchar(500) DEFAULT NULL COMMENT '失败原因',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `unq_refund_trade_id` (`refund_trade_id`) USING BTREE,
UNIQUE KEY `unq_refund_bn` (`refund_bn`) USING BTREE,
KEY `idx_order_id` (`order_id`) USING BTREE,
KEY `idx_refund_trarde_no` (`refund_trade_no`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-退款';
CREATE TABLE `mall_biz_refund_item` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`biz_refund_id` bigint unsigned NOT NULL COMMENT '退款主单id',
`sub_order_id` bigint unsigned NOT NULL COMMENT '业务子订单ID',
`item_bn` varchar(100) DEFAULT NULL COMMENT '商品SKU编号',
`item_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
`num` int unsigned DEFAULT NULL COMMENT '退货数量',
`refund_fee` bigint DEFAULT NULL COMMENT '退款金额分',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_biz_refund_id` (`biz_refund_id`) USING BTREE,
KEY `idx_sub_order_id` (`sub_order_id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务-退款商品明细';