Calculation/app/service/stage_change.js

'use strict';

/**
 * 期 - 变更数据
 *
 * @author Mai
 * @date
 * @version
 */

const defaultPid = -1; // 非pid
const audit = require('../const/audit');
const timesLen = audit.stage.timesLen;
const changeConst = require('../const/change');

class autoUseChange {
    constructor(helper, info, settleStatus) {
        this.helper = helper;
        this.precision = info.precision;
        this.decimal = info.decimal;
        this.settleStatus = settleStatus;

        this.insertBills = [];
        this.insertPos = [];
        this.updateBills = [];
        this.updatePos = [];
        this.insertChange = [];
        this.updateChange = [];

        this.changeBills = {};
        this.changePos = {};
        this.changeDetail = [];
    }

    init(source) {
        this.default = source.default;

        const LedgerModel = require('../lib/ledger');
        this.ledgerTree = new LedgerModel.billsTree(this.ctx, {
            id: 'ledger_id', pid: 'ledger_pid', order: 'order', level: 'level', rootId: -1, calcFields: [],
        });
        this.pos = new LedgerModel.pos({ id: 'id', ledgerId: 'lid' });
        this.ledgerTree.loadDatas(source.ledgerData);
        this.pos.loadDatas(source.posData);

        this.stageBills = source.stageBills;
        this.stagePos = source.stagePos;
        this.stageChange = source.stageChange || [];
    }

    findBillsPos(changeBills){
        if (changeBills.gcl_id) {
            const node = this.ledgerTree.datas.find(x => {return x.id === changeBills.gcl_id});
            if (node.settle_status === this.settleStatus.finish) return null;

            const posData = this.pos.getLedgerPos(node.id) || [];
            const changePos = posData.find(x => { return x.name === changeBills.bwmx; });
            if (changePos.settle_status) return null;

            let defaultPos;
            if (posData.length > 0) {
                defaultPos = posData.length > 0 ? posData.find(x => { return !x.settle_status}) : null;
                if (!defaultPos) return null;
            } else {
                defaultPos = { id: '-1' };
            }
            return { bills: node, lid: node.id, pid: changePos ? changePos.id : defaultPos.id };
        } else {
            const cb = {
                b_code: changeBills.code || '',
                name: changeBills.name || '',
                unit: changeBills.unit || '',
                unit_price: changeBills.unit_price || 0,
                is_tp: false,
            };
            for (const node of this.ledgerTree.nodes) {
                if (node.children && node.children.length > 0) continue;
                if (node.settle_status === this.settleStatus.finish) return null;

                const b = {
                    b_code: node.b_code || '',
                    name: node.name || '',
                    unit: node.unit || '',
                    unit_price: node.unit_price || 0,
                    is_tp: !!node.is_tp,
                };
                if (this.helper._.isMatch(cb, b)) {
                    const posData = this.pos.getLedgerPos(node.id) || [];
                    let defaultPos;
                    if (posData.length > 0) {
                        defaultPos = posData.length > 0 ? posData.find(x => { return !x.settle_status}) : null;
                        if (!defaultPos) return null;
                    } else {
                        defaultPos = { id: '-1' };
                    }
                    return { bills: node, lid: node.id, pid: defaultPos.id };
                }
            }
            return null;
        }
    }

    useBills(bills) {
        if (bills.billsPos) bills.billsPos.bills = this.ledgerTree.datas.find(x => {return x.id === bills.billsPos.lid; });
        const billsPos = bills.billsPos || this.findBillsPos(bills);
        if (!billsPos) return;
        const bamount = parseFloat(bills.samount);
        const minus = bamount < 0;
        this.changeDetail.push({
            tid: this.default.tid, sid: this.default.sid,
            lid: billsPos.lid, pid: billsPos.pid + '', cid: bills.cid, cbid: bills.id,
            qty: bills.valid_qty, stimes: 1, sorder: 0, unit_price: bills.unit_price, minus, no_value: !bills.is_valuation,
        });

        if (!bills.is_valuation) return;
        if (billsPos.pid !== '-1') {
            let cp = this.changePos[billsPos.pid];
            if (!cp) {
                cp = { lid: billsPos.lid, pid: billsPos.pid, qty: 0, bills: billsPos.bills };
                this.changePos[billsPos.pid] = cp
            }
            cp.qty = this.helper.add(cp.qty, bills.valid_qty);
            if (minus) {
                cp.negative_qc_qty = cp.qty;
            } else {
                cp.positive_qc_qty = cp.qty;
            }
        } else {
            let cb = this.changeBills[billsPos.lid];
            if (!cb) {
                cb = { lid: billsPos.lid, qty: 0, bills: billsPos.bills };
                this.changeBills[billsPos.lid] = cb;
            }
            cb.qty = this.helper.add(cb.qty, bills.valid_qty);
            if (minus) {
                cb.negative_qc_qty = this.helper.add(cb.negative_qc_qty, bills.valid_qty);
            } else {
                cb.positive_qc_qty = this.helper.add(cb.positive_qc_qty, bills.valid_qty);
            }
        }
    }

