update20230928.sql 6.3 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889
  1. ALTER TABLE `zh_material_audit`
  2. ADD COLUMN `tp_data` json DEFAULT NULL COMMENT '金额数据(报表用)' AFTER `opinion`;
  3. ALTER TABLE `zh_material`
  4. ADD COLUMN `tp_data` json DEFAULT NULL COMMENT '上报人上报时的金额数据(报表用)' AFTER `is_stage_self`;
  5. ALTER TABLE `zh_tender`
  6. ADD COLUMN `material_col_show` json DEFAULT NULL COMMENT '调差列表展示合计控制';
  7. ALTER TABLE `zh_change_audit_list`
  8. ADD COLUMN `camount_expr` varchar(500) DEFAULT '' COMMENT '变更数量计算式' AFTER `camount`;
  9. ALTER TABLE `zh_change_audit_list`
  10. ADD COLUMN `is_valuation` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否计价' AFTER `spamount`;
  11. ALTER TABLE `zh_change_audit_list`
  12. ADD COLUMN `delimit` int(4) NOT NULL DEFAULT 100 COMMENT '计量上限值';
  13. ALTER TABLE `zh_change`
  14. ADD COLUMN `state` tinyint(1) NOT NULL DEFAULT 3 COMMENT '变更令状态(计量上限设置使用)';
  15. ALTER TABLE `zh_change`
  16. ADD COLUMN `delimit` int(4) NOT NULL DEFAULT 100 COMMENT '计量上限值(用于对比项目及更新清单上限值)' AFTER `state`;
  17. CREATE TABLE `zh_sub_project_info` (
  18. `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  19. `project_id` int(11) unsigned NOT NULL COMMENT '项目id',
  20. `proj_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目名称',
  21. `chief_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '主管部门',
  22. `proj_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目类型',
  23. `proj_quality` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目性质',
  24. `proj_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '级别',
  25. `plan_start_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '计划开工',
  26. `plan_finish_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '计划完工',
  27. `real_start_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '实际开工',
  28. `real_finish_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '实际完工',
  29. `lx_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-部门',
  30. `lx_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-日期',
  31. `lx_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '立项-文号',
  32. `cb_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-部门',
  33. `cb_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-日期',
  34. `cb_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '初步-文号',
  35. `sg_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-部门',
  36. `sg_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-日期',
  37. `sg_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工许可批复-文号',
  38. `jg_department` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-部门',
  39. `jg_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-日期',
  40. `jg_code` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-文号',
  41. `jg_quality_score` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-工程质量评分',
  42. `jg_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '交工-等级',
  43. `unit_construction` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-建设单位',
  44. `unit_qa` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-质量监督机构',
  45. `unit_design` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要设计单位',
  46. `unit_supervision` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要监理单位',
  47. `unit_contract` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位-主要施工单位',
  48. `mainline_length` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '主线公路里程',
  49. `branch_length` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '支线里程',
  50. `road_level` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '公路等级',
  51. `design_speed` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '公路设计速度',
  52. `design_load` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '设计荷载',
  53. `bed_width` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '路基宽度',
  54. `tunnel_width` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '隧道净宽',
  55. `quake_peak_value` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '地震动峰值系数',
  56. `main_quantity` json DEFAULT NULL COMMENT '主要工程数据',
  57. `gcl_quantity` json DEFAULT NULL COMMENT '工料机消耗',
  58. PRIMARY KEY (`id`)
  59. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  60. CREATE TABLE `calculation`.`zh_change_history` (
  61. `id` int NOT NULL AUTO_INCREMENT,
  62. `tid` int NOT NULL COMMENT '标段id',
  63. `cid` varchar(255) NOT NULL COMMENT '变更令id',
  64. `info_json` json NULL COMMENT '内容json值',
  65. `list_json` json NULL COMMENT '清单json值',
  66. PRIMARY KEY (`id`)
  67. ) COMMENT = '变更令内容临时保存表,用于修订撤销';
  68. ALTER TABLE `zh_change_apply_list` ADD `new_up` tinyint(1) NOT NULL DEFAULT '0' COMMENT '新增单价' AFTER `camount`;
  69. ALTER TABLE `zh_change_apply_list` ADD `ex_memo1` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注1' AFTER `new_up`;
  70. ALTER TABLE `zh_change_apply_list` ADD `ex_memo2` VARCHAR(255) NULL DEFAULT NULL COMMENT '备注2' AFTER `ex_memo1`;
  71. -- 更新所有已勾选计量不计价项目的负批复变更数量值清单为不计价
  72. 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);
  73. -- 更新resive状态下usite值为0
  74. UPDATE `calculation`.zh_change_audit SET `usite` = 0 WHERE `status` = 9;