Calculation/sql/update20211015.sql

ALTER TABLE `zh_tender`
ADD COLUMN `has_rela`  tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否有关联台账' AFTER `had_map`;

CREATE TABLE `zh_stage_rela` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `sorder` tinyint(4) unsigned NOT NULL COMMENT '期序号',
  `rela_tid` int(11) unsigned NOT NULL COMMENT '关联标段id',
  `rela_tname` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '关联标段-名称',
  `rela_sid` int(11) unsigned NOT NULL COMMENT '关联期id',
  `rela_sorder` tinyint(4) unsigned NOT NULL COMMENT '关联期序号',
  `add_time` datetime NOT NULL COMMENT '添加时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `total_price` decimal(28,6) DEFAULT NULL,
  `cache_tp` text CHARACTER SET utf8 COMMENT '缓存金额(当前标段单价计算)',
  `cache_org_tp` text CHARACTER SET utf8 COMMENT '缓存金额(原关联标段单价计算)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='计量期-关联台账（含缓存数据）';

CREATE TABLE `zh_stage_rela_bills` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `sorder` tinyint(4) unsigned NOT NULL COMMENT '期序号',
  `rela_tid` int(11) unsigned NOT NULL COMMENT '关联标段id',
  `rela_sid` int(11) unsigned NOT NULL COMMENT '关联期id',
  `rela_sorder` tinyint(4) unsigned NOT NULL COMMENT '关联期序号',
  `lid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '台账id',
  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT '合同计量-数量',
  `contract_tp` decimal(24,8) DEFAULT NULL COMMENT '合同计量-金额',
  `contract_expr` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '合同计量-公式',
  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT '数量变更-数量',
  `qc_tp` decimal(24,8) DEFAULT NULL COMMENT '数量变更-金额',
  `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '本期批注',
  `unit_price` decimal(24,8) DEFAULT NULL COMMENT '单价',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_stage_rela_pos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `sorder` tinyint(4) unsigned NOT NULL COMMENT '期序号',
  `rela_tid` int(11) unsigned NOT NULL COMMENT '关联标段id',
  `rela_sid` int(11) unsigned NOT NULL COMMENT '关联期id',
  `rela_sorder` tinyint(4) unsigned NOT NULL COMMENT '关联期序号',
  `lid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '台账id',
  `pid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '计量单元id',
  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT '合同计量-数量',
  `contract_expr` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '合同计量-公式',
  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT '数量变更-数量',
  `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '本期批注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_stage_rela_bills_final` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `sorder` tinyint(4) unsigned NOT NULL COMMENT '期序号',
  `rela_tid` int(11) unsigned NOT NULL COMMENT '关联标段id',
  `rela_sid` int(11) unsigned NOT NULL COMMENT '关联期id',
  `rela_sorder` tinyint(4) unsigned NOT NULL COMMENT '关联期序号',
  `lid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '台账id',
  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT '合同计量-数量',
  `contract_tp` decimal(24,8) DEFAULT NULL COMMENT '合同计量-金额',
  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT '数量变更-数量',
  `qc_tp` decimal(24,8) DEFAULT NULL COMMENT '数量变更-金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_stage_rela_pos_final` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  `sid` int(11) unsigned NOT NULL COMMENT '期id',
  `sorder` tinyint(4) unsigned NOT NULL COMMENT '期序号',
  `rela_tid` int(11) unsigned NOT NULL COMMENT '关联标段id',
  `rela_sid` int(11) unsigned NOT NULL COMMENT '关联期id',
  `rela_sorder` tinyint(4) unsigned NOT NULL COMMENT '关联期序号',
  `lid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '台账id',
  `pid` varchar(36) CHARACTER SET ascii NOT NULL COMMENT '计量单元id',
  `contract_qty` decimal(24,8) DEFAULT NULL COMMENT '合同计量-数量',
  `qc_qty` decimal(24,8) DEFAULT NULL COMMENT '数量变更-数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `zh_stage_rela_im` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `sorder` tinyint(4) NOT NULL,
  `rela_tid` int(11) NOT NULL,
  `rela_sid` int(11) NOT NULL,
  `rela_sorder` tinyint(4) NOT NULL,
  `lid` varchar(50) CHARACTER SET ascii NOT NULL,
  `pid` varchar(50) CHARACTER SET ascii NOT NULL,
  `im_id` int(11) NOT NULL,
  `code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unit` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unit_price` decimal(24,8) DEFAULT NULL,
  `peg` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `drawing_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bw` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `xm` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `position` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jldy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dwgc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fbgc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fxgc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `doc_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `im_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calc_memo` text COLLATE utf8_unicode_ci,
  `calc_img_remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calc_img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bgl_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bgl_drawing_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jl` decimal(24,8) DEFAULT NULL,
  `contract_jl` decimal(24,8) DEFAULT NULL,
  `qc_jl` decimal(24,8) DEFAULT NULL,
  `pre_jl` decimal(24,8) DEFAULT NULL,
  `pre_contract_jl` decimal(24,8) DEFAULT NULL,
  `pre_qc_jl` decimal(24,8) DEFAULT NULL,
  `end_jl` decimal(24,8) DEFAULT NULL,
  `end_contract_jl` decimal(24,8) DEFAULT NULL,
  `end_qc_jl` decimal(24,8) DEFAULT NULL,
  `tp` decimal(24,8) DEFAULT NULL,
  `contract_tp` decimal(24,8) DEFAULT NULL,
  `qc_tp` decimal(24,8) DEFAULT NULL,
  `pre_tp` decimal(24,8) DEFAULT NULL,
  `pre_contract_tp` decimal(24,8) DEFAULT NULL,
  `pre_qc_tp` decimal(24,8) DEFAULT NULL,
  `end_tp` decimal(24,8) DEFAULT NULL,
  `end_contract_tp` decimal(24,8) DEFAULT NULL,
  `end_qc_tp` decimal(24,8) DEFAULT NULL,
  `quantity` decimal(24,8) DEFAULT NULL,
  `total_price` decimal(24,8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='关联台账，缓存，中间计量';

CREATE TABLE `zh_stage_rela_im_bills` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `sorder` tinyint(4) NOT NULL,
  `rela_tid` int(11) NOT NULL,
  `rela_sid` int(11) NOT NULL,
  `rela_sorder` tinyint(4) NOT NULL,
  `bid` varchar(50) CHARACTER SET ascii DEFAULT NULL,
  `im_id` int(11) NOT NULL,
  `b_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unit` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `unit_price` decimal(24,8) DEFAULT NULL,
  `im_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jl` decimal(24,8) DEFAULT NULL,
  `contract_jl` decimal(24,8) DEFAULT NULL,
  `qc_jl` decimal(24,8) DEFAULT NULL,
  `pre_jl` decimal(24,8) DEFAULT NULL,
  `pre_contract_jl` decimal(24,8) DEFAULT NULL,
  `pre_qc_jl` decimal(24,8) DEFAULT NULL,
  `end_jl` decimal(24,8) DEFAULT NULL,
  `end_contract_jl` decimal(24,8) DEFAULT NULL,
  `end_qc_jl` decimal(24,8) DEFAULT NULL,
  `tp` decimal(24,8) DEFAULT NULL,
  `contract_tp` decimal(24,8) DEFAULT NULL,
  `qc_tp` decimal(24,8) DEFAULT NULL,
  `pre_tp` decimal(24,8) DEFAULT NULL,
  `pre_contract_tp` decimal(24,8) DEFAULT NULL,
  `pre_qc_tp` decimal(24,8) DEFAULT NULL,
  `end_tp` decimal(24,8) DEFAULT NULL,
  `end_contract_tp` decimal(24,8) DEFAULT NULL,
  `end_qc_tp` decimal(24,8) DEFAULT NULL,
  `quantity` decimal(24,8) DEFAULT NULL,
  `total_price` decimal(24,8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='关联台账，缓存，中间计量-清单';

ALTER TABLE `zh_rpt_archive`
CHANGE COLUMN `content` `content` JSON NULL DEFAULT NULL ;

CREATE TABLE `zh_s2b_z_proj` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT '项目id',
  `xmName` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '项目发改全称',
  `xmCode` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '项目发改赋码',
  `opCode` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '来源编码',
  `base_data` text COLLATE utf8_unicode_ci COMMENT '用来存定制接口所需参数',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- 表的结构 `zh_s2b_z_tender`
--

CREATE TABLE `zh_s2b_z_tender` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL COMMENT '标段id',
  `base_data` text COLLATE utf8_unicode_ci COMMENT 'json,一些需要的数据',
  PRIMARY KEY (`id`),
  KEY `tid` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='浙江项目标段表';

ALTER TABLE `zh_project` ADD `customType` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '定制项目通知类型参数' AFTER `secret`;

ALTER TABLE `calculation`.`zh_tender_info`
ADD COLUMN `fun_rela` varchar(255) CHARACTER SET utf8 NULL COMMENT '功能设置' AFTER `ledger_check`;

ALTER TABLE `zh_project` ADD `data_collect` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '决策大屏是否显示及对应大屏编号' AFTER `sjs_rela`;

CREATE TABLE `zh_datacollect_audit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT '项目id',
  `groupid` int(11) DEFAULT NULL COMMENT '用户组id',
  `uid` int(11) DEFAULT NULL COMMENT '用户id',
  `create_time` datetime NOT NULL COMMENT '添加时间',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='决策大屏用户查看权限表';

