Calculation/sql/update.sql

-- sql提交须知：
-- 1. 请按如下分类提交sql！！！
-- 2. 表名前不可有库名！！！
-- 3. 表结构部分，新添加的表，不可先创建后修改！！！
--
-- Version
-- uat
-- prod

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

ALTER TABLE `zh_tender`
ADD COLUMN `share_token` varchar(20) NOT NULL DEFAULT '' COMMENT '标段分享token' AFTER `filter_fund`,
ADD COLUMN `share_expiration` datetime NULL COMMENT '分享token到期时间' AFTER `share_token`;

ALTER TABLE `zh_stage`
ADD COLUMN `update_calc_time` timestamp NULL COMMENT '最后修改计算数据时间' AFTER `cache_time_l`,
ADD COLUMN `update_expr_calc_time` timestamp NULL COMMENT '最后使用公式计算时间' AFTER `update_calc_time`;

ALTER TABLE `zh_contract`
ADD COLUMN `rela_tender` int NULL DEFAULT NULL COMMENT '关联标段id' AFTER `exist_supplement`,
ADD COLUMN `settle_price` decimal(30, 8) NOT NULL DEFAULT 0 COMMENT '结算金额（独立字段，非合同结算功能）' AFTER `total_price`;

ALTER TABLE `zh_contract_pay`
ADD COLUMN `rela_stage` varchar(255) NULL DEFAULT NULL COMMENT '关联的标段期id或者独立支付期id' AFTER `remark`;

ALTER TABLE `zh_project_col_set`
ADD COLUMN `list` json NULL COMMENT '列表页列设置（见/const/project_setting）' AFTER `info`;

ALTER TABLE `zh_contract`
ADD COLUMN `selected` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '报表用，是否选择' AFTER `create_time`;

ALTER TABLE `zh_contract_tree`
ADD COLUMN `selected` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '报表用，是否选择' AFTER `calc2`;

ALTER TABLE `zh_contract_pay`
ADD COLUMN `kp_price` decimal(30, 8) NULL DEFAULT 0 COMMENT '开票金额' AFTER `pay_type`,
ADD COLUMN `kp_time` datetime NULL DEFAULT NULL COMMENT '开票时间' AFTER `kp_price`;

ALTER TABLE `zh_stage_audit`
ADD COLUMN `audit_locked_lid` varchar(5000) NOT NULL DEFAULT '' COMMENT '审批退回锁定台账id' AFTER `audit_locked`;

ALTER TABLE `zh_material_exponent`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_history`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '本期有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_shard`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '上涨幅度(%)' AFTER `weight_num`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_history`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期上涨幅度(%)' AFTER `basic_times`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '本期下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '本期有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_material_exponent_shard`
ADD COLUMN `m_up_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '上涨幅度(%)' AFTER `weight_num`,
ADD COLUMN `m_down_risk` tinyint(3) NOT NULL DEFAULT 0 COMMENT '下跌幅度(%)' AFTER `m_up_risk`,
ADD COLUMN `effective_price` decimal(30,8) DEFAULT NULL COMMENT '有效价格指数(Ft)' AFTER `m_price`;

ALTER TABLE `zh_filing`
ADD COLUMN `upload_tips` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '上传说明' AFTER `tips`;

ALTER TABLE `zh_filing`
ADD COLUMN `create_uid` int(11) unsigned NOT NULL DEFAULT 0
COMMENT '新版配置目录添加人ID'
AFTER `add_user_id`;

CREATE TABLE `zh_sub_project_filing_permission` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'UUID',
  `pid` int(11) unsigned NOT NULL COMMENT '项目ID',
  `spid` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT '子项目ID',
  `uid` int(11) unsigned NOT NULL COMMENT '项目用户ID',
  `filing_type` int(11) unsigned NOT NULL COMMENT '资料类别类型',
  `can_upload` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '上传/引用权限',
  `can_edit_file` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '编辑文件权限',
  `can_edit_dir` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '编辑目录权限',
  `can_lock_file` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '锁定文件权限',
  `create_uid` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '创建人ID',
  `update_uid` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '最后修改人ID',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sp_user_filing_type` (`spid`, `uid`, `filing_type`),
  KEY `idx_sp_user` (`spid`, `uid`),
  KEY `idx_sp_filing_type` (`spid`, `filing_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='子项目资料类别用户操作权限';

ALTER TABLE `zh_sub_project_filing_permission`
ADD COLUMN `filing_id` varchar(36) COLLATE utf8_unicode_ci NULL DEFAULT NULL
COMMENT '具体资料目录ID，NULL表示旧filing_type权限'
AFTER `filing_type`,
DROP INDEX `uk_sp_user_filing_type`,
ADD UNIQUE KEY `uk_sp_user_filing_node`
(`spid`, `uid`, `filing_id`),
ADD KEY `idx_sp_filing_id`
(`spid`, `filing_id`);

ALTER TABLE `zh_file`
ADD COLUMN `is_locked` tinyint(1) UNSIGNED NOT NULL DEFAULT 0
COMMENT '文件锁定状态：0未锁定，1已锁定'
AFTER `rela_info`;
------------------------------------
-- 表数据
------------------------------------

UPDATE `zh_material_exponent` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;
UPDATE `zh_material_exponent_history` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;
UPDATE `zh_material_exponent_shard` SET `effective_price` = `m_price` WHERE `effective_price` IS NULL;