java-insagent-pc/sql/Online.sql

# 子订单
alter table ins_area_company
    add leader_id varchar(64) null comment '维护人工号';
alter table ins_area_company
    add leader_name varchar(64) null comment '维护人';
--  2024-10-15 BY dongxin statr
ALTER TABLE `agent`.`ins_fee_telemarketing`
ADD COLUMN `jq_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '交强优惠比例' AFTER `create_time`,
ADD COLUMN `sy_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '商业优惠比例' AFTER `jq_total_proportion`,
ADD COLUMN `no_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '非车优惠比例' AFTER `sy_total_proportion`,
ADD COLUMN `first_jq_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '初始交强优惠比例' AFTER `no_total_proportion`,
ADD COLUMN `first_sy_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '初始商业优惠比例' AFTER `first_jq_total_proportion`,
ADD COLUMN `first_no_total_proportion` decimal(10, 2) NULL DEFAULT NULL COMMENT '初始非车优惠比例' AFTER `first_sy_total_proportion`;
--  2024-10-15 BY dongxin end


--  2024-10-16 BY helixiao
CREATE TABLE `sys_premium_details` (
    `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `orderno` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单号',
    `company_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '子订单id',
    `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户id',
    `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
    `issuer_id` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '代理人 id',
    `issuer_name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '代理人name',
    `jqpremium` decimal(10,5) unsigned DEFAULT NULL COMMENT '交强险费用',
    `sypremium` decimal(10,5) unsigned DEFAULT NULL COMMENT '商业险费用',
    `jypremium` decimal(10,5) unsigned DEFAULT NULL COMMENT '驾意险费用',
    `sumpremium` decimal(10,5) unsigned DEFAULT NULL COMMENT '总保费',
    `proportional` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '抽成比例（总保费抽成）',
    `extract_fee` decimal(10,5) unsigned DEFAULT NULL COMMENT '抽成费用',
    `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态(0:未审核 1：审核通过)',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `type` tinyint DEFAULT NULL COMMENT '类型：1前线人员 2后线人员',
    `industry_sector` tinyint DEFAULT NULL COMMENT '行业领域 1：汽车-车险',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='合伙人、工作室管理员保费抽成明细表';


CREATE TABLE `sys_partner_account` (
    `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户id',
    `sum_Premium` decimal(10,5) unsigned DEFAULT NULL COMMENT '总保费',
    `extract_fee` decimal(10,5) unsigned DEFAULT NULL COMMENT '抽成费用',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `history_fee` decimal(10,5) DEFAULT NULL COMMENT '已提现费用',
    `cash_fee` decimal(10,5) DEFAULT NULL COMMENT '可提现抽成费用',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='合伙人、工作室管理人员抽层账户表';


CREATE TABLE `sys_partner_config` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `logo` mediumblob COMMENT '图标',
    `image` mediumblob COMMENT '背景',
    `level` tinyint DEFAULT NULL COMMENT '等级',
    `del_flag` tinyint NOT NULL DEFAULT '1' COMMENT '状态（ 0：删除 1：正常）',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='合伙人APP等级背景配置表';

