Calculation/sql/update20230928.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`;

CREATE TABLE `zh_sub_project_info` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  `project_id` int(11) unsigned NOT NULL COMMENT '项目id',
  `proj_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目名称',
  `chief_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '主管部门',
  `proj_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目类型',
  `proj_quality` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目性质',
  `proj_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '级别',
  `plan_start_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '计划开工',
  `plan_finish_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '计划完工',
  `real_start_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '实际开工',
  `real_finish_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '实际完工',
  `lx_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-部门',
  `lx_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-日期',
  `lx_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-文号',
  `cb_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-部门',
  `cb_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-日期',
  `cb_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-文号',
  `sg_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-部门',
  `sg_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-日期',
  `sg_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-文号',
  `jg_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-部门',
  `jg_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-日期',
  `jg_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-文号',
  `jg_quality_score` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-工程质量评分',
  `jg_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-等级',
  `unit_construction` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-建设单位',
  `unit_qa` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-质量监督机构',
  `unit_design` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要设计单位',
  `unit_supervision` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要监理单位',
  `unit_contract` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要施工单位',
  `mainline_length` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '主线公路里程',
  `branch_length` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '支线里程',
  `road_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '公路等级',
  `design_speed` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '公路设计速度',
  `design_load` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '设计荷载',
  `bed_width` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '路基宽度',
  `tunnel_width` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '隧道净宽',
  `quake_peak_value` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '地震动峰值系数',
  `main_quantity` json DEFAULT NULL COMMENT '主要工程数据',
  `gcl_quantity` json DEFAULT NULL COMMENT '工料机消耗',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `calculation`.`zh_change_history`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `tid` int NOT NULL COMMENT '标段id',
  `cid` varchar(255) NOT NULL COMMENT '变更令id',
  `info_json` json NULL COMMENT '内容json值',
  `list_json` json NULL COMMENT '清单json值',
  PRIMARY KEY (`id`)
) COMMENT = '变更令内容临时保存表，用于修订撤销';

ALTER TABLE `zh_change_apply_list` ADD `new_up` tinyint(1) NOT NULL DEFAULT '0' COMMENT '新增单价' AFTER `camount`;

ALTER TABLE `zh_change_apply_list` ADD `ex_memo1` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注1' AFTER `new_up`;

ALTER TABLE `zh_change_apply_list` ADD `ex_memo2` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注2' AFTER `ex_memo1`;

-- 更新所有已勾选计量不计价项目的负批复变更数量值清单为不计价
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);

-- 更新resive状态下usite值为0
UPDATE `calculation`.zh_change_audit SET `usite` = 0 WHERE `status` = 9;