易公会Plus v0.3 调整记录
<pre><code class="language-sql">
-- --------------- 已上线 ----------------- v0.3.3.2 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_13001', 'EGH-API-FRONT-TEAM-13001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_03020', 'EGH-API-FRONT-STL-03020');
-- 其他收益消费记录表
CREATE TABLE `gain_stl_bat_record` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`bat_id` varchar(64) NOT NULL COMMENT '结算批次id',
`gain_stm_id` varchar(64) 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- --------------- 已上线 ----------------- v0.3.2.2 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11306', 'EGH-API-FRONT-TEAM-11306');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06010', 'EGH-API-FRONT-SYS-06010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06011', 'EGH-API-FRONT-SYS-06011');
-- --------------- 已上线 ----------------- v0.3.2.1 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05016', 'EGH-API-FRONT-INFO-05016');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05017', 'EGH-API-FRONT-INFO-05017');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11401', 'EGH-API-FRONT-TEAM-11401');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11402', 'EGH-API-FRONT-TEAM-11402');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11403', 'EGH-API-FRONT-TEAM-11403');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11404', 'EGH-API-FRONT-TEAM-11404');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11405', 'EGH-API-FRONT-TEAM-11405');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11406', 'EGH-API-FRONT-TEAM-11406');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11407', 'EGH-API-FRONT-TEAM-11407');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11408', 'EGH-API-FRONT-TEAM-11408');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_12001', 'EGH-API-FRONT-TEAM-12001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_12002', 'EGH-API-FRONT-TEAM-12002');
-- 修改税率流程信息添加节点
UPDATE `process_node` SET `next_node_id` = 'PN62F22E80FDGILF' WHERE `id` = 'PN625FFCC4ZYRD9M';
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`, `prev_node_id`) VALUES ('PN62F22E80FDGILF', 1, 'PR61E4SD9FUDWFSH', 'EGH-API-FRONT-TEAM-10005', 'PN625FFCC4ZYRD9M');
-- 修改税率流程审核添加字段
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62F232DF9KNBCO', 'PR61E4SD9FUDWFSH', '初审人ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62F232DFBOKJV4', 'PR61E4SD9FUDWFSH', '初审人昵称', 1, 1);
-- 删除 修改税率流程 旧审核节点
UPDATE `process_action` SET `is_available` = 0 WHERE `process_node_id` = 'PN625FFCC4ZYRD9M';
UPDATE `process_action` SET `is_available` = 0 WHERE `process_node_id` = 'PN62F22E80FDGILF';
-- 删除 修改税率流程 旧审核数据
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F0BT9EMG';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F02JUKYK';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F0PS984X';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F0MPBLLW';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F068FEQY';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F058M9RS';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF610VSCAL7';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF625FF5F07GUJ6N';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF62F232DF9KNBCO';
UPDATE `process_data` SET `is_available` = 0 WHERE `process_field_id` = 'PF62F232DFBOKJV4';
-- 解除冻结审核添加节点一个
INSERT INTO `process_node` (`id`, `type`, `process_id`, `func_title`, `prev_node_id`) VALUES ('PN62D656657SYEBY', 1, 'PR61E000D4GZQAEW', 'EGH-API-FRONT-TEAM-04013', 'PN61D80463IAEQSZ');
UPDATE process_node SET next_node_id = "PN62D656657SYEBY" WHERE id = "PN61D80463IAEQSZ";
-- 解除冻结审核添加字段
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62D657CDAVK0XT', 'PR61E000D4GZQAEW', '划款用户', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62EB9E87CZRMBN', 'PR61E000D4GZQAEW', '自动驳回', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62EC8FD1MBXDA0', 'PR61E000D4GZQAEW', '初审人ID', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62EC8FD1OSEQRW', 'PR61E000D4GZQAEW', '初审人昵称', 1, 1);
INSERT INTO `process_field` (`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF62F379CEZHY19E', 'PR61E000D4GZQAEW', '驳回原因', 1, 1);
-- --------------- bug修复 ----------------- v0.3.1.6 -----------------
-- 删除部门信息
SET @dept_id = "DN62B2B75FA8J0CT";
UPDATE `dept_node` SET `is_available` = 0 WHERE `id` = @dept_id;
UPDATE `new_rec_biz_dept_stl_info` SET `is_available` = 0 WHERE `dept_id` = @dept_id;
UPDATE `rec_dept_mgr` SET `is_available` = 0 WHERE `dept_id` = @dept_id;
UPDATE `dept_node` SET `is_available` = 0 WHERE `prev_node_id` = @dept_id;
-- --------------- 已上线 ----------------- v0.3.1.5 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11304', 'EGH-API-FRONT-TEAM-11304');
-- --------------- 已上线 ----------------- v0.3.1.2 -----------------
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08006', 'EGH-API-FRONT-SYS-08006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08007', 'EGH-API-FRONT-SYS-08007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08008', 'EGH-API-FRONT-SYS-08008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05001', 'EGH-API-FRONT-INFO-05001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05002', 'EGH-API-FRONT-INFO-05002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05003', 'EGH-API-FRONT-INFO-05003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05004', 'EGH-API-FRONT-INFO-05004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05005', 'EGH-API-FRONT-INFO-05005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05006', 'EGH-API-FRONT-INFO-05006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05007', 'EGH-API-FRONT-INFO-05007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05008', 'EGH-API-FRONT-INFO-05008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05009', 'EGH-API-FRONT-INFO-05009');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05010', 'EGH-API-FRONT-INFO-05010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05011', 'EGH-API-FRONT-INFO-05011');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05012', 'EGH-API-FRONT-INFO-05012');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_05013', 'EGH-API-FRONT-INFO-05013');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_01015', 'EGH-API-FRONT-TEAM-01015');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_08010', 'EGH-API-FRONT-TEAM-08010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04023', 'EGH-API-FRONT-STL-04023');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06108', 'EGH-API-FRONT-STL-06108');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06109', 'EGH-API-FRONT-STL-06109');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_06120', 'EGH-API-FRONT-STL-06120');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01007', 'EGH-API-FRONT-EARNINGS-01007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_WALLET_01001', 'EGH-API-FRONT-WALLET-01001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_WALLET_01002', 'EGH-API-FRONT-WALLET-01002');
-- 手动划款关系表
CREATE TABLE `stl_acct_income_and_stl_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`data_id` varchar(64) NOT NULL COMMENT '结算数据ID stl_stm',
`income_id` varchar(64) NOT NULL COMMENT '钱包收入ID',
`stl_income_id` varchar(64) NOT NULL COMMENT '结算账户收入ID',
`pack_id` varchar(64) 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
) ENGINE=InnoDB AUTO_INCREMENT=960 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 收益发放记录
CREATE TABLE `view_earnings_grant_bat_grant_rec` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '收益发放记录ID',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`start_stl_date` int(11) NOT NULL COMMENT '结算开始时间',
`end_stl_date` int(11) NOT NULL COMMENT '结算结束时间',
`grant_amount_sum` decimal(13,2) NOT NULL COMMENT '发放总金额',
`grant_time` int(11) NOT NULL COMMENT '发放时间',
`grant_sucess_amount` decimal(13,2) NOT NULL COMMENT '发放成功金额',
`grant_fail_amount` decimal(13,2) NOT NULL COMMENT '发放失败金额',
`grant_status` tinyint(1) NOT NULL COMMENT '发放结果',
`grant_status_remark` varchar(256) DEFAULT NULL COMMENT '发放结果说明',
`grant_bat_id` varchar(256) DEFAULT 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_view_earnings_grant_bat` (`id`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_start_stl_date` (`start_stl_date`) USING BTREE,
KEY `k_end_stl_date` (`end_stl_date`) USING BTREE,
KEY `k_grant_time` (`grant_time`) USING BTREE,
KEY `k_grant_status` (`grant_status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 开户行查询请求记录
CREATE TABLE `bank_address_request_rec` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`order_no` varchar(128) NOT NULL COMMENT '订单号',
`bank_num` varchar(128) NOT NULL COMMENT '银行卡号',
`bank` varchar(128) DEFAULT NULL COMMENT '银行名称',
`province` varchar(128) DEFAULT NULL COMMENT '开户省',
`city` varchar(128) DEFAULT NULL COMMENT '开户市',
`card_name` varchar(128) DEFAULT NULL COMMENT '卡名称',
`type` varchar(128) DEFAULT NULL COMMENT '卡类型',
`abbreviation` varchar(128) DEFAULT NULL COMMENT '银联支付网关简码',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_bank_num` (`bank_num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 银行卡三要素校验
CREATE TABLE `bank_three_elements_request_rec` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT 'id',
`name` varchar(32) NOT NULL COMMENT '姓名',
`id_card_num` varchar(32) NOT NULL COMMENT '身份证号码',
`bank_card_num` varchar(32) NOT NULL COMMENT '银行卡号',
`response_code` varchar(16) NOT NULL COMMENT '请求返回错误码 200正常 200以上 错误',
`request_id` varchar(32) DEFAULT NULL COMMENT '请求ID',
`result_state` tinyint(1) DEFAULT NULL COMMENT '返回值为 0 : 一致 返回值为 1 : 不一致 返回值为 2：卡状态异常',
`result_msg` varchar(128) DEFAULT NULL COMMENT '错误码为200以上时会有',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_bank_account_id` (`id`) USING BTREE,
UNIQUE KEY `uk_three_elements` (`name`,`id_card_num`,`bank_card_num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- --------------- 已上线 ----------------- V0.3.1.1 -----------------
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04023', 'EGH-API-FRONT-TEAM-04023');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_03017', 'EGH-API-FRONT-STL-03017');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_03018', 'EGH-API-FRONT-STL-03018');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04020', 'EGH-API-FRONT-STL-04020');
-- --------------- 已上线 ----------------- V0.2.3 -----------------
-- view_stl_stm_earnings_detail新增字段
ALTER TABLE `view_stl_stm_earnings_detail` ADD COLUMN `all_tax_earnings` decimal(13,2) NOT NULL COMMENT '税后收益';
-- view_stl_ensure_earnings_detail新增字段
ALTER TABLE `view_stl_ensure_earnings_detail` ADD COLUMN `all_tax_earnings` decimal(13,2) NOT NULL COMMENT '税后收益';
-- view_stl_other_earnings_detail新增字段
ALTER TABLE `view_stl_other_earnings_detail` ADD COLUMN `all_tax_earnings` decimal(13,2) NOT NULL COMMENT '税后收益';
-- 清空可以收益发放的批次
TRUNCATE view_stl_stm_grant_bat;
-- 清空发放记录相关
TRUNCATE view_earnings_grant_bat;
TRUNCATE view_earnings_grant_rec;
TRUNCATE view_earnings_grant_detail;
-- 清空统计表
TRUNCATE view_withdrawl_statistics;
-- 清空钱包余额展示表
TRUNCATE view_wallet_income;
-- 清空结算账户收入相关数据
TRUNCATE stl_acct_income;
TRUNCATE stl_acct_detail;
TRUNCATE stl_bat_and_grant_bat;
-- 清空钱包出入记录与收入
TRUNCATE wallet_income;
TRUNCATE wallet_detail;
-- 更新钱包余额
UPDATE wallet_account SET available = 0;
-- 更新提现记录相关
TRUNCATE same_withdrawl_stm;
TRUNCATE income_and_withdrawl;
TRUNCATE withdrawl_stm;
TRUNCATE withdrawl_bat;
-- 清空手动划款记录相关
TRUNCATE batch_transfer_stm;
-- 清除原始数据相关表
TRUNCATE table `raw_data_bat`;
TRUNCATE table `raw_data`;
TRUNCATE table `error_data`;
-- 清除流水数据相关表
TRUNCATE table `stm_data_bat`;
TRUNCATE table `biz_acct_stm_data`;
TRUNCATE table `room_stm_data`;
-- 结算数据相关表
TRUNCATE table `biz_acct_stl_data`;
TRUNCATE table `room_stl_data`;
TRUNCATE table `stl_stm_bat`;
TRUNCATE table `stl_stm`;
TRUNCATE table `stl_stm_bat_data`;
TRUNCATE table `stl_stm_data`;
TRUNCATE table `stl_cache_data`;
TRUNCATE table `view_stl_bat_data`;
TRUNCATE table `view_bat_stm_data`;
TRUNCATE table `view_stl_results_data`;
TRUNCATE table `view_stl_stm_earnings_detail`;
TRUNCATE table `view_stl_ensure_earnings_detail`;
TRUNCATE table `view_stl_other_earnings_detail`;
-- 其他收益数据相关表
TRUNCATE table `gain_biz_acct_data`;
TRUNCATE table `gain_data_bat`;
TRUNCATE table `gain_biz_acct_stm_data`;
-- --------------- 已上线 ----------------- V0.2.2 -----------------
-- 调整权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06008', 'EGH-API-FRONT-SYS-06008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_06009', 'EGH-API-FRONT-SYS-06009');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04019', 'EGH-API-FRONT-STL-04019');
-- --------------- 已上线 ----------------- V0.2.1 -----------------
-- 调整金额小数位
ALTER TABLE view_stl_ensure_earnings_detail MODIFY `tax_before` decimal(13,2) NOT NULL COMMENT '税前';
ALTER TABLE view_stl_ensure_earnings_detail MODIFY `tax_after` decimal(13,2) NOT NULL COMMENT '税后';
ALTER TABLE view_stl_other_earnings_detail MODIFY `tax_before` decimal(13,2) NOT NULL COMMENT '税前';
ALTER TABLE view_stl_other_earnings_detail MODIFY `tax_after` decimal(13,2) NOT NULL COMMENT '税后';
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_03030', 'EGH-API-FRONT-TEAM-03030');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_WAL_01010', 'EGH-API-FRONT-WAL-01010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_USER_01010', 'EGH-API-FRONT-USER-01010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_04010', 'EGH-API-FRONT-SYS-04010');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_04011', 'EGH-API-FRONT-SYS-04011');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08001', 'EGH-API-FRONT-SYS-08001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08002', 'EGH-API-FRONT-SYS-08002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08003', 'EGH-API-FRONT-SYS-08003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08004', 'EGH-API-FRONT-SYS-08004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_08005', 'EGH-API-FRONT-SYS-08005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_03014', 'EGH-API-FRONT-TEAM-03014');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04021', 'EGH-API-FRONT-TEAM-04021');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_04022', 'EGH-API-FRONT-TEAM-04022');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10001', 'EGH-API-FRONT-TEAM-10001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10002', 'EGH-API-FRONT-TEAM-10002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10003', 'EGH-API-FRONT-TEAM-10003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10004', 'EGH-API-FRONT-TEAM-10004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10005', 'EGH-API-FRONT-TEAM-10005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_10006', 'EGH-API-FRONT-TEAM-10006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11101', 'EGH-API-FRONT-TEAM-11101');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11201', 'EGH-API-FRONT-TEAM-11201');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11202', 'EGH-API-FRONT-TEAM-11202');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11203', 'EGH-API-FRONT-TEAM-11203');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11301', 'EGH-API-FRONT-TEAM-11301');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11302', 'EGH-API-FRONT-TEAM-11302');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11303', 'EGH-API-FRONT-TEAM-11303');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01401', 'EGH-API-FRONT-STL-01401');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01402', 'EGH-API-FRONT-STL-01402');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01403', 'EGH-API-FRONT-STL-01403');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01404', 'EGH-API-FRONT-STL-01404');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01405', 'EGH-API-FRONT-STL-01405');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04003', 'EGH-API-FRONT-STL-04003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04005', 'EGH-API-FRONT-STL-04005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04007', 'EGH-API-FRONT-STL-04007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04017', 'EGH-API-FRONT-STL-04017');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_04018', 'EGH-API-FRONT-STL-04018');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08001', 'EGH-API-FRONT-STL-08001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08002', 'EGH-API-FRONT-STL-08002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08003', 'EGH-API-FRONT-STL-08003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08004', 'EGH-API-FRONT-STL-08004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08005', 'EGH-API-FRONT-STL-08005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08006', 'EGH-API-FRONT-STL-08006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08007', 'EGH-API-FRONT-STL-08007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_08008', 'EGH-API-FRONT-STL-08008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01001', 'EGH-API-FRONT-EARNINGS-01001');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01002', 'EGH-API-FRONT-EARNINGS-01002');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01003', 'EGH-API-FRONT-EARNINGS-01003');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01004', 'EGH-API-FRONT-EARNINGS-01004');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01005', 'EGH-API-FRONT-EARNINGS-01005');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_EARNINGS_01006', 'EGH-API-FRONT-EARNINGS-01006');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_STL_01204', 'EGH-API-FRONT-STL-01204');
-- 修改钱包出入记录title长度
ALTER TABLE wallet_detail MODIFY title VARCHAR(256);
-- 新增 平台业务字段
ALTER TABLE `room_info` ADD COLUMN `plf_biz_id` varchar(64) NOT NULL COMMENT '平台业务部门id';
-- 新增 同步平台业务字段数据需要先执行上面新增平台业务字段sql
-- 【注意】执行时间较长
UPDATE room_info AS room SET room.plf_biz_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 = room.dept_id ) AND biz.plf_id = ( SELECT info.plf_id FROM biz_dept_info AS info WHERE info.dept_id = room.dept_id )) WHERE room.dept_id IN ( SELECT id FROM dept_node WHERE scope = 25 );
-- --------------- 已上线 ----------------- V0.2 -----------------
-- 二要素请求表
CREATE TABLE `two_elements_request_rec` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT 'id',
`name` varchar(32) NOT NULL COMMENT '姓名',
`id_card_num` varchar(32) NOT NULL COMMENT '身份证号码',
`response_code` varchar(16) NOT NULL COMMENT '请求返回错误码 查看枚举IdCardApiStatusEnum',
`request_id` varchar(64) NOT NULL COMMENT '请求ID',
`result_status` varchar(16) NOT NULL COMMENT '返回状态',
`result_state` tinyint(4) NOT NULL COMMENT '返回值为 1 : 查询成功, 二要素一致 返回值为 2 : 查询成功, 二要素不一致 返回值为 4：库无',
`is_available` tinyint(1) unsigned NOT NULL DEFAULT '1',
`creation_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最新修改时间',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_bank_account_id` (`id`) USING BTREE,
UNIQUE KEY `uk_three_elements` (`id_card_num`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4;
-- 新增 字段
ALTER TABLE `stl_stm_bat` ADD COLUMN `stl_bat_id` varchar(128) NOT NULL COMMENT '结算批次id';
-- 新增 字段
ALTER TABLE `tmpl` ADD COLUMN `status` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '0-禁用 1-启用';
-- 新增权限
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_04008', 'EGH-API-FRONT-SYS-04008');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_SYS_04009', 'EGH-API-FRONT-SYS-04009');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_09007', 'EGH-API-FRONT-TEAM-09007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_INFO_03007', 'EGH-API-FRONT-INFO-03007');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_WAL_01010', 'EGH-API-FRONT-WAL-01010');
-- 结算账户新增部门层级
ALTER TABLE `stl_account` ADD COLUMN `scope` tinyint(4) NOT NULL COMMENT '结算账户部门层级';
-- 修改层级字段
UPDATE stl_account SET scope = (SELECT scope FROM dept_node WHERE dept_node.id = stl_account.dept_id);
-- 修改 部门税率 流程信息
INSERT INTO `process`(`id`, `name`, `process_num`) VALUES ('PR61E4SD9FUDWFSH', '修改部门税率', 'DEPT_TAX_RATE_UPDATE');
-- 修改 部门税率 流程节点
INSERT INTO `process_node`(`id`, `type`, `process_id`, `func_title`) VALUES ('PN625FFCC4ZYRD9M', 1, 'PR61E4SD9FUDWFSH', 'EGH-API-FRONT-TEAM-10005');
-- 修改 部门税率 流程字段
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F0BT9EMG', 'PR61E4SD9FUDWFSH', '上级部门ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F02JUKYK', 'PR61E4SD9FUDWFSH', '部门层级', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F0PS984X', 'PR61E4SD9FUDWFSH', '部门ID', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F0MPBLLW', 'PR61E4SD9FUDWFSH', '部门名称', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F068FEQY', 'PR61E4SD9FUDWFSH', '部门number', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F058M9RS', 'PR61E4SD9FUDWFSH', '税率', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF610VSCAL7', 'PR61E4SD9FUDWFSH', '税率开始时间', 1, 1);
INSERT INTO `process_field`(`id`, `process_id`, `name`, `type`, `is_not_null`) VALUES ('PF625FF5F07GUJ6N', 'PR61E4SD9FUDWFSH', '税率结束时间', 1, 1);
-- 新增收益发放批次对应结算批次关系表
CREATE TABLE `stl_bat_and_grant_bat` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '关系ID',
`bat_id` varchar(128) NOT NULL COMMENT '结算批次ID',
`grant_bat_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_stl_bat_and_grant_bat_id` (`id`) USING BTREE,
UNIQUE KEY `uk_stl_bat_and_grant_bat_bat_and_grant_bat` (`bat_id`,`grant_bat_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 其他收益数据批次
CREATE TABLE `gain_data_bat` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '流水数据批次ID',
`tab_id` varchar(32) NOT NULL COMMENT '关联模板表ID',
`plf_biz_id` varchar(64) NOT NULL COMMENT '平台业务部门ID',
`total` int(11) NOT NULL COMMENT '汇总计数',
`user_id` varchar(128) NOT NULL COMMENT '导入人用户ID',
`import_date` int(11) unsigned NOT NULL COMMENT '导入时间',
`audit_date` int(11) unsigned DEFAULT NULL COMMENT '审核时间',
`status` tinyint(4) unsigned NOT NULL COMMENT '批次状态1-待确认 2-已确认导入/待审核 10-已通过 11-已驳回',
`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_gain_data_bat_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 其他收益导入数据
CREATE TABLE `gain_biz_acct_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '原始数据ID',
`tab_id` varchar(32) NOT NULL COMMENT '关联模板表ID',
`bat_id` varchar(128) NOT NULL COMMENT '关联原始数据批次号',
`biz_acct_id` varchar(64) NOT NULL COMMENT '关联业务账号部门ID',
`biz_acct_num` varchar(64) NOT NULL COMMENT '业务账号',
`biz_acct_alias` varchar(64) NOT NULL COMMENT '业务账号花名',
`room_id` varchar(64) NOT NULL COMMENT '关联厅部门ID',
`room_num` varchar(32) NOT NULL COMMENT '房间号',
`plf_biz_id` varchar(64) NOT NULL COMMENT '平台业务ID',
`date` int(11) unsigned NOT NULL COMMENT '流水发生日期',
`cus_fields` json DEFAULT NULL COMMENT '可变字段',
`other_fields` json 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_gain_data_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 其他收益流水
CREATE TABLE `gain_biz_acct_stm_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '其他收益数据ID',
`tab_id` varchar(32) NOT NULL COMMENT '关联模板表ID',
`bat_id` varchar(128) NOT NULL COMMENT '其他收益数据批次号',
`biz_acct_id` varchar(64) NOT NULL COMMENT '关联业务账号部门ID',
`room_id` varchar(64) NOT NULL COMMENT '关联厅部门ID',
`plf_biz_id` varchar(64) NOT NULL COMMENT '平台业务ID',
`pre_tax` decimal(9,2) DEFAULT NULL COMMENT '税前',
`after_tax` decimal(9,2) DEFAULT NULL COMMENT '税后',
`consume` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '消费次数',
`date` 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 `gain_biz_acct_stm_data_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算方式设置
CREATE TABLE `stl_method_setting` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(64) NOT NULL COMMENT 'id',
`date` int(11) NOT NULL COMMENT '时间',
`dept_id` varchar(64) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(64) NOT NULL COMMENT '厅部门ID',
`stl_type` tinyint(3) unsigned NOT NULL COMMENT '结算方式',
`guarantee_amount` decimal(10,2) NOT NULL COMMENT '保底金额',
`tax_type` tinyint(3) NOT NULL COMMENT '税前税后',
`is_effect` tinyint(1) NOT NULL COMMENT '是否生效',
`scope` tinyint(4) unsigned NOT NULL COMMENT '层级范围：1-公会 2-组 3-厅 4-业务账号 99-职能部门',
`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 `view_account_stl_statistical` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算统计表ID',
`date` int(11) NOT NULL COMMENT '结算时间',
`dept_id` varchar(128) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(128) NOT NULL COMMENT '上级部门ID',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`group_id` varchar(128) NOT NULL COMMENT '运营组ID',
`stl_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算类型',
`content` json DEFAULT NULL COMMENT '数据json',
`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_view_account_stl_statistical_id` (`id`) USING BTREE,
KEY `k_date` (`date`) USING BTREE,
KEY `k_dept_id` (`dept_id`) USING BTREE,
KEY `k_prev_dept_id` (`prev_dept_id`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_group_id` (`group_id`) USING BTREE,
KEY `k_stl_type` (`stl_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增结算统计表(厅)
CREATE TABLE `view_room_stl_statistical` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算统计表ID',
`date` int(11) NOT NULL COMMENT '结算时间',
`dept_id` varchar(128) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(128) NOT NULL COMMENT '上级部门ID',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`stl_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算类型',
`content` json DEFAULT NULL COMMENT '数据json',
`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_view_room_stl_statistical_id` (`id`) USING BTREE,
KEY `k_date` (`date`) USING BTREE,
KEY `k_dept_id` (`dept_id`) USING BTREE,
KEY `k_prev_dept_id` (`prev_dept_id`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_stl_type` (`stl_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 新增结算发放批次表
CREATE TABLE `view_stl_stm_grant_bat` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算发放批次ID',
`room_id` varchar(128) NOT NULL COMMENT '部门id',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`start_stl_date` int(11) NOT NULL COMMENT '结算开始时间',
`end_stl_date` int(11) NOT NULL COMMENT '结算结束时间',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '发放类型',
`bat_info` json DEFAULT NULL COMMENT '数据json',
`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_view_stl_stm_grant_bat` (`id`) USING BTREE,
KEY `k_room_id` (`room_id`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_start_stl_date` (`start_stl_date`) USING BTREE,
KEY `k_end_stl_date` (`end_stl_date`) USING BTREE,
KEY `k_status` (`status`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增结算账户收入表
CREATE TABLE `stl_acct_income` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算账户收入ID',
`stl_date` int(11) NOT NULL COMMENT '结算时间',
`dept_scope` tinyint(4) unsigned NOT NULL COMMENT '层级',
`dept_id` varchar(128) NOT NULL COMMENT '结算部门ID',
`income_from_id` varchar(128) NOT NULL COMMENT '收入来源',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`stl_acct_id` varchar(128) NOT NULL COMMENT '结算账户ID',
`income_amount` decimal(10,2) NOT NULL COMMENT '收入金额',
`income_time` int(11) NOT NULL COMMENT '收入时间',
`income_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '发放类型',
`stl_bat_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_stl_account_income` (`id`) USING BTREE,
KEY `k_stl_date` (`stl_date`) USING BTREE,
KEY `k_scope` (`dept_scope`) USING BTREE,
KEY `k_dept_id` (`dept_id`) USING BTREE,
KEY `k_income_from_id` (`income_from_id`) USING BTREE,
KEY `k_stl_type` (`stl_cycle`) USING BTREE,
KEY `k_stl_acct_id` (`stl_acct_id`) USING BTREE,
KEY `k_income_time` (`income_time`) USING BTREE,
KEY `k_income_status` (`income_status`) USING BTREE,
KEY `k_stl_bat_id` (`stl_bat_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 钱包出入记录新增打包ID
ALTER TABLE `wallet_income` ADD COLUMN `pack_id` VARCHAR ( 256 ) NULL COMMENT '打包ID';
-- 新增结算账户出入记录
CREATE TABLE `stl_acct_detail` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算账户收入ID',
`create_time` int(11) NOT NULL COMMENT '结算时间',
`stl_dept_id` varchar(128) NOT NULL COMMENT '结算部门ID',
`stl_acct_id` varchar(128) NOT NULL COMMENT '结算账户ID',
`title` varchar(256) NOT NULL COMMENT 'title',
`direction` tinyint(1) DEFAULT NULL COMMENT ' 1-提现 2-收入',
`amount` decimal(13,2) 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_stl_account_detail` (`id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增收益发放批次
CREATE TABLE `view_earnings_grant_bat` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '收益发放批次ID',
`plf_biz_id` varchar(128) NOT NULL COMMENT '结算时间',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`start_stl_date` int(11) NOT NULL COMMENT '结算开始时间',
`end_stl_date` int(11) NOT NULL COMMENT '结算结束时间',
`grant_amount` decimal(13,2) NOT NULL COMMENT '发放金额',
`grant_time` int(11) NOT NULL COMMENT '发放时间',
`bat_remark` varchar(128) 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_view_earnings_grant_bat` (`id`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_start_stl_date` (`start_stl_date`) USING BTREE,
KEY `k_end_stl_date` (`end_stl_date`) USING BTREE,
KEY `k_grant_time` (`grant_time`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增收益发放用户记录
CREATE TABLE `view_earnings_grant_rec` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '收益发放用户记录ID',
`wallet_id` varchar(128) NOT NULL COMMENT '钱包ID',
`user_id` varchar(128) NOT NULL COMMENT '用户ID',
`grant_amount` decimal(13,2) NOT NULL COMMENT '发放金额',
`grant_time` int(11) NOT NULL COMMENT '发放时间',
`grant_bat_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_view_earnings_grant_rec` (`id`) USING BTREE,
KEY `k_wallet_id` (`wallet_id`) USING BTREE,
KEY `k_user_id` (`user_id`) USING BTREE,
KEY `k_grant_time` (`grant_time`) USING BTREE,
KEY `k_grant_bat_id` (`grant_bat_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增收益发放详细记录
CREATE TABLE `view_earnings_grant_detail` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '收益发放详细ID',
`stl_start_date` int(11) NOT NULL COMMENT '结算开始时间',
`stl_end_date` int(11) NOT NULL COMMENT '结算结束时间',
`dept_id` varchar(128) NOT NULL COMMENT '结算部门ID',
`prev_dept_id` varchar(128) NOT NULL COMMENT '上级部门ID',
`stl_earning` decimal(13,2) NOT NULL COMMENT '业务收益',
`guarantee_tax_before` decimal(13,2) NOT NULL COMMENT '保底收益税前',
`guarantee_tax_after` decimal(13,2) NOT NULL COMMENT '保底收益税后',
`other_tax_before` decimal(13,2) NOT NULL COMMENT '其他收益税前',
`other_tax_after` decimal(13,2) NOT NULL COMMENT '其他收益税后',
`tax_rate` varchar(128) NOT NULL COMMENT '税率',
`sum_earning_tax_after` decimal(13,2) NOT NULL COMMENT '总收益税后',
`grant_rec_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_view_earnings_grant_detail` (`id`) USING BTREE,
KEY `k_grant_rec_id` (`grant_rec_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增提现统计表
CREATE TABLE `view_withdrawl_statistics` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '提现统计ID',
`wallet_id` varchar(128) NOT NULL COMMENT '钱包ID',
`from_id` varchar(128) NOT NULL COMMENT '来源ID',
`from_type` varchar(128) NOT NULL COMMENT '来源类型',
`income_id` varchar(128) NOT NULL COMMENT '收入ID',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`group_id` varchar(128) NOT NULL COMMENT '运营组ID',
`company_id` varchar(128) NOT NULL COMMENT '公司ID',
`income_time` int(11) NOT NULL COMMENT '收入时间',
`income_amount` decimal(13,2) NOT NULL COMMENT '收入金额',
`withdrawl_time` int(11) DEFAULT NULL COMMENT '提现时间',
`status` tinyint(1) NOT NULL COMMENT '是否提现 0-未提现 11-已提现(同步收入状态)',
`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_view_withdrawl_statistics` (`id`) USING BTREE,
KEY `k_wallet_id` (`wallet_id`) USING BTREE,
KEY `k_from_id` (`from_id`) USING BTREE,
KEY `k_from_type` (`from_type`) USING BTREE,
KEY `k_income_id` (`income_id`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_group_id` (`group_id`) USING BTREE,
KEY `k_company_id` (`company_id`) USING BTREE,
KEY `k_income_time` (`income_time`) USING BTREE,
KEY `k_withdrawl_time` (`withdrawl_time`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 新增钱包收入信息展示表
CREATE TABLE `view_wallet_income` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '提现统计ID',
`wallet_id` varchar(128) NOT NULL COMMENT '钱包ID',
`stl_start_date` int(11) NOT NULL COMMENT '结算开始时间',
`stl_end_date` int(11) NOT NULL COMMENT '结算结束时间',
`dept_id` varchar(128) NOT NULL COMMENT '部门ID',
`prev_dept_id` varchar(128) NOT NULL COMMENT '上级部门ID',
`income_time` int(11) NOT NULL COMMENT '收入时间',
`income_type` varchar(128) NOT NULL COMMENT '收入类型',
`stl_cycle` tinyint(1) NOT NULL DEFAULT '1' COMMENT '结算模式',
`plf_biz_id` varchar(128) NOT NULL COMMENT '平台业务ID',
`income_pack_id` varchar(128) NOT NULL COMMENT '收入打包ID',
`income_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '收入状态',
`income_info` json 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_view_wallet_inceom` (`id`) USING BTREE,
KEY `k_wallet_id` (`wallet_id`) USING BTREE,
KEY `k_start_stl_date` (`stl_start_date`) USING BTREE,
KEY `k_end_stl_date` (`stl_end_date`) USING BTREE,
KEY `k_dept_id` (`dept_id`) USING BTREE,
KEY `k_prev_dept_id` (`prev_dept_id`) USING BTREE,
KEY `k_income_time` (`income_time`) USING BTREE,
KEY `k_income_type` (`income_type`) USING BTREE,
KEY `k_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_income_pack_id` (`income_pack_id`) USING BTREE,
KEY `k_income_status` (`income_status`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;
-- 保底数据表
CREATE TABLE `min_guar_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '保底数据ID',
`date` int(11) NOT NULL COMMENT '时间',
`tax_before` decimal(13,3) DEFAULT NULL COMMENT '税前',
`tax_after` decimal(13,3) DEFAULT NULL COMMENT '税后',
`is_effect` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:失效1:生效',
`scope` tinyint(4) unsigned NOT NULL COMMENT '层级',
`dept_id` varchar(128) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(64) DEFAULT 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_min_guar_data_id` (`id`) USING BTREE,
KEY `k_min_guar_data_is_effect` (`is_effect`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据批次表
CREATE TABLE `stl_stm_bat_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '批次id',
`stl_date` int(11) NOT NULL COMMENT '结算时间',
`status` tinyint(1) NOT NULL DEFAULT '5' COMMENT '5:待审核 7:结算主管已通过8:结算主管已驳回10:业务主管已通过 11:业务主管已驳回 15:已发放',
`reason` varchar(255) 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_stl_stm_bat_data_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 税率表
CREATE TABLE `rec_dept_tax_rate` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '税率数据ID',
`date` int(11) NOT NULL COMMENT '时间',
`tax_rate` decimal(4,3) NOT NULL COMMENT '税率',
`is_effect` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:失效1:生效',
`scope` tinyint(4) unsigned NOT NULL COMMENT '层级',
`dept_id` varchar(128) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(64) DEFAULT 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_rec_dept_tax_rate_id` (`id`) USING BTREE,
KEY `k_rec_dept_tax_rate_is_effect` (`is_effect`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据表
CREATE TABLE `stl_stm_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '结算数据表ID',
`date` int(11) NOT NULL COMMENT '时间',
`tax_before` decimal(13,2) NOT NULL COMMENT '税前',
`tax_after` decimal(13,2) NOT NULL COMMENT '税后',
`data_type` tinyint(1) NOT NULL COMMENT '数据类型1:业务数据2:保底数据3:补充数据',
`scope` tinyint(4) unsigned NOT NULL COMMENT '层级',
`dept_id` varchar(128) NOT NULL COMMENT '部门id',
`prev_dept_id` varchar(64) DEFAULT NULL COMMENT '上级部门id',
`stl_cycle` tinyint(3) unsigned NOT NULL COMMENT '批次结算模式',
`all_tax_earnings` decimal(13,2) NOT NULL COMMENT '税后收益',
`bat_id` varchar(128) NOT NULL COMMENT '批次id',
`pack_id` varchar(128) NOT NULL COMMENT '打包id',
`tax_rate` decimal(4,3) 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_stl_stm_data_id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=225 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 展示结算数据批次表
CREATE TABLE `view_stl_bat_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '展示结算数据批次表ID',
`room_id` varchar(128) NOT NULL COMMENT '厅id',
`plf_biz_id` varchar(64) DEFAULT NULL COMMENT '平台业务id',
`stl_cycle` tinyint(3) unsigned NOT NULL COMMENT '批次结算模式',
`start_stl_date` int(11) NOT NULL COMMENT '开始结算',
`end_stl_date` int(11) NOT NULL COMMENT '结束结算',
`stl_date` int(11) NOT NULL COMMENT '结算日期',
`status` tinyint(1) NOT NULL DEFAULT '5' COMMENT '5:待审核 7:结算主管已通过8:结算主管已驳回10:业务主管已通过 11:业务主管已驳回 15:已发放',
`reason` varchar(255) DEFAULT NULL COMMENT '驳回原因',
`content` json DEFAULT NULL COMMENT '数据json',
`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_view_stl_bat_dat_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据批次业务账号流水表
CREATE TABLE `view_bat_stm_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`pack_id` varchar(128) NOT NULL COMMENT '打包id',
`biz_acct_id` varchar(128) NOT NULL COMMENT '业务账号id',
`content` json DEFAULT NULL COMMENT '数据json',
`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 '最新修改时间',
`bat_id` varchar(128) NOT NULL COMMENT '批次id',
PRIMARY KEY (`no_`) USING BTREE,
UNIQUE KEY `uk_view_bat_stm_data_pack_id` (`pack_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据批次业务账号流水详情业务收益明细表
CREATE TABLE `view_stl_stm_earnings_detail` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '流水号',
`pack_id` varchar(128) NOT NULL COMMENT '打包id',
`start_stl_date` int(11) NOT NULL COMMENT '开始结算',
`tax_rate` decimal(4,3) NOT NULL COMMENT '税率',
`stl_data` decimal(13,2) NOT NULL COMMENT '业务流水',
`earnings_data` decimal(13,2) 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_view_stl_stm_earnings_detail_id` (`id`) USING BTREE,
KEY `k_view_stl_stm_earnings_detail_pack_id` (`pack_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据批次业务账号流水详情业务保底收益明细表
CREATE TABLE `view_stl_ensure_earnings_detail` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '流水号',
`pack_id` varchar(128) NOT NULL COMMENT '打包id',
`start_stl_date` int(11) NOT NULL COMMENT '开始结算',
`end_stl_date` int(11) NOT NULL COMMENT '结束结算',
`tax_rate` decimal(4,3) NOT NULL COMMENT '税率',
`tax_before` decimal(13,3) NOT NULL COMMENT '税前',
`tax_after` decimal(13,3) 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_view_stl_ensure_earnings_detail_id` (`id`) USING BTREE,
KEY `k_view_stl_ensure_earnings_detail_pack_id` (`pack_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 结算数据批次业务账号流水详情业务其他收益明细表
CREATE TABLE `view_stl_other_earnings_detail` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`id` varchar(128) NOT NULL COMMENT '流水号',
`pack_id` varchar(128) NOT NULL COMMENT '打包id',
`start_stl_date` int(11) NOT NULL COMMENT '开始结算',
`end_stl_date` int(11) NOT NULL COMMENT '结束结算',
`tax_rate` decimal(4,3) NOT NULL COMMENT '税率',
`tax_before` decimal(13,3) NOT NULL COMMENT '税前',
`tax_after` decimal(13,3) 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_view_stl_other_earnings_detail_id` (`id`) USING BTREE,
KEY `k_view_stl_other_earnings_detail_pack_id` (`pack_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 已结算业务数据缓存表
CREATE TABLE `stl_cache_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`room_id` varchar(128) NOT NULL COMMENT '厅id',
`content` json DEFAULT NULL COMMENT '数据json',
`date` int(11) NOT NULL COMMENT '时间',
`stl_cycle` tinyint(3) unsigned NOT NULL COMMENT '结算模式',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1.审核中2.已通过',
`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 '最新修改时间',
`bat_id` varchar(128) NOT NULL COMMENT '结算批次id',
PRIMARY KEY (`no_`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
-- 发起结算结果记录表
CREATE TABLE `view_stl_results_data` (
`no_` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键、自增',
`room_id` varchar(128) NOT NULL COMMENT '厅id',
`plf_biz_id` varchar(64) DEFAULT NULL COMMENT '平台业务id',
`stl_cycle` tinyint(3) unsigned NOT NULL COMMENT '结算模式',
`start_stl_date` int(11) NOT NULL COMMENT '开始结算',
`end_stl_date` int(11) NOT NULL COMMENT '结束结算',
`stl_date` int(11) NOT NULL COMMENT '结算日期',
`content` json DEFAULT NULL COMMENT '说明json',
`status` tinyint(1) NOT NULL COMMENT '结算状态1.成功2.失败',
`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,
KEY `k_view_stl_results_data_room_id` (`room_id`) USING BTREE,
KEY `k_view_stl_results_data_plf_biz_id` (`plf_biz_id`) USING BTREE,
KEY `k_view_stl_results_data_stl_cycle` (`stl_cycle`) USING BTREE,
KEY `k_view_stl_results_data_start_stl_date` (`start_stl_date`) USING BTREE,
KEY `k_view_stl_results_data_end_stl_date` (`end_stl_date`) USING BTREE,
KEY `k_view_stl_results_data_stl_date` (`stl_date`) USING BTREE,
KEY `k_view_stl_results_data_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
</code></pre>