Calculation/sql/update.sql

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

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

ALTER TABLE `zh_contract`
ADD COLUMN `price` decimal(30, 8) NULL DEFAULT 0 COMMENT '合同金额（可编辑，和补充合同金额汇总得出总金额）' AFTER `total_price`;

ALTER TABLE `zh_contract_sp_audit`
ADD COLUMN `csid` int NULL DEFAULT NULL COMMENT '补充合同id' AFTER `cpid`;

ALTER TABLE `zh_sub_project`
ADD COLUMN `cost_ledger_col_set` json NULL COMMENT '成本报审-列设置' AFTER `cost_calc_template`;

ALTER TABLE `zh_cost_stage_analysis`
ADD COLUMN `has_detail` int(1) NOT NULL DEFAULT 0 COMMENT '是否含有明细' AFTER `calc_type`,
ADD COLUMN `import_cost_id` varchar(5000) NOT NULL DEFAULT '' COMMENT '导入的成本id' AFTER `has_detail`;

CREATE TABLE `zh_budget_real`  (
  `id` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '自增id',
  `bid` int(10) NOT NULL COMMENT '概算投资项目id',
  `tree_id` int(10) NOT NULL COMMENT '节点id',
  `tree_pid` int(10) NOT NULL COMMENT '父节点id',
  `level` tinyint(4) NOT NULL COMMENT '层级',
  `order` mediumint(4) NOT NULL DEFAULT 0 COMMENT '同级排序',
  `full_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '层级定位辅助字段parent.full_path.ledger_id',
  `is_leaf` tinyint(1) NOT NULL COMMENT '是否叶子节点，界面显示辅助字段',
  `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '节点编号',
  `b_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '名称',
  `unit` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '单位',
  `unit_price` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '单价',
  `quantity` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '数量',
  `total_price` decimal(24, 8) NULL DEFAULT 0.00000000 COMMENT '金额',
  `dgn_qty1` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '设计数量1',
  `dgn_qty2` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '设计数量2',
  `drawing_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '图册号',
  `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '备注',
  `node_type` int(4) UNSIGNED NULL DEFAULT 0 COMMENT '节点类别',
  `source` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '添加源',
  `remark` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_tender_id`(`bid`) USING BTREE,
  INDEX `idx_template_pid`(`tree_pid`) USING BTREE,
  INDEX `idx_level`(`level`) USING BTREE,
  INDEX `idx_full_path`(`bid`, `full_path`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '实际投资数据' ROW_FORMAT = Dynamic;

ALTER TABLE `zh_budget_final`
ADD COLUMN `real_dgn_qty1` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '实际投资-设计数量1' AFTER `zb_tp`,
ADD COLUMN `real_dgn_qty2` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '实际投资-设计数量1' AFTER `real_dgn_qty1`,
ADD COLUMN `real_dgn_qty` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '实际投资-设计数量1/设计数量2' AFTER `real_dgn_qty2`,
ADD COLUMN `real_dgn_price` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '实际投资-经济指标' AFTER `real_dgn_qty`,
ADD COLUMN `real_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '实际投资-金额' AFTER `real_dgn_price`;

ALTER TABLE `zh_budget_std`
ADD COLUMN `real_chapter_id` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '实际投资-项目节-id列表(\',\'分隔)' AFTER `zb_bills_id`,
ADD COLUMN `real_template_id` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '实际投资-新建模板-id列表(\',\'分隔)' AFTER `real_chapter_id`,
ADD COLUMN `real_bills_id` varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '实际投资-工程量清单-id列表(\',\'分隔)' AFTER `real_template_id`;

ALTER TABLE `zh_sub_project_info`
ADD COLUMN `proj_scale` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '建设项目规模' AFTER `chief_department`;

ALTER TABLE `zh_budget`
ADD COLUMN `ctrl_optimize_rate` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '控制目标-优化率' AFTER `final_type`,
ADD COLUMN `ctrl_profit` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '控制目标-收益金额' AFTER `ctrl_optimize_rate`,
ADD COLUMN `real_optimize_rate` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '实际投资-优化率' AFTER `ctrl_profit`,
ADD COLUMN `real_profit` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '实际投资-收益金额' AFTER `real_optimize_rate`;

------------------------------------
-- 表数据
------------------------------------
update zh_contract set price = total_price;