Referência: exemplo de script de fonte de dados em nuvem personalizada - Deltek

Testar conexão.
Importar estrutura.
Importar dados.

Exemplo de script Deltek

/******************

Test Connection

******************/
function testConnection(context) {

    var params = config.init(context),
        parser = ai.xml.createParser(),
        res,
        root,
        xmlDoc,
        interrogationResults;

    res = ValidateLogin.call(params);
    xmlDoc = parser.parse(res.getBody());
    root = xmlDoc.getRootElement();

    interrogationResults = ValidateLogin.interrogateResponse(root, params);

    if (interrogationResults.Status === true) {
        params = interrogationResults.Parameters;
        ai.log.logVerbose('acquired session token' + params.SessionID);
        return true;
    } else {
        return false;
    }

}



/******************

Import Structure

******************/
function importStructure(context) {

    var params = config.init(context),
        parser = ai.xml.createParser(),
        res,
        root,
        xmlDoc,
        interrogationResults;

    // login to Deltek webservice
    res = ValidateLogin.call(params);
    xmlDoc = parser.parse(res.getBody());
    root = xmlDoc.getRootElement();

    interrogationResults = ValidateLogin.interrogateResponse(root, params);

    // we need the session id on subsequent webservice calls
    if (interrogationResults.Status === true) {
        params = interrogationResults.Parameters;
        ai.log.logVerbose('acquired session token ' + params.SessionID);
    } else {
        throw 'unable to retrieve SessionID';
    }

    res = ExecuteStoredProcedureXML.call(params);

    // brace yourself, this gets weird since we receive encoded XML in the XSD section of the results and thus can't parse it as is
    // we'll need to convert &lt; to < and &gt; to > and then convert that big string into XML and a parse-able DOM
    var resBody = res.getBody();
    
    // we're trying to just consume the sequence/element section of the encoded XSD since that's where the columns are specified and that's all we care about 
    var firstPart = resBody.substring(resBody.indexOf('&lt;xs:choice minOccurs="0" maxOccurs="unbounded"&gt;') + '&lt;xs:choice minOccurs="0" maxOccurs="unbounded"&gt;'.length);
    var secondPart = firstPart.substring(0, firstPart.indexOf('&lt;/xs:choice&gt;'));
    
    firstPart = secondPart.substring(secondPart.indexOf('&lt;xs:complexType&gt;') + '&lt;xs:complexType&gt;'.length);
    secondPart = firstPart.substring(0, firstPart.indexOf('&lt;/xs:complexType&gt;'));
    
    // here's where we decode the encoded XML
    // Again note that we're only in the XSD area, the data is in a separate section of the resBody
    var actualString = secondPart.replace(/&lt;/g, '<').replace(/&gt;/g, '>');
    
    ai.log.logInfo('actual string', '' + actualString);


    //Adaptive structure initialization.
    var builder = context.getStructureBuilder();

    xmlDoc = parser.parse(actualString);
    root = xmlDoc.getRootElement(); ai.log.logInfo('root name: ' + root.getName());
    
    var tableSchema = root;

    var table = builder.addTable('AdaptiveInsightsActualsSelect');

    for (var i = 0; i < tableSchema.getChildElements().length; i++) {
        var column = tableSchema.getChildElementAt(i);
        addColumn(table, column, i);
    }

    return true;

}

