嗨玩后端开发规范


易公会Plus v0.3 调整记录

-- ---------------  未上线  ----------------- V0.3.1 -----------------
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;

页面列表

ITEM_HTML