Calculation/sql/update20240419.sql

ALTER TABLE `zh_tender_info`
ADD COLUMN `s_type` varchar(20) NOT NULL DEFAULT '' COMMENT '标段类型（公路gl，房建fj，市政sz）' AFTER `dagl_info`;

ALTER TABLE `zh_sub_project`
ADD COLUMN `filing_template_id` varchar(36) NOT NULL DEFAULT '' COMMENT '资料归集模板id' AFTER `std_name`,
ADD COLUMN `filing_template_name` varchar(255) NOT NULL DEFAULT '' COMMENT '资料归集模板名称' AFTER `filing_template_id`;

CREATE TABLE `zh_filing_template_list`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `project_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '项目id',
  `ft_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '模板类型（0默认，1新增）',
  `user_id` int(11) UNSIGNED NOT NULL COMMENT '添加用户',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_filing_template`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `temp_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'template_id(zh_filing_template_list)',
  `tree_pid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `tree_order` int(11) UNSIGNED NOT NULL COMMENT '树结构-同层排序',
  `tree_level` int(11) UNSIGNED NOT NULL COMMENT '树结构-层级',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  `add_user_id` int(11) NOT NULL DEFAULT 0 COMMENT '添加人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `is_fixed` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为固定项',
  `filing_type` int(11) NOT NULL COMMENT '文件类型（跟父项相关，类型为最顶层父项名称）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_account_cert`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int NULL COMMENT '项目id',
  `uid` int NULL COMMENT '用户id',
  `type` tinyint(5) NULL DEFAULT NULL COMMENT '证书名称分类',
  `name` tinyint(5) NULL DEFAULT NULL COMMENT '证书名称',
  `code` varchar(255) NULL DEFAULT NULL COMMENT '证件编号',
  `reg_unit` varchar(255) NULL DEFAULT NULL COMMENT '注册单位',
  `job_title` varchar(255) NULL DEFAULT NULL COMMENT '技术职称',
  `file_name` varchar(255) NULL COMMENT '文件名称',
  `file_path` varchar(255) NULL COMMENT '文件下载地址',
  `edu_json` json NULL COMMENT '继续教育json',
  `create_time` datetime NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '用户证书表';

CREATE TABLE `zh_tender_cert`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `tid` int NULL COMMENT '标段id',
  `uid` int NULL COMMENT '用户id',
  `cert_id` int NULL COMMENT '个人证书id',
  `department` varchar(255) NULL DEFAULT NULL COMMENT '所在部门',
  `job_time` varchar(255) NULL DEFAULT NULL COMMENT '在岗时间',
  `remark` varchar(1000) NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime NULL COMMENT '入库时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '标段从业人员表';

ALTER TABLE `zh_change_audit`
ADD COLUMN `audit_type`  tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批类型(1个人，2会签，3或签)' AFTER `sin_time`,
ADD COLUMN `audit_order`  tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '审批顺序' AFTER `audit_type`;

ALTER TABLE `zh_change_audit`
ADD COLUMN `begin_time` datetime NULL DEFAULT NULL COMMENT '审批开始时间' AFTER `sdesc`,
ADD COLUMN `end_time` datetime NULL DEFAULT NULL COMMENT '审批结束时间' AFTER `begin_time`,
MODIFY COLUMN `name` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '审批人名称' AFTER `uid`,
MODIFY COLUMN `jobs` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '审批人职称' AFTER `name`,
MODIFY COLUMN `company` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '审批人单位' AFTER `jobs`,
MODIFY COLUMN `sin_time` datetime NULL DEFAULT NULL COMMENT '审批结束时间' AFTER `end_time`;

CREATE TABLE `zh_project_spread`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT 'zh_project.id',
  `code` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `is_default` tinyint(4) UNSIGNED NOT NULL COMMENT '是否默认值',
  `tz_ledger_set` json NULL COMMENT '台账-台账-设置',
  `tz_ledger_bills_spread` json NULL COMMENT '台账-台账-清单spread',
  `tz_ledger_pos_spread` json NULL COMMENT '台账-台账-计量单元spread',
  `gcl_ledger_set` json NULL COMMENT '工程量清单-台账-设置',
  `gcl_ledger_bills_spread` json NULL COMMENT '工程量清单-台账-清单spread',
  `gcl_ledger_pos_spread` json NULL COMMENT '工程量清单-台账-计量单元spread',
  `tz_stage_set` json NULL COMMENT '台账-期计量-设置',
  `tz_stage_bills_spread` json NULL COMMENT '台账-期计量-清单spread',
  `tz_stage_pos_spread` json NULL COMMENT '台账-期计量-计量单元spread',
  `gcl_stage_set` json NULL COMMENT '工程量清单-期计量-设置',
  `gcl_stage_bills_spread` json NULL COMMENT '工程量清单-期计量-清单spread',
  `gcl_stage_pos_spread` json NULL COMMENT '工程量清单-期计量-计量单元spread',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_project_col_set`  (
  `id` int(11) NOT NULL COMMENT 'zh_project.id',
  `info` json NULL COMMENT '金额概况页列设置（见/const/project_setting）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

INSERT INTO `zh_filing_template_list` (`id`, `project_id`, `ft_type`, `user_id`, `name`, `memo`, `create_time`, `update_time`) VALUES ('698e87d8-e947-4049-98e4-15aae7c5c7fc', 0, 0, 0, '建设项目档案管理规范DA_T 28-2018', '', '2024-03-22 15:04:31', '2024-03-22 15:06:07');

INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('0395ade6-5fe6-4b36-9b0a-c3696b044aaf', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 1, 1, '立项文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 1);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('5efafc28-fef5-46b3-be92-581a47cc454e', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 2, 1, '招标投标、合同协议文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 2);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('ef7ce0ba-f503-4364-b73f-ed9a1fe8c98d', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 3, 1, '勘察、设计文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 3);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('e2ac9fb4-08f7-4968-b5d1-c2487c2f3f61', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 4, 1, '征地、拆迁、移民文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 4);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('da3b9fb7-646f-49fa-8522-64d1d5ee53b1', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 5, 1, '项目管理文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 5);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('32be53bb-86db-4e4d-ae2d-03d3586e3965', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 6, 1, '施工文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 6);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('4c759d53-50f4-40fe-8f3b-b5b6ba8d3db3', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 7, 1, '信息系统开发文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 7);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('94505d98-f661-4be8-b381-2735476948b9', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 8, 1, '设备文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 8);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('5aca0493-089e-4064-83fd-3ac061cc1b04', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 9, 1, '监理文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 9);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('52f26ad6-bcd3-4bea-b533-283281fe6124', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 10, 1, '科研项目文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 10);
INSERT INTO `zh_filing_template` (`id`, `temp_id`, `tree_pid`, `tree_order`, `tree_level`, `name`, `add_user_id`, `create_time`, `update_time`, `is_fixed`, `filing_type`) VALUES ('b95ecebd-fbd4-4110-abf4-7c055255d88f', '698e87d8-e947-4049-98e4-15aae7c5c7fc', '-1', 11, 1, '生产技术准备、试运行文件', 13, '2024-03-25 17:16:46', '2024-03-25 17:16:46', 1, 11);

UPDATE zh_sub_project SET filing_template_id = "698e87d8-e947-4049-98e4-15aae7c5c7fc", filing_template_name = '建设项目档案管理规范DA_T 28-2018' WHERE management <> "";

update `zh_change_audit` set `audit_order` = `usite`;

update `zh_change_audit` set `begin_time` = `sin_time`;
update `zh_change_audit` set `end_time` = `sin_time`;