ledger_audit.js 24 KB

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