Calculation/sql/update20220125.sql

ALTER TABLE `zh_pos_0`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_1`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_2`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_3`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_4`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_5`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_6`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_7`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_8`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_9`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_10`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_11`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_12`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_13`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_14`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_15`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_16`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_17`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_18`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_pos_19`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

Update zh_pos_0 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_1 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_2 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_3 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_4 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_5 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_6 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_7 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_8 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_9 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_10 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_11 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_12 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_13 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_14 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_15 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_16 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_17 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_18 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_pos_19 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;

ALTER TABLE `zh_revise_pos_0`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_1`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_2`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_3`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_4`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_5`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_6`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_7`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_8`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_9`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_10`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_11`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_12`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_13`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_14`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_15`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_16`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_17`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_18`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

ALTER TABLE `zh_revise_pos_19`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

Update zh_revise_pos_0 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_1 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_2 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_3 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_4 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_5 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_6 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_7 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_8 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_9 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_10 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_11 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_12 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_13 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_14 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_15 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_16 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_17 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_18 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;
Update zh_revise_pos_19 p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;

CREATE TABLE `zh_change_revise_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL,
  `cid` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '变更令id',
  `lid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '插入的清单uuid',
  `pid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '插入的计量单元uuid',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '清单名称或计量单元编号',
  `create_time` datetime NOT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '变更新增部位记录表';

ALTER TABLE `zh_change_pos`
MODIFY COLUMN `add_stage`  int(11) NOT NULL COMMENT '新增期id' AFTER `quantity`,
ADD COLUMN `add_stage_order`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '新增期序号' AFTER `add_stage`;

Update zh_change_pos p Left Join zh_stage s ON p.add_stage = s.id Set p.add_stage_order = s.order where p.add_stage_order > 0;

ALTER TABLE `zh_tender`
ADD COLUMN `bills_file`  varchar(255) NOT NULL DEFAULT '' COMMENT '台账-清单文件' AFTER `has_rela`,
ADD COLUMN `pos_file`  varchar(255) NOT NULL DEFAULT '' COMMENT '台账-计量单元文件' AFTER `bills_file`;

CREATE TABLE `zh_material_checklist`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL COMMENT '标段id',
  `mid` int(11) NOT NULL COMMENT '调差期id',
  `b_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '清单编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '清单名称',
  `unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '单位',
  `unit_price` decimal(30, 8) NOT NULL COMMENT '单价',
  `quantity` decimal(30, 8) NOT NULL COMMENT '数量（工程量）',
  `total_price` decimal(30, 8) NOT NULL COMMENT '金额',
  `had_bills` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已添加工料',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '默认清单设置功能';

CREATE TABLE `zh_ledger_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) unsigned NOT NULL COMMENT '项目id',
  `tid` bigint(20) unsigned NOT NULL COMMENT '标段id',
  `rid` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '修订id',
  `rorder` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '修订序号',
  `cid` varchar(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '工程变更id',
  `in_time` datetime NOT NULL,
  `bills_file` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '项目节数据文件',
  `pos_file` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '计量单元数据文件',
  `valid`  tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否有效',
  PRIMARY KEY (`id`),
  KEY `idx_tid_valid_time` (`tid`,`valid`,`in_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `zh_ledger_revise`
ADD COLUMN `his_id`  bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修订后，台账历史数据id' AFTER `content`,
ADD COLUMN `pre_his_id`  bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修订前，台账历史数据id' AFTER `his_id`;

ALTER TABLE `zh_tender`
ADD COLUMN `his_id`  bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '台账历史数据id' AFTER `s2b_dagl_limit`;

ALTER TABLE `zh_stage`
ADD COLUMN `his_id`  bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '历史台账id' AFTER `tp_history`;

ALTER TABLE `zh_stage_pay`
ADD COLUMN `postil`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '本期批注' AFTER `start_stage_order`;

ALTER TABLE `zh_tender` ADD `c_code_rules` TEXT NULL DEFAULT NULL COMMENT '变更立项及申请的编号规则json' AFTER `c_rule_first`;

UPDATE `zh_ledger_revise` SET bills_file = replace(bills_file, '/revise', 'revise') WHERE not ISNULL(bills_file);
UPDATE `zh_ledger_revise` SET pos_file = replace(pos_file, '/revise', 'revise') WHERE not ISNULL(pos_file);