ALTER TABLE `zh_material_bills` ADD `origin` VARCHAR(255) NULL DEFAULT NULL COMMENT '来源地' AFTER `pre_tp`;
ALTER TABLE `zh_material_bills_history` ADD `origin` VARCHAR(255) NULL DEFAULT NULL COMMENT '来源地' AFTER `pre_tp`;

ALTER TABLE `zh_material_bills` ADD `order` INT NULL DEFAULT NULL COMMENT '排序' AFTER `mid`;
-- 按id排序赋值
UPDATE `zh_material_bills` SET `order`=`id`

-- 收方单表
CREATE TABLE `zh_stage_shoufang`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL COMMENT '标段id',
  `order` tinyint(4) NOT NULL COMMENT '期数',
  `sid` int(11) NOT NULL COMMENT '期id',
  `lid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '台账uuid',
  `pid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '计量单元uuid',
  `qrcode` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '二维码存放地址',
  `extra_upload` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为审核通过后再次生成的，0为否',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '收方单数据';

-- 收方单附件表
CREATE TABLE `zh_stage_shoufang_attachment`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL COMMENT '标段id',
  `sid` int(11) NOT NULL COMMENT '期id',
  `sfid` int(11) NOT NULL COMMENT '收方单id',
  `filename` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名称',
  `fileext` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  `filesize` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件大小',
  `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  `extra_upload` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为审核通过后再次上传的，0为否',
  `in_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  INDEX `sfid`(`sfid`),
  INDEX `sid`(`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '收方单附件表';

ALTER TABLE `zh_stage_detail`
ADD COLUMN `im_type`  tinyint(1) UNSIGNED NULL COMMENT '中间计量模式' AFTER `calc_img_remark`;

ALTER TABLE `zh_project`
ADD COLUMN `map_json` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '地图信息及默认坐标' AFTER `data_collect`;

CREATE TABLE `zh_s2b_z_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL COMMENT '项目id',
  `tid` int(11) NOT NULL COMMENT '标段id',
  `sid` int(11) NOT NULL COMMENT '期id',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '请求url',
  `post_data` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '请求数据',
  `result_data` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '推送结果',
  `post_time` datetime NULL DEFAULT NULL COMMENT '请求时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '浙江项目日志表';