Calculation/app/lib/analysis_excel.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */
const _ = require('lodash');
const aeUtils = {
    toNumber: function (value) {
        let num = _.toNumber(value);
        return _.isNaN(num) ? null : num;
    },
    checkColHeader: function (row, colHeaderMatch) {
        const colsDef = {};
        for (const iCol in row) {
            const text = row[iCol];
            for (const head in colHeaderMatch) {
                const match = colHeaderMatch[head];
                if (match.indexOf(text) >= 0) {
                    colsDef[head] = iCol;
                }
            }
        }
        return colsDef;
    }
};

class ImportBaseTree {
    /**
     * 构造函数
     * @param {Array} tempData - 清单模板数据
     */
    constructor (tempData, ctx) {
        this.ctx = ctx;
        // 常量
        this.splitChar = '-';
        // 索引
        // 以code为索引
        this.codeNodes = {};
        this.items = [];
        this.roots = [];
        this.pos = [];
        this.tempData = [];

        // 缓存
        this.finalNode = null;
        this.finalPrecision = null;
        this.finalXmjNode = null;
        this.keyNodeId = 1;

        this._loadTemplateTree(tempData);
    }

    /**
     * 加载 清单模板
     * @param {Array} data - 模板数据
     * @private
     */
    _loadTemplateTree(data) {
        let loadCodeNodes = true;
        for (const node of data) {
            node.ledger_id = node.template_id;
            node.ledger_pid = node.pid;
            node.id = this.ctx.app.uuid.v4();
            delete node.pid;
            if (node.code && loadCodeNodes) {
                this.codeNodes[node.code] = node;
            }
            if (node.code === '3') {
                loadCodeNodes = false;
            }
            this.items.push(node);
            if (node.ledger_pid === -1) {
                this.roots.push(node);
            }
            if (node.ledger_id >= this.keyNodeId) {
                this.keyNodeId = node.ledger_id + 1;
            }
            this.tempData.push(node);
        }
        for (const node of this.items) {
            node.tender_id = this.ctx.tender.id;
            node.children = this.items.filter(function (i) {
                return i.ledger_pid === node.ledger_id;
            });
        }
    }

    /**
     * 根据 编号、名称 查找模板节点
     * @param {Object} node - 要查找的节点
     * @returns {*}
     */
    findTempData(node) {
        return this.tempData.find(function (td) {
            return td.code === node.code && td.name === node.name;
        });
    }

    /**
     * 根据 编号 查找 父项项目节
     * @param {String} code - 子项编号
     * @returns {*}
     */
    findXmjParent(code) {
        const codePath = code.split(this.splitChar);
        if (codePath.length > 1) {
            codePath.splice(codePath.length - 1, 1);
            return this.codeNodes[codePath.join(this.splitChar)];
        }
    }

    getPosterity(node) {
        let posterity = [].concat(node.children);
        for (const c of node.children) {
            posterity = posterity.concat(this.getPosterity(c));
        }
        return posterity;
    }

    findGclParent(code, xmj) {
        let parent;
        const codePath = code.split(this.splitChar);
        if (codePath.length > 1) {
            codePath.splice(codePath.length - 1, 1);
            const parentCode = codePath.join(this.splitChar);
            const posterity = this.getPosterity(xmj);
            parent = posterity.find(function (x) {
                return x.b_code === parentCode;
            })
        }
        return parent ? parent : xmj;
    } 

    /**
     * 添加 树节点 并完善该节点的树结构
     * @param {Object} node - 添加节点
     * @param {Object} parent - 父项
     * @returns {*}
     */
    addNodeWithParent(node, parent) {
        node.id = this.ctx.app.uuid.v4();
        node.ledger_id = this.keyNodeId;
        this.keyNodeId += 1;
        node.ledger_pid = parent ? parent.ledger_id : -1;
        node.level = parent ? parent.level + 1 : 1;
        node.order = parent ? parent.children.length + 1 : this.roots.length + 1;
        node.full_path = parent ? parent.full_path + '-' + node.ledger_id : '' + node.ledger_id;
        if (parent) {
            parent.children.push(node);
        } else {
            this.roots.push(node);
        }
        this.items.push(node);
        this.codeNodes[node.code] = node;
        this.defineCacheData(node);
        return node;
    }

    /**
     * 定义缓存节点(添加工程量清单、部位明细需使用缓存定位)
     * @param {Object} node - 当前添加的节点
     */
    defineCacheData(node) {
        this.finalNode = node;
        this.finalPrecision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
        if (node.code) {
            this.finalXmjNode = node;
        }
    }

