Reference: CCDS Script Example Acumatica

Test Connection.
Import Structure.
Import Data.

Acumatica Sample Script

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

Test Connection

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


function testConnection(context) {

    var loginResults = login(context);

    if (loginResults.httpCode == '204' && loginResults.cookies !== undefined) {
        return true;
    } else {
        ai.log.logError('error authorizing against the service: ' + loginResults.httpCode);
        return false;
    }

}

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

Import Structure

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

    var loginResults = login(context),
        body, headers, method, url;

    if (loginResults.httpCode == '204') {
        body = metadataService.body();
        headers = metadataService.headers(context);
        method = metadataService.method();
        url = metadataService.url(context);

    } else {
        ai.log.logError('Error authorizing against the login service: ' + loginResults.httpCode);
        throw 'Error authorizing against the login service: ' + loginResults.httpCode;
    }

    //ai.log.logVerbose('outbound request', method + ' ' + url + '\n' + JSON.stringify(headers) + '\n' + body);

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

    if (res.getHttpCode() != '200') {
        throw 'Unable to read service metadata from ' + url;
    }

    var dataSource = context.getDataSource(),
        builder = context.getStructureBuilder(),
        parser = ai.xml.createParser();
    var xmlDoc = parser.parse(res.getBody());
    var root = xmlDoc.getRootElement();

    var dataServices = root.getChildElement('edmx:DataServices');
    var schema = dataServices.getChildElement('Schema');

    for (var j = 0; j < schema.getChildElements().length; j++) {
        var currentTableXML = schema.getChildElementAt(j);

        //process table if name == 'EntityType', because Data Entity objects are tables in OData
        if (currentTableXML.getName() == 'EntityType') {

            //table creation through EntityType
            var table = builder.addTable(currentTableXML.getAttributeValue('Name'));
            table.setDisplayName(currentTableXML.getAttributeValue('Name'));
            var keyChain = {};
            if (currentTableXML.getChildElement('Key')) {
                keyChain = ODataConnector.getEntityKeyChain(currentTableXML.getChildElement('Key'));
            }
            for (var k = 0; k < currentTableXML.getChildElements().length; k++) {
                var currentColumnXML = currentTableXML.getChildElementAt(k);
                if (currentColumnXML.getName() == 'Property') {
                    addColumn(table, currentColumnXML, k, keyChain);
                }
            }
        }
    }
}

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

Import Data

******************/
function importData(context) {

    var dataSource = context.getDataSource(),
        rowset = context.getRowset();
    rowset.setSmartParsingEnabled(true);
    var tableId = rowset.getTableId(); 

    for (var x = 0; x < config.getCompanySet(context).length; x++) {
        var companyId = config.getCompanyName(context, x);

        if (importThisDataEntity(context, companyId, tableId) === false) {
            continue;
        }

        var loginResults = login(context, companyId), //verify auth for company code before
            body, headers, method, url;

        if (loginResults.httpCode == '204') {
            body = dataService.body(context);
            headers = dataService.headers(context);
            method = dataService.method(context);

        } else {
            throw 'error authorizing into ' + companyId + ' with the login service: ' + loginResults.httpCode;
        }


        //request initializations;
        var pageSize = 1000,
            offset = 0,
            count = 1000;

        while (count == pageSize) {
            //a descision gets made here: if there are defined filters, use them; otherwise, simply page.
            try {
                url = dataService.url(context, companyId) + ODataConnector.select(context) + '&' + ODataConnector.limit(pageSize) + '&' + ODataConnector.skip(offset) + '&' + ODataConnector.filter(customSettings[tableId].getFilters(context));
            } catch (e) {
                url = dataService.url(context, companyId) + ODataConnector.select(context) + '&' + ODataConnector.limit(pageSize) + '&' + ODataConnector.skip(offset);
                ai.log.logVerbose('' + e, 'url: ' + url);
            }

            if (offset === 0) {
                ai.log.logVerbose('url', '' + url);
            }

            //ai.log.logVerbose('outbound URL -->', '' + url);

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

            if (res.getHttpCode() != '200') {
                var throwMessage;

                try {
                    var errors = JSON.parse(res.getBody());
                    var stackTrace = errors['odata.error'].innererror.stacktrace;
                    var errorMessage = errors['odata.error'].innererror.message;
                    ai.log.logError('Message from Acumatica: ' + errorMessage, 'Stack trace from Acumatica: ' + stackTrace);
                    throwMessage = 'Message from Acumatica: ' + errorMessage;
                } catch (e) {
                    ai.log.logVerbose('' + e);
                    ai.log.logError('unable to retrieve data from data service -->', '' + method + ' ' + url + ' ' + body);
                    throwMessage = 'unable to retrieve data from data service';
                }
                throw throwMessage;
            }

            var contents = JSON.parse(res.getBody());

            for (var i = 0; i < contents.value.length; i++) {
                var row = contents.value[i];
                var cells = [];
                for (j = 0; j < rowset.getColumns().length; j++) {
                    var columnId = rowset.getColumns()[j].getId();
                    cells.push(row[columnId]);
                }
                rowset.addRow(cells);
            }
            offset = offset + pageSize;
            count = contents.value.length;
        }
    }

}

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

