productivity-platform/sql/update_sql.sql

/**
  更新的SQL
 */

/**
  日期：2024-10-18
  修改人:苟耕铨
  未更新到test-env
 */
DROP TABLE IF EXISTS `assets_calculate`;
CREATE TABLE `assets_calculate` (
                                    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '资产项目测算表id',
                                    `assets_id` bigint(20) NOT NULL COMMENT '资产项目id',
                                    `calculate_name` varchar(255) DEFAULT NULL COMMENT '测算名',
                                    `valuation_basis_date` date DEFAULT NULL COMMENT '评估基准日',
                                    `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建项目user_id',
                                    `calculate_info` json DEFAULT NULL COMMENT '测算信息',
                                    `calculate_file_name` varchar(255) DEFAULT NULL COMMENT '测算表文件名',
                                    `calculate_file_url` varchar(500) DEFAULT NULL COMMENT '测算表文件存储位置',
                                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                                    `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                                    PRIMARY KEY (`id`),
                                    KEY `assets_id` (`assets_id`,`create_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资产项目测算信息表';

DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets` (
                          `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '资产项目id',
                          `project_name` varchar(500) DEFAULT NULL COMMENT '项目名称',
                          `project_type_id` bigint(20) DEFAULT NULL COMMENT '项目类型id(字典表)',
                          `create_user_id` bigint(20) DEFAULT NULL COMMENT '创建项目user_id',
                          `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                          `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                          `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                          PRIMARY KEY (`id`),
                          KEY `project_type_id` (`project_type_id`,`create_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `user_post`;
CREATE TABLE `user_post` (
                             `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户岗位-部门信息id',
                             `user_oa_id` bigint(20) unsigned DEFAULT NULL COMMENT '用户OA id',
                             `post_name` varchar(100) DEFAULT NULL COMMENT '岗位名称',
                             `department_name` varchar(100) DEFAULT NULL COMMENT '部门名称',
                             `role` varchar(50) DEFAULT NULL COMMENT '用户角色',
                             `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                             `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                             `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                             PRIMARY KEY (`id`),
                             KEY `user_oa_id` (`user_oa_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/**
  日期：2024-10-18
  修改人:苟耕铨
  未更新到test-env
 */
ALTER TABLE user_post MODIFY COLUMN post_name JSON NULL COMMENT '岗位名称(json)';
ALTER TABLE user_post MODIFY COLUMN department_name JSON NULL COMMENT '部门名称(json)';
ALTER TABLE user_post CHANGE COLUMN user_oa_id user_id bigint(20) unsigned DEFAULT NULL COMMENT 'user_id';



/**
  日期：2024-10-22
  修改人:吴长林
  未更新到test-env
 */
DROP TABLE IF EXISTS `document_production`;
CREATE TABLE `document_production` (
                                       `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                       `business_type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '业务类型（ASSETS、HOUSE、LAND）',
                                       `business_id` bigint NOT NULL COMMENT '业务id',
                                       `doc_type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文档类型（各类报告、复评函、意见书、各类测算表）',
                                       `doc_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文档编号',
                                       `doc_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文档名称',
                                       `doc_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文档路径',
                                       `doc_version` int NOT NULL DEFAULT '1' COMMENT '文档版本',
                                       `is_active` bit(1) NOT NULL DEFAULT b'1' COMMENT '当前使用',
                                       `create_user_id` bigint DEFAULT NULL COMMENT '文档生成人id',
                                       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '文档生成时间',
                                       `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                                       `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                                       PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文档产品';

DROP TABLE IF EXISTS `certificate_fixed_assets`;
CREATE TABLE `certificate_fixed_assets` (
                                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                                   `business_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '业务类型（ASSETS、HOUSE、LAND）',
                                                   `business_id` bigint NOT NULL COMMENT '业务id',
                                                   `certificate_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '不动产权证书号',
                                                   `own_ship_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '权利人',
                                                   `own_ship_info` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '共有情况',
                                                   `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '房屋坐落',
                                                   `unit_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '不动产单元号',
                                                   `own_ship_type` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '权利类型',
                                                   `own_ship_nature` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
                                                   `use_to` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '用途',
                                                   `acreage_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '面积描述',
                                                   `expire_date_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '使用期限描述',
                                                   `own_ship_other_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '权利其他状况',
                                                   `attach_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '附记',
                                                   `create_user_id` bigint NULL DEFAULT NULL COMMENT '录入人id',
                                                   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                                                   `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '权证-不动产权证书' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `certificate_house_own`;
CREATE TABLE `certificate_house_own` (
                                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                                   `business_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '业务类型（ASSETS、HOUSE、LAND）',
                                                   `business_id` bigint NOT NULL COMMENT '业务id',
                                                   `is_new` bit(1) NOT NULL COMMENT '是否为新证',
                                                   `certificate_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '房屋所有权证号',
                                                   `own_ship_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '房屋所有权人',
                                                   `own_ship_info` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '共有情况',
                                                   `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '房屋坐落',
                                                   `register_date` date NULL DEFAULT NULL COMMENT '登记日期',
                                                   `house_nature` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '房屋性质',
                                                   `use_to` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '用途（新：规划用途，老：设计用途）',
                                                   `all_floor` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '总层数',
                                                   `acreage` decimal(10, 2) NULL DEFAULT NULL COMMENT '建筑面积',
                                                   `inner_acreage` decimal(10, 2) NULL DEFAULT NULL COMMENT '套内建筑面积',
                                                   `attach_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '附记',
                                                   `area_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '丘地号',
                                                   `own_ship_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '产别',
                                                   `building_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '幢号',
                                                   `house_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '房号',
                                                   `structure` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '结构',
                                                   `at_floor` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '所在楼层',
                                                   `create_user_id` bigint NULL DEFAULT NULL COMMENT '录入人id',
                                                   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                                                   `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '权证-房屋所有权证' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `certificate_land_use`;
CREATE TABLE `certificate_land_use` (
                                                   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
                                                   `business_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '业务类型（ASSETS、HOUSE、LAND）',
                                                   `business_id` bigint NOT NULL COMMENT '业务id',
                                                   `is_new` bit(1) NOT NULL COMMENT '是否为新证',
                                                   `certificate_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '国有土地使用证号',
                                                   `land_use_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '土地使用权人/土地使用者',
                                                   `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '座落',
                                                   `land_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '地号',
                                                   `image_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '图号',
                                                   `use_to` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '地类用途',
                                                   `take_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '取得价格',
                                                   `use_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '使用权类型',
                                                   `expire_date` date NULL DEFAULT NULL COMMENT '终止日期',
                                                   `acreage` decimal(10, 2) NULL DEFAULT NULL COMMENT '使用权面积',
                                                   `outer_acreage` decimal(10, 2) NULL DEFAULT NULL COMMENT '其中分摊面积',
                                                   `attach_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '记事',
                                                   `land_level` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '土地等级',
                                                   `create_user_id` bigint NULL DEFAULT NULL COMMENT '录入人id',
                                                   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
                                                   `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态',
                                                   PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '权证-国有土地使用证' ROW_FORMAT = Dynamic;

/**
  日期：2024-10-23
  修改人:苟耕铨
  未更新到test-env
 */
 # 删除资产测算信息表中关于文档信息的字段
ALTER TABLE assets_calculate DROP COLUMN calculate_file_name;
ALTER TABLE assets_calculate DROP COLUMN calculate_file_url;
# 模板信息表修改
ALTER TABLE tmpl_asset_calculate ADD COLUMN has_section BIT(1) NOT NULL DEFAULT 0 COMMENT '是否有段落模板';
ALTER TABLE tmpl_asset_report ADD COLUMN has_section BIT(1) NOT NULL DEFAULT 0 COMMENT '是否有段落模板';

/**
  日期：2024-11-06
  修改人:苟耕铨
  未更新到test-env
 */
ALTER TABLE tmpl_asset_calculate_section ADD COLUMN section_name VARCHAR(50) NOT NULL COMMENT '测算表段落模板名称';
ALTER TABLE tmpl_asset_calculate_section ADD COLUMN tmpl_code VARCHAR(50) NOT NULL COMMENT '模板code';
ALTER TABLE tmpl_asset_calculate ADD COLUMN tmpl_code VARCHAR(50) NOT NULL COMMENT '模板code';