Calculation/app/service/report_memory.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */

const _ = require('lodash');

const StageIm = require('../lib/stage_im');
const imType = require('../const/tender').imType;
const audit = require('../const/audit');
const changeConst = require('../const/change');
const materialConst = require('../const/material');
// const path = require('path');
// const fs = require('fs');

const stageImTz = 'mem_stage_im_tz';
const stageImTzBills = 'mem_stage_im_tz_bills';
const stageImZl = 'mem_stage_im_zl';
const stageImVersion = '1.3';

const Ledger = require('../lib/ledger');

const billsFields = (function () {
    const cur = ['contract_qty', 'contract_tp', 'contract_expr', 'qc_qty', 'qc_tp', 'gather_qty', 'gather_tp', 'postil'];
    const pre = ['pre_contract_qty', 'pre_contract_tp', 'pre_qc_qty', 'pre_qc_tp', 'pre_gather_qty', 'pre_gather_tp'];
    const end = ['end_contract_qty', 'end_contract_tp', 'end_qc_qty', 'end_qc_tp', 'end_gather_qty', 'end_gather_tp'];
    const final = ['final_tp', 'final_ratio'];
    const stageDgn = ['deal_dgn_qty1', 'deal_dgn_qty2', 'c_dgn_qty1', 'c_dgn_qty2'];

    const stage = cur.concat(pre, end, final);
    const stageEnd = pre.concat(end, final);
    const bgl = ['qc_bgl_code'];
    const leafXmj = ['leaf_xmj_id'];

    return {cur, pre, end, final, stageDgn, stage, stageEnd, bgl, leafXmj};
})();
const posFields = (function () {
    const cur = ['contract_qty', 'qc_qty', 'gather_qty', 'postil'];
    const pre = ['pre_contract_qty', 'pre_qc_qty', 'pre_gather_qty'];
    const end = ['end_contract_qty', 'end_qc_qty', 'end_gather_qty'];
    const final = ['final_ratio'];

    const stage = cur.concat(pre, end, final);
    const stageEnd = pre.concat(end, final);
    const bgl = ['qc_bgl_code'];

    return {cur, pre, end, final, stage, stageEnd, bgl};
})();

