update20220624.sql 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134
  1. ALTER TABLE `zh_budget`
  2. ADD COLUMN `final_time` timestamp NULL DEFAULT NULL COMMENT '生成动态决算时间' AFTER `decimal`,
  3. ADD COLUMN `final_id` int(11) UNSIGNED NOT NULL DEFAULT 0 AFTER `final_time`;
  4. CREATE TABLE `zh_budget_final_list` (
  5. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  6. `bid` int(11) unsigned NOT NULL COMMENT '动态决算id',
  7. `uid` int(11) unsigned NOT NULL COMMENT '生成决算人id',
  8. `u_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '生成决算人-名字(缓存)',
  9. `u_role` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '生成决算人-角色(缓存)',
  10. `u_company` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '生成决算人-单位(缓存)',
  11. `u_mobile` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '生成决算人-电话',
  12. `u_login` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '生成决算人-登录方式',
  13. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  14. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  15. `tender` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '关联标段id('',''分隔)',
  16. `tender_info` text COLLATE utf8_unicode_ci COMMENT '关联标段信息(json)',
  17. `status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0: 取消, 1: 等待, 2: 进行中, 3: 完成',
  18. PRIMARY KEY (`id`),
  19. KEY `idx_bid` (`bid`,`create_time`) USING BTREE
  20. ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  21. CREATE TABLE `zh_budget_final` (
  22. `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  23. `bid` int(11) unsigned NOT NULL COMMENT '概算投资项目id',
  24. `final_id` bigint(11) unsigned NOT NULL COMMENT '决算id',
  25. `tree_id` int(11) unsigned NOT NULL COMMENT '树结构id',
  26. `tree_pid` int(11) NOT NULL COMMENT '树结构父id',
  27. `level` int(11) unsigned NOT NULL COMMENT '层级',
  28. `order` int(11) unsigned NOT NULL COMMENT '同级排序',
  29. `full_path` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '层级定位辅助字段parent.full_path.ledger_id',
  30. `is_leaf` tinyint(1) NOT NULL COMMENT '是否叶子节点,界面显示辅助字段',
  31. `code` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '节点编号',
  32. `name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '名称',
  33. `unit` varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '单位',
  34. `gu_dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算-设计数量1',
  35. `gu_dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算-设计数量1',
  36. `gu_dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '估算-设计数量1/设计数量2',
  37. `gu_dgn_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算-经济指标',
  38. `gu_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '估算-金额',
  39. `gai_dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '概算-设计数量1',
  40. `gai_dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '概算-设计数量1',
  41. `gai_dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '概算-设计数量1/设计数量2',
  42. `gai_dgn_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '概算-经济指标',
  43. `gai_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '概算-金额',
  44. `yu_dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '预算-设计数量1',
  45. `yu_dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '预算-设计数量1',
  46. `yu_dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '预算-设计数量1/设计数量2',
  47. `yu_dgn_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '预算-经济指标',
  48. `yu_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '预算-金额',
  49. `dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-台账-设计数量1',
  50. `dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-台账-设计数量1',
  51. `total_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-台账-经济指标',
  52. `dgn_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-台账-经济指标',
  53. `dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '决算-台账-设计数量1/设计数量2',
  54. `final_dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-设计数量1',
  55. `final_dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-设计数量1',
  56. `final_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-金额',
  57. `final_dgn_price` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '决算-经济指标',
  58. `final_dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '决算-设计数量1/设计数量2',
  59. `grow_dgn_qty1` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '增幅%-设计数量1',
  60. `grow_dgn_qty2` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '增幅%-设计数量1',
  61. `grow_tp` decimal(24,8) NOT NULL DEFAULT '0.00000000' COMMENT '增幅%-金额',
  62. `grow_dgn_qty` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '增幅%-设计数量1/设计数量2',
  63. PRIMARY KEY (`id`),
  64. KEY `idx_fid` (`final_id`) USING BTREE
  65. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  66. ALTER TABLE `zh_material_list_gcl` ADD `old_quantity` DECIMAL(30,8) NULL DEFAULT NULL COMMENT '数量,用于复原数据' AFTER `expr`,
  67. ADD `old_expr` VARCHAR(255) NULL DEFAULT NULL COMMENT '公式,用于复原数据' AFTER `old_quantity`;
  68. ALTER TABLE `zh_material` ADD `exponent_rate` TINYINT(3) NULL DEFAULT '0' COMMENT '材料税税率' AFTER `rate`;
  69. ALTER TABLE `zh_material_list` ADD `is_self` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否为单独添加的工料清单关联' AFTER `is_join`;
  70. ALTER TABLE `zh_material_list_notjoin` CHANGE `mx_id` `mx_id` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '部位明细id';
  71. CREATE TABLE `zh_material_list_self` (
  72. `id` int(11) NOT NULL AUTO_INCREMENT,
  73. `tid` int(11) NOT NULL COMMENT '标段id',
  74. `mid` int(11) NOT NULL COMMENT '调差期id',
  75. `gcl_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '清单id',
  76. `xmj_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '项目节id',
  77. `mx_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT '' COMMENT '部位明细id',
  78. `in_time` datetime NOT NULL COMMENT '添加时间',
  79. PRIMARY KEY (`id`) USING BTREE
  80. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '单独调差的清单表';
  81. ALTER TABLE `zh_advance_pay`
  82. ADD COLUMN `selected` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '报表用,是否选择' AFTER `id`;
  83. ALTER TABLE `zh_stage_change_final`
  84. ADD COLUMN `sorder` int(11) UNSIGNED NOT NULL AFTER `sid`;
  85. ALTER TABLE `zh_change` ADD `order_by` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '清单排序规则,默认按清单编号排(0),自定义排序(1)' AFTER `is_revise`;
  86. ALTER TABLE `zh_change_audit_list` ADD `order` INT NULL DEFAULT NULL COMMENT '排序序号(自定义规则时使用)' AFTER `spamount`;
  87. ALTER TABLE `zh_stage_change`
  88. ADD COLUMN `minus` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否负变更' AFTER `qty`;
  89. ALTER TABLE `zh_stage_change_final`
  90. ADD COLUMN `minus` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否负变更' AFTER `qty`;
  91. CREATE TABLE `zh_stage_stash` (
  92. `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  93. `pid` int(11) unsigned NOT NULL COMMENT '项目id',
  94. `tid` int(11) unsigned NOT NULL COMMENT '标段id',
  95. `sid` int(11) unsigned NOT NULL COMMENT '期id',
  96. `sorder` int(11) unsigned NOT NULL COMMENT '期序号',
  97. `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  98. `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  99. `uid` int(11) unsigned NOT NULL COMMENT '保存人',
  100. `uname` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '保存人姓名',
  101. `filepath` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'oss保存路径',
  102. `valid` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用',
  103. `info` varchar(1000) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '相关信息',
  104. `remark` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
  105. PRIMARY KEY (`id`)
  106. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  107. Update zh_stage_change_final scf Join zh_stage s on scf.sid = s.id Set scf.sorder = s.`order`;
  108. UPDATE `zh_material_list_notjoin` SET `mx_id`= '' WHERE `mx_id` is null;
  109. UPDATE `zh_material_list` SET `mx_id`= '' WHERE `mx_id` is null;
  110. UPDATE `zh_material` SET `exponent_rate`=`rate`;
  111. UPDATE `zh_material_list_gcl` SET `old_quantity`=`quantity`,`old_expr`=`expr`;
  112. Update zh_stage_change sc Left Join zh_change_audit_list cal ON sc.cbid = cal.id Set sc.minus = 1 WHERE sc.minus = 0 and cal.spamount < 0;
  113. Update zh_stage_change_final sc Left Join zh_change_audit_list cal ON sc.cbid = cal.id Set sc.minus = 1 WHERE sc.minus = 0 and cal.spamount < 0;