Calculation/app/service/change_apply.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date 2018/8/14
 * @version
 */

const audit = require('../const/audit').changeApply;
const auditType = require('../const/audit').auditType;
// const smsTypeConst = require('../const/sms_type');
// const SMS = require('../lib/sms');
// const SmsAliConst = require('../const/sms_alitemplate');
const wxConst = require('../const/wechat_template');
const pushType = require('../const/audit').pushType;
const projectLogConst = require('../const/project_log');
const codeRuleConst = require('../const/code_rule');
const changeConst = require('../const/change');

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

        async loadChangeAuditViewData(change) {
            const times = change.status === audit.status.checkNo || change.status === audit.status.revise ? change.times - 1 : change.times;

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

        async loadChangeUser(change) {
            const status = audit.status;
            const accountId = this.ctx.session.sessionUser.accountId;

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

        async add(tenderId, userId, code, project_code, name) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `tid` = ? AND `code` = ?';
            const sqlParam = [this.tableName, tenderId, code];
            const codeCount = await this.db.queryOne(sql, sqlParam);
            const count = codeCount.count;
            if (count > 0) {
                throw '变更申请书编号重复';
            }

            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const change = {
                    tid: tenderId,
                    uid: userId,
                    status: audit.status.uncheck,
                    times: 1,
                    in_time: new Date(),
                    code,
                    project_code,
                    name,
                    quality: changeConst.quality.common.name,
                };
                if (project_code) {
                    const projectInfo = await this.ctx.service.changeProject.getDataByCondition({ tid: tenderId, code: project_code });
                    if (projectInfo) {
                        change.org_name = projectInfo.org_name;
                        change.peg = projectInfo.peg;
                        change.new_code = projectInfo.new_code;
                        change.class = projectInfo.class;
                        change.quality = projectInfo.quality;
                        change.reason = projectInfo.reason;
                        change.content = projectInfo.content;
                        change.org_price = projectInfo.org_price;
                        change.change_price = projectInfo.change_price;
                        change.crease_price = projectInfo.crease_price;
                    }
                }
                const operate = await this.transaction.insert(this.tableName, change);

                if (operate.affectedRows <= 0) {
                    throw '新建变更令数据失败';
                }
                change.id = operate.insertId;
                // 先找出标段最近存在的变更令审批人的变更令info
                const preChangeInfo = await this.getHaveAuditLastInfo(tenderId);
                if (preChangeInfo) {
                    // 并把之前存在的变更令审批人添加到zh_change_audit
                    const auditResult = await this.ctx.service.changeApplyAudit.copyPreChangeApplyAuditors(this.transaction, preChangeInfo, change);
                    if (!auditResult) {
                        throw '复制上一次审批流程失败';
                    }
                }
                result = change;
                this.transaction.commit();
            } catch (error) {
                console.log(error);
                // 回滚
                await this.transaction.rollback();
            }

            return result;
        }

        async getHaveAuditLastInfo(tenderId) {
            const sql = 'SELECT a.* FROM ?? as a LEFT JOIN ?? as b ON a.`id` = b.`caid` WHERE a.`tid` = ? ORDER BY a.`in_time` DESC';
            const sqlParam = [this.tableName, this.ctx.service.changeApplyAudit.tableName, tenderId];
            return await this.db.queryOne(sql, sqlParam);
        }

        /**
         * 获取变更立项列表
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @param {int} hadlimit - 分页
         * @return {object} list - 列表
         */
        async getListByStatus(tenderId, status = 0, hadlimit = 1, sortBy = '', orderBy = '') {
            let sql = '';
            let sqlParam = '';
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                sql = 'SELECT a.*, p.name as account_name FROM ?? As a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE a.tid = ?';
                sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId];
            } else {
                switch (status) {
                    case 0: // 包含你的所有变更立项
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE a.tid = ? AND ' +
                            '(a.uid = ? OR (a.status != ? AND a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid)) OR a.status = ?)';
                        sqlParam = [
                            this.tableName,
                            this.ctx.service.projectAccount.tableName,
                            tenderId,
                            this.ctx.session.sessionUser.accountId,
                            audit.status.uncheck,
                            this.ctx.service.changeApplyAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            audit.status.checked,
                        ];
                        break;
                    case 1: // 待处理(你的)
                        sql = 'SELECT a.*, p.name as account_name FROM ?? as a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE a.tid = ? AND (a.id in(SELECT b.caid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)))';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, tenderId, this.ctx.service.changeApplyAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo, audit.status.revise];
                        break;
                    case 5: // 待上报(所有的)PS：取未上报，退回，修订的变更令
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE ' +
                            'a.uid = ? AND a.tid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)';
                        sqlParam = [
                            this.tableName,
                            this.ctx.service.projectAccount.tableName,
                            // this.ctx.service.changeApplyAudit.tableName,
                            this.ctx.session.sessionUser.accountId,
                            tenderId,
                            audit.status.uncheck,
                            audit.status.checkNo,
                            audit.status.revise,
                        ];
                        break;
                    case 2: // 进行中（所有的）
                    case 4: // 终止（所有的）
                        sql =
                            'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE ' +
                            'a.status = ? AND a.tid = ? AND (a.uid = ? OR a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid))';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeApplyAudit.tableName, this.ctx.session.sessionUser.accountId];
                        break;
                    case 3: // 已完成（所有的）
                        sql = 'SELECT a.*, p.name as account_name FROM ?? AS a LEFT JOIN ?? AS p On a.notice_uid = p.id WHERE a.status = ? AND a.tid = ?';
                        sqlParam = [this.tableName, this.ctx.service.projectAccount.tableName, status, tenderId];
                        break;
                    default:
                        break;
                }
            }
            if (sortBy && orderBy) {
                if (sortBy === 'code') {
                    sql += ' ORDER BY CHAR_LENGTH(a.code) ' + orderBy + ',convert(a.code using gbk) ' + orderBy;
                } else {
                    sql += ' ORDER BY a.in_time ' + orderBy;
                }
            } else {
                sql += ' ORDER BY a.in_time DESC';
            }
            if (hadlimit) {
                const limit = this.ctx.pageSize ? this.ctx.pageSize : this.app.config.pageSize;
                const offset = limit * (this.ctx.page - 1);
                const limitString = offset >= 0 ? offset + ',' + limit : limit;
                sql += ' LIMIT ' + limitString;
            }
            const list = await this.db.query(sql, sqlParam);
            return list;
        }

        // 获取最后一个变更申请
        async getLastChange(tenderId) {
            const sql = 'select * from ?? where tid = ? order by in_time desc LIMIT 1';
            const sqlParam = [this.tableName, tenderId];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        /**
         * 获取变更令个数
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getCountByStatus(tenderId, status) {
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                const sql5 = 'SELECT count(*) AS count FROM ?? WHERE tid = ? ORDER BY in_time DESC';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].count;
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT count(*) AS count FROM ?? AS a WHERE a.tid = ? AND ' +
                        '(a.uid = ? OR (a.status != ? AND a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid)) OR a.status = ?)';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.uncheck,
                        this.ctx.service.changeApplyAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].count;
                case 1: // 待处理(你的)
                    // return await this.ctx.service.changeAudit.count({
                    //     tid: tenderId,
                    //     uid: this.ctx.session.sessionUser.accountId,
                    //     status: 2,
                    // });
                    const sql6 = 'SELECT count(*) AS count FROM ?? as a WHERE a.tid = ? AND (a.id in(SELECT b.caid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)))';
                    const sqlParam6 = [this.tableName, tenderId, this.ctx.service.changeApplyAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo, audit.status.revise];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].count;
                case 5: // 待上报(所有的)PS：取未上报，退回的变更立项
                    const sql2 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE ' +
                        'a.uid = ? AND a.tid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)';
                    const sqlParam2 = [
                        this.tableName,
                        // this.ctx.service.changeApplyAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.uncheck,
                        audit.status.checkNo,
                        audit.status.revise,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].count;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT count(*) AS count FROM ?? AS a WHERE ' +
                        'a.status = ? AND a.tid = ? AND (a.uid = ? OR a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid))';
                    const sqlParam3 = [this.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeApplyAudit.tableName, this.ctx.session.sessionUser.accountId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].count;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT count(*) AS count FROM ?? WHERE status = ? AND tid = ?';
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].count;
                default:
                    break;
            }
        }

        /**
         * 获取变更方案金额
         * @param {int} tenderId - 标段id
         * @param {int} status - 状态
         * @return {void}
         */
        async getTp(tenderId, status) {
            if ((this.ctx.tender.isTourist || this.ctx.session.sessionUser.is_admin) && status === 0) {
                const sql5 = 'SELECT SUM(cast (total_price as decimal(18,6))) AS total_price FROM ?? WHERE tid = ?';
                const sqlParam5 = [this.tableName, tenderId];
                const result5 = await this.db.query(sql5, sqlParam5);
                return result5[0].total_price ? result5[0].total_price : 0;
            }
            switch (status) {
                case 0: // 包含你的所有变更令
                    const sql =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE a.tid = ? AND ' +
                        '(a.uid = ? OR (a.status != ? AND a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid)) OR a.status = ?)';
                    const sqlParam = [
                        this.tableName,
                        tenderId,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.uncheck,
                        this.ctx.service.changeApplyAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        audit.status.checked,
                    ];
                    const result = await this.db.query(sql, sqlParam);
                    return result[0].total_price ? result[0].total_price : 0;
                case 1: // 待处理(你的)
                    // return await this.ctx.service.changeAudit.count({
                    //     tid: tenderId,
                    //     uid: this.ctx.session.sessionUser.accountId,
                    //     status: 2,
                    // });
                    const sql6 = 'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? as a WHERE a.tid = ? AND (a.id in(SELECT b.caid FROM ?? as b WHERE b.tid = ? AND b.aid = ? AND b.status = ?) OR (a.uid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)))';
                    const sqlParam6 = [this.tableName, tenderId, this.ctx.service.changeApplyAudit.tableName, tenderId, this.ctx.session.sessionUser.accountId, audit.status.checking, this.ctx.session.sessionUser.accountId, audit.status.uncheck, audit.status.checkNo, audit.status.revise];
                    const result6 = await this.db.query(sql6, sqlParam6);
                    return result6[0].total_price ? result6[0].total_price : 0;
                case 5: // 待上报(所有的)PS：取未上报，退回的变更立项
                    const sql2 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE ' +
                        'a.uid = ? AND a.tid = ? AND (a.status = ? OR a.status = ? OR a.status = ?)';
                    const sqlParam2 = [
                        this.tableName,
                        // this.ctx.service.changePlanAudit.tableName,
                        this.ctx.session.sessionUser.accountId,
                        tenderId,
                        audit.status.uncheck,
                        audit.status.checkNo,
                        audit.status.revise,
                    ];
                    const result2 = await this.db.query(sql2, sqlParam2);
                    return result2[0].total_price ? result2[0].total_price : 0;
                case 2: // 进行中（所有的）
                case 4: // 终止（所有的）
                    const sql3 =
                        'SELECT SUM(cast (a.total_price as decimal(18,6))) AS total_price FROM ?? AS a WHERE ' +
                        'a.status = ? AND a.tid = ? AND (a.uid = ? OR a.id IN (SELECT b.caid FROM ?? AS b WHERE b.aid = ? GROUP BY b.caid))';
                    const sqlParam3 = [this.tableName, status, tenderId, this.ctx.session.sessionUser.accountId, this.ctx.service.changeApplyAudit.tableName, this.ctx.session.sessionUser.accountId];
                    const result3 = await this.db.query(sql3, sqlParam3);
                    return result3[0].total_price ? result3[0].total_price : 0;
                case 3: // 已完成（所有的）
                    const sql4 = 'SELECT SUM(cast (total_price as decimal(18,6))) AS total_price FROM ?? WHERE status = ? AND tid = ?';
                    const sqlParam4 = [this.tableName, status, tenderId];
                    const result4 = await this.db.query(sql4, sqlParam4);
                    return result4[0].total_price ? result4[0].total_price : 0;
                default:
                    break;
            }
        }

        /**
         * 保存变更信息
         * @param {int} postData - 表单提交的数据
         * @param {int} tenderId - 标段id
         * @return {void}
         */
        async saveInfo(caId, postData) {
            // 初始化事务
            const transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const updateData = {
                    id: caId,
                };
                updateData[postData.name] = postData.val;
                await transaction.update(this.tableName, updateData);
                await transaction.commit();
                result = true;
            } catch (error) {
                await transaction.rollback();
                result = false;
            }
            return result;
        }

        /**
         * 判断是否有重名的变更立项
         * @param caid
         * @param code
         * @param tid
         * @return {Promise<void>}
         */
        async isRepeat(caId, code, tid) {
            const sql = 'SELECT COUNT(*) as count FROM ?? WHERE `code` = ? AND `id` != ? AND `tid` = ?';
            const sqlParam = [this.tableName, code, caId, tid];
            const result = await this.db.queryOne(sql, sqlParam);
            return result.count !== 0;
        }

        /**
         * 查询可用的变更令
         * @param { string } cid - 查询的清单
         * @return {Promise<*>} - 可用的变更令列表
         */
        async delete(id) {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                const changeInfo = await this.getDataById(id);
                // 先删除审批人列表
                await this.transaction.delete(this.ctx.service.changeApplyAudit.tableName, { caid: id });
                // 再删除附件和附件文件ni zuo
                const attList = await this.ctx.service.changeApplyAtt.getAllDataByCondition({ where: { caid: id } });
                await this.ctx.helper.delFiles(attList);
                await this.transaction.delete(this.ctx.service.changeApplyAtt.tableName, { caid: id });
                // 最后删除变更令
                await this.transaction.delete(this.tableName, { id });
                // 删除history
                await this.transaction.delete(this.ctx.service.changeApplyHistory.tableName, { caid: id });
                // 记录删除日志
                await this.ctx.service.projectLog.addProjectLog(this.transaction, projectLogConst.type.changeApply, projectLogConst.status.delete, changeInfo.code);
                await this.transaction.commit();
                result = true;
            } catch (e) {
                await this.transaction.rollback();
                result = false;
            }
            return result;
        }

        async doCheckChangeCanCancel(change) {
            // 获取当前审批人的上一个审批人，判断是否是当前登录人，并赋予撤回功能，（当审批人存在有审批过时，上一人不允许再撤回）
            const status = audit.status;
            const accountId = this.ctx.session.sessionUser.accountId;
            const auditors = change.auditors;
            change.cancancel = 0;
            if (change.status !== status.checked && change.status !== status.uncheck && change.status !== status.revise) {
                if (change.status === status.checkNo) {
                    const lastAuditors = await this.service.changeApplyAudit.getAuditors(change.id, change.times - 1);
                    const onAuditor = this._.findLast(lastAuditors, { status: status.checkNo });
                    if (onAuditor && onAuditor.aid === accountId) {
                        change.cancancel = 4;// 审批人撤回退回原报
                        change.preAuditors = lastAuditors.filter(x => { return x.order === onAuditor.order; });
                    }
                } else {
                    if (change.flowAuditors.find(x => { return x.status !== status.checking; }) && change.flowAuditorIds.indexOf(accountId) < 0) return; // 当前流程存在审批人审批通过时，不可撤回
                    if (change.curAuditorIds.indexOf(accountId) < 0 && change.flowAuditorIds.indexOf(accountId) >= 0) {
                        change.cancancel = 5; // 会签未全部审批通过时，审批人撤回审批通过
                        return;
                    }
                    const preAuditors = change.curAuditors[0].order !== 1 ? change.auditors.filter(x => { return x.order === change.curAuditors[0].order - 1; }) : [];
                    const preAuditorCheckAgain = preAuditors.find(pa => { return pa.status === status.checkAgain; });
                    const preAuditorCheckCancel = preAuditors.find(pa => { return pa.status === status.checkCancel; });
                    const preAuditorIds = preAuditorCheckAgain ? [] : preAuditors.map(x => { return x.aid; }); // 重审不可撤回
                    if ((this._.isEqual(change.flowAuditorIds, preAuditorIds) && preAuditorCheckCancel)) {
                        return; // 不可以多次撤回
                    }
                    const preAuditChecked = preAuditors.find(pa => { return pa.status === status.checked && pa.aid === accountId; });
                    // const preAuditCheckNoPre = preAuditors.find(pa => { return pa.status === status.checkNoPre && pa.uid === accountId; });
                    if (preAuditorIds.indexOf(accountId) >= 0) {
                        if (preAuditChecked && change.status === status.checking) {
                            change.cancancel = 2;// 审批人撤回审批通过
                        }
                        // else if (preAuditCheckNoPre && change.status === status.checkNoPre) {
                        //     change.cancancel = 3;// 审批人撤回审批退回上一人
                        // }
                        change.preAuditors = preAuditors;
                    } else if (preAuditors.length === 0 && accountId === change.uid) {
                        change.cancancel = 1;// 原报撤回
                    }
                }
            }
        }

        async getListByArchives(tid, ids) {
            if (ids.length === 0) return [];
            const sql = 'SELECT c.* FROM ?? as c LEFT JOIN (SELECT caid, MAX(end_time) as end_time FROM ?? WHERE ' +
                'tid = ? AND caid in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY caid) as ca ON c.id = ca.caid 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.changeApplyAudit.tableName, tid, tid, audit.status.checked];
            const list = await this.db.query(sql, params);
            return list;
        }
    }

    return ChangeApply;
};