Calculation/app/lib/rptCustomData.js

'use strict';

/**
 * 定制报表 注意不做任何混用，也不做任何继承
 *
 * @author Mai
 * @date
 * @version
 */
const auditConst = require('../const/audit');
const Ledger = require('./ledger');

/**
 * 季华项目 定制报表
 * 汇总表，流水汇总2个标段（N个），汇总到期，每期汇总3个人的数据（3个），工程量清单流水表，并根据截至本期变更令使用情况筛选
 *
 * 借用 通用汇总标段选的界面
 * 故配置可与汇总标段选择相同
 *
 * define: {
 *     "title": "请选择汇总的标段", "type": "month/final/checked-final/stage",
 *    "defaultCompare": [1, 2, 3], // 结果按序 rn_qty, rn_tp
 *    "match": { "quality": [2, 3], "qty": "<0" }, // class根据变更类型过滤，qty根据数量过滤
 *    "merge": true,
 * }
 * defaultCompare为默认选择的审批人，0为原报，1-N为1-N审
 * match为保留的类型
 * 如需要用户选择审批人，则应配置selectCompare(目前不可用，不可配置，如需使用，则需要额外界面)，为后期可能的变动预留
 *
 */
class jhHelper {

    constructor (ctx) {
        this.ctx = ctx;
        this.result = [];
    }

    async _getValidStages(tenderId) {
        const stages = await this.ctx.service.stage.db.select(this.ctx.service.stage.tableName, {
            where: { tid: tenderId },
            orders: [['order', 'desc']],
        });
        if (stages.length !== 0) {
            const lastStage = stages[0];
            if (lastStage.status === auditConst.stage.status.uncheck && lastStage.user_id !== this.ctx.session.sessionUser.accountId) {
                stages.splice(0, 1);
            }
        }
        return stages;
    }

    async _getCheckedStages(tenderId) {
        const stages = await this.db.select(this.ctx.service.stage.tableName, {
            where: { tid: tenderId },
            orders: [['order', 'desc']],
        });
        if (stages.length !== 0) {
            const lastStage = stages[0];
            if (lastStage.status !== auditConst.stage.status.checked) {
                stages.splice(0, 1);
            }
        }
        return stages;
    }

    /**
     * 查询本期所有变更明细
     * @param tid
     * @param sid
     * @returns {Promise<void>}
    */
    async getCurChangeDetailData(tid, sid) {
        const sql = 'SELECT sc.*, c.type As c_type, c.class As c_class, c.quality As c_quality FROM ' + this.ctx.service.stageChange.tableName + ' sc' +
                    '  Left Join ' + this.ctx.service.change.tableName + ' c ON sc.cid = c.cid' +
                    '  WHERE sc.tid = ? and sc.sid = ?';
        return await this.ctx.service.stageChange.db.query(sql, [tid, sid]);
    }

    async getPreChangeDetailData(tid, sOrder) {
        const sql = 'SELECT sc.*, c.type As c_type, c.class As c_class, c.quality As c_quality FROM ' + this.ctx.service.stageChangeFinal.tableName + ' sc' +
                    '  Left Join ' + this.ctx.service.change.tableName + ' c ON sc.cid = c.cid' +
                    '  Left Join ' + this.ctx.service.stage.tableName + ' s ON sc.sid = s.id' +
                    '  WHERE sc.tid = ? and s.order < ?';
        return await this.ctx.service.stageChangeFinal.db.query(sql, [tid, sOrder]);
    }

    getLastestAuditors(auditors) {
        const index = {};
        for (const auditor of auditors) {
            if (!index[auditor.aid] || auditor.order > index[auditor.aid].order) index[auditor.aid] = auditor;
        }
        const result = [];
        for (const i in index) {
            result.push(index[i]);
        }
        result.sort((x, y) => { return x.order - y.order; });
        return result;
    }

    _loadChangeDetail(billsIndex, changeDetail, gsDefine, prefix) {
        for (const cd of changeDetail) {
            if (!cd.qty) continue;

            let match = false;
            for (const m of gsDefine.match) {
                if (m.quality === cd.c_quality && ((m.minus && cd.qty < 0) || (!m.minus && cd.qty > 0))) match = true;
            }
            if (!match) continue;

            const bills = billsIndex[cd.lid];
            if (!bills) continue;

            if (!bills[prefix + 'cd']) bills[prefix + 'cd'] = [];
            bills[prefix + 'cd'].push(cd);
            if (cd.pid) {
                const pos = bills.pos.find(x => {return x.id === cd.pid});
                if (pos) {
                    if (!pos[prefix + 'cd']) pos[prefix + 'cd'] = [];
                    pos[prefix + 'cd'].push(cd);
                }
            }
        }
    }

