importStructure

/** Import Structure function to be used for CCDS Intacct Data Source */
function importStructure(context) {

   //tables to create for readByQuery processing
   var tablesArray = ["GLDETAIL","CLASS","DEPARTMENT","GLACCOUNT","GLENTRY","ITEM","LOCATION","PROJECT","EMPLOYEE","CUSTOMER"];
   //some of the other tables available:
   //"BUDGETHEADER", "TRXCURRENCIES","GLACCOUNTBALANCE","REPORTINGPERIOND"

  //Get values from designer settings, and set other variables needed for Intacct API calls
   var dataSource = context.getDataSource();
   
   var intacctRequestParms =  getintacctRequestParms(dataSource);
   var intacctGLRequestParms = getintacctGLRequestParms(dataSource);

   var intacctURL = dataSource.getSetting('Intacct URL').getValue();


   var sessionAndEndpoint = getIntacctSessionID(intacctURL,intacctRequestParms);
//    var sessionAndEndpoint = getIntacctSessionID(intacctRequestParms);
   if (!sessionAndEndpoint)    {
       ai.log.logError('Intacct Request Failed','The request to Intacct failed.  See log for additional messages.');
       return;
   }
   //extract session id and new url from response
   var operation_sessionid = sessionAndEndpoint.sessionId;
   intacctURL = sessionAndEndpoint.endpoint;

   var builder = context.getStructureBuilder();
ai.log.logInfo('checkpoint -2')    ;

   for (tablesIndex = 0; tablesIndex < tablesArray.length; tablesIndex++) {
       
       //build Intacct request for table details
       var xmlRequest = '<?xml version="1.0" encoding="iso-8859-1"?><request><control><senderid>' + intacctRequestParms.senderId +'</senderid>'
           + '<password>' + intacctRequestParms.senderPassword + '</password><controlid>' + intacctRequestParms.control_controlid +'</controlid><uniqueid>' + intacctRequestParms.control_uniqueid + '</uniqueid>'
           + '<dtdversion>' + intacctRequestParms.control_dtdversion + '</dtdversion></control>'
           + '<operation><authentication><sessionid>' + operation_sessionid + '</sessionid></authentication>'
           + '<content><function controlid="' + intacctRequestParms.control_controlid +'"><inspect detail="1">'
           + '<object>' + tablesArray[tablesIndex] + '</object></inspect></function></content></operation></request>';

       //send XML request and get response
       var xmlResponseString = sendAndGetXML_Intacct(intacctURL,xmlRequest);
       if (!xmlResponseString)    {
           ai.log.logError('Intacct Request Failed','The request to Intacct failed.  See log for additional messages.');
           return;
       }

       //parse XML response and populate CCDS tables
       parseXMLResponseAndBuildTable_IntacctInspectWithDetail(xmlResponseString, tablesArray[tablesIndex], builder);
   }
   
   //Build GL Balances table
   //Data for this table is gathered via the get_accountbalancesbydimensions api call.  The output from this call varies depending on the dimensions included.
   //The output structure cannot be determined from an "inspect" call the way the other tables are, so the structure is defined here, and all built-in dimensions are included.
   //
   //  notes:  dataTypeIntacct is the type in Intacct, and the staging table column will be compatible.  
   //          column order doesn't matter.  The data is saved into staging by the columnName.  The columns will be displayed in alphabetical order.
   //
   var table = builder.addTable('GL Balances');
   
   var glBalancesTable = [
       {columnName:'periodstartdate',dataTypeIntacct: 'datetime',displayName: 'periodstartdate'},
       {columnName:'glaccountno',dataTypeIntacct: 'text',displayName: 'glaccountno'},
       {columnName:'startbalance',dataTypeIntacct: 'number',displayName: 'startbalance'},
       {columnName:'periodbalance',dataTypeIntacct: 'number',displayName: 'periodbalance'},
       {columnName:'endbalance',dataTypeIntacct: 'number',displayName: 'endbalance'},
       {columnName:'currency',dataTypeIntacct: 'text',displayName: 'currency'},
       {columnName:'locationid',dataTypeIntacct: 'text',displayName: 'locationid'},
       {columnName:'locationname',dataTypeIntacct: 'text',displayName: 'locationname'},
       {columnName:'departmentid',dataTypeIntacct: 'text',displayName: 'departmentid'},
       {columnName:'departmentname',dataTypeIntacct: 'text',displayName: 'departmentname'},
       {columnName:'projectid',dataTypeIntacct: 'text',displayName: 'projectid'},
       {columnName:'projectname',dataTypeIntacct: 'text',displayName: 'projectname'},
       {columnName:'customerid',dataTypeIntacct: 'text',displayName: 'customerid'},
       {columnName:'customername',dataTypeIntacct: 'text',displayName: 'customername'},
       {columnName:'vendorid',dataTypeIntacct: 'text',displayName: 'vendorid'},
       {columnName:'vendorname',dataTypeIntacct: 'text',displayName: 'vendorname'},
       {columnName:'employeeid',dataTypeIntacct: 'text',displayName: 'employeeid'},
       {columnName:'employeename',dataTypeIntacct: 'text',displayName: 'employeename'},
       {columnName:'itemid',dataTypeIntacct: 'text',displayName: 'itemid'},
       {columnName:'itemname',dataTypeIntacct: 'text',displayName: 'itemname'},
       {columnName:'classid',dataTypeIntacct: 'text',displayName: 'classid'},
       {columnName:'classname',dataTypeIntacct: 'text',displayName: 'classname'},
       {columnName:'reportingbook',dataTypeIntacct: 'text',displayName: 'reportingbook'}
   ];
   
   
   //check to see if any user defined dimensions are specified, if so, then add them to the table
   if (intacctGLRequestParms.userDefinedDimensions !== null && intacctGLRequestParms.userDefinedDimensions.trim() !== '') {
       let uud = intacctGLRequestParms.userDefinedDimensions.split(',');
       for (let i=0; i<uud.length; i++) {
           let newCol = {columnName:uud[i].trim(),dataTypeIntacct: 'text',displayName: uud[i].trim()};
           glBalancesTable.push(newCol);
       }
   }

   //add the columns to the table
   for (var iCol=0; iCol < glBalancesTable.length; iCol++) {
       addTableColumn(table,iCol,glBalancesTable[iCol].columnName,glBalancesTable[iCol].dataTypeIntacct,glBalancesTable[iCol].displayName);
   }

   return;
   
   //end main line
       
   /***
    *
    * Helper functions
    *
    ***/
   
   /** Parse XML response for tabe columns and populate CCDS
    * @param {string} responseXML - string representation of XML response to parse for table columns
    * @apram {string} strTableName - name of table to create and add columns to
    * @return {string} authenticationStatus - 'success' or 'failure'  
   */
   function parseXMLResponseAndBuildTable_IntacctInspectWithDetail(inspectResponseXML, strTableName, tableBuilder) {        
       
       try {
           var parser = ai.xml.createParser();
           var xmlDoc = parser.parse(inspectResponseXML);
           
           var docType = ai.xml.createDocType('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">');
           xmlDoc.setDOCTYPE(docType);
       
           var rootElement = xmlDoc.getRootElement();
           var operationElement = rootElement.getChildElement('operation');
           var resultElement = operationElement.getChildElement('result');
           var dataElement = resultElement.getChildElement('data');
           var typeElement = dataElement.getChildElement('Type');
           var FieldsElement = typeElement.getChildElement('Fields');
           var columnsElementArray = FieldsElement.getChildElements('Field');

           //add table
             var table = tableBuilder.addTable(strTableName);  
             
           var numColumns = columnsElementArray.length;
           //get details for each column and add columns to table
           for (var iCol=0; iCol < numColumns; iCol++) {
               
               if (strTableName.toUpperCase() == 'REPORTINGPERIOD') {
                   //a field "RECORD#" is found in the meta data, but in the data import request we think the column name is actually RECORD, which causes a data query to fail with an unhelpful error message, so skip adding it to the table.  We don't need it.
                   if (columnsElementArray[iCol].getChildElement('Name').getText() == 'RECORD#') {
//                        ai.log.logInfo('skipping column RECORD from table REPORTINGPERIOD');
                       continue;
                   }
               }
                           
               //get elements
               var nameElement = columnsElementArray[iCol].getChildElement('Name');
               var columnName = nameElement.getText();
//                ai.log.logInfo('DEBUG', 'column = ' + columnName);

/* not used                
               var groupNameElement = columnsElementArray[iCol].getChildElement('GroupName');
               var columnGroupName = groupNameElement.getText();
               
               var dataNameElement = columnsElementArray[iCol].getChildElement('dataName');
               var columnDataName = dataNameElement.getText();
               
               //get isRequired
               var isRequiredElement = columnsElementArray[iCol].getChildElement('isRequired');
               var isRequired = isRequiredElement == 'true';

               //get isReadOnly
               var isReadOnlyElement = columnsElementArray[iCol].getChildElement('isReadOnly');
               var isReadOnly = isReadOnlyElement == 'true';
*/
               var externalDataNameElement = columnsElementArray[iCol].getChildElement('externalDataName');
               var dataTypeIntacct = externalDataNameElement.getText();            
               
               var maxLengthElement = columnsElementArray[iCol].getChildElement('maxLength');
               var maxLength = maxLengthElement.getText();
   
               //var displayNameElement = columnsElementArray[iCol].getChildElement('Name');
               var displayNameElement = columnsElementArray[iCol].getChildElement('DisplayLabel');
               var displayName = displayNameElement.getText();
   
               var columnDescriptionElement = columnsElementArray[iCol].getChildElement('Description');
               var columnDescription = columnDescriptionElement.getText();
               
               var columnIdElement = columnsElementArray[iCol].getChildElement('id');
               var columnId = columnIdElement.getText();                                    
           
               //get relationship if it exists
               var columnRelationshipElement = columnsElementArray[iCol].getChildElement('relationship');
               
               if (columnRelationshipElement === undefined) {    
                   //create table column only if it's not a primary/foreign key
                   addTableColumn(table,iCol,columnName,dataTypeIntacct,displayName,maxLength);
               }    
                   
         } // end for      
   
       } catch (exception) {
           throw('EXCEPTION  ' + ' Exception from parseXMLResponseAndBuildTable_IntacctInspectWithDetail:  ' + exception + '.  Table = ' + strTableName);
       }
       
       
   }   //end parseXMLResponseAndBuildTable_IntacctInspectWithDetail
   
   
   function addTableColumn(table,iCol,columnName,dataTypeIntacct,displayName,maxLength) {
       var recordColumn = table.addColumn(columnName);
       //check column type and set dataType
       switch (dataTypeIntacct) {
           case "integer":
                   recordColumn.setIntegerColumnType();
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   //recordColumn.setIsMandatoryForImport(cIsMandatory);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("integer");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           case "string":
                   recordColumn.setTextColumnType(maxLength);
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   //recordColumn.setIsMandatoryForImport(cIncludeImports);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("string");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           case "date":
                   recordColumn.setDateTimeColumnType();
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   // recordColumn.setIsMandatoryForImports(cIsMandatory);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("date");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           case "datetime":
                   recordColumn.setDateTimeColumnType();
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   // recordColumn.setIsMandatoryForImports(cIsMandatory);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("datetime");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           case "boolean":
//                    recordColumn.setBooleanColumnType();
                   recordColumn.setTextColumnType(maxLength);  //forcing to text type.  In GLDetail values are "T" or "F", which breaks loading staging if this is a boolean type field
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   //recordColumn.setIsMandatoryForImports(cIsMandatory);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("boolean");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           case "number":
                   recordColumn.setFloatColumnType();
                   recordColumn.setDisplayName(displayName);
                   recordColumn.setDisplayOrder(iCol);
                   //recordColumn.setIsMandatoryForImports(cIsMandatory);   //in wiki not sample script
                   recordColumn.setIncludeByDefault(true);
                   recordColumn.setIncludeInKey(false);
                   recordColumn.setRemoteColumnType("number");    //in wiki not sample script
                   recordColumn.setNonFilterable(false);
                   break;
           default:
                   ai.log.logInfo("DEBUG", "Intacct Data Type: " + dataTypeIntacct);
                   break;
       } //switch

   }   //end addTableColumn
       
}   //end importStructure