Calculation/app/service/material.js

'use strict';

/**
 * 期计量 数据模型
 *
 * @author Mai
 * @date 2018/8/13
 * @version
 */

const auditConst = require('../const/audit').material;
const auditType = require('../const/audit').auditType;
const projectLogConst = require('../const/project_log');
const materialConst = require('../const/material');
module.exports = app => {
    class Material extends app.BaseService {
        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'material';
        }

        async loadMaterialUser(material) {
            const status = auditConst.status;
            const accountId = this.ctx.session.sessionUser.accountId;

            material.user = await this.ctx.service.projectAccount.getAccountInfoById(material.user_id);
            material.auditors = await this.ctx.service.materialAudit.getAuditors(material.id, material.times); // 全部参与的审批人
            material.auditorIds = this._.map(material.auditors, 'aid');
            material.curAuditors = material.auditors.filter(x => { return x.status === status.checking; }); // 当前流程中审批中的审批人
            material.curAuditorIds = this._.map(material.curAuditors, 'aid');
            material.flowAuditors = material.curAuditors.length > 0 ? material.auditors.filter(x => { return x.order === material.curAuditors[0].order; }) : []; // 当前流程中参与的审批人（包含会签时，审批通过的人）
            material.flowAuditorIds = this._.map(material.flowAuditors, 'aid');
            material.nextAuditors = material.curAuditors.length > 0 ? material.auditors.filter(x => { return x.order === material.curAuditors[0].order + 1; }) : [];
            material.nextAuditorIds = this._.map(material.nextAuditors, 'aid');
            material.auditorGroups = this.ctx.helper.groupAuditors(material.auditors);
            material.userGroups = this.ctx.helper.groupAuditorsUniq(material.auditorGroups);
            material.userGroups.unshift([{
                aid: material.user.id, order: 0, times: material.times, audit_order: 0, audit_type: auditType.key.common,
                name: material.user.name, role: material.user.role, company: material.user.company,
            }]);
            material.finalAuditorIds = material.userGroups[material.userGroups.length - 1].map(x => { return x.aid; });
        }

        async loadMaterialAuditViewData(material) {
            const times = material.status === auditConst.status.checkNo ? material.times - 1 : material.times;

            if (!material.user) material.user = await this.ctx.service.projectAccount.getAccountInfoById(material.user_id);
            material.auditHistory = await this.ctx.service.materialAudit.getAuditorHistory(material.id, times);
            // 获取审批流程中左边列表
            if (material.status === auditConst.status.checkNo && material.user_id !== this.ctx.session.sessionUser.accountId) {
                const auditors = await this.ctx.service.materialAudit.getAuditors(material.id, times); // 全部参与的审批人
                const auditorGroups = this.ctx.helper.groupAuditors(auditors);
                material.auditors2 = this.ctx.helper.groupAuditorsUniq(auditorGroups);
                material.auditors2.unshift([{
                    aid: material.user.id, order: 0, times: material.times - 1, audit_order: 0, audit_type: auditType.key.common,
                    name: material.user.name, role: material.user.role, company: material.user.company,
                }]);
            } else {
                material.auditors2 = material.userGroups;
            }
            if (material.status === auditConst.status.uncheck || material.status === auditConst.status.checkNo) {
                material.auditorList = await this.ctx.service.materialAudit.getAuditors(material.id, material.times);
            }
        }

        /**
         * 获取 最新一期 材料调差期计量
         * @param tenderId
         * @param includeUnCheck
         * @return {Promise<*>}
         */
        async getLastestMaterial(tenderId, includeUnCheck = false) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tenderId,
                operate: '=',
            });
            if (!includeUnCheck) {
                this.sqlBuilder.setAndWhere('status', {
                    value: auditConst.status.uncheck,
                    operate: '!=',
                });
            }
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const material = await this.db.queryOne(sql, sqlParam);
            return material;
        }

        /**
         * 获取 最新一期 审批完成的 材料调差期计量
         * @param tenderId
         * @return {Promise<*>}
         */
        async getLastestCompleteMaterial(tenderId) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tid', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('status', {
                value: auditConst.status.checked,
                operate: '=',
            });
            this.sqlBuilder.orderBy = [['order', 'desc']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const material = await this.db.queryOne(sql, sqlParam);
            return material;
        }

        async checkMaterial(tid, order) {
            if (this.ctx.material) return;
            const materials = await this.getSelectMaterial(tid, order);
            this.ctx.material = materials[0];
            if (this.ctx.session.sessionUser.accountId === this.ctx.material.user_id) {
                this.ctx.material.curTimes = this.ctx.material.times;
            } else {
                this.ctx.material.curTimes = this.ctx.material.status === auditConst.status.checkNo ? this.ctx.material.times - 1 : this.ctx.material.times;
            }
        }

        /**
         * 获取标段下的全部计量期，按倒序
         * @param tenderId
         * @return {Promise<void>}
         */
        async getSelectMaterial(tenderId, order) {
            const materials = await this.db.select(this.tableName, {
                where: { tid: tenderId, order },
            });
            if (materials.length > 0 && materials[0].status !== auditConst.status.checked) {
                const material = materials[0];
                const curAuditor = await this.ctx.service.materialAudit.getCurAuditor(material.id, material.times);
                const isActive = curAuditor ? curAuditor.aid === this.ctx.session.sessionUser.accountId : material.user_id === this.ctx.session.sessionUser.accountId;
                if (isActive) {
                    material.curTimes = material.times;
                    material.curOrder = curAuditor ? curAuditor.order : 0;
                }
            }
            return materials;
        }

        async getValidMaterials(tenderId) {
            const materials = await this.db.select(this.tableName, {
                where: { tid: tenderId },
                orders: [['order', 'desc']],
            });
            if (materials.length !== 0) {
                const lastMaterial = materials[materials.length - 1];
                if (lastMaterial.status === auditConst.status.uncheck && lastMaterial.user_id !== this.ctx.session.sessionUser.accountId && !this.ctx.tender.isTourist && !this.ctx.session.sessionUser.is_admin) {
                    materials.splice(materials.length - 1, 1);
                }
            }
            // 最新一期计量（未审批完成），当前操作人的期详细数据，应实时计算
            if (materials.length > 0 && materials[0].status !== auditConst.status.checked) {
                const material = materials[0];
                const curAuditors = await this.ctx.service.materialAudit.getCurAuditors(material.id, material.times);
                const isActive = curAuditors && curAuditors.length > 0 ? this._.findIndex(curAuditors, { aid: this.ctx.session.sessionUser.accountId }) !== -1 : material.user_id === this.ctx.session.sessionUser.accountId;
                if (isActive) {
                    material.curTimes = material.times;
                    material.curOrder = curAuditors && curAuditors.length > 0 ? curAuditors[0].order : 0;
                }
            }
            return materials;
        }

        /**
         * 添加材料调差期
         * @param tenderId - 标段id
         * @param data - post的数据
         * @return {Promise<void>}
         */
        async addMaterial(tenderId, data) {
            const materials = await this.getAllDataByCondition({
                where: { tid: tenderId },
                order: ['order'],
            });
            const preMaterial = materials[materials.length - 1];
            if (materials.length > 0 && materials[materials.length - 1].status !== auditConst.status.checked) {
                throw '上一期未审批通过，请等待上一期审批通过后，再新增数据';
            }
            const order = materials.length + 1;
            const newMaterial = {
                tid: tenderId,
                order,
                period: data.period,
                in_time: new Date(),
                times: 1,
                status: auditConst.status.uncheck,
                user_id: this.ctx.session.sessionUser.accountId,
                stage_id: data.stage_id.join(','),
                s_order: data.s_order,
                material_tax: this.ctx.session.sessionProject.page_show.openMaterialTax,
                decimal: preMaterial && preMaterial.decimal ? preMaterial.decimal : JSON.stringify(materialConst.decimal),
                is_new: 1,
                is_stage_self: data.is_stage_self,
                qty_source: data.qty_source,
                is_new_qty: 1,
                rate: 9,
            };
            const transaction = await this.db.beginTransaction();
            try {
                if (preMaterial) {
                    newMaterial.rate = preMaterial.rate;
                    newMaterial.exponent_rate = preMaterial.exponent_rate;
                    newMaterial.pre_tp = this.ctx.helper.add(preMaterial.m_tp, preMaterial.pre_tp);
                    newMaterial.ex_pre_tp = this.ctx.helper.add(preMaterial.ex_tp, preMaterial.ex_pre_tp);
                    newMaterial.m_tax_pre_tp = preMaterial.material_tax ? this.ctx.helper.add(preMaterial.m_tax_tp, preMaterial.m_tax_pre_tp) : preMaterial.m_tax_pre_tp;
                }
                // 新增期记录
                const result = await transaction.insert(this.tableName, newMaterial);
                if (result.affectedRows === 1) {
                    newMaterial.id = result.insertId;
                } else {
                    throw '新增期数据失败';
                }
                const insertMaterialStage = [];
                if (data.is_stage_self) {
                    // 新建多期单独单价表，工料表
                    for (const [i, d] of data.stage_id.entries()) {
                        insertMaterialStage.push({
                            tid: tenderId,
                            mid: newMaterial.id,
                            sid: d,
                            order: data.s_order.split(',')[i],
                        });
                    }
                    if (insertMaterialStage.length > 0) {
                        const resultStage = await transaction.insert(this.ctx.service.materialStage.tableName, insertMaterialStage);
                        // 获取刚批量添加的所有list
                        for (let j = 0; j < insertMaterialStage.length; j++) {
                            insertMaterialStage[j].id = resultStage.insertId + j;
                        }
                    }
                }
                // 存在上一期时，复制上一期审批流程、不参与调差的清单、上期清单并算本期有效价差，本期应耗数量，并算本期总金额
                if (preMaterial) {
                    const auditResult = await this.ctx.service.materialAudit.copyPreMaterialAuditors(transaction, preMaterial, newMaterial);
                    if (!auditResult) {
                        throw '复制上一期审批流程失败';
                    }
                    // 复制不参与调差或不计算数量变更清单
                    const preNotJoinList = await this.ctx.service.materialListNotjoin.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: preMaterial.id } });
                    await this.ctx.service.materialListNotjoin.copyNewStageNotJoinList(transaction, preNotJoinList, newMaterial.id);
                    // 复制单独计量调差清单
                    const preSelfList = await this.ctx.service.materialListSelf.getAllDataByCondition({ where: { tid: this.ctx.tender.id, mid: preMaterial.id } });
                    await this.ctx.service.materialListSelf.copyNewStageSelfList(transaction, preSelfList, newMaterial.id);
                    // 复制调差清单工料关联表
                    // await this.ctx.service.materialList.copyPreMaterialList(transaction, preMaterial, newMaterial);
                    await this.ctx.service.materialList.copyPreMaterialList2(transaction, data.material_list, data.material_self_list, preNotJoinList, newMaterial, insertMaterialStage);
                    // 新增或删除list_gcl表
                    await this.ctx.service.materialListGcl.insertOrDelGcl(transaction, data.insertGclList, data.removeGclList, newMaterial.id);
                    // 设置list_gcl表old=>new更新
                    await this.ctx.service.materialListGcl.setNewOldData(transaction, this.ctx.tender.id);
                    // 修改本期应耗数量值和有效价差,需要剔除不参与调差的清单数据,并返回总金额
                    let m_tp = null;
                    let m_tax_tp = null;
                    let rate_tp = null;
                    if (data.is_stage_self) {
                        [m_tp, m_tax_tp, rate_tp] = await this.ctx.service.materialStageBills.insertBills(transaction, this.ctx.tender.id, newMaterial.id, newMaterial.stage_id, insertMaterialStage, JSON.parse(newMaterial.decimal), preMaterial.is_stage_self, data.qty_source, newMaterial.rate);
                    } else {
                        [m_tp, m_tax_tp, rate_tp] = await this.ctx.service.materialBills.updateNewMaterial(transaction, this.ctx.tender.id, newMaterial.id, this.ctx, newMaterial.stage_id, JSON.parse(newMaterial.decimal), preMaterial.is_stage_self, data.qty_source, newMaterial.rate);
                    }
                    // 修改现行价格指数,并返回调差基数json
                    const ex_calc = await this.ctx.service.materialExponent.updateNewMaterial(transaction, newMaterial.id, this.ctx, newMaterial.stage_id, preMaterial.ex_calc, JSON.parse(newMaterial.decimal));
                    // 计算得出本期总金额
                    // 找出当前人并更新tp_data
                    const tp_data = await this.ctx.service.materialAudit.getTpData(transaction, newMaterial.id, JSON.parse(newMaterial.decimal));
                    const updateMaterialData = {
                        id: newMaterial.id,
                        m_tp,
                        m_tax_tp,
                        rate_tp,
                        ex_calc: JSON.stringify(ex_calc),
                        tp_data: JSON.stringify(tp_data),
                    };
                    await transaction.update(this.tableName, updateMaterialData);
                    // 删除material_list表冗余数据，减少表数据量
                    await transaction.delete(this.ctx.service.materialList.tableName, { tid: this.ctx.tender.id, gather_qty: null, is_self: 0 });
                }

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

        /**
         * 编辑计量期
         *
         * @param {Number} mid - 第N期
         * @param {String} period - 开始-截止时间
         * @return {Promise<void>}
         */
        async saveMaterial(mid, period) {
            await this.db.update(this.tableName, {
                period,
            }, { where: { id: mid } });
        }

        /**
         * 删除材料调差期
         *
         * @param {Number} id - 期Id
         * @return {Promise<void>}
         */
        async deleteMaterial(id) {
            const transaction = await this.db.beginTransaction();
            try {
                // 删除文件
                const attList = await this.ctx.service.materialFile.getAllMaterialFiles(this.ctx.tender.id, id);
                await this.ctx.helper.delFiles(attList);
                await transaction.delete(this.ctx.service.materialAudit.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialBills.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialList.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialListGcl.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialListSelf.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialListNotjoin.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialBillsHistory.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialFile.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialExponent.tableName, { mid: id });
                await transaction.delete(this.ctx.service.materialExponentHistory.tableName, { mid: id });
                // 如果存在上一期，把上一期的quantity,expr,msg_tp,msg_times,msg_spread,m_up_risk,m_down_risk,m_spread,m_tp,pre_tp,orgin,is_summary添加到bill中
                const materialInfo = await this.getDataById(id);
                if (materialInfo.order > 1) {
                    const sql = 'UPDATE ' + this.ctx.service.materialBills.tableName + ' as mb, ' +
                        this.ctx.service.materialBillsHistory.tableName + ' as mbh ' +
                        'SET mb.`quantity` = mbh.`quantity`, mb.`expr` = mbh.`expr`, ' +
                        'mb.`msg_tp` = mbh.`msg_tp`, mb.`msg_times` = mbh.`msg_times`, ' +
                        'mb.`msg_spread` = mbh.`msg_spread`, mb.`m_up_risk` = mbh.`m_up_risk`, ' +
                        'mb.`m_down_risk` = mbh.`m_down_risk`, mb.`m_spread` = mbh.`m_spread`, ' +
                        'mb.`m_tp` = mbh.`m_tp`, mb.`pre_tp` = mbh.`pre_tp`, ' +
                        'mb.`m_tax_tp` = mbh.`m_tax_tp`, mb.`tax_pre_tp` = mbh.`tax_pre_tp`, ' +
                        'mb.`origin` = mbh.`origin`, mb.`is_summary` = mbh.`is_summary`, mb.`m_tax` = mbh.`m_tax` ' +
                        'WHERE mbh.`tid` = ? AND mbh.`order` = ? AND mbh.`mb_id` = mb.`id`';
                    const sqlParam = [this.ctx.tender.id, materialInfo.order - 1];
                    await transaction.query(sql, sqlParam);

                    const sql2 = 'UPDATE ' + this.ctx.service.materialExponent.tableName + ' as me, ' +
                        this.ctx.service.materialExponentHistory.tableName + ' as meh ' +
                        'SET me.`weight_num` = meh.`weight_num`, me.`basic_price` = meh.`basic_price`, ' +
                        'me.`basic_times` = meh.`basic_times`, me.`m_price` = meh.`m_price`, ' +
                        'me.`calc_num` = meh.`calc_num`, me.`is_summary` = meh.`is_summary` ' +
                        'WHERE meh.`tid` = ? AND meh.`order` = ? AND meh.`me_id` = me.`id`';
                    const sqlParam2 = [this.ctx.tender.id, materialInfo.order - 1];
                    await transaction.query(sql2, sqlParam2);
                }
                // 设置list_gcl表old => new更新
                await this.ctx.service.materialListGcl.setNewOldData(transaction, this.ctx.tender.id, 'old2new');
                // 还要从material_list表更新gcl的old数据，更新方法
                await this.ctx.service.materialListGcl.setOldFromLast(transaction, this.ctx.tender.id, materialInfo.order - 2);
                if (materialInfo.is_stage_self) {
                    await transaction.delete(this.ctx.service.materialStage.tableName, { mid: id });
                    await transaction.delete(this.ctx.service.materialStageBills.tableName, { mid: id });
                }
                await transaction.delete(this.tableName, { id });
                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(transaction, projectLogConst.type.material, projectLogConst.status.delete, '第' + materialInfo.order + '期');
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 获取包含当前期之前的调差期id
         *
         * @param {Number} id - 期Id
         * @return {Promise<void>}
         */
        async getPreMidList(tid, order) {
            const midList = await this.getAllDataByCondition({
                where: { tid },
                columns: ['id'],
                limit: order,
                offset: 0,
            });
            const list = [];
            for (const ml of midList) {
                list.push(ml.id);
            }
            return list;
        }

        /**
         * 修改增税税率
         * @param {int} rate 税率
         * @return {Promise<*>}
         */
        async changeRate(rate) {
            const updateData = {
                id: this.ctx.material.id,
                rate,
            };
            return await this.db.update(this.tableName, updateData);
        }

        /**
         * 修改增税税率
         * @param {int} rate 税率
         * @return {Promise<*>}
         */
        async changeExponentRate(rate) {
            const updateData = {
                id: this.ctx.material.id,
                exponent_rate: rate,
            };
            return await this.db.update(this.tableName, updateData);
        }

        /**
         * 修改调差基数
         * @param {int} rate 税率
         * @return {Promise<*>}
         */
        async changeExCalc(ex_calc) {
            const transaction = await this.db.beginTransaction();
            try {
                const updateData = {
                    id: this.ctx.material.id,
                    ex_calc: JSON.stringify(ex_calc),
                };
                await transaction.update(this.tableName, updateData);
                const [ex_tp, ex_expr] = await this.ctx.service.materialExponent.calcMaterialExTp(transaction, ex_calc);
                await transaction.commit();
                return [ex_tp, ex_expr];
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 取当前期截止上期含建筑税金额
         * @param {int} tid 标段id
         * @param {int} order 调差期数
         * @return {Promise<*>}
         */
        async getPreTpHs(tid, order, tp) {
            const sql = 'SELECT SUM(`rate_tp`) AS `pre_tp_hs` FROM ?? WHERE `tid` = ? AND `material_tax` = ? AND `order` < ?';
            const sqlParam = [this.tableName, tid, 0, order];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.pre_tp_hs;
        }

        /**
         * 取当前期截止上期含材料税金额
         * @param {int} tid 标段id
         * @param {int} order 调差期数
         * @return {Promise<*>}
         */
        async getTaxPreTpHs(tid, order) {
            const sql = 'SELECT SUM(`m_tax_tp`) AS `tax_pre_tp_hs` FROM ?? WHERE `tid` = ? AND `material_tax` = ? AND `order` < ?';
            const sqlParam = [this.tableName, tid, 1, order];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.tax_pre_tp_hs;
        }

        /**
         * 取当前期截止上期含建筑税指数金额
         * @param {int} tid 标段id
         * @param {int} order 调差期数
         * @return {Promise<*>}
         */
        async getExPreTpHs(tid, order, tp) {
            const sql = 'SELECT SUM(ROUND(`ex_tp`*(1+ `exponent_rate`/100),' + tp + ')) AS `ex_pre_tp_hs` FROM ?? WHERE `tid` = ? AND `order` < ?';
            const sqlParam = [this.tableName, tid, order];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.ex_pre_tp_hs;
        }

        async updateMaterialTax(id, mtax) {
            const updateData = {
                id,
                material_tax: mtax,
            };
            return await this.db.update(this.tableName, updateData);
        }

        async getMaterialTaxTp(id) {
            const info = await this.getDataById(id);
            return info.m_tax_tp;
        }

        async getSumMaterial(tid) {
            const sql = 'Select sum(IFNULL(m_tp, 0) + IFNULL(ex_tp, 0)) as tp From ' + this.tableName + ' where tid = ?';
            const result = await this.db.queryOne(sql, [tid]);
            return result ? result.tp : 0;
        }

        async getOldMaterialTax(tid, order) {
            const sql = 'SELECT COUNT(id) as count FROM ?? WHERE `tid` = ? AND `order` <= ? AND `material_tax` = 1';
            const sqlParam = [this.tableName, tid, order];
            const result = await this.db.queryOne(sql, sqlParam);
            return result && result.count !== 0;
        }

        async saveDecimal(newUp, newTp, newQty) {
            const transaction = await this.db.beginTransaction();
            try {
                await this.ctx.service.materialBills.resetDecimal(transaction, newUp, newTp, newQty);
                this.ctx.material.decimal.up = newUp;
                this.ctx.material.decimal.tp = newTp;
                this.ctx.material.decimal.qty = newQty;
                const m_tp = await this.ctx.service.materialBills.calcMaterialMTp(transaction);
                let update_calc = false;
                if (this.ctx.material.ex_calc) {
                    const ex_calc = JSON.parse(this.ctx.material.ex_calc);
                    const zdy = this._.find(ex_calc, { code: 'zdy' });
                    zdy.value = this.ctx.helper.round(zdy.value, newTp);
                    this.ctx.material.ex_calc = JSON.stringify(ex_calc);
                    update_calc = true;
                }
                const [ex_tp, ex_expr] = await this.ctx.service.materialExponent.calcMaterialExTp(transaction);
                const updateData = {
                    id: this.ctx.material.id,
                    decimal: JSON.stringify(this.ctx.material.decimal),
                };
                if (update_calc) updateData.ex_calc = this.ctx.material.ex_calc;
                await transaction.update(this.tableName, updateData);
                await transaction.commit();
                return true;
            } catch (err) {
                console.log(err);
                await transaction.rollback();
                return false;
            }
        }

        async saveQtySource(newQtySource) {
            const transaction = await this.db.beginTransaction();
            try {
                await this.ctx.service.materialBills.resetQuantityByQtySource(transaction, newQtySource);
                this.ctx.material.qty_source = newQtySource;
                const m_tp = await this.ctx.service.materialBills.calcMaterialMTp(transaction);
                const updateData = {
                    id: this.ctx.material.id,
                    qty_source: newQtySource,
                };
                await transaction.update(this.tableName, updateData);
                await transaction.commit();
                return true;
            } catch (err) {
                console.log(err);
                await transaction.rollback();
                return false;
            }
        }

        async getListByArchives(tid, ids) {
            if (ids.length === 0) return [];
            const sql = 'SELECT c.* FROM ?? as c LEFT JOIN (SELECT mid, MAX(end_time) as end_time FROM ?? WHERE ' +
                'tid = ? AND mid in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY mid) as ca ON c.id = ca.mid WHERE' +
                ' c.tid = ? AND c.id in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') AND c.status = ? ORDER BY ca.end_time DESC';
            const params = [this.tableName, this.ctx.service.materialAudit.tableName, tid, tid, auditConst.status.checked];
            const list = await this.db.query(sql, params);
            return list;
        }
    }

    return Material;
};