    _loadMergeResult(bills, prefixes, decimal) {
        const rst = {
            id: bills.id,
            tid: bills.tender_id,
            b_code: bills.b_code,
            name: bills.name,
            unit: bills.unit,
            unit_price: bills.unit_price
        };
        if (bills.pos && bills.pos.length > 0) {
            for (const p of bills.pos) {
                let gather = false;
                if (p.pre_cd && p.pre_cd.length > 0) gather = true;
                for (const prefix of prefixes) {
                    if (p[prefix + 'cd'] && p[prefix + 'cd'].length > 0) gather = true;
                }
                if (gather) {
                    rst.qc_qty = this.ctx.helper.add(rst.qc_qty, p.qc_qty);
                    rst.pre_qc_qty = this.ctx.helper.add(rst.pre_qc_qty, p.pre_qc_qty);
                    rst.end_qc_qty = this.ctx.helper.add(rst.end_qc_qty, p.end_qc_qty);
                    for (const prefix of prefixes) {
                        rst[prefix + 'qc_qty'] = this.ctx.helper.add(rst[prefix + 'qc_qty'], p[prefix + 'qc_qty']);
                    }
                }
            }
            rst.qc_tp = this.ctx.helper.mul(rst.unit_price, rst.qc_qty, decimal.tp);
            rst.pre_qc_tp = this.ctx.helper.mul(rst.unit_price, rst.pre_qc_qty, decimal.tp);
            rst.end_qc_tp = this.ctx.helper.add(rst.pre_qc_tp, rst.qc_tp);
            for (const prefix of prefixes) {
                rst[prefix + 'qc_tp'] = this.ctx.helper.mul(rst.unit_price, rst[prefix + 'qc_qty'], decimal.tp);
            }
        } else {
            rst.qc_qty = bills.qc_qty;
            rst.qc_tp = bills.qc_tp;
            rst.pre_qc_qty = bills.pre_qc_qty;
            rst.pre_qc_tp = bills.pre_qc_tp;
            rst.end_qc_qty = bills.end_qc_qty;
            rst.end_qc_tp = bills.end_qc_tp;
            for (const prefix of prefixes) {
                rst[prefix + 'qc_qty'] = bills[prefix + 'qc_qty'];
                rst[prefix + 'qc_tp'] = bills[prefix + 'qc_tp'];
            }
        }
        this.result.push(rst);
    }

    _loadResult(bills, prefixes, decimal) {
        if (bills.pos) {
            for (const p of bills.pos) {
                let load = false;
                if (p.pre_cd && p.pre_cd.length > 0) load = true;
                for (const prefix of prefixes) {
                    if (p[prefix + 'cd'] && p[prefix + 'cd'].length > 0) load = true;
                }
                if (!load) continue;

                const rst = {
                    b_code: bills.b_code,
                    name: bills.name,
                    unit: bills.unit,
                    unit_price: bills.unit_price, 
                };
                rst.qc_qty = p.qc_qty;
                rst.qc_tp = this.ctx.helper.mul(bills.unit_price, p.qc_qty, decimal.tp);
                rst.pre_qc_qty = p.pre_qc_qty;
                rst.pre_qc_tp = this.ctx.helper.mul(bills.unit_price, p.pre_qc_qty, decimal.tp);
                rst.end_qc_qty = p.end_qc_qty;
                rst.end_qc_tp = this.ctx.helper.add(rst.qc_tp, p.pre_qc_tp);
                for (const prefix of prefixes) {
                    rst[prefix + 'qc_qty'] = p[prefix + 'qc_qty'];
                    rst[prefix + 'qc_tp'] = this.ctx.helper.mul(bills.unit_price, p[prefix + 'qc_qty'], decimal.tp);
                }
                this.result.push(rst);
            }
        } else {
            const rst = {
                b_code: bills.b_code,
                name: bills.name,
                unit: bills.unit,
                unit_price: bills.unit_price, 
            };
            rst.qc_qty = bills.qc_qty;
            rst.qc_tp = bills.qc_tp;
            rst.pre_qc_qty = bills.pre_qc_qty;
            rst.pre_qc_tp = bills.pre_qc_tp;
            rst.end_qc_qty = bills.end_qc_qty;
            rst.end_qc_tp = bills.end_qc_tp;
            for (const prefix of prefixes) {
                rst[prefix + 'qc_qty'] = bills[prefix + 'qc_qty'];
                rst[prefix + 'qc_tp'] = bills[prefix + 'qc_tp'];
            }
            this.result.push(rst);
        }
    }

    _generateResult(billsData, gsDefine, decimal) {
        for (const bills of billsData) {
            let load = false;
            if (bills.pre_cd && bills.pre_cd.length > 0) load = true;
            for (const dc of gsDefine.defaultCompare) {
                if (bills['r' + dc + '_cd'] && bills['r' + dc + '_cd'].length > 0) load = true;
            }
            if (!load) continue;
            gsDefine.merge ? this._loadMergeResult(bills, this.prefixes, decimal) : this._loadResult(bills, this.prefixes, decimal);
        }
    }

