-- 2024-04-15 武鸿鹏 结算需求 ALTER TABLE `ins_ply_income` ADD COLUMN `voucher_number` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL COMMENT '凭证号' AFTER `create_time`; ALTER TABLE `ins_ply_income` ADD COLUMN `contract_id` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL COMMENT '合同号' AFTER `voucher_number`; ALTER TABLE `ins_ply_income` ADD COLUMN `contract_file_id` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NULL DEFAULT NULL COMMENT '合同附件id' AFTER `contract_id`; ALTER TABLE `ins_ply_income` ADD COLUMN `handling_fees_status` varchar(5) NULL COMMENT '手续费结算状态 1 为已结算' AFTER `contract_file_id`; ALTER TABLE `ins_ply_income` ADD COLUMN `documentary_fees_status` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '跟单费结算状态 1 为已结算' AFTER `handling_fees_status`; ALTER TABLE `ins_ply_income` ADD COLUMN `is_not_car` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '跟单费结算状态 1 为已结算' AFTER `documentary_fees_status`; ALTER TABLE `ins_ply_income` ADD COLUMN `no_policyno` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '非车保单号' AFTER `is_not_car`; ALTER TABLE `ins_ply_income` ADD COLUMN `sy_policyno` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商业保单号' AFTER `no_policyno`; DROP TABLE IF EXISTS `settlement_documentary_fees`; CREATE TABLE `settlement_documentary_fees` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id', `total_orders` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '结算单数', `time_frame` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '时间范围', `total_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '结算总金额', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `start_time` datetime(0) NULL DEFAULT NULL COMMENT '开始时间', `end_time` datetime(0) NULL DEFAULT NULL COMMENT '结束时间', `settlement_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '结算金额', `unsettled_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '未结算金额', `company_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '保险公司id', `signing_time` datetime(0) NULL DEFAULT NULL COMMENT '签单日期', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `id`(`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '跟单费结算' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; DROP TABLE IF EXISTS `settlement_documentary_log`; CREATE TABLE `settlement_documentary_log` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id', `amount_type` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '金额类型 1 手续费结算 2 跟单费结算', `amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '金额', `settlement_documentary_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联跟单费id', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `handing_amount` decimal(10, 2) NULL DEFAULT NULL COMMENT '手续费结算金额', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `id`(`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '跟单费结算' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; -- 菜单sql 结算菜单 提现菜单 INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (643, '手续费待提现', '663', '/financialManagement/withdrawalManagement/withdrawalProceduresPending', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (645, '非跟单待提现', '665', '/financialManagement/withdrawalManagement/nonDocumentaryPending', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (646, '推广待提现', '664', '/financialManagement/withdrawalManagement/generalizationPending', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (647, '手续费已提现', '663', '/financialManagement/withdrawalManagement/withdrawalProceduresEnd', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (648, '非跟单已提现', '665', '/financialManagement/withdrawalManagement/nonDocumentaryEnd', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (649, '推广已提现', '664', '/financialManagement/withdrawalManagement/generalizationEnd', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (650, '审核并支付', '643', NULL, 'sys:withdrawalProceduresPending:examine', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (651, '审核提现', '643', NULL, 'sys:withdrawalProceduresPending:examinepay', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (652, '审核并支付', '645', NULL, 'sys:nonDocumentaryPending:examine', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (653, '审核提现', '645', NULL, 'sys:nonDocumentaryPending:examinepay', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (654, '审核提现', '646', NULL, 'sys:generalizationPending:examinepay', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (655, '审核并支付', '646', NULL, 'sys:generalizationPending:examine', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (663, '手续费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (664, '推广费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (665, '非跟单费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); -- inv_source表 新增修改人名字 修改人id 前端还没联调 正式库先不加字段 ALTER TABLE `inv_source` ADD COLUMN `update_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '修改人名字' AFTER `revenue_userid`; ALTER TABLE `inv_source` ADD COLUMN `update_userid` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '修改人id' AFTER `update_name`; -- add by hxl 2024-04-18 添加后线人员关联协议表 DROP TABLE IF EXISTS `sys_user_link_pl_agreement`; CREATE TABLE `sys_user_link_pl_agreement` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键/自增', `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前线用户ID', `ptl_agreement_id` bigint(20) NULL DEFAULT NULL COMMENT '协议ID', `create_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人ID', `create_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人名称', `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间', `update_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人ID', `update_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人名称', `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间', `type` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '协议类型 0 车险 1 非车', PRIMARY KEY (`id`) USING BTREE, INDEX `user_id_index`(`user_id`) USING BTREE, INDEX `ptl_agreement_id_index`(`ptl_agreement_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '后线人员关联协议表' ROW_FORMAT = DYNAMIC; -- add by hxl 2024-04-18 添加子订单表操作人和操作时间和操作人名称 ALTER TABLE ins_area_company ADD COLUMN operator_id VARCHAR(30) COMMENT '操作人'; ALTER TABLE ins_area_company ADD COLUMN operator_name VARCHAR(100) COMMENT '操作人名称'; ALTER TABLE ins_area_company ADD COLUMN operator_time datetime COMMENT '操作时间'; -- add by hxl 2024-04-18 添加子订单转发收回记录表 DROP TABLE IF EXISTS `ins_order_set_user_his`; CREATE TABLE `ins_order_set_user_his` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键/自增', `orderno` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号', `quoteno` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报价单号', `old_operator_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原操作人', `new_operator_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '现操作人', `create_time` datetime NULL DEFAULT NULL COMMENT '操作时间', `create_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人', `suborder_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '子订单id', `old_operator_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原操作人名称', `new_operator_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '现操作人名称', `remark` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '子订单转发收回记录表' ROW_FORMAT = DYNAMIC; -- add by hy 2024-04-19 添加协议授权表 DROP TABLE IF EXISTS `ptl_agreement_dept_history`; CREATE TABLE `ptl_agreement_dept_history` ( `agreement_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '协议ID', `dept_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门ID', `batch` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '批次' ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- add by hy 2024-04-19 添加协议表产品类型字段 ALTER TABLE ptl_agreement ADD COLUMN products_code VARCHAR(30) COMMENT '产品类型'; -- add by hxl 2024-04-19 添加后线人员协议配置菜单 INSERT INTO `sys_menu` (`name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES ('后线人员协议配置', '232', '/agreementManage/carInsurance/backlineAllocation', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); -- add by hxl 2024-04-18 添加子订单表新操作人和新操作人名称 ALTER TABLE ins_area_company ADD COLUMN new_operator_id VARCHAR(30) COMMENT '新操作人'; ALTER TABLE ins_area_company ADD COLUMN new_operator_name VARCHAR(100) COMMENT '新操作人名称'; -- 2024-0419 武鸿鹏 ALTER TABLE `settlement_documentary_log` ADD COLUMN `company_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '保险公司id' AFTER `handing_amount`; ALTER TABLE `settlement_documentary_log` ADD COLUMN `year` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '年度' AFTER `company_id`; ALTER TABLE `ins_ply_income` ADD COLUMN `is_not_documentary` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '是否是跟单' AFTER `is_not_car`; INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (663, '手续费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (664, '推广费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (665, '非跟单费提现', '201', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (667, '车险结算', '200', '/financialManagement/carInsurance/platformSettlement/carInsuranceBalance', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (668, '非车结算', '200', '/financialManagement/carInsurance/platformSettlement/nonVehicleBalance', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (669, '车险', '199', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (670, '非车', '199', NULL, NULL, 0, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (671, '手续费结算', '669', '/financialManagement/carInsurance/companySettlement/carInsurance/handlingFees', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (672, '手续费结算', '670', '/financialManagement/carInsurance/companySettlement/noCarInsurance/handlingFees', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (673, '跟单费结算', '669', '/financialManagement/carInsurance/companySettlement/carInsurance/documentaryFees', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (674, '跟单费结算', '670', '/financialManagement/carInsurance/companySettlement/noCarInsurance/documentaryFees', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (677, '结算', '673', NULL, 'sys:documentaryFees1:balance', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu`(`id`, `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (678, '结算', '674', NULL, 'sys:documentaryFees2:balance', 2, NULL, 0, NULL, NULL, NULL, NULL, 0); -- 2024-0422 武鸿鹏 ALTER TABLE `settlement_documentary_log` ADD COLUMN `crud_status` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作表示 1.新增 2 修改 3 删除' AFTER `handing_amount`; -- add by hxl 2024-04-22 添加渠道、保险公司、代理人任务池菜单 INSERT INTO `sys_menu` ( `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES ('任务池（渠道）', '64', '/task/taskPoolChannel', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu` (`name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES ('任务池（保险公司）', '64', '/task/companyPoolChannel', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0); INSERT INTO `sys_menu` ( `name`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES ( '任务池（代理人）', '64', '/task/agentPoolChannel', NULL, 1, NULL, 0, NULL, NULL, NULL, NULL, 0);