/** 更新的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'; /** 日期：2024-11-07 修改人:吴长林 */ 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; /** 日期：2024-11-08 修改人:吴长林 */ ALTER TABLE house_guaranty_process ADD COLUMN doc_no VARCHAR(128) NOT NULL COMMENT '文档编号'; /** 日期：2024-11-11 修改人:苟耕铨 未更新到test-env */ ALTER TABLE tmpl_asset_calculate_section CHANGE COLUMN template_report_id template_calculate_id BIGINT(20) NOT NULL COMMENT '测算表模板id'; /** 日期：2024-11-11 修改人:吴长林 未更新到test-env */ ALTER TABLE house_guaranty_process DROP COLUMN doc_no; ALTER TABLE document_production ADD COLUMN business_cate VARCHAR(128) COMMENT '业务子类'; /** 日期：2024-11-12 修改人:苟耕铨 未更新到test-env */ RENAME TABLE assets TO assets_project; ALTER TABLE assets_project ADD COLUMN principal VARCHAR(128) NOT NULL COMMENT '委托人'; /** 日期：2024-11-13 修改人:苟耕铨 未更新到test-env */ ALTER TABLE assets_calculate MODIFY COLUMN create_user_id bigint(20) DEFAULT NULL COMMENT '测算表创建者user_id'; /** 日期：2024-11-14 修改人:苟耕铨 未更新到test-env */ ALTER TABLE assets_project ADD COLUMN completion_status BIT(1) DEFAULT b'0' NOT NULL COMMENT '项目完成状态'; ALTER TABLE assets_calculate CHANGE COLUMN assets_id project_id BIGINT(20) NOT NULL COMMENT '资产项目id'; /** 日期：2024-11-15 修改人:wucl 未更新到test-env */ DROP TABLE IF EXISTS `house_guaranty_process`; CREATE TABLE `house_guaranty_process` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `parent_id` bigint(20) DEFAULT NULL COMMENT '父级id', `home` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'docHome', `targets_html` text COLLATE utf8mb4_unicode_ci COMMENT '估价结果一览表代码', `process_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '过程节点', `doc_url` varchar(255) COLLATE utf8mb4_unicode_ci 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`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='房地产抵押过程表'; DROP TABLE IF EXISTS `document_production`; CREATE TABLE `document_production` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `business_type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '业务类型（ASSETS、HOUSE、LAND）', `business_id` bigint(20) 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) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '文档名称', `consignor` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '委托人', `doc_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文档路径', `doc_version` int(11) NOT NULL DEFAULT '1' COMMENT '文档版本', `is_active` bit(1) NOT NULL DEFAULT b'1' COMMENT '当前使用', `create_user_id` bigint(20) 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 '删除状态', `business_cate` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '业务子类', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文档产品'; /** 日期：2024-11-15 修改人:苟耕铨 未更新到test-env */ # 修改表 ALTER TABLE assets_calculate ADD COLUMN progress VARCHAR(128) NOT NULL COMMENT '测算表生成进度(BASE、IMPORT、GENERATE、FINISHED)'; # 新建表 DROP TABLE IF EXISTS `assets_calculate_eqpt_data`; CREATE TABLE `assets_calculate_eqpt_data` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '机器设备测算数据id', `assets_calculate_id` bigint(20) NOT NULL COMMENT '资产测算表id', `sort_id` int(11) NOT NULL COMMENT '序号', `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名称', `manufacturer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '生产厂家', `model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '型号规格', `purchase_date` datetime(0) NOT NULL COMMENT '购买日期', `activation_date` datetime(0) NOT NULL COMMENT '启用日期', `storage_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '存放地点', `quantity` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '数量', `unit_of_measurement` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '计量单位', `original_value` float NOT NULL COMMENT '账面原值', `net_value` float NOT NULL COMMENT '账面净值', `equipment_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备类型', `equipment_type_detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备详细类型', `remark` varchar(500) DEFAULT NULL COMMENT '备注', `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间', `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间', `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态', PRIMARY KEY (`id`) USING BTREE, INDEX `assets_calculate_id`(`assets_calculate_id`, `sort_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '机器设备测算数据表' ROW_FORMAT = Dynamic; /** 日期：2024-11-18 修改人:苟耕铨 未更新到test-env */ ALTER TABLE assets_calculate CHANGE COLUMN calculate_info base_info JSON NULL COMMENT '基础测算信息'; /** 日期：2024-11-22 修改人:苟耕铨 未更新到test-env */ ALTER TABLE assets_calculate_eqpt_data ADD COLUMN taxedUnitPrice DECIMAL(11, 2) NULL COMMENT '含税单价'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN taxRate DECIMAL(11, 2) NULL COMMENT '税率'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN economicServiceLife INT(11) NULL COMMENT '经济使用年数'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN directObservationDepreciationRate DECIMAL(11, 2) NULL COMMENT '直接观察成新率'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN valuationMethod VARCHAR(100) NULL COMMENT '作价方法'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN inquiryBasis VARCHAR(500) NULL COMMENT '询价依据'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN inquiryLocation VARCHAR(500) NULL COMMENT '询价地点'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN price DECIMAL(11, 2) NULL COMMENT '单价'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN freightAndMiscellaneousFeeRate DECIMAL(11, 2) NULL COMMENT '运杂费率'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN baseRate DECIMAL(11, 2) NULL COMMENT '基础费率'; ALTER TABLE assets_calculate_eqpt_data ADD COLUMN installationRate DECIMAL(11, 2) NULL COMMENT '安装费率'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN original_value book_original_value DECIMAL(11,2) NOT NULL COMMENT '账面原值'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN net_value book_net_value DECIMAL(11,2) NOT NULL COMMENT '账面净值'; DROP TABLE IF EXISTS `eqpt_life_ifr_refs`; CREATE TABLE `eqpt_life_ifr_refs` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '设备参考年限、安装费率id', `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '类型', `life_low_limit` int(11) NULL DEFAULT NULL COMMENT '使用年限下限', `life_up_limit` int(11) NULL DEFAULT NULL COMMENT '使用年限上限', `life_recommended_value` int(11) NULL DEFAULT NULL COMMENT '推荐使用年限', `ifr_up_limit` int(11) NULL DEFAULT NULL COMMENT '安装费率%上限', `ifr_low_limit` int(11) NULL DEFAULT NULL COMMENT '安装费率%下限', `ifr_recommended_value` int(11) NULL DEFAULT NULL COMMENT '推荐安装费率%', `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注', `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间', `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间', `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1155 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '设备年限、安装费率参考信息表' ROW_FORMAT = Dynamic; DROP TABLE IF EXISTS `house_guaranty_target`; CREATE TABLE `house_guaranty_target` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id', `doc_id` bigint NOT NULL COMMENT '文档id', `t_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '估价对象id', `base_info` json NULL COMMENT '估价对象基本信息（数组）', `is_part` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '是否建筑物部分', `has_building_year` bit(1) NULL DEFAULT NULL COMMENT '是否登记建成年份', `building_year` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '调查的建成年份', `land_certificate_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '国有土地证类型', `land_use_to` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '土地用途', `land_expire_date` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '土地使用权终止日期', `share_acreage1` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '土地无分户产权时分摊面积（㎡）', `is_layer` bit(1) NULL DEFAULT NULL COMMENT '是否采用分层面积处理方式', `has_layer_image` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '分层面积处理方式是否有图', `floor1_acreage` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '一层面积（㎡）', `floor2_acreage` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '二层面积（㎡）', `floor3_acreage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '三层面积（㎡）', `is_address_same` bit(1) NULL DEFAULT NULL COMMENT '查勘地址与登记地址是否一致', `is_date_time_same` bit(1) NULL DEFAULT NULL COMMENT '查勘之日与价值时点是否一致', `is_address_certificate` bit(1) NULL DEFAULT NULL COMMENT '地址不一致时是否开具地址证明', `is_building_reduce_value` bit(1) NULL DEFAULT NULL COMMENT '是否考虑建筑物残值', `has_origin_certificate` bit(1) NULL DEFAULT NULL COMMENT '是否提供权属证件原件', `is_promise` bit(1) NULL DEFAULT NULL COMMENT '《权利状况说明及承诺书》承诺证件一致', `has_land_use_right_contract` bit(1) NULL DEFAULT NULL COMMENT '提供《国有建设用地使用权出让合同》', `is_rent_out` bit(1) NULL DEFAULT NULL COMMENT '估价对象是否已出租', `has_rent_out_contract` bit(1) NULL DEFAULT NULL COMMENT '是否提供租赁合同', `is_rent_limit` bit(1) NULL DEFAULT NULL COMMENT '是否考虑租约限制', `not_rent_limit_reason` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '未考虑租约限制原因', `rent_limit_reason` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '考虑租约限制原因', `rent_end_date` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '租约结束日期', `rent_start_date` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '租约起始日期', `rent_money` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '价值时点租金', `rent_user` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '承租人', `has_pledge` bit(1) NULL DEFAULT NULL COMMENT '是否设置抵押权', `pledge_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '抵押权人', `pledge_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '抵押权类型', `pledge_value` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '抵押权利价值', `same_pledge_continue` bit(1) NULL DEFAULT NULL COMMENT '同一抵押权人续贷', `new_pledge_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '新抵押权利人', `high_pledge_not_expire` bit(1) NULL DEFAULT NULL COMMENT '已设立最高额抵押权且抵押未到期', `same_pledge_high` bit(1) NULL DEFAULT NULL COMMENT '现拟设立最高额抵押权同一抵押权人', `first_money` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '其他法定优先受偿款（万元）', `debt_money` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '拖欠工程款（万元）', `is_take_out_first_money` bit(1) NULL 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`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '解析后的估价对象和补充信息' ROW_FORMAT = Dynamic; /** 日期：2024-11-26 修改人:苟耕铨 未更新到test-env */ DROP TABLE IF EXISTS `eqpt_ppi`; CREATE TABLE `eqpt_ppi` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '机器设备物价指数id', `ppi_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '指标名称/频率/单位', `ppi_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '指数行业类型', `ppi` float(11, 2) NULL DEFAULT NULL COMMENT '指数值', `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间', `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间', `delete_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除状态', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 70 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '机器设备物价指数表' ROW_FORMAT = Dynamic; # 将命名修改为下划线 ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN taxedUnitPrice taxed_unit_price DECIMAL(11, 2) NULL COMMENT '含税单价'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN taxRate tax_rate DECIMAL(11, 2) NULL COMMENT '税率'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN economicServiceLife economic_service_life INT(11) NULL COMMENT '经济使用年数'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN directObservationDepreciationRate direct_observation_depreciation_rate DECIMAL(11, 2) NULL COMMENT '直接观察成新率'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN valuationMethod valuation_method VARCHAR(100) NULL COMMENT '作价方法'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN inquiryBasis inquiry_basis VARCHAR(500) NULL COMMENT '询价依据'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN inquiryLocation inquiry_location VARCHAR(500) NULL COMMENT '询价地点'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN price price DECIMAL(11, 2) NULL COMMENT '单价'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN freightAndMiscellaneousFeeRate freight_and_miscellaneous_fee_rate DECIMAL(11, 2) NULL COMMENT '运杂费率'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN baseRate base_rate DECIMAL(11, 2) NULL COMMENT '基础费率'; ALTER TABLE assets_calculate_eqpt_data CHANGE COLUMN installationRate installation_rate DECIMAL(11, 2) NULL COMMENT '安装费率'; /** 日期：2024-11-28 修改人:苟耕铨 未更新到test-env */ DROP TABLE IF EXISTS `assets_calculate_eqpt_imp_data`; CREATE TABLE `assets_calculate_eqpt_imp_data` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '机器设备测算数据id', `eqpt_data_id` bigint(20) DEFAULT NULL COMMENT '测算数据主表id', `ppi` float(11,2) DEFAULT NULL COMMENT '价格指数', `pricing_basis` varchar(255) DEFAULT NULL COMMENT '取价依据', `central_parity_rate` float(11,2) DEFAULT NULL COMMENT '基准日汇率中间价', `tariff_rate` float(11,4) DEFAULT NULL COMMENT '关税税率', `import_VAT_rate` float(11,4) DEFAULT NULL COMMENT '进口增值税率', `bank_charges_rate` float(11,4) DEFAULT NULL COMMENT '银行财务费率', `foreign_transaction_rate` float(11,4) DEFAULT NULL COMMENT '外贸手续费率', `product_code` varchar(255) DEFAULT NULL COMMENT '商品编号', `currency` varchar(50) DEFAULT NULL COMMENT '币种', `address` varchar(255) DEFAULT NULL COMMENT '地址', `transaction_method` varchar(100) DEFAULT NULL COMMENT '成交方式', `contract_amount` decimal(11,2) DEFAULT NULL COMMENT '合同金额', `contract_signing_date` date DEFAULT NULL COMMENT '合同签订日期', `destination_port` varchar(100) DEFAULT NULL COMMENT '到岸港口', `original_price` decimal(11,2) 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 `eqpt_data_id` (`eqpt_data_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='机器设备-进口设备测算数据表'; /** 日期：2024-11-28 修改人:wucl 未更新到test-env */ ALTER TABLE tmpl_house_paragraph ADD COLUMN rule varchar(255) NULL COMMENT '选用规则'; ALTER TABLE house_guaranty_target ADD COLUMN is_costing_share_method bit(1) NULL COMMENT '采用成本法且分摊土地面积处理'; ALTER TABLE house_guaranty_target ADD COLUMN no_building_house_nos bit(1) NULL COMMENT '未见栋号房号'; ALTER TABLE house_guaranty_target ADD COLUMN explore_date varchar(64) NULL COMMENT '查勘日期'; ALTER TABLE house_guaranty_target ADD COLUMN land_year_lower_house_year bit(1) NULL COMMENT '土地剩余使用年限短于建筑物剩余经济寿命'; ALTER TABLE house_guaranty_target ADD COLUMN is_promise_file bit(1) NULL COMMENT '《承诺书》承诺相关的法律文件复印件与原件一致'; ALTER TABLE house_guaranty_target ADD COLUMN is_in_cheng_du bit(1) NULL COMMENT '估价对象是否属于大成都范围内';