/******************

Import Data

******************/
function importData(context) {
    var params = config.init(context),
        parser = ai.xml.createParser(),
        res,
        root,
        xmlDoc,
        dataRoot,
        dataXmlDoc,
        interrogationResults;

    // login to Deltek webservice
    res = ValidateLogin.call(params);
    xmlDoc = parser.parse(res.getBody());
    root = xmlDoc.getRootElement();

    interrogationResults = ValidateLogin.interrogateResponse(root, params);

    // we need the session id on subsequent webservice calls
    if (interrogationResults.Status === true) {
        params = interrogationResults.Parameters;
        ai.log.logVerbose('acquired session token ' + params.SessionID);
    } else {
        throw 'unable to retrieve SessionID';
    }

    res = ExecuteStoredProcedureXML.call(params);
    // brace yourself, this gets weird since we receive encoded XML in results and thus can't parse it as is
    // we'll need to convert &lt; to < and &gt; to > and then convert that big string into XML and a parse-able DOM
    var resBody = res.getBody();
    
    // this string denotes the NewDataSet section of the results
    var START_NEWDATASET = '<ExecuteStoredProcedureXMLResult>';
    
    // this string denotes the end of the NewDataSet results
    var END_NEWDATASET = '</ExecuteStoredProcedureXMLResult>';
    
    // we want to capture everything in between START_NEWDATASET and END_NEWDATASET and translate encoded XML to decoded XML so we can parse it
    var firstPart = resBody.substring(resBody.indexOf(START_NEWDATASET) + START_NEWDATASET.length);
    var secondPart = firstPart.substring(0, firstPart.indexOf(END_NEWDATASET));
    //ai.log.logInfo('raw text length before replacing chars', '' + secondPart.length);
    
    // here's where we decode the encoded XML
    // Again note that we're in the data area, the XSD was processed in importStructure()
    var actualString = secondPart.replace(/&lt;/g, '<').replace(/&gt;/g, '>');
    //ai.log.logInfo('raw text length after replacing < and >', '' + actualString.length);
    
    // we may need to decode additional encoded strings such as quotes and apostrophes, etc.
    // so, as these are discovered, use this section as a model; shown here is converting an inverted exclamation mark and ampersands
    actualString = actualString.replace(/&iexcl;/g, '¡');
    
    /* 
       Note - the data coming back to use from the SP appears to have incorrectly-formatted encoded ampersands
       normally, an encoded ampersand is &amp;, but we're getting &amp;amp; - hence the following weird replacement
       here's a useful URL showing typical XML/HTML encodings:
       https://www.web2generators.com/html-based-tools/online-html-entities-encoder-and-decoder
    */
    actualString = actualString.replace(/&amp;amp;/g, '&'); // was (/&amp;amp;/g, 'ampersand')
    
    /* We have to do some pre-processing here, otherwise the 'XML' content won't parse properly and we'll end up
       with far fewer records than are actually in the input received from the web service call.
       The issue is that there are some (unknown to us) characters found in the AccountName that cause our parser to fail.
       The workaround is to nullify that content before attempting to parse the big blob of XML.
    */
    var revisedString = '';
    var lines = actualString.split('\n');
    for (lineIndex = 0; lineIndex < lines.length; lineIndex++) {
        if (lines[lineIndex].includes("<AccountName>")) {
            revisedString = revisedString + '<AccountName/>';        
        }
        // Organization appears to NOT have this problem of invalid characters so leaving this as a comment
        // just in case that situation should change...
        //else if (lines[lineIndex].includes("<Organization>")) {
            //revisedString = revisedString + '<Organization/>';        
        //}
        else {
            revisedString = revisedString + lines[lineIndex];
        }
    }
    actualString = revisedString;
    
    
    dataXmlDoc = parser.parse(actualString);
    dataRoot = dataXmlDoc.getRootElement(); 
    //ai.log.logInfo('root name: ' + dataRoot.getName() + ' xmlDoc length: ' + dataXmlDoc.toString().length); // s/b Tables
    
    var tableData = dataRoot;
    
    var rowset = context.getRowset();
    
    rowset.setSmartParsingEnabled(true);

    var theRecords = tableData.getChildElements();
    ai.log.logInfo('theRecords has this many records: ', '' + theRecords.length);
    

    for (var recordIndex = 0; recordIndex < theRecords.length; recordIndex++) {
        // the first chunk of returned content is the XSD; we need to ignore it; we only want the 'data'
        if (theRecords[recordIndex].getName() == 'xs:schema') {continue;}
        
        var cells = []; // where the data will go into the rowset; these must be populated in rowset/cell order (see importStructure method)
        var nextTableRecord = tableData.getChildElementAt(recordIndex); 

        for (columnIndex = 0; columnIndex < rowset.getColumns().length; columnIndex++) {
            var rowSetColumnID = rowset.getColumns()[columnIndex].getId();
            var nextRecordColumn = nextTableRecord.getChildElement(rowSetColumnID);
            var columnValue = nextRecordColumn.getText(); // 12.3400 - the data we want to stuff into the cells array
            // var columnName = nextRecordColumn.getName();  // DebitAmount - the index of the cells array to hold the data
            cells.push(columnValue);
        }
       
        rowset.addRow(cells);
        // onto next record indexed by recordIndex
    }

    return true;

}