    /**
     * 添加 项目节
     * @param {Object} node - 项目节
     * @returns {*}
     */
    addXmjNode(node) {
        node.id = this.ctx.app.uuid.v4();
        node.tender_id = this.ctx.tender.id;
        node.children = [];
        if (node.code.split(this.splitChar).length > 1) {
            const temp = this.findTempData(node);
            if (temp) {
                this.defineCacheData(temp);
                return temp;
            } else {
                const parent = this.findXmjParent(node.code);
                return this.addNodeWithParent(node, parent);
            }
        } else {
            const n = this.codeNodes[node.code];
            if (!n) {
                return this.addNodeWithParent(node, null);
            } else {
                this.defineCacheData(n);
                return n;
            }
        }
    }

    /**
     * 添加 工程量清单
     * @param {Object} node - 工程量清单
     */
    addGclNode(node) {
        node.id = this.ctx.app.uuid.v4();
        node.tender_id = this.ctx.tender.id;
        node.pos = [];
        node.children = [];
        if (this.finalXmjNode) {
            const parent = node.b_code ? this.findGclParent(node.b_code, this.finalXmjNode) : this.finalXmjNode;
            return this.addNodeWithParent(node, parent);
        }
    }

    /**
     * 添加 部位明细
     * @param {object} pos - 部位明细
     * @returns {*}
     */
    addPos (pos){
        if (this.finalNode && this.finalNode.pos) {
            pos.id = this.ctx.app.uuid.v4();
            pos.lid = this.finalNode.id;
            pos.tid = this.ctx.tender.id;
            pos.add_stage = 0;
            pos.add_times = 0;
            pos.in_time = new Date();
            pos.porder = this.finalNode.pos.length + 1;
            pos.add_user = this.ctx.session.sessionUser.accountId;
            this.finalNode.pos.push(pos);
            this.pos.push(pos);
            pos.quantity = this.ctx.helper.round(pos.quantity, this.finalPrecision.value);
            return pos;
        }
    }

    /**
     * 第一部分的子节点，顺序重排
     */
    resortFirstPartChildren () {
        const splitChar = this.splitChar;
        const firstPart = this.roots[0];
        firstPart.children.sort(function (a, b) {
            if (a.code === '') {
                return 1;
            } else if (b.code === '') {
                return -1;
            }
            const codeA = a.code.split(splitChar);
            const numA = _.toNumber(codeA[codeA.length -1]);
            const codeB = b.code.split(splitChar);
            const numB = _.toNumber(codeB[codeB.length -1]);
            return numA - numB;
        });
        for (const [i, c] of firstPart.children.entries()) {
            c.order = i + 1;
        }
    }

    calculateLeafWithPos () {
        for (const node of this.items) {
            if (node.children && node.children.length > 0) { continue; }
            if (!node.pos || node.pos.length === 0) { continue; }
            node.quantity = this.ctx.helper.sum(_.map(node.pos, 'quantity'));
            if (node.quantity && node.unit_price) {
                node.total_price = this.ctx.helper.mul(node.quantity, node.unit_price, this.ctx.tender.info.decimal.tp);
            } else {
                node.total_price = null;
            }
        }
    }
}

class AnalysisExcelTree {
    /**
     * 构造函数
     */
    constructor(ctx) {
        this.ctx = ctx;
        this.colsDef = null;
        this.colHeaderMatch = {
            code: ['项目节编号', '预算项目节'],
            b_code: ['清单子目号', '清单编号', '子目号'],
            pos: ['部位明细'],
            name: ['名称'],
            unit: ['单位'],
            quantity: ['清单数量'],
            dgn_qty1: ['设计数量1'],
            dgn_qty2: ['设计数量2'],
            unit_price: ['单价'],
            drawing_code: ['图号'],
            memo: ['备注'],
        };
    }

