Calculation/app/service/stage_bills.js

'use strict';

/**
 * 期计量 - 部位明细计量
 *
 * @author Mai
 * @date 2018/12/8
 * @version
 */
const calcFields = ['contract_qty', 'qc_qty'];
const auditConst = require('../const/audit');
const timesLen = auditConst.stage.timesLen;
const SumLoad = require('../lib/sum_load');

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

        /**
         * 查询期计量最后审核人数据
         * @param {Number} tid - 标段id
         * @param {Number} sid - 期id
         * @param {Number|Array} lid - 台账节点id（可以为空）
         * @return {Promise<*>}
         */
        // async getLastestStageData(tid, sid, lid) {
        //     let lidSql = '',
        //         result;
        //     if (lid) {
        //         if (lid instanceof Array) {
        //             lidSql = lid.length > 0 ? ' And lid in (' + this.ctx.helper.getInArrStrSqlFilter(lid) + ')' : '';
        //         } else {
        //             lidSql = ' And lid in (' + this.db.escape(lid) + ')';
        //         }
        //     }
        //     const sql = 'SELECT Bills.* FROM ' + this.departTableName(tid) + ' As Bills ' +
        //                 '  INNER JOIN ( ' +
        //                 '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.departTableName(tid) +
        //                 '      WHERE tid = ? And sid = ?' + lidSql +
        //                 '      GROUP BY `lid`' +
        //                 '  ) As MaxFilter ' +
        //                 '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
        //     const sqlParam = [tid, sid];
        //     if (!lid) {
        //         return await this.db.query(sql, sqlParam);
        //     } else if (lid instanceof Array) {
        //         return await this.db.query(sql, sqlParam);
        //     }
        //     return await this.db.queryOne(sql, sqlParam);
        //
        // }

        /**
         * 查询 某期 某轮审批 某人数据
         * @param {Number} tid - 标段id
         * @param {Number} sid - 期id
         * @param {Number} times - 第几轮
         * @param {Number} order - 流程
         * @param {Number|Array} lid - 台账节点id（可以为空）
         * @return {Promise<*>}
         */
        // async getAuditorStageData(tid, sid, times, order, lid) {
        //     const lidSql = lid ? ' And Bills.lid in (?)' : '';
        //     const sql = 'SELECT Bills.* FROM ' + this.departTableName(tid) + ' As Bills ' +
        //         '  INNER JOIN ( ' +
        //         '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `tid`, `sid` From ' + this.departTableName(tid) +
        //         '      WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) And tid = ? And sid = ?' + lidSql +
        //         '      GROUP BY `lid`' +
        //         '  ) As MaxFilter ' +
        //         '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid' +
        //         '    AND Bills.sid = MaxFilter.sid';
        //     const sqlParam = [times, times, order, tid, sid];
        //     if (!lid) {
        //         return await this.db.query(sql, sqlParam);
        //     } else if (lid instanceof Array) {
        //         sqlParam.push(lid.join(', '));
        //         return await this.db.query(sql, sqlParam);
        //     }
        //     sqlParam.push(lid);
        //     return await this.db.queryOne(sql, sqlParam);
        //
        // }

        _getFilterSql(where, asTable = '') {
            let whereSql = '';
            if (!where) return whereSql;
            if (where.lid) {
                if (where.lid instanceof Array) {
                    whereSql += ' And ' + asTable + 'lid in ('  + this.ctx.helper.getInArrStrSqlFilter(where.lid) + ')';
                } else if (typeof where.lid === "string") {
                    whereSql += ' And ' + asTable + 'lid = ' + this.db.escape(where.lid);
                }
            }
            return whereSql;
        }

        async getLastestStageData2(tid, sid, lid) {
            const filterSql = lid ? this._getFilterSql({lid}) : '';
            const sql = 'Select * From ' + this.departTableName(tid) +
                '  Where tid = ? and sid = ? ' + filterSql;

            const result = await this.db.query(sql, [tid, sid]);
            const stageBills = this.ctx.helper.filterLastestData2(result, ['lid']);
            if (!lid || lid instanceof Array) return stageBills;
            return stageBills[0];
        }

        async getAuditorStageData2(tid, sid, times, order, lid) {
            const filterSql = lid ? this._getFilterSql({lid}) : '';
            const sql = 'Select * From ' + this.departTableName(tid) +
                        '  Where tid = ? and sid = ? And (`times` < ? OR (`times` = ? AND `order` <= ?)) ' + filterSql;

            const result = await this.db.query(sql, [tid, sid, times, times, order]);
            const stageBills = this.ctx.helper.filterLastestData(result, ['lid']);
            if (!lid || lid instanceof Array) return stageBills;
            return stageBills[0];
        }

        async updateStageBills4CheckCancel(sid, newTimes, newOrder, oldTimes, oldOrder, transaction) {
            const oldSBLists = await this.getAllDataByCondition({ where: { sid, times: oldTimes, order: oldOrder } });
            const newSBLists = await this.getAllDataByCondition({ where: { sid, times: newTimes, order: newOrder } });
            const delidList = [];
            for (const o of oldSBLists) {
                const newSBInfo = this._.find(newSBLists, { lid: o.lid });
                if (newSBInfo) {
                    // 删除
                    delidList.push(newSBInfo.id);
                }
            }
            if (delidList.length > 0) await transaction.delete(this.tableName, { id: delidList });
            if (oldSBLists.length > 0) {
                await transaction.update(this.tableName, {
                    times: newTimes,
                    order: newOrder,
                }, { where: { id: this._.map(oldSBLists, 'id') } });
            }
        }

        async getStageUsedBills(tid, sid) {
            const sql = 'SELECT Bills.lid, ((not IsNull(Bills.contract_qty) and Bills.contract_qty <> 0) or (not IsNull(Bills.contract_tp) and Bills.contract_tp <> 0) or (not IsNull(Bills.qc_qty) and Bills.qc_qty <> 0) or (not IsNull(Bills.qc_tp) and Bills.qc_tp <> 0)) As used' +
                '  FROM ' + this.tableName + ' As Bills ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
                '      WHERE tid = ? And sid = ?' +
                '      GROUP BY `lid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`';
            const sqlParam = [tid, sid];
            const stageBills = await this.db.query(sql, sqlParam);
            return this._.map(this._.filter(stageBills, 'used'), 'lid');
        }

        async getStageBills(tid, sid, lid) {
            const sql = 'SELECT Stage.*, Ledger.unit_price FROM ?? As Stage, ?? As Ledger ' +
                        '  Where Stage.tid = ?, Stage.sid = ?, Stage.lid = ?, Stage.lid = Ledger.id ' +
                        '  Order Stage.time DESC, Stage.order DESC ';
            const sqlParam = [this.tableName, this.ctx.service.ledger.tableName];
            sqlParam.push(this.db.escape(tid));
            sqlParam.push(this.db.escape(sid));
            sqlParam.push(this.db.escape(lid));
            return await this.db.queryOne(sql, sqlParam);
        }

        _calcContractTpByTp(bills, preStageBills, stageBills, decimal) {
            let activeQty = this.ctx.helper.add(bills.quantity, stageBills.qc_minus_qty);
            let end_contract_qty = stageBills.contract_qty;
            if (preStageBills) {
                activeQty = this.ctx.helper.add(activeQty, preStageBills.qc_minus_qty);
                end_contract_qty = this.ctx.helper.add(end_contract_qty, preStageBills.contract_qty);
            }
            const end_contract_tp = this.ctx.helper.mul(this.ctx.helper.div(end_contract_qty, activeQty), bills.total_price, decimal.tp);
            return preStageBills ? this.ctx.helper.sub(end_contract_tp, preStageBills.contract_tp) : end_contract_tp;
        }
        _calcContractTpByCorrect(bills,preStageBills, stageBills, decimal) {
            let activeQty = this.ctx.helper.add(bills.quantity, stageBills.qc_minus_qty);
            let end_contract_qty = stageBills.contract_qty;
            if (preStageBills) {
                activeQty = this.ctx.helper.add(activeQty, preStageBills.qc_minus_qty);
                end_contract_qty = this.ctx.helper.add(end_contract_qty, preStageBills.contract_qty);
            }
            const end_contract_tp = this.ctx.helper.mul(end_contract_qty, bills.unit_price, decimal.tp);
            return activeQty === end_contract_qty ? this.ctx.helper.sub(end_contract_tp, preStageBills.contract_tp) : this.ctx.helper.mul(stageBills.contract_qty, bills.unit_price, info.decimal.tp);
        }
        async calcContractTp(info, stage, bills, stageBills) {
            const correct = this.ctx.subProject.page_show.correctCalcContractTp;
            if (info.calc_type === 'tp') {
                const preStageBills = await this.ctx.service.stageBillsFinal.getDataByCondition({ tid: stage.tid, sorder: stage.order - 1, lid: bills.id });
                return this._calcContractTpByTp(bills, preStageBills, stageBills, info.decimal);
            } else if (info.calc_type === 'up') {
                if (correct) {
                    const preStageBills = await this.ctx.service.stageBillsFinal.getDataByCondition({ tid: stage.tid, sorder: stage.order - 1, lid: bills.id });
                    return this._calcContractTpByCorrect(bills, preStageBills, stageBills, info.decimal);
                } else {
                    return this.ctx.helper.mul(stageBills.contract_qty, bills.unit_price, info.decimal.tp);
                }
            }
        }
        async _calcStageBillsData(data, orgData, ledgerData) {
            const info = this.ctx.tender.info;
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);
            if (data.qc_minus_qty !== undefined) {
                data.qc_minus_qty = data.qc_minus_qty ? this.round(data.qc_minus_qty, precision.value) : 0;
            } else if (data.contract_qty !== undefined) {
                data.qc_minus_qty = orgData.qc_minus_qty;
            }
            if (data.contract_qty !== undefined) {
                data.contract_qty = this.round(data.contract_qty, precision.value);
                data.contract_tp = await this.calcContractTp(info, this.ctx.stage, ledgerData, data);
            } else if (data.qc_minus_qty !== undefined) {
                data.contract_qty = orgData.contract_qty;
                data.contract_tp = await this.calcContractTp(info, this.ctx.stage, ledgerData, data);
            }
            if (data.qc_qty !== undefined) {
                data.qc_qty = this.round(data.qc_qty, precision.value);
                data.qc_tp = this.ctx.helper.mul(data.qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (data.positive_qc_qty !== undefined) {
                data.positive_qc_qty = this.round(data.positive_qc_qty, precision.value);
                data.positive_qc_tp = this.ctx.helper.mul(data.positive_qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (data.negative_qc_qty !== undefined) {
                data.negative_qc_qty = this.round(data.negative_qc_qty, precision.value);
                data.negative_qc_tp = this.ctx.helper.mul(data.negative_qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (data.ex_stage_qty1 !== undefined) {
                data.ex_stage_qty1 = this.round(data.ex_stage_qty1, precision.value);
                data.ex_stage_tp1 = this.ctx.helper.mul(data.ex_stage_qty1, ledgerData.unit_price, info.decimal.tp);
            }
            if (ledgerData.is_tp) {
                if (data.contract_tp !== undefined) data.contract_tp = this.ctx.helper.round(data.contract_tp, info.decimal.tp);
                if (data.ex_stage_tp1 !== undefined) data.ex_stage_tp1 = this.ctx.helper.round(data.ex_stage_tp1, info.decimal.tp);
            }
        }

        async _insertStageBillsData(transaction, insertData, orgData, ledgerData) {
            const info = this.ctx.tender.info;
            const d = {
                tid: this.ctx.tender.id,
                lid: ledgerData.id,
                sid: this.ctx.stage.id,
                times: this.ctx.stage.curTimes,
                order: this.ctx.stage.curOrder,
                said: this.ctx.session.sessionUser.accountId,
            };
            if (orgData) {
                d.contract_qty = orgData.contract_qty;
                d.contract_tp = orgData.contract_tp;
                d.qc_qty = orgData.qc_qty;
                d.qc_tp = orgData.qc_tp;
                d.qc_minus_qty = orgData.qc_minus_qty;
                d.positive_qc_qty = orgData.positive_qc_qty;
                d.negative_qc_qty = orgData.negative_qc_qty;
                d.postil = orgData.postil;
                d.ex_stage_qty1 = orgData.ex_stage_qty1;
                d.ex_stage_tp1 = orgData.ex_stage_tp1;
            }
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);

            if (insertData.qc_minus_qty !== undefined) {
                d.qc_minus_qty = insertData.qc_minus_qty ? this.round(insertData.qc_minus_qty, precision.value) : 0;
            }
            if (insertData.contract_qty !== undefined) {
                d.contract_qty = this.round(insertData.contract_qty, precision.value);
                d.contract_tp = await this.calcContractTp(info, this.ctx.stage, ledgerData, d);
            }
            if (insertData.contract_expr !== undefined) d.contract_expr = insertData.contract_expr;
            if (insertData.qc_qty !== undefined) {
                d.qc_qty = this.round(insertData.qc_qty, precision.value);
                d.qc_tp = this.ctx.helper.mul(d.qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (insertData.positive_qc_qty !== undefined) {
                d.positive_qc_qty = this.round(insertData.positive_qc_qty, precision.value);
                d.positive_qc_tp = this.ctx.helper.mul(d.positive_qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (insertData.negative_qc_qty !== undefined) {
                d.negative_qc_qty = this.round(insertData.negative_qc_qty, precision.value);
                d.negative_qc_tp = this.ctx.helper.mul(d.negative_qc_qty, ledgerData.unit_price, info.decimal.tp);
            }
            if (insertData.ex_stage_qty1 !== undefined) {
                d.ex_stage_qty1 = this.round(insertData.ex_stage_qty1, precision.value);
                d.ex_stage_tp1 = this.ctx.helper.mul(d.ex_stage_qty1, ledgerData.unit_price, info.decimal.tp);
            }
            if (insertData.postil) {
                d.postil = insertData.postil;
            }
            if (ledgerData.is_tp) {
                if (insertData.contract_tp !== undefined) d.contract_tp = this.ctx.helper.round(insertData.contract_tp, info.decimal.tp);
                if (insertData.ex_stage_tp1 !== undefined) d.ex_stage_tp1 = this.ctx.helper.round(insertData.ex_stage_tp1, info.decimal.tp);
            }
            await transaction.insert(this.tableName, d);
        }

        /**
         * 前端提交数据
         * @param {Object|Array} data - 提交的数据
         * @return {Promise<void>}
         */
        async updateStageData(data) {
            const datas = data instanceof Array ? data : [data];
            const transaction = await this.db.beginTransaction();
            try {
                for (const d of datas) {
                    const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, d.lid);
                    const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(d.lid);
                    if (!stageBills || stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
                        await this._insertStageBillsData(transaction, d, stageBills, ledgerBills);
                    } else {
                        d.id = stageBills.id;
                        d.said = this.ctx.session.sessionUser.accountId;
                        await this._calcStageBillsData(d, stageBills, ledgerBills);
                        await transaction.update(this.tableName, d);
                    }
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, this._.map(datas, 'lid'));
        }

        /**
         * 根据
         * @param transaction
         * @param ledgerBills
         * @param stageBills
         * @param data
         * @return {Promise<void>}
         * @private
         */
        async updateStageBillsQty(transaction, ledgerBills, stageBills, data) {
            if (stageBills) {
                if ((data.contract_qty === undefined || stageBills.contract_qty !== data.contract_qty) ||
                    (data.qc_qty === undefined || stageBills.qc_qty !== data.qc_qty) ||
                    (data.qc_minus_qty === undefined || stageBills.qc_minus_qty !== data.qc_minus_qty) ||
                    (data.ex_stage_qty1 === undefined || stageBills.ex_stage_qty1 !== data.ex_stage_qty1)
                ) {
                    if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
                        data.id = stageBills.id;
                        data.said = this.ctx.session.sessionUser.accountId;
                        await this._calcStageBillsData(data, stageBills, ledgerBills);
                        await transaction.update(this.tableName, data);
                    } else {
                        await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
                    }
                }
            } else {
                await this._insertStageBillsData(transaction, data, stageBills, ledgerBills);
            }
        }
        /**
         * 重算 本期计量 数量 (根据部位明细)
         * @param {Number} tid - 标段id
         * @param {Number} id - 需要计算的节点的id
         * @param {Number} lid - 台账id
         * @param {Object} transaction - 操作所属事务
         * @return {Promise<void>}
         */
        async calc(tid, stage, lid, transaction) {
            const info = this.ctx.tender.info;
            const stageBills = await this.getLastestStageData2(tid, stage.id, lid);
            const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(lid);
            if (!ledgerBills) {
                throw '提交数据错误';
            }
            const posGather = await this.ctx.service.stagePos.getPosGather(tid, stage.id, lid, transaction);
            if (!posGather) { return; }
            if (posGather.qc_minus_qty !== undefined) {
                posGather.qc_minus_qty = posGather.qc_minus_qty || 0;
            } else if (posGather.contract_qty !== undefined) {
                posGather.qc_minus_qty = stageBills.qc_minus_qty;
            }

            const precision = this.ctx.helper.findPrecision(info.precision, ledgerBills.unit);
            // 计算
            if (posGather.contract_qty !== undefined) {
                posGather.contract_qty = this.round(posGather.contract_qty, precision.value);
                posGather.contract_tp = await this.calcContractTp(info, stage, ledgerBills, posGather);
            } else if (posGather.qc_minus_qty) {
                posGather.contract_qty = stageBills.contract_qty;
                posGather.contract_tp = await this.calcContractTp(info, stage, ledgerBills, posGather);
            }
            if (posGather.qc_qty !== undefined) {
                posGather.qc_qty = this.round(posGather.qc_qty, precision.value);
                posGather.qc_tp = this.ctx.helper.mul(posGather.qc_qty, ledgerBills.unit_price, info.decimal.tp);
            }
            if (posGather.positive_qc_qty !== undefined) {
                posGather.positive_qc_qty = this.round(posGather.positive_qc_qty, precision.value);
                posGather.positive_qc_tp = this.ctx.helper.mul(posGather.positive_qc_qty, ledgerBills.unit_price, info.decimal.tp);
            }
            if (posGather.negative_qc_qty !== undefined) {
                posGather.negative_qc_qty = this.round(posGather.negative_qc_qty, precision.value);
                posGather.negative_qc_tp = this.ctx.helper.mul(posGather.negative_qc_qty, ledgerBills.unit_price, info.decimal.tp);
            }
            if (posGather.ex_stage_qty1 !== undefined) {
                posGather.ex_stage_qty1 = this.round(posGather.ex_stage_qty1, precision.value);
                posGather.ex_stage_tp1 = this.ctx.helper.mul(posGather.ex_stage_qty1, ledgerBills.unit_price, info.decimal.tp);
            }
            if (stageBills) {
                if (stageBills.contract_qty === posGather.contract_qty && stageBills.qc_qty === posGather.qc_qty && stageBills.qc_minus_qty === posGather.qc_minus_qty && stageBills.ex_stage_qty1 === posGather.ex_stage_qty1) {
                    return;
                }
                if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
                    posGather.id = stageBills.id;
                    await transaction.update(this.tableName, posGather);
                } else {
                    await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
                }

            } else {
                await this._insertStageBillsData(transaction, posGather, stageBills, ledgerBills);
            }
        }

        async updateStageBillsCalcType(data) {
            const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, data.id);
            const le = await this.ctx.service.ledgerExtra.getDataById(data.id);
            const updateData = { contract_qty: 0, contract_tp: 0, ex_stage_qty1: 0, ex_stage_tp1: 0 };

            const transaction = await this.db.beginTransaction();
            try {
                if (le) {
                    await transaction.update(this.ctx.service.ledgerExtra.tableName, data);
                } else {
                    data.tid = this.ctx.tender.id;
                    await transaction.insert(this.ctx.service.ledgerExtra.tableName, data);
                }
                if (stageBills) {
                    if (stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
                        const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(data.id);
                        await this._insertStageBillsData(transaction, updateData, stageBills, ledgerBills);
                    } else {
                        updateData.id = stageBills.id;
                        updateData.said = this.ctx.session.sessionUser.accountId;
                        await transaction.update(this.tableName, updateData);
                    }
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            const bills = await this.ctx.service.ledger.getCompleteDataById(data.id);
            const curStageData = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, [data.id]);
            return { bills: [bills], curStageData };
        }

        async getSumTotalPrice(stage) {
            const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp FROM ' + this.departTableName(stage.tid) + ' As Bills ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid`, `sid` From ' + this.departTableName(stage.tid) +
                '      WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) AND `sid` = ?' +
                '      GROUP BY `lid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid And Bills.sid = MaxFilter.sid';
            const sqlParam = [stage.curTimes, stage.curTimes, stage.curOrder, stage.id];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        async getLatestSumTotalPrice(tid, sid) {
            const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp FROM ' + this.departTableName(tid) + ' As Bills ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid`, `sid` From ' + this.departTableName(tid) +
                '      WHERE `sid` = ?' +
                '      GROUP BY `lid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid And Bills.sid = MaxFilter.sid';
            const sqlParam = [sid];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        async getSumTotalPriceFilter(stage, operate, filter) {
            const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `qc_tp`, Sum(`positive_qc_tp`) As positive_qc_tp, Sum(`negative_qc_tp`) As negative_qc_tp' +
                '  FROM ' + this.departTableName(stage.tid) + ' As Bills ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `flow`, `lid` From ' + this.departTableName(stage.tid) +
                '      WHERE (`times` < ? OR (`times` = ? AND `order` <= ?)) AND `sid` = ?' +
                '      GROUP BY `lid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.flow And Bills.lid = MaxFilter.lid ' +
                '  INNER JOIN ' + this.ctx.service.ledger.departTableName(stage.tid) + ' As Ledger ON Bills.lid = Ledger.id' +
                '  WHERE Bills.sid = ? And Ledger.b_code ' + operate + ' ?';
            const sqlParam = [stage.times, stage.curTimes, stage.curOrder, stage.id, stage.id, filter];
            const result = await this.db.queryOne(sql, sqlParam);
            return result;
        }

        async getSumTotalPriceGcl(stage, regText) {
            if (regText) {
                return await this.getSumTotalPriceFilter(stage, 'REGEXP', regText);
            }
            return await this.getSumTotalPriceFilter(stage, '<>', this.db.escape(''));

        }

        async getSumTotalPriceNotGcl(stage) {
            return await this.getSumTotalPriceFilter(stage, '=', this.db.escape(''));
        }

        /**
         * 多期清单数据整合 （材料调差调用）
         * @param {Number} tid - 标段id
         * @param {String} stage_id_list - 期id列表
         * @return {Promise<void>}
         */
        async getStagesData(tid, stage_id_list) {
            const whereSql = this.ctx.helper.whereSql({ tid, sid: stage_id_list.split(',') });
            const sql = 'SELECT Bills.lid, Bills.tid, Bills.sid,' +
                '    Sum(Bills.contract_qty) As contract_qty, Sum(Bills.contract_tp) As contract_tp,' +
                '    Sum(Bills.qc_minus_qty) As qc_minus_qty,' +
                '    Sum(Bills.qc_qty) As qc_qty, Sum(Bills.qc_tp) As qc_tp' +
                '  FROM ' + this.tableName + ' As Bills ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `tid`, `lid`, `sid` From ' + this.tableName + whereSql + ' GROUP BY `lid`, `sid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.tid = MaxFilter.tid And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
                '  GROUP BY Bills.lid';
            const result = await this.db.query(sql);
            return result;
        }

        /**
         * 获取多期（合同和数量变更相加）计量-小计（材料调差调用）
         * @param {Number} tid - 标段id
         * @param {String} stage_id_list - 期id列表
         * @param {String} lid - 台账id
         * @param {String} xid - 项目节id
         * @return {Promise<void>}
         */
        async getGatherQtyByMaterial(tid, stage_id_list, lid) {
            stage_id_list = stage_id_list !== null ? stage_id_list.split(',') : [];
            const qtys = {
                gather_qty: 0,
                contract_qty: 0,
                qc_qty: 0,
                qc_minus_qty: 0,
            };
            for (const sid of stage_id_list) {
                const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills ' +
                    '  INNER JOIN ( ' +
                    '    SELECT MAX(`times` * ' + timesLen + ' + `order`) As `progress`, `lid`, `sid` From ' + this.tableName +
                    '      WHERE tid = ? And sid = ?' +
                    '      GROUP BY `lid`' +
                    '  ) As MaxFilter ' +
                    '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
                    ' WHERE Bills.lid = ?';
                const sqlParam = [tid, sid, lid];
                const result = await this.db.queryOne(sql, sqlParam);
                if (result) {
                    qtys.contract_qty = this.ctx.helper.add(qtys.contract_qty, result.contract_qty);
                    qtys.qc_qty = this.ctx.helper.add(qtys.qc_qty, result.qc_qty);
                    qtys.qc_minus_qty = this.ctx.helper.add(qtys.qc_minus_qty, result.qc_minus_qty);
                    qtys.gather_qty = this.ctx.helper.add(qtys.gather_qty, this.ctx.helper.add(result.contract_qty, result.qc_qty));
                }
            }
            return this.ctx.helper.resetQtys(qtys);
        }

        async getSumTotalPriceByMaterial(stage_list) {
            let contract_tp = 0;
            let qc_tp = 0;
            for (const stage of stage_list) {
                const result = await this.getSumTotalPrice(stage);
                if (result) {
                    contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
                    qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
                }
            }
            return { contract_tp, qc_tp };
        }

        async getSumTotalPriceGclByMaterial(stage_list, regText) {
            let contract_tp = 0;
            let qc_tp = 0;
            for (const stage of stage_list) {
                const result = await this.getSumTotalPriceGcl(stage, regText);
                if (result) {
                    contract_tp = this.ctx.helper.add(contract_tp, result.contract_tp);
                    qc_tp = this.ctx.helper.add(qc_tp, result.qc_tp);
                }
            }
            return { contract_tp, qc_tp };
        }

        async sumLoad(lid, tenders, cover, ignore, loadChange) {
            const conn = await this.db.beginTransaction();
            try {
                const maxId = await this.ctx.service.ledger._getMaxLid(this.ctx.tender.id);
                const select = await this.ctx.service.ledger.getCompleteDataById(lid);
                const sumLoad = new SumLoad(this.ctx);
                const loadTree = await sumLoad.stageGatherGcl(select, maxId, tenders, null, cover, ignore, loadChange);
                const result = loadTree.getUpdateData();
                // if (result.errors.length > 100) throw '您导入的数据存在大量数据错误，请您仔细检查';

                const stageBills = await this.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id);
                const updateStageBills = [], insertStageBills = [];
                for (const u of result.update) {
                    const sb = stageBills.find(x => { return x.lid === u.lid; });
                    if (!sb || sb.times !== this.ctx.stage.curTimes || sb.order !== this.ctx.stage.curOrder) {
                        u.postil = sb ? sb.postil : null;
                        u.tid = this.ctx.tender.id;
                        u.sid = this.ctx.stage.id;
                        u.said = this.ctx.session.sessionUser.accountId;
                        u.times = this.ctx.stage.curTimes;
                        u.order = this.ctx.stage.curOrder;
                        insertStageBills.push(u);
                    } else {
                        u.id = sb.id;
                        updateStageBills.push(u);
                    }
                }
                for (const cd of result.qc_detail) {
                    cd.tid = this.ctx.tender.id;
                    cd.sid = this.ctx.stage.id;
                    cd.import_lid = lid;
                }

                const his = await this.ctx.service.sumLoadHistory.saveStageHistory(this.ctx.tender.id, this.ctx.stage.id, lid, tenders, result.errors, cover);
                if (updateStageBills.length > 0) await conn.updateRows(this.tableName, updateStageBills);
                if (insertStageBills.length > 0) await conn.insert(this.tableName, insertStageBills);
                if (loadChange) {
                    await conn.delete(this.ctx.service.stageImportChange.tableName, { import_lid: lid, sid: this.ctx.stage.id });
                    if (result.qc_detail.length > 0) await conn.insert(this.ctx.service.stageImportChange.tableName, result.qc_detail);
                }
                await conn.commit();
                return { curStageData: result.update, sumLoadHis: his };
            } catch (err) {
                this.ctx.helper.log(err);
                await conn.rollback();
                throw (err.stack ? '导入工程量数据出错' : err);
            }
        }
    }

    return StageBills;
};