change.js 63 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date 2018/8/14
  7. * @version
  8. */
  9. const audit = require('../const/audit');
  10. const fs = require('fs');
  11. const path = require('path');
  12. const changeConst = require('../const/change');
  13. const smsTypeConst = require('../const/sms_type');
  14. const SMS = require('../lib/sms');
  15. const SmsAliConst = require('../const/sms_alitemplate');
  16. const wxConst = require('../const/wechat_template');
  17. const pushType = require('../const/audit').pushType;
  18. module.exports = app => {
  19. class Change extends app.BaseService {
  20. /**
  21. * 构造函数
  22. *
  23. * @param {Object} ctx - egg全局变量
  24. * @return {void}
  25. */
  26. constructor(ctx) {
  27. super(ctx);
  28. this.tableName = 'change';
  29. }
  30. /**
  31. * 查找数据
  32. *
  33. * @param {Object} data - 筛选表单中的get数据
  34. * @return {void}
  35. */
  36. searchFilter(data) {
  37. this.initSqlBuilder();
  38. // this.sqlBuilder.columns = ['id', 'username', 'real_name', 'create_time', 'last_login', 'login_ip',
  39. // 'group_id', 'token', 'can_login'];
  40. data.type = parseInt(data.status);
  41. if (data.keyword !== undefined) {
  42. switch (data.type) {
  43. // 用户名
  44. case 1:
  45. this.sqlBuilder.setAndWhere('username', {
  46. value: this.db.escape(data.keyword + '%'),
  47. operate: 'like',
  48. });
  49. break;
  50. // 姓名
  51. case 2:
  52. this.sqlBuilder.setAndWhere('real_name', {
  53. value: this.db.escape(data.keyword + '%'),
  54. operate: 'like',
  55. });
  56. break;
  57. // 联系电话
  58. case 3:
  59. this.sqlBuilder.setAndWhere('telephone', {
  60. value: this.db.escape(data.keyword + '%'),
  61. operate: 'like',
  62. });
  63. break;
  64. default:
  65. break;
  66. }
  67. }
  68. // 办事处筛选
  69. if (data.office !== undefined && data.office !== '') {
  70. this.sqlBuilder.setAndWhere('office', {
  71. value: this.db.escape(data.office),
  72. operate: '=',
  73. });
  74. }
  75. }
  76. async add(tenderId, userId, code, name) {
  77. const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?))';
  78. const sqlParam = [this.tableName, tenderId, code, audit.flow.status.checked, code, audit.flow.status.checked];
  79. const codeCount = await this.db.queryOne(sql, sqlParam);
  80. const count = codeCount.count;
  81. if (count > 0) {
  82. throw '变更令号重复';
  83. }
  84. // 初始化事务
  85. this.transaction = await this.db.beginTransaction();
  86. let result = false;
  87. try {
  88. const cid = this.uuid.v4();
  89. const change = {
  90. cid,
  91. tid: tenderId,
  92. uid: userId,
  93. status: audit.flow.status.uncheck,
  94. times: 1,
  95. valid: true,
  96. in_time: new Date(),
  97. code,
  98. name,
  99. };
  100. const operate = await this.transaction.insert(this.tableName, change);
  101. if (operate.affectedRows <= 0) {
  102. throw '新建变更令数据失败';
  103. }
  104. // 把提交人信息添加到zh_change_audit
  105. const userInfo = await this.ctx.service.projectAccount.getDataById(userId);
  106. const changeaudit = [
  107. {
  108. tid: tenderId,
  109. cid,
  110. uid: userId,
  111. name: userInfo.name,
  112. jobs: userInfo.role,
  113. company: userInfo.company,
  114. times: 1,
  115. usite: 0,
  116. usort: 0,
  117. status: 2,
  118. },
  119. ];
  120. // 并把之前存在的变更令审批人添加到zh_change_audit
  121. // 先找出标段最近存在的变更令审批人的变更令info
  122. const changeInfo = await this.ctx.service.change.getHaveAuditLastInfo(tenderId);
  123. if (changeInfo) {
  124. // 再获取非原报审批人
  125. const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeInfo.cid, changeInfo.times);
  126. let sort = 1;
  127. for (const audit of auditList) {
  128. if (audit.usite !== 0) {
  129. const oneaudit = {
  130. tid: tenderId,
  131. cid,
  132. uid: audit.uid,
  133. name: audit.name,
  134. jobs: audit.jobs,
  135. company: audit.company,
  136. times: 1,
  137. usite: audit.usite,
  138. usort: sort++,
  139. status: 1,
  140. };
  141. changeaudit.push(oneaudit);
  142. }
  143. }
  144. }
  145. await this.transaction.insert(this.ctx.service.changeAudit.tableName, changeaudit);
  146. result = change;
  147. this.transaction.commit();
  148. } catch (error) {
  149. console.log(error);
  150. // 回滚
  151. await this.transaction.rollback();
  152. }
  153. return result;
  154. }
  155. async getHaveAuditLastInfo(tenderId) {
  156. const sql = 'SELECT * FROM ?? as a LEFT JOIN ?? as b ON a.`cid` = b.`cid` WHERE a.`tid` = ? AND b.`usite` > 0 ORDER BY a.`in_time` DESC';
  157. const sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId];
  158. return await this.db.queryOne(sql, sqlParam);
  159. }
  160. async pendingDatas(tenderId, userId) {
  161. return await this.getAllDataByCondition({
  162. tid: tenderId,
  163. uid: userId,
  164. status: audit.flow.status.checking,
  165. });
  166. }
  167. async uncheckDatas(tenderId, userId) {
  168. return await this.getAllDataByCondition({
  169. tid: tenderId,
  170. uid: userId,
  171. status: audit.flow.status.uncheck,
  172. });
  173. }
  174. async checkingDatas(tenderId, userId) {
  175. return await this.getAllDataByCondition({
  176. tid: tenderId,
  177. uid: userId,
  178. status: audit.flow.status.checking,
  179. });
  180. }
  181. async checkedDatas(tenderId, userId) {
  182. return await this.getAllDataByCondition({
  183. tid: tenderId,
  184. uid: userId,
  185. status: audit.flow.status.checked,
  186. });
  187. }
  188. async checkNoDatas(tenderId, userId) {
  189. return await this.getAllDataByCondition({
  190. tid: tenderId,
  191. uid: userId,
  192. status: audit.flow.status.checkNo,
  193. });
  194. }
  195. async checkNoCount(tenderId, userId) {
  196. return await this.count({
  197. tid: tenderId,
  198. uid: userId,
  199. status: audit.flow.status.checkNo,
  200. });
  201. }
  202. /**
  203. * 获取变更令列表
  204. * @param {int} tenderId - 标段id
  205. * @param {int} status - 状态
  206. * @param {int} hadlimit - 分页
  207. * @return {object} list - 列表
  208. */
  209. async getListByStatus(tenderId, status = 0, hadlimit = 1) {
  210. let sql = '';
  211. let sqlParam = '';
  212. switch (status) {
  213. case 0: // 包含你的所有变更令
  214. sql =
  215. 'SELECT a.* FROM ?? AS a WHERE a.tid = ? AND ' +
  216. '(a.uid = ? OR (a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid)) OR a.status = ? ) ORDER BY a.in_time DESC';
  217. sqlParam = [
  218. this.tableName,
  219. tenderId,
  220. this.ctx.session.sessionUser.accountId,
  221. audit.flow.status.uncheck,
  222. this.ctx.service.changeAudit.tableName,
  223. this.ctx.session.sessionUser.accountId,
  224. audit.flow.status.checked,
  225. ];
  226. break;
  227. case 1: // 待处理(你的)
  228. sql = 'SELECT a.* FROM ?? as a WHERE cid in(SELECT b.cid FROM ?? as b WHERE tid = ? AND uid = ? AND status = ?) ORDER BY in_time DESC';
  229. sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.flow.auditStatus.checking];
  230. break;
  231. case 5: // 待上报(所有的)PS:取未上报和退回的变更令
  232. sql =
  233. 'SELECT a.* FROM ?? AS a WHERE ' +
  234. 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? GROUP BY b.cid) AND ' +
  235. '(a.status = ? OR a.status = ?) AND a.tid = ? ORDER BY a.in_time DESC';
  236. sqlParam = [
  237. this.tableName,
  238. this.ctx.service.changeAudit.tableName,
  239. this.ctx.session.sessionUser.accountId,
  240. audit.flow.status.uncheck,
  241. audit.flow.status.back,
  242. tenderId,
  243. ];
  244. break;
  245. case 2: // 进行中(所有的)
  246. case 4: // 终止(所有的)
  247. sql =
  248. 'SELECT a.* FROM ?? AS a WHERE ' +
  249. 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND ' +
  250. 'a.status = ? AND a.tid = ? ORDER BY a.in_time DESC';
  251. sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId];
  252. break;
  253. case 3: // 已完成(所有的)
  254. sql = 'SELECT a.* FROM ?? AS a WHERE a.status = ? AND a.tid = ? ORDER BY a.in_time DESC';
  255. sqlParam = [this.tableName, status, tenderId];
  256. break;
  257. default:
  258. break;
  259. }
  260. if (hadlimit) {
  261. const limit = this.app.config.pageSize;
  262. const offset = limit * (this.ctx.page - 1);
  263. const limitString = offset >= 0 ? offset + ',' + limit : limit;
  264. sql += ' LIMIT ' + limitString;
  265. }
  266. const list = await this.db.query(sql, sqlParam);
  267. return list;
  268. }
  269. /**
  270. * 获取变更令个数
  271. * @param {int} tenderId - 标段id
  272. * @param {int} status - 状态
  273. * @return {void}
  274. */
  275. async getCountByStatus(tenderId, status) {
  276. switch (status) {
  277. case 0: // 包含你的所有变更令
  278. const sql =
  279. 'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND ' +
  280. '(a.uid = ? OR a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))';
  281. const sqlParam = [
  282. this.tableName,
  283. tenderId,
  284. this.ctx.session.sessionUser.accountId,
  285. this.ctx.service.changeAudit.tableName,
  286. this.ctx.session.sessionUser.accountId,
  287. ];
  288. const result = await this.db.query(sql, sqlParam);
  289. return result[0].count;
  290. case 1: // 待处理(你的)
  291. return await this.ctx.service.changeAudit.count({
  292. tid: tenderId,
  293. uid: this.ctx.session.sessionUser.accountId,
  294. status: 2,
  295. });
  296. case 5: // 待上报(所有的)PS:取未上报和退回的变更令
  297. const sql2 =
  298. 'SELECT count(*) AS count FROM ?? AS a WHERE ' +
  299. 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) ' +
  300. 'AND (a.status = ? OR a.status = ?) AND a.tid = ?';
  301. const sqlParam2 = [
  302. this.tableName,
  303. this.ctx.service.changeAudit.tableName,
  304. this.ctx.session.sessionUser.accountId,
  305. audit.flow.status.uncheck,
  306. audit.flow.status.back,
  307. tenderId,
  308. ];
  309. const result2 = await this.db.query(sql2, sqlParam2);
  310. return result2[0].count;
  311. case 2: // 进行中(所有的)
  312. case 4: // 终止(所有的)
  313. const sql3 =
  314. 'SELECT count(*) AS count FROM ?? AS a WHERE ' +
  315. 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND a.status = ? AND a.tid = ?';
  316. const sqlParam3 = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId];
  317. const result3 = await this.db.query(sql3, sqlParam3);
  318. return result3[0].count;
  319. case 3: // 已完成(所有的)
  320. const sql4 = 'SELECT count(*) AS count FROM ?? WHERE status = ? AND tid = ?';
  321. const sqlParam4 = [this.tableName, status, tenderId];
  322. const result4 = await this.db.query(sql4, sqlParam4);
  323. return result4[0].count;
  324. default:
  325. break;
  326. }
  327. }
  328. /**
  329. * 上报或重新上报或保存修改功能
  330. * @param {int} postData - 表单提交的数据
  331. * @param {int} tenderId - 标段id
  332. * @return {void}
  333. */
  334. async save(postData, tenderId) {
  335. const tenderInfo = await this.ctx.service.tenderInfo.getTenderInfo(tenderId);
  336. // 初始化事务
  337. this.transaction = await this.db.beginTransaction();
  338. let result = false;
  339. try {
  340. // 变更令信息
  341. const changeInfo = await this.getDataByCondition({ cid: postData.cid });
  342. // 该变更令原报人信息
  343. const lastUser = await this.ctx.service.changeAudit.getLastUser(changeInfo.cid, changeInfo.times, 0);
  344. // 先删除本次原有的变更审批人和清单
  345. await this.ctx.service.changeAudit.deleteAuditData(this.transaction, changeInfo.cid, changeInfo.times);
  346. await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid: changeInfo.cid });
  347. let change_status = false;
  348. // 获取变更令提交状态
  349. if (postData.changestatus !== undefined && parseInt(postData.changestatus) === 1) {
  350. change_status = true;
  351. // 更新原报人审批状态
  352. await this.transaction.update(this.ctx.service.changeAudit.tableName, {
  353. id: lastUser.id,
  354. status: audit.flow.auditStatus.checked,
  355. sin_time: new Date(),
  356. });
  357. }
  358. // 再插入postData里的变更审批人和清单
  359. if (postData.changeaudit !== undefined && postData.changeaudit !== '') {
  360. const changeAudit = postData.changeaudit.split(',');
  361. const insertCA = [];
  362. let uSite = 1;
  363. let uSort = parseInt(lastUser.usort) + 1;
  364. for (const [index, ca] of changeAudit.entries()) {
  365. const auditInfo = ca.split('/%/');
  366. const uStatus = change_status && index === 0 ? audit.flow.auditStatus.checking : audit.flow.auditStatus.uncheck;
  367. const sin_time = change_status && index === 0 ? new Date() : null;
  368. const caArray = {
  369. tid: tenderId,
  370. cid: changeInfo.cid,
  371. uid: auditInfo[0],
  372. name: auditInfo[1],
  373. jobs: auditInfo[2],
  374. company: auditInfo[3],
  375. times: changeInfo.times,
  376. usite: uSite,
  377. usort: uSort,
  378. status: uStatus,
  379. sin_time,
  380. };
  381. uSite++;
  382. uSort++;
  383. insertCA.push(caArray);
  384. console.log(change_status, index);
  385. // 添加短信通知-需要审批提醒功能
  386. if (change_status && index === 0) {
  387. const sms = new SMS(this.ctx);
  388. const code = await sms.contentChange(changeInfo.code);
  389. const shenpiUrl = await this.ctx.helper.urlToShort(
  390. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi'
  391. );
  392. await this.ctx.helper.sendAliSms(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
  393. biangeng: code,
  394. code: shenpiUrl,
  395. });
  396. // 微信模板通知
  397. const wechatData = {
  398. wap_url: shenpiUrl,
  399. status: wxConst.status.check,
  400. tips: wxConst.tips.check,
  401. code: this.ctx.session.sessionProject.code,
  402. c_name: changeInfo.name,
  403. };
  404. await this.ctx.helper.sendWechat(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
  405. }
  406. }
  407. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insertCA);
  408. }
  409. let changeList = [];
  410. if (postData.changelist !== undefined && postData.changelist !== '') {
  411. changeList = postData.changelist.split('^_^');
  412. }
  413. let changeWhiteList = [];
  414. if (postData.changewhitelist !== undefined && postData.changewhitelist !== '') {
  415. changeWhiteList = postData.changewhitelist.split('^_^');
  416. }
  417. changeList.push.apply(changeList, changeWhiteList);
  418. const insertCL = [];
  419. let total_price = 0;
  420. if (changeList.length > 0) {
  421. for (const cl of changeList) {
  422. const clInfo = cl.split('*;*');
  423. const clArray = {
  424. tid: tenderId,
  425. cid: changeInfo.cid,
  426. lid: clInfo[8],
  427. code: clInfo[0],
  428. name: clInfo[1],
  429. bwmx: clInfo[2],
  430. unit: clInfo[3],
  431. unit_price: clInfo[4],
  432. oamount: clInfo[5],
  433. camount: clInfo[6],
  434. samount: '',
  435. detail: clInfo[7],
  436. spamount: clInfo[6],
  437. xmj_code: clInfo[9] !== '' ? clInfo[9] : null,
  438. xmj_jldy: clInfo[10] !== '' ? clInfo[10] : null,
  439. };
  440. if (clInfo[4] === '') {
  441. delete clArray.unit_price;
  442. }
  443. insertCL.push(clArray);
  444. total_price = this.ctx.helper.accAdd(total_price, this.ctx.helper.mul(clArray.unit_price, clArray.spamount, tenderInfo.decimal.tp));
  445. }
  446. await this.transaction.insert(this.ctx.service.changeAuditList.tableName, insertCL);
  447. }
  448. // 修改变更令基本数据
  449. const cArray = {
  450. code: postData.code,
  451. name: postData.name,
  452. peg: postData.peg,
  453. org_name: postData.org_name,
  454. org_code: postData.org_code,
  455. new_name: postData.new_name,
  456. new_code: postData.new_code,
  457. content: postData.content,
  458. basis: postData.basis,
  459. expr: postData.expr,
  460. memo: postData.memo,
  461. type: postData.type.join(','),
  462. class: postData.class,
  463. quality: postData.quality,
  464. company: postData.company,
  465. charge: postData.charge,
  466. total_price,
  467. };
  468. const options = {
  469. where: {
  470. cid: changeInfo.cid,
  471. },
  472. };
  473. if (change_status) {
  474. cArray.status = audit.flow.status.checking;
  475. cArray.cin_time = Date.parse(new Date()) / 1000;
  476. }
  477. await this.transaction.update(this.tableName, cArray, options);
  478. await this.transaction.commit();
  479. result = true;
  480. } catch (error) {
  481. await this.transaction.rollback();
  482. result = false;
  483. }
  484. return result;
  485. }
  486. /**
  487. * 审批通过
  488. * @param {Number} pid 项目id
  489. * @param {int} postData - 表单提交的数据
  490. * @param {int} changeData - 变更令的数据
  491. * @return {void}
  492. */
  493. async approvalSuccess(pid, postData, changeData) {
  494. let tenderInfo;
  495. // 初始化事务
  496. this.transaction = await this.db.beginTransaction();
  497. let result = false;
  498. try {
  499. // 获取所有审核人列表
  500. const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid);
  501. console.log('auditors', auditors);
  502. console.log('postData', postData);
  503. // 添加到消息推送表
  504. const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId);
  505. const records = [];
  506. auditors.forEach(auditor => {
  507. records.push({
  508. pid,
  509. type: pushType.change,
  510. uid: auditor.uid,
  511. status: audit.flow.status.checked,
  512. content: noticeContent,
  513. });
  514. });
  515. await this.transaction.insert('zh_notice', records);
  516. // 设置审批人通过
  517. const audit_update = {
  518. id: postData.audit_id,
  519. sdesc: postData.sdesc,
  520. status: audit.flow.auditStatus.checked,
  521. sin_time: new Date(),
  522. };
  523. const change_update = {
  524. w_code: postData.w_code,
  525. status: audit.flow.status.checking,
  526. cin_time: Date.parse(new Date()) / 1000,
  527. };
  528. await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
  529. // 清单数据更新
  530. const bills_list = postData.bills_list.split(',');
  531. let total_price = 0;
  532. for (const bl of bills_list) {
  533. const listInfo = bl.split('_');
  534. const lid = listInfo[0];
  535. const amount = listInfo[1];
  536. const changeListInfo = await this.ctx.service.changeAuditList.getDataById(lid);
  537. if (!tenderInfo) {
  538. tenderInfo = await this.ctx.service.tenderInfo.getTenderInfo(changeListInfo.tid);
  539. }
  540. if (changeListInfo !== undefined) {
  541. total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(changeListInfo.unit_price, amount, tenderInfo.decimal.tp));
  542. const audit_amount = changeListInfo.audit_amount !== null && changeListInfo.audit_amount !== '' ? changeListInfo.audit_amount.split(',') : [];
  543. audit_amount.push(amount);
  544. const list_update = {
  545. id: lid,
  546. audit_amount: audit_amount.join(','),
  547. spamount: parseFloat(amount),
  548. };
  549. if (postData.audit_next_id === undefined) {
  550. list_update.samount = amount;
  551. }
  552. await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update);
  553. }
  554. }
  555. if (postData.audit_next_id === undefined) {
  556. // 变更令审批完成
  557. change_update.status = audit.flow.status.checked;
  558. change_update.p_code = postData.p_code;
  559. change_update.sin_time = Date.parse(new Date()) / 1000;
  560. // 添加短信通知-审批通过提醒功能
  561. // const mobile_array = [];
  562. const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeData.cid, changeData.times);
  563. const users = this._.map(auditList, 'uid');
  564. const sms = new SMS(this.ctx);
  565. const code = await sms.contentChange(changeData.code);
  566. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, {
  567. biangeng: code,
  568. status: SmsAliConst.status.success,
  569. });
  570. // 微信模板通知
  571. const shenpiUrl = await this.ctx.helper.urlToShort(
  572. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi'
  573. );
  574. const wechatData = {
  575. wap_url: shenpiUrl,
  576. status: wxConst.status.success,
  577. tips: wxConst.tips.success,
  578. code: this.ctx.session.sessionProject.code,
  579. c_name: changeData.name,
  580. };
  581. await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData);
  582. } else {
  583. // 设置下一个审批人为审批状态
  584. const nextAudit_update = {
  585. id: postData.audit_next_id,
  586. status: audit.flow.auditStatus.checking,
  587. sin_time: new Date(),
  588. };
  589. await this.transaction.update(this.ctx.service.changeAudit.tableName, nextAudit_update);
  590. // 添加短信通知-需要审批提醒功能
  591. const nextAuditData = await this.ctx.service.changeAudit.getDataById(postData.audit_next_id);
  592. const sms = new SMS(this.ctx);
  593. const code = await sms.contentChange(changeData.code);
  594. const shenpiUrl = await this.ctx.helper.urlToShort(
  595. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi'
  596. );
  597. await this.ctx.helper.sendAliSms(nextAuditData.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
  598. biangeng: code,
  599. code: shenpiUrl,
  600. });
  601. // 微信模板通知
  602. const wechatData = {
  603. wap_url: shenpiUrl,
  604. status: wxConst.status.check,
  605. tips: wxConst.tips.check,
  606. code: this.ctx.session.sessionProject.code,
  607. c_name: changeData.name,
  608. };
  609. await this.ctx.helper.sendWechat(nextAuditData.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
  610. }
  611. change_update.total_price = total_price;
  612. const options = {
  613. where: {
  614. cid: postData.change_id,
  615. },
  616. };
  617. await this.transaction.update(this.tableName, change_update, options);
  618. await this.transaction.commit();
  619. result = true;
  620. } catch (error) {
  621. console.log(error);
  622. await this.transaction.rollback();
  623. result = false;
  624. }
  625. return result;
  626. }
  627. /**
  628. * 审批终止
  629. * @param {int} postData - 表单提交的数据
  630. * @return {void}
  631. */
  632. async approvalStop(postData) {
  633. // 初始化事务
  634. this.transaction = await this.db.beginTransaction();
  635. let result = false;
  636. try {
  637. // 设置审批人终止
  638. const audit_update = {
  639. id: postData.audit_id,
  640. sdesc: postData.sdesc,
  641. status: 4,
  642. sin_time: new Date(),
  643. };
  644. await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
  645. // 设置变更令终止
  646. const change_update = {
  647. w_code: postData.w_code,
  648. status: 4,
  649. cin_time: Date.parse(new Date()) / 1000,
  650. };
  651. const options = {
  652. where: {
  653. cid: postData.change_id,
  654. },
  655. };
  656. await this.transaction.update(this.tableName, change_update, options);
  657. await this.transaction.commit();
  658. result = true;
  659. } catch (error) {
  660. await this.transaction.rollback();
  661. result = false;
  662. }
  663. return result;
  664. }
  665. /**
  666. * 审批退回到原报人
  667. * @param {Number} pid 项目id
  668. * @param {int} postData - 表单提交的数据
  669. * @param {int} changeData - 变更令的数据
  670. * @return {void}
  671. */
  672. async approvalBack(pid, postData, changeData) {
  673. // 初始化事务
  674. this.transaction = await this.db.beginTransaction();
  675. let result = false;
  676. try {
  677. // 获取所有审核人列表
  678. const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid);
  679. // 添加到消息推送表
  680. const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId);
  681. const records = [];
  682. auditors.forEach(auditor => {
  683. records.push({
  684. pid,
  685. type: pushType.change,
  686. uid: auditor.uid,
  687. status: audit.flow.status.backnew,
  688. content: noticeContent,
  689. });
  690. });
  691. await this.transaction.insert('zh_notice', records);
  692. const changeInfo = await this.getDataByCondition({ cid: postData.change_id });
  693. const tenderInfo = await this.ctx.service.tenderInfo.getTenderInfo(changeInfo.tid);
  694. // 设置审批人退回
  695. const audit_update = {
  696. id: postData.audit_id,
  697. sdesc: postData.sdesc,
  698. status: audit.flow.auditStatus.back,
  699. sin_time: new Date(),
  700. };
  701. await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
  702. // 新增新一次的审批人列表
  703. // 获取当前次数审批人列表
  704. const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeInfo.cid, changeInfo.times);
  705. const lastauditInfo = await this.ctx.service.changeAudit.getLastUser(changeInfo.cid, changeInfo.times, 1, 0);
  706. let usort = lastauditInfo.usort + 1;
  707. const newTimes = changeInfo.times + 1;
  708. const insert_audit_array = [];
  709. for (const al of auditList) {
  710. const insert_audit = {
  711. tid: al.tid,
  712. cid: al.cid,
  713. uid: al.uid,
  714. name: al.name,
  715. jobs: al.jobs,
  716. company: al.company,
  717. times: newTimes,
  718. usite: al.usite,
  719. usort,
  720. status: al.usite !== 0 ? audit.flow.auditStatus.uncheck : audit.flow.auditStatus.checking,
  721. };
  722. insert_audit_array.push(insert_audit);
  723. usort++;
  724. }
  725. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit_array);
  726. // 变更金额也退回
  727. const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({
  728. where: { cid: changeInfo.cid },
  729. });
  730. let total_price = 0;
  731. for (const cl of changeList) {
  732. total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, cl.camount, tenderInfo.decimal.tp));
  733. }
  734. // 设置变更令退回
  735. const change_update = {
  736. w_code: postData.w_code,
  737. status: audit.flow.status.back,
  738. times: newTimes,
  739. cin_time: Date.parse(new Date()) / 1000,
  740. total_price,
  741. };
  742. const options = {
  743. where: {
  744. cid: postData.change_id,
  745. },
  746. };
  747. await this.transaction.update(this.tableName, change_update, options);
  748. await this.transaction.commit();
  749. result = true;
  750. const users = this._.map(insert_audit_array, 'uid');
  751. const sms = new SMS(this.ctx);
  752. const code = await sms.contentChange(changeData.code);
  753. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, {
  754. biangeng: code,
  755. status: SmsAliConst.status.back,
  756. });
  757. // 微信模板通知
  758. const shenpiUrl = await this.ctx.helper.urlToShort(
  759. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi'
  760. );
  761. const wechatData = {
  762. wap_url: shenpiUrl,
  763. status: wxConst.status.back,
  764. tips: wxConst.tips.back,
  765. code: this.ctx.session.sessionProject.code,
  766. c_name: changeInfo.name,
  767. };
  768. await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData);
  769. } catch (error) {
  770. await this.transaction.rollback();
  771. result = false;
  772. }
  773. return result;
  774. }
  775. /**
  776. * 审批退回到上一个审批人
  777. * @param {Number} pid 项目id
  778. * @param {int} postData - 表单提交的数据
  779. * @param {int} changeData - 变更令的数据
  780. * @return {void}
  781. */
  782. async approvalBackNew(pid, postData, changeData) {
  783. // 初始化事务
  784. this.transaction = await this.db.beginTransaction();
  785. let result = false;
  786. try {
  787. // 获取所有审核人列表
  788. const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid);
  789. // 添加到消息推送表
  790. const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId);
  791. const records = [];
  792. auditors.forEach(auditor => {
  793. records.push({
  794. pid,
  795. type: pushType.change,
  796. uid: auditor.uid,
  797. status: audit.flow.status.back,
  798. content: noticeContent,
  799. });
  800. });
  801. await this.transaction.insert('zh_notice', records);
  802. const changeInfo = await this.getDataByCondition({ cid: postData.change_id });
  803. const tenderInfo = await this.ctx.service.tenderInfo.getTenderInfo(changeInfo.tid);
  804. // 设置审批人退回
  805. const audit_update = {
  806. id: postData.audit_id,
  807. sdesc: postData.sdesc,
  808. status: audit.flow.auditStatus.backnew,
  809. sin_time: new Date(),
  810. };
  811. await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
  812. // 获取当前审批人信息
  813. const auditInfo = await this.ctx.service.changeAudit.getDataById(postData.audit_id);
  814. // 获取当前次数审批人列表
  815. const auditList = await this.ctx.service.changeAudit.getNextAuditList(changeInfo.cid, auditInfo.usort);
  816. let usort = auditInfo.usort + 1;
  817. // 获取上一个审批人信息
  818. const lastauditInfo = await this.ctx.service.changeAudit.getDataById(postData.audit_last_id);
  819. // 新增2个审批人到审批列表中
  820. const insert_audit1 = {
  821. tid: lastauditInfo.tid,
  822. cid: lastauditInfo.cid,
  823. uid: lastauditInfo.uid,
  824. name: lastauditInfo.name,
  825. jobs: lastauditInfo.jobs,
  826. company: lastauditInfo.company,
  827. times: lastauditInfo.times,
  828. usite: lastauditInfo.usite,
  829. usort,
  830. status: audit.flow.auditStatus.checking,
  831. sin_time: new Date(),
  832. };
  833. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit1);
  834. usort++;
  835. // 新增2个审批人到审批列表中
  836. const insert_audit2 = {
  837. tid: auditInfo.tid,
  838. cid: auditInfo.cid,
  839. uid: auditInfo.uid,
  840. name: auditInfo.name,
  841. jobs: auditInfo.jobs,
  842. company: auditInfo.company,
  843. times: auditInfo.times,
  844. usite: auditInfo.usite,
  845. usort,
  846. status: audit.flow.auditStatus.uncheck,
  847. };
  848. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit2);
  849. // 把接下未审批的审批人排序都加2
  850. for (const al of auditList) {
  851. const audit_update = {
  852. id: al.id,
  853. usort: al.usort + 2,
  854. };
  855. await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update);
  856. }
  857. // 审批列表数据也要回退
  858. const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({
  859. where: { cid: changeInfo.cid },
  860. });
  861. let total_price = 0;
  862. for (const cl of changeList) {
  863. const audit_amount = cl.audit_amount.split(',');
  864. const last_amount = audit_amount[audit_amount.length - 1];
  865. audit_amount.splice(-1, 1);
  866. const list_update = {
  867. id: cl.id,
  868. audit_amount: audit_amount.join(','),
  869. spamount: parseFloat(last_amount),
  870. };
  871. total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), tenderInfo.decimal.tp));
  872. await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update);
  873. }
  874. // 设置变更令退回
  875. const change_update = {
  876. w_code: postData.w_code,
  877. status: audit.flow.status.backnew,
  878. cin_time: Date.parse(new Date()) / 1000,
  879. total_price,
  880. };
  881. const options = {
  882. where: {
  883. cid: postData.change_id,
  884. },
  885. };
  886. await this.transaction.update(this.tableName, change_update, options);
  887. await this.transaction.commit();
  888. result = true;
  889. const sms = new SMS(this.ctx);
  890. const code = await sms.contentChange(changeData.code);
  891. const shenpiUrl = await this.ctx.helper.urlToShort(
  892. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi'
  893. );
  894. await this.ctx.helper.sendAliSms(lastauditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
  895. biangeng: code,
  896. code: shenpiUrl,
  897. });
  898. // 微信模板通知
  899. const wechatData = {
  900. wap_url: shenpiUrl,
  901. status: wxConst.status.check,
  902. tips: wxConst.tips.check,
  903. code: this.ctx.session.sessionProject.code,
  904. c_name: changeInfo.name,
  905. };
  906. await this.ctx.helper.sendWechat(lastauditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
  907. } catch (error) {
  908. await this.transaction.rollback();
  909. result = false;
  910. }
  911. return result;
  912. }
  913. /**
  914. * 查询可用的变更令
  915. * @param bills - 查询的清单
  916. * @param pos - 查询的部位
  917. * @return {Promise<*>} - 可用的变更令列表
  918. */
  919. async getValidChanges(tid, bills, pos) {
  920. const self = this;
  921. const getFilterPart = function(field, value) {
  922. return value
  923. ? field + ' = ' + self.db.escape(value)
  924. : self.db.format("(?? = null or ?? = '')", [field, field]);
  925. };
  926. const timesLen = 100;
  927. const filter = getFilterPart('cb.code', bills.b_code) +
  928. ' And ' + getFilterPart('cb.name', bills.name) +
  929. ' And ' + getFilterPart('cb.unit', bills.unit) +
  930. ' And cb.`unit_price` = ' + this.db.escape(bills.unit_price) +
  931. (pos ? getFilterPart('cb.bwmx', pos.name) : '');
  932. const sql =
  933. 'SELECT c.cid, c.code, c.name, c.w_code, c.p_code, c.peg, c.org_name, c.org_code, c.new_name, c.new_code,' +
  934. ' c.content, c.basis, c.memo, c.type, c.class, c.quality, c.company, c.charge, ' +
  935. ' cb.id As cbid, cb.code As b_code, cb.name As b_name, cb.unit As b_unit, cb.samount As b_amount, cb.detail As b_detail, cb.bwmx As b_bwmx, ' +
  936. ' scb.used_amount' +
  937. ' FROM ' + this.tableName + ' As c ' +
  938. ' Left Join ' + this.ctx.service.changeAuditList.tableName + ' As cb On c.cid = cb.cid ' +
  939. ' Left Join (' +
  940. ' SELECT SUM(sc.qty) As used_amount, sc.cbid' +
  941. ' FROM ' + this.ctx.service.stageChange.tableName + ' As sc' +
  942. ' INNER JOIN (SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, cbid, sid ' +
  943. ' FROM ' + this.ctx.service.stageChange.tableName +
  944. ' WHERE tid = ?' +
  945. ' GROUP BY cbid, sid' +
  946. ' ) As MF' +
  947. ' ON (sc.stimes * ' + timesLen + ' + sc.sorder) = MF.flow And sc.cbid = MF.cbid And sc.sid = MF.sid' +
  948. ' GROUP BY sc.cbid' +
  949. ' ) As scb ON cb.id = scb.cbid' +
  950. ' WHERE c.tid = ? And c.status = ? And c.valid And ' + filter +
  951. ' ORDER BY c.in_time';
  952. const sqlParam = [tid, tid, audit.flow.status.checked];
  953. const changes = await this.db.query(sql, sqlParam);
  954. for (const c of changes) {
  955. const aSql = 'SELECT ca.*, pa.name As u_name, pa.role As u_role ' +
  956. ' FROM ?? As ca ' +
  957. ' Left Join ?? As pa ' +
  958. ' On ca.uid = pa.id ' +
  959. ' Where ca.cid = ?';
  960. const aSqlParam = [this.ctx.service.changeAtt.tableName, this.ctx.service.projectAccount.tableName, c.cid];
  961. c.attachments = await this.db.query(aSql, aSqlParam);
  962. }
  963. return changes;
  964. }
  965. /**
  966. * 查询审批人可用的变更令
  967. * @param bills - 查询的清单
  968. * @param pos - 查询的部位
  969. * @return {Promise<*>} - 可用的变更令列表
  970. */
  971. async getAuditValidChanges(tid, bills, pos, times, order) {
  972. const timesLen = 100;
  973. const filter =
  974. 'cb.`code` = ' + this.db.escape(bills.b_code) +
  975. ' And cb.`name` = ' + this.db.escape(bills.name) +
  976. ' And cb.`unit` = ' + this.db.escape(bills.unit) +
  977. ' And cb.`unit_price` = ' + this.db.escape(bills.unit_price) +
  978. (pos ? ' And cb.`bwmx` = ' + this.db.escape(pos.name) : '');
  979. const sql =
  980. 'SELECT c.cid, c.code, c.name, c.w_code, c.p_code, c.peg, c.org_name, c.org_code, c.new_name, c.new_code,' +
  981. ' c.content, c.basis, c.memo, c.type, c.class, c.quality, c.company, c.charge, ' +
  982. ' cb.id As cbid, cb.code As b_code, cb.name As b_name, cb.unit As b_unit, cb.samount As b_amount, cb.detail As b_detail, cb.bwmx As b_bwmx, ' +
  983. ' scb.used_amount' +
  984. ' FROM ' + this.tableName + ' As c ' +
  985. ' Left Join ' + this.ctx.service.changeAuditList.tableName + ' As cb On c.cid = cb.cid ' +
  986. ' Left Join (' +
  987. ' SELECT SUM(sc.qty) As used_amount, sc.cbid' +
  988. ' FROM ' + this.ctx.service.stageChange.tableName + ' As sc' +
  989. ' INNER JOIN (SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, cbid, sid ' +
  990. ' FROM ' + this.ctx.service.stageChange.tableName +
  991. ' WHERE tid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?)) ' +
  992. ' GROUP BY cbid, sid' +
  993. ' ) As MF' +
  994. ' ON (sc.stimes * ' + timesLen + ' + sc.sorder) = MF.flow And sc.cbid = MF.cbid And sc.sid = MF.sid' +
  995. ' GROUP BY sc.cbid' +
  996. ' ) As scb ON cb.id = scb.cbid' +
  997. ' WHERE c.tid = ? And c.status = ? And c.valid And ' + filter +
  998. ' ORDER BY c.in_time';
  999. const sqlParam = [tid, times, times, order, tid, audit.flow.status.checked];
  1000. const changes = await this.db.query(sql, sqlParam);
  1001. for (const c of changes) {
  1002. const aSql = 'SELECT ca.*, pa.name As u_name, pa.role As u_role ' +
  1003. ' FROM ?? As ca ' +
  1004. ' Left Join ?? As pa ' +
  1005. ' On ca.uid = pa.id ' +
  1006. ' Where ca.cid = ?';
  1007. const aSqlParam = [this.ctx.service.changeAtt.tableName, this.ctx.service.projectAccount.tableName, c.cid];
  1008. c.attachments = await this.db.query(aSql, aSqlParam);
  1009. }
  1010. return changes;
  1011. }
  1012. /**
  1013. * 查询变更令 + 变更令执行
  1014. * @param tid
  1015. * @return {Promise<void>}
  1016. */
  1017. async getChangeAndUsedInfo(tid) {
  1018. const lastStage = await this.ctx.service.stage.getLastestStage(tid, true);
  1019. let filter;
  1020. if (lastStage.id === this.ctx.stage.id) {
  1021. filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [
  1022. lastStage.order,
  1023. lastStage.order,
  1024. this.ctx.stage.curTimes,
  1025. this.ctx.stage.curTimes,
  1026. this.ctx.stage.curOrder,
  1027. ]);
  1028. } else {
  1029. if (lastStage.status === audit.stage.status.uncheck) {
  1030. filter = ' And s.order < ' + lastStage.order;
  1031. } else if (lastStage.status === audit.stage.status.checked) {
  1032. filter = '';
  1033. } else if (lastStage.status === audit.stage.status.checkNo) {
  1034. filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And sChange.`stimes` <= ?))', [lastStage.order, lastStage.order, lastStage.times]);
  1035. } else {
  1036. const curAuditor = await this.ctx.service.stageAudit.getCurAuditor(lastStage.id, lastStage.times);
  1037. filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [
  1038. lastStage.order,
  1039. lastStage.order,
  1040. lastStage.times,
  1041. lastStage.times,
  1042. curAuditor.order - 1,
  1043. ]);
  1044. }
  1045. }
  1046. const sql =
  1047. 'SELECT C.*, Sum(U.utp) As used_tp, Round(Sum(U.utp) / C.total_price * 100, 2) As used_pt' +
  1048. ' FROM ' +
  1049. this.tableName +
  1050. ' As C' +
  1051. ' LEFT JOIN (SELECT sc.tid, sc.cid, sc.cbid, Round(SUM(sc.qty) * cb.unit_price, ?) As utp' +
  1052. ' FROM ' +
  1053. this.ctx.service.stageChange.tableName +
  1054. ' As sc' +
  1055. ' INNER JOIN (' +
  1056. ' SELECT MAX(`stimes`) As `stimes`, MAX(`sorder`) As `sorder`, `lid`, `pid`, `cbid`, sChange.`sid` ' +
  1057. ' FROM ' +
  1058. this.ctx.service.stageChange.tableName +
  1059. ' As sChange ' +
  1060. ' LEFT JOIN ' +
  1061. this.ctx.service.stage.tableName +
  1062. ' As s' +
  1063. ' ON sChange.sid = s.id' +
  1064. ' WHERE sChange.tid = ?' +
  1065. filter +
  1066. ' GROUP By `lid`, `pid`, `cbid`, `sid`' +
  1067. ' ) As m' +
  1068. ' ON sc.stimes = m.stimes And sc.sorder = m.sorder And sc.`cbid` = m.`cbid` AND sc.`sid` = m.`sid` And sc.`lid` = m.`lid` And sc.`pid` = m.`pid`' +
  1069. ' LEFT JOIN ' +
  1070. this.ctx.service.changeAuditList.tableName +
  1071. ' As cb ON sc.cbid = cb.id' +
  1072. ' GROUP By sc.`cbid`' +
  1073. ' ) As U ON C.cid = U.cid' +
  1074. ' WHERE C.tid = ? And C.status = ? And C.valid' +
  1075. ' GROUP By C.cid' +
  1076. ' ORDER By in_time';
  1077. const sqlParam = [this.ctx.tender.info.decimal.tp, tid, tid, audit.flow.status.checked];
  1078. return await this.db.query(sql, sqlParam);
  1079. }
  1080. /**
  1081. * 查询可用的变更令
  1082. * @param { string } cid - 查询的清单
  1083. * @return {Promise<*>} - 可用的变更令列表
  1084. */
  1085. async delete(cid) {
  1086. // 初始化事务
  1087. this.transaction = await this.db.beginTransaction();
  1088. let result = false;
  1089. try {
  1090. // 先删除清单,审批人列表
  1091. await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid });
  1092. await this.transaction.delete(this.ctx.service.changeAudit.tableName, { cid });
  1093. // 再删除附件和附件文件ni zuo
  1094. const attList = await this.ctx.service.changeAtt.getAllDataByCondition({ where: { cid } });
  1095. if (attList.length !== 0) {
  1096. for (const att of attList) {
  1097. await fs.unlinkSync(path.join(this.app.baseDir, att.filepath));
  1098. }
  1099. await this.transaction.delete(this.ctx.service.changeAtt.tableName, { cid });
  1100. }
  1101. // 最后删除变更令
  1102. await this.transaction.delete(this.tableName, { cid });
  1103. await this.transaction.commit();
  1104. result = true;
  1105. } catch (e) {
  1106. await this.transaction.rollback();
  1107. result = false;
  1108. }
  1109. return result;
  1110. }
  1111. /**
  1112. * 重新审批变更令
  1113. * @param { string } cid - 查询的清单
  1114. * @return {Promise<*>} - 可用的变更令列表
  1115. */
  1116. async checkAgain(cid) {
  1117. // 初始化事务
  1118. this.transaction = await this.db.beginTransaction();
  1119. let result = false;
  1120. try {
  1121. const changeInfo = await this.getDataByCondition({ cid });
  1122. const tenderInfo = await this.ctx.service.tenderInfo.getTenderInfo(changeInfo.tid);
  1123. // 获取终审
  1124. const auditInfo = (
  1125. await this.ctx.service.changeAudit.getAllDataByCondition({
  1126. where: { cid },
  1127. orders: [['usort', 'desc']],
  1128. limit: 1,
  1129. offset: 0,
  1130. })
  1131. )[0];
  1132. let usort = auditInfo.usort + 1;
  1133. // 新增2个审批状态到审批列表中
  1134. const insert_audit1 = {
  1135. tid: auditInfo.tid,
  1136. cid: auditInfo.cid,
  1137. uid: auditInfo.uid,
  1138. name: auditInfo.name,
  1139. jobs: auditInfo.jobs,
  1140. company: auditInfo.company,
  1141. times: auditInfo.times,
  1142. usite: auditInfo.usite,
  1143. usort,
  1144. sin_time: new Date(),
  1145. status: audit.flow.auditStatus.checkAgain,
  1146. };
  1147. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit1);
  1148. usort++;
  1149. // 新增2个审批人到审批列表中
  1150. const insert_audit2 = {
  1151. tid: auditInfo.tid,
  1152. cid: auditInfo.cid,
  1153. uid: auditInfo.uid,
  1154. name: auditInfo.name,
  1155. jobs: auditInfo.jobs,
  1156. company: auditInfo.company,
  1157. times: auditInfo.times,
  1158. usite: auditInfo.usite,
  1159. usort,
  1160. status: audit.flow.auditStatus.checking,
  1161. sin_time: new Date(),
  1162. };
  1163. await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit2);
  1164. // 审批列表数据也要回退
  1165. let total_price = 0;
  1166. const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({
  1167. where: { cid: changeInfo.cid },
  1168. });
  1169. for (const cl of changeList) {
  1170. const audit_amount = cl.audit_amount.split(',');
  1171. const last_amount = audit_amount[audit_amount.length - 1];
  1172. audit_amount.splice(-1, 1);
  1173. const list_update = {
  1174. id: cl.id,
  1175. audit_amount: audit_amount.join(','),
  1176. samount: '',
  1177. };
  1178. total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), tenderInfo.decimal.tp));
  1179. await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update);
  1180. }
  1181. // 设置变更令审批中
  1182. const change_update = {
  1183. p_code: null,
  1184. status: audit.flow.status.checking,
  1185. cin_time: Date.parse(new Date()) / 1000,
  1186. sin_time: null,
  1187. total_price,
  1188. };
  1189. const options = {
  1190. where: {
  1191. cid: changeInfo.cid,
  1192. },
  1193. };
  1194. await this.transaction.update(this.tableName, change_update, options);
  1195. await this.transaction.commit();
  1196. result = true;
  1197. const sms = new SMS(this.ctx);
  1198. const code = await sms.contentChange(changeInfo.code);
  1199. const shenpiUrl = await this.ctx.helper.urlToShort(
  1200. this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi'
  1201. );
  1202. await this.ctx.helper.sendAliSms(auditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, {
  1203. biangeng: code,
  1204. code: shenpiUrl,
  1205. });
  1206. // 微信模板通知
  1207. const wechatData = {
  1208. wap_url: shenpiUrl,
  1209. status: wxConst.status.check,
  1210. tips: wxConst.tips.check,
  1211. code: this.ctx.session.sessionProject.code,
  1212. c_name: changeInfo.name,
  1213. };
  1214. await this.ctx.helper.sendWechat(auditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData);
  1215. } catch (error) {
  1216. await this.transaction.rollback();
  1217. result = false;
  1218. }
  1219. return result;
  1220. }
  1221. /**
  1222. * 判断是否有重名的变更令
  1223. * @param cid
  1224. * @param code
  1225. * @param tid
  1226. * @return {Promise<void>}
  1227. */
  1228. async isRepeat(cid, code, tid) {
  1229. const sql = 'SELECT COUNT(*) as count FROM ?? WHERE ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?)) AND `cid` != ? AND `tid` = ?';
  1230. const sqlParam = [this.tableName, code, audit.flow.status.checked, code, audit.flow.status.checked, cid, tid];
  1231. const result = await this.db.queryOne(sql, sqlParam);
  1232. return result.count !== 0;
  1233. }
  1234. async getAllCheckedChanges(tid) {
  1235. return await this.getAllDataByCondition({
  1236. where: { tid, status: audit.flow.status.checked },
  1237. orders: [['in_time', 'desc']],
  1238. });
  1239. }
  1240. /**
  1241. * 用于添加推送所需的content内容
  1242. * @param {Number} pid 项目id
  1243. * @param {Number} tid 台账id
  1244. * @param {Number} cid 变更id
  1245. * @param {Number} uid 审核人id
  1246. */
  1247. async getNoticeContent(pid, tid, cid, uid) {
  1248. const noticeSql =
  1249. 'SELECT * FROM (SELECT ' +
  1250. ' t.`id` As `tid`, t.`name`, c.`cid`, c.`code` As `c_code`, pa.`name` As `su_name`, pa.role As `su_role`' +
  1251. ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' +
  1252. ' LEFT JOIN ?? As c ON c.`cid` = ?' +
  1253. ' LEFT JOIN ?? As pa ON pa.`id` = ?' +
  1254. ' WHERE t.`project_id` = ? ) as new_t GROUP BY new_t.`tid`';
  1255. const noticeSqlParam = [this.ctx.service.tender.tableName, tid, this.ctx.service.change.tableName, cid, this.ctx.service.projectAccount.tableName, uid, pid];
  1256. const content = await this.db.query(noticeSql, noticeSqlParam);
  1257. return content.length ? JSON.stringify(content[0]) : '';
  1258. }
  1259. /**
  1260. * 获取当前标段其他变更令
  1261. * @param {Number} tid - 标段id
  1262. * @param {Number} cid - 当前变更令
  1263. */
  1264. async getOthersChange(tid, cid) {
  1265. const sql = 'SELECT * FROM ?? WHERE tid = ? AND cid != ? ORDER By `in_time` desc ';
  1266. const sqlParam = [this.tableName, tid, cid];
  1267. const data = await this.db.query(sql, sqlParam);
  1268. const changeClassObj = {};
  1269. for (const c in changeConst.class) {
  1270. if (changeConst.class.hasOwnProperty(c)) {
  1271. const item = changeConst.class[c];
  1272. changeClassObj[item.value] = item.name;
  1273. }
  1274. }
  1275. for (let i = 0; i < data.length; i++) {
  1276. data[i].class = changeClassObj[data[i].class];
  1277. }
  1278. return data;
  1279. }
  1280. /**
  1281. * 拷贝变更令至当前变更令
  1282. * @param {String} cid - 当前变更令
  1283. * @param {String} copy_cid - 要拷贝的变更令
  1284. */
  1285. async handleCopyChange(cid, copy_cid) {
  1286. // const change = await this.getDataByCondition({ cid });
  1287. const copyChange = await this.getDataByCondition({ cid: copy_cid });
  1288. return await this.update({ peg: copyChange.peg, org_name: copyChange.org_name, org_code: copyChange.org_code, new_name: copyChange.new_name, content: copyChange.content, basis: copyChange.basis, expr: copyChange.expr, memo: copyChange.memo, type: copyChange.type,
  1289. class: copyChange.class, quality: copyChange.quality, company: copyChange.company, charge: copyChange.charge, new_code: copyChange.new_code }, {
  1290. cid,
  1291. });
  1292. }
  1293. }
  1294. return Change;
  1295. };