stage_bills.js 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497
  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. module.exports = app => {
  13. class StageBills extends app.BaseService {
  14. /**
  15. * 构造函数
  16. *
  17. * @param {Object} ctx - egg全局变量
  18. * @return {void}
  19. */
  20. constructor(ctx) {
  21. super(ctx);
  22. this.tableName = 'stage_bills';
  23. }
  24. /**
  25. * 查询期计量最后审核人数据
  26. * @param {Number} tid - 标段id
  27. * @param {Number} sid - 期id
  28. * @param {Number|Array} lid - 台账节点id(可以为空)
  29. * @returns {Promise<*>}
  30. */
  31. async getLastestStageData(tid, sid, lid) {
  32. let lidSql = '', result;
  33. if (lid) {
  34. if (lid instanceof Array) {
  35. lidSql = lid.length > 0 ? ' And lid in (' + this.ctx.helper.getInArrStrSqlFilter(lid) + ')' : '';
  36. } else {
  37. lidSql = ' And lid in (' + this.db.escape(lid) + ')';
  38. }
  39. }
  40. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
  41. ' INNER JOIN ( ' +
  42. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  43. ' WHERE tid = ? And sid = ?' + lidSql +
  44. ' GROUP BY `lid`' +
  45. ' ) As MaxFilter ' +
  46. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
  47. const sqlParam = [tid, sid];
  48. if (!lid) {
  49. return await this.db.query(sql, sqlParam);
  50. } else if (lid instanceof Array) {
  51. return await this.db.query(sql, sqlParam);
  52. } else {
  53. return await this.db.queryOne(sql, sqlParam);
  54. }
  55. }
  56. /**
  57. * 查询 某期 某轮审批 某人数据
  58. * @param {Number} tid - 标段id
  59. * @param {Number} sid - 期id
  60. * @param {Number} times - 第几轮
  61. * @param {Number} order - 流程
  62. * @param {Number|Array} lid - 台账节点id(可以为空)
  63. * @returns {Promise<*>}
  64. */
  65. async getAuditorStageData(tid, sid, times, order, lid) {
  66. const lidSql = lid ? ' And Bills.lid in (?)' : '';
  67. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
  68. ' INNER JOIN ( ' +
  69. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `tid`, `sid` From ' + this.tableName +
  70. ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) And tid = ? And sid = ?' + lidSql +
  71. ' GROUP BY `lid`' +
  72. ' ) As MaxFilter ' +
  73. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid' +
  74. ' AND Bills.sid = MaxFilter.sid';
  75. const sqlParam = [times, times, order, tid, sid];
  76. if (!lid) {
  77. return await this.db.query(sql, sqlParam);
  78. } else if (lid instanceof Array) {
  79. sqlParam.push(lid.join(', '));
  80. return await this.db.query(sql, sqlParam);
  81. } else {
  82. sqlParam.push(lid);
  83. return await this.db.queryOne(sql, sqlParam);
  84. }
  85. }
  86. async getLastestStageData2(tid, sid, lid) {
  87. let lidSql = '', result;
  88. if (lid) {
  89. if (lid instanceof Array) {
  90. lidSql = lid.length > 0 ? ' And lid in (' + this.ctx.helper.getInArrStrSqlFilter(lid) + ')' : '';
  91. } else {
  92. lidSql = ' And lid in (' + this.db.escape(lid) + ')';
  93. }
  94. }
  95. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
  96. ' INNER JOIN ( ' +
  97. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  98. ' WHERE tid = ? And sid = ?' + lidSql +
  99. ' GROUP BY `lid`' +
  100. ' ) As MaxFilter ' +
  101. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
  102. const sqlParam = [tid, sid];
  103. if (!lid) {
  104. return await this.db.query(sql, sqlParam);
  105. } else if (lid instanceof Array) {
  106. return await this.db.query(sql, sqlParam);
  107. } else {
  108. return await this.db.queryOne(sql, sqlParam);
  109. }
  110. }
  111. async getStageUsedBills(tid, sid) {
  112. const sql = 'SELECT Bills.lid, ((not IsNull(Bills.contract_qty) and Bills.contract_qty <> 0) or (not IsNull(Bills.qc_qty) and Bills.qc_qty <> 0)) As used' +
  113. ' FROM ' + this.tableName + ' As Bills ' +
  114. ' INNER JOIN ( ' +
  115. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  116. ' WHERE tid = ? And sid = ?' +
  117. ' GROUP BY `lid`' +
  118. ' ) As MaxFilter ' +
  119. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
  120. const sqlParam = [tid, sid];
  121. const stageBills = await this.db.query(sql, sqlParam);
  122. return this._.map(this._.filter(stageBills, 'used'), 'lid');
  123. }
  124. /**
  125. * 获取截止本期数据
  126. * @param {Number} tid - 标段id
  127. * @param {Number} sorder - 截止期序号
  128. * @param {String|Array[String]} lid - 台账id
  129. * @returns {Promise<*>}
  130. */
  131. async getEndStageData(tid, sorder, lid) {
  132. let lidSql = '';
  133. if (lid) {
  134. if (lid instanceof Array) {
  135. lidSql = lid.length > 0 ? ' And lid in (' + this.ctx.helper.getInArrStrSqlFilter(lid) + ')' : '';
  136. } else {
  137. lidSql = ' And lid in (' + this.db.escape(lid) + ')';
  138. }
  139. }
  140. const sql = 'SELECT Bills.tid, Bills.lid,' +
  141. ' Sum(Bills.contract_qty) As contract_qty, Sum(Bills.contract_tp) As contract_tp,' +
  142. ' Sum(Bills.qc_qty) As qc_qty, Sum(Bills.qc_tp) As qc_tp FROM ' + this.tableName + ' As Bills ' +
  143. ' INNER JOIN ( ' +
  144. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  145. ' WHERE tid = ? ' + lidSql +
  146. ' GROUP BY `lid`, `sid`' +
  147. ' ) As MaxFilter ' +
  148. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
  149. ' INNER JOIN ' + this.ctx.service.stage.tableName + ' As Stage' +
  150. ' ON Bills.sid = Stage.id' +
  151. ' WHERE Stage.order <= ?' +
  152. ' GROUP BY `lid`';
  153. const sqlParam = [tid, sorder];
  154. if (!lid) {
  155. return await this.db.query(sql, sqlParam);
  156. } else if (lid instanceof Array) {
  157. return await this.db.query(sql, sqlParam);
  158. } else {
  159. return await this.db.queryOne(sql, sqlParam);
  160. }
  161. }
  162. async getStageBills(tid, sid, lid) {
  163. const sql = 'SELECT Stage.*, Ledger.unit_price FROM ?? As Stage, ?? As Ledger ' +
  164. ' Where Stage.tid = ?, Stage.sid = ?, Stage.lid = ?, Stage.lid = Ledger.id ' +
  165. ' Order Stage.time DESC, Stage.order DESC ';
  166. const sqlParam = [this.tableName, this.ctx.service.ledger.tableName];
  167. sqlParam.push(this.db.escape(tid));
  168. sqlParam.push(this.db.escape(sid));
  169. sqlParam.push(this.db.escape(lid));
  170. return await this.db.queryOne(sql, sqlParam);
  171. }
  172. _calcStageBillsData(data, orgData, ledgerData) {
  173. const info = this.ctx.tender.info;
  174. const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);
  175. if (data.contract_qty !== undefined) {
  176. data.contract_qty = this.round(data.contract_qty, precision.value);
  177. data.contract_tp = this.ctx.helper.mul(data.contract_qty, ledgerData.unit_price, info.decimal.tp);
  178. }
  179. if (data.qc_qty !== undefined) {
  180. data.qc_qty = this.round(data.qc_qty, precision.value);
  181. data.qc_tp = this.ctx.helper.mul(data.qc_qty, ledgerData.unit_price, info.decimal.tp);
  182. }
  183. if (ledgerData.is_tp && data.contract_tp !== undefined) {
  184. data.contract_tp = this.ctx.helper.round(data.contract_tp, info.decimal.tp);
  185. }
  186. }
  187. async _insertStageBillsData(transaction, insertData, orgData, ledgerData) {
  188. const info = this.ctx.tender.info;
  189. const d = {
  190. tid: this.ctx.tender.id,
  191. lid: ledgerData.id,
  192. sid: this.ctx.stage.id,
  193. times: this.ctx.stage.curTimes,
  194. order: this.ctx.stage.curOrder,
  195. said: this.ctx.session.sessionUser.accountId,
  196. };
  197. if (orgData) {
  198. d.contract_qty = orgData.contract_qty;
  199. d.contract_tp = orgData.contract_tp;
  200. d.qc_qty = orgData.qc_qty;
  201. d.qc_tp = orgData.qc_tp;
  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.postil) {
  215. d.postil = insertData.postil;
  216. }
  217. if (ledgerData.is_tp && insertData.contract_tp !== undefined) {
  218. d.contract_tp = this.ctx.helper.round(insertData.contract_tp, info.decimal.tp);
  219. }
  220. await transaction.insert(this.tableName, d);
  221. }
  222. /**
  223. * 前端提交数据
  224. * @param {Object|Array} data - 提交的数据
  225. * @returns {Promise<void>}
  226. */
  227. async updateStageData(data) {
  228. const datas = data instanceof Array ? data : [data];
  229. const transaction = await this.db.beginTransaction();
  230. try {
  231. for (const d of datas) {
  232. const stageBills = await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, d.lid);
  233. const ledgerBills = await this.ctx.service.ledger.getDataById(d.lid);
  234. if (!stageBills || stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
  235. await this._insertStageBillsData(transaction, d, stageBills, ledgerBills);
  236. } else {
  237. d.id = stageBills.id;
  238. console.log(d);
  239. this._calcStageBillsData(d, stageBills, ledgerBills);
  240. await transaction.update(this.tableName, d);
  241. }
  242. }
  243. await transaction.commit();
  244. } catch (err) {
  245. await transaction.rollback();
  246. throw err;
  247. }
  248. return await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, this._.map(datas, 'lid'));
  249. }
  250. /**
  251. * 根据
  252. * @param transaction
  253. * @param ledgerBills
  254. * @param stageBills
  255. * @param data
  256. * @returns {Promise<void>}
  257. * @private
  258. */
  259. async updateStageBillsQty(transaction, ledgerBills, stageBills, data) {
  260. if (stageBills) {
  261. if ((data.contract_qty === undefined || stageBills.contract_qty !== data.contract_qty) ||
  262. (data.qc_qty === undefined || stageBills.qc_qty !== data.qc_qty)) {
  263. if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
  264. data.id = stageBills.id;
  265. this._calcStageBillsData(data, stageBills, ledgerBills);
  266. await transaction.update(this.tableName, data);
  267. } else {
  268. await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
  269. }
  270. }
  271. } else {
  272. await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
  273. }
  274. };
  275. /**
  276. * 重算 本期计量 数量 (根据部位明细)
  277. * @param {Number} tid - 标段id
  278. * @param {Number} id - 需要计算的节点的id
  279. * @param {Number} lid - 台账id
  280. * @param {Object} transaction - 操作所属事务
  281. * @returns {Promise<void>}
  282. */
  283. async calc(tid, sid, lid, transaction) {
  284. const info = this.ctx.tender.info;
  285. const stageBills = await this.getLastestStageData(tid, sid, lid);
  286. const ledgerBills = await this.ctx.service.ledger.getDataById(lid);
  287. if (!ledgerBills) {
  288. throw '提交数据错误';
  289. }
  290. const posGather = await this.ctx.service.stagePos.getPosGather(tid, sid, lid, transaction);
  291. if (!posGather) { return; }
  292. const precision = this.ctx.helper.findPrecision(info.precision, ledgerBills.unit);
  293. // 计算
  294. if (posGather.contract_qty !== undefined) {
  295. posGather.contract_qty = this.round(posGather.contract_qty, precision.value);
  296. posGather.contract_tp = this.ctx.helper.mul(posGather.contract_qty, ledgerBills.unit_price, info.decimal.tp);
  297. }
  298. if (posGather.qc_qty !== undefined) {
  299. posGather.qc_qty = this.round(posGather.qc_qty, precision.value);
  300. posGather.qc_tp = this.ctx.helper.mul(posGather.qc_qty, ledgerBills.unit_price, info.decimal.tp);
  301. }
  302. if (stageBills) {
  303. if (stageBills.contract_qty === posGather.contract_qty && stageBills.qc_qty === posGather.qc_qty) {
  304. return;
  305. } else {
  306. if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
  307. posGather.id = stageBills.id;
  308. await transaction.update(this.tableName, posGather);
  309. } else {
  310. await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
  311. }
  312. }
  313. } else {
  314. await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
  315. }
  316. }
  317. async updateStageBillsCalcType(data) {
  318. const stageBills = await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, data.id);
  319. const updateData = {contract_qty: null, contract_tp: null};
  320. const transaction = await this.db.beginTransaction();
  321. try {
  322. await transaction.update(this.ctx.service.ledger.tableName, data);
  323. if (stageBills) {
  324. if (stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
  325. const ledgerBills = await this.ctx.service.ledger.getDataById(data.id);
  326. await this._insertStageBillsData(transaction, updateData, stageBills, ledgerBills);
  327. } else {
  328. updateData.id = stageBills.id;
  329. await transaction.update(this.tableName, updateData);
  330. }
  331. }
  332. await transaction.commit();
  333. } catch (err) {
  334. await transaction.rollback();
  335. throw err;
  336. }
  337. const bills = await this.ctx.service.ledger.getDataById(data.id);
  338. const curStageData = await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, [data.id]);
  339. return {bills: [bills], curStageData: curStageData};
  340. }
  341. async getSumTotalPrice(stage) {
  342. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp` FROM ' + this.tableName + ' As Bills ' +
  343. ' INNER JOIN ( ' +
  344. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid`, `sid` From ' + this.tableName +
  345. ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) AND `sid` = ?' +
  346. ' GROUP BY `lid`' +
  347. ' ) As MaxFilter ' +
  348. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid And Bills.sid = MaxFilter.sid';
  349. const sqlParam = [stage.curTimes, stage.curTimes, stage.curOrder, stage.id];
  350. const result = await this.db.queryOne(sql, sqlParam);
  351. return result;
  352. }
  353. async getSumTotalPriceFilter(stage, operate, filter) {
  354. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`' +
  355. ' FROM ' + this.tableName + ' As Bills ' +
  356. ' INNER JOIN ( ' +
  357. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid` From ' + this.tableName +
  358. ' WHERE (`times` < ? OR (`times` = ? AND `order` <= ?))' +
  359. ' GROUP BY `lid`' +
  360. ' ) As MaxFilter ' +
  361. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid ' +
  362. ' INNER JOIN ' + this.ctx.service.ledger.tableName + ' As Ledger ON Bills.lid = Ledger.id' +
  363. ' WHERE Bills.sid = ? And Ledger.b_code ' + operate + ' ?';
  364. const sqlParam = [stage.times, stage.curTimes, stage.curAuditor ? stage.curAuditor.order : 0, stage.id, filter];
  365. const result = await this.db.queryOne(sql, sqlParam);
  366. return result;
  367. }
  368. async getSumTotalPriceGcl(stage, regText) {
  369. if (regText) {
  370. return await this.getSumTotalPriceFilter(stage, 'REGEXP', regText);
  371. } else {
  372. return await this.getSumTotalPriceFilter(stage, '<>', this.db.escape(''));
  373. }
  374. }
  375. async getSumTotalPriceNotGcl(stage) {
  376. return await this.getSumTotalPriceFilter(stage, '=', this.db.escape(''));
  377. }
  378. /**
  379. * 多期清单数据整合 (材料调差调用)
  380. * @param {Number} tid - 标段id
  381. * @param {String} stage_id_list - 期id列表
  382. * @returns {Promise<void>}
  383. */
  384. async getStagesData(tid, stage_id_list) {
  385. let stage_id_listSql = '';
  386. stage_id_list = stage_id_list.indexOf(',') !== -1 ? stage_id_list.split(',') : stage_id_list;
  387. if (stage_id_list) {
  388. if (stage_id_list instanceof Array) {
  389. stage_id_listSql = stage_id_list.length > 0 ? ' And sid in (' + this.ctx.helper.getInArrStrSqlFilter(stage_id_list) + ')' : '';
  390. } else {
  391. stage_id_listSql = ' And sid in (' + this.db.escape(stage_id_list) + ')';
  392. }
  393. }
  394. const sql = 'SELECT `lid`, `tid`, `sid`, SUM(`contract_qty`) as `contract_qty`, SUM(`contract_tp`) as `contract_tp`, ' +
  395. 'SUM(`qc_qty`) as `qc_qty`, SUM(`qc_tp`) as `qc_tp` FROM ' + this.tableName + ' WHERE `tid` = ? ' +
  396. stage_id_listSql + ' GROUP BY `lid`';
  397. const sqlParam = [tid];
  398. const result = await this.db.query(sql, sqlParam);
  399. return result;
  400. }
  401. /**
  402. * 获取多期(合同和数量变更相加)计量-小计(材料调差调用)
  403. * @param {Number} tid - 标段id
  404. * @param {String} stage_id_list - 期id列表
  405. * @param {String} lid - 台账id
  406. * @param {String} xid - 项目节id
  407. * @returns {Promise<void>}
  408. */
  409. async getGatherQtyByMaterial(tid, stage_id_list, lid) {
  410. stage_id_list = stage_id_list !== null ? stage_id_list.split(',') : [];
  411. let gather_qty = 0;
  412. for (const sid of stage_id_list) {
  413. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
  414. ' INNER JOIN ( ' +
  415. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
  416. ' WHERE tid = ? And sid = ?' +
  417. ' GROUP BY `lid`' +
  418. ' ) As MaxFilter ' +
  419. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
  420. ' WHERE Bills.lid = ?';
  421. const sqlParam = [tid, sid, lid];
  422. const result = await this.db.queryOne(sql, sqlParam);
  423. if (result) {
  424. gather_qty = this.ctx.helper.add(gather_qty, this.ctx.helper.add(result.contract_qty, result.qc_qty));
  425. }
  426. }
  427. return gather_qty !== 0 ? gather_qty : null;
  428. }
  429. async getSumTotalPriceByMaterial(stage_list) {
  430. let contract_tp = 0;
  431. let qc_tp = 0;
  432. for (const stage of stage_list) {
  433. const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp` FROM ' + this.tableName + ' As Bills ' +
  434. ' INNER JOIN ( ' +
  435. ' SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid` From ' + this.tableName +
  436. ' WHERE `times` <= ? AND `order` <= ?' +
  437. ' GROUP BY `lid`' +
  438. ' ) As MaxFilter ' +
  439. ' ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid' +
  440. ' WHERE Bills.sid = ?';
  441. const sqlParam = [stage.curTimes, stage.curOrder, stage.id];
  442. const result = await this.db.queryOne(sql, sqlParam);
  443. if (result) {
  444. contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
  445. qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
  446. }
  447. }
  448. return { contract_tp, qc_tp };
  449. }
  450. async getSumTotalPriceGclByMaterial(stage_list, regText) {
  451. let contract_tp = 0;
  452. let qc_tp = 0;
  453. for (const stage of stage_list) {
  454. let result = null;
  455. if (regText) {
  456. result = await this.getSumTotalPriceFilter(stage, 'REGEXP', regText);
  457. } else {
  458. result = await this.getSumTotalPriceFilter(stage, '<>', this.db.escape(''));
  459. }
  460. if (result) {
  461. contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
  462. qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
  463. }
  464. }
  465. return { contract_tp, qc_tp };
  466. }
  467. }
  468. return StageBills;
  469. };