depart-database-table.js 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date
  7. * @version
  8. */
  9. const mysql = require('mysql');
  10. const fs = require('fs');
  11. const config = process.argv.splice(2);
  12. const mysqlOptions = require(`../config/config.${config}`)({ baseDir: __dirname + '/app', root: __dirname, name: 'calc' }).mysql;
  13. const pool = mysql.createPool(mysqlOptions.client);
  14. const querySql = async function (sql, sqlParam) {
  15. return new Promise(function (resolve, reject) {
  16. pool.getConnection(function (err, conn) {
  17. if (err) {
  18. reject(err);
  19. } else {
  20. conn.query(sql, sqlParam, function (err, rows, fields) {
  21. //释放连接
  22. conn.release();
  23. //传递Promise回调对象
  24. resolve({"err": err, "rows": rows, "fields": fields});
  25. });
  26. }
  27. });
  28. });
  29. };
  30. const ledgerSql = 'CREATE TABLE `zh_ledger_?` (\n' +
  31. ' `id` varchar(100) NOT NULL COMMENT \'自增id\',\n' +
  32. ' `tender_id` int(10) NOT NULL COMMENT \'标段id\',\n' +
  33. ' `ledger_id` int(10) NOT NULL COMMENT \'节点id\',\n' +
  34. ' `ledger_pid` int(10) NOT NULL COMMENT \'父节点id\',\n' +
  35. ' `level` tinyint(4) NOT NULL COMMENT \'层级\',\n' +
  36. ' `order` mediumint(4) NOT NULL DEFAULT \'0\' COMMENT \'同级排序\',\n' +
  37. ' `full_path` varchar(255) DEFAULT \'\' COMMENT \'层级定位辅助字段parent.full_path.ledger_id\',\n' +
  38. ' `is_leaf` tinyint(1) NOT NULL COMMENT \'是否叶子节点,界面显示辅助字段\',\n' +
  39. ' `code` varchar(50) DEFAULT \'\' COMMENT \'节点编号\',\n' +
  40. ' `b_code` varchar(50) DEFAULT NULL,\n' +
  41. ' `name` varchar(255) DEFAULT NULL COMMENT \'名称\',\n' +
  42. ' `unit` varchar(15) DEFAULT \'\' COMMENT \'单位\',\n' +
  43. ' `source` varchar(30) DEFAULT \'\' COMMENT \'添加源\',\n' +
  44. ' `remark` varchar(60) DEFAULT \'\' COMMENT \'备注\',\n' +
  45. ' `quantity` decimal(24,8) DEFAULT NULL COMMENT \'数量\',\n' +
  46. ' `unit_price` decimal(24,8) DEFAULT NULL COMMENT \'单价\',\n' +
  47. ' `total_price` decimal(24,8) DEFAULT NULL COMMENT \'金额\',\n' +
  48. ' `drawing_code` varchar(255) DEFAULT NULL COMMENT \'图册号\',\n' +
  49. ' `memo` varchar(1000) DEFAULT NULL COMMENT \'备注\',\n' +
  50. ' `dgn_qty1` decimal(24,8) DEFAULT NULL COMMENT \'设计数量1\',\n' +
  51. ' `dgn_qty2` decimal(24,8) DEFAULT NULL COMMENT \'设计数量2\',\n' +
  52. ' `deal_qty` decimal(24,8) DEFAULT NULL COMMENT \'签约 - 数量\',\n' +
  53. ' `deal_tp` decimal(24,8) DEFAULT NULL COMMENT \'签约 - 金额\',\n' +
  54. ' `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
  55. ' `sgfh_tp` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 金额\',\n' +
  56. ' `sjcl_qty` decimal(24,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
  57. ' `sjcl_tp` decimal(24,8) DEFAULT NULL COMMENT \'设计错漏 - 金额\',\n' +
  58. ' `qtcl_qty` decimal(24,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
  59. ' `qtcl_tp` decimal(24,8) DEFAULT NULL COMMENT \'其他错漏 - 金额\',\n' +
  60. ' `node_type` int(4) unsigned DEFAULT \'0\' COMMENT \'节点类别\',\n' +
  61. ' `crid` varchar(100) DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
  62. ' `is_tp` tinyint(1) unsigned NOT NULL DEFAULT \'0\' COMMENT \'总额计量\',\n' +
  63. ' `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
  64. ' `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
  65. ' `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏 - 公式\',\n' +
  66. ' `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验-状态\',\n' +
  67. ' `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理-状态\',\n' +
  68. ' `dagl_url` varchar(255) DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
  69. ' `check_calc` tinyint(1) DEFAULT \'0\' COMMENT \'是否检查计算\',\n' +
  70. ' PRIMARY KEY (`id`),\n' +
  71. ' KEY `idx_tender_id` (`tender_id`),\n' +
  72. ' KEY `idx_template_pid` (`ledger_pid`),\n' +
  73. ' KEY `idx_level` (`level`),\n' +
  74. ' KEY `idx_full_path` (`tender_id`,`full_path`) USING BTREE\n' +
  75. ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\'标段--台账分解数据\';';
  76. const posSql = 'CREATE TABLE `zh_pos_?` (\n' +
  77. ' `id` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'主键\',\n' +
  78. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  79. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'台账id\',\n' +
  80. ' `name` varchar(1000) CHARACTER SET utf8 DEFAULT NULL COMMENT \'名称\',\n' +
  81. ' `drawing_code` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'图册号\',\n' +
  82. ' `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
  83. ' `add_stage` int(11) NOT NULL COMMENT \'新增期数\',\n' +
  84. ' `add_times` int(11) NOT NULL COMMENT \'创建于第几次审核\',\n' +
  85. ' `add_user` int(11) NOT NULL COMMENT \'创建人id\',\n' +
  86. ' `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
  87. ' `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
  88. ' `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
  89. ' `crid` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
  90. ' `in_time` datetime DEFAULT NULL COMMENT \'新增时间\',\n' +
  91. ' `porder` int(11) unsigned NOT NULL DEFAULT \'1\',\n' +
  92. ' `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'位置\',\n' +
  93. ' `sgfh_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
  94. ' `sjcl_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
  95. ' `qtcl_expr` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
  96. ' `real_qty` decimal(24,8) DEFAULT \'0.00000000\' COMMENT \'实际使用数量\',\n' +
  97. ' `gxby_status` int(4) DEFAULT \'-1\' COMMENT \'工序报验-状态\',\n' +
  98. ' `dagl_status` int(4) DEFAULT \'-1\' COMMENT \'档案管理-状态\',\n' +
  99. ' `dagl_url` varchar(255) CHARACTER SET utf8 DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
  100. ' PRIMARY KEY (`id`),\n' +
  101. ' KEY `tid` (`tid`) USING BTREE,\n' +
  102. ' KEY `tlid` (`tid`,`lid`) USING BTREE\n' +
  103. ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'部位明细\';';
  104. const reviseBillsSql = 'CREATE TABLE `zh_revise_bills_?` (\n' +
  105. ' `id` varchar(100) NOT NULL COMMENT \'自增id\',\n' +
  106. ' `code` varchar(50) DEFAULT \'\' COMMENT \'节点编号\',\n' +
  107. ' `b_code` varchar(50) DEFAULT NULL,\n' +
  108. ' `name` varchar(255) DEFAULT NULL COMMENT \'名称\',\n' +
  109. ' `unit` varchar(15) DEFAULT \'\' COMMENT \'单位\',\n' +
  110. ' `source` varchar(30) DEFAULT \'\' COMMENT \'添加源\',\n' +
  111. ' `remark` varchar(60) DEFAULT \'\' COMMENT \'备注\',\n' +
  112. ' `tender_id` int(10) NOT NULL COMMENT \'标段id\',\n' +
  113. ' `ledger_id` int(10) NOT NULL COMMENT \'节点id\',\n' +
  114. ' `ledger_pid` int(10) NOT NULL COMMENT \'父节点id\',\n' +
  115. ' `level` mediumint(3) NOT NULL COMMENT \'层级\',\n' +
  116. ' `order` mediumint(5) NOT NULL DEFAULT \'0\' COMMENT \'同级排序\',\n' +
  117. ' `full_path` varchar(100) DEFAULT \'\' COMMENT \'层级定位辅助字段parent.full_path.ledger_id\',\n' +
  118. ' `is_leaf` tinyint(1) DEFAULT NULL COMMENT \'是否叶子节点,界面显示辅助字段\',\n' +
  119. ' `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
  120. ' `unit_price` decimal(30,8) DEFAULT NULL COMMENT \'单价\',\n' +
  121. ' `total_price` decimal(30,8) DEFAULT NULL COMMENT \'金额\',\n' +
  122. ' `drawing_code` varchar(255) DEFAULT NULL COMMENT \'图册号\',\n' +
  123. ' `memo` varchar(1000) DEFAULT NULL COMMENT \'备注\',\n' +
  124. ' `dgn_qty1` decimal(30,8) DEFAULT NULL COMMENT \'设计数量1\',\n' +
  125. ' `dgn_qty2` decimal(30,8) DEFAULT NULL COMMENT \'设计数量2\',\n' +
  126. ' `deal_qty` decimal(30,8) DEFAULT NULL COMMENT \'签约 - 数量\',\n' +
  127. ' `deal_tp` decimal(30,8) DEFAULT NULL COMMENT \'签约 - 金额\',\n' +
  128. ' `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
  129. ' `sgfh_tp` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 金额\',\n' +
  130. ' `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
  131. ' `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
  132. ' `sjcl_tp` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 金额\',\n' +
  133. ' `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
  134. ' `qtcl_tp` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 金额\',\n' +
  135. ' `node_type` int(4) unsigned DEFAULT \'0\' COMMENT \'节点类别\',\n' +
  136. ' `crid` varchar(100) DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
  137. ' `is_tp` tinyint(1) unsigned NOT NULL DEFAULT \'0\' COMMENT \'总额计量\',\n' +
  138. ' `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
  139. ' `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
  140. ' `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验状态\',\n' +
  141. ' `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理状态\',\n' +
  142. ' `dagl_url` varchar(255) DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
  143. ' `check_calc` tinyint(1) DEFAULT \'0\' COMMENT \'是否检查计算\',\n' +
  144. ' PRIMARY KEY (`id`),\n' +
  145. ' KEY `idx_tender_id` (`tender_id`),\n' +
  146. ' KEY `idx_template_pid` (`ledger_pid`),\n' +
  147. ' KEY `idx_level` (`level`),\n' +
  148. ' KEY `idx_full_path` (`full_path`)\n' +
  149. ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\'标段--台账分解数据\';';
  150. const revisePosSql = 'CREATE TABLE `zh_revise_pos_?` (\n' +
  151. ' `id` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'主键\',\n' +
  152. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  153. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'台账id\',\n' +
  154. ' `name` varchar(1000) CHARACTER SET utf8 DEFAULT NULL COMMENT \'名称\',\n' +
  155. ' `drawing_code` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'图册号\',\n' +
  156. ' `quantity` decimal(30,8) DEFAULT NULL COMMENT \'数量\',\n' +
  157. ' `add_stage` int(11) NOT NULL COMMENT \'新增期数\',\n' +
  158. ' `add_times` int(11) NOT NULL COMMENT \'创建于第几次审核\',\n' +
  159. ' `add_user` int(11) NOT NULL COMMENT \'创建人id\',\n' +
  160. ' `sgfh_qty` decimal(24,8) DEFAULT NULL COMMENT \'施工复核 - 数量\',\n' +
  161. ' `sjcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'设计错漏 - 数量\',\n' +
  162. ' `qtcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'其他错漏-公式\',\n' +
  163. ' `qtcl_qty` decimal(30,8) DEFAULT NULL COMMENT \'其他错漏 - 数量\',\n' +
  164. ' `crid` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT \'create_revise_id(uuid)\',\n' +
  165. ' `in_time` datetime DEFAULT NULL COMMENT \'新增时间\',\n' +
  166. ' `porder` int(11) unsigned NOT NULL DEFAULT \'1\',\n' +
  167. ' `position` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT \'位置\',\n' +
  168. ' `sgfh_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'施工复核-公式\',\n' +
  169. ' `sjcl_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'设计错漏-公式\',\n' +
  170. ' `gxby_status` tinyint(4) DEFAULT \'-1\' COMMENT \'工序报验状态\',\n' +
  171. ' `dagl_status` tinyint(4) DEFAULT \'-1\' COMMENT \'档案管理状态\',\n' +
  172. ' `dagl_url` varchar(255) CHARACTER SET utf8 DEFAULT \'\' COMMENT \'档案管理-查看链接\',\n' +
  173. ' `real_qty` decimal(24,8) DEFAULT NULL COMMENT \'现场实际数量\',\n' +
  174. ' PRIMARY KEY (`id`),\n' +
  175. ' KEY `tid` (`tid`) USING BTREE,\n' +
  176. ' KEY `lid` (`tid`,`lid`) USING BTREE\n' +
  177. ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'部位明细\';';
  178. const stageBillsSql = 'CREATE TABLE `zh_stage_bills_?` (\n' +
  179. ' `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
  180. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  181. ' `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
  182. ' `said` int(11) NOT NULL COMMENT \'期审核记录id(stage_audit_id)\',\n' +
  183. ' `times` int(11) NOT NULL COMMENT \'期审核次数\',\n' +
  184. ' `order` int(11) NOT NULL COMMENT \'期审核顺序\',\n' +
  185. ' `contract_qty` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--数量\',\n' +
  186. ' `contract_tp` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--金额\',\n' +
  187. ' `qc_qty` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--数量\',\n' +
  188. ' `qc_tp` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--金额\',\n' +
  189. ' `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT \'批注\',\n' +
  190. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id\',\n' +
  191. ' `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
  192. ' PRIMARY KEY (`id`),\n' +
  193. ' KEY `cur` (`tid`,`sid`,`times`,`order`) USING BTREE\n' +
  194. ') ENGINE=InnoDB AUTO_INCREMENT=62246 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
  195. const stageBillsFinalSql = 'CREATE TABLE `zh_stage_bills_final_?` (\n' +
  196. ' `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
  197. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  198. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id\',\n' +
  199. ' `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
  200. ' `sorder` int(11) NOT NULL DEFAULT \'1\' COMMENT \'第几期\',\n' +
  201. ' `contract_qty` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--数量\',\n' +
  202. ' `contract_tp` decimal(24,8) DEFAULT NULL COMMENT \'合同计量--金额\',\n' +
  203. ' `qc_qty` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--数量\',\n' +
  204. ' `qc_tp` decimal(24,8) DEFAULT NULL COMMENT \'数量变更--金额\',\n' +
  205. ' `used` tinyint(1) unsigned zerofill NOT NULL COMMENT \'是否使用\',\n' +
  206. ' `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
  207. ' PRIMARY KEY (`id`)\n' +
  208. ') ENGINE=InnoDB AUTO_INCREMENT=119279 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=\'期 - 台账 - 最终数据(截止本期)\';';
  209. const stagePosSql = 'CREATE TABLE `zh_stage_pos_?` (\n' +
  210. ' `id` int(11) NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
  211. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  212. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id(uuid)\',\n' +
  213. ' `pid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'部位id(项目节,position_id)(uuid)\',\n' +
  214. ' `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
  215. ' `said` int(11) NOT NULL COMMENT \'期审核记录id(stage_audit_id)\',\n' +
  216. ' `times` int(11) NOT NULL COMMENT \'期审核次数\',\n' +
  217. ' `order` int(11) NOT NULL COMMENT \'期审核顺序\',\n' +
  218. ' `contract_qty` decimal(24,8) DEFAULT \'0.00000000\',\n' +
  219. ' `qc_qty` decimal(24,8) DEFAULT \'0.00000000\',\n' +
  220. ' `postil` varchar(1000) COLLATE utf8_unicode_ci DEFAULT \'\' COMMENT \'批注\',\n' +
  221. ' `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
  222. ' PRIMARY KEY (`id`),\n' +
  223. ' KEY `cur` (`sid`) USING BTREE,\n' +
  224. ' KEY `delete_lid` (`tid`,`lid`) USING BTREE\n' +
  225. ') ENGINE=InnoDB AUTO_INCREMENT=357234 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
  226. const stagePosFinalSql = 'CREATE TABLE `zh_stage_pos_final_?` (\n' +
  227. ' `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',\n' +
  228. ' `tid` int(11) NOT NULL COMMENT \'标段id\',\n' +
  229. ' `lid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'对应台账id(uuid)\',\n' +
  230. ' `pid` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT \'部位id(项目节,position_id)(uuid)\',\n' +
  231. ' `sid` int(11) NOT NULL COMMENT \'期id(stage_id)\',\n' +
  232. ' `sorder` int(11) NOT NULL COMMENT \'第几期\',\n' +
  233. ' `contract_qty` decimal(24,8) DEFAULT NULL,\n' +
  234. ' `qc_qty` decimal(24,8) DEFAULT NULL,\n' +
  235. ' `used` tinyint(1) NOT NULL DEFAULT \'0\' COMMENT \'是否使用\',\n' +
  236. ' `contract_expr` varchar(255) CHARACTER SET ascii DEFAULT \'\' COMMENT \'合同计量-公式\',\n' +
  237. ' PRIMARY KEY (`id`),\n' +
  238. ' KEY `pre` (`tid`,`sorder`) USING BTREE\n' +
  239. ') ENGINE=InnoDB AUTO_INCREMENT=752293 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;';
  240. const departTable = {
  241. ledger: { count: 10, table: 'zh_ledger', relaId: 'tender_id', createSql: ledgerSql },
  242. pos: { count: 20, table: 'zh_pos', relaId: 'tid', createSql: posSql },
  243. revise_bills: { count: 10, table: 'zh_revise_bills', relaId: 'tender_id', createSql: reviseBillsSql },
  244. revise_pos: { count: 20, table: 'zh_revise_pos', relaId: 'tid', createSql: revisePosSql },
  245. stage_bills: { count: 10, table: 'zh_stage_bills', relaId: 'tid', createSql: stageBillsSql },
  246. stage_pos: { count: 20, table: 'zh_stage_pos', relaId: 'tid', createSql: stagePosSql },
  247. stage_bills_final: { count: 10, table: 'zh_stage_bills_final', relaId: 'tid', createSql: stageBillsFinalSql },
  248. stage_pos_final: { count: 20, table: 'zh_stage_pos_final', relaId: 'tid', createSql: stagePosFinalSql },
  249. };
  250. // 2020/1/12各表行数统计
  251. // 表名 行数 分表数 * 为保持尽量长的时间,不需要调整分表,台账按10分表
  252. // zh_ledger 832,368 10 * 计量单元数据量大约是台账的3倍,按台账的2倍分表
  253. // zh_pos 2,498,325 20
  254. // zh_revise_bills 294,272 10
  255. // zh_revise_pos 1,009,742 20
  256. // zh_stage_bills 239,720 10 * 期(台账、计量单元)数据,同台账数据保持一致,便于查询
  257. // zh_stage_pos 861,836 20
  258. // zh_stage_bills_final 444,011 10
  259. // zh_stage_pos_final 2,366,449 20
  260. // zh_stage_detail 38,904 -
  261. // zh_stage_change 27,232 -
  262. // 合计 120
  263. // 其他:共110个项目,2013个标段(项目最少1个标段,最多303个标段)。
  264. const generateAllCreateSql = function () {
  265. for (const prop in departTable) {
  266. const table = departTable[prop];
  267. const sql = [];
  268. for (let i = 0; i < table.count; i++) {
  269. sql.push(table.createSql.replace('?', i+''), '');
  270. }
  271. fs.writeFileSync(__dirname + '/' + prop + '.sql', sql.join('\n'));
  272. }
  273. };
  274. // generateAllCreateSql();
  275. // const tenders = querySql('Select * From zh_tender');
  276. // console.log(tenders.length);
  277. //
  278. const log = [];
  279. const depart = async function (info) {
  280. for (let i = 0; i < info.count; i++) {
  281. await querySql('Delete From ' + info.table + '_' + i);
  282. const sql = 'Insert Into ' + info.table + '_' + i +
  283. ' Select * From ' + info.table +
  284. ' Where ' + info.relaId + ' % ' + info.count + ' = ' + i;
  285. console.log(sql);
  286. log.push(sql);
  287. await querySql(sql);
  288. }
  289. };
  290. const departAll = async function () {
  291. for (const prop in departTable) {
  292. await depart(departTable[prop]);
  293. }
  294. pool.end();
  295. };
  296. departAll();
  297. fs.writeFileSync(__dirname + '/depart.log', log.join('\n'));