java-insagent-pc/sql/Online.sql

--  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 '未结算金额',
                                                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 (645, '非跟单待提现', '201', '/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, '推广待提现', '201', '/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, '手续费已提现', '201', '/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, '非跟单已提现', '201', '/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, '推广已提现', '201', '/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 (643, '手续费待提现', '201', '/financialManagement/withdrawalManagement/withdrawalProceduresPending', NULL, 1, 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(30) 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);