Preview Data

******************/
function previewData(context) {
    var rowset = context.getRowset();
    rowset.setSmartParsingEnabled(true);
    var tableId = rowset.getTableId();

    var loginResults = login(context),
        body, headers, method, url;

    if (loginResults.httpCode == '204') {
        body = dataService.body(context);
        headers = dataService.headers(context);
        method = dataService.method(context);

    } else {
        ai.log.logError('error authorizing against the metadata service: ' + loginResults.httpCode);
        throw 'error authorizing against the metadata service: ' + loginResults.httpCode;
    }

    try {
        url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.maxRows(context) + '&' + ODataConnector.skip(0) + '&' + ODataConnector.filter(customSettings[tableId].getFilters(context));
    } catch (e) {
        url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.maxRows(context) + '&' + ODataConnector.skip(0);
    }

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

    if (res.getHttpCode() != '200') {

        try {
            var errors = JSON.parse(res.getBody());
            var stackTrace = errors['odata.error'].innererror.stacktrace;
            var errorMessage = errors['odata.error'].innererror.message;
            ai.log.logError('Message from Acumatica: ' + errorMessage, 'Stack trace from Acumatica: ' + stackTrace);
        } catch (e) {
            ai.log.logVerbose('' + e);
            ai.log.logError('unable to retrieve data from data service -->', '' + method + ' ' + url + ' ' + body);
            throw 'unable to retrieve data from data service';
        }
    }

    var contents = JSON.parse(res.getBody());

    for (var i = 0; i < contents.value.length; i++) {
        var row = contents.value[i];
        var cells = [];
        for (j = 0; j < rowset.getColumns().length; j++) {
            var columnId = rowset.getColumns()[j].getId();
            cells.push(row[columnId]);
        }
        rowset.addRow(cells);
    }
}


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

Supporting Functions

******************/
function addColumn(table, column, index, keyChain) {

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

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

    stagingColumn.setDisplayOrder(index);

    if (config.createKeys === true) {
        stagingColumn.setIncludeByDefault(keyChain[column.getAttributeValue('Name')] || false);
        stagingColumn.setIncludeInKey(keyChain[column.getAttributeValue('Name')] || false);
        stagingColumn.setMandatoryForImports(keyChain[column.getAttributeValue('Name')] || false);
    }

    switch (ODataConnector.typeMap[type]) {
        case "text":
            if (keyChain[column.getAttributeValue('Name')] === true && config.createKeys === true) {
                stagingColumn.setTextColumnType(100);
            } else {
                stagingColumn.setTextColumnType(1000);
            }
            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 OData type ' + column.type, 'Update structure script appropriately');
            throw 'unknown OData type: ' + column.type;
    }

}


function base64Encode(text) {

    if (/([^\u0000-\u00ff])/.test(text)) {
        throw new Error("Can't base64 encode non-ASCII characters.");
    }
    var digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/",
        i = 0,
        cur, prev, byteNum,
        result = [];

    while (i < text.length) {

        cur = text.charCodeAt(i);
        byteNum = i % 3;

        switch (byteNum) {
            case 0: //first byte
                result.push(digits.charAt(cur >> 2));
                break;

            case 1: //second byte
                result.push(digits.charAt((prev & 3) << 4 | (cur >> 4)));
                break;

            case 2: //third byte
                result.push(digits.charAt((prev & 0x0f) << 2 | (cur >> 6)));
                result.push(digits.charAt(cur & 0x3f));
                break;
        }

        prev = cur;
        i++;
    }

    if (byteNum === 0) {
        result.push(digits.charAt((prev & 3) << 4));
        result.push("==");
    } else if (byteNum == 1) {
        result.push(digits.charAt((prev & 0x0f) << 2));
        result.push("=");
    }

    return result.join("");
}