module.exports = app => {
    class ReportMemory extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局context
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            const self = this;
            this.tableName = 'report_memory';
            // 基础数据类
            // mainData
            this.pos = new Ledger.pos({
                id: 'id', ledgerId: 'lid',
                updateFields: ['contract_qty', 'qc_qty', 'postil'],
                calc: function (p) {
                    p.pre_gather_qty = ctx.helper.add(p.pre_contract_qty, p.pre_qc_qty);
                    p.gather_qty = ctx.helper.add(p.contract_qty, p.qc_qty);
                    p.end_contract_qty = self.ctx.helper.add(p.pre_contract_qty, p.contract_qty);
                    p.end_qc_qty = self.ctx.helper.add(p.pre_qc_qty, p.qc_qty);
                    p.end_gather_qty = self.ctx.helper.add(p.pre_gather_qty, p.gather_qty);
                }
            });
            // 需要缓存的数据
            this.stageImData = null;
            this.changeData = null;
            this.changeApplyData = null;
            this.changeProjectData = null;
            this.stageValidRole = [];
        }

        _getNewBillsTree(calcFields) {
            return new Ledger.billsTree(this.ctx, {
                id: 'ledger_id',
                pid: 'ledger_pid',
                order: 'order',
                level: 'level',
                rootId: -1,
                keys: ['id', 'tender_id', 'ledger_id'],
                stageId: 'id',
                calcFields: calcFields || ['deal_tp', 'total_price', 'contract_tp', 'qc_tp', 'gather_tp', 'pre_contract_tp', 'pre_qc_tp', 'pre_gather_tp'],
                calc: function (node, helper) {
                    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);

                    node.final_tp = helper.add(node.total_price, node.end_qc_tp);
                    node.final_ratio = helper.mul(helper.div(node.end_gather_tp, node.final_tp, 4), 100);
                }
            });
        }

        _checkFieldsExist(source, check) {
            for (const s of source) {
                if (check.indexOf(s) >= 0) {
                    return true;
                }
            }
            return false;
        }
        _checkFieldsExistReg(source, regStr) {
            const reg = new RegExp(regStr, 'igm');
            for (const s of source) {
                if (reg.test(s)) {
                    return true;
                }
            }
            return false;
        }

        // build-time: 162-384ms, redis-cache: 0-41ms, mysql + IO: 116-146ms
        // 一定程度上算是大Value缓存，数据多了以后：
        // 1. 达到redis内存阈值时，数据会swap到磁盘，此时将消耗IO时间
        // 2. redis单独服务器
        // 3. redis集群
        async _getReportMemoryCache(name, tid, sid, time, version = '') {
            // redis
            const cacheKey = name + '-t' + tid + (sid ? '-s' + sid : '') + (time ? '-' + time : '') + version;
            const data = await this.cache.get(cacheKey);
            if (data) {
                return eval(data);
            } else {
                return null;
            }

            // mysql + IO
            // const rm = await this.getDataByCondition({
            //     tid: tid, sid: sid, name: name, time: time
            // });
            // if (rm && rm.file) {
            //     const file = path.join(this.ctx.app.config.filePath, 'report', 'cache', rm.file);
            //     if (fs.existsSync(file)) {
            //         const data = await fs.readFileSync(file, 'utf8');
            //         return eval(data);
            //     } else {
            //         return null;
            //     }
            // }
        }

        async _setReportMemoryCache(name, tid, sid, time, data, version = '') {
            // redis
            const cacheKey = name + '-t' + tid + (sid ? '-s' + sid : '') + (time ? '-' + time : '') + version;
            this.cache.set(cacheKey, JSON.stringify(data), 'EX', this.ctx.app.config.cacheTime);

            // mysql + IO
            // const file = path.join('report', 'cache', 'rm' + (new Date()).getTime() + '.json');
            // await this.ctx.helper.saveBufferFile(JSON.stringify(data), path.join(this.ctx.app.config.filePath, file));
            // const rm = await this.getDataByCondition({
            //     tid: tid, sid: sid, name: name, time: time
            // });
            // if (rm) {
            //     await this.db.update(this.tableName, {id: rm.id, file: file});
            // } else {
            //     await this.db.insert(this.tableName, {tid: tid, sid: sid, name: name, time: time, file: file});
            // }
        }

        async _generateStageIm(tid, sid, isTz = true) {
            if (isTz && [imType.tz.value, imType.bb.value].indexOf(this.ctx.stage.im_type) === -1) {
                throw '您查看的报表跟设置不符，请查看“总量控制”的报表';
            } else if (!isTz && [imType.zl.value, imType.bw.value].indexOf(this.ctx.stage.im_type) === -1) {
                throw '您查看的报表跟设置不符，请查看“0号台账”的报表';
            }
            const stageIm = new StageIm(this.ctx);
            await stageIm.buildImData();
            this.stageImData.main = stageIm.ImData;
            if (isTz) {
                this.stageImData.bills = stageIm.ImBillsData;
                await this._setReportMemoryCache(stageImTz, tid, sid, this.ctx.stage.cacheTime, this.stageImData.main, stageImVersion);
                await this._setReportMemoryCache(stageImTzBills, tid, sid, this.ctx.stage.cacheTime, this.stageImData.bills, stageImVersion);
            } else {
                await this._setReportMemoryCache(stageImZl, tid, sid, this.ctx.stage.cacheTime, this.stageImData.main, stageImVersion);
            }
        }

        async getStageImTzData(tid, sid, fields, readCache = true) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                if (readCache) {
                    const cache = await this._getReportMemoryCache('mem_stage_im_tz', tid, sid, this.ctx.stage.cacheTime, stageImVersion);
                    if (cache) return cache;
                }

                if (!this.stageImData) {
                    this.stageImData = {};
                    try {
                        await this._generateStageIm(tid, sid);
                    } catch (err) {
                        if (err.stack) {
                            this.ctx.logger.error(err);
                        }
                        this.stageImData.main = err.stack ? '数据错误' : err;
                        this.stageImData.bills = this.stageImData.main;
                    }
                }
                return this.stageImData.main;
            } catch (err) {
                return [];
            }
        }

        async getStageImTzBillsData(tid, sid, fields, readCache = true) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                if (readCache) {
                    const cache = await this._getReportMemoryCache('mem_stage_im_tz_bills', tid, sid, this.ctx.stage.cacheTime, stageImVersion);
                    if (cache) return cache;
                }

                if (!this.stageImData) {
                    this.stageImData = {};
                    try {
                        await this._generateStageIm(tid, sid);
                    } catch (err) {
                        if (err.stack) {
                            this.ctx.logger.error(err);
                        }
                        this.stageImData.main = err.stack ? '数据错误' : err;
                        this.stageImData.bills = this.stageImData.main;
                    }
                }
                return this.stageImData.bills;
            } catch (err) {
                return [];
            }
        }

        async getStageImZlData(tid, sid, fields, readCache = true) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                if (readCache) {
                    const cache = await this._getReportMemoryCache('mem_stage_im_zl', tid, sid, this.ctx.stage.cacheTime, stageImVersion);
                    if (cache) return cache;
                }

                this.stageImData = {};
                try {
                    await this._generateStageIm(tid, sid, false);
                } catch (err) {
                    if (err.stack) {
                        this.ctx.logger.error(err);
                    }
                    this.stageImData.main = err.statck ? '数据错误' : err;
                }
                return this.stageImData.main;
            } catch (err) {
                return [];
            }
        }

        async getMonthProgress(tid, fields) {
            const helper = this.ctx.helper;
            await this.ctx.service.tender.checkTender(tid);
            const tender = this.ctx.tender;

            const stages = await this.ctx.service.stage.getValidStages(tender.id);
            const lastStage = stages.length > 0 ? stages[0] : null;
            if (lastStage) {
                await this.ctx.service.stage.checkStageGatherData(lastStage);
                tender.gather_tp = helper.add(lastStage.contract_tp, lastStage.qc_tp);
                tender.end_contract_tp = helper.add(lastStage.contract_tp, lastStage.pre_contract_tp);
                tender.end_qc_tp = helper.add(lastStage.qc_tp, lastStage.pre_qc_tp);
                tender.end_gather_tp = helper.add(tender.end_contract_tp, tender.end_qc_tp);
                tender.pre_gather_tp = helper.add(lastStage.pre_contract_tp, lastStage.pre_qc_tp);
                tender.yf_tp = lastStage.yf_tp;
                tender.qc_ratio = helper.mul(helper.div(tender.end_qc_tp, tender.info.deal_param.contractPrice, 2), 100);
                tender.sum = helper.add(tender.total_price, tender.end_qc_tp);
                tender.pre_ratio = helper.mul(helper.div(tender.pre_gather_tp, tender.sum, 2), 100);
                tender.cur_ratio = helper.mul(helper.div(tender.gather_tp, tender.sum, 2), 100);
                tender.other_tp = helper.sub(helper.sub(tender.sum, tender.pre_gather_tp), tender.gather_tp);
                tender.other_ratio = Math.max(0, 100 - tender.pre_ratio - tender.cur_ratio);
            }
            const monthProgress = [];
            for (const s of stages) {
                if (s.s_time) {
                    let progress = monthProgress.find(function (x) {
                        return x.month === s.s_time;
                    });
                    if (!progress) {
                        progress = {month: s.s_time};
                        monthProgress.push(progress);
                    }
                    progress.tp = helper.add(helper.add(progress.tp, s.contract_tp), s.qc_tp);
                }
            }
            monthProgress.sort(function (x, y) {
                return Date.parse(x.month) - Date.parse(y.month);
            });
            let sum = 0;
            for (const p of monthProgress) {
                p.ratio = helper.mul(helper.div(p.tp, tender.sum, 4), 100);
                sum = helper.add(sum, p.tp);
                p.end_tp = sum;
                p.end_ratio = helper.mul(helper.div(p.end_tp, tender.sum, 4), 100);
            }
            return monthProgress;
        }

        async _calcBillsBgl(billsTree) {
            if (!this.ctx.stage) return;

            const helper = this.ctx.helper;
            const tender = this.ctx.tender;
            const stage = this.ctx.stage;
            const bglData = this.ctx.stage.readOnly
                ? await this.ctx.service.stageChange.getAuditorAllStageData(tender.id, stage.id, stage.curTimes, stage.curOrder)
                : await this.ctx.service.stageChange.getLastestAllStageData(tender.id, stage.id);

            for (const node of billsTree.nodes) {
                node.qc_bgl_code = '';
                if (node.children && node.children.length > 0) continue;

                const nodeBgl = helper._.filter(bglData, {lid: node.id});
                if (nodeBgl.length === 0) continue;
                helper._.pullAll(bglData, nodeBgl);
                const validBgl = helper._.filter(nodeBgl, function (x) {
                    return !helper.checkZero(x.qty);
                });
                node.qc_bgl_code = helper._.uniq(helper._.map(validBgl, 'c_code')).join(';');
            }
        }

        _calcLeafXmjRela(billsTree) {
            for (const node of billsTree.nodes) {
                if (node.b_code) {
                    const leafXmj = billsTree.getLeafXmjParent(node);
                    node.leaf_xmj_id = leafXmj ? leafXmj.id : -1;
                }
            }
        }

        async getStageBillsData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                if (sid) {
                    await this.ctx.service.stage.checkStage(sid);
                }

                const billsData = await this.ctx.service.ledger.getData(this.ctx.tender.id);
                if (this._checkFieldsExist(fields, billsFields.stageDgn)) {
                    const dgnData = await this.ctx.service.stageBillsDgn.getDgnData(this.ctx.tender.id);
                    for (const d of dgnData) {
                        const l = this.ctx.helper._.find(billsData, {id: d.id});
                        this.ctx.helper._.assignIn(l, d);
                    }
                }
                if (this._checkFieldsExist(fields, billsFields.stage)) {
                    if (this.ctx.stage.readOnly) {
                        const curStage = await this.ctx.service.stageBills.getAuditorStageData2(this.ctx.tender.id,
                            this.ctx.stage.id, this.ctx.stage.curTimes, this.ctx.stage.curOrder);
                        this.ctx.helper.assignRelaData(billsData, [
                            {data: curStage, fields: ['contract_qty', 'contract_tp', 'contract_expr', 'qc_qty', 'qc_tp', 'postil'], prefix: '', relaId: 'lid'}
                        ]);
                    } else {
                        const curStage = await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id);
                        this.ctx.helper.assignRelaData(billsData, [
                            {data: curStage, fields: ['contract_qty', 'contract_tp', 'contract_expr', 'qc_qty', 'qc_tp', 'postil'], prefix: '', relaId: 'lid'}
                        ]);
                    }
                }
                if (this._checkFieldsExist(fields, billsFields.stageEnd)) {
                    const preStage = this.ctx.stage.order > 1 ? await this.ctx.service.stageBillsFinal.getFinalData(this.ctx.tender, this.ctx.stage.order - 1) : [];
                    this.ctx.helper.assignRelaData(billsData, [
                        {data: preStage, fields: ['contract_qty', 'contract_tp', 'qc_qty', 'qc_tp'], prefix: 'pre_', relaId: 'lid'}
                    ]);
                }

                const billsTree = this._getNewBillsTree();
                billsTree.loadDatas(billsData);
                billsTree.calculateAll();

                if (this._checkFieldsExist(fields, billsFields.bgl)) {
                    await this._calcBillsBgl(billsTree);
                }

                if (this._checkFieldsExist(fields, billsFields.leafXmj)) {
                    this._calcLeafXmjRela(billsTree);
                }

                return billsTree.getDefaultDatas();
            } catch(err) {
                return []
            }
        }

        async _calcPosBgl() {
            if (!this.ctx.stage) return;

            const helper = this.ctx.helper;
            const tender = this.ctx.tender;
            const stage = this.ctx.stage;
            const bglData = this.ctx.stage.readOnly
                ? await this.ctx.service.stageChange.getAuditorAllStageData(tender.id, stage.id, stage.curTimes, stage.curOrder)
                : await this.ctx.service.stageChange.getLastestAllStageData(tender.id, stage.id);

            for (const p of this.pos.datas) {
                p.qc_bgl_code = '';

                const pBgl = helper._.filter(bglData, {lid: p.lid, pid: p.id});
                if (pBgl.length === 0) continue;
                helper._.pullAll(bglData, pBgl);
                const validBgl = helper._.filter(pBgl, function (x) {
                    return !helper.checkZero(x.qty);
                });
                p.qc_bgl_code = helper._.uniq(helper._.map(validBgl, 'c_code')).join(';');
            }
        }

        async getStagePosData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                if (sid) {
                    await this.ctx.service.stage.checkStage(sid);
                }

                const posData = await this.ctx.service.pos.getAllDataByCondition({ where: {tid: this.ctx.tender.id }});
                if (this._checkFieldsExist(fields, posFields.stage)) {
                    if (this.ctx.stage.readOnly) {
                        const curPosStage = await this.ctx.service.stagePos.getAuditorStageData2(this.ctx.tender.id,
                            this.ctx.stage.id, this.ctx.stage.curTimes, this.ctx.stage.curOrder);
                        this.ctx.helper.assignRelaData(posData, [
                            {data: curPosStage, fields: ['contract_qty', 'qc_qty', 'contract_expr', 'postil'], prefix: '', relaId: 'pid'}
                        ]);
                    } else {
                        const curPosStage = await this.ctx.service.stagePos.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id);
                        this.ctx.helper.assignRelaData(posData, [
                            {data: curPosStage, fields: ['contract_qty', 'qc_qty', 'contract_expr', 'postil'], prefix: '', relaId: 'pid'}
                        ]);
                    }
                }
                if (this._checkFieldsExist(fields, posFields.stageEnd)) {
                    const prePosStage = this.ctx.stage.order > 1 ? await this.ctx.service.stagePosFinal.getFinalData(this.ctx.tender, this.ctx.stage.order - 1) : [];
                    this.ctx.helper.assignRelaData(posData, [
                        {data: prePosStage, fields: ['contract_qty', 'qc_qty'], prefix: 'pre_', relaId: 'pid'}
                    ]);
                }
                this.pos.loadDatas(posData);
                this.pos.calculateAll();

                if (this._checkFieldsExist(fields, posFields.bgl)) {
                    await this._calcPosBgl();
                }

                return this.pos.getDatas();
            } catch (err) {
                return [];
            }
        }

        _getStageValidRole () {
            if (!this.ctx.stage) throw '期数据错误，请重试';
            if (this.stageValidRole && this.stageValidRole.length > 0) return;

            const result = [{dataOrder: 0, flowOrder: 0, uid: this.ctx.stage.user_id}];
            for (const auditor of this.ctx.stage.auditors) {
                if (auditor.status === audit.stage.status.checked ||
                    (auditor.status === audit.stage.status.checking && !this.ctx.stage.readOnly)) {
                    const role = result.find(function (r) {
                        return r.uid === auditor.aid;
                    });
                    if (role) {
                        role.dataOrder = auditor.order;
                    } else {
                        result.push({
                            dataOrder: auditor.order,
                            flowOrder: result.length,
                            uid: auditor.aid
                        })
                    }
                }
            }
            this.stageValidRole = result;
        };

        async getStageBillsCompareData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                await this._getStageValidRole();

                const stage = this.ctx.stage, helper = this.ctx.helper;
                const validRole = this.stageValidRole;
                const billsData = await this.ctx.service.ledger.getData(this.ctx.tender.id);
                const allStageBills = await this.ctx.service.stageBills.getAllDataByCondition({where: {sid: sid}});

                const stageBillsIndex = {}, timesLen = 100;
                for (const role of validRole) {
                    const stageBills = this.ctx.helper._.filter(allStageBills, function (x) {
                        return x.times < stage.curTimes || (x.times === stage.curTimes && x.order <= role.dataOrder);
                    });
                    this.ctx.helper._.pullAll(allStageBills, stageBills);
                    for (const sb of stageBills) {
                        const key = 'sb-' + sb.lid;
                        const sbi = stageBillsIndex[key];
                        if (sbi) {
                            if ((sbi.times * timesLen + sbi.order) < (sb.times * timesLen + sb.order)) stageBillsIndex[key] = sb;
                        } else {
                            stageBillsIndex[key] = sb;
                        }
                    }
                    const filterStageBills = [];
                    for (const prop in stageBillsIndex) {
                        filterStageBills.push(stageBillsIndex[prop]);
                    }
                    this.ctx.helper.assignRelaData(billsData, [
                        {data: filterStageBills, fields: ['contract_qty', 'contract_tp', 'qc_qty', 'qc_tp'], prefix: 'r' + role.flowOrder + '_', relaId: 'lid'}
                    ]);
                }

                if (this._checkFieldsExist(fields, billsFields.stageEnd)) {
                    const preStage = this.ctx.stage.order > 1 ? await this.ctx.service.stageBillsFinal.getFinalData(this.ctx.tender, this.ctx.stage.order - 1) : [];
                    this.ctx.helper.assignRelaData(billsData, [
                        {data: preStage, fields: ['contract_qty', 'contract_tp', 'qc_qty', 'qc_tp'], prefix: 'pre_', relaId: 'lid'}
                    ]);
                }
                const billsTree = this._getNewBillsTree();
                billsTree.loadDatas(billsData);

                for (const role of validRole) {
                    const prefix = 'r' + role.flowOrder + '_';
                    billsTree.setting.calcFields.push(prefix + 'contract_tp', prefix + 'qc_tp', prefix + 'gather_tp');
                }

                billsTree.calculateAll(function(node) {
                    let prefix = '';
                    if (node.children && node.children.length === 0) {
                        node.pre_gather_qty = helper.add(node.pre_contract_qty, node.pre_qc_qty);
                        for (const role of validRole) {
                            prefix = 'r' + role.flowOrder + '_';
                            node[prefix + 'gather_qty'] = helper.add(node[prefix + 'contract_qty'], node[prefix + 'qc_qty']);
                        }
                    }
                    node.pre_gather_tp = helper.add(node.pre_contract_tp, node.pre_qc_tp);
                    for (const role of validRole) {
                        prefix = 'r' + role.flowOrder + '_';
                        node[prefix + 'gather_tp'] = helper.add(node[prefix + 'contract_tp'], node[prefix + 'qc_tp']);
                    }
                });

                if (this._checkFieldsExist(fields, billsFields.leafXmj)) {
                    this._calcLeafXmjRela(billsTree);
                }

                return billsTree.getDefaultDatas();
                // return billsTree.getDatas([
                //     'id', 'tender_id', 'ledger_id', 'ledger_pid', 'level', 'order', 'full_path', 'is_leaf',                 //8
                //     'code', 'b_code', 'name', 'unit', 'unit_price',                                                         //5
                //     'deal_qty', 'deal_tp', 'quantity', 'total_price', 'dgn_qty1', 'dgn_qty2',                               //6
                //     'drawing_code', 'memo', 'node_type', 'is_tp',                                                           //4
                //     'r0_contract_qty', 'r0_contract_tp', 'r0_qc_qty', 'r0_qc_tp', 'r0_gather_qty', 'r0_gather_tp',          //6
                //     'r1_contract_qty', 'r1_contract_tp', 'r1_qc_qty', 'r1_qc_tp', 'r1_gather_qty', 'r1_gather_tp',
                //     'r2_contract_qty', 'r2_contract_tp', 'r2_qc_qty', 'r2_qc_tp', 'r2_gather_qty', 'r2_gather_tp',
                //     'r3_contract_qty', 'r3_contract_tp', 'r3_qc_qty', 'r3_qc_tp', 'r3_gather_qty', 'r3_gather_tp',
                //     'r4_contract_qty', 'r4_contract_tp', 'r4_qc_qty', 'r4_qc_tp', 'r4_gather_qty', 'r4_gather_tp',
                //     'r5_contract_qty', 'r5_contract_tp', 'r5_qc_qty', 'r5_qc_tp', 'r5_gather_qty', 'r5_gather_tp',
                //     'r6_contract_qty', 'r6_contract_tp', 'r6_qc_qty', 'r6_qc_tp', 'r6_gather_qty', 'r6_gather_tp',
                //     'r7_contract_qty', 'r7_contract_tp', 'r7_qc_qty', 'r7_qc_tp', 'r7_gather_qty', 'r7_gather_tp',
                //     'r8_contract_qty', 'r8_contract_tp', 'r8_qc_qty', 'r8_qc_tp', 'r8_gather_qty', 'r8_gather_tp',
                //     'r9_contract_qty', 'r9_contract_tp', 'r9_qc_qty', 'r9_qc_tp', 'r9_gather_qty', 'r9_gather_tp',
                //     'r10_contract_qty', 'r10_contract_tp', 'r10_qc_qty', 'r10_qc_tp', 'r10_gather_qty', 'r10_gather_tp',
                //     'pre_contract_qty', 'pre_contract_tp', 'pre_qc_qty', 'pre_qc_tp', 'pre_gather_qty', 'pre_gather_tp',
                //     'chapter',                                                                                              //1
                // ]);
            } catch (err) {
                return [];
            }
        }

        async getStagePosCompareData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                await this._getStageValidRole();

                const stage = this.ctx.stage, helper = this.ctx.helper;
                const validRole = this.stageValidRole;

                const allStagePos = await this.ctx.service.stagePos.getAllDataByCondition({where: {sid: sid}});
                const posData = await this.ctx.service.pos.getAllDataByCondition({ where: {tid: this.ctx.tender.id }});
                const prePosStage = this.ctx.stage.order > 1 ? await this.ctx.service.stagePosFinal.getFinalData(this.ctx.tender, this.ctx.stage.order - 1) : [];
                this.ctx.helper.assignRelaData(posData, [
                    {data: prePosStage, fields: ['contract_qty', 'qc_qty'], prefix: 'pre_', relaId: 'pid'}
                ]);
                const stagePosIndex = {}, timesLen = 100;
                for (const role of validRole) {
                    const stagePos = this.ctx.helper._.filter(allStagePos, function (x) {
                        return x.times < stage.curTimes || (x.times === stage.curTimes && x.order <= role.dataOrder);
                    });
                    this.ctx.helper._.pullAll(allStagePos, stagePos);
                    for (const sp of stagePos) {
                        const key = 'sp-' + sp.pid;
                        const spi = stagePosIndex[key];
                        if (spi) {
                            if ((spi.times * timesLen + spi.order) < (sp.times * timesLen + sp.order)) stagePosIndex[key] = sp;
                        } else {
                            stagePosIndex[key] = sp;
                        }
                    }
                    const filterStagePos = [];
                    for (const prop in stagePosIndex) {
                        filterStagePos.push(stagePosIndex[prop]);
                    }
                    this.ctx.helper.assignRelaData(posData, [
                        {data: filterStagePos, fields: ['contract_qty', 'qc_qty'], prefix: 'r' + role.flowOrder + '_', relaId: 'pid'}
                    ]);
                }
                this.pos.loadDatas(posData);
                this.pos.calculateAll(function (p) {
                    p.pre_gather_qty = helper.add(p.pre_contract_qty, p.pre_qc_qty);
                    for (const role of validRole) {
                        const prefix = 'r' + role.flowOrder + '_';
                        p[prefix + 'gather_qty'] = helper.add(p[prefix + 'contract_qty'], p[prefix + 'qc_qty']);
                    }

                });

                return this.pos.getDatas();
            } catch (err) {
                return [];
            }
        }

        async getStagePayData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);
                const stage = this.ctx.stage;

                const dealPay = await this.ctx.service.stagePay.getStagePays(this.ctx.stage);

                if (!this.ctx.stage.readOnly) {
                    // 计算 本期金额
                    const PayCalculator = require('../lib/pay_calc');
                    const payCalculator = new PayCalculator(this.ctx, this.ctx.stage, this.ctx.tender.info);
                    await payCalculator.calculateAll(dealPay);
                }
                if (this._checkFieldsExist(fields, ['start_stage_order']) && this.ctx.stage.status !== audit.stage.status.checked) {
                    for (const dp of dealPay) {
                        if (!dp.start_stage_order || dp.start_stage_order === this.ctx.stage.order) {
                            dp.start_stage_order = this.ctx.helper.checkZero(dp.tp) ? null : this.ctx.stage.order;
                        }
                    }
                }

                if (this._checkFieldsExistReg(fields, 'r[0-9]+_tp')) {
                    this._getStageValidRole();
                    const allStagePays = await this.ctx.service.stagePay.getAllDataByCondition({
                        where: {sid: stage.id, stimes: stage.curTimes}
                    });

                    for (const [i, role] of this.stageValidRole.entries()) {
                        if (i < this.stageValidRole.length - 1) {
                            const stagePays = this.ctx.helper._.filter(allStagePays, function (x) {
                                return x.stimes === stage.curTimes && x.sorder === role.dataOrder;
                            });
                            this.ctx.helper._.pullAll(allStagePays, stagePays);

                            for (const sp of stagePays) {
                                const dp = dealPay.find(function (x) {return x.pid === sp.pid});
                                if (dp) {
                                    dp['r' + role.flowOrder + '_tp'] = sp.tp;
                                }
                            }
                        } else {
                            for (const dp of dealPay) {
                                dp['r' + role.flowOrder + '_tp'] = dp.tp;
                            }
                        }
                    }
                }
                return dealPay;
            } catch (err) {
                return [];
            }
        }

        async getStageChangeBillsData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                if (this.ctx.stage.readOnly) {
                    return await this.ctx.service.stageChange.getReportAuditorAllStageData(tid, sid,
                        this.ctx.stage.curTimes, this.ctx.stage.curOrder);
                } else {
                    return await this.ctx.service.stageChange.getReportLastestAllStageData(tid, sid);
                }
            } catch (err) {
                return [];
            }
        }

        _getChangeConstName(define, value) {
            for (const prop in define) {
                if (define[prop].value === value) {
                    return define[prop].name;
                }
            }
            return '';
        }

        async _generateChange(tid) {
            if (this.changeData !== null) return;
            const self = this;
            try {
                const decimal = this.ctx.tender.info.decimal, ctx = this.ctx;
                const where = { tid, valid: 1 };
                if (this.ctx.session.sessionProject.page_show.isOnlyChecked) where.status = audit.flow.status.checked;
                const change = await this.ctx.service.change.getAllDataByCondition({ where, orders: [['in_time', 'desc']] });
                for (const c of change) {
                    const types = ctx.helper._.map(c.type.split(','), function (t) {
                        return self._getChangeConstName(changeConst.type, ctx.helper._.toInteger(t));
                    });
                    c.type = types.join(';');
                    c.class = this._getChangeConstName(changeConst.class, c.class);
                    c.quality = this._getChangeConstName(changeConst.quality, c.quality);
                    c.charge = this._getChangeConstName(changeConst.charge, c.charge);
                    c.attachments = await ctx.service.changeAtt.getChangeAttachment(c.cid);
                    const names = ctx.helper._.map(c.attachments, function (x) {
                        return x.filename + x.fileext;
                    });
                    c.attNames = names.join('\n');
                }
                const changeBills = await this.ctx.service.changeAuditList.getChangeAuditBills(tid, this.ctx.session.sessionProject.page_show.isOnlyChecked);
                for (const d of changeBills) {
                    d.o_qty = d.oamount;
                    d.o_tp = this.ctx.helper.mul(d.o_qty, d.unit_price, decimal.tp);
                    d.c_qty = d.camount;
                    d.c_tp = this.ctx.helper.mul(d.c_qty, d.unit_price, decimal.tp);
                    d.s_qty = d.samount ? parseFloat(d.samount) : 0;
                    d.s_tp = this.ctx.helper.mul(d.s_qty, d.unit_price, decimal.tp);
                    d.sp_qty = d.spamount;
                    d.sp_tp = this.ctx.helper.mul(d.sp_qty, d.unit_price, decimal.tp);

                    const auditAmount = d.audit_amount ? d.audit_amount.split(',') : [];
                    const relaChange = ctx.helper._.find(change, {cid: d.cid});
                    for (const [i, aa] of auditAmount.entries()) {
                        const amountField = 'qty_' + (i+1), tpField = 'tp_' + (i+1);
                        d[amountField] = aa ? parseFloat(aa) : 0;
                        d[tpField] = ctx.helper.mul(d[amountField], d.unit_price, decimal.tp);
                        if (relaChange) {
                            relaChange[tpField] = ctx.helper.add(relaChange[tpField], d[tpField]);
                        }
                    }
                }

                change.sort(function (a, b) {
                    return a.code.localeCompare(b.code);
                });
                changeBills.sort(function (a, b) {
                    const aCIndex = change.findIndex(function (c) {
                        return c.cid === a.cid;
                    });
                    const bCIndex = change.findIndex(function (c) {
                        return c.cid === b.cid;
                    });
                    return aCIndex === bCIndex
                        ? ctx.helper.compareCode(a.code, b.code)
                        : aCIndex - bCIndex;
                });
                this.changeData = {change: change, bills: changeBills};
            } catch(err) {
                this.ctx.helper.log(err);
                throw err;
                this.changeData = {change: [], bills: []};
            }
        }

        async _generateChangeAudit() {
            if (!!this.changeData.audit) return;

            this.changeData.audit = [];
            for (const c of this.changeData.change) {
                const changeAudit = await this.ctx.service.changeAudit.getListGroupByTimes(c.cid, c.times);
                this.changeData.audit.push(...changeAudit);
            }
        }

        async getChangeData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);

                await this._generateChange(tid);
                return this.changeData.change;
            } catch (err) {
                return [];
            }
        }

        async getChangeBillsData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);

                await this._generateChange(tid);
                return this.changeData.bills;
            } catch (err) {
                return [];
            }
        }

        async getChangeAuditData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);

                await this._generateChange(tid);
                await this._generateChangeAudit();
                return this.changeData.audit;
            } catch (err) {
                return [];
            }
        }

        async _generateChangeApply(tid) {
            if (!!this.changeApplyData) return;
            const helper = this.ctx.helper;
            const decimal = this.ctx.tender.info.decimal;
            const where = { tid };
            if (this.ctx.session.sessionProject.page_show.isOnlyChecked) where.status = audit.changeApply.status.checked;
            const apply = await this.ctx.service.changeApply.getAllDataByCondition({ where });
            const bills = await this.ctx.service.changeApplyList.getChangeBills(tid, this.ctx.session.sessionProject.page_show.isOnlyChecked);
            bills.forEach(x => {
                const a = apply.find(y => { return y.id === x.caid });
                const a_decimal = a.decimal ? JSON.stringify(a.decimal) : { tp: decimal.tp, up: decimal.up };
                x.o_tp = helper.mul(x.oamount, x.unit_price, a_decimal.tp);
                x.c_tp = helper.mul(x.camount, x.unit_price, a_decimal.tp);
            });
            bills.sort(function(a, b ) {
                const aCIndex = apply.findIndex(function (c) {
                    return c.id === a.caid;
                });
                const bCIndex = apply.findIndex(function (c) {
                    return c.id === b.caid;
                });
                return aCIndex === bCIndex
                    ? helper.compareCode(a.code, b.code)
                    : aCIndex - bCIndex;
            });
            this.changeApplyData = { data: apply, bills };
        }

        async _generateChangeApplyAudit() {
            if (!!this.changeApplyData.audit) return;

            this.changeApplyData.audit = [];
            for (const c of this.changeApplyData.data) {
                const changeAudit = await this.ctx.service.changeApplyAudit.getAllDataByCondition({ where: { caid: c.id, times: c.times }});
                const changeAuditFilter = this.ctx.helper.filterLastestData(changeAudit, ['aid']);
                this.changeApplyData.audit.push(...changeAuditFilter);
            }
        }

        async getChangeApplyData(tid, sid, fields) {
            try {
                await this._generateChangeApply(tid);
                return this.changeApplyData.data;
            } catch (err) {
                return [];
            }
        }

        async getChangeApplyBillsData(tid, sid, fields) {
            try {
                await this._generateChangeApply(tid);
                return this.changeApplyData.bills;
            } catch (err) {
                return [];
            }
        }

        async getChangeApplyAuditData(tid, sid, fields) {
            try {
                await this._generateChangeApply(tid);
                await this._generateChangeApplyAudit();
                return this.changeApplyData.audit;
            } catch (err) {
                this.ctx.log(err);
                return [];
            }
        }

        async _generateChangePlan(tid) {
            if (!!this.changePlanData) return;
            const helper = this.ctx.helper;
            const decimal = this.ctx.tender.info.decimal;
            this.changePlanData = {};
            const where = { tid };
            if (this.ctx.session.sessionProject.page_show.isOnlyChecked) where.status = audit.changeApply.status.checked;
            const data = await this.ctx.service.changePlan.getAllDataByCondition({ where });
            const bills = await this.ctx.service.changePlanList.getChangeBills(tid, this.ctx.session.sessionProject.page_show.isOnlyChecked);
            bills.forEach(x => {
                const plan = data.find(y => { return y.id === x.cpid });
                const p_decimal = plan.decimal ? JSON.stringify(plan.decimal) : { tp: decimal.tp, up: decimal.up };
                x.o_tp = helper.mul(x.oamount, x.unit_price, p_decimal.tp);
                x.c_tp = helper.mul(x.camount, x.unit_price, p_decimal.tp);
                x.s_tp = helper.mul(x.samount, x.unit_price, p_decimal.tp);
                x.sp_tp = helper.mul(x.spamount, x.unit_price, p_decimal.tp);

                const auditAmount = x.audit_amount ? x.audit_amount.split(',') : [];
                for (const [i, aa] of auditAmount.entries()) {
                    const amountField = 'qty_' + (i+1), tpField = 'tp_' + (i+1);
                    x[amountField] = aa ? parseFloat(aa) : 0;
                    x[tpField] = helper.mul(x[amountField], x.unit_price, p_decimal.tp);
                    if (plan) {
                        plan[tpField] = helper.add(plan[tpField], x[tpField]);
                    }
                }
            });
            bills.sort(function(a, b ) {
                const aCIndex = data.findIndex(function (c) {
                    return c.id === a.cpid;
                });
                const bCIndex = data.findIndex(function (c) {
                    return c.id === b.cpid;
                });
                return aCIndex === bCIndex
                    ? helper.compareCode(a.code, b.code)
                    : aCIndex - bCIndex;
            });
            this.changePlanData = { data, bills };
        }

        async _generateChangePlanAudit() {
            if (!!this.changePlanData.audit) return;

            this.changePlanData.audit = [];
            for (const c of this.changePlanData.data) {
                const changeAudit = await this.ctx.service.changePlanAudit.getAllDataByCondition({ where: { cpid: c.id, times: c.times }});
                const changeAuditFilter = this.ctx.helper.filterLastestData(changeAudit, ['aid']);
                this.changePlanData.audit.push(...changeAuditFilter);
            }
        }

        async getChangePlanData(tid, sid, fields) {
            try {
                await this._generateChangePlan(tid);
                return this.changePlanData.data;
            } catch (err) {
                return [];
            }
        }

        async getChangePlanBillsData(tid, sid, fields) {
            try {
                await this._generateChangePlan(tid);
                return this.changePlanData.bills;
            } catch (err) {
                return [];
            }
        }

        async getChangePlanAuditData(tid, sid, fields) {
            try {
                await this._generateChangePlan(tid);
                await this._generateChangePlanAudit();
                return this.changePlanData.audit;
            } catch (err) {
                this.ctx.log(err);
                return [];
            }
        }

        async _generateChangeProject(tid) {
            if (!!this.changeProjectData) return;
            const where = { tid };
            if (this.ctx.session.sessionProject.page_show.isOnlyChecked) where.status = audit.changeProject.status.checked;
            this.changeProjectData = { data: await this.ctx.service.changeProject.getAllDataByCondition({ where }) };
        }

        async _generateChangeProjectAudit() {
            if (!!this.changeProjectData.audit) return;

            this.changeProjectData.audit = [];
            for (const c of this.changeProjectData.data) {
                const changeAudit = await this.ctx.service.changeProjectAudit.getAllDataByCondition({ where: { cpid: c.id, times: c.times }});
                const changeAuditFilter = this.ctx.helper.filterLastestData(changeAudit, ['aid']);
                this.changeProjectData.audit.push(...changeAuditFilter);
            }
        }

        async getChangeProjectData(tid, sid, fields) {
            try {
                await this._generateChangeProject(tid);
                return this.changeProjectData.data;
            } catch (err) {
                this.ctx.log(err);
                return [];
            }
        }

        async getChangeProjectAuditData(tid, sid, fields) {
            try {
                await this._generateChangeProject(tid);
                await this._generateChangeProjectAudit();
                return this.changeProjectData.audit;
            } catch (err) {
                return [];
            }
        }

        async getStageJgcl(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                const data = await this.ctx.service.stageJgcl.getStageData(this.ctx.stage);
                const preData = await this.ctx.service.stageJgcl.getPreStageData(this.ctx.stage.order);
                for (const d of data) {
                    const pd = this.ctx.helper._.find(preData, {uuid: d.uuid});
                    if (pd) {
                        d.pre_arrive_qty = pd.arrive_qty;
                        d.pre_arrive_tp = pd.arrive_tp;
                        d.pre_deduct_qty = pd.deduct_qty;
                        d.pre_deduct_tp = pd.deduct_tp;
                    }
                    d.end_arrive_qty = this.ctx.helper.add(d.pre_arrive_qty, d.arrive_qty);
                    d.end_arrive_tp = this.ctx.helper.add(d.pre_arrive_tp, d.arrive_tp);
                    d.end_deduct_qty = this.ctx.helper.add(d.pre_deduct_qty, d.deduct_qty);
                    d.end_deduct_tp = this.ctx.helper.add(d.pre_deduct_tp, d.deduct_tp);
                }
                return data;
            } catch (err) {
                return [];
            }
        }

        async getStageBonus(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                const data = await this.ctx.service.stageBonus.getEndStageData(this.ctx.stage.order);
                for (const d of data) {
                    const names = this.ctx.helper._.map(d.proof_file, function (x) {
                        return x.filename + x.fileext;
                    });
                    d.proof = names.join('\n');
                }
                return data;
            } catch (err) {
                return [];
            }
        }

        async getStageOther(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                const data = await this.ctx.service.stageOther.getStageData(this.ctx.stage);
                const preData = await this.ctx.service.stageOther.getPreStageData(this.ctx.stage.order);
                for (const d of data) {
                    const pd = this.ctx.helper._.find(preData, {uuid: d.uuid});
                    if (pd) {
                        d.pre_tp = pd.tp;
                    }
                    d.end_tp = this.ctx.helper.add(d.pre_tp, d.tp);
                }
                return data;
            } catch (err) {
                return [];
            }
        }

        async getStageSafeProd(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                const data = await this.ctx.service.stageSafeProd.getStageData(this.ctx.stage);
                const preData = await this.ctx.service.stageSafeProd.getPreStageData(this.ctx.stage.order);
                for (const d of data) {
                    const pd = this.ctx.helper._.find(preData, {uuid: d.uuid});
                    if (pd) {
                        d.pre_qty = pd.qty;
                        d.pre_tp = pd.tp;
                    }
                    d.end_qty = this.ctx.helper.add(d.pre_qty, d.qty);
                    d.end_tp = this.ctx.helper.add(d.pre_tp, d.tp);
                }
                return data;
            } catch (err) {
                return [];
            }
        }

        async getStageTempLand(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);
                await this.ctx.service.stage.checkStage(sid);

                const data = await this.ctx.service.stageTempLand.getStageData(this.ctx.stage);
                const preData = await this.ctx.service.stageTempLand.getPreStageData(this.ctx.stage.order);
                for (const d of data) {
                    const pd = this.ctx.helper._.find(preData, {uuid: d.uuid});
                    if (pd) {
                        d.pre_qty = pd.qty;
                        d.pre_tp = pd.tp;
                    }
                    d.end_qty = this.ctx.helper.add(d.pre_qty, d.qty);
                    d.end_tp = this.ctx.helper.add(d.pre_tp, d.tp);
                }
                return data;
            } catch (err) {
                return [];
            }
        }

        async getSelectMaterialAuditors(tid, material_order, fields) {
            await this.ctx.service.material.checkMaterial(tid, material_order);
            const auditors = await this.ctx.service.materialAudit.getFinalAuditGroup(this.ctx.material.id, this.ctx.material.curTimes);
            const user = await this.ctx.service.projectAccount.getDataById(this.ctx.material.user_id);
            const result = [{
                aid: user.id,
                name: user.name,
                company: user.company,
                role: user.role,
                mobile: user.mobile,
                telephone: user.telephone,
                sign_path: user.sign_path,
                opinion: user.opinion,
                end_time: auditors && auditors.length > 0 ? auditors[0].begin_time : null,
                sort: 0,
            }, ...auditors];
            return result;
        }

        async getMaterial(tender_id, material_order, fields) {
            const result = await this.ctx.service.material.getValidMaterials(tender_id);
            if (this._checkFieldsExist(fields, ['checked_time'])) {
                for (const r of result) {
                    const auditors = await this.ctx.service.materialAudit.getFinalAuditGroup(r.id, r.curTimes || r.times);
                    r.checked_time = !r.curTimes ? auditors[auditors.length - 1].end_time : null;
                }
            }
            return result;
        }

        _completeMaterialGl(materialGl) {
            const tTypeStr = [], mTypeStr = [];
            for (const t of materialConst.t_type) {
                tTypeStr[t.value] = t.text;
            }
            for (const m of materialConst.m_type) {
                mTypeStr[m.value] = m.text;
            }
            for (const gl of materialGl) {
                gl.tp = this.ctx.helper.mul(gl.quantity, gl.m_spread, 2);
                gl.t_type_str = tTypeStr[gl.t_type];
                gl.m_type_str = mTypeStr[gl.m_type];
                gl.end_tp = this.ctx.helper.add(gl.tp, gl.pre_tp);
            }
        }

        async getMaterialGl(tender_id, material_order, fields) {
            const materials = await this.ctx.service.material.getAllDataByCondition({
                where: {tid: tender_id},
                orders: [['order', 'desc']],
            });
            if (materials.length > 0) {
                let result;
                if (materials[0].order === material_order) {
                    result = await this.ctx.service.materialBills.getAllDataByCondition({
                        where: {tid: tender_id}
                    });
                } else {
                    const material = this.ctx.helper._.find(materials, {order: material_order});
                    if (!material) return [];

                    const sql = 'SELECT mb.id, mb.tid, mb.mid, mb.order, mb.order, mb.t_type, mb.code, mb.name, mb.unit, mb.spec, mb.m_type,' +
                        '    mbh.quantity, mbh.expr,' +
                        '    mb.basic_price, mb.basic_times, ' +
                        '    mbh.msg_tp, mbh.msg_times, mbh.msg_spread, mbh.m_up_risk, mbh.m_down_risk, mbh.m_spread, mbh.m_tp, mbh.pre_tp, mbh.m_tax_tp, mbh.tax_pre_tp, mbh.origin, ' +
                        '    mb.remark, mb.is_summary, mbh.m_tax, mb.in_time' +
                        '  FROM ' + this.ctx.service.materialBillsHistory.tableName + ' mbh ' +
                        '  LEFT JOIN ' + this.ctx.service.materialBills.tableName + ' mb ON mbh.mb_id = mb.id ' +
                        '  WHERE mbh.tid = ? And mbh.mid = ?';
                    result = await this.ctx.app.mysql.query(sql, [tender_id, material.id]);
                }
                this._completeMaterialGl(result);
                return result;
            } else {
                return [];
            }
        }

        async getMaterialGlDetail(tender_id, material_order, fields) {
            const material = await this.ctx.service.material.getDataByCondition({tid: tender_id, order: material_order});
            return material ? await this.ctx.service.materialList.getMaterialData(tender_id, material.id) : [];
        }

        async getMaterialBills(tender_id, material_order, fields) {
            const material = await this.ctx.service.material.getDataByCondition({tid: tender_id, order: material_order});
            try {
                const billsData = await this.ctx.service.ledger.getData(tender_id);
                if (this._checkFieldsExist(fields, billsFields.stage)) {
                    const curStage = await this.ctx.service.stageBills.getStagesData(tender_id, material.stage_id);
                    this.ctx.helper.assignRelaData(billsData, [
                        {data: curStage, fields: ['contract_qty', 'contract_tp', 'contract_expr', 'qc_qty', 'qc_tp', 'postil'], prefix: '', relaId: 'lid'}
                    ]);
                }

                const billsTree = this._getNewBillsTree();
                billsTree.loadDatas(billsData);
                billsTree.calculateAll();

                return billsTree.getDatas([
                    'id', 'tender_id', 'ledger_id', 'ledger_pid', 'level', 'order', 'full_path', 'is_leaf',
                    'code', 'b_code', 'name', 'unit', 'unit_price',
                    'deal_qty', 'deal_tp',
                    'sgfh_qty', 'sgfh_tp', 'sjcl_qty', 'sjcl_tp', 'qtcl_qty', 'qtcl_tp', 'quantity', 'total_price',
                    'dgn_qty1', 'dgn_qty2',
                    'drawing_code', 'memo', 'node_type', 'is_tp',
                    'contract_qty', 'contract_tp', 'qc_qty', 'qc_tp', 'gather_qty', 'gather_tp', 'postil',
                    'sgfh_expr', 'sjcl_expr', 'qtcl_expr', 'contract_expr',
                ]);
            } catch(err) {
                this.ctx.helper.log(err);
                return [];
            }
        }

        async getMaterialPos(tender_id, material_order, fields) {
            const material = await this.ctx.service.material.getDataByCondition({tid: tender_id, order: material_order});
            try {
                const posData = await this.ctx.service.pos.getAllDataByCondition({ where: {tid: tender_id }});
                if (this._checkFieldsExist(fields, posFields.stage)) {
                    const curPosStage = await this.ctx.service.stagePos.getStagesData(tender_id, material.stage_id);
                    this.ctx.helper.assignRelaData(posData, [
                        {data: curPosStage, fields: ['contract_qty', 'qc_qty', 'contract_expr', 'postil'], prefix: '', relaId: 'pid'}
                    ]);
                }
                this.pos.loadDatas(posData);
                this.pos.calculateAll();

                return this.pos.getDatas();
            } catch (err) {
                this.ctx.helper.log(err);
                return [];
            }
        }

        async getSumStageBillsData(tid, sid, fields) {
            try {
                await this.ctx.service.tender.checkTender(tid);

                const billsData = await this.ctx.service.ledger.getData(this.ctx.tender.id);
                const checkStageField = function (stageOrder) {
                    for (const f of fields) {
                        if (f.indexOf('s' + stageOrder + '_') >= 0) {
                            return true;
                        }
                    }
                    return false;
                };

                const calcFields = ['deal_tp', 'total_price'], calcPrefix = [];
                const stages = this.ctx.service.stage.getValidStages(this.ctx.tender.id);
                for (const stage of stages) {
                    if (!checkStageField(stage.order)) return;

                    await this.ctx.service.stage.doCheckStage(stage);
                    calcFields.push('s' + stage.order + '_contract_tp');
                    calcFields.push('s' + stage.order + '_qc_tp');
                    calcFields.push('s' + stage.order + '_gather_tp');
                    calcFields.push('s' + stage.order + '_');

                    const curStage = stage.readOnly
                        ? await this.ctx.service.stageBills.getAuditorStageData2(this.ctx.tender.id, stage.id, stage.curTimes, stage.curOrder)
                        : await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, stage.id);
                    this.ctx.helper.assignRelaData(billsData, [
                        {data: curStage, fields: ['contract_qty', 'contract_tp', 'contract_expr', 'qc_qty', 'qc_tp'], prefix: 's' + stage.order + '_', relaId: 'lid'}
                    ]);
                }

                const billsTree = new Ledger.billsTree(this.ctx, {
                    id: 'ledger_id',
                    pid: 'ledger_pid',
                    order: 'order',
                    level: 'level',
                    rootId: -1,
                    keys: ['id', 'tender_id', 'ledger_id'],
                    stageId: 'id',
                    calcFields: calcFields,
                    calc: function (node) {
                        for (const prefix of calcPrefix) {
                            if (node.children && node.children.length === 0) {
                                node[prefix + 'gather_qty'] = self.ctx.helper.add(node[prefix + 'contract_qty'], node[prefix + 'qc_qty']);
                            }
                            node[prefix + 'gather_tp'] = self.ctx.helper.add(node[prefix + 'contract_tp'], node[prefix + 'qc_tp']);
                        }
                    }
                });
                billsTree.loadDatas(billsData);
                billsTree.calculateAll();
                return billsTree.getDefaultDatas();
            } catch (err) {
                return [];
            }
        }

        async getStageAuditors(tid, sid) {
            await this.ctx.service.tender.checkTender(tid);
            await this.ctx.service.stage.checkStage(sid);

            const auditors = await this.ctx.service.stageAudit.getFinalAuditGroup(this.ctx.stage.id, this.ctx.stage.curTimes);
            const user = await this.ctx.service.projectAccount.getDataById(this.ctx.stage.user_id);
            const result = [{
                aid: user.id,
                name: user.name,
                company: user.company,
                role: user.role,
                mobile: user.mobile,
                telephone: user.telephone,
                sign_path: user.sign_path,
                opinion: user.opinion,
                end_time: auditors && auditors.length > 0 ? auditors[0].begin_time : null,
                sort: 0,
            }, ...auditors];
            return result;
        }

        async getSignSelect(tid, sid, customSelect) {
            await this.ctx.service.tender.checkTender(tid);
            await this.ctx.service.stage.checkStage(sid);

            const signSelect = customSelect.sign_select, result = {};
            for (const [i, ss] of signSelect.entries()) {
                const user = await this.ctx.service.projectAccount.getDataById(ss.id);
                const sign = {
                    id: ss.id, name: user.name, company: user.company, role: user.role,
                    mobile: user.mobile, telephone: user.telephone,
                };
                if (ss.id !== this.ctx.stage.user_id) {
                    const audit = this.ctx.stage.auditors.find(x => {return x.aid === ss.id});
                    user.end_time = audit ? audit.end_time : ss.audit_time;
                } else {
                    user.end_time = this.ctx.stage.auditors[0].end_time;
                }
                if (user.end_time) sign.sign_path = user.sign_path;
                result['sign' + (i+1)] = sign;
            }
            return result;
        }

        async getChangeInfo(tid, sid) {
            await this.ctx.service.tender.checkTender(tid);
            await this.ctx.service.stage.checkStage(sid);
            this.changeInfo = await this.ctx.service.stageChange.getChangeWithUsedInfo(this.ctx.stage);
            const usedChangesId = await this.ctx.service.stageChange.getStageUsedChangeId(sid);
            for (const c of this.changeInfo) {
                c.cur_used = usedChangesId.indexOf(c.cid) >= 0;
                c.used_ratio = c.used_pt;
                c.is_import = 0;
            }
            const importChange = await this.ctx.service.stageImportChange.get
            return this.changeInfo;
        }

        async getChangeInfoBills(tid, sid) {
            if (!this.changeInfo) await this.getChangeInfo(tid, sid);
            this.changeInfoBills = [];
            for (const c of this.changeInfo) {
                const cb = await this.ctx.service.changeAuditList.getAllDataByCondition({ where: { cid: c.cid } });
                const cbu = await this.ctx.service.stageChange.getUsedData(tid, c.cid);
                const curUsedBills = await this.ctx.service.stageChange.getStageUsedData(sid, c.cid);
                for (const b of cb) {
                    b.qty = this.ctx.helper._.toNumber(b.samount);
                    b.tp = this.ctx.helper.round(this.ctx.helper.mul(b.qty, b.unit_price), this.ctx.tender.info.decimal.tp);

                    const u = cbu.find(x => { return x.id === b.id; });
                    b.used_qty = u ? u.used_qty : 0;

                    b.valid_qty = this.ctx.helper.sub(b.qty, b.used_qty);
                    const cur = this.ctx.helper._.filter(curUsedBills, { cbid: b.id });
                    b.cur_qty = 0;
                    for (const cc of cur) {
                        b.cur_qty = this.ctx.helper.add(b.cur_qty, cc.qty);
                    }
                    b.cur_used = !!b.cur_qty;
                    this.changeInfoBills.push(b);
                }
            }
            return this.changeInfoBills;
        }
    }

    return ReportMemory;
};