Calculation/app/service/material_audit.js

'use strict';

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

const auditConst = require('../const/audit').material;
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` = ?';
            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 t1.`aid`,(@i:=@i+1) as `sort` FROM (SELECT t.`aid`, t.`order` FROM (select `aid`, `order` from ?? WHERE `mid` = ? AND `times` = ? ORDER BY `order` LIMIT 200) t GROUP BY t.`aid` ORDER BY t.`order`) t1, (select @i:=0) as it) 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;
        }

        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`, 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` ' +
                '  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);
        }

        /**
         * 获取 最新审核顺序
         *
         * @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, is_gdzs = 0) {
            const transaction = await this.db.beginTransaction();
            try {
                let newOrder = await this.getNewOrder(materialId, times);
                // 判断是否存在固定终审，存在则newOrder - 1并使终审order+1
                newOrder = is_gdzs === 1 ? newOrder - 1 : newOrder;
                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,
                };
                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,
            });
            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;
        }

        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 audit = await this.getDataByCondition({ mid: materialId, times, order: 1 });
            if (!audit) {
                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);
                await transaction.update(this.tableName, { id: audit.id, status: auditConst.status.checking, begin_time: new Date(), tp_data: JSON.stringify(tp_data) });
                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 material_decimal = materialInfo && materialInfo.decimal ? JSON.parse(materialInfo.decimal) : materialConst.decimal;
                const wechatData = {
                    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(audit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                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 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);

            const nextAudit = await this.getDataByCondition({ mid: materialId, times, order: audit.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: audit.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, audit.id);
                // 获取推送必要信息
                const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
                // 添加推送
                const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checked, content: noticeContent }];
                auditors.forEach(audit => {
                    records.push({ pid, type: pushType.material, uid: audit.aid, status: auditConst.status.checked, content: noticeContent });
                });
                await transaction.insert('zh_notice', records);
                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;

                // 无下一审核人表示，审核结束
                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, tp_data: JSON.stringify(tp_data) });

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

                    // 微信模板通知
                    const wechatData = {
                        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(nextAudit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                    await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                        pid: this.ctx.session.sessionProject.id,
                        tid: this.ctx.tender.id,
                        uid: nextAudit.aid,
                        sp_type: 'material',
                        sp_id: nextAudit.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 users = this._.uniq(this._.concat(this._.map(auditors, 'aid'), materialInfo.user_id));
                    const wechatData = {
                        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);

                    // 添加短信通知-审批通过提醒功能
                    // 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(pid, 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 {
                const tp_data = await this.getTpData(transaction, materialId);
                await transaction.update(this.tableName, { id: audit.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, audit.id);
                // 添加到消息推送表
                const noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
                const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNo, content: noticeContent }];
                auditors.forEach(audit => {
                    records.push({ pid, 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, auditors);
                // // 删除material_bills_history信息
                // await transaction.delete(this.ctx.service.materialBillsHistory.tableName, {
                //     tid: this.ctx.tender.id,
                //     order: this.ctx.material.order,
                // });
                // // 删除material_exponent_history信息
                // await transaction.delete(this.ctx.service.materialExponentHistory.tableName, {
                //     tid: this.ctx.tender.id,
                //     order: this.ctx.material.order,
                // });

                // 微信模板通知
                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 wechatData = {
                    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);

                // 添加短信通知-审批退回提醒功能
                // 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(pid, 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 noticeContent = await this.getNoticeContent(pid, audit.tid, materialId, audit.aid, checkData.opinion);
            const transaction = await this.db.beginTransaction();
            try {
                const tp_data = await this.getTpData(transaction, materialId);
                // 添加到消息推送表
                const records = [{ pid, type: pushType.material, uid: this.ctx.material.user_id, status: auditConst.status.checkNoPre, content: noticeContent }];
                auditors2.forEach(audit => {
                    records.push({ pid, type: pushType.material, uid: audit.aid, status: auditConst.status.checkNoPre, content: noticeContent });
                });
                await transaction.insert('zh_notice', records);
                await transaction.update(this.tableName, { id: audit.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, audit.id);
                // 顺移气候审核人流程顺序
                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 = {
                    tid: audit.tid, mid: audit.mid, aid: preAuditor.aid,
                    times: audit.times, order: audit.order + 1, status: auditConst.status.checking,
                    begin_time: time,
                    tp_data: JSON.stringify(tp_data),
                };
                const checking_result = await transaction.insert(this.tableName, newAuditors);
                const uncheckNewAuditors = {
                    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, uncheckNewAuditors);

                // 微信模板通知
                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 wechatData = {
                    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(preAuditor.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                await this.ctx.service.noticeAgain.addNoticeAgain(transaction, smsTypeConst.const.TC, {
                    pid: this.ctx.session.sessionProject.id,
                    tid: this.ctx.tender.id,
                    uid: preAuditor.aid,
                    sp_type: 'material',
                    sp_id: checking_result.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 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 {
                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);
                // 当前审批人2次添加至流程中
                const newAuditors = {
                    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: '',
                };
                await transaction.insert(this.tableName, newAuditors);
                const checkingNewAuditors = {
                    tid: audit.tid, mid: audit.mid, aid: audit.aid,
                    times: audit.times, order: audit.order + 2, status: auditConst.status.checking,
                    begin_time: time,
                    tp_data: JSON.stringify(tp_data),
                };
                const checking_result = await transaction.insert(this.tableName, checkingNewAuditors);

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

                // 微信模板通知
                const wechatData = {
                    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(audit.aid, smsTypeConst.const.TC, smsTypeConst.judge.approval.toString(), wxConst.template.material, wechatData);
                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: checking_result.insertId,
                    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) {
            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` 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];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取审核人审核的次数
         *
         * @param auditorId
         * @return {Promise<*>}
         */
        async getCountByChecked(auditorId) {
            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) {
            const sql = 'SELECT `end_time` FROM ?? WHERE `aid` = ? ' +
                'AND `status` in (' + this.ctx.helper.getInArrStrSqlFilter([auditConst.status.checked, auditConst.status.checkNo]) + ') ORDER BY `end_time` DESC';
            const sqlParam = [this.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.`mid`, la.`aid`, la.`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];
            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;
        }

        /**
         * 复制上一期的审批人列表给最新一期
         *
         * @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 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 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);
        }

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

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

                // 添加终审
                const newAuditor = {
                    tid: material.tid, mid: material.id, aid: lastId,
                    times: material.times, order, status: auditConst.status.uncheck,
                };
                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;
        }
    }

    return MaterialAudit;
};