Calculation/sql/update20240313.sql

ALTER TABLE `zh_tender`
ADD COLUMN `settle_order` tinyint(4) NULL DEFAULT 0 COMMENT '结算期数（用于结算变更数据更新判断）' AFTER `material_col_show`;

CREATE TABLE `zh_change_settle_list`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `tid` int NULL COMMENT '标段id',
  `cid` varchar(50) NULL COMMENT '变更id',
  `gcl_id` varchar(255) NULL DEFAULT NULL COMMENT '项目节id',
  `mx_id` varchar(255) NULL DEFAULT NULL COMMENT '计量单元id',
  `amount` decimal(30, 8) NULL DEFAULT NULL COMMENT '终审数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '变更已结算清单表（用于修订和重审时生成，防止已结算变更清单被删除）';

CREATE TABLE `zh_notice_again`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int NOT NULL COMMENT '项目id',
  `tid` int NOT NULL COMMENT '标段id',
  `uid` int NOT NULL COMMENT '用户id',
  `sp_type` varchar(30) NOT NULL COMMENT '审批类型',
  `sp_id` int NOT NULL COMMENT '审批类型对应审核表的id',
  `status` tinyint(2) NOT NULL DEFAULT 1 COMMENT '0为待执行，1为执行中，2为已审',
  `table_name` varchar(255) NOT NULL COMMENT '对应的审批表名称',
  `origin_url` json NOT NULL COMMENT 'url信息',
  `sms_type` varchar(10) NOT NULL COMMENT '模版通知类型',
  `template` tinyint(2) NOT NULL COMMENT '对应的微信通知模板id',
  `wx_data` json NOT NULL COMMENT '微信通知的json',
  `times` tinyint(3) NOT NULL DEFAULT 0 COMMENT '重复发送次数',
  `in_time` datetime NULL COMMENT '添加时间',
  `last_time` datetime NULL DEFAULT NULL COMMENT '上一次发送时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '审批信息重新发送表';

ALTER TABLE `zh_project_account`
ADD COLUMN `notice_again` json NULL DEFAULT NULL COMMENT '审批信息重新发送设置';

ALTER TABLE `zh_project`
ADD COLUMN `notice_setting` json NULL DEFAULT NULL COMMENT '重新发送通知设置';

ALTER TABLE `zh_rpt_archive`
ADD COLUMN `business_id` VARCHAR(45) NULL COMMENT '有其他业务情况，统一在这里标识(类uuid)，要结合stage_id来看，如stage_id > 0，则是标段和期，< 0则是其他业务，如 -300 -> 变更令；-301 - 变更方案；-302 - 变更立项 -303 - 变更申请' AFTER `stage_id`,
ADD COLUMN `tender_id` INT(11) NULL AFTER `business_id`,
ADD INDEX `PRJ_STG_BZ` (`prj_id` ASC, `stage_id` ASC, `business_id` ASC);
;

ALTER TABLE `zh_rpt_archive_encryption`
ADD COLUMN `business_id` VARCHAR(45) NULL COMMENT '有其他业务情况，统一在这里标识(类uuid)，要结合stage_id来看，如stage_id > 0，则是标段和期，< 0则是其他业务，如 -300 -> 变更令；-301 - 变更方案；-302 - 变更立项 -303 - 变更申请' AFTER `stage_id`,
ADD COLUMN `tender_id` INT(11) NULL AFTER `business_id`,
ADD INDEX `PRJ_STG_BZ` (`prj_id` ASC, `stage_id` ASC, `business_id` ASC);
;

ALTER TABLE `zh_rpt_archive_encryption`
ADD COLUMN `business_type` varchar(30) NOT NULL DEFAULT 'stage' COMMENT 'stage: 期；budget: 动态决算，payment_safe: 支付审批-安全生产费，change: 变更令，change_plan: 变更方案，change_project: 变更立项，change_apply: 变更申请' AFTER `stage_id`;

ALTER TABLE `zh_rpt_archive`
ADD COLUMN `business_type` varchar(30) NOT NULL DEFAULT 'stage' COMMENT 'stage: 期；budget: 动态决算，payment_safe: 支付审批-安全生产费，change: 变更令，change_plan: 变更方案，change_project: 变更立项，change_apply: 变更申请' AFTER `stage_id`;

ALTER TABLE `zh_role_rpt_rel`
ADD COLUMN `business_type` varchar(30) NOT NULL DEFAULT 'stage' COMMENT 'stage: 期；budget: 动态决算，payment_safe: 支付审批-安全生产费，change: 变更令，change_plan: 变更方案，change_project: 变更立项，change_apply: 变更申请' AFTER `sid`;

ALTER TABLE `zh_role_rpt_rel`
ADD COLUMN `business_id` VARCHAR(45) NULL AFTER `sid`,
ADD INDEX `bz_idx` (`tender_id` ASC, `business_id` ASC);
;

update zh_rpt_archive A
set A.tender_id = (select tid from zh_stage B where B.id = A.stage_id)
where (A.stage_id is not null) && (A.stage_id > 0)
;

update zh_rpt_archive_encryption A
set A.tender_id = (select tid from zh_stage B where B.id = A.stage_id)
where (A.stage_id is not null) && (A.stage_id > 0)
;