stage_bills.js 30 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580
  1. 'use strict';
  2. /**
  3. * 期计量 - 部位明细计量
  4. *
  5. * @author Mai
  6. * @date 2018/12/8
  7. * @version
  8. */
  9. const calcFields = ['contract_qty', 'qc_qty'];
  10. const auditConst = require('../const/audit');
  11. const timesLen = auditConst.stage.timesLen;
  12. const SumLoad = require('../lib/sum_load');
  13. module.exports = app => {
  14. class StageBills extends app.BaseService {
  15. /**
  16. * 构造函数
  17. *
  18. * @param {Object} ctx - egg全局变量
  19. * @return {void}
  20. */
  21. constructor(ctx) {
  22. super(ctx);
  23. this.depart = 10;
  24. this.tableName = 'stage_bills';
  25. }
  26. /**
  27. * 查询期计量最后审核人数据
  28. * @param {Number} tid - 标段id
  29. * @param {Number} sid - 期id
  30. * @param {Number|Array} lid - 台账节点id(可以为空)
  31. * @return {Promise<*>}
  32. */
  33. // async getLastestStageData(tid, sid, lid) {
  34. // let lidSql = '',
  35. // result;
  36. // if (lid) {
  37. // if (lid instanceof Array) {
  38. // lidSql = lid.length > 0 ? ' And lid in (' + this.ctx.helper.getInArrStrSqlFilter(lid) + ')' : '';
  39. // } else {
  40. // lidSql = ' And lid in (' + this.db.escape(lid) + ')';
  41. // }
  42. // }
  43. // const sql = 'SELECT Bills.* FROM ' + this.departTableName(tid) + ' As Bills ' +
  44. // ' INNER JOIN ( ' +
  45. // ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.departTableName(tid) +
  46. // ' WHERE tid = ? And sid = ?' + lidSql +
  47. // ' GROUP BY `lid`' +
  48. // ' ) As MaxFilter ' +
  49. // ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
  50. // const sqlParam = [tid, sid];
  51. // if (!lid) {
  52. // return await this.db.query(sql, sqlParam);
  53. // } else if (lid instanceof Array) {
  54. // return await this.db.query(sql, sqlParam);
  55. // }
  56. // return await this.db.queryOne(sql, sqlParam);
  57. //
  58. // }
  59. /**
  60. * 查询 某期 某轮审批 某人数据
  61. * @param {Number} tid - 标段id
  62. * @param {Number} sid - 期id
  63. * @param {Number} times - 第几轮
  64. * @param {Number} order - 流程
  65. * @param {Number|Array} lid - 台账节点id(可以为空)
  66. * @return {Promise<*>}
  67. */
  68. // async getAuditorStageData(tid, sid, times, order, lid) {
  69. // const lidSql = lid ? ' And Bills.lid in (?)' : '';
  70. // const sql = 'SELECT Bills.* FROM ' + this.departTableName(tid) + ' As Bills ' +
  71. // ' INNER JOIN ( ' +
  72. // ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `tid`, `sid` From ' + this.departTableName(tid) +
  73. // ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) And tid = ? And sid = ?' + lidSql +
  74. // ' GROUP BY `lid`' +
  75. // ' ) As MaxFilter ' +
  76. // ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid' +
  77. // ' AND Bills.sid = MaxFilter.sid';
  78. // const sqlParam = [times, times, order, tid, sid];
  79. // if (!lid) {
  80. // return await this.db.query(sql, sqlParam);
  81. // } else if (lid instanceof Array) {
  82. // sqlParam.push(lid.join(', '));
  83. // return await this.db.query(sql, sqlParam);
  84. // }
  85. // sqlParam.push(lid);
  86. // return await this.db.queryOne(sql, sqlParam);
  87. //
  88. // }
  89. _getFilterSql(where, asTable = '') {
  90. let whereSql = '';
  91. if (!where) return whereSql;
  92. if (where.lid) {
  93. if (where.lid instanceof Array) {
  94. whereSql += ' And ' + asTable + 'lid in (' + this.ctx.helper.getInArrStrSqlFilter(where.lid) + ')';
  95. } else if (typeof where.lid === "string") {
  96. whereSql += ' And ' + asTable + 'lid = ' + this.db.escape(where.lid);
  97. }
  98. }
  99. return whereSql;
  100. }
  101. async getLastestStageData2(tid, sid, lid) {
  102. const filterSql = lid ? this._getFilterSql({lid}) : '';
  103. const sql = 'Select * From ' + this.departTableName(tid) +
  104. ' Where tid = ? and sid = ? ' + filterSql;
  105. const result = await this.db.query(sql, [tid, sid]);
  106. const stageBills = this.ctx.helper.filterLastestData2(result, ['lid']);
  107. if (!lid || lid instanceof Array) return stageBills;
  108. return stageBills[0];
  109. }
  110. async getAuditorStageData2(tid, sid, times, order, lid) {
  111. const filterSql = lid ? this._getFilterSql({lid}) : '';
  112. const sql = 'Select * From ' + this.departTableName(tid) +
  113. ' Where tid = ? and sid = ? And (`times` < ? OR (`times` = ? AND `order` <= ?)) ' + filterSql;
  114. const result = await this.db.query(sql, [tid, sid, times, times, order]);
  115. const stageBills = this.ctx.helper.filterLastestData(result, ['lid']);
  116. if (!lid || lid instanceof Array) return stageBills;
  117. return stageBills[0];
  118. }
  119. async updateStageBills4CheckCancel(sid, newTimes, newOrder, oldTimes, oldOrder, transaction) {
  120. const oldSBLists = await this.getAllDataByCondition({ where: { sid, times: oldTimes, order: oldOrder } });
  121. const newSBLists = await this.getAllDataByCondition({ where: { sid, times: newTimes, order: newOrder } });
  122. const delidList = [];
  123. for (const o of oldSBLists) {
  124. const newSBInfo = this._.find(newSBLists, { lid: o.lid });
  125. if (newSBInfo) {
  126. // 删除
  127. delidList.push(newSBInfo.id);
  128. }
  129. }
  130. if (delidList.length > 0) await transaction.delete(this.tableName, { id: delidList });
  131. if (oldSBLists.length > 0) {
  132. await transaction.update(this.tableName, {
  133. times: newTimes,
  134. order: newOrder,
  135. }, { where: { id: this._.map(oldSBLists, 'id') } });
  136. }
  137. }
  138. async getStageUsedBills(tid, sid) {
  139. const sql = 'SELECT Bills.lid, ((not IsNull(Bills.contract_qty) and Bills.contract_qty <> 0) or (not IsNull(Bills.contract_tp) and Bills.contract_tp <> 0) or (not IsNull(Bills.qc_qty) and Bills.qc_qty <> 0) or (not IsNull(Bills.qc_tp) and Bills.qc_tp <> 0)) As used' +
  140. ' FROM ' + this.tableName + ' As Bills ' +
  141. ' INNER JOIN ( ' +
  142. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  143. ' WHERE tid = ? And sid = ?' +
  144. ' GROUP BY `lid`' +
  145. ' ) As MaxFilter ' +
  146. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
  147. const sqlParam = [tid, sid];
  148. const stageBills = await this.db.query(sql, sqlParam);
  149. return this._.map(this._.filter(stageBills, 'used'), 'lid');
  150. }
  151. async getStageBills(tid, sid, lid) {
  152. const sql = 'SELECT Stage.*, Ledger.unit_price FROM ?? As Stage, ?? As Ledger ' +
  153. ' Where Stage.tid = ?, Stage.sid = ?, Stage.lid = ?, Stage.lid = Ledger.id ' +
  154. ' Order Stage.time DESC, Stage.order DESC ';
  155. const sqlParam = [this.tableName, this.ctx.service.ledger.tableName];
  156. sqlParam.push(this.db.escape(tid));
  157. sqlParam.push(this.db.escape(sid));
  158. sqlParam.push(this.db.escape(lid));
  159. return await this.db.queryOne(sql, sqlParam);
  160. }
  161. _calcStageBillsData(data, orgData, ledgerData) {
  162. const info = this.ctx.tender.info;
  163. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);
  164. if (data.contract_qty !== undefined) {
  165. data.contract_qty = this.round(data.contract_qty, precision.value);
  166. data.contract_tp = this.ctx.helper.mul(data.contract_qty, ledgerData.unit_price, info.decimal.tp);
  167. }
  168. if (data.qc_qty !== undefined) {
  169. data.qc_qty = this.round(data.qc_qty, precision.value);
  170. data.qc_tp = this.ctx.helper.mul(data.qc_qty, ledgerData.unit_price, info.decimal.tp);
  171. }
  172. if (data.positive_qc_qty !== undefined) {
  173. data.positive_qc_qty = this.round(data.positive_qc_qty, precision.value);
  174. data.positive_qc_tp = this.ctx.helper.mul(data.positive_qc_qty, ledgerData.unit_price, info.decimal.tp);
  175. }
  176. if (data.negative_qc_qty !== undefined) {
  177. data.negative_qc_qty = this.round(data.negative_qc_qty, precision.value);
  178. data.negative_qc_tp = this.ctx.helper.mul(data.negative_qc_qty, ledgerData.unit_price, info.decimal.tp);
  179. }
  180. if (ledgerData.is_tp && data.contract_tp !== undefined) {
  181. data.contract_tp = this.ctx.helper.round(data.contract_tp, info.decimal.tp);
  182. }
  183. }
  184. async _insertStageBillsData(transaction, insertData, orgData, ledgerData) {
  185. const info = this.ctx.tender.info;
  186. const d = {
  187. tid: this.ctx.tender.id,
  188. lid: ledgerData.id,
  189. sid: this.ctx.stage.id,
  190. times: this.ctx.stage.curTimes,
  191. order: this.ctx.stage.curOrder,
  192. said: this.ctx.session.sessionUser.accountId,
  193. };
  194. if (orgData) {
  195. d.contract_qty = orgData.contract_qty;
  196. d.contract_tp = orgData.contract_tp;
  197. d.qc_qty = orgData.qc_qty;
  198. d.qc_tp = orgData.qc_tp;
  199. d.qc_minus_qty = orgData.qc_minus_qty;
  200. d.positive_qc_qty = orgData.positive_qc_qty;
  201. d.negative_qc_qty = orgData.negative_qc_qty;
  202. d.postil = orgData.postil;
  203. }
  204. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);
  205. if (insertData.contract_qty !== undefined) {
  206. d.contract_qty = this.round(insertData.contract_qty, precision.value);
  207. d.contract_tp = this.ctx.helper.mul(d.contract_qty, ledgerData.unit_price, info.decimal.tp);
  208. }
  209. if (insertData.contract_expr !== undefined) d.contract_expr = insertData.contract_expr;
  210. if (insertData.qc_qty !== undefined) {
  211. d.qc_qty = this.round(insertData.qc_qty, precision.value);
  212. d.qc_tp = this.ctx.helper.mul(d.qc_qty, ledgerData.unit_price, info.decimal.tp);
  213. }
  214. if (insertData.positive_qc_qty !== undefined) {
  215. d.positive_qc_qty = this.round(insertData.positive_qc_qty, precision.value);
  216. d.positive_qc_tp = this.ctx.helper.mul(d.positive_qc_qty, ledgerData.unit_price, info.decimal.tp);
  217. }
  218. if (insertData.negative_qc_qty !== undefined) {
  219. d.negative_qc_qty = this.round(insertData.negative_qc_qty, precision.value);
  220. d.negative_qc_tp = this.ctx.helper.mul(d.negative_qc_qty, ledgerData.unit_price, info.decimal.tp);
  221. }
  222. if (insertData.qc_minus_qty !== undefined) {
  223. d.qc_minus_qty = insertData.qc_minus_qty ? this.round(insertData.qc_minus_qty, precision.value) : 0;
  224. }
  225. if (insertData.postil) {
  226. d.postil = insertData.postil;
  227. }
  228. if (ledgerData.is_tp && insertData.contract_tp !== undefined) {
  229. d.contract_tp = this.ctx.helper.round(insertData.contract_tp, info.decimal.tp);
  230. }
  231. await transaction.insert(this.tableName, d);
  232. }
  233. /**
  234. * 前端提交数据
  235. * @param {Object|Array} data - 提交的数据
  236. * @return {Promise<void>}
  237. */
  238. async updateStageData(data) {
  239. const datas = data instanceof Array ? data : [data];
  240. const transaction = await this.db.beginTransaction();
  241. try {
  242. for (const d of datas) {
  243. const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, d.lid);
  244. const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(d.lid);
  245. if (!stageBills || stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
  246. await this._insertStageBillsData(transaction, d, stageBills, ledgerBills);
  247. } else {
  248. d.id = stageBills.id;
  249. d.said = this.ctx.session.sessionUser.accountId;
  250. this._calcStageBillsData(d, stageBills, ledgerBills);
  251. await transaction.update(this.tableName, d);
  252. }
  253. }
  254. await transaction.commit();
  255. } catch (err) {
  256. await transaction.rollback();
  257. throw err;
  258. }
  259. return await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, this._.map(datas, 'lid'));
  260. }
  261. /**
  262. * 根据
  263. * @param transaction
  264. * @param ledgerBills
  265. * @param stageBills
  266. * @param data
  267. * @return {Promise<void>}
  268. * @private
  269. */
  270. async updateStageBillsQty(transaction, ledgerBills, stageBills, data) {
  271. if (stageBills) {
  272. if ((data.contract_qty === undefined || stageBills.contract_qty !== data.contract_qty) ||
  273. (data.qc_qty === undefined || stageBills.qc_qty !== data.qc_qty)) {
  274. if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
  275. data.id = stageBills.id;
  276. data.said = this.ctx.session.sessionUser.accountId;
  277. this._calcStageBillsData(data, stageBills, ledgerBills);
  278. await transaction.update(this.tableName, data);
  279. } else {
  280. await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
  281. }
  282. }
  283. } else {
  284. await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
  285. }
  286. }
  287. /**
  288. * 重算 本期计量 数量 (根据部位明细)
  289. * @param {Number} tid - 标段id
  290. * @param {Number} id - 需要计算的节点的id
  291. * @param {Number} lid - 台账id
  292. * @param {Object} transaction - 操作所属事务
  293. * @return {Promise<void>}
  294. */
  295. async calc(tid, sid, lid, transaction) {
  296. const info = this.ctx.tender.info;
  297. const stageBills = await this.getLastestStageData2(tid, sid, lid);
  298. const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(lid);
  299. if (!ledgerBills) {
  300. throw '提交数据错误';
  301. }
  302. const posGather = await this.ctx.service.stagePos.getPosGather(tid, sid, lid, transaction);
  303. if (!posGather) { return; }
  304. posGather.qc_minus_qty = posGather.qc_minus_qty || 0;
  305. const precision = this.ctx.helper.findPrecision(info.precision, ledgerBills.unit);
  306. // 计算
  307. if (posGather.contract_qty !== undefined) {
  308. posGather.contract_qty = this.round(posGather.contract_qty, precision.value);
  309. posGather.contract_tp = this.ctx.helper.mul(posGather.contract_qty, ledgerBills.unit_price, info.decimal.tp);
  310. }
  311. if (posGather.qc_qty !== undefined) {
  312. posGather.qc_qty = this.round(posGather.qc_qty, precision.value);
  313. posGather.qc_tp = this.ctx.helper.mul(posGather.qc_qty, ledgerBills.unit_price, info.decimal.tp);
  314. }
  315. if (posGather.positive_qc_qty !== undefined) {
  316. posGather.positive_qc_qty = this.round(posGather.positive_qc_qty, precision.value);
  317. posGather.positive_qc_tp = this.ctx.helper.mul(posGather.positive_qc_qty, ledgerBills.unit_price, info.decimal.tp);
  318. }
  319. if (posGather.negative_qc_qty !== undefined) {
  320. posGather.negative_qc_qty = this.round(posGather.negative_qc_qty, precision.value);
  321. posGather.negative_qc_tp = this.ctx.helper.mul(posGather.negative_qc_qty, ledgerBills.unit_price, info.decimal.tp);
  322. }
  323. if (stageBills) {
  324. if (stageBills.contract_qty === posGather.contract_qty && stageBills.qc_qty === posGather.qc_qty && stageBills.qc_minus_qty === posGather.qc_minus_qty) {
  325. return;
  326. }
  327. if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
  328. posGather.id = stageBills.id;
  329. await transaction.update(this.tableName, posGather);
  330. } else {
  331. await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
  332. }
  333. } else {
  334. await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
  335. }
  336. }
  337. async updateStageBillsCalcType(data) {
  338. const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, data.id);
  339. const le = await this.ctx.service.ledgerExtra.getDataById(data.id);
  340. const updateData = { contract_qty: null, contract_tp: null };
  341. const transaction = await this.db.beginTransaction();
  342. try {
  343. if (le) {
  344. await transaction.update(this.ctx.service.ledgerExtra.tableName, data);
  345. } else {
  346. data.tid = this.ctx.tender.id;
  347. await transaction.insert(this.ctx.service.ledgerExtra.tableName, data);
  348. }
  349. if (stageBills) {
  350. if (stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
  351. const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(data.id);
  352. await this._insertStageBillsData(transaction, updateData, stageBills, ledgerBills);
  353. } else {
  354. updateData.id = stageBills.id;
  355. updateData.said = this.ctx.session.sessionUser.accountId;
  356. await transaction.update(this.tableName, updateData);
  357. }
  358. }
  359. await transaction.commit();
  360. } catch (err) {
  361. await transaction.rollback();
  362. throw err;
  363. }
  364. const bills = await this.ctx.service.ledger.getCompleteDataById(data.id);
  365. const curStageData = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, [data.id]);
  366. return { bills: [bills], curStageData };
  367. }
  368. async getSumTotalPrice(stage) {
  369. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp FROM ' + this.departTableName(stage.tid) + ' As Bills ' +
  370. ' INNER JOIN ( ' +
  371. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid`, `sid` From ' + this.departTableName(stage.tid) +
  372. ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) AND `sid` = ?' +
  373. ' GROUP BY `lid`' +
  374. ' ) As MaxFilter ' +
  375. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid And Bills.sid = MaxFilter.sid';
  376. const sqlParam = [stage.curTimes, stage.curTimes, stage.curOrder, stage.id];
  377. const result = await this.db.queryOne(sql, sqlParam);
  378. return result;
  379. }
  380. async getLatestSumTotalPrice(tid, sid) {
  381. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp FROM ' + this.departTableName(tid) + ' As Bills ' +
  382. ' INNER JOIN ( ' +
  383. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid`, `sid` From ' + this.departTableName(tid) +
  384. ' WHERE `sid` = ?' +
  385. ' GROUP BY `lid`' +
  386. ' ) As MaxFilter ' +
  387. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid And Bills.sid = MaxFilter.sid';
  388. const sqlParam = [sid];
  389. const result = await this.db.queryOne(sql, sqlParam);
  390. return result;
  391. }
  392. async getSumTotalPriceFilter(stage, operate, filter) {
  393. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp' +
  394. ' FROM ' + this.departTableName(stage.tid) + ' As Bills ' +
  395. ' INNER JOIN ( ' +
  396. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid` From ' + this.departTableName(stage.tid) +
  397. ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) AND `sid` = ?' +
  398. ' GROUP BY `lid`' +
  399. ' ) As MaxFilter ' +
  400. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid ' +
  401. ' INNER JOIN ' + this.ctx.service.ledger.departTableName(stage.tid) + ' As Ledger ON Bills.lid = Ledger.id' +
  402. ' WHERE Bills.sid = ? And Ledger.b_code ' + operate + ' ?';
  403. const sqlParam = [stage.times, stage.curTimes, stage.curOrder, stage.id, stage.id, filter];
  404. const result = await this.db.queryOne(sql, sqlParam);
  405. return result;
  406. }
  407. async getSumTotalPriceGcl(stage, regText) {
  408. if (regText) {
  409. return await this.getSumTotalPriceFilter(stage, 'REGEXP', regText);
  410. }
  411. return await this.getSumTotalPriceFilter(stage, '<>', this.db.escape(''));
  412. }
  413. async getSumTotalPriceNotGcl(stage) {
  414. return await this.getSumTotalPriceFilter(stage, '=', this.db.escape(''));
  415. }
  416. /**
  417. * 多期清单数据整合 (材料调差调用)
  418. * @param {Number} tid - 标段id
  419. * @param {String} stage_id_list - 期id列表
  420. * @return {Promise<void>}
  421. */
  422. async getStagesData(tid, stage_id_list) {
  423. const whereSql = this.ctx.helper.whereSql({tid: tid, sid: stage_id_list.split(',')});
  424. const sql = 'SELECT Bills.lid, Bills.tid, Bills.sid,' +
  425. ' Sum(Bills.contract_qty) As contract_qty, Sum(Bills.contract_tp) As contract_tp,' +
  426. ' Sum(Bills.qc_minus_qty) As qc_minus_qty,' +
  427. ' Sum(Bills.qc_qty) As qc_qty, Sum(Bills.qc_tp) As qc_tp' +
  428. ' FROM ' + this.tableName + ' As Bills ' +
  429. ' INNER JOIN ( ' +
  430. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `tid`, `lid`, `sid` From ' + this.tableName + whereSql + ' GROUP BY `lid`, `sid`' +
  431. ' ) As MaxFilter ' +
  432. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.tid = MaxFilter.tid And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
  433. ' GROUP BY Bills.lid';
  434. const result = await this.db.query(sql);
  435. return result;
  436. }
  437. /**
  438. * 获取多期(合同和数量变更相加)计量-小计(材料调差调用)
  439. * @param {Number} tid - 标段id
  440. * @param {String} stage_id_list - 期id列表
  441. * @param {String} lid - 台账id
  442. * @param {String} xid - 项目节id
  443. * @return {Promise<void>}
  444. */
  445. async getGatherQtyByMaterial(tid, stage_id_list, lid) {
  446. stage_id_list = stage_id_list !== null ? stage_id_list.split(',') : [];
  447. const qtys = {
  448. gather_qty: 0,
  449. contract_qty: 0,
  450. qc_qty: 0,
  451. qc_minus_qty: 0,
  452. };
  453. for (const sid of stage_id_list) {
  454. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
  455. ' INNER JOIN ( ' +
  456. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  457. ' WHERE tid = ? And sid = ?' +
  458. ' GROUP BY `lid`' +
  459. ' ) As MaxFilter ' +
  460. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
  461. ' WHERE Bills.lid = ?';
  462. const sqlParam = [tid, sid, lid];
  463. const result = await this.db.queryOne(sql, sqlParam);
  464. if (result) {
  465. qtys.contract_qty = this.ctx.helper.add(qtys.contract_qty, result.contract_qty);
  466. qtys.qc_qty = this.ctx.helper.add(qtys.qc_qty, result.qc_qty);
  467. qtys.qc_minus_qty = this.ctx.helper.add(qtys.qc_minus_qty, result.qc_minus_qty);
  468. qtys.gather_qty = this.ctx.helper.add(qtys.gather_qty, this.ctx.helper.add(result.contract_qty, result.qc_qty));
  469. }
  470. }
  471. return this.ctx.helper.resetQtys(qtys);
  472. }
  473. async getSumTotalPriceByMaterial(stage_list) {
  474. let contract_tp = 0;
  475. let qc_tp = 0;
  476. for (const stage of stage_list) {
  477. const result = await this.getSumTotalPrice(stage);
  478. if (result) {
  479. contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
  480. qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
  481. }
  482. }
  483. return { contract_tp, qc_tp };
  484. }
  485. async getSumTotalPriceGclByMaterial(stage_list, regText) {
  486. let contract_tp = 0;
  487. let qc_tp = 0;
  488. for (const stage of stage_list) {
  489. const result = await this.getSumTotalPriceGcl(stage, regText);
  490. if (result) {
  491. contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
  492. qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
  493. }
  494. }
  495. return { contract_tp, qc_tp };
  496. }
  497. async sumLoad(lid, tenders, cover, ignore) {
  498. const conn = await this.db.beginTransaction();
  499. try {
  500. const maxId = await this.ctx.service.ledger._getMaxLid(this.ctx.tender.id);
  501. const select = await this.ctx.service.ledger.getCompleteDataById(lid);
  502. const sumLoad = new SumLoad(this.ctx);
  503. const loadTree = await sumLoad.stageGatherGcl(select, maxId, tenders, null, cover, ignore);
  504. const result = loadTree.getUpdateData();
  505. // if (result.errors.length > 100) throw '您导入的数据存在大量数据错误,请您仔细检查';
  506. const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id);
  507. const updateStageBills = [], insertStageBills = [];
  508. for (const u of result.update) {
  509. const sb = stageBills.find(x => { return x.lid === u.lid; });
  510. if (!sb || sb.times !== this.ctx.stage.curTimes || sb.order !== this.ctx.stage.curOrder) {
  511. u.postil = sb ? sb.postil : null;
  512. u.tid = this.ctx.tender.id;
  513. u.sid = this.ctx.stage.id;
  514. u.said = this.ctx.session.sessionUser.accountId;
  515. u.times = this.ctx.stage.curTimes;
  516. u.order = this.ctx.stage.curOrder;
  517. insertStageBills.push(u);
  518. } else {
  519. u.id = sb.id;
  520. updateStageBills.push(u);
  521. }
  522. }
  523. for (const cd of result.qc_detail) {
  524. cd.tid = this.ctx.tender.id;
  525. cd.sid = this.ctx.stage.id;
  526. cd.import_lid = lid;
  527. }
  528. const his = await this.ctx.service.sumLoadHistory.saveStageHistory(this.ctx.tender.id, this.ctx.stage.id, lid, tenders, result.errors, cover);
  529. if (updateStageBills.length > 0) await conn.updateRows(this.tableName, updateStageBills);
  530. if (insertStageBills.length > 0) await conn.insert(this.tableName, insertStageBills);
  531. await conn.delete(this.ctx.service.stageImportChange.tableName, { import_lid: lid, sid: this.ctx.stage.id });
  532. if (result.qc_detail.length > 0) await conn.insert(this.ctx.service.stageImportChange.tableName, result.qc_detail);
  533. await conn.commit();
  534. return { curStageData: result.update, sumLoadHis: his };
  535. } catch (err) {
  536. this.ctx.helper.log(err);
  537. await conn.rollback();
  538. throw (err.stack ? '导入工程量数据出错' : err);
  539. }
  540. }
  541. }
  542. return StageBills;
  543. };