-- add by caiyaru 20240806 DROP TABLE IF EXISTS `sys_qr_code_relation`; create table `sys_qr_code_relation`( `id` bigint auto_increment PRIMARY KEY comment '主键id', `api_type` tinyint comment 'api类型（1、api对接，2、python对接）', `ins_company_id` varchar(32) comment '保险公司ID', `ins_company_name` varchar(255) comment '保险公司名称', `qr_code_type` tinyint comment '二维码类型（0:Base64,1:内部链接,2:外部链接）', `create_by` varchar(255) comment '创建操作人', `create_time` timestamp comment '创建时间', `last_update_by` varchar(255) comment '更新操作人', `last_update_time` timestamp comment '更新时间', CONSTRAINT `sys_qr_code_relation_uk` unique(`api_type`,`ins_company_id`,`ins_company_name`,`qr_code_type`) ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin comment='二维码类型关联表' ROW_FORMAT = Dynamic; INSERT INTO `sys_dict_type`(`id`, `name`, `parent_id`, `itemname`, `description`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ('108', '车险平台', NULL, 'insurancePlatform', NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '0', 0); INSERT INTO `sys_dict_type`(`id`, `name`, `parent_id`, `itemname`, `description`, `create_time`, `update_time`, `remarks`, `del_flag`, `tenant_id`) VALUES ('108001', '二维码', '108', 'QRCode', NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '0', 0); INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('0', 'Base64', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP); INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('1', '内部链接', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP); INSERT INTO `sys_dict`( `value`, `label`, `itemname`, `description`, `sort`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `remarks`, `del_flag`, `create_user_id`, `update_user_id`, `update_time`) VALUES ('2', '外部链接', 'QRCode', 'desc', 0, NULL, NULL, NULL, NULL, 'remark', 0, 0, 0, CURRENT_TIMESTAMP); DROP TABLE IF EXISTS `sys_user_track`; create table `sys_user_track`( `id` bigint auto_increment PRIMARY KEY comment '主键id', `user_id` varchar(30) not null comment '用户id', `status` tinyint comment '状态 0,"离司",1,"正常",2,"入司待审核",3,"入司审核驳回",4,"离司待审核",5,"离司审核驳回"', `approve_by` varchar(255) comment '审核人', `approve_time` timestamp comment '审核时间', `approve_mess` varchar(255) comment '审核信息', index `idx_sys_user_track_user_id`(`user_id`), index `idx_sys_user_track_approve_time`(`approve_time`) ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin comment='系统用户审核轨迹表' ROW_FORMAT = Dynamic; --add by helixiao 20240812 DROP TABLE IF EXISTS `sys_agency_fee_approval`; CREATE TABLE `sys_agency_fee_approval` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', `user_id` varchar(30) DEFAULT NULL COMMENT '员工id', `user_name` varchar(50) DEFAULT NULL COMMENT '员工姓名', `dept_id` varchar(30) DEFAULT NULL COMMENT '机构id', `dept_name` varchar(50) DEFAULT NULL COMMENT '机构名称', `real_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '银行卡户主', `bank_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '银行卡号', `bank_account` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '开户行', `bank_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '开户行地址', `number` varchar(255) DEFAULT NULL COMMENT '提现编号', `sumpremium` decimal(10,2) DEFAULT NULL COMMENT '总保费', `proportional` varchar(10) DEFAULT NULL COMMENT '抽成比例（总保费抽成）', `extract_fee` decimal(10,2) DEFAULT NULL COMMENT '总保费抽成费用', `history_fee` decimal(10,2) DEFAULT NULL COMMENT '已提现抽成费用', `cash_fee` decimal(10,2) DEFAULT NULL COMMENT '可提现抽成费用', `start_time` datetime DEFAULT NULL COMMENT '提层计算时间', `audit_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '审核人', `status` varchar(5) NOT NULL DEFAULT '0' COMMENT '审核状态0未审核 1审核通过 2审核驳回', `audit_time` datetime DEFAULT NULL COMMENT '审核时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_by` varchar(50) DEFAULT NULL COMMENT '创建人', `remark` varchar(255) DEFAULT NULL COMMENT '审批意见', `picture` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '附件图片', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='机构保费抽成审批表'; DROP TABLE IF EXISTS `sys_agency_fee_approval_record`; CREATE TABLE `sys_agency_fee_approval_record` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', `approval_id` int DEFAULT NULL COMMENT '审批id', `user_id` varchar(30) DEFAULT NULL COMMENT '员工id', `user_name` varchar(50) DEFAULT NULL COMMENT '员工姓名', `dept_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构id', `dept_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '机构名称', `real_user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '银行卡户主', `bank_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '银行卡号', `bank_account` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '开户行', `bank_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '开户行地址', `number` varchar(255) DEFAULT NULL COMMENT '提现编号', `sumpremium` decimal(10,2) DEFAULT NULL COMMENT '总保费', `proportional` varchar(10) DEFAULT NULL COMMENT '抽成比例（总保费抽成）', `extract_fee` decimal(10,2) DEFAULT NULL COMMENT '总保费抽成费用', `history_fee` decimal(10,2) DEFAULT NULL COMMENT '已提现抽成费用', `cash_fee` decimal(10,2) DEFAULT NULL COMMENT '可提现抽成费用', `start_time` datetime DEFAULT NULL COMMENT '提层计算时间', `audit_user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '审核人', `status` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '审核状态0未审核 1审核通过 2审核驳回', `audit_time` datetime DEFAULT NULL COMMENT '审核时间', `create_time` datetime DEFAULT NULL COMMENT '操作时间', `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '操作人', `remark` varchar(255) DEFAULT NULL COMMENT '审批意见', `picture` varchar(255) DEFAULT NULL COMMENT '附件图片', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='机构保费抽成审批表记录表'; DROP TABLE IF EXISTS `sys_agency_leader`; CREATE TABLE `sys_agency_leader` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id', `user_id` varchar(30) DEFAULT NULL COMMENT '员工id', `user_name` varchar(50) DEFAULT NULL COMMENT '员工姓名', `dept_id` varchar(30) DEFAULT NULL COMMENT '机构id', `dept_name` varchar(50) DEFAULT NULL COMMENT '机构name', `comlevel` varchar(10) DEFAULT NULL COMMENT '机构级别', `source` varchar(5) DEFAULT NULL COMMENT '业务来源', `proportional` varchar(10) DEFAULT NULL COMMENT '抽成比例（总保费抽成）', `suggest_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '推荐人id', `suggest_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '推荐人name', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_by` varchar(50) DEFAULT NULL COMMENT '创建人', `del_flag` tinyint NOT NULL DEFAULT '1' COMMENT '删除（ 0：删除 1：正常）', `update_time` datetime DEFAULT NULL COMMENT '修改时间', `update_by` varchar(50) DEFAULT NULL COMMENT '修改人', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='配置机构负责人表'; DROP TABLE IF EXISTS `sys_partner`; CREATE TABLE `sys_partner` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id', `user_id` varchar(30) DEFAULT NULL COMMENT '员工id', `user_name` varchar(50) DEFAULT NULL COMMENT '员工姓名', `proportional` varchar(10) DEFAULT NULL COMMENT '抽成比例（总保费抽成）', `suggest_id` varchar(30) DEFAULT NULL COMMENT '推荐人id', `suggest_name` varchar(50) DEFAULT NULL COMMENT '推荐人name', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_by` varchar(50) DEFAULT NULL COMMENT '创建人', `del_flag` tinyint NOT NULL DEFAULT '1' COMMENT '删除（ 0：删除 1：正常）', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='配置机构合伙人表'; DROP TABLE IF EXISTS `sys_relation_person`; CREATE TABLE `sys_relation_person` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id', `agency_leader_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '被推荐人id', `suggest_id` varchar(50) DEFAULT NULL COMMENT '推荐人id', `source` tinyint DEFAULT NULL COMMENT '推荐等级(0:直接推荐；1：间接推荐)', `type` tinyint DEFAULT NULL COMMENT '推荐类型（0：合伙人 1：机构负责人）', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `create_by` varchar(50) DEFAULT NULL COMMENT '创建人', `del_flag` tinyint NOT NULL DEFAULT '1' COMMENT '删除（ 0：删除 1：正常）', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='合伙人/推荐人和机构负责人关系表'; ALTER TABLE `sys_user_bank_card` ADD COLUMN `ascription` tinyint DEFAULT NULL COMMENT '归属端 1：合伙人绑卡 2：机构负责人绑卡'; --add by caiyaru 20240813 ALTER TABLE `sys_user_bank_card` ADD COLUMN `bank_verify` tinyint DEFAULT 0 COMMENT '0 未验证 1 已验证'; -- 控制台角色关联菜单表 DROP TABLE IF EXISTS `sys_role_menu_kzt`; CREATE TABLE `sys_role_menu_kzt` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `role_id` bigint NULL DEFAULT NULL COMMENT '角色ID', `menu_id` bigint NULL DEFAULT NULL COMMENT '菜单ID', `sys_type` varchar(10) NULL DEFAULT NULL COMMENT '系统类型', `create_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人', `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间', `last_update_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人', `last_update_time` datetime NULL DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '控制台角色关联菜单' ROW_FORMAT = Dynamic; -- 控制台用户关联角色表 DROP TABLE IF EXISTS `sys_user_role_kzt`; CREATE TABLE `sys_user_role_kzt` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID', `role_id` bigint NULL DEFAULT NULL COMMENT '角色ID', `sys_type` varchar(10) NULL DEFAULT NULL COMMENT '系统类型', `create_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人', `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间', `last_update_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '更新人', `last_update_time` datetime NULL DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '控制台用户关联角色' ROW_FORMAT = Dynamic; -- 保险手续费结算关联表 DROP TABLE IF EXISTS `sys_insurance_handling_settlement_month`; CREATE TABLE `sys_insurance_handling_settlement_month` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id', `task_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '任务id', `settlement_documentary_fees_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '结算id', `settlement_amount` decimal(15,5) DEFAULT NULL COMMENT '结算金额', `total_amount` decimal(15,5) DEFAULT NULL COMMENT '开票金额', `remaining_amount` decimal(15,5) DEFAULT NULL COMMENT '剩余结算金额', `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间', `signing_time` datetime NULL DEFAULT NULL COMMENT '创建时间', `creater_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '保险手续费结算关联表' ROW_FORMAT = Dynamic;