Calculation/app/service/change_audit_list.js

'use strict';

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

const audit = require('../const/audit');

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

        /**
         * 取出变更令清单列表，并按台账清单在前，空白清单在后排序
         * @return {void}
         */
        async getList(cid, order_by = this.ctx.change.order_by) {
            if (order_by) {
                return await this.getAllDataByCondition({ where: { cid }, orders: [['order', 'asc']] });
            }
            const sql = 'SELECT * FROM ?? WHERE `cid` = ? ORDER BY `lid` = "0", `id` asc';
            const sqlParam = [this.tableName, cid];
            const result = await this.db.query(sql, sqlParam);
            return this._.orderBy(result, ['order'], ['asc']);
        }

        /**
         * 移除清单时，同步其后清单order
         * @param transaction - 事务
         * @param {Number} cid - 变更cid
         * @param {Number} order - order之后的
         * @return {Promise<*>}
         * @private
         */
        async _syncOrder(transaction, cid, order, selfOperate = '-', num = 1) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('cid', {
                value: this.db.escape(cid),
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: order,
                operate: '>=',
            });
            this.sqlBuilder.setUpdateData('order', {
                value: num,
                selfOperate,
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const data = await transaction.query(sql, sqlParam);
            return data;
        }

        /**
         * 添加空白变更清单
         * @return {void}
         */
        async add(data, delimit = 100) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                let order = null;
                if (this.ctx.change.order_by) {
                    if (data) {
                        order = parseInt(data) + 1;
                        // order以下的清单+1
                        await this._syncOrder(transaction, this.ctx.change.cid, order, '+');
                    } else {
                        order = await this.count({ cid: this.ctx.change.cid });
                        order = order ? order + 1 : 1;
                    }
                }
                const insertData = {
                    tid: this.ctx.tender.id,
                    cid: this.ctx.change.cid,
                    lid: '0',
                    code: '',
                    name: '',
                    bwmx: '',
                    unit: '',
                    unit_price: null,
                    oamount: 0,
                    oamount2: 0,
                    camount: 0,
                    camount_expr: '',
                    samount: '',
                    detail: '',
                    spamount: 0,
                    xmj_code: null,
                    xmj_jldy: null,
                    xmj_dwgc: null,
                    xmj_fbgc: null,
                    xmj_fxgc: null,
                    gcl_id: '',
                    order,
                    is_valuation: 1,
                    delimit,
                };
                // 新增工料
                const result = await transaction.insert(this.tableName, insertData);
                if (result.affectedRows === 0) {
                    throw '新增空白清单数据失败';
                }
                await transaction.commit();
                return await this.getDataById(result.insertId);
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 添加台账清单（从新增部位页新增）
         * @return {void}
         */
        async adds(datas) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                let order = null;
                if (this.ctx.change.order_by) {
                    const data = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null;
                    if (data) {
                        order = parseInt(data.order) + 1;
                        // order以下的清单+1
                        await this._syncOrder(transaction, this.ctx.change.cid, order, '+');
                    } else {
                        order = await this.count({ cid: this.ctx.change.cid });
                        order = order ? order + 1 : 1;
                    }
                }
                const insertData = [];
                for (const d of datas) {
                    d.tid = this.ctx.tender.id;
                    d.cid = this.ctx.change.cid;
                    d.spamount = d.spamount || null;
                    d.detail = d.detail || '';
                    d.samount = d.samount || '';
                    d.order = order ? order : null;
                    order = order ? order + 1 : null;
                    insertData.push(d);
                }
                // 新增工料
                const result = await transaction.insert(this.tableName, insertData);
                if (result.affectedRows === 0) {
                    throw '添加清单数据失败';
                }
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 批量添加空白变更清单
         * @return {void}
         */
        async batchAdd(data, delimit = 100) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                const num = data.num ? parseInt(data.num) : 0;
                if (num < 1 || num > 100) {
                    throw '批量添加的空白清单数目不能小于1或大于100';
                }
                let order = null;
                if (this.ctx.change.order_by) {
                    if (data) {
                        order = parseInt(data.postData) + 1;
                        // order以下的清单+1
                        await this._syncOrder(transaction, this.ctx.change.cid, order, '+', num);
                    } else {
                        order = await this.count({ cid: this.ctx.change.cid });
                        order = order ? order + 1 : 1;
                    }
                }
                const insertArray = [];
                for (let i = 0; i < num; i++) {
                    const insertData = {
                        tid: this.ctx.tender.id,
                        cid: this.ctx.change.cid,
                        lid: '0',
                        code: '',
                        name: '',
                        bwmx: '',
                        unit: '',
                        unit_price: null,
                        oamount: 0,
                        oamount2: 0,
                        camount: 0,
                        camount_expr: '',
                        samount: '',
                        detail: '',
                        spamount: 0,
                        xmj_code: null,
                        xmj_jldy: null,
                        xmj_dwgc: null,
                        xmj_fbgc: null,
                        xmj_fxgc: null,
                        gcl_id: '',
                        order: order ? order + i : null,
                        is_valuation: 1,
                        delimit,
                    };
                    insertArray.push(insertData);
                }
                // 新增工料
                const result = await transaction.insert(this.tableName, insertArray);
                if (result.affectedRows !== num) {
                    throw '批量添加空白清单数据失败';
                }
                await transaction.commit();
                // // 获取刚批量添加的所有list
                // for (let j = 0; j < num; j++) {
                //     insertArray[j].id = result.insertId + j;
                // }
                // return insertArray;
                return await this.getList(this.ctx.change.cid);
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 删除变更清单
         * @param {int} id 清单id
         * @return {void}
         */
        async del(data) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 判断是否可删
                await transaction.delete(this.tableName, { id: data.ids });
                // // order以下的清单-1
                if (this.ctx.change.order_by) {
                    await this._syncOrder(transaction, this.ctx.change.cid, data.postData, '-', data.ids.length);
                }
                // 重新算变更令总额
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async dels(data) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            const transaction = await this.db.beginTransaction();
            try {
                // 判断是否存在调用，存在则报错
                const delList = await this.getAllDataByCondition({ where: { id: data.ids } });
                const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? AND b.id in (' + this.ctx.helper.getInArrStrSqlFilter(data.ids) + ') GROUP BY b.cbid';
                const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid];
                const usedList = await transaction.query(sql1, sqlParam1);
                if (usedList.length > 0) {
                    throw '清单已被调用，不可删除';
                }
                await transaction.delete(this.tableName, { id: data.ids });
                // // order以下的清单-1
                if (this.ctx.change.order_by) {
                    const postData = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null;
                    await this._syncOrder(transaction, this.ctx.change.cid, (postData ? postData.order : null), '-', data.ids.length);
                }
                // 重新算变更令总额
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改变更清单
         * @param {Object} data 工料内容
         * @param {int} order 期数
         * @return {void}
         */
        async save(data, order) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }

            const transaction = await this.db.beginTransaction();
            try {
                // const mb_id = data.mb_id;
                // delete data.mb_id;
                await transaction.update(this.tableName, data);
                // await this.calcQuantityByML(transaction, mb_id);
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 修改变更清单 复制粘贴
         * @param {Object} datas 修改内容
         * @return {void}
         */
        async saveDatas(datas) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                // for (const data of datas) {
                //     const mb_id = data.mb_id;
                //     delete data.mb_id;
                //     await transaction.update(this.tableName, data);
                //     await this.calcQuantityByML(transaction, mb_id);
                // }
                await transaction.updateRows(this.tableName, datas);
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 台账数据清单 重新选择
         * @param {Object} datas 内容
         * @return {void}
         */
        async saveLedgerListDatas(datas, data = null, order_by = this.ctx.change.order_by) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                let usedList = [];
                let order = null;
                if (order_by) {
                    if (data) {
                        order = parseInt(data) + 1;
                        // order以下的清单+1
                        await this._syncOrder(transaction, this.ctx.change.cid, order, '+', datas.length);
                    } else {
                        order = await this.count({ cid: this.ctx.change.cid });
                        order = order ? order + 1 : 1;
                    }
                } else {
                    const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? GROUP BY b.cbid';
                    const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid];
                    usedList = await transaction.query(sql1, sqlParam1);
                    // 先删除原本的台账清单数据
                    const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"';
                    const sqlParam = [this.tableName, this.ctx.change.cid];
                    await transaction.query(sql, sqlParam);
                }
                const insertDatas = [];
                for (const data of datas) {
                    data.tid = this.ctx.tender.id;
                    data.cid = this.ctx.change.cid;
                    data.spamount = data.camount;
                    data.samount = '';
                    data.order = order ? order : null;
                    order = order ? order + 1 : null;
                    insertDatas.push(data);
                }
                if (insertDatas.length > 0) await this.insertBigDatas(transaction, insertDatas);
                await this.calcCamountSum(transaction);
                if (!order_by) {
                    // 更新stage_change和stage_change_final的cbid
                    if (usedList.length > 0) {
                        const updateList = [];
                        const sql2 = 'SELECT * FROM ?? WHERE `cid` = ? AND `lid` != "0"';
                        const sqlParam2 = [this.tableName, this.ctx.change.cid];
                        const newList = await transaction.query(sql2, sqlParam2);
                        // const newList = await transaction.select(this.tableName, { where: { cid: this.ctx.change.cid } });
                        for (const used of usedList) {
                            const findFilter = { lid: used.lid, gcl_id: used.gcl_id, bwmx: used.bwmx };
                            if (used.mx_id) findFilter.mx_id = used.mx_id;
                            const newone = this._.find(newList, findFilter);
                            if (newone) {
                                updateList.push({
                                    row: {
                                        cbid: newone.id,
                                    },
                                    where: {
                                        cid: this.ctx.change.cid,
                                        cbid: used.id,
                                    },
                                });
                            }
                        }
                        if (updateList.length > 0) {
                            await transaction.updateRows(this.ctx.service.stageChange.tableName, updateList);
                            await transaction.updateRows(this.ctx.service.stageChangeFinal.tableName, updateList);
                        }
                    }
                }
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        /**
         * 台账数据清单 清除部分并重新算原设计总金额
         * @param {Object} datas 内容
         * @return {void}
         */
        async removeLedgerListDatas(datas) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                // 先删除原本的台账清单数据
                // const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"';
                // const sqlParam = [this.tableName, this.ctx.change.cid];
                // await transaction.query(sql, sqlParam);
                // const insertDatas = [];
                for (const data of datas) {
                    // data.tid = this.ctx.tender.id;
                    // data.cid = this.ctx.change.cid;
                    // data.spamount = data.camount;
                    // data.samount = '';
                    // insertDatas.push(data);
                    await transaction.delete(this.tableName, { id: data.id });
                }
                // if (insertDatas.length > 0) await transaction.insert(this.tableName, insertDatas);
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async calcCamountSum(transaction, updateTpDecimal = false) {
            // const sql = 'SELECT SUM(ROUND(`camount`*`unit_price`, )) as total_price FROM ?? WHERE cid = ?';
            // const sqlParam = [this.tableName, this.change.cid];
            // const tp = await transaction.queryOne(sql, sqlParam);
            // 防止小数位不精确，采用取值计算
            const sql = 'SELECT unit_price, spamount FROM ?? WHERE cid = ?';
            const sqlParam = [this.tableName, this.ctx.change.cid];
            const changeList = await transaction.query(sql, sqlParam);
            let total_price = 0;
            let positive_tp = 0;
            let negative_tp = 0;
            let valuation_tp = 0;
            let unvaluation_tp = 0;
            const tp_decimal = this.ctx.change.tp_decimal ? this.ctx.change.tp_decimal : this.ctx.tender.info.decimal.tp;
            for (const cl of changeList) {
                const price = this.ctx.helper.mul(cl.unit_price, cl.spamount, tp_decimal);
                total_price = this.ctx.helper.accAdd(total_price, price);
                if (price >= 0) {
                    positive_tp = this.ctx.helper.accAdd(positive_tp, price);
                } else {
                    negative_tp = this.ctx.helper.accAdd(negative_tp, price);
                }
                if (cl.is_valuation) {
                    valuation_tp = this.ctx.helper.accAdd(valuation_tp, price);
                } else {
                    unvaluation_tp = this.ctx.helper.accAdd(unvaluation_tp, price);
                }
            }
            const updateData = {
                total_price,
                positive_tp,
                negative_tp,
                valuation_tp,
                unvaluation_tp,
            };
            if (updateTpDecimal) {
                updateData.tp_decimal = tp_decimal;
            }
            const options = {
                where: {
                    cid: this.ctx.change.cid,
                },
            };
            await transaction.update(this.ctx.service.change.tableName, updateData, options);
        }

        /**
         * 用户数据数量提交
         * @param {Object} data 内容
         * @return {void}
         */
        async saveAmountData(data) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            // 判断是否可修改
            // 判断t_type是否为费用
            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.tableName, data);
                await this.calcCamountSum(transaction);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async gatherBgBills(tid) {
            const sql = 'SELECT cb.code, cb.name, cb.unit, cb.unit_price, Round(Sum(cb.samount + 0), 6) as quantity' +
                '  FROM ' + this.tableName + ' cb' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' +
                '  WHERE cb.tid = ? and c.status = ?' +
                '  GROUP BY code, name, unit, unit_price';
            const param = [tid, audit.flow.status.checked];
            const result = await this.db.query(sql, param);
            for (const b of result) {
                b.total_price = this.ctx.helper.mul(b.unit_price, b.quantity, this.ctx.tender.info.decimal.tp);
            }
            return result;
        }

        /**
         * 报表用
         * Tony Kang
         * @param {tid} tid - 标段id
         * @return {void}
         */
        async getChangeAuditBills(tid, onlyChecked) {
            const sql = 'SELECT cb.*' +
                '  FROM ' + this.tableName + ' cb' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' +
                '  WHERE c.tid = ? ' + (onlyChecked ? 'and c.status = 3' : '') +
                '  ORDER BY cb.cid, cb.code';
            const param = [tid];
            const result = await this.db.query(sql, param);
            return result;
        }


        /**
         * 删除变更清单（form 变更新增部位页）
         * Tony Kang
         * @param {String} transaction - 队列
         * @param {String} tid - 标段id
         * @param {Array} ids - id列表
         * @param {String} column - id所属字段
         * @param {String} mx_id - mx_id为空列删除
         * @return {void}
         */
        async deleteDataByRevise(transaction, tid, ids, column = 'gcl_id', mx_id = 'hello') {
            if (ids.length > 0) {
                const addSql = mx_id === '' ? ' AND (`mx_id` is NULL OR `mx_id` = "")' : '';
                const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ')' + addSql + ' GROUP BY `cid`';
                const params = [this.tableName, tid];
                const changes = await transaction.query(sql, params);
                if (changes.length > 0) {
                    const delData = {
                        tid,
                    };
                    delData[column] = ids;
                    await transaction.delete(this.tableName, delData);
                    for (const c of changes) {
                        // 重算选了此清单的变更令已变更金额
                        await this.reCalcTp(transaction, c.cid);
                    }
                }
            }
        }

        /**
         * 修改变更清单（form 变更新增部位页台账子节点清单编号编辑）
         * Tony Kang
         * @param {String} transaction - 队列
         * @param {String} tid - 标段id
         * @param {Array} datas - 更新列表
         * @param {String} column - id所属字段
         * @return {void}
         */
        async updateDataByReviseLedger(transaction, tid, datas, column = 'gcl_id') {
            if (datas.length > 0) {
                const ids = this._.map(datas, 'id');
                const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
                const params = [this.tableName, tid];
                const changeAuditLists = await transaction.query(sql, params);
                if (changeAuditLists.length > 0) {
                    const updateArr = [];
                    const cidList = [];
                    for (const ca of changeAuditLists) {
                        const d = this._.find(datas, { id: ca[column] });
                        if (d.id) {
                            const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id });
                            const updateCol = {};
                            if (column === 'gcl_id' && d.b_code) updateCol.code = d.b_code;
                            if (column === 'gcl_id' && d.quantity !== undefined && changePosNum === 0) updateCol.oamount = d.quantity ? d.quantity : 0;
                            if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0;
                            if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit;
                            if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name;
                            if (d.b_code !== undefined && d.b_code === null) {
                                // 清单升级成了项目节，故删除变更已有的此清单，并找出需要重新计算的变更令
                                const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
                                const params = [this.tableName, tid, d.id];
                                const changes = await transaction.query(sql, params);
                                for (const c of changes) {
                                    if (this._.indexOf(cidList, c.cid) === -1) {
                                        cidList.push(c.cid);
                                    }
                                }
                                const delData = {
                                    tid,
                                };
                                delData[column] = d.id;
                                await transaction.delete(this.tableName, delData);
                            } else {
                                const options = {
                                    row: {},
                                    where: {},
                                };
                                options.row = updateCol;
                                options.where[column] = d.id;
                                if (!this._.isEmpty(options.row)) updateArr.push(options);
                                if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) {
                                    const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
                                    const params = [this.tableName, tid, d.id];
                                    const changes = await transaction.query(sql, params);
                                    for (const c of changes) {
                                        if (this._.indexOf(cidList, c.cid) === -1) {
                                            cidList.push(c.cid);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
                    if (cidList.length > 0) {
                        for (const c of cidList) {
                            await this.reCalcTp(transaction, c);
                        }
                    }
                }
                // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号，细目，单位工程，分部分项工程数据
                for (const data of datas) {
                    const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id });
                    if (select && select.is_leaf === 0) {
                        const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction);
                        const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新
                        if (childLists.length > 0) {
                            const d = { xmj_code: '', xmj_jldy: '' };
                            if (select.code !== null) {
                                d.xmj_code = select.code;
                                d.xmj_jldy = select.name;
                            } else {
                                // 再找出上一个项目节节点并更新
                                this.newBills = false;
                                const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid);
                                d.xmj_code = parents.code;
                                d.xmj_jldy = parents.name;
                            }
                            for (const cl of childLists) {
                                await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } });
                            }
                        }
                        if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据
                            const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新
                            const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新
                            const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新
                            if (secondChildLists.length > 0) {
                                for (const sl of secondChildLists) {
                                    await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } });
                                }
                            }
                            if (thirdChildLists.length > 0) {
                                for (const tl of thirdChildLists) {
                                    await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } });
                                }
                            }
                            if (fourthChildLists.length > 0 && select.level === 2) {
                                for (const fl of fourthChildLists) {
                                    await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } });
                                }
                            }
                        }
                    }
                }
            }
        }

        /**
         * 修改变更清单（form 变更新增部位页台账节点清单编号升降级）
         * Tony Kang
         * @param {String} transaction - 队列
         * @param {String} tid - 标段id
         * @param {Array} datas - 更新列表
         * @param {String} column - id所属字段
         * @return {void}
         */
        async updateDataByReviseLedgerUpDownLevel(transaction, tid, datas, column = 'gcl_id') {
            if (datas.length > 0) {
                console.log(datas);
                // const ids = this._.map(datas, 'id');
                // const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
                // const params = [this.tableName, tid];
                // const changeAuditLists = await transaction.query(sql, params);
                // if (changeAuditLists.length > 0) {
                //     const updateArr = [];
                //     const cidList = [];
                //     for (const ca of changeAuditLists) {
                //         const d = this._.find(datas, { id: ca[column] });
                //         console.log(d);
                //         if (d.id) {
                //             const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id });
                //             const updateCol = {};
                //             if (column === 'gcl_id' && d.b_code !== undefined) updateCol.code = d.b_code;
                //             if (column === 'gcl_id' && d.sgfh_qty !== undefined && changePosNum === 0) updateCol.oamount = d.sgfh_qty ? d.sgfh_qty : 0;
                //             if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0;
                //             if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit;
                //             if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name;
                //             if (d.code !== undefined && d.b_code === null) {
                //                 // 清单升级成了项目节，故删除变更已有的此清单，并找出需要重新计算的变更令
                //                 const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
                //                 const params = [this.tableName, tid, d.id];
                //                 const changes = await transaction.query(sql, params);
                //                 for (const c of changes) {
                //                     if (this._.indexOf(cidList, c.cid) === -1) {
                //                         cidList.push(c.cid);
                //                     }
                //                 }
                //                 const delData = {
                //                     tid,
                //                 };
                //                 delData[column] = d.id;
                //                 console.log(delData);
                //                 await transaction.delete(this.tableName, delData);
                //             } else {
                //                 const options = {
                //                     row: {},
                //                     where: {},
                //                 };
                //                 options.row = updateCol;
                //                 options.where[column] = d.id;
                //                 if (!this._.isEmpty(options.row)) updateArr.push(options);
                //                 if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) {
                //                     const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`';
                //                     const params = [this.tableName, tid, d.id];
                //                     const changes = await transaction.query(sql, params);
                //                     for (const c of changes) {
                //                         if (this._.indexOf(cidList, c.cid) === -1) {
                //                             cidList.push(c.cid);
                //                         }
                //                     }
                //                 }
                //             }
                //         }
                //     }
                //     console.log(updateArr, cidList);
                //     if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
                //     if (cidList.length > 0) {
                //         for (const c of cidList) {
                //             await this.reCalcTp(transaction, c);
                //         }
                //     }
                // }
                // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号，细目，单位工程，分部分项工程数据
                // for (const data of datas) {
                //     const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id });
                //     console.log(select);
                //     if (select && select.is_leaf === 0) {
                //         const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction);
                //         const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新
                //         if (childLists.length > 0) {
                //             const d = { xmj_code: '', xmj_jldy: '' };
                //             if (select.code !== null) {
                //                 d.xmj_code = select.code;
                //                 d.xmj_jldy = select.name;
                //             } else {
                //                 // 再找出上一个项目节节点并更新
                //                 this.newBills = false;
                //                 const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid);
                //                 console.log('hello :', parents);
                //                 d.xmj_code = parents.code;
                //                 d.xmj_jldy = parents.name;
                //             }
                //             for (const cl of childLists) {
                //                 await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } });
                //             }
                //         }
                //         if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据
                //             const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新
                //             const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新
                //             const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新
                //             if (secondChildLists.length > 0) {
                //                 for (const sl of secondChildLists) {
                //                     await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } });
                //                 }
                //             }
                //             if (thirdChildLists.length > 0) {
                //                 for (const tl of thirdChildLists) {
                //                     await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } });
                //                 }
                //             }
                //             if (fourthChildLists.length > 0) {
                //                 for (const fl of fourthChildLists) {
                //                     await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } });
                //                 }
                //             }
                //         }
                //     }
                // }
            }
        }

        /**
         * 修改变更清单（form 变更新增部位页计量单元编辑）
         * Tony Kang
         * @param {String} transaction - 队列
         * @param {String} tid - 标段id
         * @param {Array} datas - 更新列表
         * @param {String} column - id所属字段
         * @return {void}
         */
        async updateDataByRevisePos(transaction, tid, datas, column = 'mx_id') {
            if (datas.length > 0) {
                const ids = this._.map(datas, 'id');
                const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column;
                const params = [this.tableName, tid];
                const changeAuditLists = await transaction.query(sql, params);
                if (changeAuditLists.length > 0) {
                    const updateArr = [];
                    for (const ca of changeAuditLists) {
                        const d = this._.find(datas, { id: ca[column] });
                        if (d.id) {
                            const updateCol = {};
                            if (column === 'mx_id' && d.name !== undefined) updateCol.bwmx = d.name;
                            if (column === 'mx_id' && d.quantity !== undefined) updateCol.oamount = d.quantity ? d.quantity : 0;
                            if (column === 'mx_id' && d.quantity === undefined &&
                                ((d.sgfh_expr && d.sgfh_expr === '') || (d.sjcl_expr && d.sjcl_expr === '') || (d.qtcl_expr && d.qtcl_expr === ''))) updateCol.oamount = 0;
                            const options = {
                                row: {},
                                where: {},
                            };
                            options.row = updateCol;
                            options.where[column] = d.id;
                            // if (!this._.isEmpty(updateCol)) await transaction.update(this.tableName, updateCol, options);
                            if (!this._.isEmpty(options.row)) updateArr.push(options);
                        }
                    }
                    if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr);
                }
            }
        }

        /**
         * 重算变更令总金额(变更新增部位设置时使用)
         * @param {String} transaction - 队列
         * @param {String} cid - 变更令id
         */
        async reCalcTp(transaction, cid) {
            const change = await transaction.get(this.ctx.service.change.tableName, { cid });
            let count = '';
            if (change.status === audit.flow.status.uncheck || change.status === audit.flow.status.back || change.status === audit.flow.status.revise) {
                count = '`camount`';
            } else if (change.status === audit.flow.status.checking || change.status === audit.flow.status.backnew) {
                count = '`spamount`';
            }
            if (count) {
                const sql = 'SELECT `unit_price`, ' + count + ' as `count` FROM ?? WHERE `cid` = ?';
                const params = [this.tableName, change.cid];
                const caLists = await transaction.query(sql, params);
                let tp = 0;
                const tpUnit = change.tp_decimal ? change.tp_decimal : this.ctx.tender.info.decimal.tp;
                for (const ca of caLists) {
                    const catp = this.ctx.helper.round(this.ctx.helper.mul(ca.unit_price, ca.count), tpUnit);
                    tp = this.ctx.helper.add(tp, catp);
                }
                console.log(tp);
                if (tp !== change.total_price) {
                    const options = {
                        where: {
                            cid: change.cid,
                        },
                    };
                    const change_update = {
                        total_price: tp,
                    };
                    await transaction.update(this.ctx.service.change.tableName, change_update, options);
                }
            }
        }

        async updateToLedger(transaction, tid, cid) {
            // 找出本条变更属于新增部位的数据
            const allList = await transaction.select(this.tableName, { where: { tid, cid } });
            const result = [];
            const result2 = [];
            for (const l of allList) {
                const changeLedgerInfo = await transaction.get(this.ctx.service.changeLedger.tableName, { id: l.gcl_id });
                if (changeLedgerInfo && this._.findIndex(result, { id: l.gcl_id }) === -1) {
                    result.push(changeLedgerInfo);
                }
                const changePosInfo = await transaction.get(this.ctx.service.changePos.tableName, { id: l.mx_id });
                if (changePosInfo) {
                    result2.push(changePosInfo);
                }
            }
            // const sql = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.gcl_id WHERE b.tid = ? AND b.cid = ? GROUP BY a.id';
            // const sqlParam = [this.ctx.service.changeLedger.tableName, this.tableName, tid, cid];
            // const result = await transaction.query(sql, sqlParam);
            // const sql2 = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.mx_id WHERE b.tid = ? AND b.cid = ?';
            // const sqlParam2 = [this.ctx.service.changePos.tableName, this.tableName, tid, cid];
            // const result2 = await transaction.query(sql2, sqlParam2);
            if (result.length > 0 || result2.length > 0) {
                const changeLedgerGclIdList = this._.map(result, 'id');
                const changeLedgerIdList = this._.uniq(this._.map(result, 'ledger_pid'));// 父节点集合
                const needUpdateLedgerList = [];// 找出需要更新的原台账清单的id
                const needUpdateChangeLedgerList = [];// 找出需要更新的新台账清单的id
                const tpDecimal = this.ctx.tender.info.decimal.tp;
                // 要更新的ledger节点,数量及总数
                for (const data of result2) {
                    if (this._.indexOf(changeLedgerGclIdList, data.lid) === -1) {
                        const info = this._.find(needUpdateLedgerList, { id: data.lid });
                        if (info) {
                            info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty);
                            info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty);
                            info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty);
                            info.quantity = this.ctx.helper.add(info.quantity, data.quantity);
                        } else {
                            needUpdateLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity });
                        }
                    } else {
                        const info = this._.find(needUpdateChangeLedgerList, { id: data.lid });
                        if (info) {
                            info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty);
                            info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty);
                            info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty);
                            info.quantity = this.ctx.helper.add(info.quantity, data.quantity);
                        } else {
                            needUpdateChangeLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity });
                        }
                    }
                }
                // 更新到result上
                if (needUpdateChangeLedgerList.length > 0) {
                    for (const nucl of needUpdateChangeLedgerList) {
                        const now = this._.find(result, { id: nucl.id });
                        now.sgfh_qty = nucl.sgfh_qty;
                        now.sjcl_qty = nucl.sjcl_qty;
                        now.qtcl_qty = nucl.qtcl_qty;
                        now.quantity = nucl.quantity;
                        now.sgfh_tp = this.ctx.helper.mul(now.sgfh_qty, now.unit_price, tpDecimal);
                        now.sjcl_tp = this.ctx.helper.mul(now.sjcl_qty, now.unit_price, tpDecimal);
                        now.qtcl_tp = this.ctx.helper.mul(now.qtcl_qty, now.unit_price, tpDecimal);
                        now.total_price = this.ctx.helper.mul(now.quantity, now.unit_price, tpDecimal);
                    }
                }
                // 更新到ledger上
                if (needUpdateLedgerList.length > 0) {
                    for (const nul of needUpdateLedgerList) {
                        const ledgerInfo = await this.ctx.service.ledger.getDataById(nul.id);
                        ledgerInfo.sgfh_qty = this.ctx.helper.add(ledgerInfo.sgfh_qty, nul.sgfh_qty);
                        ledgerInfo.sjcl_qty = this.ctx.helper.add(ledgerInfo.sjcl_qty, nul.sjcl_qty);
                        ledgerInfo.qtcl_qty = this.ctx.helper.add(ledgerInfo.qtcl_qty, nul.qtcl_qty);
                        ledgerInfo.quantity = this.ctx.helper.add(ledgerInfo.quantity, nul.quantity);
                        ledgerInfo.sgfh_tp = this.ctx.helper.mul(ledgerInfo.sgfh_qty, ledgerInfo.unit_price, tpDecimal);
                        ledgerInfo.sjcl_tp = this.ctx.helper.mul(ledgerInfo.sjcl_qty, ledgerInfo.unit_price, tpDecimal);
                        ledgerInfo.qtcl_tp = this.ctx.helper.mul(ledgerInfo.qtcl_qty, ledgerInfo.unit_price, tpDecimal);
                        ledgerInfo.total_price = this.ctx.helper.mul(ledgerInfo.quantity, ledgerInfo.unit_price, tpDecimal);
                        await transaction.update(this.ctx.service.ledger.tableName, ledgerInfo);
                    }
                }
                // 找出所有新增的父节点并插入到result中
                for (const r of changeLedgerIdList) {
                    await this._findParents(transaction, tid, r, result);
                }
                // 插入到计量单元表,并删除变更的计量单元数据, 插入清单表，并删除变更的清单表
                await this._insertByChangeRevise(transaction, tid, cid, result, result2);
                // 更新标段总金额
                const sumSql = 'SELECT Sum(total_price) As total_price, Sum(deal_tp) As deal_tp' +
                    '  FROM ' + this.ctx.service.ledger.tableName + this.ctx.helper.whereSql({ tender_id: tid });
                const sum = await transaction.queryOne(sumSql);
                await transaction.update(this.ctx.service.tender.tableName, {
                    id: tid,
                    total_price: sum.total_price,
                    deal_tp: sum.deal_tp,
                });
                // 清除修订及台账的maxLid缓存，防止树结构混乱
                await this.ctx.service.reviseBills._removeCacheMaxLid(tid);
                await this.ctx.service.ledger._removeCacheMaxLid(tid);
            }
        }

        async _findParents(transaction, tid, id, result) {
            const info = await transaction.get(this.ctx.service.changeLedger.tableName, { tender_id: tid, ledger_id: id });
            if (info && this._.findIndex(result, { ledger_id: info.ledger_id }) === -1) {
                result.push(info);
                await this._findParents(transaction, tid, info.ledger_pid, result);
            } else {
                return;
            }
        }

        async _insertByChangeRevise(transaction, tid, cid, ledgerList, posList) {
            if (ledgerList.length > 0) {
                const insertLedgerArr = [];
                for (const l of ledgerList) {
                    const insertL = [
                        l.id, l.code, l.b_code, l.name, l.unit, l.source, l.remark, l.ledger_id,
                        l.ledger_pid, l.level, l.order, l.full_path, l.is_leaf, l.quantity, l.total_price,
                        l.unit_price, l.drawing_code, l.memo, l.dgn_qty1, l.dgn_qty2, l.deal_qty, l.deal_tp,
                        l.sgfh_qty, l.sgfh_tp, l.sjcl_qty, l.sjcl_tp, l.qtcl_qty, l.qtcl_tp, l.node_type, l.crid, l.ccid,
                        l.tender_id, l.sgfh_expr, l.sjcl_expr, l.qtcl_expr, l.check_calc,
                        l.ex_memo1, l.ex_memo2, l.ex_memo3,
                    ];
                    insertLedgerArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertL) + ')');
                    await transaction.delete(this.ctx.service.changeLedger.tableName, { id: l.id });
                    // 日志添加
                    await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, lid: l.id, name: l.name ? l.name : (l.code ? l.code : ''), create_time: new Date() });
                }
                const bSql = 'Insert Into ' +
                    this.ctx.service.ledger.tableName +
                    '  (id, code, b_code, name, unit, source, remark, ledger_id, ledger_pid, level, `order`, full_path, is_leaf,' +
                    '     quantity, total_price, unit_price, drawing_code, memo, dgn_qty1, dgn_qty2, deal_qty, deal_tp,' +
                    '     sgfh_qty, sgfh_tp, sjcl_qty, sjcl_tp, qtcl_qty, qtcl_tp, node_type, crid, ccid, tender_id,' +
                    '     sgfh_expr, sjcl_expr, qtcl_expr, check_calc,' +
                    '     ex_memo1, ex_memo2, ex_memo3) VALUES ' + insertLedgerArr.join(',') + ';';
                await transaction.query(bSql, []);
            }
            if (posList.length > 0) {
                const insertPosArr = [];
                for (const p of posList) {
                    const insertp = [
                        p.id, p.tid, p.lid, p.name, p.drawing_code, p.quantity, p.add_stage, p.add_stage_order, p.add_times,
                        p.add_user, p.sgfh_qty, p.sjcl_qty, p.qtcl_qty, p.crid, p.ccid, p.porder, p.position,
                        p.sgfh_expr, p.sjcl_expr, p.qtcl_expr, p.real_qty,
                        p.ex_memo1, p.ex_memo2, p.ex_memo3,
                    ];
                    insertPosArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertp) + ')');
                    await transaction.delete(this.ctx.service.changePos.tableName, { id: p.id });
                    // 日志添加
                    await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, pid: p.id, name: p.name, create_time: new Date() });
                }
                const pSql =
                    'Insert Into ' +
                    this.ctx.service.pos.tableName +
                    '  (id, tid, lid, name, drawing_code, quantity, add_stage, add_stage_order, add_times, add_user,' +
                    '     sgfh_qty, sjcl_qty, qtcl_qty, crid, ccid, porder, position, ' +
                    '     sgfh_expr, sjcl_expr, qtcl_expr, real_qty,' +
                    '     ex_memo1, ex_memo2, ex_memo3)  VALUES ' + insertPosArr.join(',') + ';';
                await transaction.query(pSql, []);
            }
        }

        async checkedChangeBills(tid) {
            const DefaultDecimal = this.ctx.tender.info.decimal.tp;
            const sql = 'SELECT cal.*, c.tp_decimal FROM ' + this.ctx.service.changeAuditList.tableName + ' cal LEFT JOIN ' + this.ctx.service.change.tableName + ' c on cal.cid = c.cid where c.tid = ? and c.valid and c.status = ?';
            const changeBills = await this.db.query(sql, [tid, audit.flow.status.checked]);
            changeBills.forEach(x => {
                x.tp_decimal = x.tp_decimal !== 0 ? x.tp_decimal : DefaultDecimal
            });
            return changeBills;
        }

        /**
         * 交换两个清单的顺序
         * @param {Number} id1 - 工料1的id
         * @param {Number} id2 - 工料2的id
         * @returns {Promise<void>}
         */
        async changeOrder(datas) {
            if (!this.ctx.tender || !this.ctx.change) {
                throw '数据错误';
            }
            // const bill1 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id1 });
            // const bill2 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id2 });
            // if (!bill1 || !bill2) {
            //     throw '数据错误';
            // }

            const transaction = await this.db.beginTransaction();
            try {
                // const order = bill1.order;
                // bill1.order = bill2.order;
                // bill2.order = order;
                // await transaction.update(this.tableName, { id: bill1.id, order: bill1.order });
                // await transaction.update(this.tableName, { id: bill2.id, order: bill2.order });
                await transaction.updateRows(this.tableName, datas);
                await transaction.commit();
                return true;
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
        }

        async setAllValuation(cid, ids, is_valuation) {
            return await this.db.update(this.tableName, { is_valuation }, {
                where: {
                    cid,
                    id: ids,
                },
            });
        }

        async getBillsSum(tid) {
            const sql = 'SELECT gcl_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' +
                '  FROM(' +
                '    SELECT cal.gcl_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' +
                `      FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
                '      WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' +
                '      GROUP BY cal.gcl_id' +
                '    UNION ALL ' +
                '    SELECT cal.gcl_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' +
                `      FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
                '      WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' +
                '      GROUP BY cal.gcl_id) As TEMP' +
                '  GROUP BY gcl_id';
            return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]);
        }

        async getPosSum(tid) {
            const sql = 'SELECT mx_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' +
                '  FROM(' +
                '    SELECT cal.mx_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' +
                `      FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
                '      WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' +
                '      GROUP BY cal.mx_id' +
                '    UNION ALL ' +
                '    SELECT cal.mx_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' +
                `      FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` +
                '      WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' +
                '      GROUP BY cal.mx_id) As TEMP' +
                '  GROUP BY mx_id';
            return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]);
        }
    }

    return ChangeAuditList;
};