function login(context) {

    var body = loginService.body(context),
        headers = loginService.headers(),
        method = loginService.method(),
        url = loginService.url(context),
        cookieJar;

    //ai.log.logVerbose('outbound request', method + ' ' + url + '\n' + JSON.stringify(headers) + '\n' + body);

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

    if (res.getHttpCode() == '204') {
        var resHeaders = res.getHeaders();

        //ai.log.logVerbose('acquired session cookie -->', '' + resHeaders['Set-Cookie']);
        cookieJar = resHeaders['Set-Cookie'];
    } else {
        ai.log.logError('unable to authorize against ' + url, 'check username, password, and company code then try again.');
        throw 'unable to authorize against ' + url;
    }

    return {
        httpCode: res.getHttpCode(),
        cookies: cookieJar
    };

}

function importThisDataEntity(context, companyId, tableId){
    var confirmation = false;
    for(var i = 0; i < customSettings[tableId].companyImportList.length; i++) {
        if(companyId == customSettings[tableId].companyImportList[i]) {
            confirmation = true;
            break;
        }
    }
    return confirmation;
}

/************************************************************************************************************Acumatica Settings
config = {
    //simply calls config.service(context) to get the Acumatica Endpoint Base, then builds a base URL for acumatica.
    base: function(context) {
        return 'https://' + this.service(context) + '.acumatica.com';
    },

    //createKeys, for Acumatica's OData API, should generally be set to false.
    //The OData API pulls data from Generic Inquiries in Acumatica, which may not respect keys across data entities.
    createKeys: false,

    //returns a trimmed company name value after splitting the company set by a delimiter.
    getCompanyName: function(context, index, delimiter) {
        return this.getCompanySet(context, delimiter)[index].trim();
    },


    //takes context and a delimiter, returns an array.
    getCompanySet: function(context, delimiter) {
        var companies;

        //quick assumption in case delimiter wasn't passed in...
        if (!delimiter) {
            delimiter = ';';
        }

        //attempt to get Acumatica Companies list.
        try {
            companies = context.getDataSource().getSetting("Acumatica Companies").getValue().split(delimiter);
        } catch (e) {
            ai.log.logError('Couldn\'t find Static Designer Setting "Acumatica Companies"', 'Please create Static Text Setting "Acumatica Companies" and try again');
            throw 'Please create Static Text Setting "Acumatica Companies" and try again';
        }
        return companies;
    },


    getEndDate: function(context, format) {
        var dataSource = context.getDataSource();
        var dateString;
        try {
            dateString = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().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 this.getPeriodString(date, format);
    },


    getPeriodString: function(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;
    },


    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 this.getPeriodString(date, format);
    },


    password: function(context) {
        var dataSource = context.getDataSource();
        try {
            return dataSource.getSetting("Acumatica Password").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "Acumatica Password"', 'Please create Static Designer Setting "Acumatica Password"');
            throw 'Couldn\'t find setting "Acumatica Password"';
        }
    },


    service: function(context) {
        var serviceBase;

        //attempt to get Acumatica Endpoint Base setting.  this is what users see when they've logged into Acumatica: 'https://{{Acumatica Endpoint Base}}.acumatica.com'
        try {
            serviceBase = context.getDataSource().getSetting("Acumatica Endpoint Base").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find Static Designer Setting "Acumatica Endpoint Base"', 'Please create Static Text Setting "Acumatica Endpoint Base" and try again');
            throw 'Please create Static Text Setting "Acumatica Endpoint Base" and try again';
        }
        return serviceBase;
    },


    username: function(context) {
        var dataSource = context.getDataSource();
        try {
            return dataSource.getSetting("Acumatica Username").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "Acumatica Username"', 'Please create Static Designer Setting "Acumatica Username"');
            throw 'Couldn\'t find setting "Acumatica Username"';
        }
    }

};

//Contains a system's OData API specifics.
loginService = {
    body: function(context) {
        var req = {
            name: config.username(context),
            password: config.password(context),
            company: ''
        };

        return JSON.stringify(req);
    },
    headers: function() {
        return {
            "Content-Type": "application/json"
        };
    },
    method: function() {
        return 'POST';
    },
    url: function(context) {
        return config.base(context) + '/entity/auth/login';
    }
};

//Contains a system's OData API specifics.
metadataService = {
    body: function() {
        return null;
    },
    headers: function(context) {
        return {"Authorization": 'Basic ' + base64Encode(config.username(context) + ':' + config.password(context))};
    },
    method: function() {
        return 'GET';
    },
    url: function(context, companyId) {

        if (!companyId) {
            companyId = config.getCompanyName(context, 0);
        }

        return config.base(context) + '/odata/' + companyId + '/$metadata';
    }

};

//Contains a system's OData API specifics.
dataService = {
    body: function(context) {
        return null;
    },

    headers: function(context) {
        var obj = {};
        obj["Authorization"] = 'Basic ' + base64Encode(config.username(context) + ':' + config.password(context));
        obj["Content-Type"] = 'application/json';
        obj["Accept"] = 'application/json';

        return obj;
    },

    method: function(context) {
        return 'GET';
    },

    url: function(context, companyId) {

        if (!companyId) {
            companyId = config.getCompanyName(context, 0);
        }

        return config.base(context) + '/odata/' + companyId + '/' + context.getRowset().getTableId() + '?';

    }
};

/*******************************************************************************
*
* This integration should "work" without these settings, but this file is designed
* to support tailoring and optimization for specific customer requirements.
*
* The general design here:
* customSettings.{{Data Entity}} -- intended to let folks create filters for a given {{Data Entity}}
* customSettings.{{Data Entity}}.filters: [...] -- an array of relevant filters to apply to {{Data Entity}}
* customSettings.{{Data Entity}}.getFilters(...) -- a function that returns an array of filters created in customSettings.{{Data Entity}}.filters: [...]
*
* Each filter[x] should have three elements:
*   - getColName()
*   - getVal(context)
*   - getOperator()
*
* An importData() flow first attempts to call customSettings.{{Data Entity}}.getFilters(...),
* which then calls each array element in set customSettings.{{Data Entity}}.filters
* to build an array of filters for the ODataConnector used in importData().
*
* The existence of customSettings is required, even if it's empty {}.
*******************************************************************************/


