item-management-1phase/sql/update_sql.sql

/**
    更新的SQL
*/

/**
  日期：2024-03-25
 */
alter table personal_calculate add COLUMN sell_money decimal(16,2) DEFAULT NULL comment '出让金';
alter table personal_calculate add COLUMN sell_money_base decimal(16,2) DEFAULT NULL comment '出让金扣除的基准价格';
alter table personal_calculate add COLUMN sell_money_rate decimal(16,2) DEFAULT NULL comment '出让金扣除比例';

/**
  日期：2024-03-27
  新增五张资产业务相关表
 */
# 资产业务订单表
DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets`
(
    `id`                                bigint(20)                                              NOT NULL COMMENT '主键',
    `name`                              varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目名称',
    `order_id`                          varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '项目编号',
    `financial`                         bit(1)                                                  NOT NULL COMMENT '是否金融',
    `allot_type`                        varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '分单类型',
    `client_manager_id`                 bigint(20)                                              NOT NULL COMMENT '客户经理id(user_id)',
    `principal_id`                      bigint(20)                                              NULL     DEFAULT NULL COMMENT '项目负责人id(user_id)',
    `department_id`                     bigint(20)                                              NOT NULL COMMENT '接单部门id',
    `assets_business_gener`             varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL     DEFAULT NULL COMMENT '资产业务类型',
    `entrust_again`                     bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '客户再次委托',
    `clientele_type`                    varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '客户类型（企业，个人）',
    `clientele_id`                      bigint(20)                                              NULL     DEFAULT NULL COMMENT '客户id',
    `clientele_sub_id`                  bigint(20)                                              NULL     DEFAULT NULL COMMENT '业务来源（下级客户id）',
    `clientele_contact_id`              bigint(20)                                              NOT NULL COMMENT '客户联系人id',
    `security`                          bit(1)                                                  NOT NULL COMMENT '是否证券项目(0:Y 1:N)',
    `significant_assets_reorganization` bit(1)                                                  NOT NULL COMMENT '是否资产重组项目(0:Y 1:N)',
    `state_assets`                      bit(1)                                                  NOT NULL COMMENT '是否国资项目(0:Y 1:N)',
    `foreign_assets_involved`           bit(1)                                                  NOT NULL COMMENT '是否涉及境外资产(0:Y 1:N)',
    `dispense_benefit`                  bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '是否分配产值',
    `bailor`                            varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托人',
    `bailor_address`                    varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL     DEFAULT NULL COMMENT '委托人地址',
    `bailor_contact_name`               varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托联系人姓名',
    `bailor_contact_tel`                varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '委托人联系人电话',
    `terminal_clientele_id`             bigint(20)                                              NULL     DEFAULT NULL COMMENT '终端客户id',
    `terminal_clientele_type`           varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci  NOT NULL COMMENT '终端客户类型（企业，个人）',
    `terminal_clientele_contact_id`     bigint(20)                                              NULL     DEFAULT NULL COMMENT '终端联系人',
    `members`                           json                                                    NULL COMMENT '项目参与人(json格式,存用户表id)',
    `remark`                            text CHARACTER SET utf8 COLLATE utf8_unicode_ci         NULL COMMENT '备注',
    `deleted`                           bit(1)                                                  NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`                           datetime(0)                                             NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`                          timestamp(6)                                            NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产业务订单表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估方法表
DROP TABLE IF EXISTS `assets_evaluation_method`;
CREATE TABLE `assets_evaluation_method`
(
    `id`          bigint(20)                                                  NOT NULL COMMENT '资产评估方法id',
    `method_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '资产评估方法名',
    `deleted`     bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`     datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`    timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估方法表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估对象表
