Calculation/sql/update20230212.sql

ALTER TABLE `zh_project_account` CHANGE `stamp_path` `stamp_path` VARCHAR(5000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户签章oss地址';

ALTER TABLE `zh_revise_price`
ADD COLUMN `rela_lid`  varchar(1000) NOT NULL DEFAULT '' COMMENT '关联台账id(zh_ledger.ledger_id)' AFTER `use_stage_order`;
ALTER TABLE `zh_revise_price`
ADD COLUMN `rela_cid`  varchar(5000) NOT NULL DEFAULT '' COMMENT '关联变更令id(zh_change.cid)' AFTER `rela_lid`;
ALTER TABLE `zh_revise_price`
ADD COLUMN `his_rela_cid`  varchar(5000) NOT NULL DEFAULT '' COMMENT '审批通过后，关联变更令id(zh_change.cid)' AFTER `rela_cid`;

CREATE TABLE `zh_audit_ass` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` int(11) unsigned NOT NULL COMMENT '项目id',
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `user_id` int(11) unsigned NOT NULL COMMENT '主账号id',
  `ass_user_id` int(11) unsigned NOT NULL COMMENT '协审账号id',
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名',
  `company` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位',
  `role` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '角色',
  `ass_ledger_id` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '协审台账id(zh_ledger.ledger_id)',
  PRIMARY KEY (`id`),
  KEY `idx_tid` (`tid`) USING BTREE,
  KEY `idx_tid_user` (`tid`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_stage_audit_ass` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) unsigned NOT NULL COMMENT '项目id',
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `times` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '审批次',
  `user_id` int(11) unsigned NOT NULL COMMENT '主账号id',
  `ass_user_id` int(11) unsigned NOT NULL COMMENT '协审人账号id',
  `ass_ledger_id` varchar(5000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '协审台账id(zh_ledger.ledger_id)',
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  `company` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '单位',
  `role` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '角色',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `confirm` tinyint(4) unsigned NOT NULL DEFAULT '1',
  `locked` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '锁定',
  PRIMARY KEY (`id`),
  KEY `idx_sid_times` (`sid`,`times`) USING BTREE,
  KEY `idx_sid_times_user` (`sid`,`times`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `zh_budget_final`
ADD COLUMN `deal_dgn_qty1`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-合同-数量1' AFTER `dgn_qty`,
ADD COLUMN `deal_dgn_qty2`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-合同-数量2' AFTER `deal_dgn_qty1`,
ADD COLUMN `c_dgn_qty1`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-变更-数量1' AFTER `deal_dgn_qty2`,
ADD COLUMN `c_dgn_qty2`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-变更-数量2' AFTER `c_dgn_qty1`,
ADD COLUMN `final_contract_tp`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-合同金额' AFTER `final_dgn_qty2`,
ADD COLUMN `final_qc_tp`  decimal(24,8) NOT NULL DEFAULT 0 COMMENT '决算-变更金额' AFTER `final_contract_tp`;

ALTER TABLE `zh_tender`
ADD COLUMN `rpt_show_level`  varchar(50) NOT NULL DEFAULT 'last' AFTER `c_plan_list_rule`;

ALTER TABLE `zh_ledger_0`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_1`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_2`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_3`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_4`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_5`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_6`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_7`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_8`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_ledger_9`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;

ALTER TABLE `zh_pos_0`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_1`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_2`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_3`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_4`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_5`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_6`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_7`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_8`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_9`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_10`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_11`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_12`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_13`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_14`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_15`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_16`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_17`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_18`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_pos_19`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;

ALTER TABLE `zh_revise_bills_0`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_1`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_2`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_3`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_4`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_5`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_6`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_7`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_8`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_bills_9`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;

ALTER TABLE `zh_revise_pos_0`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_1`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_2`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_3`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_4`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_5`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_6`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_7`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_8`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_9`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_10`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_11`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_12`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_13`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_14`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_15`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_16`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_17`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_18`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;
ALTER TABLE `zh_revise_pos_19`
ADD COLUMN `ccid`  varchar(36) NULL DEFAULT '' COMMENT 'create_change_id(uuid)' AFTER `crid`;

ALTER TABLE `zh_stage_pay`
ADD COLUMN `porder` tinyint(4) NOT NULL DEFAULT '0' COMMENT '排序' AFTER `sorder`;
UPDATE zh_stage_pay sp LEFT JOIN zh_pay p ON sp.pid = p.id SET sp.porder = IF(ISNull(p.`order`), 0, p.`order`);

ALTER TABLE `zh_change_ledger` ADD `formc` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '是否为变更新增（用于区分台账）' AFTER `ccid`;
ALTER TABLE `zh_change_pos` ADD `formc` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '是否为变更新增（用于区分台账）' AFTER `ccid`;

ALTER TABLE `zh_stage_audit` ADD `is_old` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为旧流程(用于管理员修改流程时旧数据保留但不影响新流程)' AFTER `opinion`;