customSettings = {
    GLHistory: {
        companyImportList: [''],
        filters: [{
            getColName: function() {
                return 'FinancialPeriod';
            },
            getVal: function(context) {
                return '\'' + config.getStartDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '>=';
                return ODataConnector.operationMap(operator);
            }
        }, {
            getColName: function() {
                return 'FinancialPeriod';
            },
            getVal: function(context) {
                return '\'' + config.getEndDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '<';
                return ODataConnector.operationMap(operator);
            }
        }],
        getFilters: function(context) {
            var f = [];
            for (var i = 0; i < this.filters.length; i++) {
                f.push(this.filters[i].getColName() + this.filters[i].getOperator() + this.filters[i].getVal(context));
            }
            return f;
        }
    },
    GLTranLines: {
        companyImportList: [‘’],
        filters: [{
            getColName: function() {
                return 'Period';
            },
            getVal: function(context) {
                return '\'' + config.getStartDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '>=';
                return ODataConnector.operationMap(operator);
            }
        }, {
            getColName: function() {
                return 'Period';
            },
            getVal: function(context) {
                return '\'' + config.getEndDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '<';
                return ODataConnector.operationMap(operator);
            }
        }],
        getFilters: function(context) {
            var f = [];
            for (var i = 0; i < this.filters.length; i++) {
                f.push(this.filters[i].getColName() + this.filters[i].getOperator() + this.filters[i].getVal(context));
            }
            return f;
        }

    },
    "AR Transactions": {
        companyImportList: [
            'cofense inc'
            ],
        filters: [{
            getColName: function() {
                return 'Period';
            },
            getVal: function(context) {
                return '\'' + config.getStartDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '>=';
                return ODataConnector.operationMap(operator);
            }
        }, {
            getColName: function() {
                return 'Period';
            },
            getVal: function(context) {
                return '\'' + config.getEndDate(context, 'yyyymm') + '\'';
            },
            getOperator: function() {
                var operator = '<';
                return ODataConnector.operationMap(operator);
            }
        }],
        getFilters: function(context) {
            var f = [];
            for (var i = 0; i < this.filters.length; i++) {
                f.push(this.filters[i].getColName() + this.filters[i].getOperator() + this.filters[i].getVal(context));
            }
            return f;
        }
    },
    "ExchangeRates": {
        companyImportList: [‘’],
    }
};