DROP TABLE IF EXISTS `assets_evaluation_target`;
CREATE TABLE `assets_evaluation_target`
(
    `id`                                bigint(20)                                                  NOT NULL COMMENT '资产评估对象id',
    `evaluation_purpose_id`             bigint(20)                                                  NOT NULL COMMENT '资产评估目的id',
    `evaluation_type_id`                bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型一级id',
    `evaluation_type_sec_id`            bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型二级id',
    `the_holder`                        varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '产权持有人',
    `assets_value_id`                   bigint(20)                                                  NOT NULL COMMENT '资产价值类型id',
    `principal_id`                      bigint(20)                                                  NOT NULL COMMENT '项目负责人id (user_id)',
    `site_ids`                          json                                                        NOT NULL COMMENT '实勘人id (json 可多个)',
    `valuation_basis_date`              date                                                        NOT NULL COMMENT '估价基准日',
    `evaluation_method_id`              bigint(20)                                                  NOT NULL COMMENT '评估方法id',
    `choice_evaluation_method_id`       bigint(20)                                                  NOT NULL COMMENT '最终选择的评估方法id',
    `total_asset_carrying_amount`       decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '总资产账面值(万)',
    `total_liabilities_carrying_amount` decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '总负债账面值(万)',
    `carrying_amount_of_net_assets`     decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '净资产账面值(万)',
    `estimated_value`                   decimal(16, 2)                                              NULL     DEFAULT NULL COMMENT '评估结论对应的评估值(万)',
    `remark`                            text CHARACTER SET utf8 COLLATE utf8_unicode_ci             NULL COMMENT '备注',
    `file_path`                         varchar(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '资料路由信息',
    `deleted`                           bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`                           datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`                          timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估对象表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 资产评估对象类型表
DROP TABLE IF EXISTS `assets_evaluation_target_type`;
CREATE TABLE `assets_evaluation_target_type`
(
    `id`        bigint(20)                                                  NOT NULL COMMENT '资产评估对象类型id',
    `parent_id` bigint(20)                                                  NOT NULL COMMENT '父级id(0代表没有父级)',
    `type_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '类型名',
    `deleted`   bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`   datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`  timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '资产评估对象类型表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

# 价值类型表
DROP TABLE IF EXISTS `assets_value_type`;
CREATE TABLE `assets_value_type`
(
    `id`        bigint(20)                                                  NOT NULL COMMENT '价值类型id',
    `type_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT '价值类型名',
    `deleted`   bit(1)                                                      NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`   datetime(0)                                                 NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`  timestamp(6)                                                NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = latin1
  COLLATE = latin1_swedish_ci COMMENT = '价值类型表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


/**
  日期：2024-03-28
 */

# 删除 assets 表 financial 字段
ALTER TABLE assets DROP financial;

# 部分字段字符集问题修正
alter table assets_evaluation_method change method_name method_name varchar(255) character set utf8 collate utf8_unicode_ci not null default '';
alter table assets_evaluation_target_type change type_name type_name varchar(255) character set utf8 collate utf8_unicode_ci not null default '';
alter table assets_evaluation_target change the_holder the_holder varchar(255) character set utf8 collate utf8_unicode_ci not null default '';
alter table assets_evaluation_target change file_path file_path varchar(255) character set utf8 collate utf8_unicode_ci not null default '';
alter table assets_value_type change type_name type_name varchar(255) character set utf8 collate utf8_unicode_ci not null default '';

# 追加自增
alter table assets modify id int auto_increment;
alter table assets_evaluation_method modify id int auto_increment;
alter table assets_evaluation_target_type modify id int auto_increment;
alter table assets_evaluation_target modify id int auto_increment;
alter table assets_value_type modify id int auto_increment;

# 资产产品表
DROP TABLE IF EXISTS `assets_production`;
CREATE TABLE `assets_production`
(
    `id`                     bigint(20)                                                    NOT NULL AUTO_INCREMENT COMMENT '资产产品主键id',
    `business_id`            bigint(20)                                                    NOT NULL COMMENT '业务id(资产id)',
    `production_no`          varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '产品号',
    `production_type`        varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci        NOT NULL COMMENT '产品类型(意见书，报告)',
    `signatory`              json                                                          NULL COMMENT '签字人（产品类型为报告时，签字人必填）',
    `client_name`            varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci       NULL     DEFAULT NULL COMMENT '委托方名称',
    `client_tel`             varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci        NULL     DEFAULT NULL COMMENT '委托方电话',
    `valuation_basis_date`   date                                                          NOT NULL COMMENT '评估基准日',
    `evaluate_acreage`       decimal(16, 2)                                                NULL     DEFAULT NULL COMMENT '评估面积',
    `evaluate_amount`        decimal(16, 2)                                                NULL     DEFAULT NULL COMMENT '评估总价',
    `evaluate_price`         decimal(16, 2)                                                NULL     DEFAULT NULL COMMENT '评估单价',
    `owner`                  varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci       NULL     DEFAULT NULL COMMENT '产权人',
    `print_count`            int(11)                                                       NOT NULL COMMENT '产品打印数量',
    `create_production_date` date                                                          NOT NULL COMMENT '产品出具日期',
    `comment`                text CHARACTER SET utf8 COLLATE utf8_unicode_ci               NULL COMMENT '特殊情况说明',
    `repertory_state`        bit(1)                                                        NULL     DEFAULT NULL COMMENT '库存状态（0：已入库  1：已出库）',
    `repertory_in_time`      datetime(0)                                                   NULL     DEFAULT NULL COMMENT '入库时间',
    `repertory_out_time`     datetime(0)                                                   NULL     DEFAULT NULL COMMENT '出库时间',
    `file_path`              varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci       NULL     DEFAULT NULL COMMENT '电子文档路经',
    `save_file_date`         datetime(0)                                                   NULL     DEFAULT NULL COMMENT '归档日期',
    `delivery`               bit(1)                                                        NOT NULL DEFAULT b'0' COMMENT '送达状态',
    `deleted`                bit(1)                                                        NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
    `created`                datetime(0)                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
    `modified`               timestamp(6)                                                  NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '资产产品表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

/**
  日期：2024-03-29
 */
 # 新增字段
ALTER TABLE `assets_evaluation_target` ADD assets_id BIGINT(20)  NOT NULL COMMENT '资产业务id';

/**
  日期：2024-04-01
 */
 # 删除字段financial
ALTER TABLE `assets` DROP COLUMN financial;

/**
  日期：2024-04-03
 */
 # 新增字段qr_code
ALTER TABLE `assets_production` ADD qr_code varchar(255) NULL COMMENT '二维码路径';

# 新增字段check_value
ALTER TABLE `assets_evaluation_target` ADD check_value decimal(16,4) NULL COMMENT '审核价格';

/**
  日期：2024-04-07
 */
 # 修改产品出库申请表 production_id 可空  修改个贷订单表 terminal_clientele_type 可空
ALTER TABLE production_out_warehouse MODIFY COLUMN production_id bigint(20) NULL;
ALTER TABLE personal MODIFY COLUMN terminal_clientele_type varchar(32) NULL;
ALTER TABLE production_out_warehouse add COLUMN order_id bigint(32) DEFAULT NULL comment '订单id（个贷）';
ALTER TABLE production_out_warehouse MODIFY COLUMN production_id bigint(20) comment '产品id（大中型）';
ALTER TABLE production_out_warehouse add COLUMN order_fund_id bigint(32) DEFAULT NULL comment '订单应收款id（个贷）';
ALTER TABLE production_out_warehouse MODIFY COLUMN production_fund_id bigint(20) comment '产品应收款id(大中型)';

/**
  日期：2024-04-08
 */
ALTER TABLE assets_evaluation_target MODIFY COLUMN file_path json NOT NULL comment '资料路由信息(json)';

/**
  日期：2024-04-10
 */
ALTER TABLE personal_production DROP COLUMN if_save_file;
ALTER TABLE personal_production DROP COLUMN save_file_check;
ALTER TABLE personal_production DROP COLUMN save_file_date;
alter table personal add COLUMN if_save_file bit(1) NOT NULL DEFAULT 0 comment '是否归档';
alter table personal add COLUMN save_file_check bit(1) DEFAULT NULL comment '归档审核';
alter table personal add COLUMN save_file_date datetime(0) DEFAULT NULL comment '归档日期';

/**
  日期：2024-04-10
  修改人:苟耕铨
 */
ALTER TABLE assets_production MODIFY COLUMN create_production_date date NULL comment '产品出具日期';

/**
  日期：2024-04-11
  修改人:苟耕铨
 */
ALTER TABLE assets_production MODIFY COLUMN production_no VARCHAR(128) NULL comment '产品出具日期';

/**
  日期：2024-04-12
  修改人:苟耕铨
 */
 # 修改注释
ALTER TABLE assets_production MODIFY COLUMN production_no VARCHAR(128) NULL comment '产品号';

/**
  日期：2024-04-15
  修改人:吴长林
 */
alter table personal_target add COLUMN validate_code varchar(255)  comment '验证二维码路经';
alter table major_production add COLUMN validate_code varchar(255)  comment '验证二维码路经';

/**
  日期：2024-04-15
  修改人:苟耕铨
 */
# 资产评估对象表新增字段
ALTER TABLE `assets_evaluation_target` ADD production_no VARCHAR(128) NULL COMMENT '产品号';

/**
  日期：2024-04-17
  修改人:苟耕铨
 */
# 资产产品表file_path字段修改为json类型,新增字段assets_name
ALTER TABLE assets_production MODIFY COLUMN file_path json NULL comment '资料路由信息(json)';
ALTER TABLE assets_production ADD COLUMN assets_name VARCHAR(255) NULL comment '项目名称';

/**
  日期：2024-04-29
  修改人:苟耕铨
 */
 # 资产评估对象表修改以及新增字段
ALTER TABLE assets_evaluation_target CHANGE COLUMN production_no statement_no VARCHAR(128) NULL COMMENT '意见书产品号';
ALTER TABLE assets_evaluation_target ADD COLUMN report_no VARCHAR(128) NULL COMMENT '报告产品号';

/**
  日期：2024-04-30
  修改人:吴长林
 */
CREATE TABLE `work_record_transfer` (
                                        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
                                        `record_id` bigint(20) NOT NULL COMMENT '任务记录id',
                                        `from_id` bigint(20) NOT NULL COMMENT '原处理人id',
                                        `to_id` bigint(20) NOT NULL COMMENT '转交到处理人id',
                                        `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                        `modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '修改时间',
                                        `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '逻辑删除标识:1:删除 0:未删除',
                                        PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='任务转交表';