/******************

Supporting Functions

******************/
function generateLoginConnInfo(params) {
    
    var visionDoc = ai.xml.createDocument();

    var visionRoot = ai.xml.createElement('VisionConnInfo'),
        userEl = ai.xml.createElement('userName'),
        passwordEl = ai.xml.createElement('userPassword'),
        databaseEl = ai.xml.createElement('databaseDescription'),
        securityEl = ai.xml.createElement('integratedSecurity');

    userEl.setText(params.userName);
    passwordEl.setText(params.userPassword);
    databaseEl.setText(params.databaseDescription);
    securityEl.setText(params.integratedSecurity);

    visionDoc.setRootElement(visionRoot);
    visionRoot.addChildElement(databaseEl);
    visionRoot.addChildElement(userEl);
    visionRoot.addChildElement(passwordEl);
    visionRoot.addChildElement(securityEl);

    var soapDoc = ai.xml.createDocument(),
        soapRoot = ai.xml.createElement('del:ConnInfoXML');
    
    soapRoot.setCDATAText(visionDoc.toString());
    soapDoc.setRootElement(soapRoot);
    
    //ai.log.logVerbose('Successfully set ValidateLogin CDATA');
    
    return soapDoc.toString();
    
}

function generateExecuteSprocConnInfo(params) {

    var visionDoc = ai.xml.createDocument();

    var visionRoot = ai.xml.createElement('VisionConnInfo'),
        sessionEl = ai.xml.createElement('SessionID');
    
    sessionEl.setText(params.SessionID);
    visionRoot.addChildElement(sessionEl);
    
    visionDoc.setRootElement(visionRoot);

    var soapDoc = ai.xml.createDocument(),
        soapRoot = ai.xml.createElement('del:ConnInfoXML');
    
    soapRoot.setCDATAText(visionDoc.toString());
    soapDoc.setRootElement(soapRoot);
    
    //ai.log.logVerbose('Successfully set ExecuteSprocConnInfo CDATA');
    
    return soapDoc.toString();

}

function getPeriodString(date, format) {
    var period = '';
    switch (format) {
        case 'yyyymm':
            if (date.getMonth() + 1 < 10) {
                period += date.getFullYear() + '0' + (date.getMonth() + 1);
            } else {
                period += date.getFullYear() + '' + (date.getMonth() + 1);
            }
            break;
        default:
            ai.log.logInfo('Error encountered in config.getPeriodString(date, format)');
            ai.log.logError('Unknown date string format ' + format, 'Please update script and try again.');
            throw 'Unknown date string format ' + format;
    }
    return period;
}


function generateExecuteSprocParameters(params) {

    var dataDoc = ai.xml.createDocument();

    var dataRoot = ai.xml.createElement('data'),
        startPeriod = ai.xml.createElement('param'),
        endPeriod = ai.xml.createElement('param');
        
    var startDate = params.startDate;
    var endDate = params.endDate;
        
     ai.log.logVerbose('startDate from params is: ' + startDate, 'endDate from params is: ' + endDate);
    
    startPeriod.setAttribute('name','@startperiod');
    startPeriod.setText(startDate);
        
    endPeriod.setAttribute('name','@endperiod');
    endPeriod.setText(endDate);
        
    dataRoot.addChildElement(startPeriod);
    dataRoot.addChildElement(endPeriod);
    
    // we're trying to programmatically create the equivalent of this ...
    //dataRoot.setText('<param name="@startperiod">201805</param><param name="@endperiod">201805</param>');
    
    dataDoc.setRootElement(dataRoot);

    var soapDoc = ai.xml.createDocument(),
        soapRoot = ai.xml.createElement('del:ParameterXML');
    
    soapRoot.setCDATAText(dataDoc.toString());
    soapDoc.setRootElement(soapRoot);
    
    //ai.log.logVerbose('soapRoot for SP call is : ', dataDoc.toString());
    //ai.log.logVerbose('Successfully set ExecuteSprocParams CDATA');
    
    return soapDoc.toString();
 
}

