update.sql 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138
  1. -- 请按如下分类提交sql!!!
  2. -- Version todo
  3. -- uat todo
  4. -- prod todo
  5. ------------------------------------
  6. -- 表结构
  7. ------------------------------------
  8. ALTER TABLE `zh_sub_project_info`
  9. ADD COLUMN `proj_intro` text NULL COMMENT '项目概述' AFTER `project_id`,
  10. ADD COLUMN `proj_cur_status` text NULL COMMENT '当前状态' AFTER `proj_intro`,
  11. ADD COLUMN `proj_status` varchar(20) NOT NULL DEFAULT '' COMMENT '建设状态' AFTER `proj_level`,
  12. ADD COLUMN `lx_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '立项-金额' AFTER `lx_code`,
  13. ADD COLUMN `lx_memo` text NULL COMMENT '立项-备注' AFTER `lx_tp`,
  14. ADD COLUMN `cb_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '初步-金额' AFTER `cb_code`,
  15. ADD COLUMN `cb_memo` text NULL COMMENT '初步-备注' AFTER `cb_tp`,
  16. ADD COLUMN `sg_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '施工许可批复-金额' AFTER `sg_code`,
  17. ADD COLUMN `sg_memo` text NULL COMMENT '施工许可批复-备注' AFTER `sg_tp`,
  18. ADD COLUMN `sgt_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-部门' AFTER `gcl_quantity`,
  19. ADD COLUMN `sgt_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-日期' AFTER `sgt_department`,
  20. ADD COLUMN `sgt_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '施工图-文号' AFTER `sgt_date`,
  21. ADD COLUMN `sgt_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '施工图-金额' AFTER `sgt_code`,
  22. ADD COLUMN `sgt_memo` text NULL COMMENT '施工图-备注' AFTER `sgt_tp`,
  23. ADD COLUMN `pf_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-部门' AFTER `sgt_memo`,
  24. ADD COLUMN `pf_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-日期' AFTER `pf_department`,
  25. ADD COLUMN `pf_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '财评/造价站批复-文号' AFTER `pf_date`,
  26. ADD COLUMN `pf_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '财评/造价站批复-金额' AFTER `pf_code`,
  27. ADD COLUMN `pf_memo` text NULL COMMENT '财评/造价站批复-备注' AFTER `pf_tp`,
  28. ADD COLUMN `zb_department` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-部门' AFTER `pf_memo`,
  29. ADD COLUMN `zb_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-日期' AFTER `zb_department`,
  30. ADD COLUMN `zb_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '招标完成-文号' AFTER `zb_date`,
  31. ADD COLUMN `zb_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '招标完成-金额' AFTER `zb_code`,
  32. ADD COLUMN `zb_memo` text NULL COMMENT '招标完成-备注' AFTER `zb_tp`;
  33. ALTER TABLE `zh_sub_project_info`
  34. ADD COLUMN `jg_tp` decimal(24, 8) NOT NULL DEFAULT 0 COMMENT '交工-金额' AFTER `jg_level`,
  35. ADD COLUMN `jg_memo` text NULL COMMENT '交工-备注' AFTER `jg_tp`;
  36. CREATE TABLE `zh_sub_project_progress` (
  37. `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  38. `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  39. `tree_id` int(11) NOT NULL COMMENT '节点id',
  40. `tree_pid` int(11) NOT NULL COMMENT '父节点id',
  41. `tree_order` tinyint(4) NOT NULL COMMENT '同级排序',
  42. `tree_level` tinyint(4) NOT NULL COMMENT '层级',
  43. `tree_full_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '层级定位辅助字段parent.full_path.ledger_id',
  44. `tree_is_leaf` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否叶子节点,界面显示辅助字段',
  45. `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '编号/序号',
  46. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '阶段/项目名称',
  47. `edit_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-进度',
  48. `edit_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-日期',
  49. `edit_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '成果编制-部门',
  50. `submit_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-进度',
  51. `submit_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-日期',
  52. `submit_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '报审情况-部门',
  53. `reply_progress` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-进度',
  54. `reply_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-日期',
  55. `reply_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-部门',
  56. `reply_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '批复情况-文号',
  57. `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  58. `add_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人',
  59. `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  60. `update_user_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后编辑人',
  61. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后编辑时间',
  62. PRIMARY KEY (`id`) USING BTREE
  63. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  64. CREATE TABLE `zh_sub_project_file` (
  65. `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  66. `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目id(sub_project.id)',
  67. `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '类型(progress/push/...)',
  68. `rela_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid(zh_sub_project_progress.id/zh_sub_project_push.id/...)',
  69. `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
  70. `fileext` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  71. `filesize` int(11) NOT NULL COMMENT '文件大小',
  72. `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  73. `user_id` int(11) UNSIGNED NOT NULL COMMENT '用户id(zh_project_account.id)',
  74. `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名(缓存)',
  75. `user_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '公司(缓存)',
  76. `user_role` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '角色(缓存)',
  77. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  78. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  79. `is_deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  80. PRIMARY KEY (`id`) USING BTREE
  81. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  82. CREATE TABLE `zh_sub_project_push` (
  83. `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  84. `spid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目id(zh_sub_project.id)',
  85. `push_order` int(11) UNSIGNED NOT NULL COMMENT '序号',
  86. `push_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '日期',
  87. `push_content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '记录内容',
  88. `memo` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '备注',
  89. `user_id` int(11) UNSIGNED NOT NULL COMMENT '创建人id',
  90. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  91. `update_user_id` int(11) UNSIGNED NOT NULL COMMENT '最后修改人id',
  92. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  93. PRIMARY KEY (`id`) USING BTREE
  94. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  95. CREATE TABLE `zh_message_attachment` (
  96. `id` int(11) NOT NULL AUTO_INCREMENT,
  97. `project_id` int(11) DEFAULT NULL COMMENT '项目id',
  98. `mid` int(11) NOT NULL DEFAULT '0' COMMENT '通知id',
  99. `uid` int(11) NOT NULL COMMENT '上传者id',
  100. `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名称',
  101. `fileext` varchar(5) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  102. `filesize` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件大小',
  103. `filepath` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  104. `upload_time` datetime NOT NULL COMMENT '上传时间',
  105. PRIMARY KEY (`id`) USING BTREE,
  106. KEY `idx_mid` (`mid`) USING BTREE
  107. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='通知附件表';
  108. ALTER TABLE `zh_tender_info`
  109. ADD COLUMN `over_range_check` varchar(255) NOT NULL DEFAULT '' AFTER `s_type`;
  110. ALTER TABLE `zh_material_audit`
  111. ADD COLUMN `audit_type` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批类型(1个人,2会签,3或签)',
  112. ADD COLUMN `audit_order` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '审批顺序' AFTER `audit_type`;
  113. ALTER TABLE `zh_material`
  114. ADD COLUMN `final_auditor_str` varchar(50) NOT NULL DEFAULT '' COMMENT '终审人相关';
  115. ALTER TABLE `zh_filing_template`
  116. ADD COLUMN `tips` varchar(1000) NOT NULL DEFAULT '' COMMENT '提示' AFTER `filing_type`;
  117. ALTER TABLE `zh_filing`
  118. ADD COLUMN `tips` varchar(1000) NOT NULL DEFAULT '' COMMENT '提示' AFTER `file_count`;
  119. ALTER TABLE `zh_material_audit` ADD `is_old` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为旧流程(用于管理员修改流程时旧数据保留但不影响新流程)' AFTER `opinion`;
  120. ------------------------------------
  121. -- 表数据
  122. ------------------------------------
  123. UPDATE zh_tender_info ti LEFT JOIN zh_tender t ON ti.tid = t.id SET ti.over_range_check = '{"field":"deal","percent":100,"billsWithPos":"bills"}' WHERE t.measure_type = 2;
  124. UPDATE zh_tender_info ti LEFT JOIN zh_tender t ON ti.tid = t.id SET ti.over_range_check = '{"field":"tz","percent":100,"billsWithPos":"both"}' WHERE t.measure_type = 1;