/** 更新的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'; DROP TABLE IF EXISTS `house_guaranty_process`; CREATE TABLE `house_guaranty_process-platform` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id', `home` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'docHome', `targets_html` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '房地产抵押过程表' ROW_FORMAT = Dynamic;