-- 请按如下分类提交sql！！！
-- Version V3.5.46.1224
-- uat 20241204
-- prod 20241205

------------------------------------
-- 表结构
------------------------------------

ALTER TABLE `zh_sub_project_info`
ADD COLUMN `proj_intro` text NULL COMMENT '项目概述' AFTER `project_id`,
ADD COLUMN `proj_cur_status` text NULL COMMENT '当前状态' AFTER `proj_intro`,
ADD COLUMN `proj_status` varchar(20) NOT NULL DEFAULT '' COMMENT '建设状态' AFTER `proj_level`,
ADD COLUMN `lx_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '立项-金额' AFTER `lx_code`,
ADD COLUMN `lx_memo` text NULL COMMENT '立项-备注' AFTER `lx_tp`,
ADD COLUMN `cb_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '初步-金额' AFTER `cb_code`,
ADD COLUMN `cb_memo` text NULL COMMENT '初步-备注' AFTER `cb_tp`,
ADD COLUMN `sg_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '施工许可批复-金额' AFTER `sg_code`,
ADD COLUMN `sg_memo` text NULL COMMENT '施工许可批复-备注' AFTER `sg_tp`,
ADD COLUMN `sgt_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-部门' AFTER `gcl_quantity`,
ADD COLUMN `sgt_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-日期' AFTER `sgt_department`,
ADD COLUMN `sgt_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-文号' AFTER `sgt_date`,
ADD COLUMN `sgt_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '施工图-金额' AFTER `sgt_code`,
ADD COLUMN `sgt_memo` text NULL COMMENT '施工图-备注' AFTER `sgt_tp`,
ADD COLUMN `pf_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-部门' AFTER `sgt_memo`,
ADD COLUMN `pf_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-日期' AFTER `pf_department`,
ADD COLUMN `pf_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-文号' AFTER `pf_date`,
ADD COLUMN `pf_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '财评/造价站批复-金额' AFTER `pf_code`,
ADD COLUMN `pf_memo` text NULL COMMENT '财评/造价站批复-备注' AFTER `pf_tp`,
ADD COLUMN `zb_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-部门' AFTER `pf_memo`,
ADD COLUMN `zb_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-日期' AFTER `zb_department`,
ADD COLUMN `zb_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-文号' AFTER `zb_date`,
ADD COLUMN `zb_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '招标完成-金额' AFTER `zb_code`,
ADD COLUMN `zb_memo` text NULL COMMENT '招标完成-备注' AFTER `zb_tp`;

ALTER TABLE `zh_sub_project_info`
ADD COLUMN `jg_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '交工-金额' AFTER `jg_level`,
ADD COLUMN `jg_memo` text NULL COMMENT '交工-备注' AFTER `jg_tp`;

CREATE TABLE `zh_sub_project_progress`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `tree_id` int(11) NOT NULL COMMENT '节点id',
  `tree_pid` int(11) NOT NULL COMMENT '父节点id',
  `tree_order` tinyint(4) NOT NULL COMMENT '同级排序',
  `tree_level` tinyint(4) NOT NULL COMMENT '层级',
  `tree_full_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '层级定位辅助字段parent.full_path.ledger_id',
  `tree_is_leaf` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否叶子节点，界面显示辅助字段',
  `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '编号/序号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '阶段/项目名称',
  `edit_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-进度',
  `edit_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-日期',
  `edit_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-部门',
  `submit_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-进度',
  `submit_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-日期',
  `submit_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-部门',
  `reply_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-进度',
  `reply_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-日期',
  `reply_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-部门',
  `reply_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-文号',
  `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  `add_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人',
  `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 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_sub_project_file`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目id(sub_project.id)',
  `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '类型(progress/push/...)',
  `rela_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid(zh_sub_project_progress.id/zh_sub_project_push.id/...)',
  `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
  `fileext` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  `filesize` int(11) NOT NULL COMMENT '文件大小',
  `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  `user_id` int(11) UNSIGNED NOT NULL COMMENT '用户id(zh_project_account.id)',
  `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名(缓存)',
  `user_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '公司(缓存)',
  `user_role` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '角色(缓存)',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  `is_deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

CREATE TABLE `zh_sub_project_push`  (
  `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目id(zh_sub_project.id)',
  `push_order` int(11) UNSIGNED NOT NULL COMMENT '序号',
  `push_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '日期',
  `push_content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '记录内容',
  `memo` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '备注',
  `user_id` int(11) UNSIGNED NOT NULL COMMENT '创建人id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` int(11) UNSIGNED NOT NULL COMMENT '最后修改人id',
  `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_message_attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL COMMENT '项目id',
  `mid` int(11) NOT NULL DEFAULT '0' COMMENT '通知id',
  `uid` int(11) NOT NULL COMMENT '上传者id',
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名称',
  `fileext` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  `filesize` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件大小',
  `filepath` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  `upload_time` datetime NOT NULL COMMENT '上传时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_mid` (`mid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='通知附件表';

ALTER TABLE `zh_tender_info`
ADD COLUMN `over_range_check` varchar(255) NOT NULL DEFAULT '' AFTER `s_type`;

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

ALTER TABLE `zh_material`
ADD COLUMN `final_auditor_str` varchar(50) NOT NULL DEFAULT '' COMMENT '终审人相关';

ALTER TABLE `zh_filing_template`
ADD COLUMN `tips` varchar(1000) NOT NULL DEFAULT '' COMMENT '提示' AFTER `filing_type`;
ALTER TABLE `zh_filing`
ADD COLUMN `tips` varchar(1000) NOT NULL DEFAULT '' COMMENT '提示' AFTER `file_count`;

ALTER TABLE `zh_material_audit` ADD `is_old` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为旧流程(用于管理员修改流程时旧数据保留但不影响新流程)' AFTER `opinion`;

------------------------------------
-- 表数据
------------------------------------

UPDATE zh_tender_info ti LEFT JOIN zh_tender t ON ti.tid = t.id SET ti.over_range_check = '{"field":"deal","percent":100,"billsWithPos":"bills"}' WHERE t.measure_type = 2;
UPDATE zh_tender_info ti LEFT JOIN zh_tender t ON ti.tid = t.id SET ti.over_range_check = '{"field":"tz","percent":100,"billsWithPos":"both"}' WHERE t.measure_type = 1;