Calculation/app/service/ledger.js

'use strict';

/**
 * 标段--台账 数据模型
 *
 * @author CaiAoLin
 * @date 2017/12/1
 * @version
 */

const needField = {
    id: 'ledger_id',
    pid: 'ledger_pid',
    order: 'order',
    level: 'level',
    fullPath: 'full_path',
    isLeaf: 'is_leaf',
};
const keyFields = {
    table: ['id'],
    index: ['tender_id', 'ledger_id'],
};
// 以下字段仅可通过树结构操作改变，不可直接通过update方式从接口提交，发现时过滤
const readOnlyFields = ['id', 'tender_id', 'ledger_id', 'ledger_pid', 'order', 'level', 'full_path', 'is_leaf'];
const calcFields = ['unit_price', 'sgfh_qty', 'sgfh_tp', 'sjcl_qty', 'sjcl_tp', 'qtcl_qty', 'qtcl_tp', 'deal_qty', 'deal_tp', 'dgn_qty1', 'dgn_qty2'];
const upFields = ['unit_price'];
const qtyFields = ['sgfh_qty', 'sjcl_qty', 'qtcl_qty', 'quantity', 'deal_qty', 'dgn_qty1', 'dgn_qty2'];
const tpFields = ['sgfh_tp', 'sjcl_tp', 'qtcl_tp', 'total_price', 'deal_tp'];
const rootId = -1;
const keyPre = 'tender_node_maxId:';

