'use strict'; /** * * * @author Mai * @date 2018/8/14 * @version */ const audit = require('../const/audit'); const fs = require('fs'); const path = require('path'); const changeConst = require('../const/change'); const smsTypeConst = require('../const/sms_type'); const SMS = require('../lib/sms'); const SmsAliConst = require('../const/sms_alitemplate'); const wxConst = require('../const/wechat_template'); const pushType = require('../const/audit').pushType; module.exports = app => { class Change extends app.BaseService { /** * 构造函数 * * @param {Object} ctx - egg全局变量 * @return {void} */ constructor(ctx) { super(ctx); this.tableName = 'change'; } /** * 查找数据 * * @param {Object} data - 筛选表单中的get数据 * @return {void} */ searchFilter(data) { this.initSqlBuilder(); // this.sqlBuilder.columns = ['id', 'username', 'real_name', 'create_time', 'last_login', 'login_ip', // 'group_id', 'token', 'can_login']; data.type = parseInt(data.status); if (data.keyword !== undefined) { switch (data.type) { // 用户名 case 1: this.sqlBuilder.setAndWhere('username', { value: this.db.escape(data.keyword + '%'), operate: 'like', }); break; // 姓名 case 2: this.sqlBuilder.setAndWhere('real_name', { value: this.db.escape(data.keyword + '%'), operate: 'like', }); break; // 联系电话 case 3: this.sqlBuilder.setAndWhere('telephone', { value: this.db.escape(data.keyword + '%'), operate: 'like', }); break; default: break; } } // 办事处筛选 if (data.office !== undefined && data.office !== '') { this.sqlBuilder.setAndWhere('office', { value: this.db.escape(data.office), operate: '=', }); } } async add(tenderId, userId, code, name) { const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?))'; const sqlParam = [this.tableName, tenderId, code, audit.flow.status.checked, code, audit.flow.status.checked]; const codeCount = await this.db.queryOne(sql, sqlParam); const count = codeCount.count; if (count > 0) { throw '变更令号重复'; } // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { const cid = this.uuid.v4(); const change = { cid, tid: tenderId, uid: userId, status: audit.flow.status.uncheck, times: 1, valid: true, in_time: new Date(), code, name, }; const operate = await this.transaction.insert(this.tableName, change); if (operate.affectedRows <= 0) { throw '新建变更令数据失败'; } // 把提交人信息添加到zh_change_audit const userInfo = await this.ctx.service.projectAccount.getDataById(userId); const changeaudit = [ { tid: tenderId, cid, uid: userId, name: userInfo.name, jobs: userInfo.role, company: userInfo.company, times: 1, usite: 0, usort: 0, status: 2, }, ]; // 并把之前存在的变更令审批人添加到zh_change_audit // 先找出标段最近存在的变更令审批人的变更令info const changeInfo = await this.ctx.service.change.getHaveAuditLastInfo(tenderId); if (changeInfo) { // 再获取非原报审批人 const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeInfo.cid, changeInfo.times); let sort = 1; for (const audit of auditList) { if (audit.usite !== 0) { const oneaudit = { tid: tenderId, cid, uid: audit.uid, name: audit.name, jobs: audit.jobs, company: audit.company, times: 1, usite: audit.usite, usort: sort++, status: 1, }; changeaudit.push(oneaudit); } } } await this.transaction.insert(this.ctx.service.changeAudit.tableName, changeaudit); result = change; this.transaction.commit(); } catch (error) { console.log(error); // 回滚 await this.transaction.rollback(); } return result; } async getHaveAuditLastInfo(tenderId) { const sql = 'SELECT * FROM ?? as a LEFT JOIN ?? as b ON a.`cid` = b.`cid` WHERE a.`tid` = ? AND b.`usite` > 0 ORDER BY a.`in_time` DESC'; const sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId]; return await this.db.queryOne(sql, sqlParam); } async pendingDatas(tenderId, userId) { return await this.getAllDataByCondition({ tid: tenderId, uid: userId, status: audit.flow.status.checking, }); } async uncheckDatas(tenderId, userId) { return await this.getAllDataByCondition({ tid: tenderId, uid: userId, status: audit.flow.status.uncheck, }); } async checkingDatas(tenderId, userId) { return await this.getAllDataByCondition({ tid: tenderId, uid: userId, status: audit.flow.status.checking, }); } async checkedDatas(tenderId, userId) { return await this.getAllDataByCondition({ tid: tenderId, uid: userId, status: audit.flow.status.checked, }); } async checkNoDatas(tenderId, userId) { return await this.getAllDataByCondition({ tid: tenderId, uid: userId, status: audit.flow.status.checkNo, }); } async checkNoCount(tenderId, userId) { return await this.count({ tid: tenderId, uid: userId, status: audit.flow.status.checkNo, }); } /** * 获取变更令列表 * @param {int} tenderId - 标段id * @param {int} status - 状态 * @param {int} hadlimit - 分页 * @return {object} list - 列表 */ async getListByStatus(tenderId, status = 0, hadlimit = 1) { let sql = ''; let sqlParam = ''; switch (status) { case 0: // 包含你的所有变更令 sql = 'SELECT a.* FROM ?? AS a WHERE a.tid = ? AND ' + '(a.uid = ? OR (a.status != ? AND a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid)) OR a.status = ? ) ORDER BY a.in_time DESC'; sqlParam = [ this.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.flow.status.uncheck, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, audit.flow.status.checked, ]; break; case 1: // 待处理(你的) sql = 'SELECT a.* FROM ?? as a WHERE cid in(SELECT b.cid FROM ?? as b WHERE tid = ? AND uid = ? AND status = ?) ORDER BY in_time DESC'; sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.flow.auditStatus.checking]; break; case 5: // 待上报(所有的)PS：取未上报和退回的变更令 sql = 'SELECT a.* FROM ?? AS a WHERE ' + 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? GROUP BY b.cid) AND ' + '(a.status = ? OR a.status = ?) AND a.tid = ? ORDER BY a.in_time DESC'; sqlParam = [ this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, audit.flow.status.uncheck, audit.flow.status.back, tenderId, ]; break; case 2: // 进行中（所有的） case 4: // 终止（所有的） sql = 'SELECT a.* FROM ?? AS a WHERE ' + 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND ' + 'a.status = ? AND a.tid = ? ORDER BY a.in_time DESC'; sqlParam = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId]; break; case 3: // 已完成（所有的） sql = 'SELECT a.* FROM ?? AS a WHERE a.status = ? AND a.tid = ? ORDER BY a.in_time DESC'; sqlParam = [this.tableName, status, tenderId]; break; default: break; } if (hadlimit) { const limit = this.app.config.pageSize; const offset = limit * (this.ctx.page - 1); const limitString = offset >= 0 ? offset + ',' + limit : limit; sql += ' LIMIT ' + limitString; } const list = await this.db.query(sql, sqlParam); return list; } /** * 获取变更令个数 * @param {int} tenderId - 标段id * @param {int} status - 状态 * @return {void} */ async getCountByStatus(tenderId, status) { switch (status) { case 0: // 包含你的所有变更令 const sql = 'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND ' + '(a.uid = ? OR a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid))'; const sqlParam = [ this.tableName, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, ]; const result = await this.db.query(sql, sqlParam); return result[0].count; case 1: // 待处理(你的) return await this.ctx.service.changeAudit.count({ tid: tenderId, uid: this.ctx.session.sessionUser.accountId, status: 2, }); case 5: // 待上报(所有的)PS：取未上报和退回的变更令 const sql2 = 'SELECT count(*) AS count FROM ?? AS a WHERE ' + 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) ' + 'AND (a.status = ? OR a.status = ?) AND a.tid = ?'; const sqlParam2 = [ this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, audit.flow.status.uncheck, audit.flow.status.back, tenderId, ]; const result2 = await this.db.query(sql2, sqlParam2); return result2[0].count; case 2: // 进行中（所有的） case 4: // 终止（所有的） const sql3 = 'SELECT count(*) AS count FROM ?? AS a WHERE ' + 'a.cid IN (SELECT b.cid FROM ?? AS b WHERE b.uid = ? AND a.times = b.times GROUP BY b.cid) AND a.status = ? AND a.tid = ?'; const sqlParam3 = [this.tableName, this.ctx.service.changeAudit.tableName, this.ctx.session.sessionUser.accountId, status, tenderId]; const result3 = await this.db.query(sql3, sqlParam3); return result3[0].count; case 3: // 已完成（所有的） const sql4 = 'SELECT count(*) AS count FROM ?? WHERE status = ? AND tid = ?'; const sqlParam4 = [this.tableName, status, tenderId]; const result4 = await this.db.query(sql4, sqlParam4); return result4[0].count; default: break; } } /** * 上报或重新上报或保存修改功能 * @param {int} postData - 表单提交的数据 * @param {int} tenderId - 标段id * @return {void} */ async save(postData, tenderId) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 变更令信息 const changeInfo = await this.getDataByCondition({ cid: postData.cid }); // 该变更令原报人信息 const lastUser = await this.ctx.service.changeAudit.getLastUser(changeInfo.cid, changeInfo.times, 0); // 先删除本次原有的变更审批人和清单 await this.ctx.service.changeAudit.deleteAuditData(this.transaction, changeInfo.cid, changeInfo.times); await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid: changeInfo.cid }); let change_status = false; // 获取变更令提交状态 if (postData.changestatus !== undefined && parseInt(postData.changestatus) === 1) { change_status = true; // 更新原报人审批状态 await this.transaction.update(this.ctx.service.changeAudit.tableName, { id: lastUser.id, status: audit.flow.auditStatus.checked, sin_time: new Date(), }); } // 再插入postData里的变更审批人和清单 if (postData.changeaudit !== undefined && postData.changeaudit !== '') { const changeAudit = postData.changeaudit.split(','); const insertCA = []; let uSite = 1; let uSort = parseInt(lastUser.usort) + 1; for (const [index, ca] of changeAudit.entries()) { const auditInfo = ca.split('/%/'); const uStatus = change_status && index === 0 ? audit.flow.auditStatus.checking : audit.flow.auditStatus.uncheck; const sin_time = change_status && index === 0 ? new Date() : null; const caArray = { tid: tenderId, cid: changeInfo.cid, uid: auditInfo[0], name: auditInfo[1], jobs: auditInfo[2], company: auditInfo[3], times: changeInfo.times, usite: uSite, usort: uSort, status: uStatus, sin_time, }; uSite++; uSort++; insertCA.push(caArray); // 添加短信通知-需要审批提醒功能 if (change_status && index === 0) { const sms = new SMS(this.ctx); const code = await sms.contentChange(changeInfo.code); const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi' ); await this.ctx.helper.sendAliSms(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, { biangeng: code, code: shenpiUrl, }); // 微信模板通知 const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, c_name: changeInfo.name, }; await this.ctx.helper.sendWechat(auditInfo[0], smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData); } } await this.transaction.insert(this.ctx.service.changeAudit.tableName, insertCA); } let changeList = []; if (postData.changelist !== undefined && postData.changelist !== '') { changeList = postData.changelist.split('^_^'); } let changeWhiteList = []; if (postData.changewhitelist !== undefined && postData.changewhitelist !== '') { changeWhiteList = postData.changewhitelist.split('^_^'); } changeList.push.apply(changeList, changeWhiteList); const insertCL = []; let total_price = 0; if (changeList.length > 0) { for (const cl of changeList) { const clInfo = cl.split('*;*'); const clArray = { tid: tenderId, cid: changeInfo.cid, lid: clInfo[8], code: clInfo[0], name: clInfo[1], bwmx: clInfo[2], unit: clInfo[3], unit_price: clInfo[4], oamount: clInfo[5], camount: clInfo[6], samount: '', detail: clInfo[7], spamount: clInfo[6], xmj_code: clInfo[9] !== '' ? clInfo[9] : null, xmj_jldy: clInfo[10] !== '' ? clInfo[10] : null, gcl_id: clInfo[11] !== '' ? clInfo[11] : '', }; if (clInfo[4] === '') { delete clArray.unit_price; } insertCL.push(clArray); total_price = this.ctx.helper.accAdd(total_price, this.ctx.helper.mul(clArray.unit_price, clArray.spamount, this.ctx.tender.info.decimal.tp)); } await this.transaction.insert(this.ctx.service.changeAuditList.tableName, insertCL); } // 修改变更令基本数据 const cArray = { code: postData.code, name: postData.name, peg: postData.peg, org_name: postData.org_name, org_code: postData.org_code, new_name: postData.new_name, new_code: postData.new_code, content: postData.content, basis: postData.basis, expr: postData.expr, memo: postData.memo, type: postData.type.join(','), class: postData.class, quality: postData.quality, company: postData.company, charge: postData.charge, w_code: postData.w_code, total_price, }; const options = { where: { cid: changeInfo.cid, }, }; if (change_status) { cArray.status = audit.flow.status.checking; cArray.cin_time = Date.parse(new Date()) / 1000; } await this.transaction.update(this.tableName, cArray, options); await this.transaction.commit(); result = true; } catch (error) { await this.transaction.rollback(); result = false; } return result; } /** * 保存变更信息 * @param {int} postData - 表单提交的数据 * @param {int} tenderId - 标段id * @return {void} */ async saveInfo(postData) { // 初始化事务 const transaction = await this.db.beginTransaction(); let result = false; try { const options = { where: { cid: this.ctx.change.cid, }, }; await transaction.update(this.tableName, postData, options); await transaction.commit(); result = true; } catch (error) { await transaction.rollback(); result = false; } return result; } /** * 审批通过 * @param {Number} pid 项目id * @param {int} postData - 表单提交的数据 * @param {int} changeData - 变更令的数据 * @return {void} */ async approvalSuccess(pid, postData, changeData) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 获取所有审核人列表 const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid); // console.log('auditors', auditors); // console.log('postData', postData); // 添加到消息推送表 const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId); const records = []; auditors.forEach(auditor => { records.push({ pid, type: pushType.change, uid: auditor.uid, status: audit.flow.status.checked, content: noticeContent, }); }); await this.transaction.insert('zh_notice', records); // 设置审批人通过 const audit_update = { id: postData.audit_id, sdesc: postData.sdesc, status: audit.flow.auditStatus.checked, sin_time: new Date(), }; const change_update = { w_code: postData.w_code, status: audit.flow.status.checking, cin_time: Date.parse(new Date()) / 1000, }; await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update); // 清单数据更新 const bills_list = postData.bills_list.split(','); let total_price = 0; for (const bl of bills_list) { const listInfo = bl.split('_'); const lid = listInfo[0]; const amount = listInfo[1]; const changeListInfo = await this.ctx.service.changeAuditList.getDataById(lid); if (changeListInfo !== undefined) { total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(changeListInfo.unit_price, amount, this.ctx.tender.info.decimal.tp)); const audit_amount = changeListInfo.audit_amount !== null && changeListInfo.audit_amount !== '' ? changeListInfo.audit_amount.split(',') : []; audit_amount.push(amount); const list_update = { id: lid, audit_amount: audit_amount.join(','), spamount: parseFloat(amount), }; if (postData.audit_next_id === undefined) { list_update.samount = amount; } await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update); } } if (postData.audit_next_id === undefined) { // 变更令审批完成 change_update.status = audit.flow.status.checked; change_update.p_code = postData.p_code; change_update.sin_time = Date.parse(new Date()) / 1000; await this.ctx.service.tenderTag.saveTenderTag(changeData.tid, { bgl_time: new Date() }, this.transaction); // 添加短信通知-审批通过提醒功能 // const mobile_array = []; const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeData.cid, changeData.times); const users = this._.map(auditList, 'uid'); const sms = new SMS(this.ctx); const code = await sms.contentChange(changeData.code); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, { biangeng: code, status: SmsAliConst.status.success, }); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi' ); const wechatData = { wap_url: shenpiUrl, status: wxConst.status.success, tips: wxConst.tips.success, code: this.ctx.session.sessionProject.code, c_name: changeData.name, }; await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData); } else { // 设置下一个审批人为审批状态 const nextAudit_update = { id: postData.audit_next_id, status: audit.flow.auditStatus.checking, sin_time: new Date(), }; await this.transaction.update(this.ctx.service.changeAudit.tableName, nextAudit_update); // 添加短信通知-需要审批提醒功能 const nextAuditData = await this.ctx.service.changeAudit.getDataById(postData.audit_next_id); const sms = new SMS(this.ctx); const code = await sms.contentChange(changeData.code); const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi' ); await this.ctx.helper.sendAliSms(nextAuditData.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, { biangeng: code, code: shenpiUrl, }); // 微信模板通知 const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, c_name: changeData.name, }; await this.ctx.helper.sendWechat(nextAuditData.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData); } change_update.total_price = total_price; const options = { where: { cid: postData.change_id, }, }; await this.transaction.update(this.tableName, change_update, options); await this.transaction.commit(); result = true; } catch (error) { console.log(error); await this.transaction.rollback(); result = false; } return result; } /** * 审批终止 * @param {int} postData - 表单提交的数据 * @return {void} */ async approvalStop(postData) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 设置审批人终止 const audit_update = { id: postData.audit_id, sdesc: postData.sdesc, status: 4, sin_time: new Date(), }; await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update); // 设置变更令终止 const change_update = { w_code: postData.w_code, status: 4, cin_time: Date.parse(new Date()) / 1000, }; const options = { where: { cid: postData.change_id, }, }; await this.transaction.update(this.tableName, change_update, options); await this.transaction.commit(); result = true; } catch (error) { await this.transaction.rollback(); result = false; } return result; } /** * 审批退回到原报人 * @param {Number} pid 项目id * @param {int} postData - 表单提交的数据 * @param {int} changeData - 变更令的数据 * @return {void} */ async approvalBack(pid, postData, changeData) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 获取所有审核人列表 const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid); // 添加到消息推送表 const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId); const records = []; auditors.forEach(auditor => { records.push({ pid, type: pushType.change, uid: auditor.uid, status: audit.flow.status.backnew, content: noticeContent, }); }); await this.transaction.insert('zh_notice', records); const changeInfo = await this.getDataByCondition({ cid: postData.change_id }); // 设置审批人退回 const audit_update = { id: postData.audit_id, sdesc: postData.sdesc, status: audit.flow.auditStatus.back, sin_time: new Date(), }; await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update); // 新增新一次的审批人列表 // 获取当前次数审批人列表 const auditList = await this.ctx.service.changeAudit.getListGroupByTimes(changeInfo.cid, changeInfo.times); const lastauditInfo = await this.ctx.service.changeAudit.getLastUser(changeInfo.cid, changeInfo.times, 1, 0); let usort = lastauditInfo.usort + 1; const newTimes = changeInfo.times + 1; const insert_audit_array = []; for (const al of auditList) { const insert_audit = { tid: al.tid, cid: al.cid, uid: al.uid, name: al.name, jobs: al.jobs, company: al.company, times: newTimes, usite: al.usite, usort, status: al.usite !== 0 ? audit.flow.auditStatus.uncheck : audit.flow.auditStatus.checking, }; insert_audit_array.push(insert_audit); usort++; } await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit_array); // 变更金额也退回 const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({ where: { cid: changeInfo.cid }, }); let total_price = 0; for (const cl of changeList) { total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, cl.camount, this.ctx.tender.info.decimal.tp)); } // 设置变更令退回 const change_update = { w_code: postData.w_code, status: audit.flow.status.back, times: newTimes, cin_time: Date.parse(new Date()) / 1000, total_price, }; const options = { where: { cid: postData.change_id, }, }; await this.transaction.update(this.tableName, change_update, options); await this.transaction.commit(); result = true; const users = this._.map(insert_audit_array, 'uid'); const sms = new SMS(this.ctx); const code = await sms.contentChange(changeData.code); await this.ctx.helper.sendAliSms(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), SmsAliConst.template.change_result, { biangeng: code, status: SmsAliConst.status.back, }); // 微信模板通知 const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi' ); const wechatData = { wap_url: shenpiUrl, status: wxConst.status.back, tips: wxConst.tips.back, code: this.ctx.session.sessionProject.code, c_name: changeInfo.name, }; await this.ctx.helper.sendWechat(users, smsTypeConst.const.BG, smsTypeConst.judge.result.toString(), wxConst.template.change, wechatData); } catch (error) { await this.transaction.rollback(); result = false; } return result; } /** * 审批退回到上一个审批人 * @param {Number} pid 项目id * @param {int} postData - 表单提交的数据 * @param {int} changeData - 变更令的数据 * @return {void} */ async approvalBackNew(pid, postData, changeData) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 获取所有审核人列表 const auditors = await this.ctx.service.changeAudit.getAllAuditors(changeData.tid); // 添加到消息推送表 const noticeContent = await this.getNoticeContent(pid, changeData.tid, changeData.cid, this.ctx.session.sessionUser.accountId); const records = []; auditors.forEach(auditor => { records.push({ pid, type: pushType.change, uid: auditor.uid, status: audit.flow.status.back, content: noticeContent, }); }); await this.transaction.insert('zh_notice', records); const changeInfo = await this.getDataByCondition({ cid: postData.change_id }); // 设置审批人退回 const audit_update = { id: postData.audit_id, sdesc: postData.sdesc, status: audit.flow.auditStatus.backnew, sin_time: new Date(), }; await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update); // 获取当前审批人信息 const auditInfo = await this.ctx.service.changeAudit.getDataById(postData.audit_id); // 获取当前次数审批人列表 const auditList = await this.ctx.service.changeAudit.getNextAuditList(changeInfo.cid, auditInfo.usort); let usort = auditInfo.usort + 1; // 获取上一个审批人信息 const lastauditInfo = await this.ctx.service.changeAudit.getDataById(postData.audit_last_id); // 新增2个审批人到审批列表中 const insert_audit1 = { tid: lastauditInfo.tid, cid: lastauditInfo.cid, uid: lastauditInfo.uid, name: lastauditInfo.name, jobs: lastauditInfo.jobs, company: lastauditInfo.company, times: lastauditInfo.times, usite: lastauditInfo.usite, usort, status: audit.flow.auditStatus.checking, sin_time: new Date(), }; await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit1); usort++; // 新增2个审批人到审批列表中 const insert_audit2 = { tid: auditInfo.tid, cid: auditInfo.cid, uid: auditInfo.uid, name: auditInfo.name, jobs: auditInfo.jobs, company: auditInfo.company, times: auditInfo.times, usite: auditInfo.usite, usort, status: audit.flow.auditStatus.uncheck, }; await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit2); // 把接下未审批的审批人排序都加2 for (const al of auditList) { const audit_update = { id: al.id, usort: al.usort + 2, }; await this.transaction.update(this.ctx.service.changeAudit.tableName, audit_update); } // 审批列表数据也要回退 const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({ where: { cid: changeInfo.cid }, }); let total_price = 0; for (const cl of changeList) { const audit_amount = cl.audit_amount.split(','); const last_amount = audit_amount[audit_amount.length - 1]; audit_amount.splice(-1, 1); const list_update = { id: cl.id, audit_amount: audit_amount.join(','), spamount: parseFloat(last_amount), }; total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), this.ctx.tender.info.decimal.tp)); await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update); } // 设置变更令退回 const change_update = { w_code: postData.w_code, status: audit.flow.status.backnew, cin_time: Date.parse(new Date()) / 1000, total_price, }; const options = { where: { cid: postData.change_id, }, }; await this.transaction.update(this.tableName, change_update, options); await this.transaction.commit(); result = true; const sms = new SMS(this.ctx); const code = await sms.contentChange(changeData.code); const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeData.tid + '/change/' + changeData.cid + '/info#shenpi' ); await this.ctx.helper.sendAliSms(lastauditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, { biangeng: code, code: shenpiUrl, }); // 微信模板通知 const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, c_name: changeInfo.name, }; await this.ctx.helper.sendWechat(lastauditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData); } catch (error) { await this.transaction.rollback(); result = false; } return result; } /** * 查询可用的变更令 * @param bills - 查询的清单 * @param pos - 查询的部位 * @return {Promise<*>} - 可用的变更令列表 */ async getValidChanges(tid, bills, pos) { const self = this; const getFilterPart = function(field, value) { return value ? field + ' = ' + self.db.escape(value) : self.db.format("(?? = null or ?? = '')", [field, field]); }; const timesLen = 100; const filter = getFilterPart('cb.code', bills.b_code) + ' And ' + getFilterPart('cb.name', bills.name) + ' And ' + getFilterPart('cb.unit', bills.unit) + ' And cb.`unit_price` = ' + this.db.escape(bills.unit_price) + (pos ? getFilterPart('cb.bwmx', pos.name) : ''); const sql = 'SELECT c.cid, c.code, c.name, c.w_code, c.p_code, c.peg, c.org_name, c.org_code, c.new_name, c.new_code,' + ' c.content, c.basis, c.memo, c.type, c.class, c.quality, c.company, c.charge, ' + ' cb.id As cbid, cb.code As b_code, cb.name As b_name, cb.unit As b_unit, cb.samount As b_amount, cb.detail As b_detail, cb.bwmx As b_bwmx, ' + ' scb.used_amount' + ' FROM ' + this.tableName + ' As c ' + ' Left Join ' + this.ctx.service.changeAuditList.tableName + ' As cb On c.cid = cb.cid ' + ' Left Join (' + ' SELECT SUM(sc.qty) As used_amount, sc.cbid' + ' FROM ' + this.ctx.service.stageChange.tableName + ' As sc' + ' INNER JOIN (SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, cbid, sid ' + ' FROM ' + this.ctx.service.stageChange.tableName + ' WHERE tid = ?' + ' GROUP BY cbid, sid' + ' ) As MF' + ' ON (sc.stimes * ' + timesLen + ' + sc.sorder) = MF.flow And sc.cbid = MF.cbid And sc.sid = MF.sid' + ' GROUP BY sc.cbid' + ' ) As scb ON cb.id = scb.cbid' + ' WHERE c.tid = ? And c.status = ? And c.valid And ' + filter + ' ORDER BY c.in_time'; const sqlParam = [tid, tid, audit.flow.status.checked]; const changes = await this.db.query(sql, sqlParam); for (const c of changes) { const aSql = 'SELECT ca.*, pa.name As u_name, pa.role As u_role ' + ' FROM ?? As ca ' + ' Left Join ?? As pa ' + ' On ca.uid = pa.id ' + ' Where ca.cid = ?'; const aSqlParam = [this.ctx.service.changeAtt.tableName, this.ctx.service.projectAccount.tableName, c.cid]; c.attachments = await this.db.query(aSql, aSqlParam); } return changes; } /** * 查询审批人可用的变更令 * @param bills - 查询的清单 * @param pos - 查询的部位 * @return {Promise<*>} - 可用的变更令列表 */ async getAuditValidChanges(tid, bills, pos, times, order) { const timesLen = 100; const filter = 'cb.`code` = ' + this.db.escape(bills.b_code) + ' And cb.`name` = ' + this.db.escape(bills.name) + ' And cb.`unit` = ' + this.db.escape(bills.unit) + ' And cb.`unit_price` = ' + this.db.escape(bills.unit_price) + (pos ? ' And cb.`bwmx` = ' + this.db.escape(pos.name) : ''); const sql = 'SELECT c.cid, c.code, c.name, c.w_code, c.p_code, c.peg, c.org_name, c.org_code, c.new_name, c.new_code,' + ' c.content, c.basis, c.memo, c.type, c.class, c.quality, c.company, c.charge, ' + ' cb.id As cbid, cb.code As b_code, cb.name As b_name, cb.unit As b_unit, cb.samount As b_amount, cb.detail As b_detail, cb.bwmx As b_bwmx, ' + ' scb.used_amount' + ' FROM ' + this.tableName + ' As c ' + ' Left Join ' + this.ctx.service.changeAuditList.tableName + ' As cb On c.cid = cb.cid ' + ' Left Join (' + ' SELECT SUM(sc.qty) As used_amount, sc.cbid' + ' FROM ' + this.ctx.service.stageChange.tableName + ' As sc' + ' INNER JOIN (SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, cbid, sid ' + ' FROM ' + this.ctx.service.stageChange.tableName + ' WHERE tid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?)) ' + ' GROUP BY cbid, sid' + ' ) As MF' + ' ON (sc.stimes * ' + timesLen + ' + sc.sorder) = MF.flow And sc.cbid = MF.cbid And sc.sid = MF.sid' + ' GROUP BY sc.cbid' + ' ) As scb ON cb.id = scb.cbid' + ' WHERE c.tid = ? And c.status = ? And c.valid And ' + filter + ' ORDER BY c.in_time'; const sqlParam = [tid, times, times, order, tid, audit.flow.status.checked]; const changes = await this.db.query(sql, sqlParam); for (const c of changes) { const aSql = 'SELECT ca.*, pa.name As u_name, pa.role As u_role ' + ' FROM ?? As ca ' + ' Left Join ?? As pa ' + ' On ca.uid = pa.id ' + ' Where ca.cid = ?'; const aSqlParam = [this.ctx.service.changeAtt.tableName, this.ctx.service.projectAccount.tableName, c.cid]; c.attachments = await this.db.query(aSql, aSqlParam); } return changes; } /** * 查询变更令 + 变更令执行 * @param tid * @return {Promise} */ async getChangeAndUsedInfo(tid) { const lastStage = await this.ctx.service.stage.getLastestStage(tid, true); let filter; if (lastStage.id === this.ctx.stage.id) { filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [ lastStage.order, lastStage.order, this.ctx.stage.curTimes, this.ctx.stage.curTimes, this.ctx.stage.curOrder, ]); } else { if (lastStage.status === audit.stage.status.uncheck) { filter = ' And s.order < ' + lastStage.order; } else if (lastStage.status === audit.stage.status.checked) { filter = ''; } else if (lastStage.status === audit.stage.status.checkNo) { filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And sChange.`stimes` <= ?))', [lastStage.order, lastStage.order, lastStage.times]); } else { const curAuditor = await this.ctx.service.stageAudit.getCurAuditor(lastStage.id, lastStage.times); filter = this.db.format(' And (s.`order` < ? OR (s.`order` = ? And (sChange.`stimes` < ? OR (sChange.`stimes` = ? And sChange.`sorder` <= ?))))', [ lastStage.order, lastStage.order, lastStage.times, lastStage.times, curAuditor.order - 1, ]); } } const sql = 'SELECT C.*, Sum(U.utp) As used_tp, Round(Sum(U.utp) / C.total_price * 100, 2) As used_pt' + ' FROM ' + this.tableName + ' As C' + ' LEFT JOIN (SELECT sc.tid, sc.cid, sc.cbid, Round(SUM(sc.qty) * cb.unit_price, ?) As utp' + ' FROM ' + this.ctx.service.stageChange.tableName + ' As sc' + ' INNER JOIN (' + ' SELECT MAX(`stimes`) As `stimes`, MAX(`sorder`) As `sorder`, `lid`, `pid`, `cbid`, sChange.`sid` ' + ' FROM ' + this.ctx.service.stageChange.tableName + ' As sChange ' + ' LEFT JOIN ' + this.ctx.service.stage.tableName + ' As s' + ' ON sChange.sid = s.id' + ' WHERE sChange.tid = ?' + filter + ' GROUP By `lid`, `pid`, `cbid`, `sid`' + ' ) As m' + ' ON sc.stimes = m.stimes And sc.sorder = m.sorder And sc.`cbid` = m.`cbid` AND sc.`sid` = m.`sid` And sc.`lid` = m.`lid` And sc.`pid` = m.`pid`' + ' LEFT JOIN ' + this.ctx.service.changeAuditList.tableName + ' As cb ON sc.cbid = cb.id' + ' GROUP By sc.`cbid`' + ' ) As U ON C.cid = U.cid' + ' WHERE C.tid = ? And C.status = ? And C.valid' + ' GROUP By C.cid' + ' ORDER By in_time'; const sqlParam = [this.ctx.tender.info.decimal.tp, tid, tid, audit.flow.status.checked]; return await this.db.query(sql, sqlParam); } /** * 查询可用的变更令 * @param { string } cid - 查询的清单 * @return {Promise<*>} - 可用的变更令列表 */ async delete(cid) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { // 先删除清单，审批人列表 await this.transaction.delete(this.ctx.service.changeAuditList.tableName, { cid }); await this.transaction.delete(this.ctx.service.changeAudit.tableName, { cid }); // 再删除附件和附件文件ni zuo const attList = await this.ctx.service.changeAtt.getAllDataByCondition({ where: { cid } }); if (attList.length !== 0) { for (const att of attList) { await fs.unlinkSync(path.join(this.app.baseDir, att.filepath)); } await this.transaction.delete(this.ctx.service.changeAtt.tableName, { cid }); } // 最后删除变更令 await this.transaction.delete(this.tableName, { cid }); await this.transaction.commit(); result = true; } catch (e) { await this.transaction.rollback(); result = false; } return result; } /** * 重新审批变更令 * @param { string } cid - 查询的清单 * @return {Promise<*>} - 可用的变更令列表 */ async checkAgain(cid) { // 初始化事务 this.transaction = await this.db.beginTransaction(); let result = false; try { const changeInfo = await this.getDataByCondition({ cid }); // 获取终审 const auditInfo = ( await this.ctx.service.changeAudit.getAllDataByCondition({ where: { cid }, orders: [['usort', 'desc']], limit: 1, offset: 0, }) )[0]; let usort = auditInfo.usort + 1; // 新增2个审批状态到审批列表中 const insert_audit1 = { tid: auditInfo.tid, cid: auditInfo.cid, uid: auditInfo.uid, name: auditInfo.name, jobs: auditInfo.jobs, company: auditInfo.company, times: auditInfo.times, usite: auditInfo.usite, usort, sin_time: new Date(), status: audit.flow.auditStatus.checkAgain, }; await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit1); usort++; // 新增2个审批人到审批列表中 const insert_audit2 = { tid: auditInfo.tid, cid: auditInfo.cid, uid: auditInfo.uid, name: auditInfo.name, jobs: auditInfo.jobs, company: auditInfo.company, times: auditInfo.times, usite: auditInfo.usite, usort, status: audit.flow.auditStatus.checking, sin_time: new Date(), }; await this.transaction.insert(this.ctx.service.changeAudit.tableName, insert_audit2); // 审批列表数据也要回退 let total_price = 0; const changeList = await this.ctx.service.changeAuditList.getAllDataByCondition({ where: { cid: changeInfo.cid }, }); for (const cl of changeList) { const audit_amount = cl.audit_amount.split(','); const last_amount = audit_amount[audit_amount.length - 1]; audit_amount.splice(-1, 1); const list_update = { id: cl.id, audit_amount: audit_amount.join(','), samount: '', }; total_price = this.ctx.helper.add(total_price, this.ctx.helper.mul(cl.unit_price, parseFloat(last_amount), this.ctx.tender.info.decimal.tp)); await this.transaction.update(this.ctx.service.changeAuditList.tableName, list_update); } // 设置变更令审批中 const change_update = { p_code: null, status: audit.flow.status.checking, cin_time: Date.parse(new Date()) / 1000, sin_time: null, total_price, }; const options = { where: { cid: changeInfo.cid, }, }; await this.transaction.update(this.tableName, change_update, options); await this.transaction.commit(); result = true; const sms = new SMS(this.ctx); const code = await sms.contentChange(changeInfo.code); const shenpiUrl = await this.ctx.helper.urlToShort( this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + changeInfo.tid + '/change/' + changeInfo.cid + '/info#shenpi' ); await this.ctx.helper.sendAliSms(auditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), SmsAliConst.template.change_check, { biangeng: code, code: shenpiUrl, }); // 微信模板通知 const wechatData = { wap_url: shenpiUrl, status: wxConst.status.check, tips: wxConst.tips.check, code: this.ctx.session.sessionProject.code, c_name: changeInfo.name, }; await this.ctx.helper.sendWechat(auditInfo.uid, smsTypeConst.const.BG, smsTypeConst.judge.approval.toString(), wxConst.template.change, wechatData); } catch (error) { await this.transaction.rollback(); result = false; } return result; } /** * 判断是否有重名的变更令 * @param cid * @param code * @param tid * @return {Promise} */ async isRepeat(cid, code, tid) { const sql = 'SELECT COUNT(*) as count FROM ?? WHERE ((`code` = ? AND `status` != ?) OR (`p_code` = ? AND `status` = ?)) AND `cid` != ? AND `tid` = ?'; const sqlParam = [this.tableName, code, audit.flow.status.checked, code, audit.flow.status.checked, cid, tid]; const result = await this.db.queryOne(sql, sqlParam); return result.count !== 0; } async getAllCheckedChanges(tid) { return await this.getAllDataByCondition({ where: { tid, status: audit.flow.status.checked }, orders: [['in_time', 'desc']], }); } /** * 用于添加推送所需的content内容 * @param {Number} pid 项目id * @param {Number} tid 台账id * @param {Number} cid 变更id * @param {Number} uid 审核人id */ async getNoticeContent(pid, tid, cid, uid) { const noticeSql = 'SELECT * FROM (SELECT ' + ' t.`id` As `tid`, t.`name`, c.`cid`, c.`code` As `c_code`, pa.`name` As `su_name`, pa.role As `su_role`' + ' FROM (SELECT * FROM ?? WHERE `id` = ? ) As t' + ' LEFT JOIN ?? As c ON c.`cid` = ?' + ' 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.change.tableName, cid, this.ctx.service.projectAccount.tableName, uid, pid]; const content = await this.db.query(noticeSql, noticeSqlParam); return content.length ? JSON.stringify(content[0]) : ''; } /** * 获取当前标段其他变更令 * @param {Number} tid - 标段id * @param {Number} cid - 当前变更令 */ async getOthersChange(tid, cid) { const sql = 'SELECT * FROM ?? WHERE tid = ? AND cid != ? ORDER By `in_time` desc '; const sqlParam = [this.tableName, tid, cid]; const data = await this.db.query(sql, sqlParam); const changeClassObj = {}; for (const c in changeConst.class) { if (changeConst.class.hasOwnProperty(c)) { const item = changeConst.class[c]; changeClassObj[item.value] = item.name; } } for (let i = 0; i < data.length; i++) { data[i].class = changeClassObj[data[i].class]; } return data; } /** * 拷贝变更令至当前变更令 * @param {String} cid - 当前变更令 * @param {String} copy_cid - 要拷贝的变更令 */ async handleCopyChange(cid, copy_cid) { // const change = await this.getDataByCondition({ cid }); const copyChange = await this.getDataByCondition({ cid: copy_cid }); return await this.update({ peg: copyChange.peg, org_name: copyChange.org_name, org_code: copyChange.org_code, new_name: copyChange.new_name, content: copyChange.content, basis: copyChange.basis, expr: copyChange.expr, memo: copyChange.memo, type: copyChange.type, class: copyChange.class, quality: copyChange.quality, company: copyChange.company, charge: copyChange.charge, new_code: copyChange.new_code }, { cid, }); } } return Change; };