'use strict'; /** * * * @author Mai * @date 2019/2/27 * @version */ const auditConst = require('../const/audit').material; const pushType = require('../const/audit').pushType; module.exports = app => { class MaterialAudit extends app.BaseService { /** * 构造函数 * * @param {Object} ctx - egg全局变量 * @return {void} */ constructor(ctx) { super(ctx); this.tableName = 'material_audit'; } /** * 获取 审核人信息 * * @param {Number} materialId - 材料调差期id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getAuditor(materialId, auditorId, times = 1) { 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` ' + 'FROM ?? AS la, ?? AS pa ' + 'WHERE la.`mid` = ? and la.`aid` = ? and la.`times` = ?' + ' and la.`aid` = pa.`id`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditorId, times]; return await this.db.queryOne(sql, sqlParam); } /** * 获取 审核列表信息 * * @param {Number} materialId - 材料调差期id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getAuditors(materialId, times = 1) { 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` ' + 'FROM ?? AS la, ?? AS pa, (SELECT `aid`,(@i:=@i+1) as `sort` FROM ??, (select @i:=0) as it WHERE `mid` = ? AND `times` = ? GROUP BY `aid`) as g ' + 'WHERE la.`mid` = ? and la.`times` = ? and la.`aid` = pa.`id` and g.`aid` = la.`aid` order by la.`order`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, materialId, times, materialId, times]; const result = await this.db.query(sql, sqlParam); const sql2 = 'SELECT COUNT(a.`aid`) as num FROM (SELECT `aid` FROM ?? WHERE `mid` = ? AND `times` = ? GROUP BY `aid`) as a'; const sqlParam2 = [this.tableName, materialId, times]; const count = await this.db.queryOne(sql2, sqlParam2); for (const i in result) { result[i].max_sort = count.num; } return result; } /** * 获取 当前审核人 * * @param {Number} materialId - 材料调差期id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getCurAuditor(materialId, times = 1) { 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` ' + 'FROM ?? AS la, ?? AS pa ' + 'WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?' + ' and la.`aid` = pa.`id`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checking, times]; return await this.db.queryOne(sql, sqlParam); } /** * 获取 最新审核顺序 * * @param {Number} materialId - 材料调差期id * @param {Number} times - 第几次审批 * @return {Promise} */ async getNewOrder(materialId, times = 1) { const sql = 'SELECT Max(??) As max_order FROM ?? Where `mid` = ? and `times` = ?'; const sqlParam = ['order', this.tableName, materialId, times]; const result = await this.db.queryOne(sql, sqlParam); return result && result.max_order ? result.max_order + 1 : 1; } /** * 新增审核人 * * @param {Number} materialId - 材料调差期id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise} */ async addAuditor(materialId, auditorId, times = 1) { const newOrder = await this.getNewOrder(materialId, times); const data = { tid: this.ctx.tender.id, mid: materialId, aid: auditorId, times, order: newOrder, status: auditConst.status.uncheck, }; const result = await this.db.insert(this.tableName, data); return result.effectRows = 1; } /** * 移除审核人时,同步其后审核人order * @param transaction - 事务 * @param {Number} materialId - 材料调差期id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise<*>} * @private */ async _syncOrderByDelete(transaction, materialId, order, times) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere('mid', { value: materialId, operate: '=', }); this.sqlBuilder.setAndWhere('order', { value: order, operate: '>=', }); this.sqlBuilder.setAndWhere('times', { value: times, operate: '=', }); this.sqlBuilder.setUpdateData('order', { value: 1, selfOperate: '-', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await transaction.query(sql, sqlParam); return data; } /** * 移除审核人 * * @param {Number} materialId - 材料调差期id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise} */ async deleteAuditor(materialId, auditorId, times = 1) { const transaction = await this.db.beginTransaction(); try { const condition = { mid: materialId, aid: auditorId, times }; const auditor = await this.getDataByCondition(condition); if (!auditor) { throw '该审核人不存在'; } await this._syncOrderByDelete(transaction, materialId, auditor.order, times); await transaction.delete(this.tableName, condition); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } return true; } /** * 开始审批 * @param {Number} materialId - 材料调差期id * @param {Number} times - 第几次审批 * @return {Promise} */ async start(materialId, times = 1) { const audit = await this.getDataByCondition({ mid: materialId, times, order: 1 }); if (!audit) { throw '请先选择审批人,再上报数据'; } const transaction = await this.db.beginTransaction(); try { await transaction.update(this.tableName, { id: audit.id, status: auditConst.status.checking, begin_time: new Date() }); await transaction.update(this.ctx.service.material.tableName, { id: materialId, status: auditConst.status.checking, }); // 本期一些必要数据(如应耗数量和上期调差金额)插入到material_bills_history表里 const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id } }); if (materialBillsData.length === 0) { throw '调差工料不能为空'; } const mbhList = []; for (const mb of materialBillsData) { if (mb.code === '') { throw '调差工料编号不能为空'; } const newMbh = { tid: this.ctx.tender.id, mid: this.ctx.material.id, order: this.ctx.material.order, mb_id: mb.id, quantity: mb.quantity, expr: mb.expr, msg_tp: mb.msg_tp, msg_times: mb.msg_times, msg_spread: mb.msg_spread, m_up_risk: mb.m_up_risk, m_down_risk: mb.m_down_risk, m_spread: mb.m_spread, m_tp: mb.m_tp, pre_tp: mb.pre_tp, }; mbhList.push(newMbh); } await transaction.insert(this.ctx.service.materialBillsHistory.tableName, mbhList); // 添加短信通知-需要审批提醒功能 // const smsUser = await this.ctx.service.projectAccount.getDataById(audit.aid); // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') { // const smsType = JSON.parse(smsUser.sms_type); // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) { // const tenderInfo = await this.ctx.service.tender.getDataById(audit.tid); // const stageInfo = await this.ctx.service.stage.getDataById(audit.sid); // const sms = new SMS(this.ctx); // const tenderName = await sms.contentChange(tenderInfo.name); // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,需要您审批。'; // sms.send(smsUser.auth_mobile, content); // } // } // todo 更新标段tender状态 ? await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } return true; } async _checked(pid, materialId, checkData, times) { const time = new Date(); // 整理当前流程审核人状态更新 const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking }); if (!audit) { throw '审核数据错误'; } const nextAudit = await this.getDataByCondition({ mid: materialId, times, order: audit.order + 1 }); const transaction = await this.db.beginTransaction(); try { await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time }); // 无下一审核人表示,审核结束 if (nextAudit) { // 复制一份下一审核人数据 // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, nextAudit.order, transaction); // 流程至下一审批人 await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time }); // 添加记录到推送表 // 同步 期信息 await transaction.update(this.ctx.service.material.tableName, { id: materialId, status: auditConst.status.checking, }); // 添加短信通知-需要审批提醒功能 // const smsUser = await this.ctx.service.projectAccount.getDataById(nextAudit.aid); // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') { // const smsType = JSON.parse(smsUser.sms_type); // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) { // const tenderInfo = await this.ctx.service.tender.getDataById(nextAudit.tid); // const stageInfo = await this.ctx.service.stage.getDataById(nextAudit.sid); // const sms = new SMS(this.ctx); // const tenderName = await sms.contentChange(tenderInfo.name); // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,需要您审批。'; // sms.send(smsUser.auth_mobile, content); // } // } } else { // 本期结束 // 生成截止本期数据 final数据 // console.time('generatePre'); // await this.ctx.service.stageBillsFinal.generateFinalData(transaction, this.ctx.tender, this.ctx.stage); // await this.ctx.service.stagePosFinal.generateFinalData(transaction, this.ctx.tender, this.ctx.stage); // console.timeEnd('generatePre'); // 同步 期信息 await transaction.update(this.ctx.service.material.tableName, { id: materialId, status: checkData.checkType, }); const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId) // 审核结束,将原报添加到推送表 await transaction.insert('zh_notice', { pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checked, is_read: 0, content: noticeContent }); // 添加短信通知-审批通过提醒功能 // const mobile_array = []; // const stageInfo = await this.ctx.service.stage.getDataById(stageId); // const auditList = await this.getAuditors(stageId, stageInfo.times); // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id); // if (smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') { // const smsType = JSON.parse(smsUser1.sms_type); // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) { // mobile_array.push(smsUser1.auth_mobile); // } // } // for (const user of auditList) { // const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid); // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') { // const smsType = JSON.parse(smsUser.sms_type); // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) { // mobile_array.push(smsUser.auth_mobile); // } // } // } // if (mobile_array.length > 0) { // const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid); // const sms = new SMS(this.ctx); // const tenderName = await sms.contentChange(tenderInfo.name); // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,审批通过。'; // sms.send(mobile_array, content); // } } await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } async _checkNo(pid, materialId, checkData, times) { const time = new Date(); // 整理当前流程审核人状态更新 const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking }); if (!audit) { throw '审核数据错误'; } const sql = 'SELECT `tid`, `mid`, `aid`, `order` FROM ?? WHERE `mid` = ? and `times` = ? GROUP BY `aid` ORDER BY `id` ASC'; const sqlParam = [this.tableName, materialId, times]; const auditors = await this.db.query(sql, sqlParam); let order = 1; for (const a of auditors) { a.times = times + 1; a.order = order; a.status = auditConst.status.uncheck; order++; } const transaction = await this.db.beginTransaction(); try { await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time }); // 添加到消息推送表 const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId) await transaction.insert('zh_notice', { pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNo, is_read: 0, content: noticeContent }); // 同步期信息 await transaction.update(this.ctx.service.material.tableName, { id: materialId, status: checkData.checkType, times: times + 1, }); // 拷贝新一次审核流程列表 await transaction.insert(this.tableName, auditors); // 删除material_bills_history信息 await transaction.delete(this.ctx.service.materialBillsHistory.tableName, { tid: this.ctx.tender.id, order: this.ctx.material.order, }); // 计算该审批人最终数据 // await this.ctx.service.stagePay.calcAllStagePays(this.ctx.stage, transaction); // 复制一份最新数据给原报 // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times + 1, 0, transaction); // 添加短信通知-审批退回提醒功能 // const mobile_array = []; // const stageInfo = await this.ctx.service.stage.getDataById(stageId); // const auditList = await this.getAuditors(stageId, stageInfo.times); // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id); // if (smsUser1.auth_mobile !== '' && smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') { // const smsType = JSON.parse(smsUser1.sms_type); // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) { // mobile_array.push(smsUser1.auth_mobile); // } // } // for (const user of auditList) { // const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid); // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') { // const smsType = JSON.parse(smsUser.sms_type); // if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) { // mobile_array.push(smsUser.auth_mobile); // } // } // } // if (mobile_array.length > 0) { // const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid); // const sms = new SMS(this.ctx); // const tenderName = await sms.contentChange(tenderInfo.name); // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,审批退回。'; // sms.send(mobile_array, content); // } await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } async _checkNoPre(materialId, checkData, times) { const time = new Date(); // 整理当前流程审核人状态更新 const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking }); if (!audit || audit.order <= 1) { throw '审核数据错误'; } // 添加重新审批后,不能用order-1,取groupby值里的上一个才对 const auditors2 = await this.getAuditGroupByList(materialId, times); const auditorIndex = await auditors2.findIndex(function(item) { return item.aid === audit.aid; }); const preAuditor = auditors2[auditorIndex - 1]; const transaction = await this.db.beginTransaction(); try { await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time }); // 顺移气候审核人流程顺序 this.initSqlBuilder(); this.sqlBuilder.setAndWhere('mid', { value: materialId, operate: '=' }); this.sqlBuilder.setAndWhere('order', { value: audit.order, operate: '>' }); this.sqlBuilder.setUpdateData('order', { value: 2, selfOperate: '+' }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await transaction.query(sql, sqlParam); const newAuditors = []; newAuditors.push({ tid: audit.tid, mid: audit.mid, aid: preAuditor.aid, times: audit.times, order: audit.order + 1, status: auditConst.status.checking, begin_time: time, }); newAuditors.push({ tid: audit.tid, mid: audit.mid, aid: audit.aid, times: audit.times, order: audit.order + 2, status: auditConst.status.uncheck, }); await transaction.insert(this.tableName, newAuditors); await transaction.commit(); } catch (error) { await transaction.rollback(); throw error; } } /** * 审批 * @param {Number} materialId - 材料调差期id * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果 * @param {Number} times - 第几次审批 * @return {Promise} */ async check(materialId, checkData, times = 1) { if (checkData.checkType !== auditConst.status.checked && checkData.checkType !== auditConst.status.checkNo && checkData.checkType !== auditConst.status.checkNoPre) { throw '提交数据错误'; } const pid = this.ctx.session.sessionProject.id; switch (checkData.checkType) { case auditConst.status.checked: await this._checked(pid, materialId, checkData, times); break; case auditConst.status.checkNo: await this._checkNo(pid, materialId, checkData, times); break; case auditConst.status.checkNoPre: await this._checkNoPre(materialId, checkData, times); break; default: throw '无效审批操作'; } } /** * 用于添加推送所需的content内容 * @param {Number} pid 项目id * @param {Number} tid 台账id * @param {Number} mid 期id */ async getNoticeContent(pid, tid, mid) { const noticeSql = 'SELECT * FROM (SELECT ' + ' t.`id` As `tid`, ma.`mid`, t.`name`, m.`order`, pa.`name` As `su_name`, pa.role As `su_role`' + ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' + ' LEFT JOIN ?? As m On t.`id` = m.`tid` AND m.`id` = ?' + ' LEFT JOIN ?? As ma ON m.`id` = ma.`mid`' + ' LEFT JOIN ?? As pa ON t.`user_id` = pa.`id`' + ' WHERE t.`project_id` = ? ) as new_t GROUP BY new_t.`tid`'; const noticeSqlParam = [this.ctx.service.tender.tableName, tid, this.ctx.service.material.tableName, mid, this.tableName, this.ctx.service.projectAccount.tableName, pid]; const content = await this.db.query(noticeSql, noticeSqlParam); return content.length ? JSON.stringify(content[0]) : ''; } /** * 审批 * @param {Number} materialId - 材料调差期id * @param {Number} times - 第几次审批 * @return {Promise} */ async checkAgain(materialId, times = 1) { const time = new Date(); // 整理当前流程审核人状态更新 const audit = (await this.getAllDataByCondition({ where: { mid: materialId, times }, orders: [['order', 'desc']], limit: 1, offset: 0 }))[0]; if (!audit || audit.order <= 1) { throw '审核数据错误'; } const transaction = await this.db.beginTransaction(); try { // 当前审批人2次添加至流程中 const newAuditors = []; newAuditors.push({ tid: audit.tid, mid: audit.mid, aid: audit.aid, times: audit.times, order: audit.order + 1, status: auditConst.status.checkAgain, begin_time: time, end_time: time, opinion: '', }); newAuditors.push({ tid: audit.tid, mid: audit.mid, aid: audit.aid, times: audit.times, order: audit.order + 2, status: auditConst.status.checking, begin_time: time, }); await transaction.insert(this.tableName, newAuditors); // 复制一份最新数据给下一人 // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, audit.order + 2, transaction); // 本期结束 // 生成截止本期数据 final数据 // await this.ctx.service.stageBillsFinal.delGenerateFinalData(transaction, this.ctx.tender, this.ctx.stage); // await this.ctx.service.stagePosFinal.delGenerateFinalData(transaction, this.ctx.tender, this.ctx.stage); // 同步 期信息 await transaction.update(this.ctx.service.material.tableName, { id: materialId, status: auditConst.status.checking, }); // // 添加短信通知-需要审批提醒功能 // const smsUser = await this.ctx.service.projectAccount.getDataById(audit.aid); // if (smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') { // const smsType = JSON.parse(smsUser.sms_type); // if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) { // const tenderInfo = await this.ctx.service.tender.getDataById(audit.tid); // const stageInfo = await this.ctx.service.stage.getDataById(audit.sid); // const sms = new SMS(this.ctx); // const tenderName = await sms.contentChange(tenderInfo.name); // const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期,需要您审批。'; // sms.send(smsUser.auth_mobile, content); // } // } await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } /** * 获取审核人需要审核的期列表 * * @param auditorId * @return {Promise<*>} */ async getAuditMaterial(auditorId) { const sql = 'SELECT ma.`aid`, ma.`times`, ma.`order`, ma.`begin_time`, ma.`end_time`, ma.`tid`, ma.`mid`,' + ' m.`order` As `morder`, m.`status` As `mstatus`,' + ' t.`name`, t.`project_id`, t.`type`, t.`user_id` ' + ' FROM ?? AS ma, ?? AS m, ?? As t ' + ' WHERE ((ma.`aid` = ? and ma.`status` = ?) OR (m.`user_id` = ? and ma.`status` = ? and m.`status` = ? and ma.`times` = (m.`times`-1)))' + ' and ma.`mid` = m.`id` and ma.`tid` = t.`id`'; 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]; return await this.db.query(sql, sqlParam); } /** * 获取 某时间后 审批进度 更新的期 * @param {Number} pid - 查询标段 * @param {Number} uid - 查询人 * @param {Date} time - 查询时间 * @return {Promise<*>} */ async getNoticeMaterial(pid, uid, time) { // const sql = 'SELECT * FROM (SELECT t.`name`, t.`project_id`, t.`type`, t.`user_id`, ' + // ' m.`order` As `m_order`, m.`status` As `m_status`, ' + // ' ma.`aid`, ma.`times`, ma.`order`, ma.`end_time`, ma.`tid`, ma.`mid`, ma.`status`, ' + // ' pa.`name` As `su_name`, pa.role As `su_role`, pa.company As `su_company`' + // ' FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tid` FROM ?? WHERE `aid` = ? GROUP BY `tid`)) As t' + // ' LEFT JOIN ?? As m On t.`id` = m.`tid`' + // ' LEFT JOIN ?? As ma ON m.`id` = ma.`mid`' + // ' LEFT JOIN ?? As pa ON ma.`aid` = pa.`id`' + // ' WHERE ma.`end_time` > ? and t.`project_id` = ?' + // ' ORDER By ma.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`tid`' + // ' ORDER BY new_t.`end_time`'; // const sqlParam = [this.ctx.service.tender.tableName, uid, this.tableName, uid, this.ctx.service.material.tableName, this.tableName, // this.ctx.service.projectAccount.tableName, time, pid]; // return await this.db.query(sql, sqlParam); let notice = await this.db.select('zh_notice', { where: { pid, type: pushType.material, uid, is_read: 0 }, }); notice = notice.map(v => { const extra = JSON.parse(v.content) delete v.content return { ...v, ...extra } }) return notice; } /** * 获取审核人流程列表 * * @param auditorId * @return {Promise<*>} */ async getAuditGroupByList(materialId, times) { const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' + 'FROM ?? AS la, ?? AS pa ' + 'WHERE la.`mid` = ? and la.`times` = ? and la.`aid` = pa.`id` GROUP BY la.`aid` ORDER BY la.`order`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, times]; return await this.db.query(sql, sqlParam); } /** * 复制上一期的审批人列表给最新一期 * * @param transaction - 新增一期的事务 * @param {Object} preMaterial - 上一期 * @param {Object} newaMaterial - 最新一期 * @return {Promise<*>} */ async copyPreMaterialAuditors(transaction, preMaterial, newMaterial) { const auditors = await this.getAuditGroupByList(preMaterial.id, preMaterial.times); const newAuditors = []; for (const a of auditors) { const na = { tid: preMaterial.tid, mid: newMaterial.id, aid: a.aid, times: newMaterial.times, order: newAuditors.length + 1, status: auditConst.status.uncheck, }; newAuditors.push(na); } const result = await transaction.insert(this.tableName, newAuditors); return result.affectedRows === auditors.length; } /** * 移除审核人 * * @param {Number} materialId - 材料调差期id * @param {Number} status - 期状态 * @param {Number} status - 期次数 * @return {Promise} */ async getAuditorByStatus(materialId, status, times = 1) { let auditor = null; let sql = ''; let sqlParam = ''; switch (status) { case auditConst.status.checking : case auditConst.status.checked : case auditConst.status.checkNoPre : sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' + 'FROM ?? AS la, ?? AS pa ' + 'WHERE la.`mid` = ? and la.`status` = ? and la.`aid` = pa.`id` order by la.`times` desc, la.`order` desc'; sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, status]; auditor = await this.db.queryOne(sql, sqlParam); break; case auditConst.status.checkNo : sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`aid`, la.`order` ' + 'FROM ?? AS la, ?? AS pa ' + 'WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ? and la.`aid` = pa.`id` order by la.`times` desc, la.`order` desc'; sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checkNo, parseInt(times) - 1]; auditor = await this.db.queryOne(sql, sqlParam); break; case auditConst.status.uncheck : default:break; } return auditor; } async getAllAuditors(tenderId) { const sql = 'SELECT ma.aid, ma.tid FROM ' + this.tableName + ' ma' + ' LEFT JOIN ' + this.ctx.service.tender.tableName + ' t On ma.tid = t.id' + ' WHERE t.id = ?' + ' GROUP BY ma.aid'; const sqlParam = [tenderId]; return this.db.query(sql, sqlParam); } } return MaterialAudit; };