//adds column definitions to a staging table during importStructure
function addColumn(table, column, index) {

    var stagingColumn = table.addColumn(column.getAttributeValue('name'));
    stagingColumn.setDisplayName(column.getAttributeValue('name'));

    var type = column.getAttributeValue('type');

    //ensuring display order is always positive.
    stagingColumn.setDisplayOrder(index + 1);

    switch (xsdTypeMap[type]) {
        case "text":
            var len;
            try {
                len = column.getAttributeValue('length');
            } catch(e) {
                //ai.log.logVerbose('no \'length\' attribute found for column: ' + column.getAttributeValue('name'), '' + e);
                len = 500; //do not exceed 4000
            }
            stagingColumn.setTextColumnType(len);
            break;
        case "int":
            stagingColumn.setIntegerColumnType();
            break;
        case "float":
            stagingColumn.setFloatColumnType();
            break;
        case "boolean":
            stagingColumn.setBooleanColumnType();
            break;
        case "datetime":
            stagingColumn.setDateTimeColumnType();
            break;
        default:
            ai.log.logError('unknown xsd type ' + type, 'Update structure script appropriately');
            throw 'unknown xsd type: ' + type;
    }

}


//we leverage the report response and xsd datatype map to align with Adaptive data structures.
//add more lines to the map for additional datatype support.
xsdTypeMap = {
    //https://www.w3schools.com/xml/schema_dtypes_string.asp
    //"xs:ENTITIES": 'text',
    //"xs:ENTITY": 'text',
    //"xs:ID": 'text',                   //A string that represents the ID attribute in XML (only used with schema attributes)
    //"xs:IDREF": 'text',               //A string that represents the IDREF attribute in XML (only used with schema attributes)
    //"xs:IDREFS": 'text',
    //"xs:language": 'text',          //A string that contains a valid language id
    //"xs:Name": 'text',               //A string that contains a valid XML name
    //"xs:NCName": 'text',
    //"xs:NMTOKEN": 'text',           //A string that represents the NMTOKEN attribute in XML (only used with schema attributes)
    //"xs:NMTOKENS": 'text',
    //"xs:normalizedString": 'text',  //A string that does not contain line feeds, carriage returns, or tabs
    //"xs:QName": 'text',
    "xs:string": 'text',               //A string
    "xs:token": 'text',               //A string that does not contain line feeds, carriage returns, tabs, leading or trailing spaces, or multiple spaces

    //https://www.w3schools.com/xml/schema_dtypes_date.asp
    "xs:date": 'datetime',               //Defines a date value
    "xs:dateTime": 'datetime',        //Defines a date and time value
    "xs:duration": 'text',               //Defines a time interval
    "xs:gDay": 'text',                   //Defines a part of a date - the day (DD)
    "xs:gMonth": 'text',               //Defines a part of a date - the month (MM)
    "xs:gMonthDay": 'text',           //Defines a part of a date - the month and day (MM-DD)
    "xs:gYear": 'text',               //Defines a part of a date - the year (YYYY)
    "xs:gYearMonth": 'text',           //Defines a part of a date - the year and month (YYYY-MM)
    "xs:time": 'text',                   //Defines a time value

    //https://www.w3schools.com/xml/schema_dtypes_numeric.asp
    "xs:byte": 'int',                   //A signed 8-bit integer
    "xs:decimal": 'float',               //A decimal value
    "xs:int": 'int',                   //A signed 32-bit integer
    "xs:integer": 'int',               //An integer value
    "xs:long": 'text',                   //A signed 64-bit integer
    "xs:negativeInteger": 'int',       //An integer containing only negative values (..,-2,-1)
    "xs:nonNegativeInteger": 'int',   //An integer containing only non-negative values (0,1,2,..)
    "xs:nonPositiveInteger": 'int',   //An integer containing only non-positive values (..,-2,-1,0)
    "xs:positiveInteger": 'int',       //An integer containing only positive values (1,2,..)
    "xs:short": 'int',                   //A signed 16-bit integer
    "xs:unsignedLong": 'text',           //An unsigned 64-bit integer
    "xs:unsignedInt": 'int',           //An unsigned 32-bit integer
    "xs:unsignedShort": 'int',           //An unsigned 16-bit integer
    "xs:unsignedByte": 'int',           //An unsigned 8-bit integer

    //https://www.w3schools.com/xml/schema_dtypes_misc.asp
    //"xs:anyURI": 'text',
    //"xs:base64Binary": 'text',
    "xs:boolean": 'boolean',
    "xs:float": 'float',
    "xs:double": 'float'
    //"xs:hexBinary": 'text',
    //"xs:NOTATION": 'text',
};

