Skip to content

8
8timerapiv200
Switch branch/tag
Find file
Normal viewHistoryPermalink
insure_policy.sql 14.2 KB
Newer Older
运营后台--
7ef18ffc
 
翁国栋 committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 
/*
 Navicat Premium Data Transfer

 Source Server         : 120.78.162.177
 Source Server Type    : MySQL
 Source Server Version : 50734
 Source Host           : 120.78.162.177:3306
 Source Schema         : timer_test

 Target Server Type    : MySQL
 Target Server Version : 50734
 File Encoding         : 65001

 Date: 09/04/2022 18:43:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for insure_applicant
-- ----------------------------
DROP TABLE IF EXISTS `insure_applicant`;
CREATE TABLE `insure_applicant`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `applicant_e_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业/网点名称',
  `applicant_e_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业/网点地址',
  `applicant_employee_list` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员清单',
  `applicant_e_contacts` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业授权操作人姓名',
  `applicant_e_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作人的联系电话',
  `applicant_e_no_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业证件类型:3646、统一社会信用代码 3924、组织机构代码',
  `applicant_e_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业证件号码',
  `applicant_e_mail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业邮箱',
  `applicant_province_city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '省市区',
  `applicant_invoice_receiving_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票收件地址',
  `applicant_invoice_billing_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票开票电话',
  `applicant_business_bank_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业开户行名称',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `applicant_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '投保类型：1个人 2 企业',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报价请求流水号',
  `currency` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报价号',
  `applicant_invoiced_amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '发票开票金额',
  `org_code` int(11) NULL DEFAULT NULL,
  `applicant_corporate_bank_account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '银行账号',
  `applicanteaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantecontacts` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantemail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicanteno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicanteno_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `applicantephone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '投保人' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_log
-- ----------------------------
DROP TABLE IF EXISTS `insure_log`;
CREATE TABLE `insure_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_path` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求路径',
  `request_data` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '请求参数',
  `request_param` varchar(5000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求参数',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '请求时间',
  `return_time` datetime(0) NULL DEFAULT NULL COMMENT '返回时间',
  `return_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '返回编码',
  `return_body` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '返回参数',
  `return_msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '返回编码文本',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求流水号',
  `type` int(11) NULL DEFAULT NULL COMMENT '日志类型:1投保 2增员 3替换 4投保上传文件 5增员上传文件 6替换上传文件 7增员回调 8替换回调 9校验',
  `request_type` tinyint(11) NULL DEFAULT NULL COMMENT '请求方式1 post 2get 3put',
  `policy_id` int(11) NULL DEFAULT NULL COMMENT '保单id',
  `file_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '附件地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 782 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_policy
-- ----------------------------
DROP TABLE IF EXISTS `insure_policy`;
CREATE TABLE `insure_policy`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scheme_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '方案名称(必选)',
  `policy_date_start` datetime(0) NULL DEFAULT NULL COMMENT '保单生效日',
  `policy_date_end` datetime(0) NULL DEFAULT NULL COMMENT '保单终止日',
  `product_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品代码',
  `plan_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计划代码',
  `partner_pid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分销商pid',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `type` int(11) NULL DEFAULT NULL COMMENT '保单类型：1、年单 2、月单',
  `currency` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '5-人民币，其他币种请参见全局数据字典',
  `total_premium` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '总保费\r\n\r\n',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '1-正常 2-等待校验 3-失效',
  `policy_file` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电子保单',
  `kit_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单kit的地址',
  `insure_applicant_id` int(11) NULL DEFAULT NULL COMMENT '投保人id',
  `org_code` int(11) NULL DEFAULT NULL COMMENT '企业id',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '投保时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '最新操作时间',
  `product_id` int(11) NULL DEFAULT NULL COMMENT '保险产品id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_polivy_no`(`policy_no`) USING BTREE COMMENT '保单唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 104 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '保单信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_product
-- ----------------------------
DROP TABLE IF EXISTS `insure_product`;
CREATE TABLE `insure_product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品名称',
  `plan_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计划代码',
  `product_code_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品代码',
  `type` int(11) NULL DEFAULT NULL COMMENT '类型:1年单 2月单',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `is_del` int(11) NULL DEFAULT 0 COMMENT '是否删除 0否 1是',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '保险产品' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_province
-- ----------------------------
DROP TABLE IF EXISTS `insure_province`;
CREATE TABLE `insure_province`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '值',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3066 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '省市区' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for insure_user
-- ----------------------------
DROP TABLE IF EXISTS `insure_user`;
CREATE TABLE `insure_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人请求流水号',
  `price` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单价',
  `insured_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `batch_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人批次号',
  `premium` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单批次保费',
  `insured_mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '被保险人联系电话',
  `insured_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '证件号码',
  `insured_e_contact` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系人姓名',
  `benefit_basic_plan` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '基础计划\r\n30万雇主/3万医疗	36968,50万雇主/5万医疗	36969,80万雇主/10万医疗	36970,80万雇主/20万医疗	36971,100万雇主/10万医疗 36972\r\n',
  `benefit_occupation_category` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职业类别\r\nA类	63119\r\nB类	63120\r\nC类	63121\r\n',
  `benefit_EL_employee_number_t` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '参保人数',
  `Tricycle_frame_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二、三轮车车架号(必选)',
  `policy_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '保单号',
  `status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '参保状态：1参保中 2已失效',
  `type` tinyint(4) NULL DEFAULT NULL COMMENT '被保人类型：1个人 2企业',
  `org_code` int(11) NULL DEFAULT NULL COMMENT '企业id',
  `user_id` int(11) NULL DEFAULT NULL COMMENT '个人id',
  `applicant_employee_list` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '人员清单',
  `policy_date_start` datetime(0) NULL DEFAULT NULL COMMENT '生效日期',
  `policy_date_end` datetime(0) NULL DEFAULT NULL COMMENT '失效日期',
  `insure_status` int(11) NULL DEFAULT NULL COMMENT '投保状态：1成功 2失效 3待更新 4已被替换',
  `policy_id` int(11) NULL DEFAULT NULL COMMENT '保单id',
  `replace_trans_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '替换流水号，使用trans_id，RP_开头',
  `apply_type` int(11) NULL DEFAULT NULL COMMENT '申请类型：1新增 2投保 3批增',
  `benefit_el_employee_numbert` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `insuredecontact` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 715 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '被保人' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `timer_test`.`admin_menu_template`(`id`, `parentId`, `menu_code`, `menu_name`, `sort_number`, `path`, `enable`, `distribution`) VALUES (23, 0, 'policy', '保单管理', 23, '/policy', 1, 1);
INSERT INTO `timer_test`.`qyzx_admin_menu`(`id`, `menu_code`, `menu_name`, `sort_number`, `org_code`, `addtime`) VALUES (364, 'policy', '保单管理', 17, 1, 1648712195019);
导入被保人员bug修复
92e4c2e3
 
翁国栋 committed
170 171 172 

ALTER TABLE `timer_test`.`insure_user`
ADD COLUMN `reason` varchar(255) NULL COMMENT '批改原因' AFTER `apply_type`;
8小时后台--
34c7bbf1
 
翁国栋 committed
173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for insure_pay
-- ----------------------------
DROP TABLE IF EXISTS `insure_pay`;
CREATE TABLE `insure_pay`  (
   `id` int(11) NOT NULL COMMENT 'id',
   `pay_status` int(11) NULL DEFAULT NULL COMMENT '1已支付 2未支付 3已超时',
   `amount` double NULL DEFAULT NULL COMMENT '支付金额',
   `pay_time` datetime(0) NULL DEFAULT NULL COMMENT '支付时间',
   `end_time` datetime(0) NULL DEFAULT NULL COMMENT '超时时间',
   `pay_serial_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付流水号',
   `pay_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付类型',
   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '支付流水表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

ALTER TABLE `insure_product`
    ADD COLUMN `pay_type` int(11) NULL DEFAULT NULL COMMENT '1 在线支付 2预付款 3线下支付' AFTER `is_del`;

ALTER TABLE `insure_policy`
    ADD COLUMN `policy_pay_type` int(11) NOT NULL DEFAULT 1 COMMENT '1在线支付 2预付款 3线下支付' AFTER `create_time`,
ADD COLUMN `pay_id` int(11) NULL DEFAULT NULL COMMENT 'insure_pay表的id' AFTER `policy_pay_type`;
8小时后台--
fb987a55
 
翁国栋 committed
200