    async _loadStageBillsData(tender, stage, gsDefine, auditors, filterGcl = true) {
        const helper = this.ctx.helper;
        // 加载截止上期/本期
        let billsData = await this.ctx.service.ledger.getData(tender.id);
        if (filterGcl) billsData = billsData.filter(x => { return x.b_code && x.is_leaf });
        const curStage = await this.ctx.service.stageBills.getLastestStageData2(tender.id, stage.id);
        const preStage = stage.order > 1 ? await this.ctx.service.stageBillsFinal.getFinalData(tender, stage.order - 1) : [];
        const loadData = [
            { data: curStage, fields: this.billsQueryField, prefix: '', relaId: 'lid' },
            { data: preStage, fields: this.billsQueryField, prefix: 'pre_', relaId: 'lid' }
        ];
        for (const dc of gsDefine.defaultCompare) {
            const auditor = auditors[dc];
            if (!auditor) continue;
            const auditorStage = await this.ctx.service.stageBills.getAuditorStageData2(tender.id, stage.id, auditor.times, auditor.order);
            loadData.push({ data: auditorStage, fields: this.billsQueryField, prefix: `r${dc}_`, relaId: 'lid' });
        }
        helper.assignRelaData(billsData, loadData);
        return billsData;
    }

    async _loadStagePosData(tender, stage, gsDefine, auditors) {
        const helper = this.ctx.helper;
        const posData = await this.ctx.service.pos.getPosData({tid: tender.id});
        const curStage = await this.ctx.service.stagePos.getLastestStageData2(tender.id, stage.id);
        const preStage = stage.order > 1 ? await this.ctx.service.stagePosFinal.getFinalData(tender, stage.order - 1) : [];
        const loadData = [
            { data: curStage, fields: ['qc_qty'], prefix: '', relaId: 'pid' },
            { data: preStage, fields: ['qc_qty'], prefix: 'pre_', relaId: 'pid' }
        ];
        for (const dc of gsDefine.defaultCompare) {
            const auditor = auditors[dc];
            if (!auditor) continue;
            const auditorStage = await this.ctx.service.stagePos.getAuditorStageData2(tender.id, stage.id, auditor.times, auditor.order);
            loadData.push({ data: auditorStage, fields: ['qc_qty'], prefix: `r${dc}_`, relaId: 'pid' });
        }
        helper.assignRelaData(posData, loadData);
        return posData;
    }

