material_audit.js 56 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date 2019/2/27
  7. * @version
  8. */
  9. const auditConst = require('../const/audit').material;
  10. const pushType = require('../const/audit').pushType;
  11. const pushOperate = require('../const/spec_3f').pushOperate;
  12. const smsTypeConst = require('../const/sms_type');
  13. const wxConst = require('../const/wechat_template');
  14. const shenpiConst = require('../const/shenpi');
  15. const materialConst = require('../const/material');
  16. module.exports = app => {
  17. class MaterialAudit extends app.BaseService {
  18. /**
  19. * 构造函数
  20. *
  21. * @param {Object} ctx - egg全局变量
  22. * @return {void}
  23. */
  24. constructor(ctx) {
  25. super(ctx);
  26. this.tableName = 'material_audit';
  27. }
  28. /**
  29. * 获取 审核人信息
  30. *
  31. * @param {Number} materialId - 材料调差期id
  32. * @param {Number} auditorId - 审核人id
  33. * @param {Number} times - 第几次审批
  34. * @return {Promise<*>}
  35. */
  36. async getAuditor(materialId, auditorId, times = 1) {
  37. const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
  38. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
  39. ' WHERE la.`mid` = ? and la.`aid` = ? and la.`times` = ?';
  40. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditorId, times];
  41. return await this.db.queryOne(sql, sqlParam);
  42. }
  43. /**
  44. * 获取 审核列表信息
  45. *
  46. * @param {Number} materialId - 材料调差期id
  47. * @param {Number} times - 第几次审批
  48. * @return {Promise<*>}
  49. */
  50. async getAuditors(materialId, times = 1) {
  51. const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, g.`sort` ' +
  52. 'FROM ?? AS la, ?? AS pa, (SELECT t1.`aid`,(@i:=@i+1) as `sort` FROM (SELECT t.`aid`, t.`order` FROM (select `aid`, `order` from ?? WHERE `mid` = ? AND `times` = ? ORDER BY `order` LIMIT 200) t GROUP BY t.`aid` ORDER BY t.`order`) t1, (select @i:=0) as it) as g ' +
  53. 'WHERE la.`mid` = ? and la.`times` = ? and la.`aid` = pa.`id` and g.`aid` = la.`aid` order by la.`order`';
  54. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, materialId, times, materialId, times];
  55. const result = await this.db.query(sql, sqlParam);
  56. const sql2 = 'SELECT COUNT(a.`aid`) as num FROM (SELECT `aid` FROM ?? WHERE `mid` = ? AND `times` = ? GROUP BY `aid`) as a';
  57. const sqlParam2 = [this.tableName, materialId, times];
  58. const count = await this.db.queryOne(sql2, sqlParam2);
  59. for (const i in result) {
  60. result[i].max_sort = count.num;
  61. }
  62. return result;
  63. }
  64. async getFinalAuditGroup(materialId, times = 1) {
  65. const sql =
  66. 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, pa.`sign_path`, la.`times`, la.`mid`, Max(la.`order`) as max_order, GROUP_CONCAT(la.tp_data) as tp_data ' +
  67. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
  68. ' WHERE la.`mid` = ? and la.`times` = ? GROUP BY la.`aid` ORDER BY la.`order`';
  69. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, times];
  70. const result = await this.db.query(sql, sqlParam);
  71. for (const r of result) {
  72. const auditor = await this.getDataByCondition({mid: materialId, times: r.times, order: r.max_order});
  73. r.status = auditor.status;
  74. r.opinion = auditor.opinion;
  75. r.begin_time = auditor.begin_time;
  76. r.end_time = auditor.end_time;
  77. }
  78. return result;
  79. }
  80. /**
  81. * 获取 当前审核人
  82. *
  83. * @param {Number} materialId - 材料调差期id
  84. * @param {Number} times - 第几次审批
  85. * @return {Promise<*>}
  86. */
  87. async getCurAuditor(materialId, times = 1) {
  88. const sql = 'SELECT la.`id`, la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
  89. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
  90. ' WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?';
  91. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checking, times];
  92. return await this.db.queryOne(sql, sqlParam);
  93. }
  94. /**
  95. * 获取 最新审核顺序
  96. *
  97. * @param {Number} materialId - 材料调差期id
  98. * @param {Number} times - 第几次审批
  99. * @return {Promise<number>}
  100. */
  101. async getNewOrder(materialId, times = 1) {
  102. const sql = 'SELECT Max(??) As max_order FROM ?? Where `mid` = ? and `times` = ?';
  103. const sqlParam = ['order', this.tableName, materialId, times];
  104. const result = await this.db.queryOne(sql, sqlParam);
  105. return result && result.max_order ? result.max_order + 1 : 1;
  106. }
  107. /**
  108. * 新增审核人
  109. *
  110. * @param {Number} materialId - 材料调差期id
  111. * @param {Number} auditorId - 审核人id
  112. * @param {Number} times - 第几次审批
  113. * @return {Promise<number>}
  114. */
  115. async addAuditor(materialId, auditorId, times = 1, is_gdzs = 0) {
  116. const transaction = await this.db.beginTransaction();
  117. try {
  118. let newOrder = await this.getNewOrder(materialId, times);
  119. // 判断是否存在固定终审,存在则newOrder - 1并使终审order+1
  120. newOrder = is_gdzs === 1 ? newOrder - 1 : newOrder;
  121. if (is_gdzs) await this._syncOrderByDelete(transaction, materialId, newOrder, times, '+');
  122. const data = {
  123. tid: this.ctx.tender.id,
  124. mid: materialId,
  125. aid: auditorId,
  126. times,
  127. order: newOrder,
  128. status: auditConst.status.uncheck,
  129. };
  130. const result = await transaction.insert(this.tableName, data);
  131. await transaction.commit();
  132. return result.effectRows = 1;
  133. } catch (err) {
  134. await transaction.rollback();
  135. throw err;
  136. }
  137. return false;
  138. }
  139. /**
  140. * 移除审核人时,同步其后审核人order
  141. * @param transaction - 事务
  142. * @param {Number} materialId - 材料调差期id
  143. * @param {Number} auditorId - 审核人id
  144. * @param {Number} times - 第几次审批
  145. * @return {Promise<*>}
  146. * @private
  147. */
  148. async _syncOrderByDelete(transaction, materialId, order, times, selfOperate = '-') {
  149. this.initSqlBuilder();
  150. this.sqlBuilder.setAndWhere('mid', {
  151. value: materialId,
  152. operate: '=',
  153. });
  154. this.sqlBuilder.setAndWhere('order', {
  155. value: order,
  156. operate: '>=',
  157. });
  158. this.sqlBuilder.setAndWhere('times', {
  159. value: times,
  160. operate: '=',
  161. });
  162. this.sqlBuilder.setUpdateData('order', {
  163. value: 1,
  164. selfOperate: selfOperate,
  165. });
  166. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
  167. const data = await transaction.query(sql, sqlParam);
  168. return data;
  169. }
  170. /**
  171. * 移除审核人
  172. *
  173. * @param {Number} materialId - 材料调差期id
  174. * @param {Number} auditorId - 审核人id
  175. * @param {Number} times - 第几次审批
  176. * @return {Promise<boolean>}
  177. */
  178. async deleteAuditor(materialId, auditorId, times = 1) {
  179. const transaction = await this.db.beginTransaction();
  180. try {
  181. const condition = { mid: materialId, aid: auditorId, times };
  182. const auditor = await this.getDataByCondition(condition);
  183. if (!auditor) {
  184. throw '该审核人不存在';
  185. }
  186. await this._syncOrderByDelete(transaction, materialId, auditor.order, times);
  187. await transaction.delete(this.tableName, condition);
  188. await transaction.commit();
  189. } catch (err) {
  190. await transaction.rollback();
  191. throw err;
  192. }
  193. return true;
  194. }
  195. async getTpData(transaction, materialId, decimal = (this.ctx.material.decimal ? this.ctx.material.decimal : materialConst.decimal)) {
  196. const materialInfo = await transaction.get(this.ctx.service.material.tableName, { id: materialId });
  197. const tp_data = {
  198. m_tp: materialInfo.m_tp !== null ? this.ctx.helper.round(materialInfo.m_tp, decimal.tp) : null,
  199. tax_tp: materialInfo.material_tax ? (materialInfo.m_tax_tp ? materialInfo.m_tax_tp : materialInfo.m_tp) :
  200. (materialInfo.m_tp !== null ? this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), decimal.tp) : null),
  201. ex_tp: materialInfo.ex_tp !== null ? this.ctx.helper.round(materialInfo.ex_tp, decimal.tp) : null,
  202. ex_tax_tp: materialInfo.ex_tp !== null ? this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), decimal.tp) : null,
  203. }
  204. tp_data.total_tp = this.ctx.helper.add(tp_data.m_tp, tp_data.ex_tp);
  205. tp_data.total_tax_tp = !materialInfo.material_tax ? this.ctx.helper.add(tp_data.tax_tp, tp_data.ex_tax_tp) : tp_data.ex_tax_tp;
  206. if (materialInfo.is_stage_self) {
  207. const materialStageList = await transaction.select(this.ctx.service.materialStage.tableName, { where: { mid: materialId } });
  208. const stage_tp = [];
  209. for (const ms of materialStageList) {
  210. stage_tp.push({
  211. id: ms.id,
  212. sid: ms.sid,
  213. order: ms.order,
  214. m_tp: this.ctx.helper.round(ms.m_tp, decimal.tp),
  215. m_tax_tp: this.ctx.helper.round(ms.m_tax_tp, decimal.tp),
  216. });
  217. }
  218. tp_data.stage_tp = stage_tp;
  219. }
  220. return tp_data;
  221. }
  222. /**
  223. * 开始审批
  224. * @param {Number} materialId - 材料调差期id
  225. * @param {Number} times - 第几次审批
  226. * @return {Promise<boolean>}
  227. */
  228. async start(materialId, times = 1) {
  229. const audit = await this.getDataByCondition({ mid: materialId, times, order: 1 });
  230. if (!audit) {
  231. if(this.ctx.tender.info.shenpi.material === shenpiConst.sp_status.gdspl) {
  232. throw '请联系管理员添加审批人';
  233. } else {
  234. throw '请先选择审批人,再上报数据';
  235. }
  236. }
  237. const transaction = await this.db.beginTransaction();
  238. try {
  239. const tp_data = await this.getTpData(transaction, materialId);
  240. await transaction.update(this.tableName, { id: audit.id, status: auditConst.status.checking, begin_time: new Date(), tp_data: JSON.stringify(tp_data) });
  241. await transaction.update(this.ctx.service.material.tableName, {
  242. id: materialId, status: auditConst.status.checking, tp_data: JSON.stringify(tp_data),
  243. });
  244. // 本期一些必要数据(如应耗数量和上期调差金额)插入到material_bills_history表里
  245. const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
  246. if (materialBillsData.length === 0 && this.ctx.material.ex_expr === null) {
  247. throw '请至少使用一种调差方式';
  248. }
  249. // 微信模板通知
  250. const materialInfo = await this.ctx.service.material.getDataById(materialId);
  251. const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
  252. const wechatData = {
  253. qi: materialInfo.order,
  254. status: wxConst.status.check,
  255. tips: wxConst.tips.check,
  256. begin_time: Date.parse(new Date()),
  257. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  258. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  259. };
  260. await this.ctx.helper.sendWechat(audit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
  261. await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
  262. pid: this.ctx.session.sessionProject.id,
  263. tid: this.ctx.tender.id,
  264. uid: audit.aid,
  265. sp_type: 'material',
  266. sp_id: audit.id,
  267. table_name: this.tableName,
  268. template: wxConst.template.material,
  269. wx_data: wechatData,
  270. });
  271. // todo 更新标段tender状态 ?
  272. // 检查三方特殊推送
  273. await this.ctx.service.specMsg.addMaterialMsg(transaction, this.ctx.session.sessionProject.id, materialInfo, pushOperate.material.flow);
  274. await transaction.commit();
  275. } catch (err) {
  276. await transaction.rollback();
  277. throw err;
  278. }
  279. return true;
  280. }
  281. async _checked(pid, materialId, checkData, times) {
  282. const time = new Date();
  283. // 整理当前流程审核人状态更新
  284. const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking });
  285. if (!audit) {
  286. throw '审核数据错误';
  287. }
  288. // 获取审核人列表
  289. const sql = 'SELECT `tid`, `mid`, `aid`, `order` FROM ?? WHERE `mid` = ? and `times` = ? GROUP BY `aid` ORDER BY `id` ASC';
  290. const sqlParam = [this.tableName, materialId, times];
  291. const auditors = await this.db.query(sql, sqlParam);
  292. const nextAudit = await this.getDataByCondition({ mid: materialId, times, order: audit.order + 1 });
  293. const transaction = await this.db.beginTransaction();
  294. try {
  295. // 获取当前总金额及独立单价期的金额,添加到tp_data中,报表使用
  296. const tp_data = await this.getTpData(transaction, materialId);
  297. await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time, tp_data: JSON.stringify(tp_data) });
  298. await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, audit.id);
  299. // 获取推送必要信息
  300. const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
  301. // 添加推送
  302. const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checked, content: noticeContent }];
  303. auditors.forEach(audit => {
  304. records.push({ pid, type: pushType.material, uid: audit.aid, status: auditConst.status.checked, content: noticeContent });
  305. });
  306. await transaction.insert('zh_notice', records);
  307. const begin_audit = await this.getDataByCondition({
  308. mid: materialId,
  309. order: 1,
  310. });
  311. const materialInfo = await this.ctx.service.material.getDataById(materialId);
  312. const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
  313. // 无下一审核人表示,审核结束
  314. if (nextAudit) {
  315. // 复制一份下一审核人数据
  316. // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, nextAudit.order, transaction);
  317. // 流程至下一审批人
  318. await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time, tp_data: JSON.stringify(tp_data) });
  319. // 同步 期信息
  320. await transaction.update(this.ctx.service.material.tableName, {
  321. id: materialId, status: auditConst.status.checking,
  322. });
  323. // 微信模板通知
  324. const wechatData = {
  325. qi: materialInfo.order,
  326. status: wxConst.status.check,
  327. tips: wxConst.tips.check,
  328. begin_time: Date.parse(begin_audit.begin_time),
  329. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  330. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  331. };
  332. await this.ctx.helper.sendWechat(nextAudit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
  333. await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
  334. pid: this.ctx.session.sessionProject.id,
  335. tid: this.ctx.tender.id,
  336. uid: nextAudit.aid,
  337. sp_type: 'material',
  338. sp_id: nextAudit.id,
  339. table_name: this.tableName,
  340. template: wxConst.template.material,
  341. wx_data: wechatData,
  342. });
  343. // 检查三方特殊推送
  344. await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
  345. } else {
  346. // 本期结束
  347. // 同步 期信息
  348. await transaction.update(this.ctx.service.material.tableName, {
  349. id: materialId, status: checkData.checkType,
  350. });
  351. // 处理旧数据,防止重复插入到历史表
  352. await transaction.delete(this.ctx.service.materialBillsHistory.tableName, { tid: this.ctx.tender.id, order: this.ctx.material.order});
  353. const mbhList = [];
  354. const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
  355. for (const mb of materialBillsData) {
  356. if (mb.code === '') {
  357. throw '调差工料编号不能为空';
  358. }
  359. const newMbh = {
  360. tid: this.ctx.tender.id,
  361. mid: this.ctx.material.id,
  362. order: this.ctx.material.order,
  363. mb_id: mb.id,
  364. quantity: mb.quantity,
  365. expr: mb.expr,
  366. msg_tp: mb.msg_tp,
  367. msg_times: mb.msg_times,
  368. msg_spread: mb.msg_spread,
  369. m_up_risk: mb.m_up_risk,
  370. m_down_risk: mb.m_down_risk,
  371. m_spread: mb.m_spread,
  372. m_tp: mb.m_tp,
  373. pre_tp: mb.pre_tp,
  374. m_tax_tp: mb.m_tax_tp,
  375. tax_pre_tp: mb.tax_pre_tp,
  376. origin: mb.origin,
  377. is_summary: mb.is_summary,
  378. m_tax: mb.m_tax,
  379. };
  380. mbhList.push(newMbh);
  381. }
  382. if(mbhList.length !== 0) await transaction.insert(this.ctx.service.materialBillsHistory.tableName, mbhList);
  383. // 处理旧数据,防止重复插入到历史表
  384. await transaction.delete(this.ctx.service.materialExponentHistory.tableName, { tid: this.ctx.tender.id, order: this.ctx.material.order});
  385. const materialExponentData = await this.ctx.service.materialExponent.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
  386. const mehList = [];
  387. for (const me of materialExponentData) {
  388. const newMeh = {
  389. tid: this.ctx.tender.id,
  390. mid: this.ctx.material.id,
  391. order: this.ctx.material.order,
  392. me_id: me.id,
  393. type: me.type,
  394. weight_num: me.weight_num,
  395. basic_price: me.basic_price,
  396. basic_times: me.basic_times,
  397. m_price: me.m_price,
  398. calc_num: me.calc_num,
  399. is_summary: me.is_summary,
  400. };
  401. mehList.push(newMeh);
  402. }
  403. if(mehList.length !== 0) await transaction.insert(this.ctx.service.materialExponentHistory.tableName, mehList);
  404. // 微信模板通知
  405. const users = this._.uniq(this._.concat(this._.map(auditors, 'aid'), materialInfo.user_id));
  406. const wechatData = {
  407. qi: materialInfo.order,
  408. status: wxConst.status.success,
  409. tips: wxConst.tips.success,
  410. begin_time: Date.parse(begin_audit.begin_time),
  411. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  412. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  413. };
  414. await this.ctx.helper.sendWechat(users, smsTypeConst.const.TC, smsTypeConst.judge.result.toString(), wxConst.template.material, wechatData);
  415. // 检查三方特殊推送
  416. await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
  417. await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.checked);
  418. // 添加短信通知-审批通过提醒功能
  419. // const mobile_array = [];
  420. // const stageInfo = await this.ctx.service.stage.getDataById(stageId);
  421. // const auditList = await this.getAuditors(stageId, stageInfo.times);
  422. // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id);
  423. // if (smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') {
  424. // const smsType = JSON.parse(smsUser1.sms_type);
  425. // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  426. // mobile_array.push(smsUser1.auth_mobile);
  427. // }
  428. // }
  429. // for (const user of auditList) {
  430. // const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid);
  431. // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
  432. // const smsType = JSON.parse(smsUser.sms_type);
  433. // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  434. // mobile_array.push(smsUser.auth_mobile);
  435. // }
  436. // }
  437. // }
  438. // if (mobile_array.length > 0) {
  439. // const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid);
  440. // const sms = new SMS(this.ctx);
  441. // const tenderName = await sms.contentChange(tenderInfo.name);
  442. // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,审批通过。';
  443. // sms.send(mobile_array, content);
  444. // }
  445. }
  446. await transaction.commit();
  447. } catch (err) {
  448. await transaction.rollback();
  449. throw err;
  450. }
  451. }
  452. async _checkNo(pid, materialId, checkData, times) {
  453. const time = new Date();
  454. // 整理当前流程审核人状态更新
  455. const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking });
  456. if (!audit) {
  457. throw '审核数据错误';
  458. }
  459. const sql = 'SELECT `tid`, `mid`, `aid`, `order` FROM ?? WHERE `mid` = ? and `times` = ? GROUP BY `aid` ORDER BY `id` ASC';
  460. const sqlParam = [this.tableName, materialId, times];
  461. const auditors = await this.db.query(sql, sqlParam);
  462. let order = 1;
  463. for (const a of auditors) {
  464. a.times = times + 1;
  465. a.order = order;
  466. a.status = auditConst.status.uncheck;
  467. order++;
  468. }
  469. const transaction = await this.db.beginTransaction();
  470. try {
  471. const tp_data = await this.getTpData(transaction, materialId);
  472. await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time, tp_data: JSON.stringify(tp_data), });
  473. await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, audit.id);
  474. // 添加到消息推送表
  475. const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
  476. const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNo, content: noticeContent }];
  477. auditors.forEach(audit => {
  478. records.push({ pid, type: pushType.material, uid: audit.aid, status: auditConst.status.checkNo, content: noticeContent });
  479. });
  480. await transaction.insert(this.ctx.service.noticePush.tableName, records);
  481. // 同步期信息
  482. await transaction.update(this.ctx.service.material.tableName, {
  483. id: materialId, status: checkData.checkType,
  484. times: times + 1,
  485. tp_data: JSON.stringify(tp_data),
  486. });
  487. // 拷贝新一次审核流程列表
  488. await transaction.insert(this.tableName, auditors);
  489. // // 删除material_bills_history信息
  490. // await transaction.delete(this.ctx.service.materialBillsHistory.tableName, {
  491. // tid: this.ctx.tender.id,
  492. // order: this.ctx.material.order,
  493. // });
  494. // // 删除material_exponent_history信息
  495. // await transaction.delete(this.ctx.service.materialExponentHistory.tableName, {
  496. // tid: this.ctx.tender.id,
  497. // order: this.ctx.material.order,
  498. // });
  499. // 微信模板通知
  500. const begin_audit = await this.getDataByCondition({
  501. mid: materialId,
  502. order: 1,
  503. });
  504. const materialInfo = await this.ctx.service.material.getDataById(materialId);
  505. const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
  506. const users = this._.uniq(this._.concat(this._.map(auditors, 'aid'), materialInfo.user_id));
  507. const wechatData = {
  508. qi: materialInfo.order,
  509. status: wxConst.status.back,
  510. tips: wxConst.tips.back,
  511. begin_time: Date.parse(begin_audit.begin_time),
  512. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  513. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  514. };
  515. await this.ctx.helper.sendWechat(users, smsTypeConst.const.TC, smsTypeConst.judge.result.toString(), wxConst.template.material, wechatData);
  516. // 检查三方特殊推送
  517. await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
  518. // 添加短信通知-审批退回提醒功能
  519. // const mobile_array = [];
  520. // const stageInfo = await this.ctx.service.stage.getDataById(stageId);
  521. // const auditList = await this.getAuditors(stageId, stageInfo.times);
  522. // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id);
  523. // if (smsUser1.auth_mobile !== '' && smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') {
  524. // const smsType = JSON.parse(smsUser1.sms_type);
  525. // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  526. // mobile_array.push(smsUser1.auth_mobile);
  527. // }
  528. // }
  529. // for (const user of auditList) {
  530. // const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid);
  531. // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
  532. // const smsType = JSON.parse(smsUser.sms_type);
  533. // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
  534. // mobile_array.push(smsUser.auth_mobile);
  535. // }
  536. // }
  537. // }
  538. // if (mobile_array.length > 0) {
  539. // const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid);
  540. // const sms = new SMS(this.ctx);
  541. // const tenderName = await sms.contentChange(tenderInfo.name);
  542. // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,审批退回。';
  543. // sms.send(mobile_array, content);
  544. // }
  545. await transaction.commit();
  546. } catch (err) {
  547. await transaction.rollback();
  548. throw err;
  549. }
  550. }
  551. async _checkNoPre(pid, materialId, checkData, times) {
  552. const time = new Date();
  553. // 整理当前流程审核人状态更新
  554. const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking });
  555. if (!audit || audit.order <= 1) {
  556. throw '审核数据错误';
  557. }
  558. // 添加重新审批后,不能用order-1,取groupby值里的上一个才对
  559. const auditors2 = await this.getAuditGroupByList(materialId, times);
  560. const auditorIndex = await auditors2.findIndex(function(item) {
  561. return item.aid === audit.aid;
  562. });
  563. const preAuditor = auditors2[auditorIndex - 1];
  564. const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
  565. const transaction = await this.db.beginTransaction();
  566. try {
  567. const tp_data = await this.getTpData(transaction, materialId);
  568. // 添加到消息推送表
  569. const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNoPre, content: noticeContent }];
  570. auditors2.forEach(audit => {
  571. records.push({ pid, type: pushType.material, uid: audit.aid, status: auditConst.status.checkNoPre, content: noticeContent });
  572. });
  573. await transaction.insert('zh_notice', records);
  574. await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time, tp_data: JSON.stringify(tp_data) });
  575. await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, audit.id);
  576. // 顺移气候审核人流程顺序
  577. this.initSqlBuilder();
  578. this.sqlBuilder.setAndWhere('mid', { value: materialId, operate: '=' });
  579. this.sqlBuilder.setAndWhere('order', { value: audit.order, operate: '>' });
  580. this.sqlBuilder.setUpdateData('order', { value: 2, selfOperate: '+' });
  581. const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
  582. const data = await transaction.query(sql, sqlParam);
  583. const newAuditors = {
  584. tid: audit.tid, mid: audit.mid, aid: preAuditor.aid,
  585. times: audit.times, order: audit.order + 1, status: auditConst.status.checking,
  586. begin_time: time,
  587. tp_data: JSON.stringify(tp_data),
  588. };
  589. const checking_result = await transaction.insert(this.tableName, newAuditors);
  590. const uncheckNewAuditors = {
  591. tid: audit.tid, mid: audit.mid, aid: audit.aid,
  592. times: audit.times, order: audit.order + 2, status: auditConst.status.uncheck,
  593. };
  594. await transaction.insert(this.tableName, uncheckNewAuditors);
  595. // 微信模板通知
  596. const begin_audit = await this.getDataByCondition({
  597. mid: materialId,
  598. order: 1,
  599. });
  600. const materialInfo = await this.ctx.service.material.getDataById(materialId);
  601. const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
  602. const wechatData = {
  603. qi: materialInfo.order,
  604. status: wxConst.status.check,
  605. tips: wxConst.tips.check,
  606. begin_time: Date.parse(begin_audit.begin_time),
  607. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  608. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  609. };
  610. await this.ctx.helper.sendWechat(preAuditor.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
  611. await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
  612. pid: this.ctx.session.sessionProject.id,
  613. tid: this.ctx.tender.id,
  614. uid: preAuditor.aid,
  615. sp_type: 'material',
  616. sp_id: checking_result.id,
  617. table_name: this.tableName,
  618. template: wxConst.template.material,
  619. wx_data: wechatData,
  620. });
  621. // 检查三方特殊推送
  622. await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
  623. await transaction.commit();
  624. } catch (error) {
  625. await transaction.rollback();
  626. throw error;
  627. }
  628. }
  629. /**
  630. * 审批
  631. * @param {Number} materialId - 材料调差期id
  632. * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果
  633. * @param {Number} times - 第几次审批
  634. * @return {Promise<void>}
  635. */
  636. async check(materialId, checkData, times = 1) {
  637. if (checkData.checkType !== auditConst.status.checked && checkData.checkType !== auditConst.status.checkNo && checkData.checkType !== auditConst.status.checkNoPre) {
  638. throw '提交数据错误';
  639. }
  640. const pid = this.ctx.session.sessionProject.id;
  641. switch (checkData.checkType) {
  642. case auditConst.status.checked:
  643. await this._checked(pid, materialId, checkData, times);
  644. break;
  645. case auditConst.status.checkNo:
  646. await this._checkNo(pid, materialId, checkData, times);
  647. break;
  648. case auditConst.status.checkNoPre:
  649. await this._checkNoPre(pid, materialId, checkData, times);
  650. break;
  651. default:
  652. throw '无效审批操作';
  653. }
  654. }
  655. /**
  656. * 用于添加推送所需的content内容
  657. * @param {Number} pid 项目id
  658. * @param {Number} tid 台账id
  659. * @param {Number} mid 期id
  660. * @param {Number} uid 审批人id
  661. */
  662. async getNoticeContent(pid, tid, mid, uid, opinion = '') {
  663. const noticeSql = 'SELECT * FROM (SELECT ' +
  664. ' t.`id` As `tid`, ma.`mid`, t.`name`, m.`order`, pa.`name` As `su_name`, pa.role As `su_role`' +
  665. ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' +
  666. ' LEFT JOIN ?? As m On t.`id` = m.`tid` AND m.`id` = ?' +
  667. ' LEFT JOIN ?? As ma ON m.`id` = ma.`mid`' +
  668. ' LEFT JOIN ?? As pa ON pa.`id` = ?' +
  669. ' WHERE t.`project_id` = ? ) as new_t GROUP BY new_t.`tid`';
  670. const noticeSqlParam = [this.ctx.service.tender.tableName, tid, this.ctx.service.material.tableName, mid, this.tableName, this.ctx.service.projectAccount.tableName, uid, pid];
  671. const content = await this.db.query(noticeSql, noticeSqlParam);
  672. if (content.length) {
  673. content[0].opinion = opinion;
  674. }
  675. return content.length ? JSON.stringify(content[0]) : '';
  676. }
  677. /**
  678. * 审批
  679. * @param {Number} materialId - 材料调差期id
  680. * @param {Number} times - 第几次审批
  681. * @return {Promise<void>}
  682. */
  683. async checkAgain(materialId, times = 1) {
  684. const time = new Date();
  685. // 整理当前流程审核人状态更新
  686. const audit = (await this.getAllDataByCondition({ where: { mid: materialId, times }, orders: [['order', 'desc']], limit: 1, offset: 0 }))[0];
  687. if (!audit || audit.order < 1) {
  688. throw '审核数据错误';
  689. }
  690. const transaction = await this.db.beginTransaction();
  691. try {
  692. const materialInfo = await this.ctx.service.material.getDataById(materialId);
  693. const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
  694. const tp_data = await this.getTpData(transaction, materialId, material_decimal);
  695. // 当前审批人2次添加至流程中
  696. const newAuditors = {
  697. tid: audit.tid, mid: audit.mid, aid: audit.aid,
  698. times: audit.times, order: audit.order + 1, status: auditConst.status.checkAgain,
  699. begin_time: time, end_time: time, opinion: '',
  700. };
  701. await transaction.insert(this.tableName, newAuditors);
  702. const checkingNewAuditors = {
  703. tid: audit.tid, mid: audit.mid, aid: audit.aid,
  704. times: audit.times, order: audit.order + 2, status: auditConst.status.checking,
  705. begin_time: time,
  706. tp_data: JSON.stringify(tp_data),
  707. };
  708. const checking_result = await transaction.insert(this.tableName, checkingNewAuditors);
  709. // 本期结束
  710. // 同步 期信息
  711. await transaction.update(this.ctx.service.material.tableName, {
  712. id: materialId, status: auditConst.status.checking,
  713. });
  714. // 微信模板通知
  715. const wechatData = {
  716. qi: materialInfo.order,
  717. status: wxConst.status.check,
  718. tips: wxConst.tips.check,
  719. begin_time: Date.parse(new Date()),
  720. m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
  721. hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
  722. };
  723. await this.ctx.helper.sendWechat(audit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
  724. await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
  725. pid: this.ctx.session.sessionProject.id,
  726. tid: this.ctx.tender.id,
  727. uid: audit.aid,
  728. sp_type: 'material',
  729. sp_id: checking_result.insertId,
  730. table_name: this.tableName,
  731. template: wxConst.template.material,
  732. wx_data: wechatData,
  733. });
  734. // 检查三方特殊推送
  735. await this.ctx.service.specMsg.addMaterialMsg(transaction, this.ctx.session.sessionProject.id, materialInfo, pushOperate.material.flow);
  736. await transaction.commit();
  737. } catch (err) {
  738. await transaction.rollback();
  739. throw err;
  740. }
  741. }
  742. /**
  743. * 获取审核人需要审核的期列表
  744. *
  745. * @param auditorId
  746. * @return {Promise<*>}
  747. */
  748. async getAuditMaterial(auditorId) {
  749. const sql = 'SELECT ma.`aid`, ma.`times`, ma.`order`, ma.`begin_time`, ma.`end_time`, ma.`tid`, ma.`mid`,' +
  750. ' m.`order` As `morder`, m.`status` As `mstatus`,' +
  751. ' t.`name`, t.`project_id`, t.`type`, t.`user_id` ' +
  752. ' FROM ?? AS ma, ?? AS m, ?? As t ' +
  753. ' WHERE ((ma.`aid` = ? and ma.`status` = ?) OR (m.`user_id` = ? and ma.`status` = ? and m.`status` = ? and ma.`times` = (m.`times`-1)))' +
  754. ' and ma.`mid` = m.`id` and ma.`tid` = t.`id` ORDER BY ma.`begin_time` DESC';
  755. const sqlParam = [this.tableName, this.ctx.service.material.tableName, this.ctx.service.tender.tableName, auditorId, auditConst.status.checking, auditorId, auditConst.status.checkNo, auditConst.status.checkNo];
  756. return await this.db.query(sql, sqlParam);
  757. }
  758. /**
  759. * 获取审核人审核的次数
  760. *
  761. * @param auditorId
  762. * @return {Promise<*>}
  763. */
  764. async getCountByChecked(auditorId) {
  765. return await this.db.count(this.tableName, { aid: auditorId, status: [auditConst.status.checked, auditConst.status.checkNo, auditConst.status.checkNoPre] });
  766. }
  767. /**
  768. * 获取最近一次审批结束时间
  769. *
  770. * @param auditorId
  771. * @return {Promise<*>}
  772. */
  773. async getLastEndTimeByChecked(auditorId) {
  774. const sql = 'SELECT `end_time` FROM ?? WHERE `aid` = ? ' +
  775. 'AND `status` in (' + this.ctx.helper.getInArrStrSqlFilter([auditConst.status.checked, auditConst.status.checkNo]) + ') ORDER BY `end_time` DESC';
  776. const sqlParam = [this.tableName, auditorId];
  777. const result = await this.db.queryOne(sql, sqlParam);
  778. return result ? result.end_time : null;
  779. }
  780. /**
  781. * 获取 某时间后 审批进度 更新的期
  782. * @param {Number} pid - 查询标段
  783. * @param {Number} uid - 查询人
  784. * @param {Date} time - 查询时间
  785. * @return {Promise<*>}
  786. */
  787. async getNoticeMaterial(pid, uid, time) {
  788. // const sql = 'SELECT * FROM (SELECT t.`name`, t.`project_id`, t.`type`, t.`user_id`, ' +
  789. // ' m.`order` As `m_order`, m.`status` As `m_status`, ' +
  790. // ' ma.`aid`, ma.`times`, ma.`order`, ma.`end_time`, ma.`tid`, ma.`mid`, ma.`status`, ' +
  791. // ' pa.`name` As `su_name`, pa.role As `su_role`, pa.company As `su_company`' +
  792. // ' FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tid` FROM ?? WHERE `aid` = ? GROUP BY `tid`)) As t' +
  793. // ' LEFT JOIN ?? As m On t.`id` = m.`tid`' +
  794. // ' LEFT JOIN ?? As ma ON m.`id` = ma.`mid`' +
  795. // ' LEFT JOIN ?? As pa ON ma.`aid` = pa.`id`' +
  796. // ' WHERE ma.`end_time` > ? and t.`project_id` = ?' +
  797. // ' ORDER By ma.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`tid`' +
  798. // ' ORDER BY new_t.`end_time`';
  799. // const sqlParam = [this.ctx.service.tender.tableName, uid, this.tableName, uid, this.ctx.service.material.tableName, this.tableName,
  800. // this.ctx.service.projectAccount.tableName, time, pid];
  801. // return await this.db.query(sql, sqlParam);
  802. let notice = await this.db.select('zh_notice', {
  803. where: { pid, type: pushType.material, uid },
  804. orders: [['create_time', 'desc']],
  805. limit: 10, offset: 0,
  806. });
  807. notice = notice.map(v => {
  808. const extra = JSON.parse(v.content);
  809. delete v.content;
  810. return { ...v, ...extra };
  811. });
  812. return notice;
  813. }
  814. /**
  815. * 获取审核人流程列表
  816. *
  817. * @param auditorId
  818. * @return {Promise<*>}
  819. */
  820. async getAuditGroupByList(materialId, times) {
  821. const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' +
  822. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
  823. ' WHERE la.`mid` = ? and la.`times` = ? GROUP BY la.`aid` ORDER BY la.`order`';
  824. const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, times];
  825. return await this.db.query(sql, sqlParam);
  826. }
  827. /**
  828. * 获取审核人流程列表(包括原报)
  829. * @param {Number} materialId 调差id
  830. * @param {Number} times 审核次数
  831. * @return {Promise<Array>} 查询结果集(包括原报)
  832. */
  833. async getAuditorsWithOwner(materialId, times = 1) {
  834. const result = await this.getAuditGroupByList(materialId, times);
  835. const sql =
  836. 'SELECT pa.`id` As aid, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As mid, 0 As `order`' +
  837. ' FROM ' +
  838. this.ctx.service.material.tableName +
  839. ' As s' +
  840. ' LEFT JOIN ' +
  841. this.ctx.service.projectAccount.tableName +
  842. ' As pa' +
  843. ' ON s.user_id = pa.id' +
  844. ' WHERE s.id = ?';
  845. const sqlParam = [times, materialId, materialId];
  846. const user = await this.db.queryOne(sql, sqlParam);
  847. result.unshift(user);
  848. return result;
  849. }
  850. /**
  851. * 复制上一期的审批人列表给最新一期
  852. *
  853. * @param transaction - 新增一期的事务
  854. * @param {Object} preMaterial - 上一期
  855. * @param {Object} newaMaterial - 最新一期
  856. * @return {Promise<*>}
  857. */
  858. async copyPreMaterialAuditors(transaction, preMaterial, newMaterial) {
  859. const auditors = await this.getAuditGroupByList(preMaterial.id, preMaterial.times);
  860. const newAuditors = [];
  861. for (const a of auditors) {
  862. const na = {
  863. tid: preMaterial.tid,
  864. mid: newMaterial.id,
  865. aid: a.aid,
  866. times: newMaterial.times,
  867. order: newAuditors.length + 1,
  868. status: auditConst.status.uncheck,
  869. };
  870. newAuditors.push(na);
  871. }
  872. const result = await transaction.insert(this.tableName, newAuditors);
  873. return result.affectedRows === auditors.length;
  874. }
  875. /**
  876. * 移除审核人
  877. *
  878. * @param {Number} materialId - 材料调差期id
  879. * @param {Number} status - 期状态
  880. * @param {Number} status - 期次数
  881. * @return {Promise<boolean>}
  882. */
  883. async getAuditorByStatus(materialId, status, times = 1) {
  884. let auditor = null;
  885. let sql = '';
  886. let sqlParam = '';
  887. switch (status) {
  888. case auditConst.status.checking :
  889. case auditConst.status.checked :
  890. case auditConst.status.checkNoPre :
  891. sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' +
  892. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
  893. ' WHERE la.`mid` = ? and la.`status` = ? ' +
  894. ' ORDER BY la.`times` desc, la.`order` desc';
  895. sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, status];
  896. auditor = await this.db.queryOne(sql, sqlParam);
  897. break;
  898. case auditConst.status.checkNo :
  899. sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' +
  900. ' FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
  901. ' WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?' +
  902. ' ORDER BY la.`times` desc, la.`order` desc';
  903. sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checkNo, parseInt(times) - 1];
  904. auditor = await this.db.queryOne(sql, sqlParam);
  905. break;
  906. case auditConst.status.uncheck :
  907. default:break;
  908. }
  909. return auditor;
  910. }
  911. async getAllAuditors(tenderId) {
  912. const sql = 'SELECT ma.aid, ma.tid FROM ' + this.tableName + ' ma' +
  913. ' LEFT JOIN ' + this.ctx.service.tender.tableName + ' t On ma.tid = t.id' +
  914. ' WHERE t.id = ?' +
  915. ' GROUP BY ma.aid';
  916. const sqlParam = [tenderId];
  917. return this.db.query(sql, sqlParam);
  918. }
  919. async updateNewAuditList(material, newIdList) {
  920. const transaction = await this.db.beginTransaction();
  921. try {
  922. // 先删除旧的审批流,再添加新的
  923. await transaction.delete(this.tableName, { mid: material.id, times: material.times });
  924. const newAuditors = [];
  925. let order = 1;
  926. for (const aid of newIdList) {
  927. newAuditors.push({
  928. tid: material.tid, mid: material.id, aid,
  929. times: material.times, order, status: auditConst.status.uncheck,
  930. });
  931. order++;
  932. }
  933. if(newAuditors.length > 0) await transaction.insert(this.tableName, newAuditors);
  934. await transaction.commit();
  935. } catch (err) {
  936. await transaction.rollback();
  937. throw err;
  938. }
  939. }
  940. async updateLastAudit(material, auditList, lastId) {
  941. const transaction = await this.db.beginTransaction();
  942. try {
  943. // 先判断auditList里的aid是否与lastId相同,相同则删除并重新更新order
  944. const idList = this._.map(auditList, 'aid');
  945. let order = idList.length + 1;
  946. if (idList.indexOf(lastId) !== -1) {
  947. await transaction.delete(this.tableName, { mid: material.id, times: material.times, aid: lastId });
  948. const audit = this._.find(auditList, { 'aid': lastId });
  949. // 顺移之后审核人流程顺序
  950. await this._syncOrderByDelete(transaction, material.id, audit.order, material.times);
  951. order = order - 1;
  952. }
  953. // 添加终审
  954. const newAuditor = {
  955. tid: material.tid, mid: material.id, aid: lastId,
  956. times: material.times, order, status: auditConst.status.uncheck,
  957. };
  958. await transaction.insert(this.tableName, newAuditor);
  959. await transaction.commit();
  960. } catch (err) {
  961. await transaction.rollback();
  962. throw err;
  963. }
  964. }
  965. async getNumByMonth(tid, startMonth, endMonth) {
  966. const sql = 'SELECT COUNT(*) as num FROM ?? t1 JOIN (SELECT MAX(id) as max_id FROM ?? WHERE tid = ? GROUP BY mid) t2 ON t1.id = t2.max_id WHERE t1.status = ? AND t1.end_time between ? and ?';
  967. // const sql = 'SELECT COUNT(*) as num FROM ?? WHERE id in (SELECT MAX(id) FROM ?? WHERE tid = ? GROUP BY mid) AND status = ? AND end_time between ? and ?';
  968. const sqlParam = [this.tableName, this.tableName, tid, auditConst.status.checked, startMonth, endMonth];
  969. const result = await this.db.queryOne(sql, sqlParam);
  970. return result ? result.num : 0;
  971. }
  972. }
  973. return MaterialAudit;
  974. };