module.exports = app => {

    class Ledger extends app.BaseBillsService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx, {
                mid: 'tender_id',
                kid: 'ledger_id',
                pid: 'ledger_pid',
                order: 'order',
                level: 'level',
                isLeaf: 'is_leaf',
                fullPath: 'full_path',
                keyPre: 'ledger_bills_maxLid:',
                uuid: true,
            });
            this.tableName = 'ledger';
        }

        /**
         * 新增数据(供内部或其他service类调用, controller不可直接使用)
         * @param {Array|Object} data - 新增数据
         * @param {Number} tenderId - 标段id
         * @param {Object} transaction - 新增事务
         * @return {Promise<boolean>} - {Promise<是否正确新增成功>}
         */
        async innerAdd(data, tenderId, transaction) {
            const datas = data instanceof Array ? data : [data];
            if (tenderId <= 0) {
                throw '标段id错误';
            }
            if (datas.length <= 0) {
                throw '插入数据为空';
            }
            if (!transaction) {
                throw '内部错误';
            }
            // 整理数据
            const insertData = [];
            for (const tmp of datas) {
                tmp.ledger_id = tmp.template_id;
                tmp.ledger_pid = tmp.pid;
                tmp.tender_id = tenderId;
                delete tmp.template_id;
                delete tmp.pid;
                tmp.id = this.uuid.v4();
                insertData.push(tmp);
            }
            const operate = await transaction.insert(this.tableName, insertData);
            return operate.affectedRows === datas.length;
        }
        /**
         * 新增数据
         *
         * @param {Object} data - 新增的数据(可批量)
         * @param {Number} tenderId - 标段id
         * @return {Boolean} - 返回新增的结果
         */
        async add(data, tenderId) {
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                result = await this.innerAdd(data, tenderId, this.transaction);
                if (!result) {
                    throw '新增数据错误';
                }
                await this.transaction.commit();
            } catch (error) {
                await this.transaction.rollback();
                result = false;
            }

            return result;
        }

        /**
         * 根据节点Id获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} nodeId - 项目节/工程量清单节点id
         * @return {Object} - 返回查询到的节点数据
         */
        async getDataByNodeId(tenderId, nodeId) {
            if ((nodeId <= 0) || (tenderId <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_id', {
                value: nodeId,
                operate: '=',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.queryOne(sql, sqlParam);

            return data;
        }
        /**
         * 根据节点Id获取数据
         * @param {Number} tenderId - 标段Id
         * @param {Array} nodesIds - 节点Id
         * @return {Array}
         */
        async getDataByNodeIds(tenderId, nodesIds) {
            if (tenderId <= 0) {
                return [];
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_id', {
                value: nodesIds,
                operate: 'in',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return this._.sortBy(data, function (d) {
                return nodesIds.indexOf(d.ledger_id);
            });
        }
        /**
         * 根据主键id获取数据
         * @param {Array|Number} id - 主键id
         * @return {Promise<*>}
         */
        async getDataByIds(id) {
            if (!id) {
                return;
            }
            const ids = id instanceof Array ? id : [id];
            if (ids.length === 0) {
                return;
            }
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('id', {
                value: ids,
                operate: 'in',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 根据 父节点id 获取子节点
         * @param tenderId
         * @param nodeId
         * @return {Promise<*>}
         */
        async getChildrenByParentId(tenderId, nodeId) {
            if (tenderId <= 0 || !nodeId) {
                return undefined;
            }
            const nodeIds = nodeId instanceof Array ? nodeId : [nodeId];
            if (nodeIds.length === 0) {
                return [];
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: nodeIds,
                operate: 'in',
            });
            this.sqlBuilder.orderBy = [['order', 'ASC']];

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 获取项目工程量
         * @param tenderId
         * @returns {Promise<*>}
         */
        async getGatherGclBills(tenderId) {
            const sql = 'SELECT `b_code`, `name`, `unit`, `unit_price`, ' +
                        '    Sum(`quantity`) As `quantity`, Sum(`total_price`) As `total_price`, ' +
                        '    Sum(`deal_qty`) As `deal_qty`, Sum(`deal_tp`) As `deal_tp` ' +
                        '  From ?? ' +
                        '  WHERE `tender_id` = ? And `b_code` And `is_leaf` ' +
                        '  GROUP BY `b_code`, `name`, `unit`, `unit_price`';
            const sqlParam = [this.tableName, tenderId];
            return await this.db.query(sql, sqlParam);
        }

        async getTenderUsedUnits(tenderId) {
            const sql = 'SELECT unit ' +
                '  FROM ' + this.tableName +
                '  WHERE tender_id = ? and is_leaf = true' +
                '  GROUP By unit';
            const sqlParam = [tenderId];
            const units = await this.db.query(sql, sqlParam);
            return this._.map(units, 'unit');
        }

        /**
         * 统计子节点total_price
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number} order - order取值
         * @param {String} orderOperate - order比较操作符
         * @return {Promise<void>}
         */
        async addUpChildren(tenderId, pid, order, orderOperate) {
            this.initSqlBuilder();
            const sql = ['SELECT SUM(??) As value FROM ?? ', ' WHERE '];
            const sqlParam = ['total_price', this.tableName];
            sql.push(' ?? = ' + tenderId);
            sqlParam.push('tender_id');
            sql.push(' And ?? = ' + pid);
            sqlParam.push('ledger_pid');
            sql.push(' And ?? ' + orderOperate + ' ' + order);
            sqlParam.push('order');
            const result = await this.db.queryOne(sql.join(''), sqlParam);

            return result.value;
        }

        /**
         * 删除相关数据 用于继承
         * @param mid
         * @param deleteData
         * @returns {Promise<void>}
         * @private
         */
        async _deleteRelaData(mid, deleteData) {
            await this.ctx.service.pos.deletePosData(this.transaction, mid, this._.map(deleteData, 'id'));
        }

        _checkField(data, field) {
            const fields = field instanceof Array ? field : [field];
            for (const prop in data) {
                if (fields.indexOf(prop) >= 0) {
                    return true;
                }
            }
            return false;
        }

        /**
         * 复制粘贴整块
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中几点Id
         * @param {Array} block - 复制节点Id
         * @return {Object} - 提价后的数据(其中新增粘贴数据，只返回第一层)
         */
        async pasteBlock(tenderId, selectId, block) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(this.ctx.tender.id, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }
            const newParentPath = selectData.full_path.replace(selectData.ledger_id, '');

            const copyNodes = await this.getDataByNodeIds(tenderId, block);
            if (!copyNodes || copyNodes.length <= 0) {
                throw '复制数据错误';
            }
            let bSameParent = true;
            for (const node of copyNodes) {
                if (node.ledger_pid !== copyNodes[0].ledger_pid) {
                    bSameParent = false;
                    break;
                }
            }
            if (!bSameParent) {
                throw '复制数据错误：仅可操作同层节点';
            }
            const orgParentPath = copyNodes[0].full_path.replace(copyNodes[0].ledger_id, '');

            const newIds = [];
            this.transaction = await this.db.beginTransaction();
            try {
                // 选中节点的所有后兄弟节点，order+粘贴节点个数
                await this._updateChildrenOrder(tenderId, selectData.ledger_pid, selectData.order + 1);
                // 数据库创建新增节点数据
                for (let iNode = 0; iNode < copyNodes.length; iNode++) {
                    const node = copyNodes[iNode];
                    let datas = await this.getDataByFullPath(tenderId, node.full_path + '%');
                    datas = this._.sortBy(datas, 'level');

                    const maxId = await this._getMaxLid(this.ctx.tender.id);

                    const leafBillsId = [];
                    // 计算粘贴数据中需更新部分
                    for (let index = 0; index < datas.length; index++) {
                        const data = datas[index];
                        const newId = maxId + index + 1;
                        const idChange = {
                            org: data.id,
                        };
                        data.id = this.uuid.v4();
                        idChange.new = data.id;
                        data.tender_id = this.ctx.tender.id;
                        if (!data.is_leaf) {
                            for (const children of datas) {
                                children.full_path = children.full_path.replace('-' + data.ledger_id, '-' + newId);
                                if (children.ledger_pid === data.ledger_id) {
                                    children.ledger_pid = newId;
                                }
                            }
                        } else {
                            data.full_path = data.full_path.replace('-' + data.ledger_id, '-' + newId);
                        }
                        data.ledger_id = newId;
                        data.full_path = data.full_path.replace(orgParentPath, newParentPath);
                        if (data.ledger_pid === node.ledger_pid) {
                            data.ledger_pid = selectData.ledger_pid;
                            data.order = selectData.order + iNode + 1;
                        }
                        data.level = data.level + selectData.level - copyNodes[0].level;
                        if (data.is_leaf) {
                            leafBillsId.push(idChange);
                        }
                        newIds.push(data.id);
                    }
                    const newData = await this.transaction.insert(this.tableName, datas);
                    for (const id of leafBillsId) {
                        await this.ctx.service.pos.copyBillsPosData(id.org, id.new, this.transaction);
                    }
                    this._cacheMaxLid(tenderId, maxId + datas.length);
                }
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }


            // 查询应返回的结果
            const order = [];
            for (let i = 1; i <= copyNodes.length; i++) {
                order.push(selectData.order + i);
            }
            const createData = await this.getDataByIds(newIds);
            const updateData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + copyNodes.length);
            const posData = await this.ctx.service.pos.getPosData({ lid: newIds });
            return {
                ledger: { create: createData, update: updateData },
                pos: posData,
            };
        }

        /**
         *
         * @param tenderId
         * @param xmj
         * @param order
         * @param parentData
         * @return {Promise<*[]>}
         * @private
         */
        async _sortBatchInsertData(tenderId, xmj, order, parentData) {
            const result = [],
                newIds = [];
            let tp = 0;
            const maxId = await this._getMaxLid(tenderId);
            // 添加xmj数据
            const parent = {
                tender_id: tenderId,
                ledger_id: maxId + 1,
                ledger_pid: parentData.ledger_id,
                is_leaf: xmj.children.length === 0,
                order,
                level: parentData.level + 1,
                name: xmj.name,
            };
            parent.full_path = parentData.full_path + '-' + parent.ledger_id;
            // 添加gcl数据
            for (let i = 0, iLen = xmj.children.length; i < iLen; i++) {
                const gcl = xmj.children[i];
                const child = {
                    tender_id: tenderId,
                    ledger_id: maxId + 1 + i + 1,
                    ledger_pid: parent.ledger_id,
                    is_leaf: true,
                    order: i + 1,
                    level: parent.level + 1,
                    b_code: gcl.b_code,
                    name: gcl.name,
                    unit: gcl.unit,
                    unit_price: gcl.unit_price,
                    quantity: gcl.quantity,
                };
                child.full_path = parent.full_path + '-' + child.ledger_id;
                child.total_price = this.ctx.helper.mul(child.unit_price, child.quantity, this.ctx.tender.info.decimal.tp);
                tp = this.ctx.helper.add(tp, child.total_price);
                result.push(child);
                newIds.push(child.ledger_id);
            }
            parent.total_price = tp;
            result.push(parent);
            newIds.push(parent.ledger_id);
            return [result, tp, newIds];
        }

        /**
         * 批量插入子项
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中节点Id
         * @param {Object} data - 批量插入数据
         * @return {Promise<void>}
         */
        async batchInsertChild(tenderId, selectId, data) {
            const result = { ledger: {}, pos: null };
            if ((tenderId <= 0) || (selectId <= 0)) {
                return result;
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }

            this.transaction = await this.db.beginTransaction();
            const newIds = [];
            const lastChild = await this.getLastChildData(tenderId, selectId);
            try {
                // 更新父项isLeaf
                if (!lastChild) {
                    await this.transaction.update(this.tableName, {
                        id: selectData.id,
                        is_leaf: false,
                        unit_price: null,
                        quantity: null,
                        total_price: null,
                        deal_qty: null,
                        deal_tp: null,
                    });
                }
                const order = lastChild ? lastChild.order : 0;
                // 计算id
                const maxId = await this._getMaxLid(tenderId);

                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        id: this.uuid.v4(),
                        tender_id: tenderId,
                        ledger_id: maxId + i + 1,
                        ledger_pid: selectData.ledger_id,
                        is_leaf: true,
                        order: order + i + 1,
                        level: selectData.level + 1,
                        b_code: data[i].b_code,
                        name: data[i].name,
                        unit: data[i].unit,
                        unit_price: data[i].price,
                    };
                    qd.full_path = selectData.full_path + '-' + qd.ledger_id;
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    newIds.push(qd.id);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calcNode(qd, this.transaction);
                    }
                }
                this._cacheMaxLid(tenderId, maxId + data.length);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            result.ledger.create = await this.getDataByIds(newIds);
            if (!lastChild) {
                result.ledger.update = await this.getDataByIds([selectData.id]);
            }
            result.pos = await this.ctx.service.pos.getPosData({ lid: newIds });
            return result;
        }

        /**
         * 批量插入后项
         * @param {Number} tenderId - 标段Id
         * @param {Number} selectId - 选中节点Id
         * @param {Object} data - 批量插入数据
         * @return {Promise<void>}
         */
        async batchInsertNext(tenderId, selectId, data) {
            const result = { ledger: {}, pos: null };
            if ((tenderId <= 0) || (selectId <= 0)) {
                return result;
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '位置数据错误';
            }
            const parentData = await this.getDataByNodeId(tenderId, selectData.ledger_pid);
            if (!parentData) {
                throw '位置数据错误';
            }

            this.transaction = await this.db.beginTransaction();
            const newIds = [];
            try {
                // 选中节点的所有后兄弟节点，order+粘贴节点个数
                await this._updateChildrenOrder(tenderId, selectData.ledger_pid, selectData.order + 1, data.length);
                // 计算id和order
                const maxId = await this._getMaxLid(tenderId);
                const order = selectData.order;
                // 数据库创建新增节点数据
                for (let i = 0, iLen = data.length; i < iLen; i++) {
                    // 合并新增数据
                    const qd = {
                        id: this.uuid.v4(),
                        tender_id: tenderId,
                        ledger_id: maxId + i + 1,
                        ledger_pid: parentData.ledger_id,
                        is_leaf: true,
                        order: order + i + 1,
                        level: parentData.level + 1,
                        b_code: data[i].b_code,
                        name: data[i].name,
                        unit: data[i].unit,
                        unit_price: data[i].price,
                    };
                    qd.full_path = parentData.full_path + '-' + qd.ledger_id;
                    const insertResult = await this.transaction.insert(this.tableName, qd);
                    newIds.push(qd.id);
                    if (data[i].pos.length > 0) {
                        await this.ctx.service.pos.insertLedgerPosData(this.transaction, tenderId, qd, data[i].pos);
                        await this.calcNode(qd, this.transaction);
                    }
                }
                this._cacheMaxLid(tenderId, maxId + data.length);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            result.ledger.create = await this.getDataByIds(newIds);
            result.ledger.update = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + data.length);
            result.pos = await this.ctx.service.pos.getPosData({ lid: newIds });
            return result;
        }

        /**
         *
         * @param node
         * @param transaction
         * @returns {Promise<void>}
         * @private
         */
        async calcNode(node, transaction) {
            const info = this.ctx.tender.info;
            const precision = this.ctx.helper.findPrecision(info.precision, node.unit);

            const calcQtySql = 'SELECT SUM(`sgfh_qty`) As `sgfh_qty`, SUM(`sjcl_qty`) As `sjcl_qty`, SUM(`qtcl_qty`) As `qtcl_qty`, SUM(`quantity`) As `quantity` FROM ?? WHERE `lid` = ?';
            const data = await transaction.queryOne(calcQtySql, [this.ctx.service.pos.tableName, node.id]);
            data.id = node.id;
            data.sgfh_qty = this.round(data.sgfh_qty, precision.value);
            data.sjcl_qty = this.round(data.sjcl_qty, precision.value);
            data.qtcl_qty = this.round(data.qtcl_qty, precision.value);
            data.quantity = this.round(data.quantity, precision.value);
            data.sgfh_tp = this.ctx.helper.mul(data.sgfh_qty, node.unit_price, info.decimal.tp);
            data.sjcl_tp = this.ctx.helper.mul(data.sjcl_qty, node.unit_price, info.decimal.tp);
            data.qtcl_tp = this.ctx.helper.mul(data.qtcl_qty, node.unit_price, info.decimal.tp);
            data.total_price = this.ctx.helper.mul(data.quantity, node.unit_price, info.decimal.tp);
            const result = await transaction.update(this.tableName, data);
        }

        /**
         *
         * @param {Number} tid - 标段id
         * @param {Number} id - 需要计算的节点的id
         * @param {Object} transaction - 操作所属事务，没有则创建
         * @return {Promise<void>}
         */
        async calc(tid, id, transaction) {
            const node = await transaction.get(this.tableName, {id: id});
            if (!node) {
                throw '数据错误';
            }
            await this.calcNode(node, transaction);
        }

        async _importCacheTreeNodes(transaction, nodes) {
            const datas = [];
            for (const node of nodes) {
                datas.push({
                    id: node.id,
                    tender_id: this.ctx.tender.id,
                    ledger_id: node.ledger_id,
                    ledger_pid: node.ledger_pid,
                    level: node.level,
                    order: node.order,
                    is_leaf: !node.children || node.children.length === 0,
                    full_path: node.full_path,
                    code: node.code,
                    b_code: node.b_code,
                    name: node.name,
                    unit: node.unit,
                    sgfh_qty: node.sgfh_qty,
                    sgfh_tp: node.sgfh_tp,
                    quantity: node.quantity,
                    unit_price: node.unit_price,
                    total_price: node.total_price,
                    dgn_qty1: node.dgn_qty1,
                    dgn_qty2: node.dgn_qty2,
                    memo: node.memo,
                    drawing_code: node.drawing_code,
                });
            }
            await transaction.insert(this.tableName, datas);
            return datas;
        }

        /**
         * 导入Excel数据
         * @param excelData
         * @returns {Promise<void>}
         */
        async importExcel(templateId, excelData) {
            const AnalysisExcel = require('../lib/analysis_excel').AnalysisExcelTree;
            const analysisExcel = new AnalysisExcel(this.ctx);
            const tempData = await this.ctx.service.tenderNodeTemplate.getData(templateId, true);
            const cacheTree = analysisExcel.analysisData(excelData, tempData);
            const cacheKey = keyPre + this.ctx.tender.id;
            const orgMaxId = parseInt(await this.cache.get(cacheKey));
            const transaction = await this.db.beginTransaction();
            try {
                await transaction.delete(this.tableName, {tender_id: this.ctx.tender.id});
                await transaction.delete(this.ctx.service.pos.tableName, {tid: this.ctx.tender.id});
                const datas = [];
                for (const node of cacheTree.items) {
                    datas.push({
                        id: node.id,
                        tender_id: this.ctx.tender.id,
                        ledger_id: node.ledger_id,
                        ledger_pid: node.ledger_pid,
                        level: node.level,
                        order: node.order,
                        is_leaf: !node.children || node.children.length === 0,
                        full_path: node.full_path,
                        code: node.code,
                        b_code: node.b_code,
                        name: node.name,
                        unit: node.unit,
                        sgfh_qty: node.sgfh_qty,
                        sgfh_tp: node.sgfh_tp,
                        quantity: node.quantity,
                        unit_price: node.unit_price,
                        total_price: node.total_price,
                        dgn_qty1: node.dgn_qty1,
                        dgn_qty2: node.dgn_qty2,
                        memo: node.memo,
                        drawing_code: node.drawing_code,
                    });
                }
                await transaction.insert(this.tableName, datas);
                if (cacheTree.pos && cacheTree.pos.length > 0) {
                    await transaction.insert(this.ctx.service.pos.tableName, cacheTree.pos);
                }
                await transaction.commit();
                this.cache.set(cacheKey, cacheTree.items.length + 1, 'EX', this.ctx.app.config.cacheTime);
                return {bills: datas, pos: cacheTree.pos}
            } catch (err) {
                await transaction.rollback();
                if (orgMaxId) {
                    this.cache.set(cacheKey, cacheTree.keyNodeId, 'EX', this.ctx.app.config.cacheTime);
                }
                throw err;
            }
        }
    }

    return Ledger;
};