    async _gatherStageData(tender, stage, gsDefine) {
        if (!stage) return;
        const helper = this.ctx.helper;

        await this.ctx.service.stage.doCheckStage(stage);

        const auditors = this.getLastestAuditors(stage.auditors);
        const user = await this.ctx.service.projectAccount.getDataById(stage.user_id);
        auditors.unshift({
            aid: user.id, name: user.name, company: user.company, role: user.role,
            times: stage.curTimes, order: 0
        });
        const billsData = await this._loadStageBillsData(tender, stage, gsDefine, auditors);
        // 计算截止本期
        billsData.forEach(x => {
            x.end_qc_qty = helper.add(x.qc_qty, x.pre_qc_qty);
            x.end_qc_tp = helper.add(x.qc_tp, x.pre_qc_tp);
        });
        const posData = await this._loadStagePosData(tender, stage, gsDefine, auditors);
        posData.forEach(x => {
            x.end_qc_qty = helper.add(x.qc_qty, x.pre_qc_qty);
        });
        // 创建索引
        const billsIndex = {};
        for (const b of billsData) {
            billsIndex[b.id] = b;
            b.pos = posData.filter(x => { return x.lid === b.id; });
        }

        // 查询比较人数据
        this.prefixes = [];
        const stageChangeDetail = await this.getCurChangeDetailData(tender.id, stage.id);
        this.ctx.helper.saveBufferFile(JSON.stringify(stageChangeDetail, '', '\t'), this.ctx.app.baseDir + '/temp.json');
        for (const dc of gsDefine.defaultCompare) {
            if (!auditors[dc]) continue;
             const scd = helper.filterTimesOrderData(stageChangeDetail, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder', auditors[dc].times, auditors[dc].order);
            this._loadChangeDetail(billsIndex, scd, gsDefine, `r${dc}_`);
            this.prefixes.push(`r${dc}_`);
        }
        const finalChangeData = await this.getPreChangeDetailData(tender.id, stage.order);
        this._loadChangeDetail(billsIndex, finalChangeData, gsDefine, 'pre_');
        this._generateResult(billsData, gsDefine, tender.info.decimal);
    }
    async _gatherStageBillsData(tender, stage, gsDefine) {
        if (!stage) return;
        const helper = this.ctx.helper;

        await this.ctx.service.stage.doCheckStage(stage);

        const auditors = this.getLastestAuditors(stage.auditors);
        const user = await this.ctx.service.projectAccount.getDataById(stage.user_id);
        auditors.unshift({
            aid: user.id, name: user.name, company: user.company, role: user.role,
            times: stage.curTimes, order: 0
        });
        const billsData = await this._loadStageBillsData(tender, stage, gsDefine, auditors, false);
        const billsTree = new Ledger.billsTree(this.ctx, {
            id: 'ledger_id',
            pid: 'ledger_pid',
            order: 'order',
            level: 'level',
            rootId: -1,
            keys: ['id', 'ledger_id'],
            stageId: 'id',
            calcFields: ['deal_tp', 'total_price', 'contract_tp', 'qc_tp', 'gather_tp', 'pre_contract_tp', 'pre_qc_tp', 'pre_gather_tp'],
            calc: function (node) {
                if (node.children && node.children.length === 0) {
                    node.pre_gather_qty = helper.add(node.pre_contract_qty, node.pre_qc_qty);
                    node.gather_qty = helper.add(node.contract_qty, node.qc_qty);
                    node.end_contract_qty = helper.add(node.pre_contract_qty, node.contract_qty);
                    node.end_qc_qty = helper.add(node.pre_qc_qty, node.qc_qty);
                    node.end_gather_qty = helper.add(node.pre_gather_qty, node.gather_qty);
                }
                node.pre_gather_tp = helper.add(node.pre_contract_tp, node.pre_qc_tp);
                node.gather_tp = helper.add(node.contract_tp, node.qc_tp);
                node.end_contract_tp = helper.add(node.pre_contract_tp, node.contract_tp);
                node.end_qc_tp = helper.add(node.pre_qc_tp, node.qc_tp);
                node.end_gather_tp = helper.add(node.pre_gather_tp, node.gather_tp);
                for (const dc of gsDefine.defaultCompare) {
                    const prefix = `r${dc}_`;
                    node[prefix + 'gather_qty'] = helper.add(node[prefix + 'contract_qty'], node[prefix + 'qc_qty']);
                    node[prefix + 'gather_tp'] = helper.add(node[prefix + 'contract_tp'], node[prefix + 'qc_tp']);
                }
            }
        });
        billsTree.loadDatas(billsData);
        billsTree.calculateAll();

        this.resultTree.loadGatherTree(billsTree, function (gatherNode, sourceNode) {
            gatherNode.s_qty = helper.add(gatherNode.s_qty, sourceNode.quantity);
            gatherNode.s_tp = helper.add(gatherNode.s_tp, sourceNode.total_price);

            gatherNode.s_dgn_qty1 = helper.add(gatherNode.s_dgn_qty1, sourceNode.dgn_qty1);
            gatherNode.s_dgn_qty2 = helper.add(gatherNode.s_dgn_qty2, sourceNode.dgn_qty2);

            gatherNode.s_contract_qty = helper.add(gatherNode.s_contract_qty, sourceNode.contract_qty);
            gatherNode.s_contract_tp = helper.add(gatherNode.s_contract_tp, sourceNode.contract_tp);
            gatherNode.s_qc_qty = helper.add(gatherNode.s_qc_qty, sourceNode.qc_qty);
            gatherNode.s_qc_tp = helper.add(gatherNode.s_qc_tp, sourceNode.qc_tp);
            gatherNode.s_gather_qty = helper.add(gatherNode.s_gather_qty, sourceNode.gather_qty);
            gatherNode.s_gather_tp = helper.add(gatherNode.s_gather_tp, sourceNode.gather_tp);

            gatherNode.s_pre_contract_qty = helper.add(gatherNode.s_pre_contract_qty, sourceNode.pre_contract_qty);
            gatherNode.s_pre_contract_tp = helper.add(gatherNode.s_pre_contract_tp, sourceNode.pre_contract_tp);
            gatherNode.s_pre_qc_qty = helper.add(gatherNode.s_pre_qc_qty, sourceNode.pre_qc_qty);
            gatherNode.s_pre_qc_tp = helper.add(gatherNode.s_pre_qc_tp, sourceNode.pre_qc_tp);
            gatherNode.s_pre_gather_qty = helper.add(gatherNode.s_pre_gather_qty, sourceNode.pre_gather_qty);
            gatherNode.s_pre_gather_tp = helper.add(gatherNode.s_pre_gather_tp, sourceNode.pre_gather_tp);

            gatherNode.s_end_contract_qty = helper.add(gatherNode.s_end_contract_qty, sourceNode.end_contract_qty);
            gatherNode.s_end_contract_tp = helper.add(gatherNode.s_end_contract_tp, sourceNode.end_contract_tp);
            gatherNode.s_end_qc_qty = helper.add(gatherNode.s_end_qc_qty, sourceNode.end_qc_qty);
            gatherNode.s_end_qc_tp = helper.add(gatherNode.s_end_qc_tp, sourceNode.end_qc_tp);
            gatherNode.s_end_gather_qty = helper.add(gatherNode.s_end_gather_qty, sourceNode.end_gather_qty);
            gatherNode.s_end_gather_tp = helper.add(gatherNode.s_end_gather_tp, sourceNode.end_gather_tp);

            for (const dc of gsDefine.defaultCompare) {
                const prefix = `r${dc}_`;
                gatherNode[prefix + 'contract_qty'] = helper.add(gatherNode[prefix + 'contract_qty'], sourceNode[prefix + 'contract_qty']);
                gatherNode[prefix + 'contract_tp'] = helper.add(gatherNode[prefix + 'contract_tp'], sourceNode[prefix + 'contract_tp']);
                gatherNode[prefix + 'qc_qty'] = helper.add(gatherNode[prefix + 'qc_qty'], sourceNode[prefix + 'qc_qty']);
                gatherNode[prefix + 'qc_tp'] = helper.add(gatherNode[prefix + 'qc_tp'], sourceNode[prefix + 'qc_tp']);
                gatherNode[prefix + 'gather_qty'] = helper.add(gatherNode[prefix + 'gather_qty'], sourceNode[prefix + 'gather_qty']);
                gatherNode[prefix + 'gather_tp'] = helper.add(gatherNode[prefix + 'gather_tp'], sourceNode[prefix + 'gather_tp']);
            }
        });
    }

    async _gatherMonthData(tender, month, defaultCompare, fun) {
        const stages = await this._getValidStages(tender.id);
        const stage = this.ctx.helper._.find(stages, {s_time: month});
        await fun.call(this, tender, stage, defaultCompare);
    }

    async _gatherFinalData(tender, defaultCompare, fun) {
        const stages = await this._getValidStages(tender.id);
        await fun.call(this, tender, stages[0], defaultCompare);
    }

    async _gatherCheckedFinalData(tender, defaultCompare, fun) {
        const stages = await this._getCheckedStages(tender.id);
        await fun.call(this, tender, stages[0], defaultCompare);
    }

    async _gatherIndexData(tender, index, defaultCompare, fun) {
        const stages = await this._getValidStages(tender.id);
        const stage = this.ctx.helper._.find(stages, {order: index});
        await fun.call(this, tender, stage, defaultCompare);
    }

    /**
     *
     * @param {Array} memFieldKeys 报表添加的指标字段
     * @param {object} gsDefine
     * @param {object} gsCustom
     * @returns {Promise<Array>}
     */
    async gather(memFieldKeys, gsDefine, gsCustom) {
        this.billsQueryField = ['qc_qty', 'qc_tp'];
        if (!gsDefine || !gsDefine.enable) return [];
        if (!gsCustom || !gsCustom.tenders || gsCustom.tenders.length === 0) return [];

        const gsSetting = JSON.parse(gsDefine.setting);
        if (!gsSetting.defaultCompare || !gsSetting.match) return[];
        for (const t of gsCustom.tenders) {
            const tender = await this.ctx.service.tender.getCheckTender(t.tid);

            switch (gsSetting.type) {
                case 'month':
                    await this._gatherMonthData(tender, gsCustom.month, gsSetting, this._gatherStageData);
                    break;
                case 'final':
                    await this._gatherFinalData(tender, gsSetting, this._gatherStageData);
                    break;
                case 'checked-final':
                    await this._gatherCheckedFinalData(tender, gsSetting, this._gatherStageData);
                    break;
                case 'stage':
                    await this._gatherIndexData(tender, gsCustom.stage, gsSetting, this._gatherStageData);
                    break;
                default: throw '未知汇总类型';
            }
        }
        const helper = this.ctx.helper;
        // 排序
        this.result.sort((x, y) => { return helper.compareCode(x.b_code, y.b_code); });
        return this.result;
    }

    async convert(tid, sid, memFieldKeys, option) {
        this.billsQueryField = ['qc_qty', 'qc_tp'];
        if (!option) return [];
        const setting = JSON.parse(option);
        if (!setting || !setting.defaultCompare) return [];
        const tender = await this.ctx.service.tender.getCheckTender(tid);
        const stage = await this.ctx.service.stage.getDataById(sid);
        await this._gatherStageData(tender, stage, setting);
        const helper = this.ctx.helper;
        // 排序
        this.result.sort((x, y) => { return helper.compareCode(x.b_code, y.b_code); });
        return this.result;
    }

    async gatherBills(memFieldKeys, gsDefine, gsCustom) {
        if (!gsDefine || !gsDefine.enable) return [];
        if (!gsCustom || !gsCustom.tenders || gsCustom.tenders.length === 0) return [];

        const gsSetting = JSON.parse(gsDefine.setting);
        if (!gsSetting.defaultCompare) return [];
        this.billsQueryField = ['contract_qty', 'contract_tp', 'qc_qty', 'qc_tp'];
        this.resultTree = new Ledger.gatherTree(this.ctx, {
            id: 'id',
            pid: 'pid',
            order: 'order',
            level: 'level',
            rootId: -1
        });
        for (const t of gsCustom.tenders) {
            const tender = await this.ctx.service.tender.getCheckTender(t.tid);

            switch (gsSetting.type) {
                case 'month':
                    await this._gatherMonthData(tender, gsCustom.month, gsSetting, this._gatherStageBillsData);
                    break;
                case 'final':
                    await this._gatherFinalData(tender, gsSetting, this._gatherStageBillsData);
                    break;
                case 'checked-final':
                    await this._gatherCheckedFinalData(tender, gsSetting, this._gatherStageBillsData);
                    break;
                case 'stage':
                    await this._gatherIndexData(tender, gsCustom.stage, gsSetting, this._gatherStageBillsData);
                    break;
                default: throw '未知汇总类型';
            }
        }
        this.resultTree.generateSortNodes();
        return this.resultTree.getDefaultDatas();
    }
}

/**
 * 奉建项目 定制报表
 * 变更过程管理表
 * 汇总工程量清单（区分正负变更）
 */

class fjHelper {
    constructor (ctx) {
        this.ctx = ctx;
    }
    async _getStageBillsData(tid, sid) {
        const helper = this.ctx.helper;
        const billsData = await this.ctx.app.mysql.select(this.ctx.service.ledger.tableName, {
            where: { tender_id: tid, is_leaf: 1 }
        });

        const preStage = this.ctx.stage.order > 1 ? await this.ctx.service.stageBillsFinal.getFinalData(this.ctx.tender, this.ctx.stage.order - 1) : [];
        const curStage = this.ctx.stage.readOnly
            ? await this.ctx.service.stageBills.getAuditorStageData2(tid, this.ctx.stage.id, this.ctx.stage.curTimes, this.ctx.stage.curOrder)
            : await this.ctx.service.stageBills.getLastestStageData2(tid, this.ctx.stage.id);
        const bpcStage = await this.ctx.service.stageBillsPc.getAllDataByCondition({ where: { sid: this.ctx.stage.id } });

        this.ctx.helper.assignRelaData(billsData, [
            { data: curStage, fields: ['qc_qty', 'qc_tp', 'qc_minus_qty'], prefix: '', relaId: 'lid' },
            { data: preStage, fields: ['qc_qty', 'qc_tp', 'qc_minus_qty'], prefix: 'pre_', relaId: 'lid' },
            { data: bpcStage, fields: ['contract_pc_tp', 'qc_pc_tp', 'pc_tp', 'org_price'], prefix: '', relaId: 'lid' },
        ]);
        billsData.forEach(node => {
            node.end_qc_qty = helper.add(node.pre_qc_qty, node.qc_qty);
            node.end_qc_tp = helper.sum([node.pre_qc_tp, node.qc_tp, node.qc_pc_tp]);
            node.final_qty = helper.add(node.quantity, node.end_qc_qty);
            node.final_tp = helper.add(node.total_price, node.end_qc_tp);
        });
        return billsData;
    }
    async _getStageImportChangeData(tid, sid) {
        const sql = 'SELECT sic.*, l.b_code, l.name, l.unit, l.unit_price, l.quantity, l.total_price' +
            '  FROM ' + this.ctx.service.stageImportChange.tableName + ' sic ' +
            '  LEFT JOIN (SELECT * FROM ' + this.ctx.service.ledger.tableName + ' WHERE tender_id = ? AND is_leaf) AS l ON sic.lid = l.id' +
            '  WHERE sic.tid = ? AND sic.sid <= ?';
        return await this.ctx.app.mysql.query(sql, [tid, tid, sid]);
    }
    convertChangeProgress(data, sid) {
        const helper = this.ctx.helper, tpDecimal = this.ctx.tender.info.decimal.tp;
        const bills = [];
        for (const d of data) {
            let b = bills.find(x => {
                return x.b_code === d.b_code && x.name === d.name && x.unit === d.unit && x.unit_price === d.unit_price;
            });
            if (!b) {
                b = { b_code: d.b_code, name: d.name, unit: d.unit, unit_price: d.unit_price, ledgerSource: [] };
                bills.push(b);
            }
            const ls = b.ledgerSource.find(x => { return x.lid === d.lid; });
            if (!ls) b.ledgerSource.push({lid: d.lid, quantity: d.quantity, total_price: d.total_price });
            const field = (d.sid < sid ? 'pre_' : '') + (d.rela_qty > 0 ? 'positive_' : 'negative_') + 'qc_qty';
            b[field] = this.ctx.helper.add(b[field], d.rela_qty);
        }
        bills.sort((a, b) => { return helper.compareCode(a.b_code, b.b_code); });

        const result = [];
        for (const b of bills) {
            b.pre_positive_qc_tp = helper.mul(b.unit_price, b.pre_positive_qc_qty, tpDecimal);
            b.pre_negative_qc_tp = helper.mul(b.unit_price, b.pre_negative_qc_qty, tpDecimal);
            b.positive_qc_tp = helper.mul(b.unit_price, b.positive_qc_qty, tpDecimal);
            b.negative_qc_tp = helper.mul(b.unit_price, b.negative_qc_qty, tpDecimal);
            b.end_positive_qc_qty = helper.add(b.pre_positive_qc_qty, b.positive_qc_qty);
            b.end_positive_qc_tp = helper.add(b.pre_positive_qc_tp, b.positive_qc_tp);
            b.end_negative_qc_qty = helper.add(b.pre_negative_qc_qty, b.negative_qc_qty);
            b.end_negative_qc_tp = helper.add(b.pre_negative_qc_tp, b.negative_qc_tp);
            b.final_qty = helper.add(b.quantity, helper.add(b.end_positive_qc_qty, b.end_negative_qc_qty));
            b.final_qty = helper.add(b.total_price, helper.add(b.end_positive_qc_tp, b.end_negative_qc_tp));
            result.push({
                b_code: b.b_code, name: b.name, unit: b.unit, unit_price: b.unit_price,
                quantity: b.quantity, total_price: b.total_price,
                pre_qc_qty: b.pre_positive_qc_qty, pre_qc_tp: b.pre_positive_qc_tp,
                qc_qty: b.positive_qc_qty, qc_tp: b.positive_qc_tp,
                end_qc_qty: b.end_positive_qc_qty, end_qc_tp: b.end_positive_qc_tp,
                final_qty: b.final_qty, final_tp: b.final_tp,
                minus: 0,
            });
            result.push({
                b_code: b.b_code, name: b.name, unit: b.unit, unit_price: b.unit_price,
                quantity: b.quantity, total_price: b.total_price,
                pre_qc_qty: b.pre_negative_qc_tp, pre_qc_tp: b.pre_negative_qc_tp,
                qc_qty: b.negative_qc_qty, qc_tp: b.negative_qc_tp,
                end_qc_qty: b.end_negative_qc_qty, end_qc_tp: b.end_negative_qc_tp,
                final_qty: b.final_qty, final_tp: b.final_tp,
                minus: 1,
            });
        }
        return result;
    }
    convertChangeProgress1(stageBills, stageImportChange, sid) {
        const helper = this.ctx.helper, tpDecimal = this.ctx.tender.info.decimal.tp;
        const bills = [];
        for (const sb of stageBills) {
            if (!sb.b_code) continue;
            let b = bills.find(x => { return x.b_code === sb.b_code && x.name === sb.name && x.unit === sb.unit && x.unit_price === sb.unit_price; });
            if (!b) {
                b = { b_code: sb.b_code, name: sb.name, unit: sb.unit, unit_price: sb.unit_price, org_price: sb.org_price, ledgerSource: [] };
                bills.push(b);
            }
            const ls = b.ledgerSource.find(x => { return x.lid === sb.lid; });
            if (!ls) b.ledgerSource.push({
                lid: sb.id,
                quantity: sb.quantity, total_price: sb.total_price,
                final_qty: sb.final_qty, final_tp: sb.final_tp,
            });
        }
        bills.sort((a, b) => { return helper.compareCode(a.b_code, b.b_code); });

        for (const d of stageImportChange) {
            const b = bills.find(x => { return x.ledgerSource.findIndex(y => { return y.lid === d.lid; }) >= 0; });
            if (!b) continue;

            const field = (d.sid < sid ? 'pre_' : '') + (d.rela_qty > 0 ? 'positive_' : 'negative_') + 'qc_qty';
            const minusField = (d.sid < sid ? 'pre_c_' : 'c_') + (d.rela_minus > 0 ? 'minus_' : '') + 'qc_qty';
            b[field] = this.ctx.helper.add(b[field], d.rela_qty);
            b[minusField] = this.ctx.helper.add(b[minusField], d.rela_qty);
        }

        const result = [], changeSum = {};
        for (const b of bills) {
            b.quantity = helper.sum(b.ledgerSource.map(x => { return x.quantity; }));
            b.total_price = helper.sum(b.ledgerSource.map(x => { return x.total_price; }));
            b.final_qty = helper.sum(b.ledgerSource.map(x => { return x.final_qty; }));
            b.final_tp = helper.sum(b.ledgerSource.map(x => { return x.final_tp; }));
            if (b.org_price) {
                b.pre_positive_qc_tp = helper.mul(b.org_price, b.pre_positive_qc_qty, tpDecimal);
                b.pre_negative_qc_tp = helper.mul(b.org_price, b.pre_negative_qc_qty, tpDecimal);
                b.positive_qc_pc_tp = helper.sub(helper.mul(b.unit_price, b.pre_positive_qc_qty, tpDecimal), b.pre_positive_qc_tp);
                b.negative_qc_pc_tp = helper.sub(helper.mul(b.unit_price, b.pre_negative_qc_qty, tpDecimal), b.pre_negative_qc_tp);
            } else {
                b.pre_positive_qc_tp = helper.mul(b.unit_price, b.pre_positive_qc_qty, tpDecimal);
                b.pre_negative_qc_tp = helper.mul(b.unit_price, b.pre_negative_qc_qty, tpDecimal);
                b.positive_qc_pc_tp = 0;
                b.negative_qc_pc_tp = 0;
            }
            b.positive_qc_tp = helper.mul(b.unit_price, b.positive_qc_qty, tpDecimal);
            b.negative_qc_tp = helper.mul(b.unit_price, b.negative_qc_qty, tpDecimal);
            b.end_positive_qc_qty = helper.add(b.pre_positive_qc_qty, b.positive_qc_qty);
            b.end_positive_qc_tp = helper.add(helper.add(b.pre_positive_qc_tp, b.positive_qc_tp), b.positive_qc_pc_tp);
            b.end_negative_qc_qty = helper.add(b.pre_negative_qc_qty, b.negative_qc_qty);
            b.end_negative_qc_tp = helper.add(helper.add(b.pre_negative_qc_tp, b.negative_qc_tp), b.negative_qc_pc_tp);

            if (b.org_price) {
                b.pre_c_qc_tp = helper.mul(b.org_price, b.pre_c_qc_qty, tpDecimal);
                b.pre_c_minus_qc_tp = helper.mul(b.org_price, b.pre_c_minus_qc_qty, tpDecimal);
                b.c_qc_pc_tp = helper.sub(helper.mul(b.unit_price, b.pre_c_qc_qty, tpDecimal), b.pre_c_qc_tp);
                b.c_minus_qc_pc_tp = helper.sub(helper.mul(b.unit_price, b.pre_c_minus_qc_qty, tpDecimal), b.pre_c_minus_qc_tp);
            } else {
                b.pre_c_qc_tp = helper.mul(b.unit_price, b.pre_c_qc_qty, tpDecimal);
                b.pre_c_minus_qc_tp = helper.mul(b.unit_price, b.pre_c_minus_qc_qty, tpDecimal);
                b.c_positive_qc_pc_tp = 0;
                b.c_negative_qc_pc_tp = 0;
            }
            b.c_qc_tp = helper.mul(b.unit_price, b.c_qc_qty, tpDecimal);
            b.c_minus_qc_tp = helper.mul(b.unit_price, b.c_minus_qc_qty, tpDecimal);
            b.end_c_qc_qty = helper.add(b.pre_c_qc_qty, b.c_qc_qty);
            b.end_c_qc_tp = helper.add(helper.add(b.pre_c_qc_tp, b.c_qc_tp), b.c_qc_pc_tp);
            b.end_c_minus_qc_qty = helper.add(b.pre_c_minus_qc_qty, b.c_minus_qc_qty);
            b.end_c_minus_qc_tp = helper.add(helper.add(b.pre_c_minus_qc_tp, b.c_minus_qc_tp), b.c_minus_qc_pc_tp);

            // 统计合计
            changeSum.pre_c_tp = helper.add(changeSum.pre_c_tp, b.pre_c_qc_tp);
            changeSum.c_tp = helper.add(changeSum.c_tp, b.c_qc_tp);
            changeSum.c_pc_tp = helper.add(changeSum.c_pc_tp, b.c_qc_pc_tp);
            changeSum.end_c_tp = helper.add(changeSum.end_c_tp, b.end_c_qc_tp);
            changeSum.pre_c_minus_tp = helper.add(changeSum.pre_c_minus_tp, b.pre_c_minus_qc_tp);
            changeSum.c_minus_tp = helper.add(changeSum.c_minus_tp, b.c_minus_qc_tp);
            changeSum.c_minus_pc_tp = helper.add(changeSum.c_minus_pc_tp, b.c_minus_qc_pc_tp);
            changeSum.end_c_minus_tp = helper.add(changeSum.end_c_minus_tp, b.end_c_minus_qc_tp);
            result.push({
                b_code: b.b_code, name: b.name, unit: b.unit, unit_price: b.unit_price,
                quantity: b.quantity, total_price: b.total_price,
                pre_qc_qty: b.pre_positive_qc_qty, pre_qc_tp: b.pre_positive_qc_tp,
                qc_qty: b.positive_qc_qty, qc_tp: b.positive_qc_tp, qc_pc_tp: b.positive_qc_pc_tp,
                end_qc_qty: b.end_positive_qc_qty, end_qc_tp: b.end_positive_qc_tp,
                final_qty: b.final_qty, final_tp: b.final_tp,
                minus: 0,
            });
            result.push({
                b_code: b.b_code, name: b.name, unit: b.unit, unit_price: b.unit_price,
                quantity: b.quantity, total_price: b.total_price,
                pre_qc_qty: b.pre_negative_qc_qty, pre_qc_tp: b.pre_negative_qc_tp,
                qc_qty: b.negative_qc_qty, qc_tp: b.negative_qc_tp, qc_pc_tp: b.negative_qc_pc_tp,
                end_qc_qty: b.end_negative_qc_qty, end_qc_tp: b.end_negative_qc_tp,
                final_qty: b.final_qty, final_tp: b.final_tp,
                minus: 1,
            });
        }
        return { mem_fj_change_progress: result, mem_fj_change_sum: [changeSum] };
    }
    async getChangeProgressData(tid, sid) {
        await this.ctx.service.stage.checkStage(sid);
        const stageBills = await this._getStageBillsData(tid, sid);
        const stageImportChange = await this._getStageImportChangeData(tid, sid);
        return this.convertChangeProgress1(stageBills, stageImportChange, sid);
    }
}

module.exports = {
    jhHelper,
    fjHelper,
};