易公会数据库v1.6调整记录
<pre><code class="language-sql">-- --------------- 已上线 ----------------- V1.6.1 -----------------
-- 新增权限
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_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_TEAM_04020', 'EGH-API-FRONT-TEAM-04020');
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_11102', 'EGH-API-FRONT-TEAM-11102');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11103', 'EGH-API-FRONT-TEAM-11103');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11104', 'EGH-API-FRONT-TEAM-11104');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11105', 'EGH-API-FRONT-TEAM-11105');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11106', 'EGH-API-FRONT-TEAM-11106');
INSERT INTO `api_privilege`(`func_title`, `api_title`) VALUES ('EGH_TEAM_11107', 'EGH-API-FRONT-TEAM-11107');
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_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_04019', 'EGH-API-FRONT-STL-04019');
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');
-- 钱包出入记录新增打包ID
ALTER TABLE `wallet_income` ADD COLUMN `pack_id` VARCHAR ( 256 ) NULL COMMENT '打包ID';
-- 新增 字段
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-启用';
-- 结算账户新增部门层级
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;
-- 新增结算账户出入记录
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 '业务收益',
`all_tax_earnings` 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,2) NOT NULL COMMENT '税前',
`tax_after` decimal(13,2) NOT NULL COMMENT '税后',
`all_tax_earnings` 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_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,2) NOT NULL COMMENT '税前',
`tax_after` decimal(13,2) NOT NULL COMMENT '税后',
`all_tax_earnings` 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_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>