    /**
     * 读取项目节节点
     * @param {Array} row - excel行数据
     * @returns {*}
     * @private
     */
    _loadXmjNode(row) {
        const node = {};
        node.code = this.ctx.helper.replaceReturn(row[this.colsDef.code]);
        node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
        node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
        const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
        node.quantity = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.quantity]), precision.value);
        node.dgn_qty1 = aeUtils.toNumber(row[this.colsDef.dgn_qty1]);
        node.dgn_qty2 = aeUtils.toNumber(row[this.colsDef.dgn_qty2]);
        node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
        node.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
        node.memo = this.ctx.helper.replaceReturn(row[this.colsDef.memo]);
        if (node.quantity && node.unit_price) {
            node.total_price = this.ctx.helper.mul(node.quantity, node.unit_price, this.ctx.tender.info.decimal.tp);
        } else {
            node.total_price = null;
        }
        return this.cacheTree.addXmjNode(node);
    }
    /**
     * 读取工程量清单数据
     * @param {Array} row - excel行数据
     * @returns {*}
     * @private
     */
    _loadGclNode(row) {
        const node = {};
        node.b_code = this.ctx.helper.replaceReturn(row[this.colsDef.b_code]);
        node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
        node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
        const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
        node.quantity = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.quantity]), precision.value);
        node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
        node.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
        node.memo = this.ctx.helper.replaceReturn(row[this.colsDef.memo]);
        if (node.quantity && node.unit_price) {
            node.total_price = this.ctx.helper.mul(node.quantity, node.unit_price, this.ctx.tender.info.decimal.tp);
        } else {
            node.total_price = null;
        }
        return this.cacheTree.addGclNode(node);
    }
    /**
     * 读取部位明细数据
     * @param {Array} row - excel行数据
     * @returns {*}
     * @private
     */
    _loadPos(row) {
        const pos = {};
        pos.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
        pos.quantity = aeUtils.toNumber(row[this.colsDef.quantity]);
        pos.sgfh_qty = pos.quantity;
        pos.drawing_code = this.ctx.helper.replaceReturn(row[this.colsDef.drawing_code]);
        return this.cacheTree.addPos(pos);
    }

    /**
     * 读取数据行
     * @param {Array} row - excel数据行
     * @param {Number} index - 行索引号
     */
    loadRowData(row, index) {
        let result;
        // 含code识别为项目节，含posCode识别为部位明细，其他识别为工程量清单
        if (row[this.colsDef.code]) {
            // 第三部分（编号为'3'）及之后的数据不读取
            if (row[this.colsDef.code] === '3') {
                this.loadEnd = true;
                return;
            }
            result = this._loadXmjNode(row)
        } else if (row[this.colsDef.pos]) {
            result = this._loadPos(row);
        } else {
            result = this._loadGclNode(row);
        }
        // 读取失败则写入错误数据 todo 返回前端告知用户?
        if (!result) {
            this.errorData.push({
                serialNo: index,
                data: row,
            });
        }
    }

    /**
     * 读取表头并检查
     * @param {Number} row - Excel数据行
     */
    checkColHeader(row) {
        const colsDef = aeUtils.checkColHeader(row, this.colHeaderMatch);
        if (colsDef.code && colsDef.b_code && colsDef.pos) {
            this.colsDef = colsDef;
        }
    }

    /**
     * 将excel清单 平面数据 解析为 树结构数据
     * @param {object} sheet - excel清单数据
     * @param {Array} tempData - 新建项目使用的清单模板
     * @returns {ImportBaseTree}
     */
    analysisData(sheet, tempData) {
        this.colsDef = null;
        this.cacheTree = new ImportBaseTree(tempData, this.ctx);
        this.errorData = [];
        this.loadEnd = false;

        for (const iRow in sheet.rows) {
            const row = sheet.rows[iRow];
            if (this.colsDef && !this.loadEnd) {
                this.loadRowData(row, iRow);
            } else {
                this.checkColHeader(row);
            }
        }
        this.cacheTree.resortFirstPartChildren();
        this.cacheTree.calculateLeafWithPos();
        return this.cacheTree;
    }
}

class ImportGclBaseTree {
    /**
     * 构造函数
     * @param {Array} tempData - 清单模板数据
     */
    constructor (ctx, parent, maxId, defaultData) {
        this.ctx = ctx;
        this.parent = parent;
        this.defaultData = defaultData;
        // 常量
        this.splitChar = '-';
        // 索引
        // 以code为索引
        this.codeNodes = {};
        this.items = [];

        // 缓存
        this.keyNodeId = maxId ? maxId + 1 : 1;
        this.blankParent = null;
    }

    /**
     * 根据 编号 查找 父项项目节
     * @param {String} code - 子项编号
     * @returns {*}
     */
    findParent(code) {
        const codePath = code.split(this.splitChar);
        if (codePath.length > 1) {
            codePath.splice(codePath.length - 1, 1);
            return this.codeNodes[codePath.join(this.splitChar)];
        }
    }

