易公会数据库v1.4调整记录
<pre><code class="language-sql">----------------- 2022/02/10 ----------------- V1.4.4.6 -----------------
-- 新增 权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_08009', 'EGH-API-FRONT-TEAM-08009');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06001', 'EGH-API-FRONT-STL-06001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06002', 'EGH-API-FRONT-STL-06002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06003', 'EGH-API-FRONT-STL-06003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06004', 'EGH-API-FRONT-STL-06004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06005', 'EGH-API-FRONT-STL-06005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06006', 'EGH-API-FRONT-STL-06006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06007', 'EGH-API-FRONT-STL-06007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06101', 'EGH-API-FRONT-STL-06101');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06102', 'EGH-API-FRONT-STL-06102');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06103', 'EGH-API-FRONT-STL-06103');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06104', 'EGH-API-FRONT-STL-06104');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06105', 'EGH-API-FRONT-STL-06105');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06106', 'EGH-API-FRONT-STL-06106');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_07001', 'EGH-API-FRONT-STL-07001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_07002', 'EGH-API-FRONT-STL-07002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_07003', 'EGH-API-FRONT-STL-07003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06107', 'EGH-API-FRONT-STL-06107');
-- 新增 表
CREATE TABLE `batch_transfer_stm` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '划款ID',
`type` tinyint(1) NOT NULL COMMENT '划款类型',
`stm_id` varchar(128) NOT NULL COMMENT '关联流水ID',
`stm_amount` decimal(13,2) NOT NULL COMMENT '结算流水金额',
`stl_amount` decimal(13,2) NOT NULL COMMENT '结算流水收益',
`fee` decimal(13,2) NOT NULL COMMENT '手续费',
`transfer_time` int(11) NOT NULL COMMENT '划款时间',
`batch_id` varchar(128) NOT NULL COMMENT '划款批次',
`wallet_id` varchar(128) NOT NULL COMMENT '钱包ID',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_batch_transfer_stm_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增 字段
ALTER TABLE `biz_acct_info` ADD COLUMN `owner` varchar(128) NOT NULL COMMENT '拥有人 厅或用户';
ALTER TABLE `biz_acct_info` ADD COLUMN `biz_acct_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1-个人账号, 2-房主号, 3-公用账号';
-- 修改 字段
ALTER TABLE `biz_acct_info` MODIFY COLUMN `user_id` varchar(128) DEFAULT NULL COMMENT '使用人';
ALTER TABLE `rec_biz_acct_info` MODIFY COLUMN `room_id` varchar(64) DEFAULT NULL COMMENT '厅部门id';
-- 添加 默认数据 先执行上面新增字段再执行该语句 ！！！
UPDATE biz_acct_info SET biz_acct_info.`owner` = biz_acct_info.user_id;
-- 新增 业务账号 类型字段
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E28330HAICVW', 'PR601CFB13N8VQDM', '业务账号类型', 1, 1);
-- 业务 业务账号 类型字段
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61EEA488TXV583', 'PR601D002516KMMU', '业务账号类型', 1, 1);
-- 绑定 公用账号 流程信息
INSERT INTO `process`(`id`, `name`, `process_num`) VALUES ('PR61E69AFFUDWVYL', '绑定公用账号', 'BIND_PUBLIC_BIZ_ACCT');
-- 绑定 公用账号 流程节点
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`) VALUES ('PN61E77E661KSK28', 1, 'PR61E69AFFUDWVYL', 'EGH-API-FRONT-INFO-03004', 'PN61E77E66GVSXGZ', '');
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`) VALUES ('PN61E77E66GVSXGZ', 2, 'PR61E69AFFUDWVYL', 'EGH-API-FRONT-INFO-03004', '', 'PN61E77E661KSK28');
-- 绑定 公用账号 流程字段
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F58PBKZIW', 'PR61E69AFFUDWVYL', '用户花名', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F58N6N1MO', 'PR61E69AFFUDWVYL', '业务账号类型', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F58U7ECIB', 'PR61E69AFFUDWVYL', '业务账号部门ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7DF1319QTGB', 'PR61E69AFFUDWVYL', '业务账号id', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F58AAV89T', 'PR61E69AFFUDWVYL', '业务账号昵称', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7890CSLZRFW', 'PR61E69AFFUDWVYL', '厅ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F58W38HFE', 'PR61E69AFFUDWVYL', '厅房间号', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F97KE3FZO', 'PR61E69AFFUDWVYL', '厅部门名称', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E77F97K7PT10', 'PR61E69AFFUDWVYL', '厅负责人', 1, 1);
-- 移除n 公用账号 流程信息
INSERT INTO `process`(`id`, `name`, `process_num`) VALUES ('PR61E7CBC2TVBQZK', '移除公用账号', 'REMOVE_PUBLIC_BIZ_ACCT');
-- 移除 公用账号 节点
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`) VALUES ('PN61E7CC26IRSZBL', 1, 'PR61E7CBC2TVBQZK', 'EGH-API-FRONT-INFO-03004', 'PN61E7CC261UAD0J', '');
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`) VALUES ('PN61E7CC261UAD0J', 2, 'PR61E7CBC2TVBQZK', 'EGH-API-FRONT-INFO-03004', '', 'PN61E7CC26IRSZBL');
-- 移除 公用账号 流程字段
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC490QPXLV', 'PR61E7CBC2TVBQZK', '用户花名', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC49ZQSBWW', 'PR61E7CBC2TVBQZK', '业务账号类型', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC49FNKGHA', 'PR61E7CBC2TVBQZK', '业务账号部门ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC491U7KND', 'PR61E7CBC2TVBQZK', '业务账号id', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC494FVHWJ', 'PR61E7CBC2TVBQZK', '业务账号昵称', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC5CGA9TYV', 'PR61E7CBC2TVBQZK', '厅ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC5CQIITH4', 'PR61E7CBC2TVBQZK', '厅房间号', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC5CVEQVDT', 'PR61E7CBC2TVBQZK', '厅部门名称', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7CC6FOSMVSI', 'PR61E7CBC2TVBQZK', '厅负责人', 1, 1);
-- 插入 业务账号冻结 流程
INSERT INTO `process` (`id`, `name`, `process_num`) VALUES ('PR61D8004FOB0X6M', '业务账号冻结流程', 'INFO_FREEZE_BIZACCT');
-- 插入 业务账号冻结 流程节点
INSERT INTO `process_node` (`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`, `fail_time`, `is_available`) VALUES ('PN61D80463SEW90D', 1, 'PR61D8004FOB0X6M', 'EGH-API-FRONT-TEAM-04012', NULL, NULL, NULL, 1);
-- 移除 业务账号冻结 流程字段
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0G1C6KO', 'PR61D8004FOB0X6M', '用户ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0CNSR2K', 'PR61D8004FOB0X6M', '用户花名', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F02POU0V', 'PR61D8004FOB0X6M', '业务账号ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0NUWEZL', 'PR61D8004FOB0X6M', '业务账号类型', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0TCMZWB', 'PR61D8004FOB0X6M', '厅名称', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0BLYTOG', 'PR61D8004FOB0X6M', '厅ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0UXPVZC', 'PR61D8004FOB0X6M', '冻结原因', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F08XN7YG', 'PR61D8004FOB0X6M', '申请日期', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E002F0ZBHUCX', 'PR61D8004FOB0X6M', '审核日期', 1, 1);
-- 插入 业务账号解冻 流程
INSERT INTO `process` (`id`, `name`, `process_num`) VALUES ('PR61E000D4GZQAEW', '业务账号解冻流程', 'INFO_UNFREEZE_BIZACCT');
-- 插入 业务账号解冻 流程节点
INSERT INTO `process_node` (`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`, `fail_time`, `is_available`) VALUES ('PN61D80463RGBD7L', 2, 'PR61E000D4GZQAEW', 'EGH-API-FRONT-TEAM-04013', NULL, 'PN61D80463IAEQSZ', NULL, 1);
INSERT INTO `process_node` (`id`, `type`, `process_id`, `func_title`, `prev_node_id`, `next_node_id`, `fail_time`, `is_available`) VALUES ('PN61D80463IAEQSZ', 1, 'PR61E000D4GZQAEW', '待定', 'PN61D80463RGBD7L', NULL, NULL, 1);
-- 移除 业务账号解冻 流程字段
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E7C26ENOM2GF', 'PR61E000D4GZQAEW', '冻结流程ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCE4WFRG', 'PR61E000D4GZQAEW', '用户ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCWIED2J', 'PR61E000D4GZQAEW', '用户花名', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCXV79HC', 'PR61E000D4GZQAEW', '业务账号ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCBIJAF7', 'PR61E000D4GZQAEW', '业务账号类型', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCHWLBZD', 'PR61E000D4GZQAEW', '厅名称', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CC9DQVTN', 'PR61E000D4GZQAEW', '厅ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CC3IA6UM', 'PR61E000D4GZQAEW', '解冻原因', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCBYF90A', 'PR61E000D4GZQAEW', '申请日期', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E003CCJTKC1G', 'PR61E000D4GZQAEW', '审核日期', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E905E0HQLPZJ', 'PR61E000D4GZQAEW', '审核人', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61E91249NYVF2K', 'PR61E000D4GZQAEW', '划款记录批次ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61F0C1DC8MZHS5', 'PR61E000D4GZQAEW', '冻结金额', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF61F0C5D1WB89RL', 'PR61E000D4GZQAEW', '冻结数量', 1, 1);
-- 添加业务账号对应负责人记录
INSERT INTO `rec_dept_mgr` ( `biz_dept_id`, `dept_id`, `user_id`, `effe_date` ) SELECT dept_node.`NAME`, dept_node.`id`, dept_node.`head`, (SELECT UNIX_TIMESTAMP( dept_node.creation_time )) FROM dept_node WHERE dept_node.`scope`=30;
-- 更新业务账号对应平台业务
UPDATE rec_dept_mgr AS mgr SET mgr.biz_dept_id =(SELECT biz.dept_id FROM biz_dept_info AS biz,dept_node AS dept WHERE biz.dept_id = dept.id AND dept.scope = 10 AND biz.biz_id = ( SELECT info.biz_id FROM biz_dept_info AS info WHERE info.dept_id = mgr.dept_id ) AND biz.plf_id = ( SELECT info.plf_id FROM biz_dept_info AS info WHERE info.dept_id = mgr.dept_id )) where mgr.dept_id in (select id from dept_node where scope=30);
-- 更新可结算时间
UPDATE rec_dept_mgr AS mgr, rec_biz_acct_info AS rbai SET mgr.effe_date = rbai.effe_date WHERE rbai.no_ IN ( SELECT MIN( no_ ) FROM rec_biz_acct_info AS aaa WHERE aaa.effe_date IS NOT NULL GROUP BY aaa.dept_id ) and rbai.dept_id = mgr.dept_id AND mgr.dept_id IN ( SELECT id FROM dept_node WHERE scope = 30 );
----------------- 2022/01/18 ----------------- V1.4.4.5 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_01013', 'EGH-API-FRONT-TEAM-01013');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_01014', 'EGH-API-FRONT-TEAM-01014');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_02016', 'EGH-API-FRONT-TEAM-02016');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_03021', 'EGH-API-FRONT-TEAM-03021');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04012', 'EGH-API-FRONT-TEAM-04012');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04013', 'EGH-API-FRONT-TEAM-04013');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04014', 'EGH-API-FRONT-TEAM-04014');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04015', 'EGH-API-FRONT-TEAM-04015');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_07007', 'EGH-API-FRONT-TEAM-07007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_07008', 'EGH-API-FRONT-TEAM-07008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05001', 'EGH-API-FRONT-STL-05001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05002', 'EGH-API-FRONT-STL-05002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05105', 'EGH-API-FRONT-STL-05105');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05106', 'EGH-API-FRONT-STL-05106');
-- 新增字段
ALTER TABLE `wallet_account` ADD COLUMN `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1-正常 2禁用';
-- 创建表
CREATE TABLE `rec_dept_mgr` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT,
`biz_dept_id` varchar(64) NOT NULL COMMENT '平台业务id',
`dept_id` varchar(64) NOT NULL COMMENT '部门id',
`effe_date` int(11) unsigned NOT NULL COMMENT '生效时间',
`user_id` varchar(128) DEFAULT NULL COMMENT '用户id',
`old_user_id` varchar(128) DEFAULT NULL COMMENT '原用户id',
`effective` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '当天是否生效',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '自动更新最新修改时间',
PRIMARY KEY (`no_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 添加rec_dept_mgr系统已有数据 先执行上面创建表操作再执行该语句 ！！！
INSERT INTO `rec_dept_mgr`(`biz_dept_id`, `dept_id`, `user_id`, `effe_date`) SELECT dept_node.name,dept_node.id,dept_node.head,(SELECT UNIX_TIMESTAMP(dept_node.creation_time)) from dept_node WHERE dept_node.scope not in (5,10,30,60,65);
-- 更新数据
UPDATE rec_dept_mgr AS mgr SET mgr.biz_dept_id =(SELECT biz.dept_id FROM biz_dept_info AS biz, dept_node AS dept WHERE biz.dept_id = dept.id AND dept.scope = 10 AND biz.biz_id = (SELECT info.biz_id FROM biz_dept_info AS info WHERE info.dept_id = mgr.dept_id) AND biz.plf_id = (SELECT info.plf_id FROM biz_dept_info AS info WHERE info.dept_id = mgr.dept_id));
-- 更新部门负责人历史记录生效时间
UPDATE rec_dept_mgr AS mgr
SET mgr.effe_date =(
SELECT
ddd.start_stl_date
FROM
(
SELECT
biz.start_stl_date,
dn.id
FROM
biz_dept_info AS biz,
dept_node AS dn
WHERE
biz.dept_id = dn.id
AND dn.scope IN ( 15, 20, 25)
) AS ddd
WHERE
ddd.id = mgr.dept_id
);
----------------- 2022/01/06----------------- V1.4.4.4 -----------------
-- 修改字段
ALTER TABLE `rec_biz_acct_info` MODIFY COLUMN `effe_date` INT ( 11 ) UNSIGNED DEFAULT NULL COMMENT '生效时间';
ALTER TABLE `new_rec_biz_dept_stl_info` MODIFY COLUMN `stl_prop` decimal(5,4) NOT NULL COMMENT '分成比例';
-- 用例和接口
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06006', 'EGH-API-FRONT-SYS-06006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06007', 'EGH-API-FRONT-SYS-06007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04015', 'EGH-API-FRONT-STL-04015');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04016', 'EGH-API-FRONT-STL-04016');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05100', 'EGH-API-FRONT-STL-05100');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05101', 'EGH-API-FRONT-STL-05101');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05102', 'EGH-API-FRONT-STL-05102');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05103', 'EGH-API-FRONT-STL-05103');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05104', 'EGH-API-FRONT-STL-05104');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05201', 'EGH-API-FRONT-STL-05201');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_05202', 'EGH-API-FRONT-STL-05202');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04011', 'EGH-API-FRONT-TEAM-04011');
-- 新增数据表
CREATE TABLE `income_and_withdrawl` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`wallet_id` varchar(64) NOT NULL COMMENT '钱包账户ID',
`income_id` varchar(64) NOT NULL COMMENT '钱包收入来源ID',
`income_type` varchar(64) NOT NULL COMMENT '钱包收入来源类型',
`withdrawl_stm_id` varchar(64) NOT NULL COMMENT '收入对应提现记录ID',
`withdrawl_time` int(10) unsigned DEFAULT NULL COMMENT '收入提现时间',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `income_and_withdrawl` (`income_id`,`withdrawl_stm_id`) COMMENT '提现ID与来源ID联合唯一'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增数据表
CREATE TABLE `wallet_income` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`wallet_id` varchar(64) NOT NULL COMMENT '钱包账户ID',
`from_id` varchar(64) NOT NULL COMMENT '钱包收入来源ID',
`from_type` varchar(64) NOT NULL COMMENT '钱包收入来源类型',
`income_status` tinyint(3) unsigned NOT NULL COMMENT '收入状态',
`income_amount` decimal(10,2) NOT NULL COMMENT '分成比收入金额例',
`income_fee` decimal(10,2) NOT NULL COMMENT '收入手续费',
`income_time` int(10) unsigned NOT NULL COMMENT '收入时间',
`withdrawl_time` int(10) unsigned DEFAULT NULL COMMENT '收入提现时间',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增数据表
CREATE TABLE `same_withdrawl_stm` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT 'ID',
`wallet_id` varchar(128) NOT NULL COMMENT '关联钱包账户ID',
`same_id` varchar(128) NOT NULL COMMENT '同一笔提现ID一样, ID只做识别作用',
`withdrawl_stm_id` varchar(128) NOT NULL COMMENT '关联钱包流水ID',
`withdrawl_amount` decimal(13,4) NOT NULL COMMENT '提现金额',
`withdrawl_fee` decimal(13,4) NOT NULL COMMENT '提现手续费',
`withdrawl_all_amount` decimal(13,4) NOT NULL COMMENT '总提现金额',
`withdrawl_all_fee` decimal(13,4) NOT NULL COMMENT '总提现手续费',
`withdrawl_all_count` int(11) unsigned NOT NULL COMMENT '总提现条数',
`withdrawl_time` int(11) unsigned NOT NULL COMMENT '提现时间',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_id` (`id`) USING BTREE,
UNIQUE KEY `uk_withdrawl_stm_id` (`withdrawl_stm_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
----------------- 2021/12/16 ----------------- V1.4.4.3 -----------------
-- 用例和接口
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_09003', 'EGH-API-FRONT-TEAM-09003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_09004', 'EGH-API-FRONT-TEAM-09004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_09005', 'EGH-API-FRONT-TEAM-09005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_09006', 'EGH-API-FRONT-TEAM-09006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04008', 'EGH-API-FRONT-STL-04008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04009', 'EGH-API-FRONT-STL-04009');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04010', 'EGH-API-FRONT-STL-04010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04011', 'EGH-API-FRONT-STL-04011');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04014', 'EGH-API-FRONT-STL-04014');
-- 新增数据表
CREATE TABLE `dept_and_company` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`dept_id` varchar(64) NOT NULL COMMENT '部门ID',
`company_id` varchar(64) NOT NULL COMMENT '公司ID',
`effe_date` int(10) unsigned NOT NULL COMMENT '生效时间',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增数据表
CREATE TABLE `new_rec_biz_dept_stl_info` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`dept_id` varchar(64) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(64) NOT NULL COMMENT '上级部门id',
`stl_method` tinyint(3) unsigned NOT NULL COMMENT '结算方式',
`stl_mode` tinyint(3) unsigned NOT NULL COMMENT '结算周期',
`stl_prop` decimal(3,2) NOT NULL COMMENT '分成比例',
`prop_cls` tinyint(3) NOT NULL COMMENT '比例类型',
`effe_date` int(10) unsigned NOT NULL COMMENT '生效时间',
`exp_date` int(10) unsigned NOT NULL COMMENT '失效时间',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
----------------- 2021/11/19 ----------------- V1.4.4.1 -----------------
-- 新增数据表
CREATE TABLE `withdrawl_astrict` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '提现限制ID',
`daily_withdrawl_time` int(32) NOT NULL COMMENT '每日累计提现次数',
`monthly_withdrawl_limit` decimal(11,2) DEFAULT NULL COMMENT '每月提现累计额度',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_wallet_detail_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 角色及接口用例
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06004', 'EGH-API-FRONT-SYS-06004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06005', 'EGH-API-FRONT-SYS-06005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04012', 'EGH-API-FRONT-STL-04012');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04013', 'EGH-API-FRONT-STL-04013');
-- 系统管理员权限
INSERT INTO `role_privilege`(`func_title`, `role_id`) VALUES ('EGH_SYS_06004', 'R20210001');
INSERT INTO `role_privilege`(`func_title`, `role_id`) VALUES ('EGH_SYS_06005', 'R20210001');
-- 超级管理权限
INSERT INTO `role_privilege`(`func_title`, `role_id`) VALUES ('EGH_STL_04012', 'R20210002');
-- 结算主管权限
INSERT INTO `role_privilege`(`func_title`, `role_id`) VALUES ('EGH_STL_04013', 'R20210003');
-- 新增字段
ALTER TABLE `stl_stm_bat` ADD COLUMN `room_manage_amount` DECIMAL ( 10, 2 ) NOT NULL COMMENT '厅管金额';
ALTER TABLE `stl_tab_conf` ADD COLUMN `room_stl_free_prop_tab_id`varchar(32) DEFAULT NULL COMMENT '厅自由结算数据模板表ID';
-- 新增字段
ALTER TABLE `rec_biz_acct_info` ADD COLUMN `dept_id` varchar(64) DEFAULT NULL COMMENT '业务账号id';
ALTER TABLE `rec_biz_acct_info` ADD COLUMN `room_id` varchar(64) NOT NULL COMMENT '厅部门id;
</code></pre>