src/utils/ExportExcel.js

   1 /*
   2  * ============LICENSE_START=======================================================
   3  * org.onap.aai
   4  * ================================================================================
   5  * Copyright © 2017-2021 AT&T Intellectual Property. All rights reserved.
   6  * ================================================================================
   7  * Licensed under the Apache License, Version 2.0 (the "License");
   8  * you may not use this file except in compliance with the License.
   9  * You may obtain a copy of the License at
  10  *
  11  *       http://www.apache.org/licenses/LICENSE-2.0
  12  *
  13  * Unless required by applicable law or agreed to in writing, software
  14  * distributed under the License is distributed on an "AS IS" BASIS,
  15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  16  * See the License for the specific language governing permissions and
  17  * limitations under the License.
  18  * ============LICENSE_END=========================================================
  19  */
  20
  21 import {GlobalExtConstants} from './GlobalExtConstants.js';
  22 import * as Excel from 'exceljs/dist/exceljs.min.js';
  23
  24 let OXM = GlobalExtConstants.OXM;
  25 let EXCELCELLS = GlobalExtConstants.EXCELCELLS;
  26 let DOWNLOAD_ALL = GlobalExtConstants.DOWNLOAD_ALL;
  27 let INVLIST = GlobalExtConstants.INVLIST;
  28
  29 function camelToDash(str) {
  30         return (str.replace(/\W+/g, '-')
  31           .replace(/([a-z\d])([A-Z])/g, '$1-$2')).toLowerCase();
  32 }
  33
  34 function sortColumn(col1, col2) {
  35         if (col1.value < col2.value) {
  36                 return -1;
  37         } else if (col1.value > col2.value) {
  38                 return 1;
  39         } else {
  40                 return 0;
  41         }
  42 }
  43
  44 var  buildAttrList = function(inputParam, cols, sortBy) {
  45         // console.log('[getSearchParam] inputParam: ' + inputParam);
  46         let searchParam = inputParam;
  47
  48         if (['PSERVER', 'COMPLEX', 'CLOUDREGION',
  49           'NETWORKPROFILE', 'VIRTUALDATACENTER','VNFC'].indexOf(inputParam.toUpperCase()) === -1) {
  50           searchParam = inputParam.substring(0, inputParam.length - 1);
  51           // console.log('[getSearchParam] searchParam: ' + searchParam);
  52         }
  53         if('CHASSIES'.indexOf(inputParam.toUpperCase()) !== -1){
  54           searchParam = inputParam.substring(0, inputParam.length - 2) + 's';
  55         }else if(inputParam.substr(inputParam.length - 3) === 'ies'){
  56           searchParam = inputParam.substring(0, inputParam.length - 3) + 'y';
  57         }else if('COMPLEXES'.indexOf(inputParam.toUpperCase()) !== -1){
  58           searchParam = inputParam.substring(0, inputParam.length - 2);
  59         }
  60         if(inputParam.toUpperCase() === 'PINTERFACES'){
  61           searchParam = 'pInterface';
  62         }
  63         if (inputParam.toUpperCase() === 'LINESOFBUSINESS') {
  64           searchParam = 'lineOfBusiness';
  65           // console.log('[getSearchParam] searchParam: ' + searchParam);
  66         }
  67
  68         // read oxm xml file
  69         let oxmFile = JSON.parse(OXM);
  70         let javaTypes = oxmFile['xml-bindings']['java-types'][0]['java-type'];
  71         // console.log('[buildAttrList] javaTypes:');
  72         // console.log(javaTypes);
  73
  74         let foundIndex = -1;
  75         for (let i = 0; i < javaTypes.length && foundIndex === -1; i++) {
  76           if (javaTypes[i]['xml-root-element'][0]['$']['name'] === camelToDash(searchParam)) {
  77                 // console.log(javaTypes[i]);
  78                 foundIndex = i;
  79           }
  80         }
  81
  82         //Adding Quick fix will be get rid of it later
  83         console.log('searchParam>>>>>>>stage2:',inputParam);
  84         if(foundIndex === -1){
  85          for (let i = 0; i < javaTypes.length && foundIndex === -1; i++) {
  86            if (javaTypes[i]['xml-root-element'][0]['$']['name'] === camelToDash(inputParam)) {
  87                  foundIndex = i;
  88            }
  89            if(foundIndex === -1){
  90                  if(inputParam.substr(inputParam.length - 1) === 's'){
  91                    searchParam = inputParam.substring(0, inputParam.length - 1);
  92                    console.log('searchParam>>>>>>>stage3:',searchParam);
  93                  }
  94                  if (javaTypes[i]['xml-root-element'][0]['$']['name'] === camelToDash(searchParam)) {
  95                    foundIndex = i;
  96                  }
  97            }
  98          }
  99         }
 100
 101         // build the column list
 102         let columns = cols;
 103   let requiredColumn = [];
 104         if(foundIndex > -1) {
 105           //for (let j = 0; j < javaTypes[foundIndex]['java-attributes'][0]['xml-element'].length; j++) {
 106            // columns.push({value: javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['$']['name']});
 107           //}
 108           if (javaTypes[foundIndex]['java-attributes']) {
 109                 let elementLength = 0;
 110                 if (javaTypes[foundIndex]['java-attributes'][0]['xml-element']) {
 111                   elementLength = javaTypes[foundIndex]['java-attributes'][0]['xml-element'].length;
 112                 }
 113                 for (var j = 0; j < elementLength; j++) {
 114                   let isPrimitive = JSON.stringify(javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['$']['type']).indexOf('java.lang') > -1;
 115       let reqObj = JSON.stringify(javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['$']['required']);
 116       let description='';
 117       if(javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['xml-properties'] && javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['xml-properties'][0]['xml-property'][0]['$']['name']=='description'){
 118         description= javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['xml-properties'][0]['xml-property'][0]['$']['value'];
 119       }
 120       let node = {value: javaTypes[foundIndex]['java-attributes'][0]['xml-element'][j]['$']['name'],description: description};
 121       if(isPrimitive) { //add to the list
 122                           columns.push(node);
 123                   }
 124                   if(reqObj && reqObj.indexOf('true') > -1){
 125                        requiredColumn.push(node);
 126                   }
 127                 }
 128           }
 129         }
 130
 131         //sort the column list
 132         columns.sort(sortColumn);
 133           //Sort column with requiredColumn
 134           if(sortBy === 'required'){
 135             for(var c=0; c< columns.length; c++){
 136               if(requiredColumn.indexOf(columns[c]) === -1){
 137                 requiredColumn.push(columns[c]);
 138               }
 139             }
 140           }
 141         if(sortBy && (sortBy === 'required' || sortBy === 'mandatory')){
 142            columns = requiredColumn;
 143         }
 144         // console.log('[buildAttrList] columns:');
 145         // console.log(columns);
 146
 147         return columns;
 148 }
 149 var getDescriptionForNodes= function (){
 150     var invKeys = Object.keys(INVLIST.INVENTORYLIST);
 151     let oxmFile = JSON.parse(OXM);
 152     let javaTypes = oxmFile['xml-bindings']['java-types'][0]['java-type'];
 153     let nodeDescriptionObj={};
 154     let invList = invKeys.map(item => {
 155       for (let i = 0; i < javaTypes.length; i++) {
 156         if(javaTypes[i]['$']['name'].toLowerCase() === camelToDash(INVLIST.INVENTORYLIST[item].modelPath).toLowerCase()) {
 157           if(javaTypes[i]['xml-properties'] !== undefined && javaTypes[i]['xml-properties'][0]['xml-property'] && javaTypes[i]['xml-properties'][0]['xml-property'][0]['$']['name']==='description'){
 158             nodeDescriptionObj[INVLIST.INVENTORYLIST[item].modelPath]=javaTypes[i]['xml-properties'][0]['xml-property'][0]['$']['value'];
 159           }else{
 160             nodeDescriptionObj[INVLIST.INVENTORYLIST[item].modelPath]='';
 161           }
 162         }
 163       }
 164       return nodeDescriptionObj;
 165     });
 166     return invList;
 167 }
 168
 169 var generateExcels = function(nodeResults,dslQuery) {
 170     console.log('inside generateExcels>>>>>');
 171     //Creating New Workbook
 172     var workbook = new Excel.Workbook();
 173     var nodesType = [];
 174     var worksheet;
 175     var columnsJsonObj = {}
 176     var columnsList = []
 177     columnsJsonObj.columns = columnsList;
 178     var nodesLength = nodeResults.length;
 179     console.log('nodeResults.length.......',nodesLength);
 180     for(var n=0;n<nodesLength;n++){
 181       //Creating Sheet for that particular WorkBook
 182       let nodeTypeVal = nodeResults[n]["node-type"];
 183       let url = nodeResults[n]["url"];
 184       let columns = [];
 185       let buildColumns = [];
 186       if(nodesType.indexOf(nodeTypeVal) == -1){
 187          nodesType.push(nodeTypeVal);
 188          worksheet = workbook.addWorksheet(nodeTypeVal,{properties: {showGridLines: true}});
 189          buildColumns = buildAttrList(nodeTypeVal, [], 'required');
 190          if(dslQuery){
 191           let tempDslQuery= dslQuery+',';
 192           let nodeTypeProperties ='';
 193           let plainNodes ='';
 194           var nodePatternwithProp = generateRegexForDsl(nodeTypeVal);
 195           var nodeRegularExp = new RegExp(nodePatternwithProp, 'g');
 196           plainNodes = tempDslQuery.match(nodeRegularExp);
 197           console.log('plainNodes model Gallery null>>>>>*',plainNodes);
 198           if(plainNodes){
 199             let propertiesPattern ="\\{.*?\\}";
 200             var propRegularExp = new RegExp(propertiesPattern, 'g');
 201             nodeTypeProperties = plainNodes[0].match(propRegularExp);
 202             console.log('nodeTypeProperties model Gallery>>>>>*',nodeTypeProperties);
 203             nodeTypeProperties = nodeTypeProperties[0].slice(1,-1).replace(/\'/g,'').toLowerCase().split(',');
 204             for(var z=0;z<buildColumns.length;z++){
 205                 if(nodeTypeProperties.indexOf(buildColumns[z].value.toLowerCase()) !== -1){
 206                   columns.push(buildColumns[z]);
 207                 }
 208               }
 209               console.log('on condition table columns>>>>',columns);
 210             }else{
 211               columns=buildColumns;
 212             }
 213           }else{
 214             columns=buildColumns;
 215           }
 216          columns.push({value: 'uri'});
 217          columns.push({value: 'relationship-list'});
 218          var column = {
 219           [nodeTypeVal] : columns
 220         }
 221         columnsJsonObj.columns.push(column);
 222          if (!worksheet.columns) {
 223           worksheet.columns = Object.keys(columns).map((k) => ({ header: columns[k].value, key: columns[k].value, width:15}))
 224         }
 225       }else{
 226         worksheet = workbook.getWorksheet(nodeTypeVal);
 227         let col = columnsJsonObj.columns;
 228         for(var c in col){
 229           if(col[c][nodeTypeVal]){
 230             columns = col[c][nodeTypeVal];
 231           }
 232         }
 233       }
 234       var excelCellsHeader = [];
 235       var loop = 0;
 236       var noOfCol = Math.ceil(columns.length/26);
 237       while(loop<noOfCol){
 238         if(loop === 0){
 239           for(var j=0; j<EXCELCELLS.length; j++){
 240             excelCellsHeader.push(EXCELCELLS[j]);
 241           }
 242         }else{
 243           for(var j=0; j<EXCELCELLS.length; j++){
 244             excelCellsHeader.push(EXCELCELLS[loop-1]+EXCELCELLS[j]);
 245           }
 246         }
 247         loop++;
 248       }
 249       console.log('excelCellsHeader.......',excelCellsHeader);
 250       console.log('worksheet.columns>>>>',worksheet.columns);
 251       var row = [];
 252       var relativeRow = [];
 253       var rows = [];
 254       for(var x in columns){
 255         if(nodeResults[n].properties[columns[x].value] !== undefined){
 256           row.push(nodeResults[n].properties[columns[x].value]);
 257           relativeRow.push("");
 258         }else{
 259           row.push("");
 260           relativeRow.push("");
 261         }
 262       }
 263       row[row.length-2] = url;
 264       console.log('Push Row after>>>>>>>>>>>',row);
 265       var relationships = [];
 266       if(nodeResults[n]["related-to"]){
 267         let relative =nodeResults[n]["related-to"];
 268         var relativeLength = relative.length;
 269         console.log('relativeLength>>>>>>>>>>>',relativeLength);
 270         if(relativeLength>0){
 271         for(var r = 0; r<relative.length; r++){
 272           var relationship = '';
 273           var relationshipLabel = '';
 274           var nodeType = '';
 275           var localUrl = '';
 276           for(var k in relative[r]){
 277             let relation = k.toString();
 278             if(relation === 'relationship-label'){
 279               relationshipLabel = relative[r][k].toString().slice(33);
 280             }
 281             if(relation === 'node-type'){
 282               nodeType = relative[r][k].toString();
 283             }
 284             if(relation === 'url'){
 285               localUrl = relative[r][k].toString();
 286             }
 287           }
 288           relationship = 'Relationship ' + relationshipLabel + ' ' + nodeType + ' - ' + localUrl;
 289           if(r===0){
 290             row[row.length-1] = relationship;
 291             rows.push(row);
 292           }else{
 293             let tempRow = [];
 294             tempRow = [...relativeRow]; // cloning in ES6 Way to avoid deep copy
 295             tempRow[tempRow.length-1] = relationship;
 296             rows.push(tempRow);
 297           }
 298         }
 299       }else{
 300         rows.push(row);
 301       }
 302       }else{
 303         rows.push(row);
 304       }
 305       console.log('Rows before pushing>>>>>>>>>>>',rows);
 306       let initMergeCell = (n==0) ? 2: worksheet.rowCount + 1;
 307       worksheet.addRows(rows);
 308       console.log('initMergeCell>>>>>>',initMergeCell);
 309       //worksheet.getColumn(columns.length).values = relationships;
 310       let lastMergeCell = (relativeLength==0)? initMergeCell : initMergeCell + relativeLength - 1;
 311       console.log('lastMergeCell>>>>>>',lastMergeCell);
 312       for(var x=0;x<columns.length-1;x++){
 313         var cell = excelCellsHeader[x] + initMergeCell +':'+excelCellsHeader[x]+lastMergeCell;
 314         worksheet.mergeCells(cell);
 315       }
 316     }
 317     var workbookName = 'NodesList_'+nodesLength+'_Results.xlsx';
 318     generateWorkbook(workbook,workbookName);
 319   }
 320   var generateRegexForDsl= function(nodeType){
 321     var nodePatternwithProp = nodeType+"\\*\\{.*?\\}\\(.*?\\)[\\,|\\>|\\]|\\)]|"+nodeType+"\\*\\(.*?\\)\\{.*?\\}[\\,|\\>|\\]|\\)]|"+nodeType+"\\{.*?\\}\\(.*?\\)[\\,|\\>|\\]|\\)]|"+nodeType+"\\(.*?\\)\\{.*?\\}[\\,|\\>|\\]|\\)]|"+nodeType+"\\{.*?\\}[\\,|\\>|\\]|\\)]|"+nodeType+"\\*\\{.*?\\}[\\,|\\>|\\]|\\)]";
 322                 return nodePatternwithProp;
 323   }
 324  var generateExcelFromTabularView = function(tabledata,tableheaders,workbookName) {
 325    console.log('inside generateExcelFromTabularView>>>>>');
 326    //Creating New Workbook
 327    var workbook = new Excel.Workbook();
 328    var worksheet = workbook.addWorksheet('Results',{properties: {showGridLines: true}});
 329    var columns = [];
 330    if (!worksheet.columns) {
 331      worksheet.columns = Object.keys(tableheaders).map((k) => ({ header: tableheaders[k].name, key: tableheaders[k].name, width:20}))
 332    }
 333    var rows = [];
 334    var row = [];
 335    for(var n=0; n<tabledata.length; n++){
 336        console.log('TableData<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<',tabledata[n]);
 337        row = [];
 338        Object.keys(tableheaders).map((k) => { row.push(tabledata[n][tableheaders[k].name])});
 339        rows.push(row);
 340    }
 341    worksheet.addRows(rows);
 342    generateWorkbook(workbook,workbookName);
 343  }
 344  var generateExcelFromTabularViewMultiTabs = function(tabData,tabHeaders,workBookName) {
 345    console.log('inside generateExcelFromTabularViewMultiTabs>>>>>');
 346    //Creating New Workbook
 347    var workbook = new Excel.Workbook();
 348    Object.keys(tabData).forEach((key,index) => {
 349         var worksheet = workbook.addWorksheet((index+1)+'_'+key,{properties: {showGridLines: true}});
 350         var columns = [];
 351         var tableData = tabData[key];
 352         var tableHeaders = tabHeaders[key];
 353         if (!worksheet.columns) {
 354           worksheet.columns = Object.keys(tableHeaders).map((k) => ({ header: tableHeaders[k].name, key: tableHeaders[k].name, width:20}))
 355         }
 356         var rows = [];
 357         var row = [];
 358         for(var n = 0; n < tableData.length; n++){
 359             console.log('TableData<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<',tableData[n]);
 360             row = [];
 361             Object.keys(tableHeaders).map((k) => { row.push(tableData[n][tableHeaders[k].name])});
 362             rows.push(row);
 363         }
 364         worksheet.addRows(rows);
 365    });
 366    generateWorkbook(workbook,workBookName);
 367  }
 368  var generateViolationExcels = function(tabledata,tableheaders) {
 369    generateExcelFromTabularView(tabledata, tableheaders, 'DataIntegrityViolationTable.xlsx');
 370  }
 371  var generateWorkbook = function(workbook,workbookName){
 372    console.log('generateWorkbook :',workbookName);
 373     workbook.xlsx.writeBuffer().then(function (data) {
 374       const blob = new Blob([data],
 375         { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
 376       const url = window.URL.createObjectURL(blob);
 377       const anchor = document.createElement('a');
 378       document.body.appendChild(anchor);
 379       anchor.href = url;
 380       anchor.download = workbookName;
 381       anchor.click();
 382       window.URL.revokeObjectURL(url);
 383     });
 384   }
 385
 386
 387 export const ExportExcel = {
 388   buildAttrList : buildAttrList,
 389   generateExcels : generateExcels,
 390   generateViolationExcels : generateViolationExcels,
 391   generateExcelFromTabularView: generateExcelFromTabularView,
 392   generateExcelFromTabularViewMultiTabs: generateExcelFromTabularViewMultiTabs,
 393   getDescriptionForNodes: getDescriptionForNodes
 394 };