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`;
------------------------------------
-- 表数据
------------------------------------

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;