CREATE TABLE `sys_partner_time` (
    `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户id',
    `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户name',
    `old_proportional` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '旧比例',
    `new_proportional` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '新比例',
    `create_time` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '月份',
    `type` tinyint DEFAULT NULL COMMENT '类型1合伙人 2工作室管理员',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='抽成比例修改表';


--  2024-10-18 BY WeiRuiJie
ALTER TABLE `sys_qy_wechat` ADD COLUMN `support_staff_url` varchar(500) NULL COMMENT '客服URL';

ALTER TABLE `agent`.`sys_user`
    ADD COLUMN `dept_type` varchar(30) NULL COMMENT '机构类型（股份，直营，加盟）',
    ADD COLUMN `referrer_id` varchar(30) NULL COMMENT '推荐人id';

-- 2024.11.1 --
ALTER TABLE `agent`.`sys_user` ADD COLUMN `identity_time` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证有效期' AFTER `identity`;



-- 2024.11.4  helixiao --

ALTER TABLE `agent`.`sys_premium_details` ADD COLUMN `licenseno` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '车牌号';
ALTER TABLE `agent`.`sys_premium_details` ADD COLUMN `source` tinyint DEFAULT NULL COMMENT '费用来源(1，2,3,4,5级)';

-- 2024.11.11 dongxin --
CREATE TABLE `sys_user_agent_track`  (
                                         `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                         `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户id',
                                         `status` tinyint NULL DEFAULT NULL COMMENT '状态  0.注册 1.认证成功 2.认证失败 3.审核成功 4.审核失败',
                                         `approve_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审核人Id',
                                         `approve_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '审核人',
                                         `approve_mess` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci 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 '创建时间',
                                         PRIMARY KEY (`id`) USING BTREE,
                                         INDEX `idx_sys_user_track_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 571 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '个代用户认证审核轨迹表' ROW_FORMAT = DYNAMIC;





-- 2024-11-15 协议修改时间格式--
ALTER TABLE `agent`.`ptl_agreement`
MODIFY COLUMN `start_date` datetime NOT NULL COMMENT '协议起期' AFTER `insurance_company_id`,
MODIFY COLUMN `end_date` datetime NOT NULL COMMENT '协议止期' AFTER `start_date`;



-- 2024-11-16 添加提现与银联api表 --
CREATE TABLE `pay_payment_notice` (
                                      `id` bigint NOT NULL,
                                      `type` varchar(255) DEFAULT NULL COMMENT '通知类型',
                                      `mch_order_no` varchar(255) DEFAULT NULL COMMENT '商户订单号',
                                      `order_no` varchar(255) DEFAULT NULL COMMENT '天天企赋业务订单号',
                                      `amount` decimal(15,2) DEFAULT NULL COMMENT '打款金额',
                                      `fee_amount` decimal(15,2) DEFAULT NULL COMMENT '服务费',
                                      `status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态',
                                      `message` varchar(255) DEFAULT NULL COMMENT '状态描述',
                                      `pay_time` datetime DEFAULT NULL COMMENT '打款时间',
                                      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `pay_sign_callback` (
                                     `id` bigint NOT NULL,
                                     `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '默认值：PRESIGN',
                                     `org_req_id` bigint DEFAULT NULL COMMENT '最后一次请求',
                                     `name` varchar(255) DEFAULT NULL,
                                     `mobile` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                                     `id_card_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证号',
                                     `bank_card_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '银行卡号',
                                     `bank_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '银行名称',
                                     `auth_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '认证状态:0-未实名1-已实名',
                                     `sign_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '签约状态:0-未签约1-已签约',
                                     `bind_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT ' 绑卡状态:0-未绑定1-已绑定',
                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户签约回调';
CREATE TABLE `pay_ttqf_confirm` (
                                    `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
                                    `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户ID',
                                    `biz_msg` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '返回信息',
                                    `biz_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务响应码',
                                    `batch_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '天天企赋业务批次号',
                                    `mch_batch_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '商户批次号',
                                    `total_mount` decimal(10,2) DEFAULT NULL COMMENT '计划打款总金额',
                                    `total_fee_amount` decimal(10,2) DEFAULT NULL COMMENT '总服务费服务费',
                                    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `pay_ttqf_log` (
                                `id` varchar(255) NOT NULL,
                                `user_id` varchar(255) DEFAULT NULL COMMENT '用户ID',
                                `sed_status` tinyint NOT NULL DEFAULT '0' COMMENT '是否发送成功0-失败',
                                `api_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '请求url',
                                `request_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '请求数据',
                                `response_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '响应数据',
                                `mch_batch_no` varchar(255) DEFAULT NULL COMMENT '商户批次号',
                                `mch_order_no` varchar(255) DEFAULT NULL COMMENT '商户订单号',
                                `amount` decimal(10,2) DEFAULT NULL COMMENT '打款金额',
                                `fee_amount` decimal(10,2) DEFAULT NULL COMMENT '服务费',
                                `batch_no` varchar(255) DEFAULT NULL COMMENT '天天企赋业务批次号',
                                `order_no` varchar(255) DEFAULT NULL COMMENT '天天企赋业务订单号',
                                `payment_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '打款方式',
                                `biz_msg` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '返回信息',
                                `biz_code` varchar(255) DEFAULT NULL,
                                `confirm` tinyint(1) NOT NULL DEFAULT '0' COMMENT '确认打款1-已确认',
                                `create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者',
                                `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                `sys_amount_auditing_id` varchar(255) DEFAULT NULL,
                                PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pay_user_sign` (
                                 `id` bigint NOT NULL,
                                 `user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户ID',
                                 `identity` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户身份证号',
                                 `sign` tinyint NOT NULL DEFAULT '0' COMMENT '签约状态:0-未签约1-已签约',
                                 `auth_status` tinyint DEFAULT '0' COMMENT '认证状态:0-未实名1-已实名',
                                 `online_payment` tinyint NOT NULL DEFAULT '0' COMMENT '线上支付1-是',
                                 `create_time` datetime DEFAULT NULL,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `push_lg_data` (
                                `id` bigint NOT NULL,
                                `application_time` datetime DEFAULT NULL,
                                `business_umber` varchar(255) DEFAULT NULL,
                                `payee` varchar(255) DEFAULT NULL,
                                `payee_id` varchar(36) DEFAULT NULL,
                                `receiving_account` varchar(255) DEFAULT NULL,
                                `extract_money` decimal(10,2) DEFAULT NULL,
                                `service_charge` decimal(10,2) DEFAULT NULL,
                                `extract_status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                                `push_status` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
                                `msg` longtext,
                                `send_number` int DEFAULT NULL,
                                PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='推送提现 记录到零工系统';

ALTER TABLE `agent`.`sys_amount_auditing` ADD COLUMN `approval` tinyint(1) NULL DEFAULT 0 COMMENT '是否自动审批1-是' AFTER `account_id`;

--  --