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;

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
         * @returns {Promise<*>}
         */
        async getLastestStageData(tid, sid, lid, pid) {
            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` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And lid = ? And pid = ?' +
                '      GROUP By `lid`, `pid`, `cid`, `cbid`' +
                '  ) 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.`cid` = m.`cid` And c.`cbid` = m.`cbid`' +
                '  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];
            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
         * @returns {Promise<*>}
         */
        async getAuditorStageData(tid, sid, times, order, lid, pid) {
            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` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?)) And lid = ? And pid = ?' +
                '      GROUP By `lid`, `pid`' +
                '  ) 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.`cid` = m.`cid` And c.`cbid` = m.`cbid`' +
                '  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];
            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' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid` From ' + this.tableName +
                '      WHERE tid = ? And sid = ?' +
                '      GROUP By `lid`, `pid`' +
                '  ) 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.`cid` = m.`cid` And c.`cbid` = m.`cbid`' +
                '  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' +
                '  FROM ' + this.tableName + ' As c ' +
                '  INNER JOIN ( ' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `progress`, `lid`, `pid`, `sid`, `cid`, `cbid` From ' + this.tableName +
                '      WHERE tid = ? And sid = ? And (`stimes` < ? OR (`stimes` = ? AND `sorder` <= ?))' +
                '      GROUP By `lid`, `pid`' +
                '  ) 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.`cid` = m.`cid` And c.`cbid` = m.`cbid`' +
                '  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 - 调用的变更令
         * @returns {Promise<void>}
         */
        async billsChange(bills, changes) {
            const self = this;
            function getNewChange(cid, cbid, times, order, qty) {
                return {
                    tid: self.ctx.tender.id,
                    sid: self.ctx.stage.id,
                    lid: bills.id,
                    pid: -1,
                    cid: cid,
                    cbid: cbid,
                    stimes: times,
                    sorder: order,
                    qty: qty
                }
            }
            const ledgerBills = await this.ctx.service.ledger.getDataById(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);
            // 获取更新数据
            const updateChanges = [], newChanges = [];
            let billsQty = 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);
                    billsQty = this.ctx.helper.add(billsQty, 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);
                }
            }
            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));
                    billsQty = this.ctx.helper.add(billsQty, change.qty);
                    newChanges.push(change);
                }
            }
            // 更新数据
            const transaction = await this.db.beginTransaction();
            try {
                if (newChanges.length > 0) {
                    await transaction.insert(this.tableName, newChanges);
                }
                for (const c of updateChanges) {
                    await transaction.update(this.tableName, c);
                }
                const stageBills = await this.ctx.service.stageBills.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, bills.id);
                await this.ctx.service.stageBills.updateStageBillsQty(transaction, ledgerBills, stageBills, { qc_qty: billsQty });
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            const result = await this.ctx.service.stageBills.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, [bills.id]);
            return { bills: {curStageData: result} };
        }

        /**
         * 部位明细，调用变更令
         *
         * @param {Object} pos - 部位明细数据
         * @param {Array} changes - 调用的变更令
         * @returns {Promise<{}>}
         */
        async posChange(pos, changes) {
            const self = this;
            function getNewChange(cid, cbid, times, order, qty) {
                return {
                    tid: self.ctx.tender.id,
                    sid: self.ctx.stage.id,
                    lid: pos.lid,
                    pid: pos.id,
                    cid: cid,
                    cbid: cbid,
                    stimes: times,
                    sorder: order,
                    qty: qty
                }
            }
            const ledgerBills = await this.ctx.service.ledger.getDataById(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);
            const updateChanges = [], newChanges = [];
            let posQty = 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);
                    posQty = this.ctx.helper.add(posQty, 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);
                }
            }
            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));
                    posQty = this.ctx.helper.add(posQty, change.qty);
                    newChanges.push(change);
                }
            }
            // 更新数据
            const transaction = await this.db.beginTransaction();
            try {
                if (newChanges.length > 0) {
                    await transaction.insert(this.tableName, newChanges);
                }
                for (const c of updateChanges) {
                    await transaction.update(this.tableName, c);
                }
                await this.ctx.service.stagePos.updateChangeQuantity(transaction, pos, posQty);
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            // 获取返回数据
            try {
                const data = { bills: {}, pos: {} };
                data.bills.curStageData = await this.ctx.service.stageBills.getLastestStageData(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
         * @returns {Promise<void>}
         */
        async getUsedData(tid, cid) {
            const lastStage = await this.ctx.service.stage.getLastestStage(tid, true);
            let filter;
            if (this.ctx.stage && lastStage.id === this.ctx.stage.id) {
                filter = this.db.format(' And (s.`order` < ? || (s.`order` = ? And sChange.`stimes` <= ? And sChange.`sorder` <= ?))',
                    [lastStage.order, lastStage.order, this.ctx.stage.curTimes, this.ctx.stage.curOrder]);
            } else {
                if (lastStage.status === audit.stage.status.uncheck) {
                    filter = 'And s.order < ' + lastStage.order;
                } else if (lastStage.status === audit.stage.status.checked) {
                    filter = '';
                } else if (lastStage.status === audit.stage.status.checkNo) {
                    filter = this.db.format(' And (s.`order` < ? || (s.`order` = ? And sChange.`stimes` <= ?))',
                        [lastStage.order, lastStage.order, lastStage.times])
                } else {
                    const curAuditor = await this.ctx.service.stageAudit.getCurAuditor(lastStage.id, lastStage.times);
                    filter = this.db.format(' And (s.`order` < ? || (s.`order` = ? And sChange.`stimes` <= ? And sChange.`sorder` <= ?))',
                        [lastStage.order, lastStage.order, lastStage.times, curAuditor.order - 1]);
                }
            }

            const sql = 'SELECT c.lid, c.pid, SUM(c.qty) as used_qty,' +
                        '    cb.tid, cb.cid, cb.id, cb.code, cb.name, cb.unit, cb.unit_price, cb.detail, cb.samount' +
                        '  FROM ' + this.ctx.service.changeAuditList.tableName + ' As cb' +
                        '  LEFT JOIN ' + this.tableName + ' As c ON cb.id = c.cbid ' +
                        '  INNER JOIN (' +
                        '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, `lid`, `pid`, `cbid`, sChange.`sid`, `cid` ' +
                        '      FROM ' + this.tableName + ' As sChange' +
                        '      LEFT JOIN ' + this.ctx.service.stage.tableName + ' As s ON sChange.sid = s.id' +
                        '      WHERE sChange.tid = ? AND cid = ?' + filter +
                        '      GROUP By `lid`, `pid`, `cbid`, sChange.`sid`' +
                        '  ) As m' +
                        '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.flow And c.`cbid` = m.`cbid` AND c.`sid` = m.`sid` And c.`cid` = m.`cid` And c.`lid` = m.`lid` And c.`pid` = m.`pid`' +
                        '  WHERE cb.cid = ?' +
                        '  GROUP By c.`cbid`';
            const sqlParam = [tid, cid, cid];
            return await this.db.query(sql, sqlParam);
        }

        async getFinalUsedData(tid, cid) {
            const sql = 'SELECT c.lid, c.pid, SUM(c.qty) as used_qty,' +
                '    cb.tid, cb.cid, cb.id, cb.code, cb.name, cb.unit, cb.unit_price, cb.detail, cb.samount' +
                '  FROM ' + this.ctx.service.changeAuditList.tableName + ' As cb' +
                '  LEFT JOIN ' + this.tableName + ' As c ON cb.id = c.cbid ' +
                '  INNER JOIN (' +
                '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, `lid`, `pid`, `cbid`, sChange.`sid`, `cid` ' +
                '      FROM ' + this.tableName + ' As sChange' +
                '      LEFT JOIN ' + this.ctx.service.stage.tableName + ' As s ON sChange.sid = s.id' +
                '      WHERE sChange.tid = ? AND cid = ?' +
                '      GROUP By `lid`, `pid`, `cbid`, sChange.`sid`' +
                '  ) As m' +
                '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.flow And c.`cbid` = m.`cbid` AND c.`sid` = m.`sid` And c.`cid` = m.`cid` And c.`lid` = m.`lid` And c.`pid` = m.`pid`' +
                '  WHERE cb.cid = ?' +
                '  GROUP By c.`cbid`';
            const sqlParam = [tid, cid, cid];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取 变更令 - 变更清单 当期使用情况
         * @param {Number} sid - 查询期id
         * @param {uuid} cid - 变更令id
         * @returns {Promise<*>}
         */
        async getStageUsedData(sid, cid) {
            const sql = 'SELECT c.*, ' +
                        '    l.ledger_id As `ledger_id`, l.b_code As `l_code`, l.name As `l_name`, l.unit As `l_unit`, l.unit_price As `l_up`,' +
                        '    l.deal_qty As `l_deal_qty`, l.deal_tp As `l_deal_tp`, l.quantity As `l_qty`, l.total_price As `l_tp`, ' +
                        '    l.drawing_code As `l_drawing_code`, ' +
                        '    p.name As `p_name`, p.drawing_code As `p_drawing_code`, p.`quantity` As `p_qty`' +
                        '  FROM ' + this.tableName + ' As c ' +
                        '  INNER JOIN ( ' +
                        '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, `lid`, `pid`, `cbid` From ' + this.tableName +
                        '      WHERE sid = ? And cid = ?' +
                        '      GROUP By `lid`, `pid`, `cbid`' +
                        '  ) As m ' +
                        '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.flow And c.lid = m.lid And c.pid = m.pid And c.cbid = m.cbid' +
                        '  LEFT JOIN ' + this.ctx.service.ledger.tableName + ' As l ON c.lid = l.id' +
                        '  LEFT JOIN ' + this.ctx.service.pos.tableName + ' As p ON c.pid = p.id';
            const sqlParam = [sid, cid];
            return await this.db.query(sql, sqlParam);
        }

        /**
         * 获取 本期 使用的变更令
         * @param sid
         * @returns {Promise<void>}
         */
        async getStageUsedChangeId(sid) {
            const sql = 'SELECT c.`cid`, sum(qty) As qty FROM ' + this.tableName + ' As c' +
                        '  INNER JOIN (' +
                        '    SELECT MAX(`stimes` * ' + timesLen + ' + `sorder`) As `flow`, `lid`, `pid`, `cbid` From ' + this.tableName +
                        '      WHERE sid = ?' +
                        '      GROUP By `lid`, `pid`, `cbid`' +
                        '  ) As m' +
                        '  ON (c.stimes * ' + timesLen + ' + c.sorder) = m.flow And c.lid = m.lid And c.pid = m.pid And c.cbid = m.cbid' +
                        '  GROUP BY c.`cid`';
            const sqlParam = [sid];
            const result = await this.db.query(sql, sqlParam);
            return this._.map(this._.filter(result, 'qty'), 'cid');
        }
    }

    return StageChange;

};