'use strict'; /** * * * @author Mai * @date * @version */ const auditConst = require('../const/audit').revise; const auditType = require('../const/audit').auditType; const smsTypeConst = require('../const/sms_type'); const SmsAliConst = require('../const/sms_alitemplate'); const wxConst = require('../const/wechat_template'); const shenpiConst = require('../const/shenpi'); const pushType = require('../const/audit').pushType; const RevisePrice = require('../lib/revise_price'); const pushOperate = require('../const/spec_3f').pushOperate; module.exports = app => { class ReviseAudit extends app.BaseService { /** * 构造函数 * * @param {Object} ctx - egg全局变量 * @return {void} */ constructor(ctx) { super(ctx); this.tableName = 'revise_audit'; } /** * 获取标段审核人信息 * * @param {Number} reviseId - 修订id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getAuditor(reviseId, auditorId, times = 1) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`audit_id` = ? and la.`times` = ?'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, reviseId, auditorId, times]; return await this.db.queryOne(sql, sqlParam); } async getAuditorByOrder(reviseId, order, times = 1) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`audit_type`, la.`audit_ledger_id`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' + ' FROM ?? AS la Left Join ?? AS pa ON la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`audit_order` = ? and la.`times` = ?'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, reviseId, order, times]; return await this.db.queryOne(sql, sqlParam); } async getAuditorsByOrder(reviseId, order, times) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`,' + ' la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, la.audit_type, la.audit_ledger_id ' + ' FROM ' + this.tableName + ' AS la' + ' Left Join ' + this.ctx.service.projectAccount.tableName + ' AS pa ON la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`audit_order` = ? and la.`times` = ?' + ' ORDER BY `audit_order` DESC'; const sqlParam = [reviseId, order, times ? times: 1]; return await this.db.query(sql, sqlParam); } async getLatestAuditor(reviseId, times, status) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`,' + ' la.`times`, la.`audit_order`, la.`audit_type`, la.`audit_ledger_id`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' + ' FROM ' + this.tableName + ' AS la' + ' Left Join ' + this.ctx.service.projectAccount.tableName + ' AS pa ON la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`status` = ? and la.`times` = ?' + ' ORDER BY `audit_order` DESC'; const sqlParam = [reviseId, status, times ? times : 1]; return await this.db.queryOne(sql, sqlParam); } async getLatestAuditors(reviseId, times, status) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.audit_type, la.audit_order, la.audit_ledger_id,' + ' la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' + ' FROM ' + this.tableName + ' AS la' + ' Left Join ' + this.ctx.service.projectAccount.tableName + ' AS pa ON la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`status` = ? and la.`times` = ?' + ' ORDER BY `audit_order` DESC'; const sqlParam = [reviseId, status, times ? times: 1]; const result = await this.db.query(sql, sqlParam); if (result.length === 0) return []; return result.filter(x => { return x.audit_order === result[0].audit_order }); } /** * 获取标段审核列表信息 * * @param {Number} reviseId - 修订id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getAuditors(reviseId, times = 1, order_sort = 'asc') { const sql = 'SELECT la.id, la.audit_id, la.times, la.audit_order, la.audit_type, la.audit_ledger_id, la.status, la.opinion, la.begin_time, la.end_time, ' + ' pa.name, pa.company, pa.role, pa.mobile, pa.telephone, pa.sign_path' + ` FROM ${this.tableName} la LEFT JOIN ${this.ctx.service.projectAccount.tableName} pa ON la.audit_id = pa.id` + ' WHERE la.rid = ? AND la.times = ?' + ' ORDER BY la.audit_order ' + order_sort; const sqlParam = [reviseId, times]; const result = await this.db.query(sql, sqlParam); const max_sort = this._.max(result.map(x => { return x.audit_order; })); for (const i in result) { result[i].max_sort = max_sort; } return result; } /** * 获取 审核列表信息(修订列表页审批流程用) * * @param {Number} rid - 修订id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getAuditors2ReviseList(rid, times = 1) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, g.`sort` ' + 'FROM ?? AS la, ?? AS pa, (SELECT t1.`audit_id`,(@i:=@i+1) as `sort` FROM (SELECT t.`audit_id`, t.`audit_order` FROM (select `audit_id`, `audit_order` from ?? WHERE `rid` = ? AND `times` = ? ORDER BY `audit_order` LIMIT 200) t GROUP BY t.`audit_id` ORDER BY t.`audit_order`) t1, (select @i:=0) as it) as g ' + 'WHERE la.`rid` = ? and la.`times` = ? and la.`audit_id` = pa.`id` and g.`audit_id` = la.`audit_id` order by la.`audit_order`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, rid, times, rid, times]; const result = await this.db.query(sql, sqlParam); const sql2 = 'SELECT COUNT(a.`audit_id`) as num FROM (SELECT `audit_id` FROM ?? WHERE `rid` = ? AND `times` = ? GROUP BY `audit_id`) as a'; const sqlParam2 = [this.tableName, rid, times]; const count = await this.db.queryOne(sql2, sqlParam2); for (const i in result) { result[i].max_sort = count.num; } return result; } /** * 获取标段当前审核人 * * @param {Number} reviseId - 修订id * @param {Number} times - 第几次审批 * @return {Promise<*>} */ async getCurAuditor(reviseId, times = 1) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`status` = ? and la.`times` = ?'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, reviseId, auditConst.status.checking, times]; return await this.db.queryOne(sql, sqlParam); } async getCurAuditors(reviseId, times = 1) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`audit_order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, la.audit_type, la.audit_order, la.audit_ledger_id ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`status` = ? and la.`times` = ?'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, reviseId, auditConst.status.checking, times]; return await this.db.query(sql, sqlParam); } async getAuditorGroup(reviseId, times) { const auditors = await this.getAuditors(reviseId, times); // 全部参与的审批人 return this.ctx.helper.groupAuditors(auditors, 'audit_order'); } async getUserGroup(reviseId, times) { const group = await this.getAuditorGroup(reviseId, times); const sql = 'SELECT pa.`id` As audit_id, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As rid, 0 As audit_order, 1 As audit_type' + ' FROM ' + this.ctx.service.ledgerRevise.tableName + ' As r' + ' LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa' + ' ON r.uid = pa.id' + ' WHERE r.id = ?'; const sqlParam = [times, reviseId, reviseId]; const user = await this.db.queryOne(sql, sqlParam); group.unshift([ user ]); return group; } async getUniqUserGroup(reviseId, times) { const group = await this.getAuditorGroup(reviseId, times); const sql = 'SELECT pa.`id` As audit_id, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As rid, 0 As audit_order, 1 As audit_type' + ' FROM ' + this.ctx.service.ledgerRevise.tableName + ' As r' + ' LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa' + ' ON r.uid = pa.id' + ' WHERE r.id = ?'; const sqlParam = [times, reviseId, reviseId]; const user = await this.db.queryOne(sql, sqlParam); group.unshift([ user ]); return this.ctx.helper.groupAuditorsUniq(group, 'audit_order'); } async getAuditorHistory(reviseId, times, reverse = false) { const history = []; if (times >= 1) { for (let i = 1; i <= times; i++) { const auditors = await this.getAuditors(reviseId, i); const group = this.ctx.helper.groupAuditors(auditors, 'audit_order'); const historyGroup = []; const max_order = group.length > 0 && group[group.length - 1].length > 0 ? group[group.length - 1][0].audit_order : -1; for (const g of group) { const his = { beginYear: '', beginDate: '', beginTime: '', endYear: '', endDate: '', endTime: '', begin_time: null, end_time: null, audit_type: g[0].audit_type, audit_order: g[0].audit_order, auditors: g }; if (his.audit_type === auditType.key.common) { his.name = g[0].name; } else { his.name = this.ctx.helper.transFormToChinese(his.audit_order) + '审'; } his.is_final = his.audit_order === max_order; if (g[0].begin_time) { his.begin_time = g[0].begin_time; const beginTime = this.ctx.moment(g[0].begin_time); his.beginYear = beginTime.format('YYYY'); his.beginDate = beginTime.format('MM-DD'); his.beginTime = beginTime.format('HH:mm:ss'); } let end_time; g.forEach(x => { if (x.status === auditConst.status.checkSkip) return; if (!his.status || x.status === auditConst.status.checking) his.status = x.status; if (x.end_time && (!end_time || x.end_time > end_time)) { end_time = x.end_time; if (his.status !== auditConst.status.checking) his.status = x.status; } }); if (end_time) { his.end_time = end_time; const endTime = this.ctx.moment(end_time); his.endYear = endTime.format('YYYY'); his.endDate = endTime.format('MM-DD'); his.endTime = endTime.format('HH:mm:ss'); } historyGroup.push(his); } if (reverse) { history.push(historyGroup.reverse()); } else { history.push(historyGroup); } } } return history; } async getUniqAuditor(reviseId, times) { const auditors = await this.getAuditors(reviseId, times); // 全部参与的审批人 const result = []; auditors.forEach(x => { if (result.findIndex(r => { return x.audit_id === r.audit_id && x.audit_order === r.audit_order; }) < 0) { result.push(x); } }); return result; } async loadReviseUser(revise) { const status = auditConst.status; const accountId = this.ctx.session.sessionUser.accountId; revise.user = await this.ctx.service.projectAccount.getAccountInfoById(revise.uid); revise.auditors = await this.getAuditors(revise.id, revise.times); // 全部参与的审批人 revise.auditorIds = this._.map(revise.auditors, 'audit_id'); revise.curAuditors = revise.auditors.filter(x => { return x.status === status.checking; }); // 当前流程中审批中的审批人 revise.curAuditorIds = this._.map(revise.curAuditors, 'audit_id'); revise.flowAuditors = revise.curAuditors.length > 0 ? revise.auditors.filter(x => { return x.audit_order === revise.curAuditors[0].audit_order; }) : []; // 当前流程中参与的审批人（包含会签时，审批通过的人） revise.flowAuditorIds = this._.map(revise.flowAuditors, 'audit_id'); revise.nextAuditors = revise.curAuditors.length > 0 ? revise.auditors.filter(x => { return x.audit_order === revise.curAuditors[0].audit_order + 1; }) : []; revise.nextAuditorIds = this._.map(revise.nextAuditors, 'audit_id'); revise.auditorGroups = this.ctx.helper.groupAuditors(revise.auditors, 'audit_order'); revise.userGroups = this.ctx.helper.groupAuditorsUniq(revise.auditorGroups); revise.userGroups.unshift([{ audit_id: revise.user.id, order: 0, times: revise.times, audit_order: 0, audit_type: auditType.key.common, name: revise.user.name, role: revise.user.role, company: revise.user.company }]); revise.finalAuditorIds = revise.userGroups[revise.userGroups.length - 1].map(x => { return x.audit_id; }); revise.relaAuditor = revise.auditors.find(x => { return x.audit_id === accountId }); revise.assists = [];// await this.service.ledgerAuditAss.getData(tender); // 全部协同人 revise.assists = revise.assists.filter(x => { return x.user_id === revise.uid || revise.auditorIds.indexOf(x.uid) >= 0; }); // 过滤无效协同人 revise.userAssists = revise.assists.filter(x => { return x.user_id === revise.uid; }); // 原报协同人 revise.userAssistIds = this._.map(revise.userAssists, 'ass_user_id'); revise.auditAssists = revise.assists.filter(x => { return x.user_id !== revise.uid; }); // 审批协同人 revise.auditAssistIds = this._.map(revise.auditAssists, 'ass_user_id'); revise.relaAssists = revise.assists.filter(x => { return x.user_id === accountId }); // 登录人的协同人 revise.userIds = revise.status === status.uncheck // 当前流程下全部参与人id ? [revise.user_id, ...revise.userAssistIds] : [revise.user_id, ...revise.userAssistIds, ...revise.auditorIds, ...revise.auditAssistIds]; } async loadReviseAuditViewData(revise) { const times = revise.status === auditConst.status.checkNo ? revise.times - 1 : revise.times; if (!revise.user) revise.user = await this.ctx.service.projectAccount.getAccountInfoById(revise.uid); revise.auditHistory = await this.getAuditorHistory(revise.id, times); // 获取审批流程中左边列表 if (revise.status === auditConst.status.checkNo && revise.user_id !== this.ctx.session.sessionUser.accountId) { const auditors = await this.getAuditors(revise.id, times); // 全部参与的审批人 const auditorGroups = this.ctx.helper.groupAuditors(auditors, 'audit_order'); revise.auditors2 = this.ctx.helper.groupAuditorsUniq(auditorGroups, 'audit_order'); revise.auditors2.unshift([{ audit_id: revise.user.id, order: 0, times: revise.times - 1, audit_order: 0, audit_type: auditType.key.common, name: revise.user.name, role: revise.user.role, company: revise.user.company }]); } else { revise.auditors2 = revise.userGroups; } if (revise.status === auditConst.status.uncheck || revise.status === auditConst.status.checkNo) { revise.auditorList = await this.getAuditors(revise.id, revise.times); } } /** * 获取最新审核顺序 * * @param {Number} reviseId - 修订id * @param {Number} times - 第几次审批 * @return {Promise} */ async getNewOrder(reviseId, times = 1) { const sql = 'SELECT Max(??) As max_order FROM ?? Where `rid` = ? and `times` = ?'; const sqlParam = ['audit_order', this.tableName, reviseId, times]; const result = await this.db.queryOne(sql, sqlParam); return result && result.max_order ? result.max_order + 1 : 1; } /** * 新增审核人 * * @param {Object} revise - 修订 * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise} */ async addAuditor(revise, auditorId, is_gdzs = 0) { const transaction = await this.db.beginTransaction(); try { const times = revise.times ? revise.times : 1; let newOrder = await this.getNewOrder(revise.id, times); // 判断是否存在固定终审，存在则newOrder - 1并使终审order+1 newOrder = is_gdzs === 1 ? newOrder - 1 : newOrder; if (is_gdzs) await this._syncOrderByDelete(transaction, revise.id, newOrder, times, '+'); const data = { tender_id: revise.tid, audit_id: auditorId, times, audit_order: newOrder, status: auditConst.status.uncheck, rid: revise.id, in_time: new Date(), }; const result = await transaction.insert(this.tableName, data); await transaction.commit(); return (result.effectRows = 1); } catch (err) { await transaction.rollback(); throw err; } return false; } /** * 移除审核人时，同步其后审核人order * @param transaction - 事务 * @param {Number} reviseId - 修订id * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise<*>} * @private */ async _syncOrderByDelete(transaction, reviseId, order, times, selfOperate = '-') { this.initSqlBuilder(); this.sqlBuilder.setAndWhere('rid', { value: this.db.escape(reviseId), operate: '=', }); this.sqlBuilder.setAndWhere('audit_order', { value: order, operate: '>=', }); this.sqlBuilder.setAndWhere('times', { value: times, operate: '=', }); this.sqlBuilder.setUpdateData('audit_order', { value: 1, selfOperate: selfOperate, }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await transaction.query(sql, sqlParam); return data; } /** * 移除审核人 * * @param {Object} revise - 修订 * @param {Number} auditorId - 审核人id * @param {Number} times - 第几次审批 * @return {Promise} */ async deleteAuditor(revise, auditorId) { const times = revise.times ? revise.times : 1; const transaction = await this.db.beginTransaction(); try { const condition = { rid: revise.id, audit_id: auditorId, times }; const auditor = await this.getDataByCondition(condition); if (!auditor) { throw '该审核人不存在'; } await transaction.delete(this.tableName, { rid: revise.id, audit_order: auditor.audit_order, times }); await this._syncOrderByDelete(transaction, revise.id, auditor.audit_order, times); await transaction.delete(this.tableName, condition); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } return true; } /** * 开始审批 * * @param {Object} revise - 修订 * @param {Number} times - 第几次审批 * @return {Promise} */ async start(revise, times = 1) { const audits = await this.getAllDataByCondition({ where: { rid: revise.id, times, audit_order: 1 } }); if (audits.length === 0) { if (this.ctx.tender.info.shenpi.revise === shenpiConst.sp_status.gdspl) { throw '请联系管理员添加审批人'; } else { throw '请先选择审批人，再上报数据'; } } const time = new Date(); // 拷贝备份台账数据 const [his_id, sum] = await this.ctx.service.ledgerHistory.backupReviseLedgerHistory(revise); const transaction = await this.db.beginTransaction(); try { const updateAuditData = audits.map(a => { return { id: a.id, status: auditConst.status.checking, begin_time: time, bills_file: revise.bills_file, pos_file: revise.pos_file }; }); await transaction.updateRows(this.tableName, updateAuditData); const reviseData = { id: revise.id, status: auditConst.status.checking, his_id, sum: JSON.stringify(sum), }; if (revise.times === 1) reviseData.begin_time = time; await transaction.update(this.ctx.service.ledgerRevise.tableName, reviseData); // 投资进度改变状态 await transaction.update(this.ctx.service.schedule.tableName, { revising: 1 }, { where: { tid: this.ctx.tender.id } }); // 添加短信通知-需要审批提醒功能 // 下一人 // await this.ctx.helper.sendUserSms(audit.audit_id, smsTypeConst.const.XD, // smsTypeConst.judge.approval.toString(), '台账修订需要您审批，请登录系统处理。'); const auditorIds = audits.map(x => { return x.audit_id; }); await this.ctx.helper.sendAliSms(auditorIds, smsTypeConst.const.XD, smsTypeConst.judge.approval.toString(), SmsAliConst.template.revise_check); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + revise.tid + '/revise/' + revise.id + '/info' ); const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, begin_time: Date.parse(time), }; await this.ctx.helper.sendWechat(auditorIds, smsTypeConst.const.XD, smsTypeConst.judge.approval.toString(), wxConst.template.revise, wechatData); for (const audit of audits) { await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.XD, { pid: this.ctx.session.sessionProject.id, tid: this.ctx.tender.id, uid: audit.audit_id, sp_type: 'revise', sp_id: audit.id, table_name: this.tableName, template: wxConst.template.revise, wx_data: wechatData, }); } // 其他参与人 const auditList = await this.getAuditors(revise.id, times); const users = this._.map(auditList.filter(x => { return auditorIds.indexOf(x.audit_id) < 0; }, 'audit_id')); // await this.ctx.helper.sendUserSms(users, smsTypeConst.const.XD, // smsTypeConst.judge.result.toString(), '台账修订已上报。'); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_report); // 微信模板通知 const wechatData2 = { wap_url: shenpiUrl, status: wxConst.status.report, tips: wxConst.tips.report, begin_time: Date.parse(time), code: this.ctx.session.sessionProject.code, }; await this.ctx.helper.sendWechat(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), wxConst.template.revise, wechatData2); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } return true; } async _replaceLedgerByRevise(transaction, revise) { const sqlParam = [revise.tid]; await transaction.delete(this.ctx.service.ledger.tableName, { tender_id: revise.tid }); const bSql = 'Insert Into ' + this.ctx.service.ledger.tableName + ' (id, code, b_code, name, unit, source, remark, ledger_id, ledger_pid, level, `order`, full_path, is_leaf,' + ' quantity, total_price, unit_price, drawing_code, memo, features, dgn_qty1, dgn_qty2, deal_qty, deal_tp,' + ' sgfh_qty, sgfh_tp, sjcl_qty, sjcl_tp, qtcl_qty, qtcl_tp, node_type, crid, ccid, tender_id,' + ' sgfh_expr, sjcl_expr, qtcl_expr, check_calc,' + ' ex_memo1, ex_memo2, ex_memo3)' + ' Select id, code, b_code, name, unit, source, remark, ledger_id, ledger_pid, level, `order`, full_path, is_leaf,' + ' quantity, total_price, unit_price, drawing_code, memo, features, dgn_qty1, dgn_qty2, deal_qty, deal_tp,' + ' sgfh_qty, sgfh_tp, sjcl_qty, sjcl_tp, qtcl_qty, qtcl_tp, node_type, crid, ccid, tender_id, ' + ' sgfh_expr, sjcl_expr, qtcl_expr, check_calc,' + ' ex_memo1, ex_memo2, ex_memo3' + ' From ' + this.ctx.service.reviseBills.tableName + ' Where `tender_id` = ?'; await transaction.query(bSql, sqlParam); await transaction.delete(this.ctx.service.pos.tableName, { tid: revise.tid }); const pSql = 'Insert Into ' + this.ctx.service.pos.tableName + ' (id, tid, lid, name, drawing_code, quantity, add_stage, add_stage_order, add_times, add_user,' + ' sgfh_qty, sjcl_qty, qtcl_qty, crid, ccid, porder, position, ' + ' sgfh_expr, sjcl_expr, qtcl_expr, real_qty,' + ' ex_memo1, ex_memo2, ex_memo3)' + ' Select id, tid, lid, name, drawing_code, quantity, add_stage, add_stage_order, add_times, add_user,' + ' sgfh_qty, sjcl_qty, qtcl_qty, crid, ccid, porder, position,' + ' sgfh_expr, sjcl_expr, qtcl_expr, real_qty,' + ' ex_memo1, ex_memo2, ex_memo3' + ' From ' + this.ctx.service.revisePos.tableName + ' Where `tid` = ?'; await transaction.query(pSql, sqlParam); } async _checked(revise, opinion, times) { const accountId = this.ctx.session.sessionUser.accountId; const pid = this.ctx.session.sessionProject.id; const auditors = await this.getAllDataByCondition({ where: { rid: revise.id, times, status: auditConst.status.checking } }); const selfAuditor = auditors.find(x => { return x.audit_id === accountId; }); if (!selfAuditor) throw '审核数据错误'; const flowAuditors = await this.getAllDataByCondition({ where: { rid: revise.id, times, audit_order: selfAuditor.audit_order } }); const nextAuditors = await this.getAllDataByCondition({ where: { rid: revise.id, times, audit_order: selfAuditor.audit_order + 1 } }); const beginAuditors = await this.getAllDataByCondition({ where: { rid: revise.id, audit_order: 1, times } }); const time = new Date(); const noticeContent = await this.getNoticeContent(pid, selfAuditor.tender_id, revise.id, selfAuditor.audit_id, opinion); const defaultNoticeRecord = { pid, type: pushType.revise, status: auditConst.status.checked, content: noticeContent}; const transaction = await this.db.beginTransaction(); try { const auditList = await this.getAuditors(revise.id, times); // 审核通过添加到推送表 const records = [ { uid: revise.uid, ...defaultNoticeRecord } ]; auditList.forEach(audit => { records.push({ uid: audit.audit_id, ...defaultNoticeRecord }); }); await transaction.insert('zh_notice', records); // 整理当前流程审核人状态更新 // 更新当前审核流程 await transaction.update(this.tableName, { id: selfAuditor.id, status: auditConst.status.checked, opinion, end_time: time }); await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, selfAuditor.id); if (auditors.length === 1 || selfAuditor.audit_type === auditType.key.or) { // 或签更新他人审批状态 if (selfAuditor.audit_type === auditType.key.or) { const updateOther = []; for (const audit of auditors) { if (audit.audit_id === selfAuditor.audit_id) continue; updateOther.push({ id: audit.id, status: auditConst.status.checkSkip, opinion: '', end_time: time }); } if (updateOther.length > 0) { await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, updateOther.map(x => { return x.id})); await transaction.updateRows(this.tableName, updateOther); } } // 无下一审核人表示，审核结束 if (nextAuditors.length > 0) { const nextAuditUpdateData = nextAuditors.map(x => { return { id: x.id, status: auditConst.status.checking, begin_time: time }}); await transaction.updateRows(this.tableName, nextAuditUpdateData); // 短信通知-需要审批提醒功能 const nextAuditorIds = nextAuditors.map(x => { return x.audit_id; }); await this.ctx.helper.sendAliSms(nextAuditorIds, smsTypeConst.const.XD, smsTypeConst.judge.approval.toString(), SmsAliConst.template.revise_check); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + revise.tid + '/revise/' + revise.id + '/info' ); const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, begin_time: Date.parse(revise.begin_time), }; await this.ctx.helper.sendWechat(nextAuditorIds, smsTypeConst.const.XD, smsTypeConst.judge.approval.toString(), wxConst.template.revise, wechatData); for (const audit of nextAuditors) { await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.XD, { pid: this.ctx.session.sessionProject.id, tid: this.ctx.tender.id, uid: audit.audit_id, sp_type: 'revise', sp_id: audit.id, table_name: this.tableName, template: wxConst.template.revise, wx_data: wechatData, }); } const users = this._.map(auditList.filter(x => { return x.audit_id !== accountId}), 'audit_id'); users.push(revise.uid); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_result2, { status: SmsAliConst.status.success, }); } else { // 修订历史设为有效 if (revise.his_id) await transaction.update(this.ctx.service.ledgerHistory.tableName, { id: revise.his_id, valid: 1 }); // 同步修订信息 await transaction.update(this.ctx.service.ledgerRevise.tableName, { id: revise.id, status: auditConst.status.checked, end_time: time, }); await this.ctx.service.tenderTag.saveTenderTag(revise.tid, {revise_time: new Date()}, transaction); // 最新一期跟台账相关的缓存数据应过期 const lastStage = await this.ctx.service.stage.getFlowLatestStage(revise.tid, true); const cacheTime = new Date(); if (lastStage) { await transaction.update(this.ctx.service.stage.tableName, { id: lastStage.id, cache_time_l: cacheTime, cache_time_r: cacheTime, }); } // 重算台账、计量、工程变更 const reviseCalc = new RevisePrice(this.ctx); const pcTp = await reviseCalc.calcRevise(revise, transaction); const sum = revise.sum || await this.ctx.service.reviseBills.addUp({ tender_id: revise.tid, /* , is_leaf: true*/ }); await transaction.update(this.ctx.service.tender.tableName, { id: revise.tid, total_price: sum.total_price, deal_tp: sum.deal_tp, }); // 修订附件取消修订中标记 await transaction.update(this.ctx.service.ledgerAtt.tableName, { revising: 0 }, { where: { revise_id: revise.id } }); // 更新台账缓存 await this.ctx.service.tenderCache.updateStageCache4Revise(transaction, revise.tid, sum, pcTp); // 清除变更新增部位maxLid缓存，防止树结构混乱 await this.ctx.service.changeLedger._removeCacheMaxLid(revise.tid); // 短信通知-审批通过提醒功能 await this.ctx.helper.sendAliSms(revise.uid, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_result, { status: SmsAliConst.status.success, }); const users = this._.pull(this._.map(auditList, 'audit_id'), revise.uid); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_result2, { status: SmsAliConst.status.success, }); users.push(revise.uid); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + revise.tid + '/revise/' + revise.id + '/info' ); const wechatData2 = { wap_url: shenpiUrl, status: wxConst.status.success, tips: wxConst.tips.success, begin_time: Date.parse(revise.begin_time), code: this.ctx.session.sessionProject.code, }; await this.ctx.helper.sendWechat(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), wxConst.template.revise, wechatData2); // 审批通过 - 检查三方特殊推送 await this.ctx.service.specMsg.addReviseMsg(transaction, pid, revise, pushOperate.ledger.checked); } } await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } async _checkNo(revise, opinion, times) { const accountId = this.ctx.session.sessionUser.accountId; const pid = this.ctx.session.sessionProject.id; let audits = await this.getAllDataByCondition({ where: { rid: revise.id, times, status: auditConst.status.checking } }); const selfAuditor = audits.find(x => { return x.audit_id === accountId; }); if (!selfAuditor) throw '当前标段您无权审批'; const beginAuditors = await this.getAllDataByCondition({ where: { rid: revise.id, audit_order: 1, times } }); const time = new Date(); const noticeContent = await this.getNoticeContent(pid, revise.tid, revise.id, accountId, opinion); const defaultNoticeRecord = { pid, type: pushType.revise, status: auditConst.status.checkNo, content: noticeContent }; // 整理当前流程审核人状态更新 warning(在try catch中写，会报auditors未初始化) const updateAuditData = audits.map(x => { return { id: x.id, status: x.audit_id === selfAuditor.audit_id ? auditConst.status.checkNo : auditConst.status.checkSkip, opinion: x.audit_id === selfAuditor.audit_id ? opinion : '', end_time: x.audit_id === selfAuditor.audit_id ? time : null, }; }); const transaction = await this.db.beginTransaction(); try { const auditList = await this.getAuditors(revise.id, times); // 审核通过添加到推送表 const records = [{ uid: revise.uid, ...defaultNoticeRecord }]; auditList.forEach(audit => { records.push({ uid: audit.audit_id, ...defaultNoticeRecord }); }); await transaction.insert('zh_notice', records); // 更新当前审核流程 await transaction.updateRows(this.tableName, updateAuditData); await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, this._.map(audits, 'id')); // 同步修订信息 await transaction.update(this.ctx.service.ledgerRevise.tableName, { id: revise.id, times: times + 1, status: auditConst.status.checkNo, }); // 拷贝新一次审核流程列表 const orgAuditors = await this.getAllDataByCondition({ where: { rid: revise.id, times }, columns: ['tender_id', 'rid', 'audit_order', 'audit_id', 'audit_type', 'audit_ledger_id'], }); const insertAuditors = orgAuditors.map(x => { return { ...x, times: times + 1, status: auditConst.status.uncheck, in_time: time }; }); await transaction.insert(this.tableName, insertAuditors); await transaction.update(this.ctx.service.ledgerHistory.tableName, { id: revise.his_id, valid: 0 }); // 短信通知-审批退回提醒功能 await this.ctx.helper.sendAliSms(revise.uid, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_result, { status: SmsAliConst.status.back, }); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + revise.tid + '/revise/' + revise.id + '/info' ); const wechatData = { wap_url: shenpiUrl, status: wxConst.status.back, tips: wxConst.tips.back, begin_time: Date.parse(revise.begin_time), code: this.ctx.session.sessionProject.code, }; await this.ctx.helper.sendWechat(revise.uid, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), wxConst.template.revise, wechatData); // 其他参与人 const users = this._.map(orgAuditors.filter(x => { return x.audit_id !== accountId; }), 'audit_id'); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), SmsAliConst.template.revise_result2, {status: SmsAliConst.status.back}); // 微信模板通知 const wechatData2 = { wap_url: shenpiUrl, status: wxConst.status.back, tips: wxConst.tips.back, begin_time: Date.parse(revise.begin_time), code: this.ctx.session.sessionProject.code, }; await this.ctx.helper.sendWechat(users, smsTypeConst.const.XD, smsTypeConst.judge.result.toString(), wxConst.template.revise, wechatData2); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } /** * 审批 * @param {Object} revise - 修订 * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果 * @param {String} opinion - 审批意见 * @param {Number} times - 第几次审批 * @return {Promise} */ async check(revise, checkType, opinion, times = 1) { switch (checkType) { case auditConst.status.checked: await this._checked(revise, opinion, times); break; case auditConst.status.checkNo: await this._checkNo(revise, opinion, times); break; default: throw '提交数据错误'; } } /** * 取待审批修订列表(wap用) * * @param auditorId * @return {Promise<*>} */ async getAuditReviseByWap(auditorId) { const sql = 'SELECT ra.`audit_id`, ra.`times`, ra.`audit_order`, ra.`begin_time`, ra.`end_time`,' + ' r.id, r.corder, r.uid, r.status, r.content, r.in_time, ' + ' t.id As t_id, t.`name` As t_name, t.`project_id` As t_pid, t.`type` As t_type, t.`user_id` As t_uid, t.`status` As t_status, ' + ' ti.`deal_info`, ' + ' p.name As audit_name, p.role As audit_role, p.company As audit_company' + ' FROM ' + this.tableName + ' AS ra' + ' Left Join ' + this.ctx.service.ledgerRevise.tableName + ' As r On ra.rid = r.id' + ' Left Join ' + this.ctx.service.tender.tableName + ' AS t On r.tid = t.id' + ' Left Join ' + this.ctx.service.tenderInfo.tableName + ' AS ti On r.tid = ti.tid' + ' Left Join ' + this.ctx.service.projectAccount.tableName + ' As p On ra.audit_id = p.id' + ' WHERE r.`valid` != 0 and ra.`audit_id` = ? and ra.`status` = ?' + ' ORDER BY ra.`begin_time` DESC'; const sqlParam = [auditorId, auditConst.status.checking]; return await this.db.query(sql, sqlParam); } /** * 获取审核人需要审核的标段列表 * * @param auditorId * @return {Promise<*>} */ async getAuditRevise(auditorId) { const sql = 'SELECT ra.`audit_id`, ra.`times`, ra.`audit_order`, ra.`begin_time`, ra.`end_time`,' + ' r.id, r.corder, r.uid, r.status, r.content,' + ' t.id As t_id, t.`name` As t_name, t.`project_id` As t_pid, t.`type` As t_type, t.`user_id` As t_uid, t.`status` As t_status, ' + ' p.name As audit_name, p.role As audit_role, p.company As audit_company' + ' FROM ' + this.tableName + ' AS ra' + ' Left Join ' + this.ctx.service.ledgerRevise.tableName + ' As r On ra.rid = r.id' + ' Left Join ' + this.ctx.service.tender.tableName + ' AS t On r.tid = t.id' + ' Left Join ' + this.ctx.service.projectAccount.tableName + ' As p On ra.audit_id = p.id' + ' WHERE r.`valid` != 0 and ((ra.`audit_id` = ? and ra.`status` = ?) OR' + ' (r.`uid` = ? and r.`status` = ? and ra.`status` = ? and ra.`times` = (r.`times`-1))) ORDER BY ra.`begin_time` DESC'; const sqlParam = [auditorId, auditConst.status.checking, auditorId, auditConst.status.checkNo, auditConst.status.checkNo]; return await this.db.query(sql, sqlParam); } /** * 获取审核人审核的次数 * * @param auditorId * @return {Promise<*>} */ async getCountByChecked(auditorId) { return await this.db.count(this.tableName, { audit_id: auditorId, status: [auditConst.status.checked, auditConst.status.checkNo] }); } /** * 获取最近一次审批结束时间 * * @param auditorId * @return {Promise<*>} */ async getLastEndTimeByChecked(auditorId) { const sql = 'SELECT `end_time` FROM ?? WHERE `audit_id` = ? ' + 'AND `status` in (' + this.ctx.helper.getInArrStrSqlFilter([auditConst.status.checked, auditConst.status.checkNo]) + ') ORDER BY `end_time` DESC'; const sqlParam = [this.tableName, auditorId]; const result = await this.db.queryOne(sql, sqlParam); return result ? result.end_time : null; } /** * 获取 某时间后 审批进度 更新的台账 * @param {Integer} pid - 项目id * @param {Integer} uid - 查询人id * @param {Date} noticeTime - 查询事件 * @return {Promise<*>} */ async getNoticeRevise(pid, uid, noticeTime) { // const sql = 'SELECT * FROM (SELECT ra.`audit_id`, ra.`times`, ra.`audit_order`, ra.`end_time`, ra.`status`,' + // ' r.id, r.corder, r.uid, r.status As `r_status`, r.content, ' + // ' t.`id` As t_id, t.`name` As t_name, t.`project_id` As t_pid, t.`type` As t_type, t.`user_id` As t_uid, ' + // ' pa.name As `ru_name`, pa.role As `ru_role`, pa.company As `ru_company`' + // ' FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tender_id` FROM ?? WHERE `audit_id` = ? GROUP BY `tender_id`)) As t' + // ' LEFT JOIN ' + this.ctx.service.ledgerRevise.tableName + ' As r ON r.`tid` = t.`id`' + // ' LEFT JOIN ' + this.tableName + ' As ra ON ra.rid = r.id' + // ' LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa ON ra.`audit_id` = pa.`id`' + // ' WHERE ra.end_time > ? and t.project_id = ?' + // ' ORDER By ra.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`id`' + // ' ORDER By new_t.`end_time`'; // const sqlParam = [this.ctx.service.tender.tableName, auditorId, this.tableName, auditorId, noticeTime, projectId]; // return await this.db.query(sql, sqlParam); let notice = await this.db.select('zh_notice', { where: { pid, type: pushType.revise, uid }, orders: [['create_time', 'desc']], limit: 10, offset: 0, }); notice = notice.map(v => { const extra = JSON.parse(v.content); delete v.content; return { ...v, ...extra }; }); return notice; } /** * 用于添加推送所需的content内容 * @param {Number} pid 项目id * @param {Number} tid 台账id * @param {Number} rid 修订id * @param {Number} uid 审核人id */ async getNoticeContent(pid, tid, rid, uid, opinion = '') { const noticeSql = 'SELECT * FROM (SELECT ' + ' t.`id` As `tid`, t.`name`, r.`corder`, r.`id` As rid, pa.`name` As `su_name`, pa.role As `su_role`' + ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' + ' LEFT JOIN ?? As r On r.`id` = ? ' + ' LEFT JOIN ?? As pa ON 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.ledgerRevise.tableName, rid, this.ctx.service.projectAccount.tableName, uid, pid]; const content = await this.db.query(noticeSql, noticeSqlParam); if (content.length) { content[0].opinion = opinion; } return content.length ? JSON.stringify(content[0]) : ''; } /** * 获取最新的审批人状态 * * @param {Number} rid - 修订id * @param {Number} status - 修订状态 * @param {Number} times - 修订次数 * @return {Promise} */ async getAuditorByStatus(rid, 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.`audit_id`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`rid`, la.`audit_order` ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id` ' + ' WHERE la.`rid` = ? and la.`status` = ? order by la.`times` desc, la.`audit_order` desc'; sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, rid, status]; auditor = await this.db.queryOne(sql, sqlParam); break; case auditConst.status.checkNo: sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`rid`, la.`audit_order` ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`status` = ? and la.`times` = ? order by la.`times` desc, la.`audit_order` desc'; sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, rid, auditConst.status.checkNo, parseInt(times) - 1]; auditor = await this.db.queryOne(sql, sqlParam); break; case auditConst.status.uncheck: default: break; } return auditor; } /** * 获取审核人流程列表 * * @param auditorId * @return {Promise<*>} */ async getAuditGroupByList(rid, times) { const sql = 'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`rid`, la.`audit_order` ' + ' FROM ?? AS la Left Join ?? AS pa On la.`audit_id` = pa.`id`' + ' WHERE la.`rid` = ? and la.`times` = ? GROUP BY la.`audit_id` ORDER BY la.`audit_order`'; const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, rid, times]; return await this.db.query(sql, sqlParam); // const sql = 'SELECT `tid`, `sid`, `aid`, `order` FROM ?? WHERE `sid` = ? and `times` = ? GROUP BY `aid`'; // const sqlParam = [this.tableName, stageId, times]; // return await this.db.query(sql, sqlParam); } /** * 获取审核人流程列表(包括原报) * @param {Number} rid 修订id * @param {Number} times 审核次数 * @return {Promise} 查询结果集(包括原报) */ async getAuditorsWithOwner(rid, times = 1) { const result = await this.getAuditGroupByList(rid, times); const sql = 'SELECT pa.`id` As aid, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As rid, 0 As `order`' + ' FROM ' + this.ctx.service.ledgerRevise.tableName + ' As s' + ' LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa' + ' ON s.uid = pa.id' + ' WHERE s.id = ?'; const sqlParam = [times, rid, rid]; const user = await this.db.queryOne(sql, sqlParam); result.unshift(user); return result; } async getAllAuditors(tenderId) { const sql = 'SELECT ra.audit_id, ra.tender_id FROM ' + this.tableName + ' ra' + ' LEFT JOIN ' + this.ctx.service.tender.tableName + ' t On ra.tender_id = t.id' + ' WHERE t.id = ?' + ' GROUP BY ra.audit_id'; const sqlParam = [tenderId]; return this.db.query(sql, sqlParam); } async updateNewAuditList(revise, newList) { const transaction = await this.db.beginTransaction(); try { // 先删除旧的审批流，再添加新的 await transaction.delete(this.tableName, { rid: revise.id, times: revise.times }); const newAuditors = []; const in_time = new Date(); for (const auditor of newList) { newAuditors.push({ tender_id: revise.tid, audit_id: auditor.audit_id, rid: revise.id, in_time, times: revise.times, audit_order: auditor.audit_order, status: auditConst.status.uncheck, audit_type: auditor.audit_type, audit_ledger_id: auditor.audit_type === auditType.key.union ? auditor.audit_ledger_id : '', }) } if(newAuditors.length > 0) await transaction.insert(this.tableName, newAuditors); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } async updateLastAudit(revise, auditList, lastId) { const transaction = await this.db.beginTransaction(); try { // 先判断auditList里的audit_id是否与lastId相同，相同则删除并重新更新order const existAudit = auditList.find(x => { return x.audit_id === lastId }); let order = auditList.length > 0 ? auditList.reduce((rst, a) => { return Math.max(rst, a.audit_order)}, 0) + 1 : 1; // 最大值 + 1 if (existAudit) { await transaction.delete(this.tableName, { rid: revise.id, times: revise.times, audit_id: lastId }); const sameOrder = auditList.filter(x => { return x.audit_order === existAudit.audit_order }); if (sameOrder.length === 1) { const updateData = []; auditList.forEach(x => { if (x.audit_order <= existAudit.audit_order) return; updateData.push({id: x.id, audit_order: x.audit_order - 1}); }); if (updateData.length > 0) { await transaction.updateRows(updateData); } order = order - 1; } } // 添加终审 const newAuditor = { tender_id: revise.tid, audit_id: lastId, times: revise.times, audit_order: order, status: auditConst.status.uncheck, rid: revise.id, in_time: new Date(), }; await transaction.insert(this.tableName, newAuditor); await transaction.commit(); } catch (err) { await transaction.rollback(); throw err; } } async getNumByMonth(tid, startMonth, endMonth) { const sql = 'SELECT COUNT(*) as num FROM ?? t1 JOIN (SELECT MAX(id) as max_id FROM ?? WHERE tender_id = ? GROUP BY rid) t2 ON t1.id = t2.max_id WHERE t1.status = ? AND t1.end_time between ? and ?'; // const sql = 'SELECT COUNT(*) as num FROM ?? WHERE id in (SELECT MAX(id) FROM ?? WHERE tender_id = ? GROUP BY rid) AND status = ? AND end_time between ? and ?'; const sqlParam = [this.tableName, this.tableName, tid, auditConst.status.checked, startMonth, endMonth]; const result = await this.db.queryOne(sql, sqlParam); return result ? result.num : 0; } } return ReviseAudit; };