20260509-rider-withdraw.sql
2.94 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
-- 骑手提现审核增量脚本
-- 在已有环境执行；全新环境可直接使用 schema.sql。
ALTER TABLE `rider`
ADD COLUMN `frozen_balance` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '冻结余额（提现审核中）' AFTER `balance`;
CREATE TABLE `rider_withdraw_apply` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`withdraw_no` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '提现单号',
`rider_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '骑手ID',
`city_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市ID',
`amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '提现金额',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0=待审核 1=审核通过待打款 2=审核拒绝 3=已打款 4=打款失败',
`account_type` TINYINT NOT NULL DEFAULT 1 COMMENT '收款账户类型：1=银行卡 2=支付宝 3=微信',
`account_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '收款人',
`bank_name` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '开户行',
`bank_branch` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '开户支行',
`account_no` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '收款账号',
`apply_remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '申请备注',
`audit_remark` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '审核/打款备注',
`auditor_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核人ID',
`auditor_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '审核人名称',
`apply_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请时间',
`audit_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核时间',
`pay_time` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '打款时间',
`transfer_no` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '打款流水号',
`create_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`update_time` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_withdraw_no` (`withdraw_no`),
KEY `idx_rider_status` (`rider_id`, `status`),
KEY `idx_city_status_time` (`city_id`, `status`, `apply_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='骑手提现申请表';
INSERT INTO `sys_menu` (`code`, `name`, `type`, `path`, `icon`, `parent_id`, `menu_scope`, `list_order`, `visible`, `status`, `create_time`)
SELECT 'rider.withdraw', '骑手提现审核', 'MENU', '/rider/withdraw', 'WalletOutlined', 0, 'BOTH', 56, 1, 1, UNIX_TIMESTAMP()
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `code` = 'rider.withdraw');
UPDATE `sys_menu`
SET `icon` = 'WalletOutlined'
WHERE `code` = 'rider.withdraw'
AND (`icon` IS NULL OR `icon` = '');
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`, `create_time`)
SELECT r.id, m.id, UNIX_TIMESTAMP()
FROM `sys_role` r
INNER JOIN `sys_menu` m ON m.code = 'rider.withdraw'
WHERE r.code IN ('platform_admin', 'substation_admin')
AND NOT EXISTS (
SELECT 1 FROM `sys_role_menu` rm WHERE rm.role_id = r.id AND rm.menu_id = m.id
);