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 = 10;
            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.filterLastestData(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 getStageUsedBills(tid, sid) {
            const sql = 'SELECT Bills.lid, ((not IsNull(Bills.contract_qty) and Bills.contract_qty <> 0) or (not IsNull(Bills.qc_qty) and Bills.qc_qty <> 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');
        }

        /**
         * 获取截止本期数据
         * @param {Number} tid - 标段id
         * @param {Number} sorder - 截止期序号
         * @param {String|Array[String]} lid - 台账id
         * @returns {Promise<*>}
         */
        async getEndStageData(tid, sorder, lid) {
            let lidSql = '';
            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.tid, Bills.lid,' +
                '  Sum(Bills.contract_qty) As contract_qty, Sum(Bills.contract_tp) As contract_tp,' +
                '  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`, `lid`, `sid` From ' + this.tableName +
                '      WHERE tid = ? ' + lidSql +
                '      GROUP BY `lid`, `sid`' +
                '  ) As MaxFilter ' +
                '  ON (Bills.times * ' + timesLen + ' + `order`) = MaxFilter.progress And Bills.lid = MaxFilter.lid And Bills.`sid` = MaxFilter.`sid`' +
                '  INNER JOIN ' + this.ctx.service.stage.tableName + ' As Stage' +
                '  ON Bills.sid = Stage.id' +
                '  WHERE Stage.order <= ?' +
                '  GROUP BY `lid`';
            const sqlParam = [tid, sorder];
            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);

        }

        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);
        }

        _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.contract_qty !== undefined) {
                data.contract_qty = this.round(data.contract_qty, precision.value);
                data.contract_tp = this.ctx.helper.mul(data.contract_qty, ledgerData.unit_price, info.decimal.tp);
            }
            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 (ledgerData.is_tp && data.contract_tp !== undefined) {
                data.contract_tp = this.ctx.helper.round(data.contract_tp, 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.postil = orgData.postil;
            }
            const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, ledgerData.unit);

            if (insertData.contract_qty !== undefined) {
                d.contract_qty = this.round(insertData.contract_qty, precision.value);
                d.contract_tp = this.ctx.helper.mul(d.contract_qty, ledgerData.unit_price, info.decimal.tp);
            }
            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.postil) {
                d.postil = insertData.postil;
            }
            if (ledgerData.is_tp && insertData.contract_tp !== undefined) {
                d.contract_tp = this.ctx.helper.round(insertData.contract_tp, 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.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, d.lid);
                    const ledgerBills = await this.ctx.service.ledger.getDataById(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;
                        this._calcStageBillsData(d, stageBills, ledgerBills);
                        await transaction.update(this.tableName, d);
                    }
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }
            return await this.getLastestStageData(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)) {
                    if (stageBills.times === this.ctx.stage.curTimes && stageBills.order === this.ctx.stage.curOrder) {
                        data.id = stageBills.id;
                        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, sid, lid, transaction) {
            const info = this.ctx.tender.info;
            const stageBills = await this.getLastestStageData(tid, sid, lid);
            const ledgerBills = await this.ctx.service.ledger.getDataById(lid);
            if (!ledgerBills) {
                throw '提交数据错误';
            }
            const posGather = await this.ctx.service.stagePos.getPosGather(tid, sid, lid, transaction);
            if (!posGather) { return; }

            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 = this.ctx.helper.mul(posGather.contract_qty, ledgerBills.unit_price, info.decimal.tp);
            }
            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 (stageBills) {
                if (stageBills.contract_qty === posGather.contract_qty && stageBills.qc_qty === posGather.qc_qty) {
                    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.getLastestStageData(this.ctx.tender.id, this.ctx.stage.id, data.id);
            const updateData = { contract_qty: null, contract_tp: null };

            const transaction = await this.db.beginTransaction();
            try {
                await transaction.update(this.ctx.service.ledger.tableName, data);
                if (stageBills) {
                    if (stageBills.times !== this.ctx.stage.curTimes || stageBills.order !== this.ctx.stage.curOrder) {
                        const ledgerBills = await this.ctx.service.ledger.getDataById(data.id);
                        await this._insertStageBillsData(transaction, updateData, stageBills, ledgerBills);
                    } else {
                        updateData.id = stageBills.id;
                        await transaction.update(this.tableName, updateData);
                    }
                }
                await transaction.commit();
            } catch (err) {
                await transaction.rollback();
                throw err;
            }

            const bills = await this.ctx.service.ledger.getDataById(data.id);
            const curStageData = await this.getLastestStageData(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` 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 getSumTotalPriceFilter(stage, operate, filter) {
            const sql = 'SELECT Sum(`contract_tp`) As `contract_tp`, Sum(`qc_tp`) As `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: 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_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(',') : [];
            let gather_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) {
                    gather_qty = this.ctx.helper.add(gather_qty, this.ctx.helper.add(result.contract_qty, result.qc_qty));
                }
            }
            return gather_qty !== 0 ? gather_qty : null;
        }

        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) {
            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.getDataById(lid);
                const sumLoad = new SumLoad(this.ctx);
                const loadTree = await sumLoad.stageGatherGcl(select, maxId, tenders);
                const result = loadTree.getUpdateData();
                if (result.errors.length > 100) throw '您导入的数据存在大量数据错误，请您仔细检查';

                const stageBills = await this.getLastestStageData(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.qc_qty = sb ? sb.qc_qty : null;
                        u.qc_tp = sb ? sb.qc_tp : null;
                        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);
                    }
                }

                const his = await this.ctx.service.sumLoadHistory.saveStageHistory(this.ctx.tender.id, this.ctx.stage.id, lid, tenders, result.errors);
                if (updateStageBills.length > 0) await conn.updateRows(this.tableName, updateStageBills);
                if (insertStageBills.length > 0) await conn.insert(this.tableName, insertStageBills);
                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;
};