ledger_audit.js 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495
  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. const wxConst = require('../const/wechat_template');
  14. const pushType = require('../const/audit').pushType;
  15. module.exports = app => {
  16. class LedgerAudit extends app.BaseService {
  17. /**
  18. * 构造函数
  19. *
  20. * @param {Object} ctx - egg全局变量
  21. * @return {void}
  22. */
  23. constructor(ctx) {
  24. super(ctx);
  25. this.tableName = 'ledger_audit';
  26. }
  27. /**
  28. * 获取标段审核人信息
  29. *
  30. * @param {Number} tenderId - 标段id
  31. * @param {Number} auditorId - 审核人id
  32. * @param {Number} times - 第几次审批
  33. * @return {Promise<*>}
  34. */
  35. async getAuditor(tenderId, auditorId, times = 1) {
  36. const sql =
  37. '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` ' +
  38. 'FROM ?? AS la, ?? AS pa ' +
  39. 'WHERE la.`tender_id` = ? and la.`audit_id` = ? and la.`times` = ?' +
  40. ' and la.`audit_id` = pa.`id`';
  41. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditorId, times];
  42. return await this.db.queryOne(sql, sqlParam);
  43. }
  44. /**
  45. * 获取标段审核人最后一位的名称
  46. *
  47. * @param {Number} tenderId - 标段id
  48. * @param {Number} auditorId - 审核人id
  49. * @param {Number} times - 第几次审批
  50. * @return {Promise<*>}
  51. */
  52. async getStatusName(tenderId, times) {
  53. const sql =
  54. 'SELECT pa.`name` ' +
  55. 'FROM ?? AS la, ?? AS pa ' +
  56. 'WHERE la.`tender_id` = ?' +
  57. ' and la.`audit_id` = pa.`id` and la.`status` != ? ORDER BY la.`times` DESC, la.`audit_order` DESC';
  58. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditConst.status.uncheck];
  59. return await this.db.queryOne(sql, sqlParam);
  60. }
  61. /**
  62. * 获取标段审核列表信息
  63. *
  64. * @param {Number} tenderId - 标段id
  65. * @param {Number} times - 第几次审批
  66. * @return {Promise<*>}
  67. */
  68. async getAuditors(tenderId, times = 1) {
  69. const sql =
  70. '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` ' +
  71. '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 ' +
  72. '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`';
  73. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, tenderId, times, tenderId, times];
  74. const result = await this.db.query(sql, sqlParam);
  75. 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';
  76. const sqlParam2 = [this.tableName, tenderId, times];
  77. const count = await this.db.queryOne(sql2, sqlParam2);
  78. for (const i in result) {
  79. result[i].max_sort = count.num;
  80. }
  81. return result;
  82. }
  83. /**
  84. * 获取标段当前审核人
  85. *
  86. * @param {Number} tenderId - 标段id
  87. * @param {Number} times - 第几次审批
  88. * @return {Promise<*>}
  89. */
  90. async getCurAuditor(tenderId, times = 1) {
  91. const sql =
  92. '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` ' +
  93. 'FROM ?? AS la, ?? AS pa ' +
  94. 'WHERE la.`tender_id` = ? and la.`status` = ? and la.`times` = ?' +
  95. ' and la.`audit_id` = pa.`id`';
  96. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, auditConst.status.checking, times];
  97. return await this.db.queryOne(sql, sqlParam);
  98. }
  99. /**
  100. * 获取最新审核顺序
  101. *
  102. * @param {Number} tenderId - 标段id
  103. * @param {Number} times - 第几次审批
  104. * @return {Promise<number>}
  105. */
  106. async getNewOrder(tenderId, times = 1) {
  107. const sql = 'SELECT Max(??) As max_order FROM ?? Where `tender_id` = ? and `times` = ?';
  108. const sqlParam = ['audit_order', this.tableName, tenderId, times];
  109. const result = await this.db.queryOne(sql, sqlParam);
  110. return result && result.max_order ? result.max_order + 1 : 1;
  111. }
  112. /**
  113. * 新增审核人
  114. *
  115. * @param {Number} tenderId - 标段id
  116. * @param {Number} auditorId - 审核人id
  117. * @param {Number} times - 第几次审批
  118. * @return {Promise<number>}
  119. */
  120. async addAuditor(tenderId, auditorId, times = 1) {
  121. const newOrder = await this.getNewOrder(tenderId, times);
  122. const data = {
  123. tender_id: tenderId,
  124. audit_id: auditorId,
  125. times,
  126. audit_order: newOrder,
  127. status: auditConst.status.uncheck,
  128. };
  129. const result = await this.db.insert(this.tableName, data);
  130. return (result.effectRows = 1);
  131. }
  132. /**
  133. * 移除审核人时,同步其后审核人order
  134. * @param transaction - 事务
  135. * @param {Number} tenderId - 标段id
  136. * @param {Number} auditorId - 审核人id
  137. * @param {Number} times - 第几次审批
  138. * @return {Promise<*>}
  139. * @private
  140. */
  141. async _syncOrderByDelete(transaction, tenderId, order, times) {
  142. this.initSqlBuilder();
  143. this.sqlBuilder.setAndWhere('tender_id', {
  144. value: tenderId,
  145. operate: '=',
  146. });
  147. this.sqlBuilder.setAndWhere('audit_order', {
  148. value: order,
  149. operate: '>=',
  150. });
  151. this.sqlBuilder.setAndWhere('times', {
  152. value: times,
  153. operate: '=',
  154. });
  155. this.sqlBuilder.setUpdateData('audit_order', {
  156. value: 1,
  157. selfOperate: '-',
  158. });
  159. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
  160. const data = await transaction.query(sql, sqlParam);
  161. return data;
  162. }
  163. /**
  164. * 移除审核人
  165. *
  166. * @param {Number} tenderId - 标段id
  167. * @param {Number} auditorId - 审核人id
  168. * @param {Number} times - 第几次审批
  169. * @return {Promise<boolean>}
  170. */
  171. async deleteAuditor(tenderId, auditorId, times = 1) {
  172. const transaction = await this.db.beginTransaction();
  173. try {
  174. const condition = { tender_id: tenderId, audit_id: auditorId, times };
  175. const auditor = await this.getDataByCondition(condition);
  176. if (!auditor) {
  177. throw '该审核人不存在';
  178. }
  179. await this._syncOrderByDelete(transaction, tenderId, auditor.audit_order, times);
  180. await transaction.delete(this.tableName, condition);
  181. await transaction.commit();
  182. } catch (err) {
  183. await transaction.rollback();
  184. throw err;
  185. }
  186. return true;
  187. }
  188. /**
  189. * 开始审批
  190. *
  191. * @param {Number} tenderId - 标段id
  192. * @param {Number} times - 第几次审批
  193. * @return {Promise<boolean>}
  194. */
  195. async start(tenderId, times = 1) {
  196. const audit = await this.getDataByCondition({ tender_id: tenderId, times, audit_order: 1 });
  197. if (!audit) {
  198. throw '审核人信息错误';
  199. }
  200. const sum = await this.ctx.service.ledger.addUp({ tender_id: tenderId /* , is_leaf: true*/ });
  201. const transaction = await this.db.beginTransaction();
  202. try {
  203. await transaction.update(this.tableName, {
  204. id: audit.id,
  205. status: auditConst.status.checking,
  206. begin_time: new Date(),
  207. });
  208. await transaction.update(this.ctx.service.tender.tableName, {
  209. id: tenderId,
  210. ledger_status: auditConst.status.checking,
  211. total_price: sum.total_price,
  212. deal_tp: sum.deal_tp,
  213. });
  214. // 添加短信通知-需要审批提醒功能
  215. await this.ctx.helper.sendAliSms(audit.audit_id, smsTypeConst.const.TZ, smsTypeConst.judge.approval.toString(), SmsAliConst.template.ledger_check);
  216. // 微信模板通知
  217. const wechatData = {
  218. status: wxConst.status.check,
  219. tips: wxConst.tips.check,
  220. begin_time: Date.parse(new Date()),
  221. };
  222. await this.ctx.helper.sendWechat(audit.audit_id, smsTypeConst.const.TZ, smsTypeConst.judge.approval.toString(), wxConst.template.ledger, wechatData);
  223. await transaction.commit();
  224. } catch (err) {
  225. await transaction.rollback();
  226. throw err;
  227. }
  228. return true;
  229. }
  230. /**
  231. * 审批
  232. * @param {Number} tenderId - 标段id
  233. * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果
  234. * @param {String} opinion 审批意见
  235. * @param {Number} times - 第几次审批
  236. * @return {Promise<void>}
  237. */
  238. async check(tenderId, checkType, opinion, times = 1) {
  239. if (checkType !== auditConst.status.checked && checkType !== auditConst.status.checkNo) {
  240. throw '提交数据错误';
  241. }
  242. const pid = this.ctx.session.sessionProject.id;
  243. const transaction = await this.db.beginTransaction();
  244. try {
  245. // 整理当前流程审核人状态更新
  246. const time = new Date();
  247. const audit = await this.getDataByCondition({
  248. tender_id: tenderId,
  249. times,
  250. status: auditConst.status.checking,
  251. });
  252. if (!audit) {
  253. throw '审核数据错误';
  254. }
  255. // 获取审核人列表
  256. const auditList = await this.getAuditors(tenderId, times);
  257. // 添加推送
  258. const noticeContent = await this.getNoticeContent(audit.tender_id, pid, audit.audit_id);
  259. const records = [
  260. {
  261. pid,
  262. type: pushType.ledger,
  263. uid: this.ctx.tender.data.user_id,
  264. status: checkType,
  265. content: noticeContent,
  266. },
  267. ];
  268. auditList.forEach(audit => {
  269. records.push({
  270. pid,
  271. type: pushType.ledger,
  272. uid: audit.audit_id,
  273. status: checkType,
  274. content: noticeContent,
  275. });
  276. });
  277. await transaction.insert('zh_notice', records);
  278. // 更新当前审核流程
  279. await transaction.update(this.tableName, { id: audit.id, status: checkType, opinion, end_time: time });
  280. const begin_audit = await this.getDataByCondition({
  281. tender_id: tenderId,
  282. audit_order: 1,
  283. });
  284. if (checkType === auditConst.status.checked) {
  285. const nextAudit = await this.getDataByCondition({
  286. tender_id: tenderId,
  287. times,
  288. audit_order: audit.audit_order + 1,
  289. });
  290. // 无下一审核人表示,审核结束
  291. if (nextAudit) {
  292. await transaction.update(this.tableName, {
  293. id: nextAudit.id,
  294. status: auditConst.status.checking,
  295. begin_time: time,
  296. });
  297. await this.ctx.helper.sendAliSms(nextAudit.audit_id, smsTypeConst.const.TZ, smsTypeConst.judge.approval.toString(), SmsAliConst.template.ledger_check);
  298. // 微信模板通知
  299. const wechatData = {
  300. status: wxConst.status.check,
  301. tips: wxConst.tips.check,
  302. begin_time: Date.parse(begin_audit.begin_time),
  303. };
  304. await this.ctx.helper.sendWechat(nextAudit.audit_id, smsTypeConst.const.TZ, smsTypeConst.judge.approval.toString(), wxConst.template.ledger, wechatData);
  305. } else {
  306. // 同步标段信息
  307. await transaction.update(this.ctx.service.tender.tableName, {
  308. id: tenderId,
  309. ledger_status: checkType,
  310. });
  311. const users = this._.pull(this._.map(auditList, 'audit_id'), audit.id);
  312. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.TZ, smsTypeConst.judge.result.toString(), SmsAliConst.template.ledger_result, {
  313. status: SmsAliConst.status.success,
  314. });
  315. // 微信模板通知
  316. const wechatData = {
  317. status: wxConst.status.success,
  318. tips: wxConst.tips.success,
  319. begin_time: Date.parse(begin_audit.begin_time),
  320. };
  321. await this.ctx.helper.sendWechat(users, smsTypeConst.const.TZ, smsTypeConst.judge.result.toString(), wxConst.template.ledger, wechatData);
  322. }
  323. } else {
  324. // 同步标段信息
  325. await transaction.update(this.ctx.service.tender.tableName, {
  326. id: tenderId,
  327. ledger_times: times + 1,
  328. ledger_status: checkType,
  329. });
  330. // 拷贝新一次审核流程列表
  331. const auditors = await this.getAllDataByCondition({
  332. where: { tender_id: tenderId, times },
  333. columns: ['tender_id', 'audit_order', 'audit_id'],
  334. });
  335. for (const a of auditors) {
  336. a.times = times + 1;
  337. a.status = auditConst.status.uncheck;
  338. }
  339. await transaction.insert(this.tableName, auditors);
  340. const users = this._.pull(this._.map(auditList, 'audit_id'), audit.id);
  341. await this.ctx.helper.sendAliSms(users, smsTypeConst.const.TZ, smsTypeConst.judge.result.toString(), SmsAliConst.template.ledger_result, {
  342. status: SmsAliConst.status.back,
  343. });
  344. // 微信模板通知
  345. const wechatData = {
  346. status: wxConst.status.back,
  347. tips: wxConst.tips.back,
  348. begin_time: Date.parse(begin_audit.begin_time),
  349. };
  350. await this.ctx.helper.sendWechat(audit.audit_id, smsTypeConst.const.TZ, smsTypeConst.judge.result.toString(), wxConst.template.ledger, wechatData);
  351. }
  352. await transaction.commit();
  353. } catch (err) {
  354. await transaction.rollback();
  355. throw err;
  356. }
  357. }
  358. /**
  359. * 获取审核人需要审核的标段列表
  360. *
  361. * @param auditorId
  362. * @return {Promise<*>}
  363. */
  364. async getAuditTender(auditorId) {
  365. const sql =
  366. '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` ' +
  367. 'FROM ?? AS la, ?? AS t ' +
  368. '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)))' +
  369. ' and la.`tender_id` = t.`id` ORDER BY la.`begin_time` DESC';
  370. const sqlParam = [
  371. this.tableName,
  372. this.ctx.service.tender.tableName,
  373. auditorId,
  374. auditConst.status.checking,
  375. auditorId,
  376. auditConst.status.checkNo,
  377. auditConst.status.checkNo,
  378. ];
  379. return await this.db.query(sql, sqlParam);
  380. }
  381. /**
  382. * 获取 某时间后 审批进度 更新的台账
  383. * @param {Integer} pid - 项目id
  384. * @param {Integer} uid - 查询人id
  385. * @param {Date} noticeTime - 查询事件
  386. * @return {Promise<*>}
  387. */
  388. async getNoticeTender(pid, uid, noticeTime) {
  389. // 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`, ' +
  390. // ' pa.name As `lu_name`, pa.role As `lu_role`, pa.company As `lu_company`' +
  391. // ' FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tender_id` FROM ?? WHERE `audit_id` = ? GROUP BY `tender_id`)) As t ' +
  392. // ' LEFT JOIN ?? As la ON la.`tender_id` = t.`id`' +
  393. // ' LEFT JOIN ?? As pa ON la.`audit_id` = pa.`id`' +
  394. // ' WHERE la.`end_time` > ? and t.`project_id` = ?' +
  395. // ' ORDER By la.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`id` ORDER BY new_t.`end_time`';
  396. // const sqlParam = [this.ctx.service.tender.tableName, auditorId, this.tableName, auditorId, this.tableName, this.ctx.service.projectAccount.tableName,
  397. // noticeTime, projectId];
  398. // return await this.db.query(sql, sqlParam);
  399. let notice = await this.db.select('zh_notice', {
  400. where: { pid, type: pushType.ledger, uid },
  401. orders: [['create_time', 'desc']],
  402. limit: 10,
  403. offset: 0,
  404. });
  405. notice = notice.map(v => {
  406. const extra = JSON.parse(v.content);
  407. delete v.content;
  408. return { ...v, ...extra };
  409. });
  410. return notice;
  411. }
  412. /**
  413. * 用于添加推送所需的content内容
  414. * @param {Number} id 标段id
  415. * @param {Number} pid 项目id
  416. * @param {Number} uid 审核人id
  417. */
  418. async getNoticeContent(id, pid, uid) {
  419. const noticeSql =
  420. 'SELECT * FROM (SELECT ' +
  421. ' t.`id` As `tid`, t.`name`, pa.`name` As `su_name`, pa.role As `su_role`' +
  422. ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' +
  423. ' LEFT JOIN ?? As pa ON pa.`id` = ?' +
  424. ' WHERE t.`project_id` = ? ) as new_t GROUP BY new_t.`tid`';
  425. const noticeSqlParam = [this.ctx.service.tender.tableName, id, this.ctx.service.projectAccount.tableName, uid, pid];
  426. const content = await this.db.query(noticeSql, noticeSqlParam);
  427. return content.length ? JSON.stringify(content[0]) : '';
  428. }
  429. /**
  430. * 获取审核人流程列表
  431. * @param {Number} tender_id - 标段id
  432. * @param {Number} times 审核次数
  433. * @return {Promise<Array>} 查询结果集
  434. */
  435. async getAuditGroupByList(tender_id, times = 1) {
  436. const sql =
  437. 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`tender_id`, la.`audit_order` ' +
  438. 'FROM ?? AS la, ?? AS pa ' +
  439. 'WHERE la.`tender_id` = ? and la.`times` = ? and la.`audit_id` = pa.`id` GROUP BY la.`audit_id` ORDER BY la.`audit_order`';
  440. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tender_id, times];
  441. return await this.db.query(sql, sqlParam);
  442. }
  443. /**
  444. * 获取审核人流程列表(包括原报)
  445. * @param {Number} tender_id - 标段id
  446. * @param {Number} times 审核次数
  447. * @return {Promise<Array>} 查询结果集(包括原报)
  448. */
  449. async getAuditorsWithOwner(tender_id, times = 1) {
  450. const result = await this.getAuditGroupByList(tender_id, times);
  451. const sql =
  452. 'SELECT pa.`id` As audit_id, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As tender_id, 0 As `audit_order`' +
  453. ' FROM ' +
  454. this.ctx.service.tender.tableName +
  455. ' As s' +
  456. ' LEFT JOIN ' +
  457. this.ctx.service.projectAccount.tableName +
  458. ' As pa' +
  459. ' ON s.user_id = pa.id' +
  460. ' WHERE s.id = ?';
  461. const sqlParam = [times, tender_id, tender_id];
  462. const user = await this.db.queryOne(sql, sqlParam);
  463. result.unshift(user);
  464. return result;
  465. }
  466. }
  467. return LedgerAudit;
  468. };