/************************************************************************************************************
ODataConnector = {

    getEntityKeyChain: function(keys) {
        var keyChain = {};
        for (var k = 0; k < keys.getChildElements().length; k++) {
            var key = keys.getChildElementAt(k).getAttributeValue('Name');
            keyChain[key] = true;
        }
        return keyChain;
    },

    //known OData datatypes.
    typeMap: {
        "Edm.Binary": 'int',
        "Edm.Boolean": 'boolean',
        "Edm.Byte": 'int',
        "Edm.Date": 'datetime',
        "Edm.DateTime": 'datetime',
        "Edm.DateTimeOffset": 'datetime',
        "Edm.Decimal": 'float',
        "Edm.Double": 'float',
        "Edm.Duration": 'text',
        "Edm.Guid": 'text',
        "Edm.Int16": 'int',
        "Edm.Int32": 'int',
        "Edm.Int64": 'int', //possibly change to text
        "Edm.SByte": 'int',
        "Edm.Single": 'float',
        "Edm.Stream": 'text',
        "Edm.String": 'text',

        //experimental area
        "Edm.Geography": 'text',
        "Edm.GeographyPoint": 'text',
        "Edm.GeographyLineString": 'text',
        "Edm.GeographyPolygon": 'text',
        "Edm.GeographyMultiPoint": 'text',
        "Edm.GeographyMultiLineString": 'text',
        "Edm.GeographyMultiPolygon": 'text',
        "Edm.GeographyCollection": 'text',
        "Edm.Geometry": 'text',
        "Edm.GeometryPoint": 'text',
        "Edm.GeometryLineString": 'text',
        "Edm.GeometryPolygon": 'text',
        "Edm.GeometryMultiPoint": 'text',
        "Edm.GeometryMultiLineString": 'text',
        "Edm.GeometryMultiPolygon": 'text',
        "Edm.GeometryCollection": 'text'
    },

    /***************************************************************************
    *
    * OData's Data Services interface needs to help make decisions on at least
    * a few points:
    *  - $filter: which records to query, the 'where' clause
    *  - $top: specify a maximum number of records to query at any given time
    *  - $select: a set of fields to query, just like a 'select' clause in SQL
    *  - $skip: the quantity of records to offset in a given query, for paging.
    *
    ***************************************************************************/

    //locking in design...
    filter: function(set) {
        var f = '$filter=';
        for (var i = 0; i < set.length; i++) {
            if (i == set.length - 1) {
                f += set[i];
            } else {
                f += set[i] + ' and ';
            }
        }
        return f;
    },

    //designed specifically for the importData function's finite, but unknown e.
    limit: function(pageSize) {
        return '$top=' + pageSize;
    },

    //designed specifically for previewData
    maxRows: function(context) {
        return '$top=' + context.getRowset().getMaxRows();
    },

    //for either importData or previewData.  Looks at columns selected in the staging table and builds a $select clause.
    select: function(context) {

        var columnSet = context.getRowset().getColumns(),
            select = '$select=';

        for(var i = 0; i < columnSet.length; i++) {
            if (i == columnSet.length - 1) {
                select += columnSet[i].getId();
            } else {
                select += columnSet[i].getId() + ',';
            }
        }

        return select;
    },

    //only used in importData, for paging.
    skip: function(offset) {
        return '$skip=' + offset;
    },

    operationMap: function(op){
        var operation;
        switch (op) {
            case '=':
            case '==':
            case 'eq':
            case ' eq ':
                operation = ' eq ';
                break;
            case '<>':
            case '!=':
            case 'ne':
            case ' ne ':
                operation = ' ne ';
                break;
            case '>':
            case 'gt':
            case ' gt ':
                operation = ' gt ';
                break;
            case '>=':
            case 'ge':
            case ' ge ':
                operation = ' ge ';
                break;
            case '<':
            case 'lt':
            case ' lt ':
                operation = ' lt ';
                break;
            case '<=':
            case 'le':
            case ' le ':
                operation = ' le ';
                break;
            default:
                operation = '';
                ai.log.logError('No matched operation for ' + op + '.', 'Please extend available OData operator options and try again.');
                throw 'Please extend available OData operator options and try again.';
        }
    return operation;
    }
};