update20250120.sql 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172
  1. -- 请按如下分类提交sql!!!
  2. -- Version V3.5.47.0141
  3. -- uat 2025-1-16/20
  4. -- prod todo
  5. ------------------------------------
  6. -- 表结构
  7. ------------------------------------
  8. ALTER TABLE `zh_tender`
  9. ADD COLUMN `c_mode` tinyint(1) NULL DEFAULT 0 COMMENT '变更清单模式' AFTER `c_rule_first`;
  10. ALTER TABLE `zh_change_ledger`
  11. ADD COLUMN `features` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '项目特征' AFTER `memo`;
  12. ALTER TABLE `zh_sub_project`
  13. ADD COLUMN `lock_file` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '资料归集,是否锁定' AFTER `filing_template_name`;
  14. ALTER TABLE `zh_filing_template_list`
  15. ADD COLUMN `is_share` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否分享' AFTER `memo`;
  16. CREATE TABLE `zh_phase_pay` (
  17. `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  18. `tid` int(11) NOT NULL COMMENT '标段id',
  19. `phase_order` int(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '期序号',
  20. `phase_date` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '支付年月',
  21. `create_user_id` int(11) NOT NULL COMMENT '创建人id',
  22. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  23. `update_user_id` int(10) UNSIGNED NOT NULL COMMENT '最后修改人id',
  24. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  25. `rela_stage` json NOT NULL COMMENT '关联期信息(json)--[{sid: int, sorder: int}]',
  26. `calc_base` json NOT NULL COMMENT '计算基数',
  27. `calc_base_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '计算基数缓存时间',
  28. `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  29. `audit_times` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批轮次',
  30. `audit_status` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '审批状态',
  31. `audit_max_sort` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '最大审批排序',
  32. `audit_begin_time` timestamp NULL DEFAULT NULL COMMENT '审批开始时间',
  33. `audit_end_time` timestamp NULL DEFAULT NULL COMMENT '审批结束时间',
  34. `final_auditor_str` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '终审缓存信息',
  35. `calc_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期计量=工程计量款',
  36. `pay_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期付款=工程计量款+其他付款项',
  37. `cut_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期扣款=其他扣款项',
  38. `yf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期应付',
  39. `sf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期实付',
  40. `pre_calc_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期计量',
  41. `pre_pay_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期付款',
  42. `pre_cut_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期扣款',
  43. `pre_yf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期应付',
  44. `pre_sf_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期实付',
  45. PRIMARY KEY (`id`) USING BTREE
  46. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  47. CREATE TABLE `zh_phase_pay_audit` (
  48. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  49. `tid` int(11) UNSIGNED NOT NULL COMMENT '标段id',
  50. `phase_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '合同支付期id',
  51. `audit_id` int(11) UNSIGNED NOT NULL COMMENT '流程参与人id(含原报)',
  52. `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-姓名',
  53. `company` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-单位',
  54. `role` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-角色',
  55. `mobile` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参与人-电话',
  56. `audit_times` int(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批次数',
  57. `audit_order` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '预定流程顺序',
  58. `audit_type` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '流程类型',
  59. `active_order` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '实际流程顺序',
  60. `audit_status` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '流程状态',
  61. `audit_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '流程结束时间',
  62. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  63. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  64. `opinion` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '意见',
  65. PRIMARY KEY (`id`) USING BTREE
  66. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  67. CREATE TABLE `zh_phase_pay_detail` (
  68. `id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  69. `uuid` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  70. `tid` int(11) NOT NULL COMMENT '标段id',
  71. `phase_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '合同支付期id',
  72. `create_phase_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '创建期id',
  73. `create_phase_order` int(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT '创建期序号',
  74. `master_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '拼接phase_id-audit_times-audit_sort',
  75. `tree_id` int(11) UNSIGNED NOT NULL COMMENT '树结构-id',
  76. `tree_pid` int(11) NOT NULL COMMENT '树结构-父id',
  77. `tree_order` int(11) UNSIGNED NOT NULL COMMENT '树结构-排序',
  78. `tree_level` int(4) UNSIGNED NOT NULL COMMENT '树结构-层级',
  79. `tree_is_leaf` tinyint(4) UNSIGNED NOT NULL COMMENT '树结构-是否子项',
  80. `tree_full_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '树结构-完整路径',
  81. `audit_times` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '审批轮次',
  82. `audit_sort` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '审批流程',
  83. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  84. `create_user_id` int(11) UNSIGNED NOT NULL COMMENT '创建人id',
  85. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  86. `update_user_id` int(11) UNSIGNED NOT NULL COMMENT '最后修改人id',
  87. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  88. `is_minus` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否扣款',
  89. `is_fixed` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否固定项',
  90. `is_deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  91. `is_pause` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否暂停计量',
  92. `is_gather` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否汇总计算',
  93. `pay_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '支付类型',
  94. `start_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '起扣金额',
  95. `start_expr` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '起扣金额-计算式',
  96. `range_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '扣款限额',
  97. `range_expr` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '扣款限额-计算式',
  98. `expr` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '本期金额-计算式',
  99. `tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '本期金额',
  100. `pre_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止上期金额',
  101. `pre_used` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上期是否已计量',
  102. `pre_finished` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上期是否已计完',
  103. `end_tp` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '截止本期金额',
  104. `postil` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '本期批注',
  105. `dl_value` decimal(24, 8) NOT NULL DEFAULT 0.00000000 COMMENT '计提期限-限制',
  106. `dl_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '计提期限-类型',
  107. PRIMARY KEY (`id`) USING BTREE
  108. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  109. CREATE TABLE `zh_phase_pay_file` (
  110. `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid',
  111. `tid` int(11) UNSIGNED NOT NULL COMMENT '标段id',
  112. `phase_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '合同支付期phase_id(zh_phase_pay.id)',
  113. `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '类型(pay/...)',
  114. `rela_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'uuid(zh_phase_pay_detail.uuid/...)',
  115. `filename` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
  116. `fileext` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件后缀',
  117. `filesize` int(11) NOT NULL COMMENT '文件大小',
  118. `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文件存储路径',
  119. `user_id` int(11) UNSIGNED NOT NULL COMMENT '用户id(zh_project_account.id)',
  120. `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '用户名(缓存)',
  121. `user_company` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '公司(缓存)',
  122. `user_role` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '角色(缓存)',
  123. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  124. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
  125. `is_deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除',
  126. PRIMARY KEY (`id`) USING BTREE
  127. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  128. CREATE TABLE `zh_file_reference_list` (
  129. `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  130. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '名称',
  131. `create_time` int(10) NOT NULL COMMENT '创建时间',
  132. `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '备注',
  133. PRIMARY KEY (`id`) USING BTREE
  134. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '标准清单列表' ROW_FORMAT = Dynamic;
  135. CREATE TABLE `zh_file_reference` (
  136. `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
  137. `list_id` int(8) NOT NULL COMMENT '列表id',
  138. `template_id` int(8) NOT NULL,
  139. `pid` int(10) NOT NULL COMMENT '父id',
  140. `order` tinyint(4) NOT NULL COMMENT '同级排序',
  141. `level` tinyint(2) NOT NULL COMMENT '层级',
  142. `full_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '层级定位辅助字段parent.full_path.ledger_id',
  143. `is_leaf` tinyint(1) NOT NULL COMMENT '是否叶子节点,界面显示辅助字段',
  144. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  145. `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '备注',
  146. `node_type` int(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '节点类别',
  147. PRIMARY KEY (`id`) USING BTREE
  148. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '标段项目节点模板数据表' ROW_FORMAT = Dynamic;
  149. ------------------------------------
  150. -- 表数据
  151. ------------------------------------
  152. INSERT INTO `zh_permission` (`id`, `name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`) VALUES (74, '关联项目', 'enterprise', 'conn', 48, 'add', NULL, 1);
  153. INSERT INTO `zh_permission` (`id`, `name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`) VALUES (75, '停用提示设置', 'project', 'stopmsg', 38, 'stopmsg', NULL, 1);
  154. INSERT INTO `zh_permission` (`id`, `name`, `controller`, `action`, `pid`, `icon_class`, `create_time`, `isshow`) VALUES (76, '参考文件', 'fileReference', 'all', 44, '', 6, 1);
  155. UPDATE `zh_permission` SET `create_time` = 5 WHERE `id` = 57;