java-insagent-pc/sql/Online.sql

-- 协议调度表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_tax_source_new`;
CREATE TABLE `ptl_tax_source_new`  (
                                       `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                       `ins_company_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '保险公司id',
                                       `ins_company_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '保险公司名称',
                                       `agreement_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '协议id',
                                       `priority` int NULL DEFAULT NULL COMMENT '优先级',
                                       `default_flag` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '是否默认',
                                       `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                       `create_user` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
                                       `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
                                       `update_user` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '修改人',
                                       `dispatch_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '调度内容',
                                       PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '协议调度管理' ROW_FORMAT = Dynamic;

-- 协议调度历史记录表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_tax_source_new_history`;
CREATE TABLE `ptl_tax_source_new_history`  (
                                               `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                               `ins_company_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '保险公司id',
                                               `ins_company_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '保险公司名称',
                                               `agreement_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '协议id',
                                               `priority` int NULL DEFAULT NULL COMMENT '优先级',
                                               `default_flag` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '是否默认',
                                               `operator_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                               `modifier` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
                                               `dispatch_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '调度内容',
                                               `ptl_tax_source_new_id` bigint NULL DEFAULT NULL COMMENT '协议调度表id',
                                               `batch_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '批次id',
                                               `batch_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '批次类型',
                                               PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '协议调度历史记录表' ROW_FORMAT = Dynamic;

-- 协议调度关联表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_agreement_dispatch_rules_attr_link`;
CREATE TABLE `ptl_agreement_dispatch_rules_attr_link`  (
                                                           `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                                           `rules_id` bigint NOT NULL COMMENT '协议规则id',
                                                           `attr_id` bigint NULL DEFAULT NULL COMMENT '协议属性id',
                                                           `attr_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '协议code',
                                                           `min` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '左操作数',
                                                           `operator` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '运算符\r\n1.等于\r\n2.不等于\r\n3.小于等于\r\n4.大于等于',
                                                           `max` 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;

-- 协议调度关联历史记录表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_agreement_dispatch_rules_attr_link_history`;
CREATE TABLE `ptl_agreement_dispatch_rules_attr_link_history`  (
                                                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                                                   `rules_id` int NULL DEFAULT NULL COMMENT '承包规则id',
                                                                   `attr_id` int NULL DEFAULT NULL COMMENT '属性id',
                                                                   `attr_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性编码',
                                                                   `min` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '左操作数',
                                                                   `operator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作符',
                                                                   `batch` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '右操作数',
                                                                   `operator_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '批次',
                                                                   `modifier` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作类型',
                                                                   `operator_time` datetime NULL DEFAULT NULL COMMENT '操作人',
                                                                   `max` varchar(255) 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;

-- 协议调度规则属性   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_agreement_dispatch_rules_attr`;
CREATE TABLE `ptl_agreement_dispatch_rules_attr`  (
                                                      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                                      `attr_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性名称',
                                                      `attr_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性code',
                                                      `attr_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表单类型',
                                                      `dict_id` bigint NULL DEFAULT NULL COMMENT '关联字典id',
                                                      `attr_group` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组',
                                                      `company_ids` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保险公司id',
                                                      `sort` int NULL DEFAULT NULL COMMENT '排序',
                                                      PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '协议调度规则属性' ROW_FORMAT = DYNAMIC;


INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (1, '车牌号', 'LicenseNo', 'select', NULL, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (2, '座位数', 'PassengerCarSeatingCapacity', 'inputNumber', NULL, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (3, '排量', 'volume', 'inputNumber', NULL, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (4, '车辆大类', 'CarType', 'checkbox', 4, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (5, '使用性质', 'NatureOfUse', 'checkbox', 5, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (6, '交强投保', 'yqInsureFlag', 'radio', 1, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (7, '车损投保', 'cxInsureFlag', 'radio', 1, 'dispatch', 'all', NULL);
INSERT INTO `ptl_agreement_dispatch_rules_attr` VALUES (8, '第三者责任险 ', 'ThirdPartyLiabilityInsurance', 'inputNumber', NULL, 'dispatch', 'all', NULL);

-- 协议调度属性字典表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_agreement_dispatch_rules_dict`;
CREATE TABLE `ptl_agreement_dispatch_rules_dict`  (
                                                      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                                      `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典名称',
                                                      `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '字典code',
                                                      `remark` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
                                                      PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '协议调度属性字典表' ROW_FORMAT = Dynamic;


INSERT INTO `ptl_agreement_dispatch_rules_dict` VALUES (1, '交强投保', 'yqInsureFlag', '是与否');
INSERT INTO `ptl_agreement_dispatch_rules_dict` VALUES (4, '车辆大类', 'carType', '客车和货车');
INSERT INTO `ptl_agreement_dispatch_rules_dict` VALUES (5, '使用性质', 'useDesc', '营业 非营业');

-- 协议调度字典详情   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_agreement_dispatch_rules_dict_labal`;
CREATE TABLE `ptl_agreement_dispatch_rules_dict_labal`  (
                                                            `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                                            `parent_id` bigint NULL DEFAULT 0 COMMENT '父id',
                                                            `dict_id` bigint NULL DEFAULT NULL COMMENT '字典id',
                                                            `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性名称',
                                                            `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '属性code',
                                                            `sort` int NULL DEFAULT NULL COMMENT '排序',
                                                            PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '协议调度字典详情' ROW_FORMAT = Dynamic;


INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (1, 0, 1, '是', '1', 1);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (2, 0, 1, '否', '0', 2);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (7, 0, 4, '客车', 'A0', 1);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (8, 0, 4, '货车', 'A1', 2);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (10, 0, 5, '家庭自用', '05', 1);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (11, 0, 5, '非营业机关', '06', 2);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (12, 0, 5, '非营业企业', '07', 3);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (13, 0, 5, '非营业货运', '08', 4);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (15, 0, 5, '营业出租租赁', '01', 1);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (17, 0, 5, '营业城市公交', '02', 3);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (18, 0, 5, '营业公路客运', '03', 4);
INSERT INTO `ptl_agreement_dispatch_rules_dict_labal` VALUES (19, 0, 5, '营业货运', '04', 5);

-- 协议调度匹配信息记录表   add by hxl  2024-05-29
DROP TABLE IF EXISTS `ptl_tax_source_new_record`;
CREATE TABLE `ptl_tax_source_new_record`  (
                                              `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键/自增',
                                              `orderno` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '主订单编号',
                                              `ptl_tax_source_new_str` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '调度协议对象信息',
                                              `dispatch_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '调度协议规则内容',
                                              `quote_info_msg` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '报价信息内容',
                                              `mate_msg` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '匹配信息',
                                              `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
                                              `create_user` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '创建人',
                                              PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '调度协议默认匹配信息记录表' ROW_FORMAT = Dynamic;

-- 费用审核轨迹表   add by hy  2024-06-3
DROP TABLE IF EXISTS `ins_fee_audit_history`;
CREATE TABLE `ins_fee_audit_history`  (
                                          `id` int(0) NOT NULL COMMENT '主键ID',
                                          `orderno` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号',
                                          `ins_order_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保司订单号 关联ins_area_company表id',
                                          `auditstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '费用审核状态0未审核 1审核通过',
                                          `audittime` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
                                          `audituser` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审核人',
                                          `operator` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人',
                                          `operatorTime` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
                                          PRIMARY KEY (`id`) USING BTREE,
                                          INDEX `ins_order_no`(`ins_order_no`) USING BTREE COMMENT 'ins_order_no'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '费用审核表' ROW_FORMAT = Dynamic;