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
| SET @db_name = DATABASE();
|
| CREATE TABLE IF NOT EXISTS `tcm_payment_flow` (
| `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
| `flow_no` VARCHAR(64) NOT NULL COMMENT '平台流水号',
| `business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：COURSE_PURCHASE课程购买 MEMBER_PURCHASE会员购买',
| `business_order_id` BIGINT UNSIGNED NOT NULL COMMENT '业务订单ID',
| `business_order_no` VARCHAR(32) NOT NULL COMMENT '业务订单编号',
| `flow_type` VARCHAR(32) NOT NULL COMMENT '流水类型：PAYMENT支付 REFUND退款',
| `trade_status` VARCHAR(32) NOT NULL COMMENT '交易状态：SUCCESS成功 REFUNDED已退款',
| `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
| `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
| `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户手机号快照',
| `product_name_snapshot` VARCHAR(255) NOT NULL COMMENT '商品名称快照',
| `payment_method` VARCHAR(32) NOT NULL COMMENT '支付方式：WECHAT微信 ALIPAY支付宝',
| `transaction_amount` DECIMAL(10,2) NOT NULL COMMENT '交易金额',
| `third_party_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方支付单号',
| `third_party_refund_no` VARCHAR(64) DEFAULT NULL COMMENT '第三方退款单号',
| `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
| `trade_time` DATETIME NOT NULL COMMENT '交易时间',
| `source_type` VARCHAR(32) NOT NULL COMMENT '来源类型：COURSE_PAYMENT课程支付 COURSE_REFUND课程退款 MEMBER_PAYMENT会员支付 MEMBER_REFUND会员退款',
| `source_id` BIGINT UNSIGNED NOT NULL COMMENT '来源记录ID',
| `deleted` TINYINT(1) NOT NULL DEFAULT 0,
| `created_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
| `updated_by` VARCHAR(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
| `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
| `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_payment_flow_no` (`flow_no`),
| UNIQUE KEY `uk_payment_flow_source` (`source_type`, `source_id`),
| KEY `idx_payment_flow_business_order` (`business_type`, `business_order_id`),
| KEY `idx_payment_flow_business_order_no` (`business_order_no`),
| KEY `idx_payment_flow_user` (`app_user_id`),
| KEY `idx_payment_flow_flow_type` (`flow_type`),
| KEY `idx_payment_flow_trade_status` (`trade_status`),
| KEY `idx_payment_flow_payment_method` (`payment_method`),
| KEY `idx_payment_flow_trade_time` (`trade_time`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付流水表';
|
| INSERT INTO `sys_menu`
| (`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
| SELECT
| 4220, 4200, '支付流水', 'trade_payment_flow', 'C', '/trade/flow/payment-flow', 'Trade/PaymentFlow', NULL, 10, 1, 1, 'payment:flow:view', '支付流水菜单', 0
| FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4220 OR `menu_code` = 'trade_payment_flow');
|
| INSERT INTO `sys_menu`
| (`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
| SELECT
| 4221, 4220, '导出支付流水', 'trade_payment_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'payment:flow:export', '支付流水导出按钮', 0
| FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4221 OR `menu_code` = 'trade_payment_flow_export');
|
| INSERT INTO `sys_menu`
| (`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
| SELECT
| 4230, 4200, '退费流水', 'trade_refund_flow', 'C', '/trade/flow/refund-flow', 'Trade/RefundFlow', NULL, 20, 1, 1, 'refund:flow:view', '退费流水菜单', 0
| FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4230 OR `menu_code` = 'trade_refund_flow');
|
| INSERT INTO `sys_menu`
| (`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
| SELECT
| 4231, 4230, '导出退费流水', 'trade_refund_flow_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'refund:flow:export', '退费流水导出按钮', 0
| FROM DUAL
| WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4231 OR `menu_code` = 'trade_refund_flow_export');
|
| UPDATE `sys_menu`
| SET `sort` = 30
| WHERE `id` = 4210
| AND `parent_id` = 4200;
|
| INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
| SELECT sr.id, sm.id
| FROM `sys_role` sr
| JOIN `sys_menu` sm ON sm.id IN (4220, 4221, 4230, 4231)
| WHERE sr.deleted = 0
| AND sr.status = 1
| AND EXISTS (
| SELECT 1
| FROM `sys_role_menu` rm
| WHERE rm.role_id = sr.id
| AND rm.menu_id IN (4200, 4210, 4220)
| );
|
|
