ledger_audit.js 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396
  1. 'use strict';
  2. /**
  3. * 台账审批流程表
  4. *
  5. * @author Mai
  6. * @date 2018/5/25
  7. * @version
  8. */
  9. const auditConst = require('../const/audit').ledger;
  10. const smsTypeConst = require('../const/sms_type');
  11. const SMS = require('../lib/sms');
  12. const SmsAliConst = require('../const/sms_alitemplate');
  13. module.exports = app => {
  14. class LedgerAudit extends app.BaseService {
  15. /**
  16. * 构造函数
  17. *
  18. * @param {Object} ctx - egg全局变量
  19. * @return {void}
  20. */
  21. constructor(ctx) {
  22. super(ctx);
  23. this.tableName = 'ledger_audit';
  24. }
  25. /**
  26. * 获取标段审核人信息
  27. *
  28. * @param {Number} tenderId - 标段id
  29. * @param {Number} auditorId - 审核人id
  30. * @param {Number} times - 第几次审批
  31. * @returns {Promise<*>}
  32. */
  33. async getAuditor(tenderId, auditorId, times = 1) {
  34. const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
  35. 'FROM ?? AS la, ?? AS pa ' +
  36. 'WHERE la.`tender_id` = ? and la.`audit_id` = ? and la.`times` = ?' +
  37. ' and la.`audit_id` = pa.`id`';
  38. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditorId, times];
  39. return await this.db.queryOne(sql, sqlParam);
  40. }
  41. /**
  42. * 获取标段审核人最后一位的名称
  43. *
  44. * @param {Number} tenderId - 标段id
  45. * @param {Number} auditorId - 审核人id
  46. * @param {Number} times - 第几次审批
  47. * @returns {Promise<*>}
  48. */
  49. async getStatusName(tenderId, times) {
  50. const sql = 'SELECT pa.`name` ' +
  51. 'FROM ?? AS la, ?? AS pa ' +
  52. 'WHERE la.`tender_id` = ?' +
  53. ' and la.`audit_id` = pa.`id` and la.`status` != ? ORDER BY la.`times` DESC, la.`audit_order` DESC';
  54. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditConst.status.uncheck];
  55. return await this.db.queryOne(sql, sqlParam);
  56. }
  57. /**
  58. * 获取标段审核列表信息
  59. *
  60. * @param {Number} tenderId - 标段id
  61. * @param {Number} times - 第几次审批
  62. * @returns {Promise<*>}
  63. */
  64. async getAuditors(tenderId, times = 1) {
  65. const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, g.`sort` ' +
  66. 'FROM ?? AS la, ?? AS pa, (SELECT `audit_id`,(@i:=@i+1) as `sort` FROM ??, (select @i:=0) as it WHERE `tender_id` = ? AND `times` = ? GROUP BY `audit_id`) as g ' +
  67. 'WHERE la.`tender_id` = ? and la.`times` = ? and la.`audit_id` = pa.`id` and g.`audit_id` = la.`audit_id` order by la.`audit_order`';
  68. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, tenderId, times, tenderId, times];
  69. const result = await this.db.query(sql, sqlParam);
  70. const sql2 = 'SELECT COUNT(a.`audit_id`) as num FROM (SELECT `audit_id` FROM ?? WHERE `tender_id` = ? AND `times` = ? GROUP BY `audit_id`) as a';
  71. const sqlParam2 = [this.tableName, tenderId, times];
  72. const count = await this.db.queryOne(sql2, sqlParam2);
  73. for (const i in result) {
  74. result[i].max_sort = count.num;
  75. }
  76. return result;
  77. }
  78. /**
  79. * 获取标段当前审核人
  80. *
  81. * @param {Number} tenderId - 标段id
  82. * @param {Number} times - 第几次审批
  83. * @returns {Promise<*>}
  84. */
  85. async getCurAuditor(tenderId, times = 1) {
  86. const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
  87. 'FROM ?? AS la, ?? AS pa ' +
  88. 'WHERE la.`tender_id` = ? and la.`status` = ? and la.`times` = ?' +
  89. ' and la.`audit_id` = pa.`id`';
  90. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditConst.status.checking, times];
  91. return await this.db.queryOne(sql, sqlParam);
  92. }
  93. /**
  94. * 获取最新审核顺序
  95. *
  96. * @param {Number} tenderId - 标段id
  97. * @param {Number} times - 第几次审批
  98. * @returns {Promise<number>}
  99. */
  100. async getNewOrder(tenderId, times = 1) {
  101. const sql = 'SELECT Max(??) As max_order FROM ?? Where `tender_id` = ? and `times` = ?';
  102. const sqlParam = ['audit_order', this.tableName, tenderId, times];
  103. const result = await this.db.queryOne(sql, sqlParam);
  104. return result && result.max_order ? result.max_order + 1 : 1;
  105. }
  106. /**
  107. * 新增审核人
  108. *
  109. * @param {Number} tenderId - 标段id
  110. * @param {Number} auditorId - 审核人id
  111. * @param {Number} times - 第几次审批
  112. * @returns {Promise<number>}
  113. */
  114. async addAuditor(tenderId, auditorId, times = 1) {
  115. const newOrder = await this.getNewOrder(tenderId, times);
  116. const data = {
  117. tender_id: tenderId,
  118. audit_id: auditorId,
  119. times: times,
  120. audit_order: newOrder,
  121. status: auditConst.status.uncheck,
  122. };
  123. const result = await this.db.insert(this.tableName, data);
  124. return result.effectRows = 1;
  125. }
  126. /**
  127. * 移除审核人时,同步其后审核人order
  128. * @param transaction - 事务
  129. * @param {Number} tenderId - 标段id
  130. * @param {Number} auditorId - 审核人id
  131. * @param {Number} times - 第几次审批
  132. * @returns {Promise<*>}
  133. * @private
  134. */
  135. async _syncOrderByDelete(transaction, tenderId, order, times) {
  136. this.initSqlBuilder();
  137. this.sqlBuilder.setAndWhere('tender_id', {
  138. value: tenderId,
  139. operate: '='
  140. });
  141. this.sqlBuilder.setAndWhere('audit_order', {
  142. value: order,
  143. operate: '>=',
  144. });
  145. this.sqlBuilder.setAndWhere('times', {
  146. value: times,
  147. operate: '=',
  148. });
  149. this.sqlBuilder.setUpdateData('audit_order', {
  150. value: 1,
  151. selfOperate: '-',
  152. });
  153. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
  154. const data = await transaction.query(sql, sqlParam);
  155. return data;
  156. }
  157. /**
  158. * 移除审核人
  159. *
  160. * @param {Number} tenderId - 标段id
  161. * @param {Number} auditorId - 审核人id
  162. * @param {Number} times - 第几次审批
  163. * @returns {Promise<boolean>}
  164. */
  165. async deleteAuditor(tenderId, auditorId, times = 1) {
  166. const transaction = await this.db.beginTransaction();
  167. try {
  168. const condition = {tender_id: tenderId, audit_id: auditorId, times: times};
  169. const auditor = await this.getDataByCondition(condition);
  170. if (!auditor) {
  171. throw '该审核人不存在';
  172. }
  173. await this._syncOrderByDelete(transaction, tenderId, auditor.audit_order, times);
  174. await transaction.delete(this.tableName, condition);
  175. await transaction.commit();
  176. } catch (err) {
  177. await transaction.rollback();
  178. throw err;
  179. }
  180. return true;
  181. }
  182. /**
  183. * 开始审批
  184. *
  185. * @param {Number} tenderId - 标段id
  186. * @param {Number} times - 第几次审批
  187. * @returns {Promise<boolean>}
  188. */
  189. async start(tenderId, times = 1) {
  190. const audit = await this.getDataByCondition({tender_id: tenderId, times: times, audit_order: 1});
  191. if (!audit) {
  192. throw '审核人信息错误';
  193. }
  194. const sum = await this.ctx.service.ledger.addUp({tender_id: tenderId/*, is_leaf: true*/});
  195. const transaction = await this.db.beginTransaction();
  196. try {
  197. await transaction.update(this.tableName, {id: audit.id, status: auditConst.status.checking, begin_time: new Date()});
  198. await transaction.update(this.ctx.service.tender.tableName, {
  199. id: tenderId, ledger_status: auditConst.status.checking,
  200. total_price: sum.total_price, deal_tp: sum.deal_tp,
  201. });
  202. // 添加短信通知-需要审批提醒功能
  203. // await this.ctx.helper.sendUserSms(audit.audit_id, smsTypeConst.const.TZ,
  204. // smsTypeConst.judge.approval.toString(), '台帐需要您审批。');
  205. await this.ctx.helper.sendAliSms(audit.audit_id, smsTypeConst.const.TZ,
  206. smsTypeConst.judge.approval.toString(), SmsAliConst.template.ledger_check);
  207. await transaction.commit();
  208. } catch (err) {
  209. await transaction.rollback();
  210. throw err;
  211. }
  212. return true;
  213. }
  214. /**
  215. * 审批
  216. * @param {Number} tenderId - 标段id
  217. * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果
  218. * @param {Number} times - 第几次审批
  219. * @returns {Promise<void>}
  220. */
  221. async check(tenderId, checkType, opinion, times = 1) {
  222. if (checkType !== auditConst.status.checked && checkType !== auditConst.status.checkNo) {
  223. throw '提交数据错误';
  224. }
  225. const transaction = await this.db.beginTransaction();
  226. try {
  227. // 整理当前流程审核人状态更新
  228. const time = new Date();
  229. const audit = await this.getDataByCondition({ tender_id: tenderId, times, status: auditConst.status.checking });
  230. if (!audit) {
  231. throw '审核数据错误';
  232. }
  233. // 更新当前审核流程
  234. await transaction.update(this.tableName, { id: audit.id, status: checkType, opinion, end_time: time });
  235. if (checkType === auditConst.status.checked) {
  236. const nextAudit = await this.getDataByCondition({ tender_id: tenderId, times, audit_order: audit.audit_order + 1 });
  237. // 无下一审核人表示,审核结束
  238. if (nextAudit) {
  239. await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time });
  240. // await this.ctx.helper.sendUserSms(nextAudit.audit_id, smsTypeConst.const.TZ,
  241. // smsTypeConst.judge.approval.toString(), '台帐需要您审批。');
  242. await this.ctx.helper.sendAliSms(nextAudit.audit_id, smsTypeConst.const.TZ,
  243. smsTypeConst.judge.approval.toString(), SmsAliConst.template.ledger_check);
  244. } else {
  245. // 同步标段信息
  246. await transaction.update(this.ctx.service.tender.tableName, { id: tenderId, ledger_status: checkType });
  247. // 添加短信通知-审批通过提醒功能
  248. // const mobile_array = [];
  249. // const tenderInfo = await this.ctx.service.tender.getDataById(tenderId);
  250. // const smsUser1 = await this.ctx.service.projectAccount.getDataById(tenderInfo.user_id);
  251. // if (smsUser1.auth_mobile !== '' && smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '' && smsUser1.sms_type !== null) {
  252. // const smsType = JSON.parse(smsUser1.sms_type);
  253. // if (smsType[smsTypeConst.const.TZ] !== undefined && smsType[smsTypeConst.const.TZ].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  254. // mobile_array.push(smsUser1.auth_mobile);
  255. // }
  256. // }
  257. // const auditList = await this.getAuditors(tenderId, times);
  258. // for (const user of auditList) {
  259. // const smsUser = await this.ctx.service.projectAccount.getDataById(user.audit_id);
  260. // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '' && smsUser.sms_type !== null) {
  261. // const smsType = JSON.parse(smsUser.sms_type);
  262. // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.TZ] !== undefined && smsType[smsTypeConst.const.TZ].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  263. // mobile_array.push(smsUser.auth_mobile);
  264. // }
  265. // }
  266. // }
  267. // if (mobile_array.length > 0) {
  268. // const sms = new SMS(this.ctx);
  269. // const tenderName = await sms.contentChange(tenderInfo.name);
  270. // const projectName = await sms.contentChange(this.ctx.tender.info.deal_info.buildName);
  271. // const ptmsg = projectName !== '' ? '项目「' + projectName + '」标段「' + tenderName + '」' : tenderName;
  272. // const content = '【纵横计量支付】' + ptmsg + '台账审批通过,请登录系统处理。';
  273. // sms.send(mobile_array, content);
  274. // }
  275. const auditList = await this.getAuditors(tenderId, times);
  276. const users = this._.pull(this._.map(auditList, 'audit_id'), audit.id);
  277. // await this.ctx.helper.sendUserSms(users, smsTypeConst.const.TZ,
  278. // smsTypeConst.judge.result.toString(), '台账审批通过,请登录系统处理。');
  279. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.TZ,
  280. smsTypeConst.judge.result.toString(), SmsAliConst.template.ledger_result, { status: SmsAliConst.status.success });
  281. }
  282. } else {
  283. // 同步标段信息
  284. await transaction.update(this.ctx.service.tender.tableName, { id: tenderId, ledger_times: times + 1, ledger_status: checkType});
  285. // 拷贝新一次审核流程列表
  286. const auditors = await this.getAllDataByCondition({
  287. where: { tender_id: tenderId, times },
  288. columns: ['tender_id', 'audit_order', 'audit_id'],
  289. });
  290. for (const a of auditors) {
  291. a.times = times + 1;
  292. a.status = auditConst.status.uncheck;
  293. }
  294. await transaction.insert(this.tableName, auditors);
  295. // 添加短信通知-审批退回提醒功能
  296. // const mobile_array = [];
  297. // const tenderInfo = await this.ctx.service.tender.getDataById(tenderId);
  298. // const smsUser1 = await this.ctx.service.projectAccount.getDataById(tenderInfo.user_id);
  299. // if (smsUser1.auth_mobile !== '' && smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '' && smsUser1.sms_type !== null) {
  300. // const smsType = JSON.parse(smsUser1.sms_type);
  301. // if (smsType[smsTypeConst.const.TZ] !== undefined && smsType[smsTypeConst.const.TZ].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  302. // mobile_array.push(smsUser1.auth_mobile);
  303. // }
  304. // }
  305. // for (const user of auditors) {
  306. // const smsUser = await this.ctx.service.projectAccount.getDataById(user.audit_id);
  307. // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '' && smsUser.sms_type !== null) {
  308. // const smsType = JSON.parse(smsUser.sms_type);
  309. // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.TZ] !== undefined && smsType[smsTypeConst.const.TZ].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  310. // mobile_array.push(smsUser.auth_mobile);
  311. // }
  312. // }
  313. // }
  314. // if (mobile_array.length > 0) {
  315. // const sms = new SMS(this.ctx);
  316. // const tenderName = await sms.contentChange(tenderInfo.name);
  317. // const projectName = await sms.contentChange(this.ctx.tender.info.deal_info.buildName);
  318. // const ptmsg = projectName !== '' ? '项目「' + projectName + '」标段「' + tenderName + '」' : tenderName;
  319. // const content = '【纵横计量支付】' + ptmsg + '台账审批退回,请登录系统处理。';
  320. // sms.send(mobile_array, content);
  321. // }
  322. const auditList = await this.getAuditors(tenderId, times);
  323. console.log(auditList);
  324. const users = this._.pull(this._.map(auditList, 'audit_id'), audit.id);
  325. console.log(users);
  326. // await this.ctx.helper.sendUserSms(users, smsTypeConst.const.TZ,
  327. // smsTypeConst.judge.result.toString(), '台账审批退回,请登录系统处理。');
  328. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.TZ,
  329. smsTypeConst.judge.result.toString(), SmsAliConst.template.ledger_result, { status: SmsAliConst.status.back });
  330. }
  331. await transaction.commit();
  332. } catch (err) {
  333. await transaction.rollback();
  334. throw err;
  335. }
  336. }
  337. /**
  338. * 获取审核人需要审核的标段列表
  339. *
  340. * @param auditorId
  341. * @returns {Promise<*>}
  342. */
  343. async getAuditTender(auditorId) {
  344. const sql = 'SELECT la.`audit_id`, la.`times`, la.`audit_order`, la.`begin_time`, la.`end_time`, t.`id`, t.`name`, t.`project_id`, t.`type`, t.`user_id`, t.`ledger_status` ' +
  345. 'FROM ?? AS la, ?? AS t ' +
  346. 'WHERE ((la.`audit_id` = ? and la.`status` = ?) OR (t.`user_id` = ? and t.`ledger_status` = ? and la.`status` = ? and la.`times` = (t.`ledger_times`-1)))' +
  347. ' and la.`tender_id` = t.`id`';
  348. const sqlParam = [this.tableName, this.ctx.service.tender.tableName, auditorId, auditConst.status.checking, auditorId, auditConst.status.checkNo, auditConst.status.checkNo];
  349. return await this.db.query(sql, sqlParam);
  350. }
  351. /**
  352. * 获取 某时间后 审批进度 更新的台账
  353. * @param {Integer} projectId - 项目id
  354. * @param {Integer} auditorId - 查询人id
  355. * @param {Date} noticeTime - 查询事件
  356. * @returns {Promise<*>}
  357. */
  358. async getNoticeTender(projectId, auditorId, noticeTime) {
  359. const sql = 'SELECT * FROM (SELECT la.`audit_id`, la.`times`, la.`audit_order`, la.`end_time`, la.`status`, t.`id`, t.`name`, t.`project_id`, t.`type`, t.`user_id`, ' +
  360. ' pa.name As `lu_name`, pa.role As `lu_role`, pa.company As `lu_company`' +
  361. ' FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tender_id` FROM ?? WHERE `audit_id` = ? GROUP BY `tender_id`)) As t ' +
  362. ' LEFT JOIN ?? As la ON la.`tender_id` = t.`id`' +
  363. ' LEFT JOIN ?? As pa ON la.`audit_id` = pa.`id`' +
  364. ' WHERE la.`end_time` > ? and t.`project_id` = ?' +
  365. ' ORDER By la.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`id` ORDER BY new_t.`end_time`';
  366. const sqlParam = [this.ctx.service.tender.tableName, auditorId, this.tableName, auditorId, this.tableName, this.ctx.service.projectAccount.tableName,
  367. noticeTime, projectId];
  368. return await this.db.query(sql, sqlParam);
  369. }
  370. }
  371. return LedgerAudit;
  372. };