ConstructionCost/web/building_saas/main/js/views/importBills.js

'use strict';

/**
 *
 *
 * @author Zhong
 * @date 2018/8/2
 * @version
 */

/*
* 清单导入模块,前端导入excel,进行数据提取，用lz-string进行压缩上传处理
* */

const importBills = (function(){
    //单元格数据是否存在
    function _isDef(data){
        return typeof data !== 'undefined' && data !== null && data !== '';
    }
    
    //去除转义字符
    function _deESC(data) {
        return _isDef(data) ? data.toString().replace(/[\r,\n,\s,\t]/g, '') : data;
    }

    //列名对应中文字符
    const colText = {
        serialNo: ['序号'],
        code: ['编码', '项目编码'],
        name: ['名称', '项目名称'],
        itemCharacterText: ['特征', '项目特征'],
        unit: ['单位', '计量单位'],
        quantity: ['工程量', '项目工程量'],
        money: ['金额'],
        quantityDetail: ['工程量明细'],
        feeDetail: ['费用明细'],
        summation: ['合计', '本页小计'],
    };

    //导入位置对应清单固定标记
    const positionFlag = {
        fbfx: fixedFlag.SUB_ENGINERRING,
        jscsxm: fixedFlag.CONSTRUCTION_TECH,
        zzcsxm: fixedFlag.CONSTRUCTION_ORGANIZATION,
    };

    //上传类型
    const uploadType = {
        lj: 'lj',
        gld: 'gld',
    };

    //设置导入表内容(选择导入位置)
    //@param {Object}workBook
    function setImportSheetsInfo(sheets){
        let sheetNames = [];
        let indexMapping = {};
        for(let sheetName in sheets){
            indexMapping[sheets[sheetName]['index']] = sheetName;
        }
        let sheetsCount = Object.keys(sheets).length;
        for(let i = 0; i < sheetsCount; i++){
            sheetNames.push(indexMapping[i]);
        }
        let sheetArea = $('#uploadSheets');
        $('#uploadSheets').height('');
        sheetArea.empty();
        for(let sheetName of sheetNames){
            let sheetDiv = $(`<div style="margin-left: 5px;margin-top: 5px;" class="input-group form-check"><label class="form-check-label" style="width:270px; overflow: hidden; white-space: nowrap; text-overflow: ellipsis">
                           <input class="form-check-input" type="checkbox">${sheetName}</label></div>`);
            sheetDiv.find('input[type="checkbox"]').click(function () {
                if($('#uploadAlert').is(':visible')){
                    $('#uploadAlert').hide();
                }
            });
            let sel = $(`<select style="margin-left: 5px; border-radius: .20rem;"><option value="fbfx">分部分项工程</option><option value="zzcsxm">施工组织措施项目</option><option value="jscsxm">施工技术措施项目</option></select>`);
            if(sheetName.includes('分部分项工程项目清单计价表')){
                sheetDiv.find('input[type="checkbox"]').prop('checked', true);
                sel.find('option:eq(0)').prop('selected', true);
            }
            else if(sheetName.includes('施工组织措施项目清单计价表')){
                sheetDiv.find('input[type="checkbox"]').prop('checked', true);
                sel.find('option:eq(1)').prop('selected', true);
            }
            else if(sheetName.includes('施工技术措施项目清单计价表')){
                sheetDiv.find('input[type="checkbox"]').prop('checked', true);
                sel.find('option:eq(2)').prop('selected', true);
            }
            sheetDiv.append(sel);
            sheetArea.append(sheetDiv);

        }
        if(sheetNames.length > 0){
            sheetArea.show();
        }
        if($('#uploadSheets').height() > 250){
            sheetArea.css('overflow', 'auto');
            sheetArea.height(250);
        }
        else {
            sheetArea.css('overflow', 'hidden');
        }
    }

    //获得选择导入的表信息(表索引及导入位置)
    //@return {Object}
    function getImportSheetsInfo(){
        let rst = [];
        let sheetArea = $('#uploadSheets');
        let checkedInputs = sheetArea.find('input:checked');
        for(let checked of checkedInputs){
            rst.push({index: $(checked).parent().parent().index(), position: $(checked).parent().next().select().val()})
        }
        return rst;
    }

    function getSheetByIndex(sheets, index){
        for(let sheetName in sheets){
            let sheet = sheets[sheetName];
            if(sheet.index === index){
                return sheet;
            }
        }
        return null;
    }

    //提取excel表头列名与列下标映射
    //@
    function getColMapping(dataTable){
        //获取表头
        function getHeadRow(dataTable){
            for(let rowIdx in dataTable ){
                for(let colIdx in dataTable[rowIdx]){
                    let cellData = dataTable[rowIdx][colIdx]['value'];
                    if(cellData && _deESC(cellData) === colText.serialNo[0]){
                        return dataTable[rowIdx];
                    }
                }
            }
            return {};
        }
        //获取需要的表头列与列号对应关系
        let colMapping = {};
        let headRow = getHeadRow(dataTable);
        for(let colIdx in headRow){
            let cellData = headRow[colIdx]['value'];
            if(!_isDef(cellData)){
                continue;
            }
            //序号
            if(colMapping.serialNo === undefined && cellData === colText.serialNo[0]){
                colMapping.serialNo = colIdx;
            }
            //编码
            else if(colMapping.code === undefined && (cellData === colText.code[0] || cellData === colText.code[1])){
                colMapping.code = colIdx;
            }
            //名称
            else if(colMapping.name === undefined && (cellData === colText.name[0] || cellData === colText.name[1])){
                colMapping.name = colIdx;
            }
            //项目特征
            else if(colMapping.itemCharacterText === undefined && (cellData === colText.itemCharacterText[0] || cellData === colText.itemCharacterText[1])){
                colMapping.itemCharacterText = colIdx;
            }
            //单位
            else if(colMapping.unit === undefined && (cellData === colText.unit[0] || cellData === colText.unit[1])){
                colMapping.unit = colIdx;
            }
            //工程量
            else if(colMapping.quantity === undefined && (cellData === colText.quantity[0] || cellData === colText.quantity[1])){
                colMapping.quantity = colIdx;
            }
            //金额
            else if(colMapping.money === undefined && cellData.includes(colText.money[0])){
                colMapping.money = colIdx;
            }
            //工程量明细
            else if(colMapping.quantityDetail === undefined && cellData === colText.quantityDetail[0]){
                colMapping.quantityDetail = colIdx;
            }
            //费用明细
            else if(colMapping.feeDetail === undefined && cellData === colText.feeDetail[0]){
                colMapping.feeDetail = colIdx;
            }
        }
        return colMapping;
    }

    //是否是有效的表头列格式,只要含有各表需要的列就行，不严格控制多少列
    function isValidSheet(colMapping, fileType){
        function hasField(field, all){
            for(let i of all){
                if(field === i){
                    return true;
                }
            }
            return false;
        }
        let needFields;
        if(fileType === uploadType.lj){
            //09表：序号、项目编码、项目名称、项目特征、计量单位、工程量、金额
            needFields = ['serialNo', 'code', 'name', 'money'];
        }
        else {
            //广联达表：序号、项目编码、项目名称、项目特征、计量单位、工程量、工程量明细、费用明细
            needFields = ['serialNo', 'code', 'name', 'itemCharacterText', 'unit', 'quantity', 'quantityDetail', 'feeDetail'];
        }
        let hasFieldCount = 0;
        for(let attr in colMapping){
            if(hasField(attr, needFields)){
                hasFieldCount++;
            }
        }
        return hasFieldCount === needFields.length;
    }

    //获取要无效和有效导入表
    //@param {Array}importSheetInfo 勾选要导入的表
    function getImportSheets(sheets, sheetsInfo, fileType){
        let rst = {invalidSheets: [], validSheets: {fbfx: [], jscsxm: [], zzcsxm: []}};

        for(let sheetInfo of sheetsInfo){
            let sheet = getSheetByIndex(sheets, sheetInfo.index);
            if(!sheet){
                continue;
            }
            //没有数据
            if(!sheet.data.dataTable){
                rst.invalidSheets.push(sheet.name);
                continue;
            }
            //获取表的列设置确定导入的格式是否合法（09、广联达）
            let colMapping = getColMapping(sheet.data.dataTable);
            if(!isValidSheet(colMapping, fileType)){
                rst.invalidSheets.push(sheet.name);
                continue;
            }
            //合法的表
            sheet.colMapping = colMapping;
            //将合法的表按导入位置分类当做一个表来处理
            if(rst.validSheets[sheetInfo.position] !== undefined){
                rst.validSheets[sheetInfo.position].push(sheet)
            }
        }
        return rst;
    }

    //行存在数据
    function rowExistData(rowData){
        for(let colIdx in rowData){
            let colData = rowData[colIdx]['value'];
            if(_isDef(colData)){
                return true;
            }
        }
        return false;
    }

    //提取excel表数据中的有效数据(去表头表尾，提取其中的excel数据)(根据fixedBill获取栏头占行数)
    function getValidImportData(colMapping, sheetData){
        let withingD = false;
        let validData = [];
        function isHead(rData){
            return rData[colMapping.serialNo] && _deESC(rData[colMapping.serialNo]['value']) === colText.serialNo[0];
        }
        function isTail(rowData){
            for(let colIdx in rowData){
                let colData = rowData[colIdx]['value'];
                if(colData){
                    let trimColData= _deESC(colData);
                    if(trimColData === colText.summation[0] || trimColData === colText.summation[1]){
                        return true;
                    }
                }
            }
            return false;
        }
        for(let rowIdx in sheetData){
            let rowData = sheetData[rowIdx];
            if(isHead(rowData)){
                withingD = true;
                continue;
            }
            else if(isTail(rowData)){
                withingD = false;
            }
            if(withingD && rowExistData(rowData)){
                validData.push(rowData);
            }
        }
        return validData;
    }

    //excel数据转换成清单数据
    function parseToBillData(validData, colMapping, flag, projectID){
        let rst = [];
        let billIdx = {};
        let preRootID = -1,
            preLeafID = -1,
            preID = -1;
        //父节点：1.无序号 2有编码
        function isRoot(rData){
            //序号和编码去除转义字符(有的表格单元格看起来是没数据，实际含有\r,\n等数据)
            let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
            let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
            return !_isDef(serialNo) && _isDef(code);
        }
        //子节点：有序号
        function isLeaf(rData){
            let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
            return _isDef(serialNo);
        }
        //续数据：1. 前数据有效 2.无序号 3.无编码 4.有名称或特征
        function isExtend(preData, rData){
            let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
            let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
            let name = rData[colMapping.name] ? _deESC(rData[colMapping.name]['value']) : '';
            let itemCharacterText = rData[colMapping.itemCharacterText] ? _deESC(rData[colMapping.itemCharacterText]['value']) : '';
            return _isDef(preData) && (isRoot(preData) || isLeaf(preData)) && !_isDef(serialNo) && !_isDef(code) && (_isDef(name) || _isDef(itemCharacterText));
        }
        function getBillType(rData, flag){
            if(flag === fixedFlag.CONSTRUCTION_TECH || flag === fixedFlag.CONSTRUCTION_ORGANIZATION){
                return billType.BILL;
            }
            else if(flag === fixedFlag.SUB_ENGINERRING){
                return isLeaf(rData) ? billType.FX : billType.FB;
            }
            return null;
        }
        //excel数据与标准库数据匹配，根据清单前九位编码匹配，匹配成功则获取标准清单对应的工程专业、特征及内容
        /*function matchStdBill(excelBill, stdData){
            let isMatch = false;
            let regExp = /^\d{12}$/g;
            if(regExp.test(excelBill.code)){
                let nineCode = excelBill.code.substr(0, 9);
                for(let stdBill of stdData.stdBills){
                    //set programID
                    if(nineCode == stdBill.code){
                        isMatch = true;
                        excelBill.programID = stdBill.engineering ? stdBill.engineering : null;
                        excelBill.billsLibId = stdBill.billsLibId ? stdBill.billsLibId : null;
                        //set jobContent and itemCharacter
                        let tempJob = [], tempCharacter = [];
                        for(let billJob of stdBill.jobs){
                            for(let stdJob of stdData.stdJobs) {
                                if (billJob.id == stdJob.id) {
                                    tempJob.push({isChecked: false, serialNo: billJob.serialNo, content: stdJob.content});
                                }
                            }
                        }
                        for(let billCharacter of stdBill.items){
                            for(let stdCharacter of stdData.stdCharacters){
                                if(billCharacter.id == stdCharacter.id){
                                    let eigenvalue = [];
                                    for(let eValue of stdCharacter.itemValue){
                                        eigenvalue.push({isSelected: false, value: eValue.value});
                                    }
                                    tempCharacter.push({isChecked: false, serialNo: billCharacter.serialNo, character: stdCharacter.content, eigenvalue: eigenvalue});
                                }
                            }
                        }
                        excelBill.jobContent = tempJob;
                        excelBill.itemCharacter = tempCharacter;
                    }
                }
            }
            if(!isMatch && excelBill.type === billType.FX){//分项不为空，同时在标准清单中不匹配，则识别为补项
                excelBill.type = billType.BX;
            }
        }*/
        for(let r = 0; r < validData.length; r++){
            let preData = validData[r-1],
                rData = validData[r];
            if(flag == fixedFlag.CONSTRUCTION_TECH && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工技术措施项目'
                || flag == fixedFlag.CONSTRUCTION_ORGANIZATION && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工组织措施项目'){
                continue;
            }
            //过滤无效数据
            if(!isRoot(rData) && !isLeaf(rData) && !isExtend(preData, rData)){
                continue;
            }
            if(isExtend(preData, rData)){
                let preBill = billIdx[preID];
                if(preBill){
                    //合并续数据
                    preBill.code += rData[colMapping.code] && rData[colMapping.code]['value'] ? rData[colMapping.code]['value'] : '';
                    preBill.name += rData[colMapping.name] && rData[colMapping.name]['value'] ? rData[colMapping.name]['value'] : '';
                    preBill.itemCharacterText += rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] ? rData[colMapping.itemCharacterText]['value'] : '';
                    preBill.unit += rData[colMapping.unit] && rData[colMapping.unit]['value'] ? rData[colMapping.unit]['value'] : '';
                    preBill.quantity += rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? rData[colMapping.quantity]['value'] : '';
                }
            }
            else {
                let newID = uuid.v1();
                let pID = -1;
                let preBill = null;
                let nodeType = 'root';//后端以此标记来设置ParentID
                if(isRoot(rData)){

                    //pID = 'fixedBillID';
                    preBill = billIdx[preRootID];
                }
                else if(isLeaf(rData)){
                    nodeType = 'leaf';
                    //pID = preRootID !== -1 ? preRootID : fixedBill.ID;
                    preBill = billIdx[preLeafID];
                }
                //set bill data
                billIdx[newID] = {
                    nodeType: nodeType,
                    ID: newID, ParentID: pID, NextSiblingID: -1,
                    code: rData[colMapping.code] && rData[colMapping.code]['value'] ? _deESC(rData[colMapping.code]['value']) : '',
                    name: rData[colMapping.name] && rData[colMapping.name]['value'] ? _deESC(rData[colMapping.name]['value']) : '',
                    itemCharacterText: rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] ? rData[colMapping.itemCharacterText]['value'] : '',
                    itemCharacter: [],
                    jobContentText: '',
                    jobContent: [],
                    programID: null,
                    unit: rData[colMapping.unit] && rData[colMapping.unit]['value'] ? rData[colMapping.unit]['value'] : '',
                    quantity: rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? rData[colMapping.quantity]['value'] : '',
                    //安全文明
                    flags: flag === fixedFlag.CONSTRUCTION_ORGANIZATION && (rData[colMapping.name] && (rData[colMapping.name]['value'] === '安全文明施工专项费用' || rData[colMapping.name]['value'] === '安全文明施工费')) ?
                        [{fieldName: 'fixed', flag: fixedFlag.SAFETY_CONSTRUCTION}] : [],
                    fees: [],
                    projectID: projectID,
                    type: getBillType(rData, flag)};
                //match stdBill and reset programID、jobContent、itemCharacter
                //matchStdBill(billIdx[newID], stdData);
                //update preBill NextSibling
                if(preBill){
                    preBill.NextSiblingID = newID;
                }
                //set new preID
                preID = newID;
                preRootID = isRoot(rData) ? newID : preRootID;
                preLeafID = isLeaf(rData) ? newID : preLeafID;
            }
        }
        for(let i in billIdx){
            rst.push(billIdx[i]);
        }
        return rst;
    }

    function getImportData(validSheets, projectID){
        let rst = {fbfx: [], jscsxm: [], zzcsxm: []};
        let validSheetsDatas = [];
        for(let uploadPosition in validSheets){
            let validExcelData = [];
            for(let uSheet of validSheets[uploadPosition]){
                validExcelData = validExcelData.concat(getValidImportData(uSheet.colMapping, uSheet.data.dataTable));
            }
            if(validSheets[uploadPosition].length > 0){
                validSheetsDatas.push({position: uploadPosition, colMapping: validSheets[uploadPosition][0].colMapping, validExcelData: validExcelData});
            }
        }
        console.log(validSheetsDatas);
        for(let validSheetData of validSheetsDatas){
            if(validSheetData.validExcelData.length > 0){
                rst[validSheetData.position] = parseToBillData(validSheetData.validExcelData, validSheetData.colMapping, positionFlag[validSheetData.position], projectID);
            }
        }
        console.log(rst);
        return rst;
    }

    function excelHasValidBills(importBillsData){
        for(let i in importBillsData){
            if(importBillsData[i].length > 0){
                return true;
            }
        }
        return false;
    }



    return {setImportSheetsInfo, getImportSheetsInfo, getImportSheets, getImportData, excelHasValidBills}
})();