Calculation/app/service/payment_detail_audit.js

'use strict';

/**
 * 版本数据模型
 *
 * @author CaiAoLin
 * @date 2017/10/25
 * @version
 */
const auditConst = require('../const/audit').stage;
const shenpiConst = require('../const/shenpi');

module.exports = app => {

    class PaymentDetailAudit extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'payment_detail_audit';
        }

        /**
         * 获取审核人流程列表
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getAuditGroupByList(detailId, times) {
            const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`td_id`, la.`aid`, la.`order` ' +
                '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
                '  WHERE la.`td_id` = ? and la.`times` = ? GROUP BY la.`aid` ORDER BY la.`order`';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, detailId, times];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 复制上一期的审批人列表给最新一期
         *
         * @param transaction - 新增一期的事务
         * @param {Object} preStage - 上一期
         * @param {Object} newStage - 最新一期
         * @return {Promise<*>}
         */
        async copyPreDetailAuditors(transaction, preDetail, newDetail) {
            const auditors = await this.getAuditGroupByList(preDetail.id, preDetail.times);
            const newAuditors = [];
            for (const a of auditors) {
                if (a.aid !== newDetail.uid) {
                    const na = {
                        tender_id: preDetail.tender_id,
                        tr_id: preDetail.tr_id,
                        td_id: newDetail.id,
                        aid: a.aid,
                        times: newDetail.times,
                        order: newAuditors.length + 1,
                        status: auditConst.status.uncheck,
                    };
                    newAuditors.push(na);
                }
            }
            const result = await transaction.insert(this.tableName, newAuditors);
            return (result.effectRows = auditors.length);
        }

        /**
         * 获取 审核列表信息
         *
         * @param {Number} detailId - 支付审批表单详情id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>}
         */
        async getAuditors(detailId, times = 1) {
            const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`, g.`sort` ' +
                'FROM ?? AS la, ?? AS pa, (SELECT `aid`,(@i:=@i+1) as `sort` FROM ??, (select @i:=0) as it WHERE `td_id` = ? AND `times` = ? GROUP BY `aid`) as g ' +
                'WHERE la.`td_id` = ? and la.`times` = ? and la.`aid` = pa.`id` and g.`aid` = la.`aid` order by la.`order`';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, this.tableName, detailId, times, detailId, times];
            const result = await this.db.query(sql, sqlParam);
            const sql2 = 'SELECT COUNT(a.`aid`) as num FROM (SELECT `aid` FROM ?? WHERE `td_id` = ? AND `times` = ? GROUP BY `aid`) as a';
            const sqlParam2 = [this.tableName, detailId, times];
            const count = await this.db.queryOne(sql2, sqlParam2);
            for (const i in result) {
                result[i].max_sort = count.num;
            }
            return result;
        }

        /**
         * 获取 当前审核人
         *
         * @param {Number} materialId - 支付审批表单详情id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>}
         */
        async getCurAuditor(detailId, times = 1) {
            const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
                '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                '  WHERE la.`td_id` = ? and la.`status` = ? and la.`times` = ?';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, detailId, auditConst.status.checking, times];
            return await this.db.queryOne(sql, sqlParam);
        }

        async updateNewAuditList(detail, newIdList) {
            const transaction = await this.db.beginTransaction();
            try {
                // 先删除旧的审批流，再添加新的
                await transaction.delete(this.tableName, { td_id: detail.id, times: detail.times });
                const newAuditors = [];
                let order = 1;
                for (const aid of newIdList) {
                    newAuditors.push({
                        tender_id: detail.tender_id, tr_id: detail.tr_id, td_id: detail.id, aid,
                        times: detail.times, order, status: auditConst.status.uncheck,
                    });
                    order++;
                }
                if (newAuditors.length > 0) await transaction.insert(this.tableName, newAuditors);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async updateLastAudit(detail, auditList, lastId) {
            const transaction = await this.db.beginTransaction();
            try {
                // 先判断auditList里的aid是否与lastId相同，相同则删除并重新更新order
                const idList = this._.map(auditList, 'aid');
                let order = idList.length + 1;
                if (idList.indexOf(lastId) !== -1) {
                    await transaction.delete(this.tableName, { td_id: detail.id, times: detail.times, aid: lastId });
                    const audit = this._.find(auditList, { aid: lastId });
                    // 顺移之后审核人流程顺序
                    await this._syncOrderByDelete(transaction, detail.id, audit.order, detail.times);
                    order = order - 1;
                }

                // 添加终审
                const newAuditor = {
                    tender_id: detail.tender_id, tr_id: detail.tr_id, td_id: detail.id, aid: lastId,
                    times: detail.times, order, status: auditConst.status.uncheck,
                };
                await transaction.insert(this.tableName, newAuditor);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 移除审核人时，同步其后审核人order
         * @param transaction - 事务
         * @param {Number} materialId - 材料调差期id
         * @param {Number} auditorId - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>}
         * @private
         */
        async _syncOrderByDelete(transaction, detailId, order, times, selfOperate = '-') {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('td_id', {
                value: detailId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: order,
                operate: '>=',
            });
            this.sqlBuilder.setAndWhere('times', {
                value: times,
                operate: '=',
            });
            this.sqlBuilder.setUpdateData('order', {
                value: 1,
                selfOperate,
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const data = await transaction.query(sql, sqlParam);

            return data;
        }

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

        /**
         * 获取 最新审核顺序
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<number>}
         */
        async getNewOrder(detailId, times = 1) {
            const sql = 'SELECT Max(??) As max_order FROM ?? Where `td_id` = ? and `times` = ?';
            const sqlParam = ['order', this.tableName, detailId, times];
            const result = await this.db.queryOne(sql, sqlParam);
            return result && result.max_order ? result.max_order + 1 : 1;
        }

        /**
         * 新增审核人
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} auditorId - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<number>}
         */
        async addAuditor(detailId, auditorId, times = 1, is_gdzs = 0) {
            const transaction = await this.db.beginTransaction();
            try {
                let newOrder = await this.getNewOrder(detailId, times);
                // 判断是否存在固定终审，存在则newOrder - 1并使终审order+1
                newOrder = is_gdzs === 1 ? newOrder - 1 : newOrder;
                if (is_gdzs) await this._syncOrderByDelete(transaction, detailId, newOrder, times, '+');
                const data = {
                    tender_id: this.ctx.tender.id,
                    tr_id: this.ctx.trInfo.id,
                    td_id: detailId,
                    aid: auditorId,
                    times,
                    order: newOrder,
                    status: auditConst.status.uncheck,
                };
                const result = await transaction.insert(this.tableName, data);
                await transaction.commit();
                return result.affectedRows === 1;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return false;
        }

        /**
         * 移除审核人
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} auditorId - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<boolean>}
         */
        async deleteAuditor(detailId, auditorId, times = 1) {
            const transaction = await this.db.beginTransaction();
            try {
                const condition = { td_id: detailId, aid: auditorId, times };
                const auditor = await this.getDataByCondition(condition);
                if (!auditor) {
                    throw '该审核人不存在';
                }
                await this._syncOrderByDelete(transaction, detailId, auditor.order, times);
                await transaction.delete(this.tableName, condition);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return true;
        }

        /**
         * 移除审核人
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} status - 期状态
         * @param {Number} status - 期次数
         * @return {Promise<boolean>}
         */
        async getAuditorByStatus(detailId, status, times = 1) {
            let auditor = null;
            let sql = '';
            let sqlParam = '';
            switch (status) {
                case auditConst.status.checking :
                case auditConst.status.checked :
                case auditConst.status.checkNoPre :
                    sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`tr_id`, la.`td_id`, la.`aid`, la.`order` ' +
                        '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                        '  WHERE la.`td_id` = ? and la.`status` = ? ' +
                        '  ORDER BY la.`times` desc, la.`order` desc';
                    sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, detailId, status];
                    auditor = await this.db.queryOne(sql, sqlParam);
                    break;
                case auditConst.status.checkNo :
                    sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`tr_id`, la.`td_id`, la.`aid`, la.`order` ' +
                        '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
                        '  WHERE la.`td_id` = ? and la.`status` = ? and la.`times` = ?' +
                        '  ORDER BY la.`times` desc, la.`order` desc';
                    sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, detailId, auditConst.status.checkNo, parseInt(times) - 1];
                    auditor = await this.db.queryOne(sql, sqlParam);
                    break;
                case auditConst.status.uncheck :
                default:break;
            }
            return auditor;
        }

        /**
         * 开始审批
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<boolean>}
         */
        async start(detailId, times = 1) {
            const audit = await this.getDataByCondition({ td_id: detailId, times, order: 1 });
            if (!audit) {
                if (this.ctx.trinfo.sp_status === shenpiConst.sp_status.gdspl) {
                    throw '请联系管理员添加审批人';
                } else {
                    throw '请先选择审批人，再上报数据';
                }
            }

            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, { id: audit.id, status: auditConst.status.checking, begin_time: new Date() });
                // 如果是报表角色则需要更新到detail中
                const updateDetailData = {
                    id: detailId, status: auditConst.status.checking,
                };
                const rptAudit = await this.ctx.service.paymentRptAudit.getDataByCondition({ td_id: detailId, uid: this.ctx.session.sessionUser.accountId });
                if (rptAudit && rptAudit.signature_msg) {
                    const report_json = JSON.parse(this.ctx.detail.report_json);
                    const sign_msg = JSON.parse(rptAudit.signature_msg);
                    updateDetailData.report_json = JSON.stringify(await this.ctx.service.paymentDetail.signOneSignatureData(report_json, rptAudit.signature_name, sign_msg));
                }
                await transaction.update(this.ctx.service.paymentDetail.tableName, updateDetailData);
                // 判断用户是否有权限查看支付审批，没有则自动加入到权限中
                const auditList = await this.getAllDataByCondition({ where: { td_id: detailId, times } });
                const rptAuditList = await this.ctx.service.paymentRptAudit.getAllDataByCondition({ where: { td_id: detailId } });
                const auditIdList = this._.map(auditList, 'aid');
                const rptAuditIdList = this._.map(rptAuditList, 'uid');
                const permissionAuditList = await this.ctx.service.paymentPermissionAudit.getAllDataByCondition({ where: { pid: this.ctx.session.sessionProject.id } });
                const paIdList = this._.map(permissionAuditList, 'uid');
                const detailIdList = this._.union(auditIdList, rptAuditIdList);
                const newAudits = this._.difference(detailIdList, paIdList);
                if (newAudits.length > 0) {
                    const accountList = await this.ctx.service.projectAccount.getAllDataByCondition({
                        where: { project_id: this.ctx.session.sessionProject.id, id: newAudits },
                        columns: ['id', 'name', 'company', 'role', 'enable', 'is_admin', 'account_group', 'mobile'],
                    });
                    await this.ctx.service.paymentPermissionAudit.saveAudits(this.ctx.session.sessionProject.id, accountList, transaction);
                }
                // todo 更新标段tender状态 ?
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return true;
        }

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

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

            const nextAudit = await this.getDataByCondition({ td_id: detailId, 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 });

                // 如果是报表角色则需要更新到detail中
                const updateDetailData = {
                    id: detailId,
                };
                const rptAudit = await this.ctx.service.paymentRptAudit.getDataByCondition({ td_id: detailId, uid: audit.aid });
                if (rptAudit && rptAudit.signature_msg) {
                    const report_json = JSON.parse(this.ctx.detail.report_json);
                    const sign_msg = JSON.parse(rptAudit.signature_msg);
                    updateDetailData.report_json = JSON.stringify(await this.ctx.service.paymentDetail.signOneSignatureData(report_json, rptAudit.signature_name, sign_msg));
                }

                // 无下一审核人表示，审核结束
                if (nextAudit) {
                    // 流程至下一审批人
                    await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time });

                    // 同步 期信息
                    updateDetailData.status = auditConst.status.checking;
                } else {
                    // 本期结束
                    // 同步 期信息
                    updateDetailData.status = checkData.checkType;
                }
                await transaction.update(this.ctx.service.paymentDetail.tableName, updateDetailData);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async _checkNo(pid, detailId, checkData, times) {
            const time = new Date();
            const detailInfo = await ctx.service.paymentDetail.getDataById(detailId);
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ td_id: detailId, times, status: auditConst.status.checking });
            if (!audit) {
                throw '审核数据错误';
            }
            const sql = 'SELECT `tender_id`, `tr_id`, `td_id`, `aid`, `order` FROM ?? WHERE `td_id` = ? and `times` = ? GROUP BY `aid` ORDER BY `id` ASC';
            const sqlParam = [this.tableName, detailId, 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 userIndex = this._.findIndex(auditors, { aid: detailInfo.uid });
            if (userIndex !== -1) {
                auditors.splice(userIndex, 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 });
                // 清空所有签名数据
                let report_json = JSON.parse(this.ctx.detail.report_json);
                report_json = await this.ctx.service.paymentDetail.clearAllSignatureData(report_json);
                // 同步期信息
                await transaction.update(this.ctx.service.paymentDetail.tableName, {
                    id: detailId, status: checkData.checkType,
                    times: times + 1,
                    report_json: JSON.stringify(report_json),
                });
                // 清空签名
                await transaction.update(this.ctx.service.paymentRptAudit.tableName, {
                    signature_msg: null,
                }, {
                    where: {
                        td_id: detailId,
                    },
                });
                // 拷贝新一次审核流程列表
                await transaction.insert(this.tableName, auditors);

                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async _checkNoPre(pid, detailId, checkData, times) {
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ td_id: detailId, times, status: auditConst.status.checking });
            if (!audit || audit.order <= 1) {
                throw '审核数据错误';
            }
            // 添加重新审批后，不能用order-1,取groupby值里的上一个才对
            const auditors2 = await this.getAuditGroupByList(detailId, times);
            const auditorIndex = await auditors2.findIndex(function(item) {
                return item.aid === audit.aid;
            });
            const preAuditor = auditors2[auditorIndex - 1];
            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time });
                // 顺移气候审核人流程顺序
                this.initSqlBuilder();
                this.sqlBuilder.setAndWhere('td_id', { value: detailId, operate: '=' });
                this.sqlBuilder.setAndWhere('order', { value: audit.order, operate: '>' });
                this.sqlBuilder.setUpdateData('order', { value: 2, selfOperate: '+' });
                const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
                const data = await transaction.query(sql, sqlParam);
                const newAuditors = [];
                newAuditors.push({
                    tender_id: audit.tender_id, tr_id: audit.tr_id, td_id: audit.td_id, aid: preAuditor.aid,
                    times: audit.times, order: audit.order + 1, status: auditConst.status.checking,
                    begin_time: time,
                });
                newAuditors.push({
                    tender_id: audit.tender_id, tr_id: audit.tr_id, td_id: audit.td_id, aid: audit.aid,
                    times: audit.times, order: audit.order + 2, status: auditConst.status.uncheck,
                });
                await transaction.insert(this.tableName, newAuditors);
                // 清除本人的签章
                await this.ctx.service.paymentRptAudit.clearSignatureMsg(transaction, detailId, audit.aid);
                await transaction.commit();
            } catch (error) {
                await transaction.rollback();
                throw error;
            }
        }

        /**
         * 审批
         * @param {Number} materialId - 材料调差期id
         * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果
         * @param {Number} times - 第几次审批
         * @return {Promise<void>}
         */
        async check(detailId, 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, detailId, checkData, times);
                    break;
                case auditConst.status.checkNo:
                    await this._checkNo(pid, detailId, checkData, times);
                    break;
                case auditConst.status.checkNoPre:
                    await this._checkNoPre(pid, detailId, checkData, times);
                    break;
                default:
                    throw '无效审批操作';
            }
        }

        async followAuditByRptAudit(detail) {
            const transaction = await this.db.beginTransaction();
            try {
                const newAuditIds = this._.map(detail.rptAudits, 'uid');
                // 去除上报人
                if (this._.includes(newAuditIds, detail.uid)) {
                    this._.pull(newAuditIds, detail.uid);
                }
                // 如果存在固定终审，判断它是否在报表人员里，存在则把该id移到最后,不存在则插入newAuditIds最后;
                if (this.ctx.trInfo.sp_status === shenpiConst.sp_status.gdzs) {
                    const gdzsInfo = await this.ctx.service.paymentShenpiAudit.getAudit(detail.tender_id, detail.tr_id, shenpiConst.sp_status.gdzs);
                    if (gdzsInfo && gdzsInfo.audit_id !== newAuditIds[newAuditIds.length - 1]) {
                        if (this._.includes(newAuditIds, gdzsInfo.audit_id)) {
                            this._.pull(newAuditIds, gdzsInfo.audit_id);
                        }
                        newAuditIds.push(gdzsInfo.audit_id);
                    }
                }
                // 删除原审批流
                await transaction.delete(this.tableName, { td_id: detail.id, times: detail.times });
                // 添加新审批流
                const insertDatas = [];
                let order = 1;
                for (const id of newAuditIds) {
                    insertDatas.push({
                        tender_id: detail.tender_id,
                        tr_id: detail.tr_id,
                        td_id: detail.id,
                        aid: id,
                        order,
                        times: detail.times,
                        status: auditConst.status.uncheck,
                    });
                    order = order + 1;
                }
                if (insertDatas.length > 0) await transaction.insert(this.tableName, insertDatas);
                await transaction.commit();
                return true;
            } catch (error) {
                await transaction.rollback();
                throw error;
            }
        }
    }

    return PaymentDetailAudit;
};