Calculation/app/service/material_audit.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date 2019/2/27
 * @version
 */

const auditConst = require('../const/audit').material;
const materialConst = require('../const/material');
// const smsTypeConst = require('../const/sms_type');
const SMS = require('../lib/sms');

module.exports = app => {
    class MaterialAudit extends app.BaseService {
        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'material_audit';
        }

        /**
         * 获取 审核人信息
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} auditorId - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>}
         */
        async getAuditor(materialId, auditorId, times = 1) {
            const sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
                'FROM ?? AS la, ?? AS pa ' +
                'WHERE la.`mid` = ? and la.`aid` = ? and la.`times` = ?' +
                '    and la.`aid` = pa.`id`';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditorId, times];
            return await this.db.queryOne(sql, sqlParam);
        }

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

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

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

        /**
         * 新增审核人
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} auditorId - 审核人id
         * @param {Number} times - 第几次审批
         * @return {Promise<number>}
         */
        async addAuditor(materialId, auditorId, times = 1) {
            const newOrder = await this.getNewOrder(materialId, times);
            const data = {
                tid: this.ctx.tender.id,
                mid: materialId,
                aid: auditorId,
                times,
                order: newOrder,
                status: auditConst.status.uncheck,
            };
            const result = await this.db.insert(this.tableName, data);
            return result.effectRows = 1;
        }

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

            return data;
        }

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

        /**
         * 开始审批
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<boolean>}
         */
        async start(materialId, times = 1) {
            const audit = await this.getDataByCondition({ mid: materialId, times, order: 1 });
            if (!audit) {
                throw '请先选择审批人，再上报数据';
            }

            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, { id: audit.id, status: auditConst.status.checking, begin_time: new Date() });
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: auditConst.status.checking,
                });
                // 添加记录至推送表
                await transaction.insert('zh_notice', { type: 'material', uid: audit.aid, status: auditConst.status.checking, is_read: 0, content: '待审核' });
                console.log('1111');
                // 本期一些必要数据（如应耗数量和上期调差金额）插入到material_bills_history表里
                const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                if (materialBillsData.length === 0) {
                    throw '调差工料不能为空';
                }
                const mbhList = [];
                for (const mb of materialBillsData) {
                    if (mb.code === '') {
                        throw '调差工料编号不能为空';
                    }
                    const newMbh = {
                        tid: this.ctx.tender.id,
                        mid: this.ctx.material.id,
                        order: this.ctx.material.order,
                        mb_id: mb.id,
                        quantity: mb.quantity,
                        expr: mb.expr,
                        msg_tp: mb.msg_tp,
                        msg_times: mb.msg_times,
                        msg_spread: mb.msg_spread,
                        m_up_risk: mb.m_up_risk,
                        m_down_risk: mb.m_down_risk,
                        m_spread: mb.m_spread,
                        m_tp: mb.m_tp,
                        pre_tp: mb.pre_tp,
                    };
                    mbhList.push(newMbh);
                }
                await transaction.insert(this.ctx.service.materialBillsHistory.tableName, mbhList);

                // 添加短信通知-需要审批提醒功能
                // const smsUser = await this.ctx.service.projectAccount.getDataById(audit.aid);
                // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
                //     const smsType = JSON.parse(smsUser.sms_type);
                //     if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) {
                //         const tenderInfo = await this.ctx.service.tender.getDataById(audit.tid);
                //         const stageInfo = await this.ctx.service.stage.getDataById(audit.sid);
                //         const sms = new SMS(this.ctx);
                //         const tenderName = await sms.contentChange(tenderInfo.name);
                //         const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期，需要您审批。';
                //         sms.send(smsUser.auth_mobile, content);
                //     }
                // }

                // todo 更新标段tender状态 ?
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return true;
        }

        async _checked(materialId, checkData, times) {
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking });
            if (!audit) {
                throw '审核数据错误';
            }
            const nextAudit = await this.getDataByCondition({ mid: materialId, times, order: audit.order + 1 });

            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time });
                // 无下一审核人表示，审核结束
                if (nextAudit) {
                    // 复制一份下一审核人数据
                    // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, nextAudit.order, transaction);
                    // 流程至下一审批人
                    await transaction.update(this.tableName, { id: nextAudit.id, status: auditConst.status.checking, begin_time: time });
                    // 同步 期信息
                    await transaction.update(this.ctx.service.material.tableName, {
                        id: materialId, status: auditConst.status.checking,
                    });

                    // 添加短信通知-需要审批提醒功能
                    // const smsUser = await this.ctx.service.projectAccount.getDataById(nextAudit.aid);
                    // if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
                    //     const smsType = JSON.parse(smsUser.sms_type);
                    //     if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) {
                    //         const tenderInfo = await this.ctx.service.tender.getDataById(nextAudit.tid);
                    //         const stageInfo = await this.ctx.service.stage.getDataById(nextAudit.sid);
                    //         const sms = new SMS(this.ctx);
                    //         const tenderName = await sms.contentChange(tenderInfo.name);
                    //         const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期，需要您审批。';
                    //         sms.send(smsUser.auth_mobile, content);
                    //     }
                    // }
                } else {
                    // 本期结束
                    // 生成截止本期数据 final数据
                    // console.time('generatePre');
                    // await this.ctx.service.stageBillsFinal.generateFinalData(transaction, this.ctx.tender, this.ctx.stage);
                    // await this.ctx.service.stagePosFinal.generateFinalData(transaction, this.ctx.tender, this.ctx.stage);
                    // console.timeEnd('generatePre');
                    // 同步 期信息
                    await transaction.update(this.ctx.service.material.tableName, {
                        id: materialId, status: checkData.checkType,
                    });

                    // 添加短信通知-审批通过提醒功能
                    // const mobile_array = [];
                    // const stageInfo = await this.ctx.service.stage.getDataById(stageId);
                    // const auditList = await this.getAuditors(stageId, stageInfo.times);
                    // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id);
                    // if (smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') {
                    //     const smsType = JSON.parse(smsUser1.sms_type);
                    //     if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
                    //         mobile_array.push(smsUser1.auth_mobile);
                    //     }
                    // }
                    // for (const user of auditList) {
                    //     const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid);
                    //     if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
                    //         const smsType = JSON.parse(smsUser.sms_type);
                    //         if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
                    //             mobile_array.push(smsUser.auth_mobile);
                    //         }
                    //     }
                    // }
                    // if (mobile_array.length > 0) {
                    //     const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid);
                    //     const sms = new SMS(this.ctx);
                    //     const tenderName = await sms.contentChange(tenderInfo.name);
                    //     const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期，审批通过。';
                    //     sms.send(mobile_array, content);
                    // }
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async _checkNo(materialId, checkData, times) {
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audit = await this.getDataByCondition({ mid: materialId, times, status: auditConst.status.checking });
            if (!audit) {
                throw '审核数据错误';
            }
            const sql = 'SELECT `tid`, `mid`, `aid`, `order` FROM ?? WHERE `mid` = ? and `times` = ? GROUP BY `aid` ORDER BY `id` ASC';
            const sqlParam = [this.tableName, materialId, times];
            const auditors = await this.db.query(sql, sqlParam);
            let order = 1;
            for (const a of auditors) {
                a.times = times + 1;
                a.order = order;
                a.status = auditConst.status.uncheck;
                order++;
            }

            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, { id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time });
                // 同步 期信息
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: checkData.checkType,
                    times: times + 1,
                });
                // 拷贝新一次审核流程列表
                await transaction.insert(this.tableName, auditors);
                // 删除material_bills_history信息
                await transaction.delete(this.ctx.service.materialBillsHistory.tableName, {
                    tid: this.ctx.tender.id,
                    order: this.ctx.material.order,
                });

                // 计算该审批人最终数据
                // await this.ctx.service.stagePay.calcAllStagePays(this.ctx.stage, transaction);
                // 复制一份最新数据给原报
                // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times + 1, 0, transaction);

                // 添加短信通知-审批退回提醒功能
                // const mobile_array = [];
                // const stageInfo = await this.ctx.service.stage.getDataById(stageId);
                // const auditList = await this.getAuditors(stageId, stageInfo.times);
                // const smsUser1 = await this.ctx.service.projectAccount.getDataById(stageInfo.user_id);
                // if (smsUser1.auth_mobile !== '' && smsUser1.auth_mobile !== undefined && smsUser1.sms_type !== '') {
                //     const smsType = JSON.parse(smsUser1.sms_type);
                //     if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
                //         mobile_array.push(smsUser1.auth_mobile);
                //     }
                // }
                // for (const user of auditList) {
                //     const smsUser = await this.ctx.service.projectAccount.getDataById(user.aid);
                //     if (smsUser.auth_mobile !== '' && smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
                //         const smsType = JSON.parse(smsUser.sms_type);
                //         if (mobile_array.indexOf(smsUser.auth_mobile) === -1 && smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.result.toString()) !== -1) {
                //             mobile_array.push(smsUser.auth_mobile);
                //         }
                //     }
                // }
                // if (mobile_array.length > 0) {
                //     const tenderInfo = await this.ctx.service.tender.getDataById(stageInfo.tid);
                //     const sms = new SMS(this.ctx);
                //     const tenderName = await sms.contentChange(tenderInfo.name);
                //     const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期，审批退回。';
                //     sms.send(mobile_array, content);
                // }

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

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

        /**
         * 审批
         * @param {Number} materialId - 材料调差期id
         * @param {auditConst.status.checked|auditConst.status.checkNo} checkType - 审批结果
         * @param {Number} times - 第几次审批
         * @return {Promise<void>}
         */
        async check(materialId, checkData, times = 1) {
            if (checkData.checkType !== auditConst.status.checked && checkData.checkType !== auditConst.status.checkNo && checkData.checkType !== auditConst.status.checkNoPre) {
                throw '提交数据错误';
            }
            switch (checkData.checkType) {
                case auditConst.status.checked:
                    await this._checked(materialId, checkData, times);
                    break;
                case auditConst.status.checkNo:
                    await this._checkNo(materialId, checkData, times);
                    break;
                case auditConst.status.checkNoPre:
                    await this._checkNoPre(materialId, checkData, times);
                    break;
                default:
                    throw '无效审批操作';
            }
        }

        /**
         * 审批
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<void>}
         */
        async checkAgain(materialId, times = 1) {
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audit = (await this.getAllDataByCondition({ where: { mid: materialId, times }, orders: [['order', 'desc']], limit: 1, offset: 0 }))[0];
            if (!audit || audit.order <= 1) {
                throw '审核数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 当前审批人2次添加至流程中
                const newAuditors = [];
                newAuditors.push({
                    tid: audit.tid, mid: audit.mid, aid: audit.aid,
                    times: audit.times, order: audit.order + 1, status: auditConst.status.checkAgain,
                    begin_time: time, end_time: time, opinion: '',
                });
                newAuditors.push({
                    tid: audit.tid, mid: audit.mid, aid: audit.aid,
                    times: audit.times, order: audit.order + 2, status: auditConst.status.checking,
                    begin_time: time,
                });
                await transaction.insert(this.tableName, newAuditors);

                // 复制一份最新数据给下一人
                // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, audit.order + 2, transaction);

                // 本期结束
                // 生成截止本期数据 final数据
                // await this.ctx.service.stageBillsFinal.delGenerateFinalData(transaction, this.ctx.tender, this.ctx.stage);
                // await this.ctx.service.stagePosFinal.delGenerateFinalData(transaction, this.ctx.tender, this.ctx.stage);
                // 同步 期信息
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: auditConst.status.checking,
                });

                // // 添加短信通知-需要审批提醒功能
                // const smsUser = await this.ctx.service.projectAccount.getDataById(audit.aid);
                // if (smsUser.auth_mobile !== undefined && smsUser.sms_type !== '') {
                //     const smsType = JSON.parse(smsUser.sms_type);
                //     if (smsType[smsTypeConst.const.JL] !== undefined && smsType[smsTypeConst.const.JL].indexOf(smsTypeConst.judge.approval.toString()) !== -1) {
                //         const tenderInfo = await this.ctx.service.tender.getDataById(audit.tid);
                //         const stageInfo = await this.ctx.service.stage.getDataById(audit.sid);
                //         const sms = new SMS(this.ctx);
                //         const tenderName = await sms.contentChange(tenderInfo.name);
                //         const content = '【纵横计量支付】' + tenderName + '第' + stageInfo.order + '期，需要您审批。';
                //         sms.send(smsUser.auth_mobile, content);
                //     }
                // }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 获取审核人需要审核的期列表
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getAuditMaterial(auditorId) {
            const sql = 'SELECT ma.`aid`, ma.`times`, ma.`order`, ma.`begin_time`, ma.`end_time`, ma.`tid`, ma.`mid`,' +
                        '    m.`order` As `morder`, m.`status` As `mstatus`,' +
                        '    t.`name`, t.`project_id`, t.`type`, t.`user_id` ' +
                        '  FROM ?? AS ma, ?? AS m, ?? As t ' +
                        '  WHERE ((ma.`aid` = ? and ma.`status` = ?) OR (m.`user_id` = ? and ma.`status` = ? and m.`status` = ? and ma.`times` = (m.`times`-1)))' +
                        '    and ma.`mid` = m.`id` and ma.`tid` = t.`id`';
            const sqlParam = [this.tableName, this.ctx.service.material.tableName, this.ctx.service.tender.tableName, auditorId, auditConst.status.checking, auditorId, auditConst.status.checkNo, auditConst.status.checkNo];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取 某时间后 审批进度 更新的期
         * @param {Number} pid - 查询标段
         * @param {Number} uid - 查询人
         * @param {Date} time - 查询时间
         * @return {Promise<*>}
         */
        async getNoticeMaterial(pid, uid, time) {
            const sql = 'SELECT * FROM (SELECT t.`name`, t.`project_id`, t.`type`, t.`user_id`, ' +
                '    m.`order` As `m_order`, m.`status` As `m_status`, ' +
                '    ma.`aid`, ma.`times`, ma.`order`, ma.`end_time`, ma.`tid`, ma.`mid`, ma.`status`, ' +
                '    pa.`name` As `su_name`, pa.role As `su_role`, pa.company As `su_company`' +
                '  FROM (SELECT * FROM ?? WHERE `user_id` = ? OR `id` in (SELECT `tid` FROM ?? WHERE `aid` = ? GROUP BY `tid`)) As t' +
                '  LEFT JOIN ?? As m On t.`id` = m.`tid`' +
                '  LEFT JOIN ?? As ma ON m.`id` = ma.`mid`' +
                '  LEFT JOIN ?? As pa ON ma.`aid` = pa.`id`' +
                '  WHERE ma.`end_time` > ? and t.`project_id` = ?' +
                '  ORDER By ma.`end_time` DESC LIMIT 1000) as new_t GROUP BY new_t.`tid`' +
                '  ORDER BY new_t.`end_time`';
            const sqlParam = [this.ctx.service.tender.tableName, uid, this.tableName, uid, this.ctx.service.material.tableName, this.tableName,
                this.ctx.service.projectAccount.tableName, time, pid];
            return await this.db.query(sql, sqlParam);
        }

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

        /**
         * 复制上一期的审批人列表给最新一期
         *
         * @param transaction - 新增一期的事务
         * @param {Object} preMaterial - 上一期
         * @param {Object} newaMaterial - 最新一期
         * @return {Promise<*>}
         */
        async copyPreMaterialAuditors(transaction, preMaterial, newMaterial) {
            const auditors = await this.getAuditGroupByList(preMaterial.id, preMaterial.times);
            const newAuditors = [];
            for (const a of auditors) {
                const na = {
                    tid: preMaterial.tid,
                    mid: newMaterial.id,
                    aid: a.aid,
                    times: newMaterial.times,
                    order: newAuditors.length + 1,
                    status: auditConst.status.uncheck,
                };
                newAuditors.push(na);
            }
            const result = await transaction.insert(this.tableName, newAuditors);
            return result.affectedRows === auditors.length;
        }

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

        async getAllAuditors(tenderId) {
            const sql = 'SELECT ma.aid, ma.tid FROM ' + this.tableName + ' ma' +
                '  LEFT JOIN ' + this.ctx.service.tender.tableName + ' t On ma.tid = t.id' +
                '  WHERE t.id = ?' +
                '  GROUP BY  ma.aid';
            const sqlParam = [tenderId];
            return this.db.query(sql, sqlParam);
        }
    }

    return MaterialAudit;
};