/************************************************************************************************************

Deltek Vision Config and SOAP Methods

config = {
    //config.init(context) initializes all parameter values that don't need to be extracted programmatically.
    init: function(context) {
        var params = {};

        params.databaseDescription = this._databaseDescription(context);
        params.url = this._url(context);
        params.userPassword = this._userPassword(context);
        params.userName = this._userName(context);
        params.integratedSecurity = this._integratedSecurity(context);
        params.endDate = this._getEndDate(context, 'yyyymm');
        params.startDate = this._getStartDate(context, 'yyyymm');

        return params;
    },
    //returns the configured URL value.  Used by config.init(context)
    _url: function(context) {
        return 'https://woodardcurran.deltekfirst.com/woodardcurran/visionws.asmx';
    },

    //returns the user's password from a designer setting.  Used by config.init(context)
    _databaseDescription: function(context) {
        var dataSource = context.getDataSource(),
            databaseDescription;

        try {
            databaseDescription = dataSource.getSetting("databaseDescription").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "databaseDescription"', 'Please create Static Designer Setting "databaseDescription"');
            throw 'Couldn\'t find setting "databaseDescription"';
        }

        return databaseDescription;
    },
    
    //returns a setting for leveraging integrated security into the Deltek databases
    _integratedSecurity: function(context) {
        return 'N';
    },

    //returns the user's password from a designer setting.  Used by config.init(context)
    _userPassword: function(context) {
        var dataSource = context.getDataSource(),
            password;

        try {
            password = dataSource.getSetting("userPassword").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "userPassword"', 'Please create Static Designer Setting "userPassword"');
            throw 'Couldn\'t find setting "userPassword"';
        }

        return password;
    },

    //returns the userName from a designer setting.  Used by config.init(context)
    _userName: function(context) {
        var dataSource = context.getDataSource(),
            userName;

        try {
            userName = dataSource.getSetting("userName").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "userName"', 'Please create Static Designer Setting "userName"');
            throw 'Couldn\'t find setting "userName"';
        }

        return userName;
    },
    
    _getEndDate: function(context, format) {
        var dataSource, dateString;
        
        try {
            dataSource = context.getDataSource();
        }
        catch (ex) {
            ai.log.logError('Couldn\'t getDataSource in getEndDate "');
        }

        try {
            dateString = dataSource.getSetting("Period Range").getValue().getToPeriodStartDateTime().substring(0, 10);
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "Period Range"', 'Please create Parameterized Designer Setting "Period Range"');
            throw 'Couldn\'t find setting "Period Range"';
        }
        var date = new Date((dateString.substring(5) + '-' + dateString.substring(0, 4)).replace(/-/g, '/'));

        return getPeriodString(date, format);
    },

    _getStartDate: function(context, format) {
        var dataSource = context.getDataSource();
        var dateString;
        try {
            dateString = dataSource.getSetting("Period Range").getValue().getFromPeriodStartDateTime().substring(0, 10);
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "Period Range"', 'Please create Parameterized Designer Setting "Period Range"');
            throw 'Couldn\'t find setting "Period Range"';
        }
        var date = new Date((dateString.substring(5) + '-' + dateString.substring(0, 4)).replace(/-/g, '/'));

        return getPeriodString(date, format);
    }
    
};




/*******************************************************************************
 *
 * this is the 'methods' block.
 *
 * we call the following UltiPro methods in a given RaaS integration
 *  - /ValidateLogin
 * each have a specific request body, and response.  sometimes they provide inputs to other methods.
 * therefore, each method "class" builds requests and interrogates responses in its own way.
 *
*******************************************************************************/

