Calculation/sql/update.sql

-- 请按如下分类提交sql！！！
-- Version V3.5.47.0141
-- uat 2025-1-16/20
-- prod todo

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

ALTER TABLE `zh_sub_project`
ADD COLUMN `page_show` varchar(5000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '{\"bwtz\":\"1\"}' COMMENT '前台页面或功能展示与隐藏' AFTER `lock_file`,
ADD COLUMN `fun_rela` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '功能设置(json.stringify)' AFTER `page_show`,
ADD COLUMN `data_collect` tinyint(1) NOT NULL DEFAULT 0 COMMENT '决策大屏是否显示及对应大屏编号' AFTER `fun_rela`,
ADD COLUMN `data_collect_pages` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '需要展示的大屏字符串，以，分隔' AFTER `data_collect`,
ADD COLUMN `fun_set` varchar(5000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '项目设置页内容json' AFTER `data_collect_pages`,
ADD COLUMN `payment_setting` JSON NULL DEFAULT NULL COMMENT '支付审批模块设置' AFTER `fun_set`,
ADD COLUMN `common_json` json NULL COMMENT '通用json，没有sql查询值必要的可放这' AFTER `payment_setting`;

ALTER TABLE `zh_category`
ADD COLUMN `spid` varchar(36) NOT NULL DEFAULT '' COMMENT '子项目id（uuid）' AFTER `pid`;

ALTER TABLE `zh_category_value`
ADD COLUMN `spid` varchar(36) NOT NULL DEFAULT '' COMMENT '子项目id（uuid）' AFTER `pid`;

ALTER TABLE `zh_sub_project_permission`
ADD COLUMN `self_category_level` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' AFTER `update_time`;

ALTER TABLE `zh_project_log`
ADD COLUMN `spid` varchar(36) NOT NULL DEFAULT '' COMMENT '操作子项目' AFTER `pid`;

ALTER TABLE `zh_sub_project`
MODIFY COLUMN `rela_tender` varchar(5000) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '关联标段id(\',\'分隔)' AFTER `user_id`;

ALTER TABLE `zh_budget`
MODIFY COLUMN `rela_tender` varchar(5000) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '关联标段id(\',\'分隔)' AFTER `std_id`;

ALTER TABLE `zh_sub_project_permission`
ADD COLUMN `info_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '项目概况，权限id列表(\',\'分隔)' AFTER `self_category_level`,
ADD COLUMN `fund_trans_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '资金划拨，权限id列表(\',\'分隔)' AFTER `info_permission`,
ADD COLUMN `fund_pay_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '资金支付，权限id列表(\',\'分隔)' AFTER `fund_trans_permission`,
ADD COLUMN `contract_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '合同管理，权限id列表(\',\'分隔)' AFTER `fund_pay_permission`,
ADD COLUMN `datacollect_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '决策大屏，权限id列表(\',\'分隔)' AFTER `contract_permission`,
ADD COLUMN `payment_permission` varchar(255) NOT NULL DEFAULT '' COMMENT '支付审批，权限id列表(\',\'分隔)' AFTER `datacollect_permission`;

ALTER TABLE `zh_file_reference_list`
ADD COLUMN `file_type` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '文件类型' AFTER `remark`,
ADD COLUMN `file_type_str` varchar(50) NOT NULL DEFAULT '资料归集' COMMENT '文件类型-文本' AFTER `file_type`;

ALTER TABLE `zh_file_reference`
MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '名称' AFTER `is_leaf`,
ADD COLUMN `code` varchar(50) NOT NULL DEFAULT '' COMMENT '序号/编号' AFTER `name`,
MODIFY COLUMN `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '备注' AFTER `name`;

ALTER TABLE `zh_budget_std`
ADD COLUMN `progress_template_id` varchar(255) NOT NULL DEFAULT '' COMMENT '项目概况-阶段进度-新建模板id列表（‘,’分隔）' AFTER `ht_tender_template_id`;

ALTER TABLE `zh_project_col_set`
ADD COLUMN `pid` int(11) NOT NULL DEFAULT 0 COMMENT 'zh_project.id' AFTER `id`,
ADD COLUMN `spid` varchar(36) NOT NULL DEFAULT '' COMMENT 'zh_sub_project.id' AFTER `pid`,
ADD COLUMN `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `info`,
ADD COLUMN `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间' AFTER `create_time`;

ALTER TABLE `zh_payment_tender`
ADD COLUMN `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '子项目id' AFTER `pid`;

ALTER TABLE `zh_payment_folder`
MODIFY COLUMN `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL FIRST,
ADD COLUMN `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '子项目id' AFTER `pid`,
ADD COLUMN `folder_id` int(11) NOT NULL DEFAULT 0 COMMENT '节点id' AFTER `name`;

ALTER TABLE `zh_tender`
ADD COLUMN `filter_budget` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '动态投资-是否过滤' AFTER `spid`,
ADD COLUMN `filter_fund` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '资金监管-是否过滤' AFTER `filter_budget`;

ALTER TABLE `zh_notice`
ADD COLUMN `spid` varchar(36) NULL DEFAULT '' COMMENT '子项目id' AFTER `pid`,
ADD COLUMN `tid` int(11) NULL DEFAULT NULL COMMENT '标段id' AFTER `spid`;

ALTER TABLE `zh_message`
ADD COLUMN `spid` varchar(36) NULL DEFAULT '' COMMENT '子项目id' AFTER `project_id`;

ALTER TABLE `zh_filing_template`
ADD COLUMN `is_rela` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '可否导入文件' AFTER `tips`;

ALTER TABLE `zh_filing`
ADD COLUMN `is_rela` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '可否导入文件' AFTER `tips`;

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

UPDATE zh_project_log pl LEFT JOIN zh_tender t ON pl.tid = t.id SET pl.spid = IF(ISNULL(t.spid),'',t.spid);

Update zh_project_col_set SET pid = id;

Update zh_payment_folder SET folder_id = id;

INSERT INTO `zh_permission` (`id`, `name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`) VALUES (77, '项目设置', 'project', 'sp-pageshow', 38, 'sp-pageshow', NULL, 1);

UPDATE zh_notice SET tid = JSON_UNQUOTE(JSON_EXTRACT(content, '$.tid')) WHERE tid IS NULL;

UPDATE zh_notice SET spid = JSON_UNQUOTE(JSON_EXTRACT(content, '$.spid')) WHERE spid = '';