Calculation/sql/update.sql

ALTER TABLE `zh_material_audit`
ADD COLUMN `tp_data` json DEFAULT NULL COMMENT '金额数据(报表用)' AFTER `opinion`;

ALTER TABLE `zh_material`
ADD COLUMN `tp_data` json DEFAULT NULL COMMENT '上报人上报时的金额数据(报表用)' AFTER `is_stage_self`;

ALTER TABLE `zh_tender`
ADD COLUMN `material_col_show` json DEFAULT NULL COMMENT '调差列表展示合计控制';

ALTER TABLE `zh_change_audit_list`
ADD COLUMN `camount_expr` varchar(500) DEFAULT '' COMMENT '变更数量计算式' AFTER `camount`;

ALTER TABLE `zh_change_audit_list`
ADD COLUMN `is_valuation` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否计价' AFTER `spamount`;

ALTER TABLE `zh_change_audit_list`
ADD COLUMN `delimit` int(4) NOT NULL DEFAULT 100 COMMENT '计量上限值';

ALTER TABLE `zh_change`
ADD COLUMN `state` tinyint(1) NOT NULL DEFAULT 3 COMMENT '变更令状态(计量上限设置使用)';

ALTER TABLE `zh_change`
ADD COLUMN `delimit` int(4) NOT NULL DEFAULT 100 COMMENT '计量上限值（用于对比项目及更新清单上限值）' AFTER `state`;

-- 更新所有已勾选计量不计价项目的负批复变更数量值清单为不计价
UPDATE `calculation`.zh_change_audit_list SET `is_valuation` = 0 WHERE `id` IN (SELECT id FROM (SELECT a.id FROM `calculation`.zh_change_audit_list as a LEFT JOIN `calculation`.`zh_tender` as t ON a.tid = t.id LEFT JOIN `calculation`.`zh_project` as p ON p.id = t.project_id LEFT JOIN `calculation`.`zh_change` as c ON c.cid = a.cid WHERE c.`status` = 3 AND a.`spamount` < 0 AND p.fun_rela != '' AND p.fun_rela IS NOT NULL AND JSON_CONTAINS(p.fun_rela, JSON_OBJECT('minusNoValue', true))) as aa)