//used to acquire a session token.
ValidateLogin = {
    call: function(params) {
        var body = this._body(params),
            headers = this._headers(),
            method = this._method(),
            url = this._url(params),
            res;

        res = ai.https.request(url, method, body, headers);

        if (res.getHttpCode() != '200') {
            ai.log.logError('Unsuccessful request to ValidateLogin service', 'Please check message contents / request structure and try again.');
            throw 'Unsuccessful request to ValidateLogin service';
        }
        return res;

    },
    interrogateResponse: function(xml, params) {
        var loginResult;
        
        try {
            loginResult = xml.getChildElement('ValidateLoginResponse').getChildElement('ValidateLoginResult').getText();
        } catch(e) {
            ai.log.logError('unable to acquire SessionID', '' + e);
            throw 'unable to acquire SessionID';
        }

        if(loginResult) {
            params.SessionID = loginResult;
            return {"Status" : true, "Parameters" : params};
        } else {
            return {"Status" : false};
        }
    },
    _body: function(params) {
        var req =
        '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:del="http://tempuri.org/Deltek.Vision.WebServiceAPI.Server/DeltekVisionOpenAPIWebService">' +
            '<soapenv:Header/>' +
            '<soapenv:Body>' +
                '<del:ValidateLogin>' +
                    generateLoginConnInfo(params) +
                '</del:ValidateLogin>' +
            '</soapenv:Body>' +
        '</soapenv:Envelope>';
        
        //ai.log.logVerbose('ValidateLogin request body -->', '' + req);
        
        return req;
    },
    _headers: function() {
        return {"Content-Type": "text/xml;charset=utf-8"};
    },
    _method: function() {
        return 'POST';
    },
    _url: function(params) {
        return params.url;
    }
};

/*******************************************************************************
The ExecuteStoredProcedureXML output will need to be inferred from the output from other code in this script.
*******************************************************************************/


//used to acquire stored procedure results.
ExecuteStoredProcedureXML = {
    call: function(params) {
        var body = this._body(params),
            headers = this._headers(),
            method = this._method(),
            url = this._url(params),
            res;
            
        //ai.log.logInfo('Time of request: ' + new Date());

        res = ai.https.request(url, method, body, headers);

        if (res.getHttpCode() != '200') {
            ai.log.logError('Unsuccessful request to ExecuteStoredProcedureXML service', 'Please check message contents / request structure and try again.');
            throw 'Unsuccessful request to ExecuteStoredProcedureXML service';
        }
        ai.log.logInfo('returning the response');
        return res;
    },
    
    // Note - params is not used in this method
    interrogateResponse: function(xml, params) {
        var execResult;
        
        try {
            execResult = xml.getChildElement('ExecuteStoredProcedureXMLResponse').getChildElement('ExecuteStoredProcedureXMLResult').getText();
        } catch(e) {
            ai.log.logError('unable to acquire ExecuteStoredProcedureXMLResult', '' + e);
            throw 'unable to acquire ExecuteStoredProcedureXMLResult';
        }

        return execResult;
    },
    _body: function(params) {
        
        ai.log.logInfo('start: ' + params.startDate, 'end: ' + params.endDate);
        
        var req =
        '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:del="http://tempuri.org/Deltek.Vision.WebServiceAPI.Server/DeltekVisionOpenAPIWebService">' +
            '<soapenv:Header/>' +
            '<soapenv:Body>' +
                '<del:ExecuteStoredProcedureXML>' +
                    generateExecuteSprocConnInfo(params) +
                    '<del:SprocName>AdaptiveInsightsActualsSelect</del:SprocName>' + 
                    generateExecuteSprocParameters(params) + 
                '</del:ExecuteStoredProcedureXML>' +
            '</soapenv:Body>' +
        '</soapenv:Envelope>';
        
        ai.log.logVerbose('ExecuteStoredProcedureXML request body -->', '' + req);
        
        return req;
    },
    _headers: function() {
        return {"Content-Type": "text/xml;charset=utf-8"};
    },
    _method: function() {
        return 'POST';
    },
    _url: function(params) {
        return params.url;
    }
};