    calculateAll() {
        for (const cd of this.changeDetail) {
            const sci = this.stageChange.findIndex(x => {
                return x.lid === cd.lid && x.pid === cd.pid && x.cbid === cd.cbid;
            });
            const sc = this.stageChange[sci];
            if (sc) {
                this.updateChange.push({ id: sc.id, qty: cd.qty });
                this.stageChange.splice(sci, 1);
            } else {
                this.insertChange.push(cd);
            }
        }
        for (const sc of this.stageChange) {
            if (sc.no_value) continue;
            const cp = this.changePos[sc.pid];
            if (cp) {
                cp.qty = this.helper.add(cp.qty, sc.qty);
                if (sc.minus) {
                    cp.negative_qc_qty = this.helper.add(cp.negative_qc_qty, sc.qty);
                } else {
                    cp.positive_qc_qty = this.helper.add(cp.positive_qc_qty, sc.qty);
                }
            }
        }
        for (const pid in this.changePos) {
            const cp = this.changePos[pid];
            if (!cp) continue;

            const precision = this.helper.findPrecision(this.precision, cp.bills.unit);
            const qc_qty = this.helper.round(cp.qty, precision.value);
            const positive_qc_qty = this.helper.round(cp.positive_qc_qty || 0, precision.value);
            const negative_qc_qty = this.helper.round(cp.negative_qc_qty || 0, precision.value);
            const sp = this.stagePos.find(x => {return x.pid === pid});
            if (sp) {
                this.updatePos.push({ id: sp.id, qc_qty, positive_qc_qty, negative_qc_qty });
            } else {
                this.insertPos.push({
                    tid: this.default.tid, sid: this.default.sid, said: this.default.said,
                    lid: cp.lid, pid, qc_qty, positive_qc_qty, negative_qc_qty, times: 1, order: 0
                });
            }
            const cb = this.changeBills[cp.lid];
            if (!cb) {
                this.changeBills[cp.lid] = { lid: cp.lid, qty: qc_qty, positive_qc_qty, negative_qc_qty, bills: cp.bills };
            } else {
                cb.qty = this.helper.add(cb.qty, qc_qty);
                cb.positive_qc_qty = this.helper.add(cb.positive_qc_qty, positive_qc_qty);
                cb.negative_qc_qty = this.helper.add(cb.negative_qc_qty, negative_qc_qty);
            }
        }
        for (const sc of this.stageChange) {
            if (sc.no_value) continue;
            const cp = this.changePos[sc.pid];
            if (cp) continue;
            const cb = this.changeBills[sc.lid];
            if (cb) {
                cb.qty = this.helper.add(cb.qty, sc.qty);
                if (sc.minus) {
                    cb.negative_qc_qty = this.helper.add(cb.negative_qc_qty, sc.qty);
                } else {
                    cb.positive_qc_qty = this.helper.add(cb.positive_qc_qty, sc.qty);
                }
            }
        }
        for (const lid in this.changeBills) {
            const cb = this.changeBills[lid];
            if (!cb) continue;
            const precision = this.helper.findPrecision(this.precision, cb.bills.unit);
            const qc_qty = this.helper.round(cb.qty, precision.value);
            const qc_tp = this.helper.mul(cb.qty, cb.bills.unit_price, this.decimal.tp);
            const positive_qc_qty = this.helper.round(cb.positive_qc_qty || 0, precision.value);
            const positive_qc_tp = this.helper.mul(positive_qc_qty, cb.bills.unit_price, this.decimal.tp);
            const negative_qc_qty = this.helper.round(cb.negative_qc_qty || 0, precision.value);
            const negative_qc_tp = this.helper.mul(negative_qc_qty, cb.bills.unit_price, this.decimal.tp);
            const sb = this.stageBills.find(x => {return x.lid === lid});
            if (sb) {
                this.updateBills.push({ id: sb.id, qc_qty, qc_tp, positive_qc_qty, positive_qc_tp, negative_qc_qty, negative_qc_tp });
            } else {
                this.insertBills.push({
                    tid: this.default.tid, sid: this.default.sid, said: this.default.said,
                    lid, qc_qty, qc_tp, positive_qc_qty, positive_qc_tp, negative_qc_qty, negative_qc_tp, times: 1, order: 0
                });
            }
        }
    }

    use(source, validChangeBills) {
        this.init(source);

        for (const bills of validChangeBills) {
            this.useBills(bills);
        }
        this.calculateAll();
    }
}

