Calculation/app/service/advance_audit.js

'use strict'

const auditConst = require('../const/audit').advance
const pushType = require('../const/audit').pushType
module.exports = app => {
    class AdvanceAudit extends app.BaseService {
        constructor(ctx) {
            super(ctx)
            this.tableName = 'advance_audit'
        }

        /**
         * 获取审核人流程列表
         * @param {Number} vid 预付款记录id
         * @param {Number} times 审核次数
         * @return {Promise<Array>} 查询结果集
         */
        async getAuditGroupByList(vid, times = 1) {
            const sql =
                'SELECT la.`audit_id`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`vid`, la.`order` ' +
                'FROM ?? AS la, ?? AS pa ' +
                'WHERE la.`vid` = ? and la.`times` = ? and la.`audit_id` = pa.`id` GROUP BY la.`audit_id` ORDER BY la.`order`'
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, vid, times]
            return await this.db.query(sql, sqlParam)
        }

        /**
         * 获取审核人流程列表(包括原报)
         * @param {Number} vid 预付款记录id
         * @param {Number} times 审核次数
         * @return {Promise<Array>} 查询结果集(包括原报)
         */
        async getAuditorsWithOwner(vid, times = 1) {
            const result = await this.getAuditGroupByList(vid, times)
            const sql =
                'SELECT pa.`id` As aid, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As vid, 0 As `order`' +
                '  FROM ' +
                this.ctx.service.advance.tableName +
                ' As s' +
                '  LEFT JOIN ' +
                this.ctx.service.projectAccount.tableName +
                ' As pa' +
                '  ON s.uid = pa.id' +
                '  WHERE s.id = ?'
            const sqlParam = [times, vid, vid]
            const user = await this.db.queryOne(sql, sqlParam)
            result.unshift(user)
            return result
        }

        /**
         * 获取最新审核顺序
         * @param {Number} vid - 预付款id
         * @param {Number} times - 第几次审批
         * @return {Number} 审核顺序
         */
        async getNewOrder(vid, times = 1) {
            const sql = 'SELECT Max(??) As max_order FROM ?? Where `vid` = ? and `times` = ?'
            const sqlParam = ['order', this.tableName, vid, times]
            const result = await this.db.queryOne(sql, sqlParam)
            return result && result.max_order ? result.max_order + 1 : 1
        }

        /**
         * 新增审核人
         * @param {Number} vid - 预付款id
         * @param {Number} audit_id - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Boolean} 是否插入成功
         */
        async addAuditor(vid, audit_id, times = 1) {
            const newOrder = await this.getNewOrder(vid, times)
            const record = {
                vid,
                audit_id,
                times,
                order: newOrder,
                status: auditConst.status.uncheck
            }
            const result = await this.db.insert(this.tableName, record)
            return result && result.affectedRows === 1
        }

        /**
         * 移除审核人
         * @param {Number} vid - 预付款id
         * @param {Number} audit_id - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<boolean>}
         */
        async deleteAuditor(vid, audit_id, times = 1) {
            const transaction = await this.db.beginTransaction()
            try {
                const condition = { vid, audit_id, times }
                const auditor = await this.getDataByCondition(condition)
                if (!auditor) {
                    throw '该审核人不存在'
                }
                await this._syncOrderByDelete(transaction, vid, auditor.order, times)
                await transaction.delete(this.tableName, condition)
                await transaction.commit()
            } catch (err) {
                await transaction.rollback()
                throw err
            }
            return true
        }

        /**
         * 移除审核人时，同步其后审核人order
         * @param {Function} transaction - 事务
         * @param {Number} vid - 预付款id
         * @param {Number} order - 审核顺序
         * @param {Number} times - 第几次审批
         * @return {Promise<*>} 查询结果集
         * @private
         */
        async _syncOrderByDelete(transaction, vid, order, times) {
            this.initSqlBuilder()
            this.sqlBuilder.setAndWhere('vid', {
                value: this.db.escape(vid),
                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} vid - 预付款id
         * @param {Number} times - 第几次审批
         * @return {Promise<Object>} 查询结果集
         */
        async getCurAuditor(vid, times = 1) {
            const sql =
                'SELECT la.`audit_id`, 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.`vid` = ? and la.`status` = ? and la.`times` = ?' +
                '    and la.`audit_id` = pa.`id`'
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, vid, auditConst.status.checking, times]
            return await this.db.queryOne(sql, sqlParam)
        }

        /**
         * 获取审核列表信息
         * @param {Number} vid - 预付款id
         * @param {Number} times - 第几次审批
         * @return {Promise<Array>} 查询结果集
         */
        async getAuditors(vid, times = 1) {
            const sql =
                'SELECT la.`audit_id`, 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 `audit_id`,(@i:=@i+1) as `sort` FROM ??, (select @i:=0) as it WHERE `vid` = ? AND `times` = ? GROUP BY `audit_id`) as g ' +
                'WHERE la.`vid` = ? and la.`times` = ? and la.`audit_id` = pa.`id` and g.`audit_id` = la.`audit_id` order by la.`order`'
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, vid, times, vid, times]
            const result = await this.db.query(sql, sqlParam)
            const sql2 = 'SELECT COUNT(a.`audit_id`) as num FROM (SELECT `audit_id` FROM ?? WHERE `vid` = ? AND `times` = ? GROUP BY `audit_id`) as a'
            const sqlParam2 = [this.tableName, vid, times]
            const count = await this.db.queryOne(sql2, sqlParam2)
            for (const i in result) {
                result[i].max_sort = count.num
            }
            return result
        }

        /**
         * 获取审核人信息
         * @param {Number} vid - 预付款id
         * @param {Number} audit_id - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>} 查询结果
         */
        async getAuditor(vid, audit_id, times = 1) {
            const sql =
                'SELECT la.`audit_id`, 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.`vid` = ? and la.`audit_id` = ? and la.`times` = ?' +
                '    and la.`audit_id` = pa.`id`'
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, vid, audit_id, times]
            return await this.db.queryOne(sql, sqlParam)
        }

        /**
         * 开始审批
         * @param {Number} vid - 预付款id
         * @param {Number} times - 第几次审批
         * @param {Object} data - 载荷
         */
        async start(vid, times = 1, data) {
            const audit = await this.getDataByCondition({ vid, 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.advance.tableName, {
                    id: audit.vid,
                    ...data
                })
                await transaction.commit()
            } catch (err) {
                await transaction.rollback()
                throw err
            }
            return true
        }

        async _checked(pid, advanceId, checkData, times) {
            const time = new Date()

            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ vid: advanceId, times, status: auditConst.status.checking })
            if (!audit) {
                throw '审核数据错误'
            }

            // 获取审核人列表
            const sql = 'SELECT `tid`, `vid`, `audit_id`, `order` FROM ?? WHERE `vid` = ? and `times` = ? GROUP BY `audit_id` ORDER BY `id` ASC'
            const sqlParam = [this.tableName, advanceId, times]
            const auditors = await this.db.query(sql, sqlParam)

            const nextAudit = await this.getDataByCondition({ vid: advanceId, 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 })
                // 获取推送必要信息
                const noticeContent = await this.getNoticeContent(pid, audit.tid, advanceId, audit.audit_id)
                // 添加推送
                const records = [{ pid, type: pushType.advance, uid: this.ctx.advance.uid, status: auditConst.status.checked, content: noticeContent }]
                auditors.forEach(audit => {
                    records.push({ pid, type: pushType.advance, uid: audit.aid, status: auditConst.status.checked, content: noticeContent })
                })
                await transaction.insert(this.ctx.service.notice.tableName, records)
                // 无下一审核人表示，审核结束
                if (nextAudit) {
                    // 流程至下一审批人
                    await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time })

                    // 同步期信息
                    await transaction.update(this.ctx.service.advance.tableName, {
                        id: advanceId,
                        status: auditConst.status.checking
                    })
                } else {
                    await transaction.update(this.ctx.service.advance.tableName, {
                        id: advanceId,
                        status: checkData.checkType
                    })
                }
                await transaction.commit()
            } catch (err) {
                await transaction.rollback()
                throw err
            }
        }

        async _checkNo(pid, advanceId, checkData, times) {
            const time = new Date()
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ vid: advanceId, times, status: auditConst.status.checking })
            if (!audit) {
                throw '审核数据错误'
            }
            const sql = 'SELECT `tid`, `vid`, `audit_id`, `order` FROM ?? WHERE `vid` = ? and `times` = ? GROUP BY `audit_id` ORDER BY `id` ASC'
            const sqlParam = [this.tableName, advanceId, 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, advanceId, audit.aid)
                const records = [{ pid, type: pushType.advance, uid: this.ctx.advance.uid, status: auditConst.status.checkNo, content: noticeContent }]
                auditors.forEach(audit => {
                    records.push({ pid, type: pushType.advance, uid: audit.aid, status: auditConst.status.checkNo, content: noticeContent })
                })
                await transaction.insert(this.ctx.service.notice.tableName, records)

                // 同步期信息
                await transaction.update(this.ctx.service.advance.tableName, {
                    id: advanceId,
                    status: checkData.checkType,
                    times: times + 1
                })
                // 拷贝新一次审核流程列表
                await transaction.insert(this.tableName, auditors)
                await transaction.commit()
            } catch (err) {
                await transaction.rollback()
                throw err
            }
        }

        async _checkNoPre(pid, advanceId, checkData, times) {
            const time = new Date()
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ vid: advanceId, times, status: auditConst.status.checking })
            if (!audit || audit.order <= 1) {
                throw '审核数据错误'
            }
            // 添加重新审批后，不能用order-1,取groupby值里的上一个才对
            const auditors2 = await this.getAuditGroupByList(advanceId, times)
            const auditorIndex = await auditors2.findIndex(function (item) {
                return item.audit_id === audit.audit_id
            })
            const preAuditor = auditors2[auditorIndex - 1]
            const noticeContent = await this.getNoticeContent(pid, audit.tid, advanceId, audit.aid)
            const transaction = await this.db.beginTransaction()
            try {
                // 添加到消息推送表
                const records = [{ pid, type: pushType.advance, uid: this.ctx.advance.uid, status: auditConst.status.checkNoPre, content: noticeContent }]
                auditors2.forEach(audit => {
                    records.push({ pid, type: pushType.advance, uid: audit.aid, status: auditConst.status.checkNoPre, content: noticeContent })
                })
                await transaction.insert(this.ctx.service.notice.tableName, records)
                await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time })
                // 顺移气候审核人流程顺序
                this.initSqlBuilder()
                this.sqlBuilder.setAndWhere('vid', { value: advanceId, 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')
                await transaction.query(sql, sqlParam)
                const newAuditors = []
                newAuditors.push({
                    tid: audit.tid,
                    vid: audit.vid,
                    audit_id: preAuditor.audit_id,
                    times: audit.times,
                    order: audit.order + 1,
                    status: auditConst.status.checking,
                    begin_time: time
                })
                newAuditors.push({
                    tid: audit.tid,
                    vid: audit.vid,
                    audit_id: audit.audit_id,
                    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} advanceId - 预付款id
         * @param {auditConst.status.checked|auditConst.status.checkNo} checkData - 审批结果
         * @param {Number} times - 第几次审批
         * @return {Promise<void>}
         */
        async check(advanceId, 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, advanceId, checkData, times)
                    break
                case auditConst.status.checkNo:
                    await this._checkNo(pid, advanceId, checkData, times)
                    break
                case auditConst.status.checkNoPre:
                    await this._checkNoPre(pid, advanceId, checkData, times)
                    break
                default:
                    throw '无效审批操作'
            }
        }

        /**
         * 用于添加推送所需的content内容
         * @param {Number} pid 项目id
         * @param {Number} tid 台账id
         * @param {Number} vid 预付款id
         * @param {Number} uid 审批人id
         */
        async getAdvanceContent(pid, tid, vid, uid) {
            const noticeSql =
                'SELECT * FROM (SELECT ' +
                '  t.`id` As `tid`, ad.`vid`, 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 ad ON m.`id` = ad.`vid`' +
                '  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.advance.tableName,
                vid,
                this.tableName,
                this.ctx.service.projectAccount.tableName,
                uid,
                pid
            ]
            const content = await this.db.query(noticeSql, noticeSqlParam)
            return content.length ? JSON.stringify(content[0]) : ''
        }
    }
    return AdvanceAudit
}