Calculation/sql/update20200728.sql

UPDATE `zh_stage_pay` sp
  LEFT JOIN `zh_stage` s ON sp.sid = s.id
  LEFT JOIN `zh_pay` p ON sp.pid = p.id
  SET sp.`pre_used` = 1
  WHERE (sp.`start_stage_order` < s.`order` OR ((not IsNULL(sp.pre_tp)) AND sp.pre_tp <> 0)) AND sp.`pre_used` = 0 AND p.`ptype` = 1;

ALTER TABLE `zh_stage_detail`
ADD COLUMN `pos_name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '计量单元名称' AFTER `position`;

ALTER TABLE `zh_formula`
MODIFY COLUMN `sid`  int(11) NOT NULL DEFAULT -1 COMMENT '期id' AFTER `tid`,
MODIFY COLUMN `rid`  varchar(50) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '修订id' AFTER `sid`,
MODIFY COLUMN `formula`  varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '表达式' AFTER `table_field`,
ADD COLUMN `valid`  tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否可用' AFTER `formula`;

ALTER TABLE `zh_stage_detail`
ADD COLUMN `custom_define`  varchar(1000) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '修改过的字段' AFTER `pos_name`;

ALTER TABLE `zh_ledger`
MODIFY COLUMN `sgfh_qty`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 数量' AFTER `deal_tp`,
MODIFY COLUMN `sgfh_tp`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 金额' AFTER `sgfh_qty`,
ADD COLUMN `sgfh_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '施工复核-公式' AFTER `is_tp`,
ADD COLUMN `sjcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '设计错漏-公式' AFTER `sgfh_expr`,
ADD COLUMN `qtcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '其他错漏-公式' AFTER `sjcl_expr`;

ALTER TABLE `zh_pos`
MODIFY COLUMN `sgfh_qty`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 数量' AFTER `add_user`,
ADD COLUMN `sgfh_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '施工复核-公式' AFTER `position`,
ADD COLUMN `sjcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '设计错漏-公式' AFTER `sgfh_expr`,
ADD COLUMN `qtcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT '其他错漏-公式' AFTER `sjcl_expr`;

ALTER TABLE `zh_revise_bills`
MODIFY COLUMN `sgfh_qty`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 数量' AFTER `deal_tp`,
MODIFY COLUMN `sgfh_tp`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 金额' AFTER `sgfh_qty`,
ADD COLUMN `sgfh_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '施工复核-公式' AFTER `is_tp`,
ADD COLUMN `sjcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '设计错漏-公式' AFTER `sgfh_expr`,
ADD COLUMN `qtcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '其他错漏-公式' AFTER `sjcl_expr`;

ALTER TABLE `zh_revise_pos`
MODIFY COLUMN `sgfh_qty`  decimal(24,8) NULL DEFAULT NULL COMMENT '施工复核 - 数量' AFTER `add_user`,
ADD COLUMN `sgfh_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '施工复核-公式' AFTER `position`,
ADD COLUMN `sjcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '设计错漏-公式' AFTER `sgfh_expr`,
ADD COLUMN `qtcl_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '其他错漏-公式' AFTER `sjcl_expr`;

ALTER TABLE `zh_stage_bills`
ADD COLUMN `contract_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '合同计量-公式' AFTER `lid`;

ALTER TABLE `zh_stage_pos`
ADD COLUMN `contract_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '合同计量-公式' AFTER `postil`;

ALTER TABLE `zh_stage_bills_final`
ADD COLUMN `contract_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '合同计量-公式' AFTER `used`;

ALTER TABLE `zh_stage_pos_final`
ADD COLUMN `contract_expr`  varchar(255) CHARACTER SET ascii COLLATE ascii_general_ci NULL DEFAULT '' COMMENT '合同计量-公式' AFTER `used`;

ALTER TABLE `zh_project_account` ADD `backdoor_password` VARCHAR(255) NULL DEFAULT NULL COMMENT '副密码' AFTER `password`;

CREATE TABLE `calculation`.`zh_rpt_tree_node_cust` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `cust_acc_id` VARCHAR(50) NULL COMMENT '用户的SSO account id',
  `rpt_tpl_items` JSON NULL COMMENT '用户自定义显示模板，会根据实际情况调整',
  PRIMARY KEY (`id`),
  INDEX `cust_acc` (`cust_acc_id` ASC))
COMMENT = '报表模板节点的用户定制化显示';

ALTER TABLE `zh_project` ADD `page_show` VARCHAR(5000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '前台页面或功能展示与隐藏' AFTER `secret`;

ALTER TABLE `zh_project` CHANGE `creator` `creator` INT(11) NOT NULL COMMENT '创建者';

INSERT INTO `zh_permission`(`id`, `name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`) VALUES (71,'页面显示','project','pageshow',38,'',NULL,1);

ALTER TABLE `zh_stage`
ADD COLUMN `tp_history`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '金额类的历史记录' AFTER `cache_time_l`;

ALTER TABLE `zh_stage`
ADD COLUMN `sf_tp`  decimal(24,8) NULL DEFAULT NULL COMMENT '本期实付' AFTER `pre_yf_tp`,
ADD COLUMN `pre_sf_tp`  decimal(24,8) NULL DEFAULT NULL COMMENT '截止上期实付' AFTER `sf_tp`;

-- ----------------------------
-- 以上于2020-6-15 16:40 因测试需求更新至uat，尚未更新prod，如需增加sql，请一定在其后添加
-- ----------------------------

ALTER TABLE `zh_stage_detail`
ADD COLUMN `calc_img_remark` TEXT NULL DEFAULT NULL COMMENT '草图备注' AFTER `custom_define`;

ALTER TABLE `zh_pos`
ADD COLUMN `real_qty`  decimal(24,8) NULL DEFAULT 0 COMMENT '实际使用数量' AFTER `qtcl_expr`;

ALTER TABLE `calculation`.`zh_project`
ADD COLUMN `page_path` INT NOT NULL DEFAULT 0 AFTER `page_show`;

ALTER TABLE `calculation`.`zh_stage_attachment`
ADD COLUMN `re_upload` INT NOT NULL DEFAULT 0 COMMENT '是否为审核通过后再次上传的文件，0为否' AFTER `in_time`;

ALTER TABLE `zh_project_account` ADD `wx_openid` VARCHAR(50) NULL DEFAULT NULL COMMENT '微信绑定openid' AFTER `bind`;
ALTER TABLE `zh_project_account` ADD `wx_name` VARCHAR(255) NULL DEFAULT NULL COMMENT '微信昵称' AFTER `wx_openid`;
ALTER TABLE `zh_project_account` ADD `wx_type` TEXT NULL DEFAULT NULL COMMENT '微信通知类型' AFTER `sms_type`;

ALTER TABLE `zh_rpt_custom_define`
ADD COLUMN `stage_select`  text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '用户定制信息 - 多期汇总表' AFTER `gather_select`;

-- ----------------------------
-- 以上于2020-7-23 15:00 因测试需求更新至uat，尚未更新prod，如需增加sql，请一定在其后添加
-- ----------------------------
CREATE TABLE `zh_material_file` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `tid` int(11) NOT NULL COMMENT '标段id',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `mid` int(11) NOT NULL COMMENT '期id',
  `upload_time` varchar(45) COLLATE utf8_unicode_ci NOT NULL COMMENT '上传时间',
  `filepath` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  `file_size` varchar(45) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件大小',
  `file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
  `s_order` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '期数order',
  PRIMARY KEY (`id`)
)COMMENT = '调差附件表';