importData

function importData(context)
{

/***
*
* Extract data from Sage Intacct
*
* This code uses the Intacct API (https://developer.intacct.com/api/) to extract General Ledger balances and details,
* and also several other tables that may hold data of interest.
*
* The extracts break out into two basic types: GL data, which requires reporting book and period parameters, and other data,
* which does not.  
*
* The staging tables are mostly defined by querying Intacct and replicating the table structure in Adaptive.  The one exception
* is the GL Balances table.  That table is not a replica of a table in Intacct so the layout has to be explicitly defined.
*
* Data in Intacct is organized in "reporting books", with "Accrual" being the default but others are common.  Data has to be extracted
* one reporting book at a time.  The main extract loop for GL data loops first over all reporting books, and within that loop over
* all requested periods.  
*
* There may also be user defined dimensions.  The paramater "User Defined Dimensions (comma separated)" holds the names of any user defined dimensions you wish to include.
* Once the list has been added, or updated, in the parameter, you must run "import structure" to build the staging tables to hold the
* user defined dimension information.  The extract itself will automatically include the user defined dimensions.
*
* Programming note:
* Responses from Intacct are in XML format.  Two different techniques are used to parse the XML. For smaller responses the non-streaming parser created by ai.xml.createParser() is used.
* Most tables are potentially large, so the streaming SAX parser created by ai.xml.sax.createFromUrl() is used instead.  
*
***/

   //global settings
   const intacctSessionTimeoutTolerance = 600; //seconds; if session timeout is less than this many seconds away get a new session token
   const maxRows = 1000;    //number of rows returned by paged call; 1000 is the maximum
   var offset = 0;         //for query calls, incremental steps if more than maxRows of data returned
   
  //Get values from designer settings, and set other variables needed for Intacct API calls
   var dataSource = context.getDataSource();
   
   //Intacct details
   var intacctRequestParms =  getintacctRequestParms(dataSource);
   var intacctGLRequestParms = getintacctGLRequestParms(dataSource);

   var intacctURL = dataSource.getSetting('Intacct URL').getValue();
   var method = 'POST';
   var headers = { 'Content-Type': 'x-intacct-xml-request' };

   var periodRange = dataSource.getSetting('Period Range');
   
   //the Adaptive API is called to get start and end dates of the accounting periods
   var adaptiveUser = dataSource.getSetting('Adaptive User').getValue();
   var adaptivePassword = dataSource.getSetting('Adaptive Password').getValue();

   /***
    *
    * validate that all required parameters have valid values
    *
    *
    * some paramaters have defaults; set the defaults if there user made no selection
    *
    **/
   if (intacctGLRequestParms.reportingBooks === null || intacctGLRequestParms.reportingBooks.trim() === '') intacctGLRequestParms.reportingBooks = 'Accrual'; //default
   intacctGLRequestParms.reportingBooks = intacctGLRequestParms.reportingBooks.toUpperCase();
   if (intacctURL === null || intacctURL.trim() === '') intacctURL = 'https://www.intacct.com/ia/xml/xmlgw.phtml'; //default
   //user could validly specify no dimensions
   //if (intacctGLRequestParms.intacctDimensions === null || intacctGLRequestParms.intacctDimensions.trim() === '') intacctGLRequestParms.intacctDimensions = 'Location,Department,Project,Customer,Vendor,Employee,Item,Class'; //default
   if (intacctGLRequestParms.intacctDimensions === null || intacctGLRequestParms.intacctDimensions.trim() === '') ai.log.logVerbose("No dimensions specified","No dimensions were specified so data will not be split by any dimensions");
   
   /***
    *
    * other required parameters do not have defaults; report a fatal error if empty
    *
    ***/
   var missingParms = '';
   
   function checkIfEmpty(item) {
       //item is a 2 element array; the thing to check, and some text for an error message
       //missingParms is a string that will contain a list of parameters that are empty
       if (item[0] === null || item[0].trim() === '') missingParms += item[1] + '; ';
   }
   
   [
       [intacctRequestParms.userID,'Intacct UserID'],
       [intacctRequestParms.userPassword,'Intacct Password'],
       [intacctRequestParms.companyId, 'Company ID'],
       [intacctRequestParms.senderId,'Sender ID'],
       [intacctRequestParms.senderPassword,'Sender Password'],
       [adaptiveUser,'Adaptive User'],
       [adaptivePassword, 'Adaptive Password']
   ].forEach(checkIfEmpty);
   
   if (missingParms.length > 0 ) {
       missingParms = missingParms.substring(0, missingParms.length - 2);  //trim last semicolon and space
       throw('The following required parameter values are empty: ' + missingParms);
   }

   var rowsProcessed = 0;
   var extractPeriodRange = {};    //holds start and end day, month, and year values from user periodRange parameter
   
   var requestStart,firstRequestBody,requestEnd,rows,rowsremaining,response;    //for making api requests to Intacct

   var reportingBook='unknown', reportingPeriodName='unknown', reportingPeriodStartDate='unknown', reportingPeriodEndDate='unknown';   //defined here, needed inside different functions

   // Prepare to extract data from Intacct and load into the staging table
   var rowset = context.getRowset();
   rowset.setSmartParsingEnabled(true);
   var tableId = rowset.getTableId();
   var columns = rowset.getColumns();
   var columnRemoteIds = getColumnRemoteIds(rowset);

   //get the session token and new endpoint
   var sessionAndEndpoint = getIntacctSessionID(intacctURL,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;
   
   switch(tableId) {
       /***
        *
        * GL Balances and GLDETAIL require different processing from other tables due to the need to loop over reporting books and periods
        *
        ***/

       case 'GL Balances' :
       case 'GLDETAIL' :
           /***
            *
            * Build specific extract requests for these tables.  
            *
            * We have to loop over both reporting books and reporting periods.
            * The exact syntax for specifying the request varies with the table.
            *
            ***/
           
           //get the specified period range info for the GL balances import
           var reportingPeriodsToImport = getReportingPeriodsToImport(periodRange);

           /***
            *
            * if there are user defined dimensions, build the clause to include them in balances request
            * and also need to add them to the groupby list, with a leading comma to make a syntactily correct list
            *
            ***/
           var userDefinedDimensions='';
           if (intacctGLRequestParms.userDefinedDimensions !== null && intacctGLRequestParms.userDefinedDimensions !== '') {
               userDefinedDimensions = intacctGLRequestParms.userDefinedDimensions.split(',');   //turn into array
           }
           var uddXMLclause='';
           if (userDefinedDimensions.length > 0 ) {
               uddXMLclause = '<userDefinedDimensions>';
               for (let j=0; j<userDefinedDimensions.length; j++ ) {
                   uddXMLclause += '<userDefinedDimension><objectName>' + userDefinedDimensions[j] + '</objectName></userDefinedDimension>';
               }
               uddXMLclause += '</userDefinedDimensions>';
               userDefinedDimensions = ',' + userDefinedDimensions.join(',');    // turn back into string, add leading comma for groupBy clause
           }


           /***
            *
            * Finally, start pulling the data from Intacct
            *
            * ***
            *
            * Iterate over reporting books
            *
            ***/
           var reportingBooksToExport = intacctGLRequestParms.reportingBooks.split(',');   //put reportingbooks into an array
   
           for (reportingBook of reportingBooksToExport) {

               if (reportingBook === null || reportingBook === '') continue;   //skip blank values
               ai.log.logVerbose('Beginning import for book ' + reportingBook);

               /***
                *
                * Iterate over reporting periods
                *
                ***/

               for (let i=0; i<reportingPeriodsToImport.length; i++ ) {
               
                   reportingPeriodStartDate = reportingPeriodsToImport[i].startDate;
                   reportingPeriodEndDate = reportingPeriodsToImport[i].endDate;

                   //build the first part of the request, common for both GL Balances and GLDETAIL
                   //this is done inside the loop because the sessionid could change
                   var glExportRequest, glExportRequestHeader, glExportQuery, glExportRequestFooter;    //query will vary if multiple requests are required
                   glExportRequestHeader = '<request><control><senderid>' + intacctRequestParms.senderId + '</senderid><password>' + intacctRequestParms.senderPassword + '</password><controlid>' + intacctRequestParms.control_controlid + '</controlid><dtdversion>' + intacctRequestParms.control_dtdversion + '</dtdversion></control>';
                   glExportRequestHeader += '<operation><authentication><sessionid>' + operation_sessionid + '</sessionid></authentication>';
                   glExportRequestHeader += '<content><function controlid="' + intacctRequestParms.control_controlid + '">';
                   
                   switch(tableId) {

                       //the export function is different between the two GL tables
                       case 'GL Balances' :

                           glExportQuery = '<get_accountbalancesbydimensions>';
                           
                           glExportQuery += '<startdate>' + getDateElements(reportingPeriodStartDate) + '</startdate>';
                           glExportQuery += '<enddate>' + getDateElements(reportingPeriodEndDate) + '</enddate>';

                               /*  include this inside get_accountbalancesbydimensions to limit to an account range
                               <startaccountno>4000</startaccountno> \
                               <endaccountno>4999</endaccountno> \   */

                           glExportQuery += '<reportingbook>' + reportingBook + '</reportingbook>';
       
                           glExportQuery += uddXMLclause;    //user defined dimensions, if any
                                                   
                           glExportQuery += '<groupby>' + intacctGLRequestParms.intacctDimensions + userDefinedDimensions + '</groupby></get_accountbalancesbydimensions>';
               
                           break;

                       case 'GLDETAIL' :

                           //generate the query block, incorporating the offset needed for paging calls
                           glExportQuery = setGLDetailQuery(offset);
                           break;

                       default:
                           throw('FATAL ERROR Attempt to create extract request for table ' + tableId);
   
                   }   //end switch

                   //close out the request
                   glExportRequestFooter = '</function></content></operation></request>';
                   
                   //put it all together
                   glExportRequest = glExportRequestHeader + glExportQuery + glExportRequestFooter;

                   /***
                    *
                    * Make Intacct call, process results, load staging
                    *
                    ***/
               
                   response = updateStagingFromGLQuery(tableId,rowset,intacctURL,glExportRequest,reportingBook,reportingPeriodStartDate);
                   if (!response) {
                       ai.log.logError('Intacct Request Error','The request to read data from Intacct failed.  See log for additional messages.');
                       break;    
                   }
                   numremaining = response.numremaining;
                   
                   //check for more records; if so, increment offset and call it again
                   
                   if (tableId =='GLDETAIL' && numremaining !== null && numremaining != '0') {
                       //There are more records for GLDETAIL, need to keep calling until we get them all
                       while (numremaining != '0') {
                           offset = offset + parseInt(numremaining);   //compute where to start with next call

                           //build the query
                           glExportQuery = setGLDetailQuery(offset);
                           glExportRequest = glExportRequestHeader + glExportQuery + glExportRequestFooter;    //put it all together

                           /***
                            *
                            * Make Intacct call, process results, load staging
                            *
                            ***/
               
                           response = updateStagingFromGLQuery(tableId,rowset,intacctURL,glExportRequest,reportingBook,reportingPeriodStartDate);
                           if (!response) {
                               ai.log.logError('Intacct Request Error','The request to read data from Intacct failed.  See log for additional messages.');
                               break;    
                           }
                           numremaining = response.numremaining;

                       }
                   }
               
               }   //end for reporting periods
           }   //end for reporting books

           function setGLDetailQuery(offset) {
               /***
                * internal function to build GLDETAIL query, setting the offset appropriately
                *
                *
                        <query>
                         <object>GLDETAIL</object>
                         <select>
                           <field>BOOKID</field>
                           <field>ENTRY_DATE</field>
                           <field>ACCOUNTNO</field>
                           <field>AMOUNT</field>
                           <field>TRX_AMOUNT</field>
                         </select>
                         <filter>
                           <and>
                           <equalto>
                             <field>MODULEKEY</field>
                             <value>2.GL</value>
                           </equalto>
                           <equalto>
                             <field>BOOKID</field>
                             <value>ACCRUAL</value>
                           </equalto>
                           <greaterthanorequalto>
                               <field>ENTRY_DATE</field>
                               <value>01/01/2011</value>
                           </greaterthanorequalto>
                           <lessthanorequalto>
                               <field>ENTRY_DATE</field>
                               <value>02/28/2011</value>
                           </lessthanorequalto>
                       </and>
                         </filter>
                       </query>
                *
                ***/
   
               var glExportQuery = '<query><object>GLDETAIL</object>';

               //get columns in staging table
               glExportQuery += '<select>';
               for (var j=0; j<columnRemoteIds.length; j++) {
                   glExportQuery += '<field>'+ columnRemoteIds[j] +'</field>';
               }
               glExportQuery +='</select>';
               
               glExportQuery +='<filter><and>';
               glExportQuery +='<equalto><field>MODULEKEY</field><value>2.GL</value></equalto>';
               glExportQuery +='<equalto><field>BOOKID</field><value>' + reportingBook +'</value></equalto>';

               //get start and end date of reporting period
               glExportQuery += '<greaterthanorequalto><field>ENTRY_DATE</field><value>' + reportingPeriodStartDate + '</value></greaterthanorequalto>';
               glExportQuery += '<lessthanorequalto><field>ENTRY_DATE</field><value>' + reportingPeriodEndDate + '</value></lessthanorequalto>';

               glExportQuery += '</and></filter><pagesize>' + maxRows + '</pagesize><offset>' + offset + '</offset></query>';

               return glExportQuery;
               
           }   //end setGLDetailQuery

           break;  //end GL Balances
       
       default:
           
           /***
            *
            * all other tables, using the readByQuery method
            *
            ***/
           
           rowsremaining = -1; //intial condition
           while(rowsremaining != 0) {    

               /***
                *
                * Make Intacct call, process results, load staging
                *
                ***/
               
               //returns {rows,rowsremaining,operation_sessionid,intacctURL}
               response = intacctReadByQueryRequest(tableId,rowset,intacctRequestParms,maxRows,operation_sessionid,intacctURL,rowsremaining);                

               if (!response) {
                   ai.log.logError('Intacct Request Error','The request to read data from Intacct failed.  See log for additional messages.');
                   break;    
               }
               
               //set variables from response
               rowsremaining = response.rowsremaining;
               if (response.operation_sessionid !== undefined) operation_sessionid = response.operation_sessionid;
               if (response.intacctURL !== undefined) intacctURL = response.intacctURL;

          } //end while        
         
   }   //end table switch
   
   /***
    *
    *  Internal functions.  These may modify variables that are defined outside the functions
    *
    ***/

  function intacctReadByQueryRequest(table,rowset,intacctRequestParms,maxRows,operation_sessionid,intacctURL,rowsremaining) {

      /***
       *
       * returns {rows,rowsremaining,operation_sessionid,intacctURL};
       * return false if any errors in getting the data
       *
       * Read by query request.  If more than maxPages of data is returned then rowsremaining will be non-zero (for number of rows remaining) and subsequent calls will be required.
       * rowsremaining is initially set to -1 coming in.
       *
       ***/
      var requestStart, requestQuery, requestEnd;
      var operation_sessionid_internal = operation_sessionid;
      var intacctURL_internal = intacctURL;
     
       var columnRemoteIds = getColumnRemoteIds(rowset);
       var cols = columnRemoteIds.join(",")
     

       /***
        *
        * Build the read-by-query HTTPS request
        *
        ***/

       //first part of request, authentication etc.
       requestStart = '<request><control><senderid>' + intacctRequestParms.senderId + '</senderid><password>' + intacctRequestParms.senderPassword + '</password><controlid>' + intacctRequestParms.control_controlid + '</controlid><dtdversion>' + intacctRequestParms.control_dtdversion + '</dtdversion></control>';
       requestStart += '<operation><authentication><sessionid>' + operation_sessionid + '</sessionid></authentication>';
       requestStart += '<content><function controlid="' + intacctRequestParms.control_controlid + '">';
       
       //middle of request, the actual query
       if (rowsremaining == -1) {
           //intial call
           requestQuery = '<readByQuery><object>' + table + '</object><fields>' + cols + '</fields><query></query><pagesize>' + maxRows + '</pagesize><returnFormat>xml</returnFormat></readByQuery>';
       } else {
           //subsequent calls to get remaining rows
           requestQuery = '<readMore><object>' + table + '</object></readMore>' ;
       }        
       //close out the request
       requestEnd = '</function></content></operation></request>';

       //put it all together
       var strXMLRequest = requestStart + requestQuery + requestEnd;
       
       /***
        *
        * Make the request, load staging
        *
        ***/
       
       //returns {rowsremaining, sessionTO}
       var response = updateStagingFromReadByQuery(table, rowset, intacctURL, strXMLRequest);     //retrieve the rows of data from Intacct, push to staging
       if (!response) return false;

       /***
        *
        * Update session id if needed
        *
        ***/
       
       sessionTO = response.sessionTO;
       sessionTimeOut = new Date(sessionTO);   //datetime when session expires

       //intacctSessionTimeoutTolerance is tolerance in seconds, multiply by 1000 to get milliseconds
       //compute how much time is left to expiration, compare with the tolerance
       var needNewSessionId = (parseInt((sessionTimeOut.getTime() - new Date().getTime()).toString()) < intacctSessionTimeoutTolerance * 1000)
       if (needNewSessionId) {
           var sessionAndEndpoint = getIntacctSessionID(intacctURL,intacctRequestParms);
           if (!sessionAndEndpoint)    {
               ai.log.logError('Intacct Request Failed','The request to Intacct to update the session id failed.  See log for additional messages.');
               return false;
           }
           //extract session id and new url from response
           operation_sessionid_internal = sessionAndEndpoint.sessionId;
           intacctURL_internal = sessionAndEndpoint.endpoint;
       }

       /**
        *
        * return results
        *
        ***/

       return {rowsremaining:response.rowsremaining,operation_sessionid:operation_sessionid_internal,intacctURL:intacctURL_internal};
       
   }    //end intacctReadByQueryRequest  
 

   function getReportingPeriodsToImport(periodRange) {    
       /***
        *
        * Get the specified period range info for the GL balances importf
        *
        * Get the period range from Adaptive Time Stratum Settings
        * Use the parameter periodRange to pull all periods that fall between start and end
        * Return data in Intacct by converting YYYY-MM-DD to MM/DD/YYYY
        * Set Period End Dates to Day-1 (Adaptive period end date is first day of next month; for Intacct request we want last day of current month)
        *
        ***/
       var periodRangeStartSetting = periodRange.getValue().getFromPeriodStartDateTime();
       var periodRangeEndSetting = periodRange.getValue().getToPeriodEndDateTime();

       //extract start and end dates into strings formatted YYYY-MM-DD
       //these will be used to pull exportTime periods
       
       extractPeriodRangeStartDate = periodRangeStartSetting.substring(0,10);
       extractPeriodRangeEndDate = periodRangeEndSetting.substring(0,10);

       /***
        *
        * Make Adaptive exportTime call
        *
        ***/

       var adaptiveURL = 'https://api.adaptiveplanning.com/api/v39';
       var method = 'POST';
       var xmlBody = '<call method="exportTime" callerName="IntacctImport">';
       xmlBody += '<credentials login="' + adaptiveUser + '" password="' + adaptivePassword + '"/></call>';
       var headers = null;
       try {
           response = ai.https.request(adaptiveURL, method, xmlBody, headers);
       } catch(e) {
           //Catch catastrophic failure
           ai.log.logError('exportTime Failed','exportTime request failed.  Error: ' + e);
           return false;
       }
       
       //validate response
       var responseCode = response.getHttpCode();

       if (responseCode != 200){
           ai.log.logError('exportTime Failed','exportTime response http code: ' + responseCode);
           return false;
       }
       
       /***
        *
        * Process the response
        *
        ***/
       var responseBody = response.getBody();
       var defaultStratumID;
       var withinPeriodRange = false; //Used to add periods between specified start and end ranges
       var doneParsing = false; //Indicates that all data has been collected
       var reportingPeriodMap = []; //For mapping calendar dates to Intacct reporting periods
       var loadTimeCallbacks = {};
       var responseStatusSuccess, responseMessage, adaptiveMessages= [], foundMessages=false, foundMessage=false, messageText='';

       //Coded with expectation that extractTime is ordered chronological and is consistent
       //stratum tags should be read before period tags
       loadTimeCallbacks.onOpenTag = function(tag){
           switch (tag.name){
               case 'response' :
                   responseStatusSuccess = tag.attributes['success'] == 'true';
                   break;
               case 'messages' :
                   foundMessages = true;
                   break;
               case 'message' :
                   foundMessage = true;
                   messageKey = tag.attributes['key'] ;
                   break;
               case 'stratum':
                   //assumes the "default" startum is the one we want
                   if (tag.attributes['isDefault'] == 1) {
                       defaultStratumID = tag.attributes['id'];
                   }
                   break;
               case 'period':
                   //if no more data is being collected, skip this tag
                   if(doneParsing){
                       break;
                   }

                   //if still looking for data, process tag if correct stratumId
                   if (tag.attributes['stratumId'] == defaultStratumID){
                       var periodStart;
                       var periodEnd;
                       //runs when first part of range is met
                       if (extractPeriodRangeStartDate == tag.attributes['start']){
                           periodStart = tag.attributes['start'];
                           periodEnd = tag.attributes['end'];

                           //check if end of range is part of first period
                           //if not set withinPeriodRange and skip else if and else checks
                           //if yes then all data is collected after this run
                           if(extractPeriodRangeEndDate != periodEnd){
                               withinPeriodRange = true;
                           } else {
                               doneParsing = true;
                           }
                       } else if (extractPeriodRangeEndDate == tag.attributes['end']){
                           //runs when last part of range is met
                           //set withinPeriodRange to false and that all data has been collected
                           withinPeriodRange = false;
                           doneParsing = true;
                           periodStart = tag.attributes['start'];
                           periodEnd = tag.attributes['end'];
                       } else if (withinPeriodRange){
                           //add values and continue since we are somewhere in the middle of the range
                           periodStart = tag.attributes['start'];
                           periodEnd = tag.attributes['end'];
                       }
                       
                       //If data was collected, reformat the data and add it to output
                       if(typeof periodStart != 'undefined' && typeof periodEnd != 'undefined'){
                           //Reformat periodStart YYYY-MM-DD to MM/DD/YYYY
                           mo = periodStart.substring(5,7);
                           dy = periodStart.substring(8,10);
                           yr = periodStart.substring(0,4);
                           periodStartTemp = mo + '/' + dy + '/' + yr;

                           //Reformat periodStart YYYY-MM-DD to MM/DD/YYYY -1 day
                           periodEndTemp = new Date(periodEnd +'T00:00:00');
                           periodEndTemp = new Date(periodEndTemp.setDate(periodEndTemp.getDate()-1));
                           yr = periodEndTemp.getFullYear().toString();
                           mo = (periodEndTemp.getMonth() + 1); //Month is 0 based
                           pad = '';
                           if (mo < 10) {
                               pad = '0';
                           }
                           mo = pad + mo.toString();
                           dy =  periodEndTemp.getDate();
                           pad = '';
                           if (dy < 10) {
                               pad = '0';
                           }
                           dy = pad + dy.toString();
                           periodEndTemp = mo + '/' + dy + '/' + yr;

                           reportingPeriodMap.push({startDate: periodStartTemp, endDate: periodEndTemp});
                       }
                   }
           }
       }   //end onOpenTag
       
       loadTimeCallbacks.onText = function(text) {
           if (foundMessage) {
               foundText=true;
               messageText = text;    //save value in row
           }
       
       };


       loadTimeCallbacks.onCloseTag = function(tagName) {
           switch (tagName) {
               case 'response' :
                   responseStatusSuccess = true; //TODO work around bug PLNINT-9135
                   if (!responseStatusSuccess) {
                       //something went wrong, log the messages
                       for (var msgIdx in adaptiveMessages) {
                           ai.log.logError('Adaptive GetTime API error', adaptiveMessages[msgIdx]);
                       }
                       //fatal error
                       throw('Error attempting to process Adaptive Time Stratum to determine accounting periods.  See log for additional details');
                   }
                   break;
               case 'messages' :
                   foundMessages = false;
                   break;
               case 'message' :
                   adaptiveMessages.push(messageKey + ':' + messageText);
                   foundMessage = false;
                   break;
           }
       }   // end onCloseTag

       //Sets SAX parser which will run the above onOpenTag function for each tag
       var timeParser = ai.xml.sax.createFromText(responseBody, "UTF8", loadTimeCallbacks);
       timeParser.readToEnd();   //process response

       return reportingPeriodMap;

   }   //end getReportingPeriodsToImport  
 
   function getDateElements(aMMDDYYYYdate) {
       /***
        *
        * Generate an XML formated block containing a date
        *
        * assumes date is in MM/DD/YYYY format
        *                    0123456789
        *
        * returns:
        *
           <year>2010</year>
           <month>06</month>
           <day>01</day>

        *
        ***/
       
       let dateElements = '<year>' +aMMDDYYYYdate.substring(6,10) +'</year>';
       dateElements += '<month>' + aMMDDYYYYdate.substring(0,2) + '</month>';
       dateElements += '<day>' + aMMDDYYYYdate.substring(3,5) + '</day>';

       return dateElements;
       
   };  //end getDateElements
   
}   //end importData

/***
*
* Global functions
*
***/

function updateStagingFromGLQuery(tableId,rowset,url,requestBody,reportingBook,reportingPeriodStartDate) {
   
   /***
    *
    * returns {numremaining : numremaining}
    *
    * Post GLQuery request to Intacct, process the results, load data into staging
    *
    ***/

   var columnRemoteIds = getColumnRemoteIds(rowset);

   var row = {};
   var loadGLCallbacks = {};
   var data = {
       currentTag: ''
   };

   //state flags for keeping track of where we are in the xml stream, and for capturing some data
   var foundOperation=false, foundResult=false, foundData=false, foundAccountBalance=false, foundText=false, foundGLDetail = false, foundError = false, errorsFound=false, numremaining=0;
   var functionText, errorNo, errorDescription, errorDescription2, correction;
   var errorMsg='';

   /***
    * Set up the SAX parser callbacks to process the XML reponse
    *
     
       For GL Balance the result looks like -
           <result>
           <status>success</status>
           <function>get_accountbalancesbydimensions</function>
           <controlid>f860fdfd-f88c-41ae-a93f-e1d1b8012ace</controlid>
           <data>
               <accountbalance>
                   <glaccountno>4000</glaccountno>
                   ... for each field
               </accountbalance>
               
       For GLDETAIL the result looks like -                        
           <result>
               <status>success</status>
               <function>query</function>
               <controlid>d5589b55-3c0b-40ff-8e19-d8aa11dab11e</controlid>
               <data listtype="GLDETAIL" totalcount="10" offset="0" count="10" numremaining="0">
                   <GLDETAIL>
                       <BATCHKEY>44</BATCHKEY>
                       ... for each field
                   </GLDETAIL>
    *
    *
    ***/
   loadGLCallbacks.onOpenTag = function(tag) {
       data.currentTag = tag.name;
       switch (tag.name) {
           case 'errormessage' :
               foundError = true;  //bad news
               break;
           case 'operation' :
               foundOperation = true;
               break;
           case 'result' :
               foundResult = true;
               break;
           case 'data' :
               foundData = true;
               numremaining = tag.attributes['numremaining']; //GLDETAIL may require more than one call
               break;
           case 'accountbalance' :
               foundAccountBalance = true;
               row = {};   //reset
               break;
           case 'GLDETAIL' :
               foundGLDetail = true;
               row = {};   //reset
               break;
           default :
       }
   
   };
   
   loadGLCallbacks.onText = function(text) {
       // looking for error messages
       switch (data.currentTag) {
           case 'correction' :
               correction = text;
               break;
           case 'description2' :
               errorDescription2 = text;
               break;
           case 'description' :
               errorDescription = text;
               break;
           case 'errorno' :
               errorNo = text;
               break;
           case 'function' :
               functionText = text;
               break;
       }                        

       if (foundAccountBalance || foundGLDetail) {
           foundText=true;
           row[data.currentTag] = text;    //save value in row
       }

   };


   loadGLCallbacks.onCloseTag = function(tagName) {
       let cells = [];
       switch (tagName) {
       case 'errormessage' :
           //Oops, found an error
           //assemble the error message to return to caller
           foundError = false;
           if (functionText) errorMsg += 'Operation: ' + functionText + ' ';
           errorMsg += 'Error Number: ' + errorNo + ' Description: ' + errorDescription;
           if (errorDescription2) errorMsg += '; ' + errorDescription2;
           if (correction) errorMsg += ' Correction: ' + correction;
           
           ai.log.logError('Intacct API request operation failed',errorMsg);

           errorsFound = true;
           
       case 'operation' :
           foundOperation = false;
           break;
       case 'result' :
           foundResult = false;
           break;
       case 'data' :
           foundData = false;
           break;
       case 'accountbalance' :
           //done processing one "row" of data
           foundAccountBalance = false;

           //tack on reporting book
           row.reportingbook=reportingBook;
           
           //tack on reporting period start date
           row.periodstartdate = reportingPeriodStartDate;

           cells = [];
           //put data in the right column order
           for (let i=0;i<columnRemoteIds.length;i++) {
               let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value
               cells.push(val);
           }
           rowset.addRow(cells);   //push to staging

           break;
       case 'GLDETAIL' :
           foundGLDetail = false;

           cells = [];
           //put data in the right column order
           for (let i=0;i<columnRemoteIds.length;i++) {
               let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value
               cells.push(val);
           }
           rowset.addRow(cells);   //push to staging

           break;
       default:
           if (foundAccountBalance || foundGLDetail) {
               if (foundText) {
                   foundText = false;  //reset flag
               } else {
                   //empty field, output an empty string
                   row[data.currentTag] = '';
               }
           }
       }  
   }; //end onCloseTag

   var method = 'POST';
   var headers = { 'Content-Type': 'x-intacct-xml-request' };
   var glParser = ai.xml.sax.createFromUrl(url, method ,requestBody, headers, loadGLCallbacks);
   glParser.readToEnd();
   
   if (errorsFound) return false;  //errors mean no data was returned

   return {numremaining : numremaining};    
           
} //end updateStagingFromGLQuery


function updateStagingFromReadByQuery(tableId,rowset,url,requestBody) {
   
   /***
    *
    * returns {rowsremaining, sessionTO}
    *
    * Post read-by-query request to Intacct, process the results, load data into staging
    *
    ***/
   
   //state flags for keeping track of where we are in the xml stream, and for capturing some data
   var foundOperation=false, foundAuthentication=false, foundResult=false, foundSessionTimeout=false, foundResult=false, foundData=false, foundTableRow=false, foundError = false, errorsFound = false;
   var functionText, errorNo, errorDescription, errorDescription2, correction, sessionTimeout, rowsRemaining;
   var errorMsg='', rows=[], row={};
   var rowCallBacks = {}, data = {}, cells=[];

   var columnRemoteIds = getColumnRemoteIds(rowset);

   /***
    *
    * Define SAX parser callbacks
    *
       
       response looks like this
       <?xml version="1.0" encoding="UTF-8"?>
       <response>
           <control>
               <status>success</status>
               <senderid>adaptiveplanning</senderid>
               <controlid>c5KLKSWFEKPR50GF4</controlid>
               <uniqueid>false</uniqueid>
               <dtdversion>3.0</dtdversion>
           </control>
           <operation>
               <authentication>
                   <status>success</status>
                   <userid>Julia</userid>
                   <companyid>adaptiveplanning-DEV</companyid>
                   <locationid></locationid>
                   <sessiontimestamp>2024-09-12T18:30:53+00:00</sessiontimestamp>
                   <sessiontimeout>2024-09-12T22:30:53+00:00</sessiontimeout>
               </authentication>
               <result>
                   <status>success</status>
                   <function>readByQuery</function>
                   <controlid>c5KLKSWFEKPR50GF4000002.00</controlid>
                   <data listtype="department" count="14" totalcount="14" numremaining="0" resultId="">
                       <department>
                           <DEPARTMENTID>QADept</DEPARTMENTID>
                           <RECORDNO>14</RECORDNO>
                           <TITLE>Brisbane-QA</TITLE>
                           <PARENTKEY>12</PARENTKEY>
                           <PARENTID>QA</PARENTID>
                           <PARENTNAME>TestQA</PARENTNAME>
                           <SUPERVISORKEY>1</SUPERVISORKEY>
                           <SUPERVISORID>1</SUPERVISORID>
                           <WHENCREATED>05/12/2023 07:14:45</WHENCREATED>
                           <WHENMODIFIED>05/12/2023 07:14:45</WHENMODIFIED>
                           <SUPERVISORNAME>John Pearce</SUPERVISORNAME>
                           <STATUS>active</STATUS>
                           <CUSTTITLE></CUSTTITLE>
                           <CREATEDBY>19</CREATEDBY>
                           <MODIFIEDBY>19</MODIFIEDBY>
                       </department>
                       ...
                   </data>
               </result>
           </operation>
       </response>                

    ***/
   
   rowCallBacks.onOpenTag = function(tag) {
       data.currentTag = tag.name;
       switch (tag.name) {
           case 'operation' :
               foundOperation = true;
               break;
           case 'authentication' :
               foundAuthentication = true;
               break;
           case 'result' :
               foundResult = true;
               break;
           case 'errormessage' :
               foundError = true;  //bad news
               break;
           case 'sessiontimeout' :
               foundSessionTimeout = true;
               break;
           case 'data' :
               foundData = true;
               rowsRemaining = tag.attributes['numremaining'] ;
               break;
           case tableId.toLowerCase() :
               foundTableRow = true;
               break;

           default :
       }
   
   };
   
   rowCallBacks.onText = function(text) {
       // looking for error messages
       switch (data.currentTag) {
           case 'sessiontimeout' :
               if (foundOperation && foundAuthentication ) sessionTimeout = text;
               break;
           case 'correction' :
               correction = text;
               break;
           case 'description2' :
               errorDescription2 = text;
               break;
           case 'description' :
               errorDescription = text;
               break;
           case 'errorno' :
               errorNo = text;
               break;
           case 'function' :
               functionText = text;
               break;
           default :
               if (foundOperation && foundResult && foundData && foundTableRow) {
                   //we're processing a row of data; grab this text value and add it to the row array
                   row[data.currentTag] = text;
               }
       }                        

   };


   rowCallBacks.onCloseTag = function(tagName) {
       let cells = [];
       switch (tagName) {
           case 'operation' :
               foundOperation = false;
               break;
           case 'authentication' :
               foundAuthentication = false;
               break
           case 'result' :
               foundResult = false;
               break;
   
           case 'errormessage' :
               //Oops, found an error
               //assemble the error message to return to caller
               foundError = false;
               if (functionText) errorMsg += 'Operation: ' + functionText + ' ';
               errorMsg += 'Error Number: ' + errorNo + ' Description: ' + errorDescription;
               if (errorDescription2) errorMsg += '; ' + errorDescription2;
               if (correction) errorMsg += ' Correction: ' + correction;

               ai.log.logError('Intacct API request operation failed',errorMsg);

               errorsFound = true;
               
           case 'sessiontimeout' :
               foundSessionTimeout = false;
               break;
           case 'data' :
               foundResult = false;
               break;
           case tableId.toLowerCase() :
               //done with one row entry; add row to collection of rows
               foundTableRow = false;

               cells = [];
               //put data in the right column order
               for (let i=0;i<columnRemoteIds.length;i++) {
                   let val = row[columnRemoteIds[i]] === undefined ? '' : row[columnRemoteIds[i]]; //has to have a value
                   cells.push(val);
               }
               
               rowset.addRow(cells);   //push to staging

               row = {}; //reset row holder

               break;
           default :                    
       }
   }; //end onCloseTag


   /***
    *
    * Make the request, process the response
    *
    **/

   var method = 'POST';
   var headers = { 'Content-Type': 'x-intacct-xml-request' };
   var saxParser = ai.xml.sax.createFromUrl(url, method, requestBody, headers, rowCallBacks);
   saxParser.readToEnd();   //process response

   if (errorsFound) return false;  //errors mean no data was returned
   
   return {rowsremaining : rowsRemaining, sessionTO : sessionTimeout};
   
} //end updateStagingFromReadByQuery


function previewData(context) {
   //previewData should do the same as importData, except that the results are not saved to staging
   //since importData is just loading the data without any other manipulation there isn't much to be learned by "previewing"
   throw('External Preview Data is not implemented');
}