Calculation/app/service/material_audit.js

'use strict';

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

const auditConst = require('../const/audit').material;
const auditType = require('../const/audit').auditType;
const pushType = require('../const/audit').pushType;
const pushOperate = require('../const/spec_3f').pushOperate;
const smsTypeConst = require('../const/sms_type');
const wxConst = require('../const/wechat_template');
const shenpiConst = require('../const/shenpi');
const materialConst = require('../const/material');


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 Left Join ?? AS pa On la.`aid` = pa.`id`' +
                '  WHERE la.`mid` = ? and la.`aid` = ? and la.`times` = ? ORDER BY la.`order` DESC';
            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, order_sort = 'asc') {
            const sql = 'SELECT la.id, la.aid, la.times, la.order, la.status, la.opinion, la.is_old, la.begin_time, la.end_time, la.audit_type, la.audit_order,' +
                '    pa.name, pa.company, pa.role, pa.mobile, pa.telephone, pa.sign_path' +
                `  FROM ${this.tableName} la LEFT JOIN ${this.ctx.service.projectAccount.tableName} pa ON la.aid = pa.id` +
                '  WHERE la.mid = ? AND la.times = ?' +
                '  ORDER BY la.order ' + order_sort;
            const sqlParam = [materialId, times];
            const result = await this.db.query(sql, sqlParam);
            const max_sort = this._.max(result.map(x => { return x.audit_order; }));
            for (const i in result) {
                result[i].max_sort = max_sort;
            }
            return result;
        }

        async getLastestAuditors(materialId, times, status) {
            const sql =
                'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, la.audit_type, la.audit_order,' +
                '    la.`times`, la.`order`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time` ' +
                '    FROM ' + this.tableName + ' AS la' +
                '    Left Join ' + this.ctx.service.projectAccount.tableName + ' AS pa ON la.`aid` = pa.`id`' +
                '  WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?' +
                '  ORDER BY `order` DESC';
            const sqlParam = [materialId, status, times ? times: 1];
            const result = await this.db.query(sql, sqlParam);
            if (result.length === 0) return [];
            return result.filter(x => { return x.order === result[0].order });
        }

        async getFinalAuditGroup(materialId, times = 1) {
            const sql =
                'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, pa.`mobile`, pa.`telephone`, pa.`sign_path`, la.`times`, la.`mid`, la.`audit_order`, la.`audit_type`, Max(la.`order`) as max_order, GROUP_CONCAT(la.tp_data) as tp_data ' +
                '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                '  WHERE la.`mid` = ? and la.`times` = ? GROUP BY la.`aid` ORDER BY la.`order`';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, times];
            const result = await this.db.query(sql, sqlParam);
            for (const r of result) {
                const auditor = await this.getDataByCondition({mid: materialId, times: r.times, order: r.max_order});
                r.status = auditor.status;
                r.opinion = auditor.opinion;
                r.begin_time = auditor.begin_time;
                r.end_time = auditor.end_time;
            }
            return result;
        }

        /**
         * 获取 当前审核人
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<*>}
         */
        async getCurAuditor(materialId, times = 1) {
            const sql = 'SELECT la.`id`, 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`, la.audit_order ' +
                '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                '  WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checking, times];
            return await this.db.queryOne(sql, sqlParam);
        }

        async getCurAuditors(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`, la.audit_type, la.audit_order ' +
                '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id`' +
                '  WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?';
            const sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, auditConst.status.checking, times];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取 最新审核顺序
         *
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<number>}
         */
        async getNewOrder(materialId, times = 1) {
            const sql = 'SELECT Max(??) As max_order, Max(audit_order) As max_audit_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, result.max_audit_order + 1] : [1, 1];
        }

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

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

            return data;
        }

        /**
         * 移除审核人
         *
         * @param {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 transaction.delete(this.tableName, { mid: materialId, order: auditor.order, times});
                await this._syncOrderByDelete(transaction, materialId, auditor.order, times);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return true;
        }

        async getTpData(transaction, materialId, decimal = (this.ctx.material.decimal ? this.ctx.material.decimal : materialConst.decimal)) {
            const materialInfo = await transaction.get(this.ctx.service.material.tableName, { id: materialId });
            const tp_data = {
                m_tp: materialInfo.m_tp !== null ? this.ctx.helper.round(materialInfo.m_tp, decimal.tp) : null,
                tax_tp: materialInfo.material_tax ? (materialInfo.m_tax_tp ? materialInfo.m_tax_tp : materialInfo.m_tp) :
                    (materialInfo.m_tp !== null ? this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), decimal.tp) : null),
                ex_tp: materialInfo.ex_tp !== null ? this.ctx.helper.round(materialInfo.ex_tp, decimal.tp) : null,
                ex_tax_tp: materialInfo.ex_tp !== null ? this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), decimal.tp) : null,
            }
            tp_data.total_tp = this.ctx.helper.add(tp_data.m_tp, tp_data.ex_tp);
            tp_data.total_tax_tp = !materialInfo.material_tax ? this.ctx.helper.add(tp_data.tax_tp, tp_data.ex_tax_tp) : tp_data.ex_tax_tp;
            if (materialInfo.is_stage_self) {
                const materialStageList = await transaction.select(this.ctx.service.materialStage.tableName, { where: { mid: materialId } });
                const stage_tp = [];
                for (const ms of materialStageList) {
                    stage_tp.push({
                        id: ms.id,
                        sid: ms.sid,
                        order: ms.order,
                        m_tp: this.ctx.helper.round(ms.m_tp, decimal.tp),
                        m_tax_tp: this.ctx.helper.round(ms.m_tax_tp, decimal.tp),
                    });
                }
                tp_data.stage_tp = stage_tp;
            }
            return tp_data;
        }

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

            const transaction = await this.db.beginTransaction();
            try {
                const tp_data = await this.getTpData(transaction, materialId);
                const begin_time = new Date();
                const updateData = audits.map(x => { return { id: x.id, status: auditConst.status.checking, begin_time, tp_data: JSON.stringify(tp_data) } });
                await transaction.updateRows(this.tableName, updateData);
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: auditConst.status.checking, tp_data: JSON.stringify(tp_data),
                });
                // 本期一些必要数据（如应耗数量和上期调差金额）插入到material_bills_history表里
                const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({ where: { tid: this.ctx.tender.id } });
                if (materialBillsData.length === 0 && this.ctx.material.ex_expr === null) {
                    throw '请至少使用一种调差方式';
                }

                // 微信模板通知
                const materialInfo = await this.ctx.service.material.getDataById(materialId);
                const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                const users = this._.map(audits, 'aid');
                const wechatData = {
                    wap_url: shenpiUrl,
                    qi: materialInfo.order,
                    status: wxConst.status.check,
                    tips: wxConst.tips.check,
                    begin_time: Date.parse(new Date()),
                    m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                    hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
                };
                await this.ctx.helper.sendWechat(users, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                for (const audit of audits) {
                    await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                        pid: this.ctx.session.sessionProject.id,
                        tid: this.ctx.tender.id,
                        uid: audit.aid,
                        sp_type: 'material',
                        sp_id: audit.id,
                        table_name: this.tableName,
                        template: wxConst.template.material,
                        wx_data: wechatData,
                    });
                }

                // todo 更新标段tender状态 ?
                // 检查三方特殊推送
                await this.ctx.service.specMsg.addMaterialMsg(transaction, this.ctx.session.sessionProject.id, materialInfo, pushOperate.material.flow);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return true;
        }

        async _checked(pid, materialId, checkData, times) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();

            // 整理当前流程审核人状态更新
            const audits = await this.getAllDataByCondition({ where: { mid: materialId, times, status: auditConst.status.checking } });
            if (audits.length === 0) throw '审核数据错误';
            const selfAudit = audits.find(x => { return x.aid === accountId; });
            if (!selfAudit) throw '当前标段您无权审批';

            const flowAudits = await this.getAllDataByCondition({ where: { mid: materialId, times, order: selfAudit.order } });
            const nextAudits = await this.getAllDataByCondition({ where: { mid: materialId, times, order: selfAudit.order + 1 } });

            const transaction = await this.db.beginTransaction();
            try {
                // 获取当前总金额及独立单价期的金额，添加到tp_data中，报表使用
                const tp_data = await this.getTpData(transaction, materialId);
                await transaction.update(this.tableName, {
                    id: selfAudit.id,
                    status: checkData.checkType,
                    opinion: checkData.opinion,
                    end_time: time,
                    tp_data: JSON.stringify(tp_data),
                });
                await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, selfAudit.id);
                // 获取推送必要信息
                const noticeContent = await this.getNoticeContent(pid, selfAudit.tid, materialId, accountId, checkData.opinion);
                const auditors = await this.getAuditorsWithOwner(materialId, times);
                // 添加推送
                const records = [];
                // const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checked, content: noticeContent }];
                auditors.forEach(audit => {
                    records.push({ pid, tid: selfAudit.tid, type: pushType.material, uid: audit.aid, status: auditConst.status.checked, content: noticeContent });
                });
                await transaction.insert('zh_notice', records);
                const materialInfo = await this.ctx.service.material.getDataById(materialId);
                const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                if (audits.length === 1 || selfAudit.audit_type === auditType.key.or) {
                    // 或签更新他人审批状态
                    if (selfAudit.audit_type === auditType.key.or) {
                        const updateOther = [];
                        for (const audit of audits) {
                            if (audit.aid === selfAudit.aid) continue;
                            updateOther.push({
                                id: audit.id,
                                status: auditConst.status.checkSkip,
                                opinion: '',
                                end_time: time,
                            });
                            await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, audit.id);
                        }
                        if (updateOther.length > 0) transaction.updateRows(this.tableName, updateOther);
                    }
                    // 无下一审核人表示，审核结束
                    if (nextAudits.length > 0) {
                        // 复制一份下一审核人数据
                        // await this.ctx.service.stagePay.copyAuditStagePays(this.ctx.stage, this.ctx.stage.times, nextAudit.order, transaction);
                        // 流程至下一审批人
                        const updateData = nextAudits.map(x => {
                            return {
                                id: x.id,
                                status: auditConst.status.checking,
                                begin_time: time,
                                tp_data: JSON.stringify(tp_data)
                            };
                        });
                        await transaction.updateRows(this.tableName, updateData);

                        // 同步 期信息
                        await transaction.update(this.ctx.service.material.tableName, {
                            id: materialId, status: auditConst.status.checking,
                        });
                        const begin_audit = await this.getDataByCondition({
                            mid: materialId,
                            order: 1,
                        });
                        const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                        // 微信模板通知
                        const wechatData = {
                            wap_url: shenpiUrl,
                            qi: materialInfo.order,
                            status: wxConst.status.check,
                            tips: wxConst.tips.check,
                            begin_time: Date.parse(begin_audit.begin_time),
                            m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                            hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1 + materialInfo.rate / 100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1 + materialInfo.exponent_rate / 100), material_decimal.tp)),
                        };
                        await this.ctx.helper.sendWechat(this._.map(nextAudits, 'aid'), smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                        // 重新发送配置
                        for (const a of nextAudits) {
                            await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                                pid: this.ctx.session.sessionProject.id,
                                tid: this.ctx.tender.id,
                                uid: a.aid,
                                sp_type: 'material',
                                sp_id: a.id,
                                table_name: this.tableName,
                                template: wxConst.template.material,
                                wx_data: wechatData,
                            });
                        }
                        // 检查三方特殊推送
                        await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
                    } else {
                        // 本期结束
                        // 同步 期信息
                        await transaction.update(this.ctx.service.material.tableName, {
                            id: materialId, status: checkData.checkType,
                        });

                        // 处理旧数据，防止重复插入到历史表
                        await transaction.delete(this.ctx.service.materialBillsHistory.tableName, {
                            tid: this.ctx.tender.id,
                            order: this.ctx.material.order
                        });
                        const mbhList = [];
                        const materialBillsData = await this.ctx.service.materialBills.getAllDataByCondition({where: {tid: this.ctx.tender.id}});
                        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,
                                m_tax_tp: mb.m_tax_tp,
                                tax_pre_tp: mb.tax_pre_tp,
                                origin: mb.origin,
                                is_summary: mb.is_summary,
                                m_tax: mb.m_tax,
                            };
                            mbhList.push(newMbh);
                        }
                        if (mbhList.length !== 0) await transaction.insert(this.ctx.service.materialBillsHistory.tableName, mbhList);
                        // 处理旧数据，防止重复插入到历史表
                        await transaction.delete(this.ctx.service.materialExponentHistory.tableName, {
                            tid: this.ctx.tender.id,
                            order: this.ctx.material.order
                        });
                        const materialExponentData = await this.ctx.service.materialExponent.getAllDataByCondition({where: {tid: this.ctx.tender.id}});
                        const mehList = [];
                        for (const me of materialExponentData) {
                            const newMeh = {
                                tid: this.ctx.tender.id,
                                mid: this.ctx.material.id,
                                order: this.ctx.material.order,
                                me_id: me.id,
                                type: me.type,
                                weight_num: me.weight_num,
                                basic_price: me.basic_price,
                                basic_times: me.basic_times,
                                m_price: me.m_price,
                                calc_num: me.calc_num,
                                is_summary: me.is_summary,
                            };
                            mehList.push(newMeh);
                        }
                        if (mehList.length !== 0) await transaction.insert(this.ctx.service.materialExponentHistory.tableName, mehList);

                        // 微信模板通知
                        const begin_audit = await this.getDataByCondition({
                            mid: materialId,
                            order: 1,
                        });
                        const users = this._.uniq(this._.concat(this._.map(auditors, 'aid'), materialInfo.user_id));
                        const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                        const wechatData = {
                            wap_url: shenpiUrl,
                            qi: materialInfo.order,
                            status: wxConst.status.success,
                            tips: wxConst.tips.success,
                            begin_time: Date.parse(begin_audit.begin_time),
                            m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                            hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1 + materialInfo.rate / 100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1 + materialInfo.exponent_rate / 100), material_decimal.tp)),
                        };
                        await this.ctx.helper.sendWechat(users, smsTypeConst.const.TC, smsTypeConst.judge.result.toString(), wxConst.template.material, wechatData);
                        // 检查三方特殊推送
                        await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
                        await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.checked);
                    }
                } else {
                    // 同步 期信息
                    await transaction.update(this.ctx.service.material.tableName, {
                        id: materialId,
                        status: auditConst.status.checking,
                    });
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async _checkNo(pid, materialId, checkData, times) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audits = await this.getAllDataByCondition({ where: { mid: materialId, times, status: auditConst.status.checking } });
            if (!audits) throw '审核数据错误';
            const selfAudit = audits.find(x => { return x.aid === accountId; });
            if (!selfAudit) throw '当前标段您无权审批';
            const flowAudits = await this.getAllDataByCondition({ where: { mid: materialId, times: selfAudit.times, order: selfAudit.order }});

            const auditors = await this.getUniqAuditor(materialId, times); // 全部参与的审批人
            const newAuditors = auditors.map(x => {
                return {
                    aid: x.aid, tid: selfAudit.tid, mid: selfAudit.mid,
                    times: times + 1, order: x.audit_order, status: auditConst.status.uncheck,
                    audit_type: x.audit_type, audit_order: x.audit_order,
                }
            });
            const transaction = await this.db.beginTransaction();
            try {
                const tp_data = await this.getTpData(transaction, materialId);
                const updateData = [];
                audits.forEach(x => {
                    updateData.push({
                        id: x.id,
                        status: x.aid === selfAudit.aid ? checkData.checkType : auditConst.status.checkSkip,
                        opinion: x.aid === selfAudit.aid ? checkData.opinion : '',
                        end_time: x.aid === selfAudit.aid ? time : null,
                        tp_data: JSON.stringify(tp_data),
                    });
                });
                await transaction.updateRows(this.tableName, updateData);
                await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, this._.map(updateData, 'id'));
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, selfAudit.tid, materialId, selfAudit.aid, checkData.opinion);
                const records = [{ pid, tid: selfAudit.tid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNo, content: noticeContent }];
                auditors.forEach(audit => {
                    records.push({ pid, tid: selfAudit.tid, type: pushType.material, uid: audit.aid, status: auditConst.status.checkNo, content: noticeContent });
                });
                await transaction.insert(this.ctx.service.noticePush.tableName, records);
                // 同步期信息
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: checkData.checkType,
                    times: times + 1,
                    tp_data: JSON.stringify(tp_data),
                });
                // 拷贝新一次审核流程列表
                await transaction.insert(this.tableName, newAuditors);

                // 微信模板通知
                const begin_audit = await this.getDataByCondition({
                    mid: materialId,
                    order: 1,
                });
                const materialInfo = await this.ctx.service.material.getDataById(materialId);
                const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                const users = this._.uniq(this._.concat(this._.map(auditors, 'aid'), materialInfo.user_id));
                const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                const wechatData = {
                    wap_url: shenpiUrl,
                    qi: materialInfo.order,
                    status: wxConst.status.back,
                    tips: wxConst.tips.back,
                    begin_time: Date.parse(begin_audit.begin_time),
                    m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                    hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
                };
                await this.ctx.helper.sendWechat(users, smsTypeConst.const.TC, smsTypeConst.judge.result.toString(), wxConst.template.material, wechatData);
                // 检查三方特殊推送
                await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);

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

        async _checkNoPre(pid, materialId, checkData, times) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            // 整理当前流程审核人状态更新
            const audits = await this.getAllDataByCondition({ where: { mid: materialId, times, status: auditConst.status.checking } });
            if (audits.length === 0 || audits[0].order <= 1) throw '审核数据错误';
            const selfAudit = audits.find(x => { return x.aid === accountId; });
            if (!selfAudit) throw '当前标段您无权审批';
            const flowAudits = await this.getAllDataByCondition({ where: { mid: materialId, times: selfAudit.times, order: selfAudit.order }});
            // 添加重新审批后，不能用order-1,取groupby值里的上一个才对
            const auditors2 = await this.getAuditGroupByList(materialId, times);
            const preAuditors = auditors2.filter(x => { return x.audit_order === selfAudit.audit_order - 1});
            const transaction = await this.db.beginTransaction();
            try {
                const tp_data = await this.getTpData(transaction, materialId);
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, selfAudit.tid, materialId, selfAudit.aid, checkData.opinion);
                const records = [{ pid, tid: selfAudit.tid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNoPre, content: noticeContent }];
                auditors2.forEach(audit => {
                    records.push({ pid, tid: selfAudit.tid, type: pushType.material, uid: audit.aid, status: auditConst.status.checkNoPre, content: noticeContent });
                });
                await transaction.insert('zh_notice', records);
                const updateData = [];
                for (const audit of audits) {
                    if (audit.aid === selfAudit.aid) {
                        updateData.push({
                            id: audit.id, status: checkData.checkType, opinion: checkData.opinion, end_time: time, tp_data: JSON.stringify(tp_data)
                        });
                    } else {
                        updateData.push({
                            id: audit.id, status: auditConst.status.checkSkip, opinion: '', end_time: null, tp_data: JSON.stringify(tp_data)
                        });
                    }
                }
                await transaction.updateRows(this.tableName, updateData);
                await this.ctx.service.noticeAgain.stopNoticeAgain(transaction, this.tableName, this._.map(updateData, 'id'));
                // 顺移其后审核人流程顺序
                const sql = 'UPDATE ' + this.tableName + ' SET `order` = `order` + 2 WHERE mid = ? AND times = ? AND `order` > ?';
                await transaction.query(sql, [materialId, selfAudit.times, selfAudit.order]);
                const newAuditors = [];
                preAuditors.forEach(x => {
                    newAuditors.push({
                        tid: selfAudit.tid, mid: materialId, aid: x.aid,
                        times: x.times, order: selfAudit.order + 1,
                        status: auditConst.status.checking, begin_time: time,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                        tp_data: JSON.stringify(tp_data),
                    });
                });
                const newAuditors_result = await transaction.insert(this.tableName, newAuditors);
                // 获取刚批量添加的所有list
                for (let j = 0; j < preAuditors.length; j++) {
                    newAuditors[j].id = newAuditors_result.insertId + j;
                }
                const newFlowAuditors = [];
                flowAudits.forEach(x => {
                    newFlowAuditors.push({
                        tid: selfAudit.tid, mid: materialId, aid: x.aid,
                        times: x.times, order: selfAudit.order + 2,
                        status: auditConst.status.uncheck,
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                await transaction.insert(this.tableName, newFlowAuditors);

                // 微信模板通知
                const begin_audit = await this.getDataByCondition({
                    mid: materialId,
                    order: 1,
                });
                const materialInfo = await this.ctx.service.material.getDataById(materialId);
                const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                const wechatData = {
                    wap_url: shenpiUrl,
                    qi: materialInfo.order,
                    status: wxConst.status.check,
                    tips: wxConst.tips.check,
                    begin_time: Date.parse(begin_audit.begin_time),
                    m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                    hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
                };
                await this.ctx.helper.sendWechat(this._.map(preAuditors, 'aid'), smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                for (const a of newAuditors) {
                    await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                        pid: this.ctx.session.sessionProject.id,
                        tid: this.ctx.tender.id,
                        uid: a.aid,
                        sp_type: 'material',
                        sp_id: a.id,
                        table_name: this.tableName,
                        template: wxConst.template.material,
                        wx_data: wechatData,
                    });
                }
                // 检查三方特殊推送
                await this.ctx.service.specMsg.addMaterialMsg(transaction, pid, materialInfo, pushOperate.material.flow);
                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 '提交数据错误';
            }
            const pid = this.ctx.session.sessionProject.id;
            switch (checkData.checkType) {
                case auditConst.status.checked:
                    await this._checked(pid, materialId, checkData, times);
                    break;
                case auditConst.status.checkNo:
                    await this._checkNo(pid, materialId, checkData, times);
                    break;
                case auditConst.status.checkNoPre:
                    await this._checkNoPre(pid, materialId, checkData, times);
                    break;
                default:
                    throw '无效审批操作';
            }
        }

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

        /**
         * 审批
         * @param {Number} materialId - 材料调差期id
         * @param {Number} times - 第几次审批
         * @return {Promise<void>}
         */
        async checkAgain(materialId, times = 1) {
            const accountId = this.ctx.session.sessionUser.accountId;
            const time = new Date();
            // 整理当前流程审核人状态更新
            const auditors = await this.getAllDataByCondition({
                where: { mid: materialId, times },
                orders: [['order', 'asc']],
            });
            const maxOrder = auditors[auditors.length - 1].order;
            const audits = auditors.filter(x => { return x.order === maxOrder});
            if (!audits || audits.length === 0 || maxOrder < 1) throw '审核数据错误';
            const selfAudit = audits.find(x => { return x.aid === accountId; });
            if (!selfAudit) throw '当前标段您无权审批';
            const finalAudit = selfAudit || audits[0];
            const transaction = await this.db.beginTransaction();
            try {
                const materialInfo = await this.ctx.service.material.getDataById(materialId);
                const material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                const tp_data = await this.getTpData(transaction, materialId, material_decimal);
                const checkAgainAuditors = [], checkingAuditors = [];
                audits.forEach(x => {
                    checkAgainAuditors.push({
                        tid: x.tid, mid: x.mid, aid: x.aid,
                        times: x.times, order: x.order + 1,
                        status: !selfAudit || x.aid === selfAudit.aid ? auditConst.status.checkAgain : auditConst.status.checkSkip,
                        begin_time: time, end_time: time, opinion: '',
                        audit_type: x.audit_type, audit_order: x.audit_order,
                    });
                });
                audits.forEach(x => {
                    checkingAuditors.push({
                        tid: x.tid, mid: x.mid, aid: x.aid,
                        times: x.times, order: x.order + 2,
                        status: auditConst.status.checking,
                        begin_time: time, end_time: time, opinion: '',
                        audit_type: x.audit_type, audit_order: x.audit_order,
                        tp_data: JSON.stringify(tp_data),
                    });
                });
                await transaction.insert(this.tableName, checkAgainAuditors);
                const checkingAuditors_result = await transaction.insert(this.tableName, checkingAuditors);
                // 获取刚批量添加的所有list
                for (let j = 0; j < checkingAuditors.length; j++) {
                    checkingAuditors[j].id = checkingAuditors_result.insertId + j;
                }

                // 本期结束
                // 同步 期信息
                await transaction.update(this.ctx.service.material.tableName, {
                    id: materialId, status: auditConst.status.checking,
                    final_auditor_str: '',
                });

                // 微信模板通知
                const shenpiUrl = await this.ctx.helper.urlToShort(this.ctx.protocol + '://' + this.ctx.host + '/wap/tender/' + this.ctx.tender.id + '/measure/material/' + materialInfo.order);
                const wechatData = {
                    wap_url: shenpiUrl,
                    qi: materialInfo.order,
                    status: wxConst.status.check,
                    tips: wxConst.tips.check,
                    begin_time: Date.parse(new Date()),
                    m_tp: this.ctx.helper.add(this.ctx.helper.round(materialInfo.m_tp, material_decimal.tp), this.ctx.helper.round(materialInfo.ex_tp, material_decimal.tp)),
                    hs_m_tp: this.ctx.helper.add(this.ctx.helper.round(this.ctx.helper.mul(materialInfo.m_tp, 1+materialInfo.rate/100), material_decimal.tp), this.ctx.helper.round(this.ctx.helper.mul(materialInfo.ex_tp, 1+materialInfo.exponent_rate/100), material_decimal.tp)),
                };
                await this.ctx.helper.sendWechat(this._.map(checkingAuditors, 'aid'), smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                for (const a of checkingAuditors) {
                    await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                        pid: this.ctx.session.sessionProject.id,
                        tid: this.ctx.tender.id,
                        uid: a.aid,
                        sp_type: 'material',
                        sp_id: a.id,
                        table_name: this.tableName,
                        template: wxConst.template.material,
                        wx_data: wechatData,
                    });
                }
                // 检查三方特殊推送
                await this.ctx.service.specMsg.addMaterialMsg(transaction, this.ctx.session.sessionProject.id, materialInfo, pushOperate.material.flow);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 获取审核人需要审核的期列表
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getAuditMaterial(auditorId, spid = '') {
            const spSql = spid ? ' and t.`spid` = "' + spid + '"' : '';
            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`, t.`spid` ' +
                '  FROM ?? AS ma' +
                '  LEFT JOIN ?? AS m On ma.mid = m.id' +
                '  LEFT JOIN ?? As t On m.tid = t.id' +
                '  WHERE ((ma.`aid` = ? and ma.`status` = ?) OR (m.`user_id` = ? and ma.`status` = ? and m.`status` = ? and ma.`times` = (m.`times`-1)))' + spSql +
                '  ORDER BY ma.`begin_time` DESC';
            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];
            const result = await this.db.query(sql, sqlParam);
            // 过滤result中存在重复sid的值, 保留最新的一条
            const filterResult = [];
            const midArr = [];
            for (const r of result) {
                if (midArr.indexOf(r.mid) === -1) {
                    filterResult.push(r);
                    midArr.push(r.mid);
                }
            }
            return filterResult;
        }

        /**
         * 获取审核人审核的次数
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getCountByChecked(auditorId, spid = '') {
            if (spid) {
                const sql = 'SELECT count(*) AS count FROM ?? AS a LEFT JOIN ?? AS t ON a.tid = t.id WHERE a.`aid` = ? AND a.`status` in (' + this.ctx.helper.getInArrStrSqlFilter([auditConst.status.checked, auditConst.status.checkNo, auditConst.status.checkNoPre]) + ') AND t.`spid` = ?';
                const sqlParam = [this.tableName, this.ctx.service.tender.tableName, auditorId, spid];
                const result = await this.db.queryOne(sql, sqlParam);
                return result.count ? result.count : 0;
            }
            return await this.db.count(this.tableName, { aid: auditorId, status: [auditConst.status.checked, auditConst.status.checkNo, auditConst.status.checkNoPre] });
        }

        /**
         * 获取最近一次审批结束时间
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getLastEndTimeByChecked(auditorId, spid = '') {
            const sqSql = spid ? ' AND t.`spid` = "' + spid + '"' : '';
            const sql = 'SELECT a.`end_time` FROM ?? AS a LEFT JOIN ?? AS t ON a.`tid` = t.`id` WHERE a.`aid` = ? ' +
                'AND a.`status` in (' + this.ctx.helper.getInArrStrSqlFilter([auditConst.status.checked, auditConst.status.checkNo]) + ')' + sqSql +
                ' ORDER BY a.`end_time` DESC';
            const sqlParam = [this.tableName, this.ctx.service.tender.tableName, auditorId];
            const result = await this.db.queryOne(sql, sqlParam);
            return result ? result.end_time : null;
        }

        /**
         * 获取 某时间后 审批进度 更新的期
         * @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);
            let notice = await this.db.select('zh_notice', {
                where: { pid, type: pushType.material, uid },
                orders: [['create_time', 'desc']],
                limit: 10, offset: 0,
            });
            notice = notice.map(v => {
                const extra = JSON.parse(v.content);
                delete v.content;
                return { ...v, ...extra };
            });
            return notice;
        }

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

        /**
         * 获取审核人流程列表(包括原报)
         * @param {Number} materialId 调差id
         * @param {Number} times 审核次数
         * @return {Promise<Array>} 查询结果集(包括原报)
         */
        async getAuditorsWithOwner(materialId, times = 1) {
            const result = await this.getAuditGroupByList(materialId, 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.material.tableName +
                ' As s' +
                '  LEFT JOIN ' +
                this.ctx.service.projectAccount.tableName +
                ' As pa' +
                '  ON s.user_id = pa.id' +
                '  WHERE s.id = ?';
            const sqlParam = [times, materialId, materialId];
            const user = await this.db.queryOne(sql, sqlParam);
            result.unshift(user);
            return result;
        }

        /**
         * 获取tender下所有的审核列表（报表用）
         * @param {Number} tenderId
         */
        async getAuditorsByTender(tenderId) {
            const sql = 'SELECT la.`tid`, la.`mid`, la.`aid`, la.`order`, la.`times`, la.`status`, la.`opinion`, la.`begin_time`, la.`end_time`' +
                '  FROM ?? AS la ' +
                '  WHERE la.`tid` = ?';
            const sqlParam = [this.tableName, tenderId];
            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: a.audit_order,
                    status: auditConst.status.uncheck,
                    audit_type: a.audit_type,
                    audit_order: a.audit_order,
                };
                newAuditors.push(na);
            }
            if (newAuditors.length > 0) {
                const result = await transaction.insert(this.tableName, newAuditors);
                return (result.effectRows = auditors.length);
            } else {
                return true;
            }
        }

        /**
         * 移除审核人
         *
         * @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 Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                        '  WHERE la.`mid` = ? and la.`status` = ? ' +
                        '  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 Left Join ?? AS pa On la.`aid` = pa.`id`' +
                        '  WHERE la.`mid` = ? and la.`status` = ? and la.`times` = ?' +
                        '  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 getAuditorsByStatus(materialId, status, times = 1) {
            let auditor = [];
            let sql = '';
            let sqlParam = '';
            let cur;
            switch (status) {
                case auditConst.status.checking:
                case auditConst.status.checked:
                case auditConst.status.checkNoPre:
                    cur = await this.db.queryOne(`SELECT * From ${this.tableName} where mid = ? AND times = ? AND status = ? ORDER By times DESC, ` + '`order` DESC', [materialId, times, status]);
                    if (!cur) return [];

                    sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`order`, la.`status`, la.audit_order, la.audit_type ' +
                        '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                        '  WHERE la.`mid` = ? and la.`order` = ? and times = ?';
                    sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, cur.order, times];
                    auditor = await this.db.query(sql, sqlParam);
                    break;
                case auditConst.status.checkNo:
                    cur = await this.db.queryOne(`SELECT * From ${this.tableName} where mid = ? AND times = ? AND status = ? ORDER By times DESC, ` + '`order` DESC', [materialId, parseInt(times) - 1, status]);
                    if (!cur) return [];

                    sql = 'SELECT la.`aid`, pa.`name`, pa.`company`, pa.`role`, la.`times`, la.`mid`, la.`order`, la.`status`, la.audit_order, la.audit_type ' +
                        '  FROM ?? AS la Left Join ?? AS pa On la.`aid` = pa.`id` ' +
                        '  WHERE la.`mid` = ? and la.`order` = ? and la.`times` = ?';
                    sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, materialId, cur.order, parseInt(times) - 1];
                    auditor = await this.db.query(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);
        }

        /**
         * 取待审批期列表(wap用)
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getAuditMaterialByWap(auditorId) {
            const sql =
                'SELECT sa.`aid`, sa.`times`, sa.`begin_time`, sa.`end_time`, sa.`tid`, sa.`mid`,' +
                // '    s.`order` As `sorder`, s.`status` As `sstatus`, s.`s_time`, s.`contract_tp`, s.`qc_tp`, s.`pre_contract_tp`, s.`pre_qc_tp`, s.`yf_tp`, s.`pre_yf_tp`, ' +
                '    s.*,' +
                '    t.`name`, t.`project_id`, t.`type`, t.`user_id`, t.`spid`,' +
                '    ti.`deal_info` ' +
                '  FROM ?? AS sa' +
                '    Left Join ?? AS s On sa.`mid` = s.`id`' +
                '    Left Join ?? As t On sa.`tid` = t.`id`' +
                '    Left Join ?? AS ti ON ti.`tid` = t.`id`' +
                '  WHERE sa.`aid` = ? and sa.`status` = ?';
            const sqlParam = [
                this.tableName,
                this.ctx.service.material.tableName,
                this.ctx.service.tender.tableName,
                this.ctx.service.tenderInfo.tableName,
                auditorId,
                auditConst.status.checking,
            ];
            return await this.db.query(sql, sqlParam);
        }

        async updateNewAuditList(material, newList) {
            const transaction = await this.db.beginTransaction();
            try {
                // 先删除旧的审批流，再添加新的
                await transaction.delete(this.tableName, { mid: material.id, times: material.times });
                const newAuditors = [];
                for (const auditor of newList) {
                    newAuditors.push({
                        tid: material.tid, mid: material.id, aid: auditor.audit_id,
                        times: material.times, order: auditor.audit_order, status: auditConst.status.uncheck,
                        audit_type: auditor.audit_type, audit_order: auditor.audit_order,
                    });
                }
                if(newAuditors.length > 0) await transaction.insert(this.tableName, newAuditors);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async updateLastAudit(material, auditList, lastId) {
            const transaction = await this.db.beginTransaction();
            try {
                // 先判断auditList里的aid是否与lastId相同，相同则删除并重新更新order
                const existAudit = auditList.find(x => { return x.aid === lastId });
                let order = auditList.length > 0 ? auditList.reduce((rst, a) => { return Math.max(rst, a.order)}, 0) + 1 : 1; // 最大值 + 1
                // const idList = this._.map(auditList, 'aid');
                // let order = idList.length + 1;
                if (existAudit) {
                    await transaction.delete(this.tableName, {mid: material.id, times: material.times, aid: lastId});
                    const sameOrder = auditList.filter(x => { return x.order === existAudit.order });
                    if (sameOrder.length === 1) {
                        const updateData = [];
                        auditList.forEach(x => {
                            if (x.order <= existAudit.order) return;
                            updateData.push({id: x.id, order: x.order - 1, audit_order: x.audit_order - 1});
                        });
                        if (updateData.length > 0) {
                            await transaction.updateRows(updateData);
                        }
                        order = order - 1;
                    }
                }

                // 添加终审
                const newAuditor = {
                    tid: material.tid, mid: material.id, aid: lastId,
                    times: material.times, order, status: auditConst.status.uncheck,
                    audit_type: auditType.key.common, audit_order: order,
                };
                await transaction.insert(this.tableName, newAuditor);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async getNumByMonth(tid, startMonth, endMonth) {
            const sql = 'SELECT COUNT(*) as num FROM ?? t1 JOIN (SELECT MAX(id) as max_id FROM ?? WHERE tid = ? GROUP BY mid) t2 ON t1.id = t2.max_id WHERE t1.status = ? AND t1.end_time between ? and ?';
            // const sql = 'SELECT COUNT(*) as num FROM ?? WHERE id in (SELECT MAX(id) FROM ?? WHERE tid = ? GROUP BY mid) AND status = ? AND end_time between ? and ?';
            const sqlParam = [this.tableName, this.tableName, tid, auditConst.status.checked, startMonth, endMonth];
            const result = await this.db.queryOne(sql, sqlParam);
            return result ? result.num : 0;
        }

        /**
         * 删除本次审批流程
         * @param {Number} materialId - 调差id
         * @param {Number} times - 第几次审批
         * @param {Object} data - 更改参数
         * @return {Promise<void>}
         */
        async saveAudit(materialId, times, sp_group, data) {
            const transaction = await this.db.beginTransaction();
            try {
                const auditors = await this.getAuditGroupByList(materialId, times);
                const now_audit = this._.find(auditors, { aid: data.old_aid });
                if (data.operate !== 'del') {
                    const exist = await this.getDataByCondition({ mid: materialId, times, aid: data.new_aid, is_old: 0 });
                    if (exist) throw '该审核人已存在，请勿重复添加';
                }
                if (data.operate === 'add') {
                    if (now_audit.status !== auditConst.status.uncheck && now_audit.status !== auditConst.status.checking) {
                        throw '当前人下无法操作新增';
                    }
                    const newAudit = {
                        tid: this.ctx.tender.id,
                        mid: materialId,
                        aid: data.new_aid,
                        order: now_audit.order + 1,
                        audit_order: now_audit.audit_order + 1,
                        audit_type: auditType.key.common,
                        times: times,
                        status: 1
                    };
                    // order+1
                    await this._syncOrderByDelete(transaction, materialId, now_audit.order+1, times, '+');
                    await transaction.insert(this.tableName, newAudit);
                    // 更新审批流程页数据，如果存在
                } else if (data.operate === 'add-sibling') {
                    if (now_audit.status !== auditConst.status.uncheck && now_audit.status !== auditConst.status.checking) {
                        throw '当前人下无法操作新增';
                    }
                    const newAudit = {
                        tid: this.ctx.tender.id,
                        mid: materialId,
                        aid: data.new_aid,
                        order: now_audit.order,
                        audit_order: now_audit.audit_order,
                        audit_type: now_audit.audit_type,
                        times: times,
                        status: 1
                    };
                    await transaction.insert(this.tableName, newAudit);
                } else if (data.operate === 'del') {
                    if (now_audit.status !== auditConst.status.uncheck) {
                        throw '当前人无法操作删除';
                    }
                    const flowAuditors = auditors.filter(x => { return x.order === now_audit.order; });
                    await transaction.delete(this.tableName, { mid: materialId, times, aid: now_audit.aid, order: now_audit.order });
                    if (flowAuditors.length === 1) await this._syncOrderByDelete(transaction, materialId, now_audit.order, times);
                    // 旧的更新为is_old为1
                    await transaction.update(this.tableName, { is_old: 1 }, {
                        where: {
                            mid: materialId,
                            times,
                            aid: data.old_aid,
                        }
                    });
                } else if (data.operate === 'change') {
                    const nowAudit = await this.getDataByCondition({ mid: materialId, times, aid: now_audit.aid, order: now_audit.order });
                    if (now_audit.status !== auditConst.status.uncheck || !nowAudit) {
                        throw '当前人无法操作替换';
                    }
                    nowAudit.aid = data.new_aid;
                    await transaction.update(this.tableName, nowAudit);
                    // 旧的更新为is_old为1
                    await transaction.update(this.tableName, { is_old: 1 }, {
                        where: {
                            mid: materialId,
                            times,
                            aid: data.old_aid,
                        }
                    });
                }
                if (this.ctx.tender.info.shenpi.material === shenpiConst.sp_status.gdspl) {
                    const newAuditors = await transaction.select(this.tableName, { where: { mid: materialId, times } });
                    const newAuditorGroup = this.ctx.helper.groupAuditors(newAuditors.filter(x => { return x.is_old === 0; }));
                    const uniqNewAuditorGroup = this.ctx.helper.groupAuditorsUniq(newAuditorGroup);
                    await this.ctx.service.shenpiAudit.updateAuditListWithAuditType(transaction, this.ctx.tender.id, this.ctx.tender.info.shenpi.material, shenpiConst.sp_type.material, uniqNewAuditorGroup, sp_group);
                } else if (this.ctx.tender.info.shenpi.material === shenpiConst.sp_status.gdzs) {
                    const newAuditors = await this.getAuditGroupByList(materialId, times, transaction);
                    await this.ctx.service.shenpiAudit.updateAuditList(transaction, this.ctx.tender.id, this.ctx.tender.info.shenpi.material, shenpiConst.sp_type.material, this._.map(newAuditors, 'aid'));
                }
                // 更新到审批流程方法
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async getAuditorGroup(materialId, times) {
            const auditors = await this.getAuditors(materialId, times); // 全部参与的审批人
            const newAuditors = auditors.filter(x => { return x.is_old === 0; });
            return this.ctx.helper.groupAuditors(newAuditors);
        }

        async getUserGroup(materialId, times) {
            const group = await this.getAuditorGroup(materialId, times);
            const sql =
                'SELECT pa.`id` As aid, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As mid, 0 As `order`, 1 As audit_type, 0 As audit_order' +
                '  FROM ' + this.ctx.service.material.tableName + ' As m' +
                '  LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa' +
                '  ON m.user_id = pa.id' +
                '  WHERE m.id = ?';
            const sqlParam = [times, materialId, materialId];
            const user = await this.db.queryOne(sql, sqlParam);
            group.unshift([ user ]);
            return group;
        }

        async getUniqUserGroup(materialId, times) {
            const group = await this.getAuditorGroup(materialId, times);
            const sql =
                'SELECT pa.`id` As aid, pa.`name`, pa.`company`, pa.`role`, ? As times, ? As mid, 0 As `order`, 1 As audit_type, 0 As audit_order' +
                '  FROM ' + this.ctx.service.material.tableName + ' As m' +
                '  LEFT JOIN ' + this.ctx.service.projectAccount.tableName + ' As pa' +
                '  ON m.user_id = pa.id' +
                '  WHERE m.id = ?';
            const sqlParam = [times, materialId, materialId];
            const user = await this.db.queryOne(sql, sqlParam);
            user.audit_order = 0;
            group.unshift([ user ]);
            return this.ctx.helper.groupAuditorsUniq(group);
        }

        async getAuditorHistory(materialId, times, reverse = false) {
            const history = [];
            if (times >= 1) {
                for (let i = 1; i <= times; i++) {
                    const auditors = await this.getAuditors(materialId, i);
                    const group = this.ctx.helper.groupAuditors(auditors);
                    const historyGroup = [];
                    const max_order = group.length > 0 && group[group.length - 1].length > 0 ? group[group.length - 1][0].audit_order : -1;
                    for (const g of group) {
                        const his = {
                            beginYear: '', beginDate: '', beginTime: '', endYear: '', endDate: '', endTime: '', begin_time: null, end_time: null,
                            audit_type: g[0].audit_type, audit_order: g[0].audit_order,
                            auditors: g
                        };
                        if (his.audit_type === auditType.key.common) {
                            his.name = g[0].name;
                        } else {
                            his.name = this.ctx.helper.transFormToChinese(his.audit_order) + '审';
                        }
                        his.is_final = his.audit_order === max_order;
                        if (g[0].begin_time) {
                            his.begin_time = g[0].begin_time;
                            const beginTime = this.ctx.moment(g[0].begin_time);
                            his.beginYear = beginTime.format('YYYY');
                            his.beginDate = beginTime.format('MM-DD');
                            his.beginTime = beginTime.format('HH:mm:ss');
                        }
                        let end_time;
                        g.forEach(x => {
                            if (x.status === auditConst.status.checkSkip) return;
                            if (!his.status || x.status === auditConst.status.checking) his.status = x.status;
                            if (x.end_time && (!end_time || x.end_time > end_time)) {
                                end_time = x.end_time;
                                if (his.status !== auditConst.status.checking) his.status = x.status;
                            }
                        });
                        if (end_time) {
                            his.end_time = end_time;
                            const endTime = this.ctx.moment(end_time);
                            his.endYear = endTime.format('YYYY');
                            his.endDate = endTime.format('MM-DD');
                            his.endTime = endTime.format('HH:mm:ss');
                        }
                        historyGroup.push(his);
                    }
                    if (reverse) {
                        history.push(historyGroup.reverse());
                    } else {
                        history.push(historyGroup);
                    }
                }
            }
            return history;
        }

        async getUniqAuditor(materialId, times) {
            const auditors = await this.getAuditors(materialId, times); // 全部参与的审批人
            const result = [];
            auditors.forEach(x => {
                if (result.findIndex(r => { return x.aid === r.aid && x.audit_order === r.audit_order; }) < 0) {
                    result.push(x);
                }
            });
            return result;
        }

        /**
         * 获取审核人已经审核过的审批信息（包括退回，通过，重新审批等）
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getDonesByAudit(auditorId, spid = '') {
            const spSql = spid ? ' and t.`spid` = "' + spid + '"' : '';
            const status = [auditConst.status.checked, auditConst.status.checkNo, auditConst.status.checkNoPre, auditConst.status.checkAgain];
            const sql =
                'SELECT la.`status`, la.`end_time` as `shenpi_time`, la.`mid`, t.`id`, m.`order` As `morder`, t.`name`, t.`spid` ' +
                '  FROM ?? AS la Left Join ?? AS t ON la.`tid` = t.`id` LEFT JOIN ?? AS m ON la.`mid` = m.`id`' +
                '  WHERE la.`aid` = ? AND la.`status` in (' + this.ctx.helper.getInArrStrSqlFilter(status) +')' + spSql +
                '  ORDER BY la.`end_time` DESC';
            const sqlParam = [
                this.tableName,
                this.ctx.service.tender.tableName,
                this.ctx.service.material.tableName,
                auditorId,
            ];
            return await this.db.query(sql, sqlParam);
        }
    }

    return MaterialAudit;
};