analysis_excel.js 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date
  7. * @version
  8. */
  9. const _ = require('lodash');
  10. const aeUtils = {
  11. toNumber: function (value) {
  12. let num = _.toNumber(value);
  13. return _.isNaN(num) ? null : num;
  14. },
  15. checkColHeader: function (row, colHeaderMatch) {
  16. const colsDef = {};
  17. for (const iCol in row) {
  18. const text = row[iCol];
  19. for (const head in colHeaderMatch) {
  20. const match = colHeaderMatch[head];
  21. if (match.indexOf(text) >= 0) {
  22. colsDef[head] = iCol;
  23. }
  24. }
  25. }
  26. return colsDef;
  27. }
  28. };
  29. class ImportBaseTree {
  30. /**
  31. * 构造函数
  32. * @param {Array} tempData - 清单模板数据
  33. */
  34. constructor (tempData, ctx) {
  35. this.ctx = ctx;
  36. // 常量
  37. this.splitChar = '-';
  38. // 索引
  39. // 以code为索引
  40. this.codeNodes = {};
  41. this.items = [];
  42. this.roots = [];
  43. this.pos = [];
  44. this.tempData = [];
  45. // 缓存
  46. this.finalNode = null;
  47. this.finalPrecision = null;
  48. this.finalXmjNode = null;
  49. this.keyNodeId = 1;
  50. this._loadTemplateTree(tempData);
  51. }
  52. /**
  53. * 加载 清单模板
  54. * @param {Array} data - 模板数据
  55. * @private
  56. */
  57. _loadTemplateTree(data) {
  58. for (const node of data) {
  59. node.ledger_id = node.template_id;
  60. node.ledger_pid = node.pid;
  61. node.id = this.ctx.app.uuid.v4();
  62. delete node.pid;
  63. if (node.code) {
  64. this.codeNodes[node.code] = node;
  65. }
  66. this.items.push(node);
  67. if (node.ledger_pid === -1) {
  68. this.roots.push(node);
  69. }
  70. if (node.ledger_id >= this.keyNodeId) {
  71. this.keyNodeId = node.ledger_id + 1;
  72. }
  73. this.tempData.push(node);
  74. }
  75. for (const node of this.items) {
  76. node.tender_id = this.ctx.tender.id;
  77. node.children = this.items.filter(function (i) {
  78. return i.ledger_pid === node.ledger_id;
  79. });
  80. }
  81. }
  82. /**
  83. * 根据 编号、名称 查找模板节点
  84. * @param {Object} node - 要查找的节点
  85. * @returns {*}
  86. */
  87. findTempData(node) {
  88. return this.tempData.find(function (td) {
  89. return td.code === node.code && td.name === node.name;
  90. });
  91. }
  92. /**
  93. * 根据 编号 查找 父项项目节
  94. * @param {String} code - 子项编号
  95. * @returns {*}
  96. */
  97. findXmjParent(code) {
  98. const codePath = code.split(this.splitChar);
  99. if (codePath.length > 1) {
  100. codePath.splice(codePath.length - 1, 1);
  101. return this.codeNodes[codePath.join(this.splitChar)];
  102. }
  103. }
  104. /**
  105. * 添加 树节点 并完善该节点的树结构
  106. * @param {Object} node - 添加节点
  107. * @param {Object} parent - 父项
  108. * @returns {*}
  109. */
  110. addNodeWithParent(node, parent) {
  111. node.id = this.ctx.app.uuid.v4();
  112. node.ledger_id = this.keyNodeId;
  113. this.keyNodeId += 1;
  114. node.ledger_pid = parent ? parent.ledger_id : -1;
  115. node.level = parent ? parent.level + 1 : 1;
  116. node.order = parent ? parent.children.length + 1 : this.roots.length + 1;
  117. node.full_path = parent ? parent.full_path + '-' + node.ledger_id : '' + node.ledger_id;
  118. if (parent) {
  119. parent.children.push(node);
  120. } else {
  121. this.roots.push(node);
  122. }
  123. this.items.push(node);
  124. this.codeNodes[node.code] = node;
  125. this.defineCacheData(node);
  126. return node;
  127. }
  128. /**
  129. * 定义缓存节点(添加工程量清单、部位明细需使用缓存定位)
  130. * @param {Object} node - 当前添加的节点
  131. */
  132. defineCacheData(node) {
  133. this.finalNode = node;
  134. this.finalPrecision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
  135. if (node.code) {
  136. this.finalXmjNode = node;
  137. }
  138. }
  139. /**
  140. * 添加 项目节
  141. * @param {Object} node - 项目节
  142. * @returns {*}
  143. */
  144. addXmjNode(node) {
  145. node.id = this.ctx.app.uuid.v4();
  146. node.tender_id = this.ctx.tender.id;
  147. node.children = [];
  148. if (node.code.split(this.splitChar).length > 1) {
  149. const temp = this.findTempData(node);
  150. if (temp) {
  151. this.defineCacheData(temp);
  152. return temp;
  153. } else {
  154. const parent = this.findXmjParent(node.code);
  155. return this.addNodeWithParent(node, parent);
  156. }
  157. } else {
  158. const n = this.codeNodes[node.code];
  159. if (!n) {
  160. return this.addNodeWithParent(node, null);
  161. } else {
  162. this.defineCacheData(n);
  163. return n;
  164. }
  165. }
  166. }
  167. /**
  168. * 添加 工程量清单
  169. * @param {Object} node - 工程量清单
  170. */
  171. addGclNode(node) {
  172. node.id = this.ctx.app.uuid.v4();
  173. node.tender_id = this.ctx.tender.id;
  174. node.pos = [];
  175. if (this.finalXmjNode) {
  176. return this.addNodeWithParent(node, this.finalXmjNode);
  177. }
  178. }
  179. /**
  180. * 添加 部位明细
  181. * @param {object} pos - 部位明细
  182. * @returns {*}
  183. */
  184. addPos (pos){
  185. if (this.finalNode && this.finalNode.pos) {
  186. pos.id = this.ctx.app.uuid.v4();
  187. pos.lid = this.finalNode.id;
  188. pos.tid = this.ctx.tender.id;
  189. pos.add_stage = 0;
  190. pos.add_times = 0;
  191. pos.in_time = new Date();
  192. pos.porder = this.finalNode.pos.length + 1;
  193. pos.add_user = this.ctx.session.sessionUser.accountId;
  194. this.finalNode.pos.push(pos);
  195. this.pos.push(pos);
  196. pos.sgfh_qty = this.ctx.helper.round(pos.sgfh_qty, this.finalPrecision.value);
  197. pos.quantity = pos.sgfh_qty;
  198. return pos;
  199. }
  200. }
  201. /**
  202. * 第一部分的子节点,顺序重排
  203. */
  204. resortFirstPartChildren () {
  205. const splitChar = this.splitChar;
  206. const firstPart = this.roots[0];
  207. firstPart.children.sort(function (a, b) {
  208. if (a.code === '') {
  209. return 1;
  210. } else if (b.code === '') {
  211. return -1;
  212. }
  213. const codeA = a.code.split(splitChar);
  214. const numA = _.toNumber(codeA[codeA.length -1]);
  215. const codeB = b.code.split(splitChar);
  216. const numB = _.toNumber(codeB[codeB.length -1]);
  217. return numA - numB;
  218. });
  219. }
  220. calculateLeafWithPos () {
  221. for (const node of this.items) {
  222. if (node.children && node.children.length > 0) { continue; }
  223. if (!node.pos || node.pos.length === 0) { continue; }
  224. // node.sgfh_qty = this.ctx.helper.sum(_.map(node.pos, 'sgfh_qty'));
  225. // if (node.sgfh_qty && node.unit_price) {
  226. // node.sgfh_tp = this.ctx.helper.round(this.ctx.helper.mul(node.sgfh_qty, node.unit_price),
  227. // this.ctx.tender.info.decimal.tp);
  228. // } else {
  229. // node.sgfh_tp = null;
  230. // }
  231. // node.quantity = this.ctx.helper.sum(_.map(node.pos, 'quantity'));
  232. // if (node.quantity && node.unit_price) {
  233. // node.total_price = this.ctx.helper.round(this.ctx.helper.mul(node.quantity, node.unit_price),
  234. // this.ctx.tender.info.decimal.tp);
  235. // } else {
  236. // node.total_price = null;
  237. // }
  238. node.sgfh_qty = this.ctx.helper.sum(_.map(node.pos, 'sgfh_qty'));
  239. if (node.sgfh_qty && node.unit_price) {
  240. node.sgfh_tp = this.ctx.helper.mul(node.sgfh_qty, node.unit_price, this.ctx.tender.info.decimal.tp);
  241. } else {
  242. node.sgfh_tp = null;
  243. }
  244. node.quantity = node.sgfh_qty;
  245. node.total_price = node.sgfh_tp;
  246. }
  247. }
  248. }
  249. class AnalysisExcelTree {
  250. /**
  251. * 构造函数
  252. */
  253. constructor(ctx) {
  254. this.ctx = ctx;
  255. this.colsDef = null;
  256. this.colHeaderMatch = {
  257. code: ['项目节编号', '预算项目节'],
  258. b_code: ['清单子目号', '清单编号', '子目号'],
  259. pos: ['部位明细'],
  260. name: ['名称'],
  261. unit: ['单位'],
  262. sgfh_qty: ['清单数量'], // 施工图复核数量
  263. dgn_qty1: ['设计数量1'],
  264. dgn_qty2: ['设计数量2'],
  265. unit_price: ['单价'],
  266. drawing_code: ['图号'],
  267. memo: ['备注'],
  268. };
  269. }
  270. /**
  271. * 读取项目节节点
  272. * @param {Array} row - excel行数据
  273. * @returns {*}
  274. * @private
  275. */
  276. _loadXmjNode(row) {
  277. const node = {};
  278. node.code = this.ctx.helper.replaceReturn(row[this.colsDef.code]);
  279. node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
  280. node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
  281. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
  282. node.sgfh_qty = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.sgfh_qty]), precision.value);
  283. node.dgn_qty1 = aeUtils.toNumber(row[this.colsDef.dgn_qty1]);
  284. node.dgn_qty2 = aeUtils.toNumber(row[this.colsDef.dgn_qty2]);
  285. node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
  286. node.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
  287. node.memo = this.ctx.helper.replaceReturn(row[this.colsDef.memo]);
  288. // if (node.sgfh_qty && node.unit_price) {
  289. // node.sgfh_tp = this.ctx.helper.round(this.ctx.helper.mul(node.sgfh_qty, node.unit_price), this.ctx.tender.info.decimal.tp);
  290. // } else {
  291. // node.sgfh_tp = null;
  292. // }
  293. // node.quantity = node.sgfh_qty;
  294. // if (node.quantity && node.unit_price) {
  295. // node.total_price = this.ctx.helper.round(this.ctx.helper.mul(node.quantity, node.unit_price), this.ctx.tender.info.decimal.tp);
  296. // } else {
  297. // node.total_price = null;
  298. // }
  299. if (node.sgfh_qty && node.unit_price) {
  300. node.sgfh_tp = this.ctx.helper.mul(node.sgfh_qty, node.unit_price, this.ctx.tender.info.decimal.tp);
  301. } else {
  302. node.sgfh_tp = null;
  303. }
  304. node.quantity = node.sgfh_qty;
  305. node.total_price = node.sgfh_tp;
  306. return this.cacheTree.addXmjNode(node);
  307. }
  308. /**
  309. * 读取工程量清单数据
  310. * @param {Array} row - excel行数据
  311. * @returns {*}
  312. * @private
  313. */
  314. _loadGclNode(row) {
  315. const node = {};
  316. node.b_code = this.ctx.helper.replaceReturn(row[this.colsDef.b_code]);
  317. node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
  318. node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
  319. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
  320. node.sgfh_qty = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.sgfh_qty]), precision.value);
  321. node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
  322. node.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
  323. node.memo = this.ctx.helper.replaceReturn(row[this.colsDef.memo]);
  324. // if (node.sgfh_qty && node.unit_price) {
  325. // node.sgfh_tp = this.ctx.helper.round(this.ctx.helper.mul(node.sgfh_qty, node.unit_price), this.ctx.tender.info.decimal.tp);
  326. // } else {
  327. // node.sgfh_tp = null;
  328. // }
  329. // node.quantity = node.sgfh_qty;
  330. // if (node.quantity && node.unit_price) {
  331. // node.total_price = this.ctx.helper.round(this.ctx.helper.mul(node.quantity, node.unit_price), this.ctx.tender.info.decimal.tp);
  332. // } else {
  333. // node.total_price = null;
  334. // }
  335. if (node.sgfh_qty && node.unit_price) {
  336. node.sgfh_tp = this.ctx.helper.mul(node.sgfh_qty, node.unit_price, this.ctx.tender.info.decimal.tp);
  337. } else {
  338. node.sgfh_tp = null;
  339. }
  340. node.quantity = node.sgfh_qty;
  341. node.total_price = node.sgfh_tp;
  342. return this.cacheTree.addGclNode(node);
  343. }
  344. /**
  345. * 读取部位明细数据
  346. * @param {Array} row - excel行数据
  347. * @returns {*}
  348. * @private
  349. */
  350. _loadPos(row) {
  351. const pos = {};
  352. pos.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
  353. pos.sgfh_qty = aeUtils.toNumber(row[this.colsDef.sgfh_qty]);
  354. pos.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
  355. return this.cacheTree.addPos(pos);
  356. }
  357. /**
  358. * 读取数据行
  359. * @param {Array} row - excel数据行
  360. * @param {Number} index - 行索引号
  361. */
  362. loadRowData(row, index) {
  363. let result;
  364. // 含code识别为项目节,含posCode识别为部位明细,其他识别为工程量清单
  365. if (row[this.colsDef.code]) {
  366. // 第三部分(编号为'3')及之后的数据不读取
  367. if (row[this.colsDef.code] === '3') {
  368. this.loadEnd = true;
  369. return;
  370. }
  371. result = this._loadXmjNode(row)
  372. } else if (row[this.colsDef.pos]) {
  373. result = this._loadPos(row);
  374. } else {
  375. result = this._loadGclNode(row);
  376. }
  377. // 读取失败则写入错误数据 todo 返回前端告知用户?
  378. if (!result) {
  379. this.errorData.push({
  380. serialNo: index,
  381. data: row,
  382. });
  383. }
  384. }
  385. /**
  386. * 读取表头并检查
  387. * @param {Number} row - Excel数据行
  388. */
  389. checkColHeader(row) {
  390. const colsDef = aeUtils.checkColHeader(row, this.colHeaderMatch);
  391. if (colsDef.code && colsDef.b_code && colsDef.pos) {
  392. this.colsDef = colsDef;
  393. }
  394. }
  395. /**
  396. * 将excel清单 平面数据 解析为 树结构数据
  397. * @param {object} sheet - excel清单数据
  398. * @param {Array} tempData - 新建项目使用的清单模板
  399. * @returns {ImportBaseTree}
  400. */
  401. analysisData(sheet, tempData) {
  402. this.colsDef = null;
  403. this.cacheTree = new ImportBaseTree(tempData, this.ctx);
  404. this.errorData = [];
  405. this.loadEnd = false;
  406. for (const iRow in sheet.rows) {
  407. const row = sheet.rows[iRow];
  408. if (this.colsDef && !this.loadEnd) {
  409. this.loadRowData(row, iRow);
  410. } else {
  411. this.checkColHeader(row);
  412. }
  413. }
  414. this.cacheTree.resortFirstPartChildren();
  415. this.cacheTree.calculateLeafWithPos();
  416. return this.cacheTree;
  417. }
  418. }
  419. class ImportGclBaseTree {
  420. /**
  421. * 构造函数
  422. * @param {Array} tempData - 清单模板数据
  423. */
  424. constructor (ctx, parent, maxId, defaultData) {
  425. this.ctx = ctx;
  426. this.parent = parent;
  427. this.defaultData = defaultData;
  428. // 常量
  429. this.splitChar = '-';
  430. // 索引
  431. // 以code为索引
  432. this.codeNodes = {};
  433. this.items = [];
  434. // 缓存
  435. this.keyNodeId = maxId ? maxId + 1 : 1;
  436. this.blankParent = null;
  437. }
  438. /**
  439. * 根据 编号 查找 父项项目节
  440. * @param {String} code - 子项编号
  441. * @returns {*}
  442. */
  443. findParent(code) {
  444. const codePath = code.split(this.splitChar);
  445. if (codePath.length > 1) {
  446. codePath.splice(codePath.length - 1, 1);
  447. return this.codeNodes[codePath.join(this.splitChar)];
  448. }
  449. }
  450. /**
  451. * 添加 树节点 并完善该节点的树结构
  452. * @param {Object} node - 添加节点
  453. * @param {Object} parent - 父项
  454. * @returns {*}
  455. */
  456. addNodeWithParent(node, parent) {
  457. parent = parent ? parent : this.parent;
  458. if (!parent.children) parent.children = [];
  459. node.id = this.ctx.app.uuid.v4();
  460. node.tender_id = this.ctx.tender.id;
  461. node.ledger_id = this.keyNodeId;
  462. this.keyNodeId += 1;
  463. node.ledger_pid = parent.ledger_id;
  464. node.level = parent.level + 1;
  465. node.order = parent.children.length + 1;
  466. node.full_path = parent.full_path + '-' + node.ledger_id;
  467. parent.children.push(node);
  468. node.children = [];
  469. if (this.defaultData) _.assignIn(node, this.defaultData);
  470. this.items.push(node);
  471. if (!_.isNil(node.b_code) && node.b_code !== '') {
  472. this.codeNodes[node.b_code] = node;
  473. } else {
  474. this.blankParent = node;
  475. }
  476. return node;
  477. }
  478. /**
  479. * 添加 项目节
  480. * @param {Object} node - 项目节
  481. * @returns {*}
  482. */
  483. addNode(node) {
  484. if (_.isNil(node.b_code) || node.b_code === '') {
  485. return this.addNodeWithParent(node, null);
  486. } else if (node.b_code.split(this.splitChar).length > 1) {
  487. const parent = this.findParent(node.b_code);
  488. return this.addNodeWithParent(node, parent ? parent : this.blankParent);
  489. } else {
  490. return this.addNodeWithParent(node, this.blankParent);
  491. }
  492. }
  493. }
  494. class AnalysisGclExcelTree {
  495. /**
  496. * 构造函数
  497. */
  498. constructor(ctx) {
  499. this.ctx = ctx;
  500. this.colsDef = null;
  501. this.colHeaderMatch = {
  502. b_code: ['编号', '清单编号', '子目号'],
  503. name: ['名称'],
  504. unit: ['单位'],
  505. sgfh_qty: ['清单数量'], // 施工图复核数量
  506. unit_price: ['单价'],
  507. };
  508. }
  509. /**
  510. * 读取表头并检查
  511. * @param {Number} row - Excel数据行
  512. */
  513. checkColHeader(row) {
  514. const colsDef = aeUtils.checkColHeader(row, this.colHeaderMatch);
  515. if (colsDef.b_code) {
  516. this.colsDef = colsDef;
  517. }
  518. }
  519. loadRowData(row) {
  520. const node = {};
  521. node.b_code = this.ctx.helper.replaceReturn(row[this.colsDef.b_code]);
  522. node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
  523. if ((_.isNil(node.b_code) || node.b_code === '') && (_.isNil(node.name) || node.name === '')) return node;
  524. node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
  525. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
  526. node.sgfh_qty = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.sgfh_qty]), precision.value);
  527. node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
  528. if (node.sgfh_qty && node.unit_price) {
  529. node.sgfh_tp = this.ctx.helper.mul(node.sgfh_qty, node.unit_price, this.ctx.tender.info.decimal.tp);
  530. } else {
  531. node.sgfh_tp = null;
  532. }
  533. node.quantity = node.sgfh_qty;
  534. node.total_price = node.sgfh_tp;
  535. return this.cacheTree.addNode(node);
  536. }
  537. /**
  538. * 将excel清单 平面数据 解析为 树结构数据
  539. * @param {object} sheet - excel清单数据
  540. * @param {Array} parentId - 导入至的节点id
  541. * @returns {ImportBaseTree}
  542. */
  543. analysisData(sheet, parent, maxId, defaultData) {
  544. try {
  545. this.colsDef = null;
  546. this.cacheTree = new ImportGclBaseTree(this.ctx, parent, maxId, defaultData);
  547. this.errorData = [];
  548. this.loadEnd = false;
  549. for (const iRow in sheet.rows) {
  550. const row = sheet.rows[iRow];
  551. if (this.colsDef && !this.loadEnd) {
  552. const result = this.loadRowData(row);
  553. // 读取失败则写入错误数据 todo 返回前端告知用户?
  554. if (!result) {
  555. this.errorData.push({
  556. serialNo: iRow,
  557. data: row,
  558. });
  559. }
  560. } else {
  561. this.checkColHeader(row);
  562. }
  563. }
  564. return this.cacheTree;
  565. } catch(err) {
  566. this.ctx.helper.log(err);
  567. }
  568. }
  569. }
  570. module.exports = { AnalysisExcelTree, AnalysisGclExcelTree };