/** 更新的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)'; 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 '归档日期';