module.exports = app => {

    class StageChange extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'stage_change';
        }

        /**
         * 查询 调用的变更令 最新数据
         * @param {Number} tid - 标段id
         * @param {Number} sid - 期id
         * @param {Number} lid - 台账节点id
         * @param {Number} pid - 部位明细id
         * @return {Promise<*>}
         */
        async getLastestStageData(tid, sid, lid, pid, noValue) {
            const filter = noValue !== undefined ? ' And no_value = ?' : '';
            const sql = 'SELECT c.*,' +
                '  oc.p_code As c_code, oc.new_code As c_new_code' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid`, `no_value` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And lid = ? And pid = ?' + filter +
                '      GROUP By `lid`, `pid`, `cbid`, `no_value`' +
                '  ) As m ' +
                '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.progress And c.lid = m.lid And c.pid = m.pid And c.`sid` = m.`sid` And c.`cbid` = m.`cbid` And c.`no_value` = m.`no_value`' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' As oc' +
                '  ON c.cid = oc.cid' +
                '  LEFT JOIN ' + this.ctx.service.changeAuditList.tableName + ' As ocb' +
                '  ON c.cbid = ocb.id' +
                '  WHERE not ISNULL(ocb.id)';
            const sqlParam = [tid, sid, lid, pid ? pid : -1];
            if (noValue !== undefined) sqlParam.push(noValue);
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 查询 调用的变更令 某轮 某人的数据
         * @param {Number} tid - 标段id
         * @param {Number} sid - 期id
         * @param {Number} times - 第几轮
         * @param {Number} order - 第几人
         * @param {Number} lid - 台账节点id
         * @param {Number} pid - 部位明细id
         * @return {Promise<*>}
         */
        async getAuditorStageData(tid, sid, times, order, lid, pid, noValue) {
            const filter = noValue !== undefined ? ' And no_value = ?' : '';
            const sql = 'SELECT c.*,' +
                '  oc.p_code As c_code, oc.new_code As c_new_code, oc.quality, ocb.code as b_code, ocb.name, ocb.unit' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid`, `no_value` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?)) And lid = ? And pid = ?' + filter +
                '      GROUP By `lid`, `pid`, cbid, no_value' +
                '  ) As m ' +
                '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.progress And c.lid = m.lid And c.pid = m.pid And c.`sid` = m.`sid` And c.`cbid` = m.`cbid` And c.`no_value` = m.`no_value`' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' As oc' +
                '  ON c.cid = oc.cid' +
                '  LEFT JOIN ' + this.ctx.service.changeAuditList.tableName + ' As ocb' +
                '  ON c.cbid = ocb.id' +
                '  WHERE not ISNULL(ocb.id)';
            const sqlParam = [tid, sid, times, times, order, lid, pid ? pid : -1];
            if (noValue !== undefined) sqlParam.push(noValue);
            return await this.db.query(sql, sqlParam);
        }

        async getLastestAllStageData(tid, sid) {
            const sql = 'SELECT c.*,' +
                '  oc.p_code As c_code, oc.new_code As c_new_code, oc.quality, ocb.code as b_code, ocb.name, ocb.unit' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid`, `no_value` From ' + this.tableName +
                '      WHERE tid = ? And sid = ?' +
                '      GROUP By `lid`, `pid`, cbid, no_value' +
                '  ) As m ' +
                '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.progress And c.lid = m.lid And c.pid = m.pid And c.`sid` = m.`sid` And c.`cbid` = m.`cbid` And c.`no_value` = m.`no_value`' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' As oc' +
                '  ON c.cid = oc.cid' +
                '  LEFT JOIN ' + this.ctx.service.changeAuditList.tableName + ' As ocb' +
                '  ON c.cbid = ocb.id' +
                '  WHERE not ISNULL(ocb.id)';
            const sqlParam = [tid, sid];
            return await this.db.query(sql, sqlParam);
        }

        async getAuditorAllStageData(tid, sid, times, order) {
            const sql = 'SELECT c.*, ' +
                '  oc.p_code As c_code, oc.new_code As c_new_code, oc.quality, ocb.code as b_code, ocb.name, ocb.unit' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid`, `no_value` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?))' +
                '      GROUP By `lid`, `pid`, cbid, no_value' +
                '  ) As m ' +
                '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.progress And c.lid = m.lid And c.pid = m.pid And c.`sid` = m.`sid` And c.`cbid` = m.`cbid` And c.`no_value` = m.`no_value`' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' As oc' +
                '  ON c.cid = oc.cid' +
                '  LEFT JOIN ' + this.ctx.service.changeAuditList.tableName + ' As ocb' +
                '  ON c.cbid = ocb.id' +
                '  WHERE not ISNULL(ocb.id)';
            const sqlParam = [tid, sid, times, times, order];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 台账，调用变更令
         *
         * @param {Object} bills - 台账节点数据
         * @param {Array} changes - 调用的变更令
         * @return {Promise<void>}
         */
        async billsChange(bills, noValue, changes) {
            const self = this;
            function getNewChange(cid, cbid, times, order, qty, minus, no_value) {
                return {
                    tid: self.ctx.tender.id, sid: self.ctx.stage.id,
                    lid: bills.id, pid: -1,
                    cid, cbid,
                    stimes: times, sorder: order,
                    qty, minus, no_value,
                };
            }
            const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(bills.id);
            if (!ledgerBills || ledgerBills.tender_id !== this.ctx.tender.id) {
                throw '提交数据错误';
            }
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerBills.unit);
            // 获取原变更令
            const oldChanges = await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, bills.id, -1, noValue);
            // 获取更新数据
            const updateChanges = [],
                newChanges = [];
            let billsQty = 0, billsPositiveQty = 0, billsNegativeQty = 0;
            for (const oc of oldChanges) {
                const nc = this._.find(changes, { cid: oc.cid, cbid: oc.cbid });
                if (!nc || nc.qty !== oc.qty) {
                    const qty = nc ? this.round(nc.qty, precision.value) : null;
                    const change = getNewChange(oc.cid, oc.cbid, this.ctx.stage.curTimes, this.ctx.stage.curOrder, qty, nc ? nc.minus : oc.minus, noValue);
                    change.unit_price = ledgerBills.unit_price;
                    billsQty = this.ctx.helper.add(billsQty, change.qty);
                    if (change.minus) {
                        billsNegativeQty = this.ctx.helper.add(billsNegativeQty, change.qty);
                    } else {
                        billsPositiveQty = this.ctx.helper.add(billsPositiveQty, change.qty);
                    }
                    if (oc.stimes === this.ctx.stage.curTimes && oc.sorder === this.ctx.stage.curOrder) {
                        change.id = oc.id;
                        updateChanges.push(change);
                    } else {
                        newChanges.push(change);
                    }
                } else {
                    billsQty = this.ctx.helper.add(billsQty, oc.qty);
                    if (oc.minus) {
                        billsNegativeQty = this.ctx.helper.add(billsNegativeQty, oc.qty);
                    } else {
                        billsPositiveQty = this.ctx.helper.add(billsPositiveQty, oc.qty);
                    }
                }
            }
            for (const c of changes) {
                const nc = this._.find(oldChanges, { cid: c.cid, cbid: c.cbid });
                if (!nc) {
                    const change = getNewChange(c.cid, c.cbid, this.ctx.stage.curTimes, this.ctx.stage.curOrder, this.round(c.qty, precision.value), c.minus, noValue);
                    change.unit_price = ledgerBills.unit_price;
                    billsQty = this.ctx.helper.add(billsQty, change.qty);
                    if (change.minus) {
                        billsNegativeQty = this.ctx.helper.add(billsNegativeQty, change.qty);
                    } else {
                        billsPositiveQty = this.ctx.helper.add(billsPositiveQty, change.qty);
                    }
                    newChanges.push(change);
                }
            }
            // 更新数据
            const transaction = await this.db.beginTransaction();
            try {
                if (newChanges.length > 0) await transaction.insert(this.tableName, newChanges);
                if (updateChanges.length > 0) await transaction.updateRows(this.tableName, updateChanges);
                const stageBills = await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, bills.id);
                const sbUpdate = noValue
                    ? { qc_minus_qty: billsQty }
                    : { qc_qty: billsQty, positive_qc_qty: billsPositiveQty, negative_qc_qty: billsNegativeQty };
                await this.ctx.service.stageBills.updateStageBillsQty(transaction, ledgerBills, stageBills, sbUpdate);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            const result = await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, [bills.id]);
            return { bills: { curStageData: result } };
        }

        /**
         * 部位明细，调用变更令
         *
         * @param {Object} pos - 部位明细数据
         * @param {Array} changes - 调用的变更令
         * @return {Promise<{}>}
         */
        async posChange(pos, noValue, changes) {
            const self = this;
            function getNewChange(cid, cbid, times, order, qty, minus, no_value) {
                return {
                    tid: self.ctx.tender.id, sid: self.ctx.stage.id,
                    lid: pos.lid, pid: pos.id,
                    cid, cbid,
                    stimes: times, sorder: order,
                    qty, minus, no_value,
                };
            }
            const ledgerBills = await this.ctx.service.ledger.getCompleteDataById(pos.lid);
            if (!ledgerBills || ledgerBills.tender_id !== this.ctx.tender.id) {
                throw '提交数据错误';
            }
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerBills.unit);
            // 获取原变更令
            const oldChanges = await this.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, pos.lid, pos.id, noValue);
            const updateChanges = [],
                newChanges = [];
            let posQty = 0, posPositiveQty = 0, posNegativeQty = 0;
            for (const oc of oldChanges) {
                const nc = this._.find(changes, { cid: oc.cid, cbid: oc.cbid });
                if (!nc || nc.qty !== oc.qty) {
                    const qty = nc ? this.round(nc.qty, precision.value) : null;
                    const change = getNewChange(oc.cid, oc.cbid, this.ctx.stage.curTimes, this.ctx.stage.curOrder, qty, nc ? nc.minus : oc.minus, noValue);
                    change.unit_price = ledgerBills.unit_price;
                    posQty = this.ctx.helper.add(posQty, change.qty);
                    if (change.minus) {
                        posNegativeQty = this.ctx.helper.add(posNegativeQty, change.qty);
                    } else {
                        posPositiveQty = this.ctx.helper.add(posPositiveQty, change.qty);
                    }
                    if (oc.stimes === this.ctx.stage.curTimes && oc.sorder === this.ctx.stage.curOrder) {
                        change.id = oc.id;
                        updateChanges.push(change);
                    } else {
                        newChanges.push(change);
                    }
                } else {
                    posQty = this.ctx.helper.add(posQty, oc.qty);
                    if (oc.minus) {
                        posNegativeQty = this.ctx.helper.add(posNegativeQty, oc.qty);
                    } else {
                        posPositiveQty = this.ctx.helper.add(posPositiveQty, oc.qty);
                    }
                }
            }
            for (const c of changes) {
                const nc = this._.find(oldChanges, { cid: c.cid, cbid: c.cbid });
                if (!nc) {
                    const change = getNewChange(c.cid, c.cbid, this.ctx.stage.curTimes, this.ctx.stage.curOrder, this.round(c.qty, precision.value), c.minus, noValue);
                    change.unit_price = ledgerBills.unit_price;
                    posQty = this.ctx.helper.add(posQty, change.qty);
                    if (change.minus) {
                        posNegativeQty = this.ctx.helper.add(posNegativeQty, change.qty);
                    } else {
                        posPositiveQty = this.ctx.helper.add(posPositiveQty, change.qty);
                    }
                    newChanges.push(change);
                }
            }
            // 更新数据
            const transaction = await this.db.beginTransaction();
            try {
                if (newChanges.length > 0) await transaction.insert(this.tableName, newChanges);
                if (updateChanges.length > 0) await transaction.updateRows(this.tableName, updateChanges);
                await this.ctx.service.stagePos.updateChangeQuantity(transaction, pos, posQty, noValue, posPositiveQty, posNegativeQty);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            // 获取返回数据
            try {
                const data = { bills: {}, pos: {} };
                data.bills.curStageData = await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, [pos.lid]);
                data.pos.curStageData = await this.ctx.service.stagePos.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, { pid: pos.id });
                return data;
            } catch (err) {
                throw '获取数据错误，请刷新页面';
            }
        }

        /**
         * 获取 变更令 - 变更清单 使用情况
         * @param {Number} sid - 查询期id
         * @param {uuid} cid - 变更令id
         * @return {Promise<void>}
         */
        async getUsedData(tid, cid) {
            if (this.ctx.stage.status === audit.stage.status.checked) {
                const sql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    '  WHERE scf.tid = ? And scf.cid = ? And s.order <= ?';
                const result = await this.db.query(sql, [tid, cid, this.ctx.stage.order]);
                return result;
            } else {
                const preSql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    '  WHERE scf.tid = ? And scf.cid = ? And s.order < ?';
                const pre = await this.db.query(preSql, [tid, cid, this.ctx.stage.order]);
                const sql = 'SELECT * FROM ' + this.tableName + ' WHERE sid = ?';
                const curAll = await this.db.query(sql, [this.ctx.stage.id]);
                const cur = this.ctx.helper.filterLastestData(curAll, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
                return [...pre, ...cur];
            }
        }

        async getFinalUsedData(tid, cid) {
            const stage = await this.ctx.service.stage.getLastestStage(tid, true);
            if (!stage) { // 防止未创建期时调用
                return [];
            }
            if (stage.status === audit.stage.status.checked) {
                const sql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    this.ctx.helper.whereSql({ tid, cid }, 'scf') + ' And s.order <= ?';
                const result = await this.db.query(sql, [stage.order]);
                return result;
            } else {
                const preSql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    this.ctx.helper.whereSql({ tid, cid }, 'scf') + ' And s.order < ?';
                const pre = await this.db.query(preSql, [stage.order]);
                const sql = 'SELECT * FROM ' + this.tableName + this.ctx.helper.whereSql({ sid: stage.id, cid });
                const curAll = await this.db.query(sql);
                const cur = this.ctx.helper.filterLastestData(curAll, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
                return [...pre, ...cur];
            }
        }

        /**
         * 获取 变更令 - 变更清单 当期使用情况
         * @param {Number} sid - 查询期id
         * @param {uuid} cid - 变更令id
         * @return {Promise<*>}
         */
        async getStageUsedData(sid, cid) {
            const data = await this.getAllDataByCondition({ where: { sid, cid } });
            const _ = this.ctx.helper._;
            const filter = this.ctx.helper.filterLastestData(data, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
            if (filter.length === 0) return filter;

            const bills = await this.ctx.service.ledger.getAllDataByCondition({
                where: { id: _.uniq(_.map(filter, 'lid')) }
            });
            const pos = await this.ctx.service.pos.getAllDataByCondition({
                where: { id: _.uniq(_.map(filter, 'pid')) }
            });
            return filter.map(x => {
                const b = bills.find(y => { return y.id === x.lid });
                const rela_b = b
                    ? {ledger_id: b ? b.ledger_id : -1, l_code: b.b_code, l_name: b.name, l_unit: b.unit, l_up: b.unit_price,
                        l_deal_qty: b.deal_qty, l_deal_tp: b.deal_tp, l_qty: b.quantity, l_tp: b.total_price, l_drawing_code: b.drawing_code}
                    : { ledger_id:  -1, l_code: '', l_name: '', l_unit: '', l_up: 0, l_deal_qty: 0, l_deal_tp: 0, l_qty: 0, l_tp: 0, l_drawing_code: '' };
                const p = pos.find(y => { return y.id === x.pid });
                const rela_p = p ? { p_name: p.name, p_drawing_code: p.drawing_code, p_qty: p.quantity } : { p_name: '', p_drawing_code: '', p_qty: 0 };
                return { ...x, ...rela_b, ...rela_p };
            });
        }

        /**
         * 获取 本期 使用的变更令
         * @param sid
         * @return {Promise<void>}
         */
        async getStageUsedChangeId(sid) {
            const sql = 'SELECT lid, pid, cid, cbid, qty, stimes, sorder FROM ' + this.tableName + ' WHERE sid = ?';
            const curAll = await this.db.query(sql, [sid]);
            const cur = this.ctx.helper.filterLastestData(curAll, ['lid', 'pid', 'cbid'], 'stimes', 'sorder');
            return this._.map(this._.filter(cur, 'qty'), 'cid');
        }

        async getFinalStageData(tid, sid) {
            const data = await this.getAllDataByCondition({ where: { tid, sid } });
            return this.ctx.helper.filterLastestData(data, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
        }

        async getSumLoadFinalData(sid) {
            const sql = 'Select cf.tid, cf.sid, cf.lid, cf.pid, cf.cid, cf.cbid, cf.qty, cf.stimes, cf.sorder, cf.minus, cf.no_value, c.code As c_code' +
                '  FROM ' + this.tableName + ' cf' +
                '  Left Join ' + this.ctx.service.change.tableName + ' c ON cf.cid = c.cid' +
                '  Where cf.sid = ?';
            const result = await this.db.query(sql, [sid]);
            return this.ctx.helper.filterLastestData(result, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
        }

        async _getTender(stage) {
            if (this.ctx.tender) return this.ctx.tender;
            const tender = { id: stage.tid };
            tender.data = await this.ctx.service.tender.getTender(stage.tid);
            tender.info = await this.service.tenderInfo.getTenderInfo(tender.id);
            return tender;
        }

        async getSubtotal(stage) {
            const helper = this.ctx.helper;
            const tender = await this._getTender(stage);
            const sql = 'SELECT sc.*, c.quality FROM ' + this.tableName + ' sc' +
                '  LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON sc.cid = c.cid' +
                '  WHERE sid = ? ' + (stage.readOnly ? ` and (stimes < ${stage.curTimes} or (stimes = ${stage.curTimes} and sorder <= ${stage.curOrder}))` : '');
            let data = await this.db.query(sql, [stage.id]);
            data = helper.filterLastestData(data, ['lid', 'pid', 'cbid'], 'stimes', 'sorder');
            const bqData = [];
            for (const d of data) {
                if (!d.qty || d.no_value) continue;
                let bd = bqData.find(x => { return x.lid === d.lid && x.quality === d.quality; });
                if (!bd) {
                    bd = { lid: d.lid, quality: d.quality, unit_price: d.unit_price };
                    bqData.push(bd);
                }
                const tp = this.ctx.helper.mul(d.qty, bd.unit_price, tender.info.decimal.tp);
                bd.tp = this.ctx.helper.add(bd.tp, tp);
            }
            const result = {};
            result.common = helper.sum(helper._.map(bqData.filter(x => {return x.quality === changeConst.quality.common.value; }), 'tp'));
            result.more = helper.sum(helper._.map(bqData.filter(x => {return x.quality === changeConst.quality.more.value; }), 'tp'));
            result.great = helper.sum(helper._.map(bqData.filter(x => {return x.quality === changeConst.quality.great.value; }), 'tp'));
            return result;
        }

        async getChangeBillsWithUsedInfo(stage) {
            if (stage.status === audit.stage.status.checked) {
                const sql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    '  WHERE scf.tid = ? And s.order <= ?';
                const result = await this.db.query(sql, [stage.tid, stage.order]);
                return result;
            } else {
                const preSql = 'SELECT scf.* ' +
                    '  FROM ' + this.ctx.service.stageChangeFinal.tableName + ' scf ' +
                    '  LEFT JOIN ' + this.ctx.service.stage.tableName + ' s ON scf.sid = s.id' +
                    '  WHERE scf.tid = ? And s.order < ?';
                const pre = await this.db.query(preSql, [stage.tid, stage.order]);
                const sql = 'SELECT * FROM ' + this.tableName + ' WHERE sid = ? AND (stimes * 100 + sorder) <= (? * 100 + ?)';
                const curAll = await this.db.query(sql, [stage.id, stage.curTimes, stage.curOrder]);
                const cur = this.ctx.helper.filterLastestData(curAll, ['lid', 'pid', 'cbid', 'no_value'], 'stimes', 'sorder');
                return [...pre, ...cur];
            }
        }

        async getChangeWithUsedInfo(stage) {
            const change = await this.ctx.service.change.getAllDataByCondition({
                where: { tid: stage.tid, status: audit.flow.status.checked },
                orders: [['code', 'asc']],
            });
            if (change.length === 0) return [];

            const changeBills = await this.ctx.service.changeAuditList.getAllDataByCondition({
                where: { cid: change.map(x => { return x.cid; }) }
            });
            const changeBillsIndex = {}, changeBillsPart = {};
            for (const cb of changeBills) {
                changeBillsIndex[cb.id] = cb;
                if (!changeBillsPart[cb.cid]) changeBillsPart[cb.cid] = [];
                changeBillsPart[cb.cid].push(cb);
            }
            const stageChangeBills = await this.getChangeBillsWithUsedInfo(stage);
            for (const scb of stageChangeBills) {
                if (!scb.qty) continue;

                const cb = changeBillsIndex[scb.cbid];
                if (cb) cb.used_qty = this.ctx.helper.add(cb.used_qty, scb.qty);
            }
            for (const cid in changeBillsPart) {
                const c = change.find(x => { return x.cid === cid });
                if (!c) continue;

                for (const cb of changeBillsPart[cid]) {
                    cb.tp = this.ctx.helper.mul(cb.spamount, cb.unit_price, c.tp_decimal || this.ctx.tender.info.decimal.tp);
                    cb.used_tp = this.ctx.helper.mul(cb.used_qty, cb.unit_price, this.ctx.tender.info.decimal.tp);

                    c.used_tp = this.ctx.helper.add(c.used_tp, cb.used_tp);
                    if (cb.spamount > 0) {
                        c.p_tp = this.ctx.helper.add(c.p_tp, cb.tp);
                        c.p_used_tp = this.ctx.helper.add(c.p_used_tp, cb.used_tp);
                    } else if (cb.spamount < 0){
                        c.n_tp = this.ctx.helper.add(c.n_tp, cb.tp);
                        c.n_used_tp = this.ctx.helper.add(c.n_used_tp, cb.used_tp);
                    }
                }
                c.used_pt = c.total_price ? this.ctx.helper.mul(this.ctx.helper.div(c.used_tp, c.total_price, 4), 100) : 0;
                c.p_used_pt = c.p_tp ? this.ctx.helper.mul(this.ctx.helper.div(c.p_used_tp, c.p_tp, 4), 100) : 0;
                c.n_used_pt = c.n_tp ? this.ctx.helper.mul(this.ctx.helper.div(c.n_used_tp, c.n_tp, 4), 100) : 0;
            }
            return change;
        }

        async getStageMinusChange(stage) {
            const data = await this.getAllDataByCondition({ where: { sid: stage.id, minus: 1 } });
            return this.ctx.helper.filterLastestData(data, ['lid', 'pid', 'cbid'], 'stimes', 'sorder');
        };

        async getBillsMinusQty(stage, lid) {
            const data = await this.getAllDataByCondition({ where: { sid: stage.id, lid, minus: 1 } });
            const filter = this.ctx.helper.filterLastestData(data, ['lid', 'pid', 'cbid'], 'stimes', 'sorder');
            return { lid, qty: this.ctx.helper.sum(filter.map(x => { return x.qty; })) };
        };

        async getPosMinusQty(stage, pid) {
            const data = await this.getAllDataByCondition({ where: { sid: stage.id, pid, minus: 1 } });
            const filter = this.ctx.helper.filterLastestData(data, ['lid', 'pid', 'cbid'], 'stimes', 'sorder');
            return { pid, qty: this.ctx.helper.sum(filter.map(x => { return x.qty; })) };
        };

        async autoUseChangeBills(tender, stage, data) {
            const lid = [], cbid = [];
            data.forEach(x => {
                lid.push(x.lid);
                cbid.push(x.cbid);
            });
            const validChangeBills = await this.ctx.service.stageChangeFinal.getListChangeBillsValidQty(tender.id, cbid);
            validChangeBills.forEach(x => {
                x.billsPos = data.find(y => { return x.id === y.cbid; });
            });
            const ledgerData = await this.ctx.service.ledger.getAllDataByCondition({
                columns: ['id', 'ledger_id', 'ledger_pid', 'level', 'order', 'full_path', 'is_leaf', 'code', 'b_code', 'name', 'unit', 'unit_price'],
                where: { id: lid },
            });
            const extraData = await this.ctx.service.ledgerExtra.getData(this.ctx.tender.id, ['is_tp']);
            this.ctx.helper.assignRelaData(ledgerData, [
                { data: extraData, fields: ['is_tp'], prefix: '', relaId: 'id' },
            ]);
            const posData = await this.ctx.service.pos.getAllDataByCondition({
                columns: ['id', 'lid', 'name', 'porder'],
                where: { lid },
            });
            const stageBills = await this.ctx.service.stageBills.getAllDataByCondition({ where: { sid: stage.id } });
            const stagePos = await this.ctx.service.stagePos.getAllDataByCondition({ where: { sid: stage.id } });
            const stageChange = await this.ctx.service.stageChange.getAllDataByCondition({ where: { sid: stage.id, lid }});

            const useModal = new autoUseChange(this.ctx.helper, tender.info);
            useModal.use({ledgerData, posData, stageBills, stagePos, stageChange, default: { tid: stage.tid, sid: stage.id, said: this.ctx.session.sessionUser.accountId } }, validChangeBills);

            const conn = await this.db.beginTransaction();
            try {
                if (useModal.insertBills.length > 0) await conn.insert(this.ctx.service.stageBills.tableName, useModal.insertBills);
                if (useModal.updateBills.length > 0) await conn.updateRows(this.ctx.service.stageBills.tableName, useModal.updateBills);
                if (useModal.insertPos.length > 0) await conn.insert(this.ctx.service.stagePos.tableName, useModal.insertPos);
                if (useModal.updatePos.length > 0) await conn.updateRows(this.ctx.service.stagePos.tableName, useModal.updatePos);
                if (useModal.insertChange.length > 0) await conn.insert(this.tableName, useModal.insertChange);
                if (useModal.updateChange.length > 0) await conn.updateRows(this.tableName, useModal.updateChange);
                await conn.commit();
            } catch (err) {
                await conn.rollback();
                this.ctx.log(err);
                throw '保存导入数据失败';
            }

            const rst = { bills: {}, pos: {} };
            rst.bills.curStageData = await this.ctx.service.stageBills.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, lid);
            rst.pos.curStageData = await this.ctx.service.stagePos.getLastestStageData2(this.ctx.tender.id, this.ctx.stage.id, { lid });
            return rst;
        };

        async autoUseAllChange(tender, stage) {
            const validChangeBills = await this.ctx.service.stageChangeFinal.getAllChangeBillsValidQty(tender.id);
            const ledgerData = await this.ctx.service.ledger.getAllDataByCondition({
                columns: ['id', 'ledger_id', 'ledger_pid', 'level', 'order', 'full_path', 'is_leaf', 'code', 'b_code', 'name', 'unit', 'unit_price'],
                where: { tender_id: stage.tid },
            });
            const extraData = await this.ctx.service.ledgerExtra.getData(this.ctx.tender.id, ['is_tp']);
            const settleStatusBills = stage.readySettle ? await this.ctx.service.settleBills.getAllDataByCondition({ where: { settle_id: stage.readySettle.id }}) : [];
            this.ctx.helper.assignRelaData(ledgerData, [
                { data: extraData, fields: ['is_tp'], prefix: '', relaId: 'id' },
                { data: settleStatusBills, fields: ['settle_status'], prefix: '', relaId: 'lid' },
            ]);

            const posData = await this.ctx.service.pos.getAllDataByCondition({
                columns: ['id', 'lid', 'name', 'porder'],
                where: { tid: stage.tid },
            });
            const settleStatusPos = stage.readySettle ? await this.ctx.service.settlePos.getAllDataByCondition({ where: { settle_id: stage.readySettle.id }}) : [];
            this.ctx.helper.assignRelaData(posData, [
                { data: settleStatusPos, fields: ['settle_status'], prefix: '', relaId: 'pid' },
            ]);
            const stageBills = await this.ctx.service.stageBills.getAllDataByCondition({ where: { sid: stage.id } });
            const stagePos = await this.ctx.service.stagePos.getAllDataByCondition({ where: { sid: stage.id } });
            const useModal = new autoUseChange(this.ctx.helper, tender.info, this.ctx.service.settle.settleStatus);
            useModal.use({ledgerData, posData, stageBills, stagePos, default: { tid: stage.tid, sid: stage.id, said: this.ctx.session.sessionUser.accountId } }, validChangeBills);

            // if (useModal.insertChange.length === 0) return '无可调用的清单或计量单元';

            const conn = await this.db.beginTransaction();
            try {
                if (useModal.insertBills.length > 0) await conn.insert(this.ctx.service.stageBills.tableName, useModal.insertBills);
                if (useModal.updateBills.length > 0) await conn.updateRows(this.ctx.service.stageBills.tableName, useModal.updateBills);
                if (useModal.insertPos.length > 0) await conn.insert(this.ctx.service.stagePos.tableName, useModal.insertPos);
                if (useModal.updatePos.length > 0) await conn.updateRows(this.ctx.service.stagePos.tableName, useModal.updatePos);
                await conn.delete(this.tableName, { sid: stage.id });
                if (useModal.insertChange.length > 0) await conn.insert(this.tableName, useModal.insertChange);
                await conn.commit();
            } catch (err) {
                await conn.rollback();
                this.ctx.log(err);
                throw '保存导入数据失败';
            }
        }

        async updateStageChange4CheckCancel(sid, newTimes, newOrder, oldTimes, oldOrder, transaction) {
            const oldSBLists = await this.getAllDataByCondition({ where: { sid, stimes: oldTimes, sorder: oldOrder } });
            const newSBLists = await this.getAllDataByCondition({ where: { sid, stimes: newTimes, sorder: 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, {
                    stimes: newTimes,
                    sorder: newOrder,
                }, { where: { id: this._.map(oldSBLists, 'id') } });
            }
        }
    }

    return StageChange;

};