change_audit_list.js 62 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date 2018/8/14
  7. * @version
  8. */
  9. const audit = require('../const/audit');
  10. module.exports = app => {
  11. class ChangeAuditList extends app.BaseService {
  12. /**
  13. * 构造函数
  14. *
  15. * @param {Object} ctx - egg全局变量
  16. * @return {void}
  17. */
  18. constructor(ctx) {
  19. super(ctx);
  20. this.tableName = 'change_audit_list';
  21. }
  22. /**
  23. * 取出变更令清单列表,并按台账清单在前,空白清单在后排序
  24. * @return {void}
  25. */
  26. async getList(cid, order_by = this.ctx.change.order_by) {
  27. if (order_by) {
  28. return await this.getAllDataByCondition({ where: { cid }, orders: [['order', 'asc']] });
  29. }
  30. const sql = 'SELECT * FROM ?? WHERE `cid` = ? ORDER BY `lid` = "0", `id` asc';
  31. const sqlParam = [this.tableName, cid];
  32. const result = await this.db.query(sql, sqlParam);
  33. return this._.orderBy(result, ['order'], ['asc']);
  34. }
  35. /**
  36. * 移除清单时,同步其后清单order
  37. * @param transaction - 事务
  38. * @param {Number} cid - 变更cid
  39. * @param {Number} order - order之后的
  40. * @return {Promise<*>}
  41. * @private
  42. */
  43. async _syncOrder(transaction, cid, order, selfOperate = '-', num = 1) {
  44. this.initSqlBuilder();
  45. this.sqlBuilder.setAndWhere('cid', {
  46. value: this.db.escape(cid),
  47. operate: '=',
  48. });
  49. this.sqlBuilder.setAndWhere('order', {
  50. value: order,
  51. operate: '>=',
  52. });
  53. this.sqlBuilder.setUpdateData('order', {
  54. value: num,
  55. selfOperate,
  56. });
  57. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
  58. const data = await transaction.query(sql, sqlParam);
  59. return data;
  60. }
  61. /**
  62. * 添加空白变更清单
  63. * @return {void}
  64. */
  65. async add(data, delimit = 100) {
  66. if (!this.ctx.tender || !this.ctx.change) {
  67. throw '数据错误';
  68. }
  69. const transaction = await this.db.beginTransaction();
  70. try {
  71. let order = null;
  72. if (this.ctx.change.order_by) {
  73. if (data) {
  74. order = parseInt(data) + 1;
  75. // order以下的清单+1
  76. await this._syncOrder(transaction, this.ctx.change.cid, order, '+');
  77. } else {
  78. order = await this.count({ cid: this.ctx.change.cid });
  79. order = order ? order + 1 : 1;
  80. }
  81. }
  82. const insertData = {
  83. tid: this.ctx.tender.id,
  84. cid: this.ctx.change.cid,
  85. lid: '0',
  86. code: '',
  87. name: '',
  88. bwmx: '',
  89. unit: '',
  90. unit_price: null,
  91. oamount: 0,
  92. oamount2: 0,
  93. camount: 0,
  94. camount_expr: '',
  95. samount: '',
  96. detail: '',
  97. spamount: 0,
  98. xmj_code: null,
  99. xmj_jldy: null,
  100. xmj_dwgc: null,
  101. xmj_fbgc: null,
  102. xmj_fxgc: null,
  103. gcl_id: '',
  104. mx_id: '',
  105. order,
  106. is_valuation: 1,
  107. delimit,
  108. };
  109. // 新增工料
  110. const result = await transaction.insert(this.tableName, insertData);
  111. if (result.affectedRows === 0) {
  112. throw '新增空白清单数据失败';
  113. }
  114. await transaction.commit();
  115. return await this.getDataById(result.insertId);
  116. } catch (err) {
  117. await transaction.rollback();
  118. throw err;
  119. }
  120. }
  121. /**
  122. * 添加台账清单(从新增部位页新增)
  123. * @return {void}
  124. */
  125. async adds(datas) {
  126. if (!this.ctx.tender || !this.ctx.change) {
  127. throw '数据错误';
  128. }
  129. const transaction = await this.db.beginTransaction();
  130. try {
  131. let order = null;
  132. if (this.ctx.change.order_by) {
  133. const data = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null;
  134. if (data) {
  135. order = parseInt(data.order) + 1;
  136. // order以下的清单+1
  137. await this._syncOrder(transaction, this.ctx.change.cid, order, '+');
  138. } else {
  139. order = await this._getMaxOrder(this.ctx.change.cid);
  140. order = order ? order + 1 : 1;
  141. }
  142. }
  143. const insertData = [];
  144. for (const d of datas) {
  145. d.tid = this.ctx.tender.id;
  146. d.cid = this.ctx.change.cid;
  147. d.spamount = d.spamount || null;
  148. d.detail = d.detail || '';
  149. d.samount = d.samount || '';
  150. d.order = order ? order : null;
  151. order = order ? order + 1 : null;
  152. insertData.push(d);
  153. }
  154. // 新增工料
  155. const result = await transaction.insert(this.tableName, insertData);
  156. if (result.affectedRows === 0) {
  157. throw '添加清单数据失败';
  158. }
  159. await transaction.commit();
  160. return true;
  161. } catch (err) {
  162. await transaction.rollback();
  163. throw err;
  164. }
  165. }
  166. /**
  167. * 批量添加空白变更清单
  168. * @return {void}
  169. */
  170. async batchAdd(data, delimit = 100) {
  171. if (!this.ctx.tender || !this.ctx.change) {
  172. throw '数据错误';
  173. }
  174. const transaction = await this.db.beginTransaction();
  175. try {
  176. const num = data.num ? parseInt(data.num) : 0;
  177. if (num < 1 || num > 100) {
  178. throw '批量添加的空白清单数目不能小于1或大于100';
  179. }
  180. let order = null;
  181. if (this.ctx.change.order_by) {
  182. if (data) {
  183. order = parseInt(data.postData) + 1;
  184. // order以下的清单+1
  185. await this._syncOrder(transaction, this.ctx.change.cid, order, '+', num);
  186. } else {
  187. order = await this._getMaxOrder(this.ctx.change.cid);
  188. order = order ? order + 1 : 1;
  189. }
  190. }
  191. const insertArray = [];
  192. for (let i = 0; i < num; i++) {
  193. const insertData = {
  194. tid: this.ctx.tender.id,
  195. cid: this.ctx.change.cid,
  196. lid: '0',
  197. code: '',
  198. name: '',
  199. bwmx: '',
  200. unit: '',
  201. unit_price: null,
  202. oamount: 0,
  203. oamount2: 0,
  204. camount: 0,
  205. camount_expr: '',
  206. samount: '',
  207. detail: '',
  208. spamount: 0,
  209. xmj_code: null,
  210. xmj_jldy: null,
  211. xmj_dwgc: null,
  212. xmj_fbgc: null,
  213. xmj_fxgc: null,
  214. gcl_id: '',
  215. mx_id: '',
  216. order: order ? order + i : null,
  217. is_valuation: 1,
  218. delimit,
  219. };
  220. insertArray.push(insertData);
  221. }
  222. // 新增工料
  223. const result = await transaction.insert(this.tableName, insertArray);
  224. if (result.affectedRows !== num) {
  225. throw '批量添加空白清单数据失败';
  226. }
  227. await transaction.commit();
  228. // // 获取刚批量添加的所有list
  229. // for (let j = 0; j < num; j++) {
  230. // insertArray[j].id = result.insertId + j;
  231. // }
  232. // return insertArray;
  233. return await this.getList(this.ctx.change.cid);
  234. } catch (err) {
  235. await transaction.rollback();
  236. throw err;
  237. }
  238. }
  239. async _getMaxOrder(cid) {
  240. const sql = 'SELECT MAX(`order`) AS `order` FROM ?? WHERE cid = ?';
  241. const sqlParams = [this.tableName, cid];
  242. const result = await this.db.queryOne(sql, sqlParams);
  243. return result ? result.order : 0;
  244. }
  245. /**
  246. * 删除变更清单
  247. * @param {int} id 清单id
  248. * @return {void}
  249. */
  250. async del(data) {
  251. if (!this.ctx.tender || !this.ctx.change) {
  252. throw '数据错误';
  253. }
  254. const transaction = await this.db.beginTransaction();
  255. try {
  256. // 判断是否可删
  257. await transaction.delete(this.tableName, { id: data.ids });
  258. // // order以下的清单-1
  259. if (this.ctx.change.order_by && data.postData) {
  260. await this._syncOrder(transaction, this.ctx.change.cid, data.postData, '-', data.delLength ? data.delLength : data.ids.length);
  261. }
  262. // 重新算变更令总额
  263. await this.calcCamountSum(transaction);
  264. await transaction.commit();
  265. return true;
  266. } catch (err) {
  267. await transaction.rollback();
  268. throw err;
  269. }
  270. }
  271. async dels(data) {
  272. if (!this.ctx.tender || !this.ctx.change) {
  273. throw '数据错误';
  274. }
  275. const transaction = await this.db.beginTransaction();
  276. try {
  277. // 判断是否存在调用,存在则报错
  278. const delList = await this.getAllDataByCondition({ where: { id: data.ids } });
  279. const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? AND b.id in (' + this.ctx.helper.getInArrStrSqlFilter(data.ids) + ') GROUP BY b.cbid';
  280. const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid];
  281. const usedList = await transaction.query(sql1, sqlParam1);
  282. if (usedList.length > 0) {
  283. throw '清单已被调用,不可删除';
  284. }
  285. await transaction.delete(this.tableName, { id: data.ids });
  286. // // order以下的清单-1
  287. if (this.ctx.change.order_by) {
  288. const postData = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null;
  289. await this._syncOrder(transaction, this.ctx.change.cid, (postData ? postData.order : null), '-', data.ids.length);
  290. }
  291. // 重新算变更令总额
  292. await this.calcCamountSum(transaction);
  293. await transaction.commit();
  294. return true;
  295. } catch (err) {
  296. await transaction.rollback();
  297. throw err;
  298. }
  299. }
  300. /**
  301. * 修改变更清单
  302. * @param {Object} data 工料内容
  303. * @param {int} order 期数
  304. * @return {void}
  305. */
  306. async save(data) {
  307. if (!this.ctx.tender || !this.ctx.change) {
  308. throw '数据错误';
  309. }
  310. const transaction = await this.db.beginTransaction();
  311. try {
  312. // const mb_id = data.mb_id;
  313. // delete data.mb_id;
  314. await transaction.update(this.tableName, data);
  315. // await this.calcQuantityByML(transaction, mb_id);
  316. await this.calcCamountSum(transaction);
  317. await transaction.commit();
  318. return true;
  319. } catch (err) {
  320. await transaction.rollback();
  321. throw err;
  322. }
  323. }
  324. /**
  325. * 修改变更清单 复制粘贴
  326. * @param {Object} datas 修改内容
  327. * @return {void}
  328. */
  329. async saveDatas(datas) {
  330. if (!this.ctx.tender || !this.ctx.change) {
  331. throw '数据错误';
  332. }
  333. // 判断是否可修改
  334. // 判断t_type是否为费用
  335. const transaction = await this.db.beginTransaction();
  336. try {
  337. // for (const data of datas) {
  338. // const mb_id = data.mb_id;
  339. // delete data.mb_id;
  340. // await transaction.update(this.tableName, data);
  341. // await this.calcQuantityByML(transaction, mb_id);
  342. // }
  343. await transaction.updateRows(this.tableName, datas);
  344. await this.calcCamountSum(transaction);
  345. await transaction.commit();
  346. return true;
  347. } catch (err) {
  348. await transaction.rollback();
  349. throw err;
  350. }
  351. }
  352. /**
  353. * 台账数据清单 重新选择
  354. * @param {Object} datas 内容
  355. * @return {void}
  356. */
  357. async saveLedgerListDatas(datas, data = null, order_by = this.ctx.change.order_by) {
  358. if (!this.ctx.tender || !this.ctx.change) {
  359. throw '数据错误';
  360. }
  361. // 判断是否可修改
  362. // 判断t_type是否为费用
  363. const transaction = await this.db.beginTransaction();
  364. try {
  365. let usedList = [];
  366. let order = null;
  367. if (order_by) {
  368. if (data) {
  369. order = parseInt(data) + 1;
  370. // order以下的清单+1
  371. await this._syncOrder(transaction, this.ctx.change.cid, order, '+', datas.length);
  372. } else {
  373. order = await this.count({ cid: this.ctx.change.cid });
  374. order = order ? order + 1 : 1;
  375. }
  376. } else {
  377. const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? GROUP BY b.cbid';
  378. const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid];
  379. usedList = await transaction.query(sql1, sqlParam1);
  380. // 先删除原本的台账清单数据
  381. const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"';
  382. const sqlParam = [this.tableName, this.ctx.change.cid];
  383. await transaction.query(sql, sqlParam);
  384. }
  385. const insertDatas = [];
  386. for (const data of datas) {
  387. data.tid = this.ctx.tender.id;
  388. data.cid = this.ctx.change.cid;
  389. data.spamount = data.camount;
  390. data.samount = '';
  391. data.order = order ? order : null;
  392. order = order ? order + 1 : null;
  393. insertDatas.push(data);
  394. }
  395. if (insertDatas.length > 0) await this.insertBigDatas(transaction, insertDatas);
  396. await this.calcCamountSum(transaction);
  397. if (!order_by) {
  398. // 更新stage_change和stage_change_final的cbid
  399. if (usedList.length > 0) {
  400. const updateList = [];
  401. const sql2 = 'SELECT * FROM ?? WHERE `cid` = ? AND `lid` != "0"';
  402. const sqlParam2 = [this.tableName, this.ctx.change.cid];
  403. const newList = await transaction.query(sql2, sqlParam2);
  404. // const newList = await transaction.select(this.tableName, { where: { cid: this.ctx.change.cid } });
  405. for (const used of usedList) {
  406. const findFilter = { lid: used.lid, gcl_id: used.gcl_id, bwmx: used.bwmx };
  407. if (used.mx_id) findFilter.mx_id = used.mx_id;
  408. const newone = this._.find(newList, findFilter);
  409. if (newone) {
  410. updateList.push({
  411. row: {
  412. cbid: newone.id,
  413. },
  414. where: {
  415. cid: this.ctx.change.cid,
  416. cbid: used.id,
  417. },
  418. });
  419. }
  420. }
  421. if (updateList.length > 0) {
  422. await transaction.updateRows(this.ctx.service.stageChange.tableName, updateList);
  423. await transaction.updateRows(this.ctx.service.stageChangeFinal.tableName, updateList);
  424. }
  425. }
  426. }
  427. await transaction.commit();
  428. return true;
  429. } catch (err) {
  430. await transaction.rollback();
  431. throw err;
  432. }
  433. }
  434. /**
  435. * 台账数据清单 清除部分并重新算原设计总金额
  436. * @param {Object} datas 内容
  437. * @return {void}
  438. */
  439. async removeLedgerListDatas(datas) {
  440. if (!this.ctx.tender || !this.ctx.change) {
  441. throw '数据错误';
  442. }
  443. // 判断是否可修改
  444. // 判断t_type是否为费用
  445. const transaction = await this.db.beginTransaction();
  446. try {
  447. // 先删除原本的台账清单数据
  448. // const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"';
  449. // const sqlParam = [this.tableName, this.ctx.change.cid];
  450. // await transaction.query(sql, sqlParam);
  451. // const insertDatas = [];
  452. for (const data of datas) {
  453. // data.tid = this.ctx.tender.id;
  454. // data.cid = this.ctx.change.cid;
  455. // data.spamount = data.camount;
  456. // data.samount = '';
  457. // insertDatas.push(data);
  458. await transaction.delete(this.tableName, { id: data.id });
  459. }
  460. // if (insertDatas.length > 0) await transaction.insert(this.tableName, insertDatas);
  461. await this.calcCamountSum(transaction);
  462. await transaction.commit();
  463. return true;
  464. } catch (err) {
  465. await transaction.rollback();
  466. throw err;
  467. }
  468. }
  469. async calcCamountSum(transaction, updateTpDecimal = false) {
  470. // const sql = 'SELECT SUM(ROUND(`camount`*`unit_price`, )) as total_price FROM ?? WHERE cid = ?';
  471. // const sqlParam = [this.tableName, this.change.cid];
  472. // const tp = await transaction.queryOne(sql, sqlParam);
  473. // 防止小数位不精确,采用取值计算
  474. const sql = 'SELECT unit_price, spamount, is_valuation, gcl_id, unit FROM ?? WHERE cid = ?';
  475. const sqlParam = [this.tableName, this.ctx.change.cid];
  476. const changeList = await transaction.query(sql, sqlParam);
  477. let total_price = 0;
  478. let positive_tp = 0;
  479. let negative_tp = 0;
  480. let valuation_tp = 0;
  481. let unvaluation_tp = 0;
  482. const tp_decimal = this.ctx.change.tp_decimal ? this.ctx.change.tp_decimal : this.ctx.tender.info.decimal.tp;
  483. const up_decimal = this.ctx.change.up_decimal ? this.ctx.change.up_decimal : this.ctx.tender.info.decimal.up;
  484. const gclChangeList = this._.uniq(this._.map(changeList, 'gcl_id'));
  485. for (const g of gclChangeList) {
  486. if (g) {
  487. const list = this._.filter(changeList, { gcl_id: g });
  488. let spamount = 0;
  489. let valuation_amount = 0;
  490. let unvaluation_amount = 0;
  491. let unitPrice = 0;
  492. for (const cl of list) {
  493. if (cl.spamount) {
  494. spamount = this.ctx.helper.accAdd(spamount, cl.spamount);
  495. unitPrice = cl.unit_price;
  496. if (cl.is_valuation) {
  497. valuation_amount = this.ctx.helper.accAdd(valuation_amount, cl.spamount);
  498. } else {
  499. unvaluation_amount = this.ctx.helper.accAdd(unvaluation_amount, cl.spamount);
  500. }
  501. }
  502. }
  503. const price = this.ctx.helper.mul(spamount, this.ctx.helper.round(unitPrice, up_decimal), tp_decimal);
  504. const valuation_price = this.ctx.helper.mul(valuation_amount, this.ctx.helper.round(unitPrice, up_decimal), tp_decimal) || 0;
  505. const unvaluation_price = this.ctx.helper.mul(unvaluation_amount, this.ctx.helper.round(unitPrice, up_decimal), tp_decimal) || 0;
  506. valuation_tp = this.ctx.helper.accAdd(valuation_tp, valuation_price);
  507. unvaluation_tp = this.ctx.helper.accAdd(unvaluation_tp, unvaluation_price);
  508. total_price = this.ctx.helper.accAdd(total_price, price);
  509. if (price >= 0) {
  510. positive_tp = this.ctx.helper.accAdd(positive_tp, price);
  511. } else {
  512. negative_tp = this.ctx.helper.accAdd(negative_tp, price);
  513. }
  514. } else {
  515. const list = this._.filter(changeList, { gcl_id: g });
  516. for (const cl of list) {
  517. const price = this.ctx.helper.mul(this.ctx.helper.round(cl.unit_price, up_decimal), cl.spamount, tp_decimal);
  518. total_price = this.ctx.helper.accAdd(total_price, price);
  519. if (price >= 0) {
  520. positive_tp = this.ctx.helper.accAdd(positive_tp, price);
  521. } else {
  522. negative_tp = this.ctx.helper.accAdd(negative_tp, price);
  523. }
  524. if (cl.is_valuation) {
  525. valuation_tp = this.ctx.helper.accAdd(valuation_tp, price);
  526. } else {
  527. unvaluation_tp = this.ctx.helper.accAdd(unvaluation_tp, price);
  528. }
  529. }
  530. }
  531. }
  532. const updateData = {
  533. total_price,
  534. positive_tp,
  535. negative_tp,
  536. valuation_tp,
  537. unvaluation_tp,
  538. };
  539. if (updateTpDecimal) {
  540. updateData.tp_decimal = tp_decimal;
  541. updateData.up_decimal = up_decimal;
  542. }
  543. const options = {
  544. where: {
  545. cid: this.ctx.change.cid,
  546. },
  547. };
  548. await transaction.update(this.ctx.service.change.tableName, updateData, options);
  549. }
  550. /**
  551. * 用户数据数量提交
  552. * @param {Object} data 内容
  553. * @return {void}
  554. */
  555. async saveAmountData(data) {
  556. if (!this.ctx.tender || !this.ctx.change) {
  557. throw '数据错误';
  558. }
  559. // 判断是否可修改
  560. // 判断t_type是否为费用
  561. const transaction = await this.db.beginTransaction();
  562. try {
  563. await transaction.update(this.tableName, data);
  564. await this.calcCamountSum(transaction);
  565. await transaction.commit();
  566. return true;
  567. } catch (err) {
  568. await transaction.rollback();
  569. throw err;
  570. }
  571. }
  572. async gatherBgBills(tid) {
  573. const sql = 'SELECT cb.code, cb.name, cb.unit, cb.unit_price, Round(Sum(cb.samount + 0), 6) as quantity' +
  574. ' FROM ' + this.tableName + ' cb' +
  575. ' LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' +
  576. ' WHERE cb.tid = ? and c.status = ?' +
  577. ' GROUP BY code, name, unit, unit_price';
  578. const param = [tid, audit.flow.status.checked];
  579. const result = await this.db.query(sql, param);
  580. for (const b of result) {
  581. b.total_price = this.ctx.helper.mul(b.unit_price, b.quantity, this.ctx.tender.info.decimal.tp);
  582. }
  583. return result;
  584. }
  585. /**
  586. * 报表用
  587. * Tony Kang
  588. * @param {tid} tid - 标段id
  589. * @return {void}
  590. */
  591. async getChangeAuditBills(tid, onlyChecked) {
  592. const sql = 'SELECT cb.*' +
  593. ' FROM ' + this.tableName + ' cb' +
  594. ' LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' +
  595. ' WHERE c.tid = ? ' + (onlyChecked ? 'and c.status = 3' : '') +
  596. ' ORDER BY cb.cid, cb.code';
  597. const param = [tid];
  598. const result = await this.db.query(sql, param);
  599. return result;
  600. }
  601. /**
  602. * 删除变更清单(form 变更新增部位页)
  603. * Tony Kang
  604. * @param {String} transaction - 队列
  605. * @param {String} tid - 标段id
  606. * @param {Array} ids - id列表
  607. * @param {String} column - id所属字段
  608. * @param {String} mx_id - mx_id为空列删除
  609. * @return {void}
  610. */
  611. async deleteDataByRevise(transaction, tid, ids, column = 'gcl_id', mx_id = 'hello') {
  612. if (ids.length > 0) {
  613. const addSql = mx_id === '' ? ' AND (`mx_id` is NULL OR `mx_id` = "")' : '';
  614. const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ')' + addSql + ' GROUP BY `cid`';
  615. const params = [this.tableName, tid];
  616. const changes = await transaction.query(sql, params);
  617. if (changes.length > 0) {
  618. const delData = {
  619. tid,
  620. };
  621. delData[column] = ids;
  622. await transaction.delete(this.tableName, delData);
  623. for (const c of changes) {
  624. // 重算选了此清单的变更令已变更金额
  625. await this.reCalcTp(transaction, c.cid);
  626. }
  627. }
  628. }
  629. }
  630. /**
  631. * 修改变更清单(form 变更新增部位页台账子节点清单编号编辑)
  632. * Tony Kang
  633. * @param {String} transaction - 队列
  634. * @param {String} tid - 标段id
  635. * @param {Array} datas - 更新列表
  636. * @param {String} column - id所属字段
  637. * @return {void}
  638. */
  639. async updateDataByReviseLedger(transaction, tid, datas, column = 'gcl_id') {
  640. if (datas.length > 0) {
  641. const ids = this._.map(datas, 'id');
  642. const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
  643. const params = [this.tableName, tid];
  644. const changeAuditLists = await transaction.query(sql, params);
  645. if (changeAuditLists.length > 0) {
  646. const updateArr = [];
  647. const cidList = [];
  648. for (const ca of changeAuditLists) {
  649. const d = this._.find(datas, { id: ca[column] });
  650. if (d.id) {
  651. const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id });
  652. const updateCol = {};
  653. if (column === 'gcl_id' && d.b_code) updateCol.code = d.b_code;
  654. if (column === 'gcl_id' && d.quantity !== undefined && changePosNum === 0) updateCol.oamount = d.quantity ? d.quantity : 0;
  655. if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0;
  656. if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit;
  657. if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name;
  658. if (d.b_code !== undefined && d.b_code === null) {
  659. // 清单升级成了项目节,故删除变更已有的此清单,并找出需要重新计算的变更令
  660. const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
  661. const params = [this.tableName, tid, d.id];
  662. const changes = await transaction.query(sql, params);
  663. for (const c of changes) {
  664. if (this._.indexOf(cidList, c.cid) === -1) {
  665. cidList.push(c.cid);
  666. }
  667. }
  668. const delData = {
  669. tid,
  670. };
  671. delData[column] = d.id;
  672. await transaction.delete(this.tableName, delData);
  673. } else {
  674. const options = {
  675. row: {},
  676. where: {},
  677. };
  678. options.row = updateCol;
  679. options.where[column] = d.id;
  680. if (!this._.isEmpty(options.row)) updateArr.push(options);
  681. if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) {
  682. const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
  683. const params = [this.tableName, tid, d.id];
  684. const changes = await transaction.query(sql, params);
  685. for (const c of changes) {
  686. if (this._.indexOf(cidList, c.cid) === -1) {
  687. cidList.push(c.cid);
  688. }
  689. }
  690. }
  691. }
  692. }
  693. }
  694. if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
  695. if (cidList.length > 0) {
  696. for (const c of cidList) {
  697. await this.reCalcTp(transaction, c);
  698. }
  699. }
  700. }
  701. // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号,细目,单位工程,分部分项工程数据
  702. for (const data of datas) {
  703. const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id });
  704. if (select && select.is_leaf === 0) {
  705. const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction);
  706. const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新
  707. if (childLists.length > 0) {
  708. const d = { xmj_code: '', xmj_jldy: '' };
  709. if (select.code !== null) {
  710. d.xmj_code = select.code;
  711. d.xmj_jldy = select.name;
  712. } else {
  713. // 再找出上一个项目节节点并更新
  714. this.newBills = false;
  715. const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid);
  716. d.xmj_code = parents.code;
  717. d.xmj_jldy = parents.name;
  718. }
  719. for (const cl of childLists) {
  720. await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } });
  721. }
  722. }
  723. if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据
  724. const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新
  725. const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新
  726. const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新
  727. if (secondChildLists.length > 0) {
  728. for (const sl of secondChildLists) {
  729. await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } });
  730. }
  731. }
  732. if (thirdChildLists.length > 0) {
  733. for (const tl of thirdChildLists) {
  734. await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } });
  735. }
  736. }
  737. if (fourthChildLists.length > 0 && select.level === 2) {
  738. for (const fl of fourthChildLists) {
  739. await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } });
  740. }
  741. }
  742. }
  743. }
  744. }
  745. }
  746. }
  747. /**
  748. * 修改变更清单(form 变更新增部位页台账节点清单编号升降级)
  749. * Tony Kang
  750. * @param {String} transaction - 队列
  751. * @param {String} tid - 标段id
  752. * @param {Array} datas - 更新列表
  753. * @param {String} column - id所属字段
  754. * @return {void}
  755. */
  756. async updateDataByReviseLedgerUpDownLevel(transaction, tid, datas, column = 'gcl_id') {
  757. if (datas.length > 0) {
  758. console.log(datas);
  759. // const ids = this._.map(datas, 'id');
  760. // const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
  761. // const params = [this.tableName, tid];
  762. // const changeAuditLists = await transaction.query(sql, params);
  763. // if (changeAuditLists.length > 0) {
  764. // const updateArr = [];
  765. // const cidList = [];
  766. // for (const ca of changeAuditLists) {
  767. // const d = this._.find(datas, { id: ca[column] });
  768. // console.log(d);
  769. // if (d.id) {
  770. // const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id });
  771. // const updateCol = {};
  772. // if (column === 'gcl_id' && d.b_code !== undefined) updateCol.code = d.b_code;
  773. // if (column === 'gcl_id' && d.sgfh_qty !== undefined && changePosNum === 0) updateCol.oamount = d.sgfh_qty ? d.sgfh_qty : 0;
  774. // if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0;
  775. // if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit;
  776. // if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name;
  777. // if (d.code !== undefined && d.b_code === null) {
  778. // // 清单升级成了项目节,故删除变更已有的此清单,并找出需要重新计算的变更令
  779. // const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
  780. // const params = [this.tableName, tid, d.id];
  781. // const changes = await transaction.query(sql, params);
  782. // for (const c of changes) {
  783. // if (this._.indexOf(cidList, c.cid) === -1) {
  784. // cidList.push(c.cid);
  785. // }
  786. // }
  787. // const delData = {
  788. // tid,
  789. // };
  790. // delData[column] = d.id;
  791. // console.log(delData);
  792. // await transaction.delete(this.tableName, delData);
  793. // } else {
  794. // const options = {
  795. // row: {},
  796. // where: {},
  797. // };
  798. // options.row = updateCol;
  799. // options.where[column] = d.id;
  800. // if (!this._.isEmpty(options.row)) updateArr.push(options);
  801. // if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) {
  802. // const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
  803. // const params = [this.tableName, tid, d.id];
  804. // const changes = await transaction.query(sql, params);
  805. // for (const c of changes) {
  806. // if (this._.indexOf(cidList, c.cid) === -1) {
  807. // cidList.push(c.cid);
  808. // }
  809. // }
  810. // }
  811. // }
  812. // }
  813. // }
  814. // console.log(updateArr, cidList);
  815. // if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
  816. // if (cidList.length > 0) {
  817. // for (const c of cidList) {
  818. // await this.reCalcTp(transaction, c);
  819. // }
  820. // }
  821. // }
  822. // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号,细目,单位工程,分部分项工程数据
  823. // for (const data of datas) {
  824. // const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id });
  825. // console.log(select);
  826. // if (select && select.is_leaf === 0) {
  827. // const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction);
  828. // const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新
  829. // if (childLists.length > 0) {
  830. // const d = { xmj_code: '', xmj_jldy: '' };
  831. // if (select.code !== null) {
  832. // d.xmj_code = select.code;
  833. // d.xmj_jldy = select.name;
  834. // } else {
  835. // // 再找出上一个项目节节点并更新
  836. // this.newBills = false;
  837. // const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid);
  838. // console.log('hello :', parents);
  839. // d.xmj_code = parents.code;
  840. // d.xmj_jldy = parents.name;
  841. // }
  842. // for (const cl of childLists) {
  843. // await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } });
  844. // }
  845. // }
  846. // if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据
  847. // const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新
  848. // const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新
  849. // const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新
  850. // if (secondChildLists.length > 0) {
  851. // for (const sl of secondChildLists) {
  852. // await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } });
  853. // }
  854. // }
  855. // if (thirdChildLists.length > 0) {
  856. // for (const tl of thirdChildLists) {
  857. // await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } });
  858. // }
  859. // }
  860. // if (fourthChildLists.length > 0) {
  861. // for (const fl of fourthChildLists) {
  862. // await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } });
  863. // }
  864. // }
  865. // }
  866. // }
  867. // }
  868. }
  869. }
  870. /**
  871. * 修改变更清单(form 变更新增部位页计量单元编辑)
  872. * Tony Kang
  873. * @param {String} transaction - 队列
  874. * @param {String} tid - 标段id
  875. * @param {Array} datas - 更新列表
  876. * @param {String} column - id所属字段
  877. * @return {void}
  878. */
  879. async updateDataByRevisePos(transaction, tid, datas, column = 'mx_id') {
  880. if (datas.length > 0) {
  881. const ids = this._.map(datas, 'id');
  882. const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
  883. const params = [this.tableName, tid];
  884. const changeAuditLists = await transaction.query(sql, params);
  885. if (changeAuditLists.length > 0) {
  886. const updateArr = [];
  887. for (const ca of changeAuditLists) {
  888. const d = this._.find(datas, { id: ca[column] });
  889. if (d.id) {
  890. const updateCol = {};
  891. if (column === 'mx_id' && d.name !== undefined) updateCol.bwmx = d.name;
  892. if (column === 'mx_id' && d.quantity !== undefined) updateCol.oamount = d.quantity ? d.quantity : 0;
  893. if (column === 'mx_id' && d.quantity === undefined &&
  894. ((d.sgfh_expr && d.sgfh_expr === '') || (d.sjcl_expr && d.sjcl_expr === '') || (d.qtcl_expr && d.qtcl_expr === ''))) updateCol.oamount = 0;
  895. const options = {
  896. row: {},
  897. where: {},
  898. };
  899. options.row = updateCol;
  900. options.where[column] = d.id;
  901. // if (!this._.isEmpty(updateCol)) await transaction.update(this.tableName, updateCol, options);
  902. if (!this._.isEmpty(options.row)) updateArr.push(options);
  903. }
  904. }
  905. if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
  906. }
  907. }
  908. }
  909. /**
  910. * 重算变更令总金额(变更新增部位设置时使用)
  911. * @param {String} transaction - 队列
  912. * @param {String} cid - 变更令id
  913. */
  914. async reCalcTp(transaction, cid) {
  915. const change = await transaction.get(this.ctx.service.change.tableName, { cid });
  916. let count = '';
  917. if (change.status === audit.flow.status.uncheck || change.status === audit.flow.status.back || change.status === audit.flow.status.revise) {
  918. count = '`camount`';
  919. } else if (change.status === audit.flow.status.checking || change.status === audit.flow.status.backnew) {
  920. count = '`spamount`';
  921. }
  922. if (count) {
  923. const sql = 'SELECT `unit_price`, ' + count + ' as `count` FROM ?? WHERE `cid` = ?';
  924. const params = [this.tableName, change.cid];
  925. const caLists = await transaction.query(sql, params);
  926. let tp = 0;
  927. const tpUnit = change.tp_decimal ? change.tp_decimal : this.ctx.tender.info.decimal.tp;
  928. for (const ca of caLists) {
  929. const catp = this.ctx.helper.round(this.ctx.helper.mul(ca.unit_price, ca.count), tpUnit);
  930. tp = this.ctx.helper.add(tp, catp);
  931. }
  932. console.log(tp);
  933. if (tp !== change.total_price) {
  934. const options = {
  935. where: {
  936. cid: change.cid,
  937. },
  938. };
  939. const change_update = {
  940. total_price: tp,
  941. };
  942. await transaction.update(this.ctx.service.change.tableName, change_update, options);
  943. }
  944. }
  945. }
  946. async updateToLedger(transaction, tid, cid) {
  947. // 找出本条变更属于新增部位的数据
  948. const allList = await transaction.select(this.tableName, { where: { tid, cid } });
  949. const result = [];
  950. const result2 = [];
  951. for (const l of allList) {
  952. const changeLedgerInfo = await transaction.get(this.ctx.service.changeLedger.tableName, { id: l.gcl_id });
  953. if (changeLedgerInfo && this._.findIndex(result, { id: l.gcl_id }) === -1) {
  954. result.push(changeLedgerInfo);
  955. }
  956. const changePosInfo = await transaction.get(this.ctx.service.changePos.tableName, { id: l.mx_id });
  957. if (changePosInfo) {
  958. result2.push(changePosInfo);
  959. }
  960. }
  961. // const sql = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.gcl_id WHERE b.tid = ? AND b.cid = ? GROUP BY a.id';
  962. // const sqlParam = [this.ctx.service.changeLedger.tableName, this.tableName, tid, cid];
  963. // const result = await transaction.query(sql, sqlParam);
  964. // const sql2 = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.mx_id WHERE b.tid = ? AND b.cid = ?';
  965. // const sqlParam2 = [this.ctx.service.changePos.tableName, this.tableName, tid, cid];
  966. // const result2 = await transaction.query(sql2, sqlParam2);
  967. if (result.length > 0 || result2.length > 0) {
  968. const changeLedgerGclIdList = this._.map(result, 'id');
  969. const changeLedgerIdList = this._.uniq(this._.map(result, 'ledger_pid'));// 父节点集合
  970. const needUpdateLedgerList = [];// 找出需要更新的原台账清单的id
  971. const needUpdateChangeLedgerList = [];// 找出需要更新的新台账清单的id
  972. const tpDecimal = this.ctx.tender.info.decimal.tp;
  973. // 要更新的ledger节点,数量及总数
  974. for (const data of result2) {
  975. if (this._.indexOf(changeLedgerGclIdList, data.lid) === -1) {
  976. const info = this._.find(needUpdateLedgerList, { id: data.lid });
  977. if (info) {
  978. info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty);
  979. info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty);
  980. info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty);
  981. info.quantity = this.ctx.helper.add(info.quantity, data.quantity);
  982. } else {
  983. needUpdateLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity });
  984. }
  985. } else {
  986. const info = this._.find(needUpdateChangeLedgerList, { id: data.lid });
  987. if (info) {
  988. info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty);
  989. info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty);
  990. info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty);
  991. info.quantity = this.ctx.helper.add(info.quantity, data.quantity);
  992. } else {
  993. needUpdateChangeLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity });
  994. }
  995. }
  996. }
  997. // 更新到result上
  998. if (needUpdateChangeLedgerList.length > 0) {
  999. for (const nucl of needUpdateChangeLedgerList) {
  1000. const now = this._.find(result, { id: nucl.id });
  1001. now.sgfh_qty = nucl.sgfh_qty;
  1002. now.sjcl_qty = nucl.sjcl_qty;
  1003. now.qtcl_qty = nucl.qtcl_qty;
  1004. now.quantity = nucl.quantity;
  1005. now.sgfh_tp = this.ctx.helper.mul(now.sgfh_qty, now.unit_price, tpDecimal);
  1006. now.sjcl_tp = this.ctx.helper.mul(now.sjcl_qty, now.unit_price, tpDecimal);
  1007. now.qtcl_tp = this.ctx.helper.mul(now.qtcl_qty, now.unit_price, tpDecimal);
  1008. now.total_price = this.ctx.helper.mul(now.quantity, now.unit_price, tpDecimal);
  1009. }
  1010. }
  1011. // 更新到ledger上
  1012. if (needUpdateLedgerList.length > 0) {
  1013. for (const nul of needUpdateLedgerList) {
  1014. const ledgerInfo = await this.ctx.service.ledger.getDataById(nul.id);
  1015. ledgerInfo.sgfh_qty = this.ctx.helper.add(ledgerInfo.sgfh_qty, nul.sgfh_qty);
  1016. ledgerInfo.sjcl_qty = this.ctx.helper.add(ledgerInfo.sjcl_qty, nul.sjcl_qty);
  1017. ledgerInfo.qtcl_qty = this.ctx.helper.add(ledgerInfo.qtcl_qty, nul.qtcl_qty);
  1018. ledgerInfo.quantity = this.ctx.helper.add(ledgerInfo.quantity, nul.quantity);
  1019. ledgerInfo.sgfh_tp = this.ctx.helper.mul(ledgerInfo.sgfh_qty, ledgerInfo.unit_price, tpDecimal);
  1020. ledgerInfo.sjcl_tp = this.ctx.helper.mul(ledgerInfo.sjcl_qty, ledgerInfo.unit_price, tpDecimal);
  1021. ledgerInfo.qtcl_tp = this.ctx.helper.mul(ledgerInfo.qtcl_qty, ledgerInfo.unit_price, tpDecimal);
  1022. ledgerInfo.total_price = this.ctx.helper.mul(ledgerInfo.quantity, ledgerInfo.unit_price, tpDecimal);
  1023. await transaction.update(this.ctx.service.ledger.tableName, ledgerInfo);
  1024. }
  1025. }
  1026. // 找出所有新增的父节点并插入到result中
  1027. for (const r of changeLedgerIdList) {
  1028. await this._findParents(transaction, tid, r, result);
  1029. }
  1030. // 插入到计量单元表,并删除变更的计量单元数据, 插入清单表,并删除变更的清单表
  1031. await this._insertByChangeRevise(transaction, tid, cid, result, result2);
  1032. // 更新标段总金额
  1033. const sumSql = 'SELECT Sum(total_price) As total_price, Sum(deal_tp) As deal_tp' +
  1034. ' FROM ' + this.ctx.service.ledger.tableName + this.ctx.helper.whereSql({ tender_id: tid });
  1035. const sum = await transaction.queryOne(sumSql);
  1036. await transaction.update(this.ctx.service.tender.tableName, {
  1037. id: tid,
  1038. total_price: sum.total_price,
  1039. deal_tp: sum.deal_tp,
  1040. });
  1041. // 清除修订及台账的maxLid缓存,防止树结构混乱
  1042. await this.ctx.service.reviseBills._removeCacheMaxLid(tid);
  1043. await this.ctx.service.ledger._removeCacheMaxLid(tid);
  1044. }
  1045. }
  1046. async _findParents(transaction, tid, id, result) {
  1047. const info = await transaction.get(this.ctx.service.changeLedger.tableName, { tender_id: tid, ledger_id: id });
  1048. if (info && this._.findIndex(result, { ledger_id: info.ledger_id }) === -1) {
  1049. result.push(info);
  1050. await this._findParents(transaction, tid, info.ledger_pid, result);
  1051. } else {
  1052. return;
  1053. }
  1054. }
  1055. async _insertByChangeRevise(transaction, tid, cid, ledgerList, posList) {
  1056. if (ledgerList.length > 0) {
  1057. const insertLedgerArr = [];
  1058. for (const l of ledgerList) {
  1059. const insertL = [
  1060. l.id, l.code, l.b_code, l.name, l.unit, l.source, l.remark, l.ledger_id,
  1061. l.ledger_pid, l.level, l.order, l.full_path, l.is_leaf, l.quantity, l.total_price,
  1062. l.unit_price, l.drawing_code, l.memo, l.dgn_qty1, l.dgn_qty2, l.deal_qty, l.deal_tp,
  1063. l.sgfh_qty, l.sgfh_tp, l.sjcl_qty, l.sjcl_tp, l.qtcl_qty, l.qtcl_tp, l.node_type, l.crid, l.ccid,
  1064. l.tender_id, l.sgfh_expr, l.sjcl_expr, l.qtcl_expr, l.check_calc,
  1065. l.ex_memo1, l.ex_memo2, l.ex_memo3,
  1066. ];
  1067. insertLedgerArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertL) + ')');
  1068. await transaction.delete(this.ctx.service.changeLedger.tableName, { id: l.id });
  1069. // 日志添加
  1070. await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, lid: l.id, name: l.name ? l.name : (l.code ? l.code : ''), create_time: new Date() });
  1071. }
  1072. const bSql = 'Insert Into ' +
  1073. this.ctx.service.ledger.tableName +
  1074. ' (id, code, b_code, name, unit, source, remark, ledger_id, ledger_pid, level, `order`, full_path, is_leaf,' +
  1075. ' quantity, total_price, unit_price, drawing_code, memo, dgn_qty1, dgn_qty2, deal_qty, deal_tp,' +
  1076. ' sgfh_qty, sgfh_tp, sjcl_qty, sjcl_tp, qtcl_qty, qtcl_tp, node_type, crid, ccid, tender_id,' +
  1077. ' sgfh_expr, sjcl_expr, qtcl_expr, check_calc,' +
  1078. ' ex_memo1, ex_memo2, ex_memo3) VALUES ' + insertLedgerArr.join(',') + ';';
  1079. await transaction.query(bSql, []);
  1080. }
  1081. if (posList.length > 0) {
  1082. const insertPosArr = [];
  1083. for (const p of posList) {
  1084. const insertp = [
  1085. p.id, p.tid, p.lid, p.name, p.drawing_code, p.quantity, p.add_stage, p.add_stage_order, p.add_times,
  1086. p.add_user, p.sgfh_qty, p.sjcl_qty, p.qtcl_qty, p.crid, p.ccid, p.porder, p.position,
  1087. p.sgfh_expr, p.sjcl_expr, p.qtcl_expr, p.real_qty,
  1088. p.ex_memo1, p.ex_memo2, p.ex_memo3,
  1089. ];
  1090. insertPosArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertp) + ')');
  1091. await transaction.delete(this.ctx.service.changePos.tableName, { id: p.id });
  1092. // 日志添加
  1093. await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, pid: p.id, name: p.name, create_time: new Date() });
  1094. }
  1095. const pSql =
  1096. 'Insert Into ' +
  1097. this.ctx.service.pos.tableName +
  1098. ' (id, tid, lid, name, drawing_code, quantity, add_stage, add_stage_order, add_times, add_user,' +
  1099. ' sgfh_qty, sjcl_qty, qtcl_qty, crid, ccid, porder, position, ' +
  1100. ' sgfh_expr, sjcl_expr, qtcl_expr, real_qty,' +
  1101. ' ex_memo1, ex_memo2, ex_memo3) VALUES ' + insertPosArr.join(',') + ';';
  1102. await transaction.query(pSql, []);
  1103. }
  1104. }
  1105. async checkedChangeBills(tid) {
  1106. const DefaultDecimal = this.ctx.tender.info.decimal.tp;
  1107. const sql = 'SELECT cal.*, c.tp_decimal FROM ' + this.ctx.service.changeAuditList.tableName + ' cal LEFT JOIN ' + this.ctx.service.change.tableName + ' c on cal.cid = c.cid where c.tid = ? and c.valid and c.status = ?';
  1108. const changeBills = await this.db.query(sql, [tid, audit.flow.status.checked]);
  1109. changeBills.forEach(x => {
  1110. x.tp_decimal = x.tp_decimal !== 0 ? x.tp_decimal : DefaultDecimal
  1111. });
  1112. return changeBills;
  1113. }
  1114. /**
  1115. * 交换两个清单的顺序
  1116. * @param {Number} id1 - 工料1的id
  1117. * @param {Number} id2 - 工料2的id
  1118. * @returns {Promise<void>}
  1119. */
  1120. async changeOrder(datas) {
  1121. if (!this.ctx.tender || !this.ctx.change) {
  1122. throw '数据错误';
  1123. }
  1124. // const bill1 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id1 });
  1125. // const bill2 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id2 });
  1126. // if (!bill1 || !bill2) {
  1127. // throw '数据错误';
  1128. // }
  1129. const transaction = await this.db.beginTransaction();
  1130. try {
  1131. // const order = bill1.order;
  1132. // bill1.order = bill2.order;
  1133. // bill2.order = order;
  1134. // await transaction.update(this.tableName, { id: bill1.id, order: bill1.order });
  1135. // await transaction.update(this.tableName, { id: bill2.id, order: bill2.order });
  1136. await transaction.updateRows(this.tableName, datas);
  1137. await transaction.commit();
  1138. return true;
  1139. } catch (err) {
  1140. await transaction.rollback();
  1141. throw err;
  1142. }
  1143. }
  1144. async setAllValuation(cid, ids, is_valuation) {
  1145. return await this.db.update(this.tableName, { is_valuation }, {
  1146. where: {
  1147. cid,
  1148. id: ids,
  1149. },
  1150. });
  1151. }
  1152. async getBillsSum(tid) {
  1153. const sql = 'SELECT gcl_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' +
  1154. ' FROM(' +
  1155. ' SELECT cal.gcl_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' +
  1156. ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
  1157. ' WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' +
  1158. ' GROUP BY cal.gcl_id' +
  1159. ' UNION ALL ' +
  1160. ' SELECT cal.gcl_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' +
  1161. ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
  1162. ' WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' +
  1163. ' GROUP BY cal.gcl_id) As TEMP' +
  1164. ' GROUP BY gcl_id';
  1165. return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]);
  1166. }
  1167. async getPosSum(tid) {
  1168. const sql = 'SELECT mx_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' +
  1169. ' FROM(' +
  1170. ' SELECT cal.mx_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' +
  1171. ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
  1172. ' WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' +
  1173. ' GROUP BY cal.mx_id' +
  1174. ' UNION ALL ' +
  1175. ' SELECT cal.mx_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' +
  1176. ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
  1177. ' WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' +
  1178. ' GROUP BY cal.mx_id) As TEMP' +
  1179. ' GROUP BY mx_id';
  1180. return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]);
  1181. }
  1182. }
  1183. return ChangeAuditList;
  1184. };