    /**
     * 添加 树节点 并完善该节点的树结构
     * @param {Object} node - 添加节点
     * @param {Object} parent - 父项
     * @returns {*}
     */
    addNodeWithParent(node, parent) {
        parent = parent ? parent : this.parent;
        if (!parent.children) parent.children = [];

        node.id = this.ctx.app.uuid.v4();
        node.tender_id = this.ctx.tender.id;
        node.ledger_id = this.keyNodeId;
        this.keyNodeId += 1;
        node.ledger_pid = parent.ledger_id;
        node.level = parent.level + 1;
        node.order = parent.children.length + 1;
        node.full_path = parent.full_path + '-' + node.ledger_id;
        parent.children.push(node);
        node.children = [];
        if (this.defaultData) _.assignIn(node, this.defaultData);
        this.items.push(node);
        if (!_.isNil(node.b_code) && node.b_code !== '') {
            this.codeNodes[node.b_code] = node;
        } else {
            this.blankParent = node;
        }
        return node;
    }

    /**
     * 添加 项目节
     * @param {Object} node - 项目节
     * @returns {*}
     */
    addNode(node) {
        if (_.isNil(node.b_code) || node.b_code === '') {
            return this.addNodeWithParent(node, null);
        } else if (node.b_code.split(this.splitChar).length > 1) {
            const parent = this.findParent(node.b_code);
            return this.addNodeWithParent(node, parent ? parent : this.blankParent);
        } else {
            return this.addNodeWithParent(node, this.blankParent);
        }
    }
}

class AnalysisGclExcelTree {
    /**
     * 构造函数
     */
    constructor(ctx) {
        this.ctx = ctx;
        this.colsDef = null;
        this.colHeaderMatch = {
            b_code: ['编号', '清单编号', '子目号', '子目编号', '清单号'],
            name: ['名称', '清单名称', '子目名称'],
            unit: ['单位'],
            quantity: ['清单数量'], // 施工图复核数量
            unit_price: ['单价'],
        };
    }

    /**
     * 读取表头并检查
     * @param {Number} row - Excel数据行
     */
    checkColHeader(row) {
        const colsDef = aeUtils.checkColHeader(row, this.colHeaderMatch);
        if (colsDef.b_code) {
            this.colsDef = colsDef;
        }
    }

    loadRowData(row) {
        const node = {};
        node.b_code = this.ctx.helper.replaceReturn(row[this.colsDef.b_code]);
        node.name = this.ctx.helper.replaceReturn(row[this.colsDef.name]);
        if ((_.isNil(node.b_code) || node.b_code === '') && (_.isNil(node.name) || node.name === '')) return node;

        node.unit = this.ctx.helper.replaceReturn(row[this.colsDef.unit]);
        const precision = this.ctx.helper.findPrecision(this.ctx.tender.info.precision, node.unit);
        node.quantity = this.ctx.helper.round(aeUtils.toNumber(row[this.colsDef.quantity]), precision.value);
        node.unit_price = aeUtils.toNumber(row[this.colsDef.unit_price]);
        if (node.quantity && node.unit_price) {
            node.total_price = this.ctx.helper.mul(node.quantity, node.unit_price, this.ctx.tender.info.decimal.tp);
        } else {
            node.total_price = null;
        }
        return this.cacheTree.addNode(node);
    }

    /**
     * 将excel清单 平面数据 解析为 树结构数据
     * @param {object} sheet - excel清单数据
     * @param {Array} parentId - 导入至的节点id
     * @returns {ImportBaseTree}
     */
    analysisData(sheet, parent, maxId, defaultData) {
        try {
            this.colsDef = null;
            this.cacheTree = new ImportGclBaseTree(this.ctx, parent, maxId, defaultData);
            this.errorData = [];
            this.loadEnd = false;
            // 识别表头导入
            for (const iRow in sheet.rows) {
                const row = sheet.rows[iRow];
                if (this.colsDef && !this.loadEnd) {
                    const result = this.loadRowData(row);
                    // 读取失败则写入错误数据 todo 返回前端告知用户?
                    if (!result) {
                        this.errorData.push({
                            serialNo: iRow,
                            data: row,
                        });
                    }
                } else {
                    this.checkColHeader(row);
                }
            }
            // 固定列导入
            // this.colsDef = {
            //     b_code: 0,
            //     name: 1,
            //     unit: 2,
            //     quantity: 3,
            //     unit_price: 4
            // };
            // for (let iRow = 1, iLen = sheet.rows.length; iRow < iLen; iRow++) {
            //     const row = sheet.rows[iRow];
            //     const result = this.loadRowData(row);
            //     // 读取失败则写入错误数据 todo 返回前端告知用户?
            //     if (!result) {
            //         this.errorData.push({
            //             serialNo: iRow,
            //             data: row,
            //         });
            //     }
            // }
            return this.cacheTree;
        } catch(err) {
            this.ctx.helper.log(err);
        }
    }
}

module.exports = { AnalysisExcelTree, AnalysisGclExcelTree };