Referentie: CCDS-scriptvoorbeeld Microsoft Dynamics 365

Verbinding testen.
Structuur importeren.
Gegevens importeren.

Microsoft Dynamics 365-voorbeeldscript

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

Test Connection

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

// The script utilizes OAuth2.0 to connect and retrieve the files from D365 OData service. Create OAuth 2.0 credential and associate it with the CCDS data source.

// The connection test and authorization both happen on the OAuth 2.0 Credential, when the credential is associated with the data source, the environment testing happens from the framework.

function testConnection(context) {
    return true;
}

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

Config and OData Connector

******************/
config = {
    service : function(context) {
        var dataSource = context.getDataSource();
        var service;
        try {
            service = dataSource.getSetting("D365 Environment URL").getValue();
        } catch (e) {
            ai.log.logError('Couldn\'t find setting "D365 Environment URL"', 'Please create Parameterized Designer Setting "D365 Tenant"');
            throw 'Couldn\'t find setting "D365 Environment URL"';
        }

        return service;
    },

    base : function(context) {
        return this.service(context);
    },

    //defaulted to false, may be switched to true to create key columns in the staging area during importStructure.
    createKeys: false,

    //pulls a Period Range setting value from the Data Source, and creates a js date from it.
    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);
    },

    //after a date is created from getStartDate or getEndDate, this function creates a string of the date, given a format specified.
    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;
            case 'yyyy-mm-dd':
                var year, month, day;

                year = '' + date.getFullYear();

                if (date.getMonth() + 1 < 10) {
                    month = '0' + (date.getMonth() + 1);
                } else {
                    month = '' + (date.getMonth() + 1);
                }

                if (date.getDate() < 10) {
                    day = '0' + date.getDate();
                } else {
                    day = '' + date.getDate();
                }

                period = year + '-' + month + '-' + day;
                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;
    },

    //pulls a Period Range setting value from the Data Source, and creates a js date from it.
    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);
    },
};

//service that returns a JSON formatted list of Data Entities.
authorizedMetadataService = {
    url : function(context){
        return config.base(context) + '/Metadata/DataEntities';
    },
    method : function() {
        return 'GET';
    },
    body : function() {
        return null;
    },
    headers : function() {
        var obj = {"Accept": 'application/json'};
        return obj;
    }
};

//Service to generate an XML of the Data Entity schema for a given environment.
metadataService = {
    url : function(context){
        return config.base(context) + '/data/$metadata';
    },
    method : function() {
        return 'GET';
    },
    body : function() {
        return null;
    },
    headers : function() {
        var obj = {"Accept": 'application/xml'};
        return obj;
    }
};

//generates the base data service request.
dataService = {
    body : function(context) {
        return null;
    },
    headers : function(context) {
        var obj = {};

        obj["Accept"] = 'application/json';

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

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

    },
    maxRows : function(context) {
        return '$top=' + context.getRowset().getMaxRows();
    }
};

//used for OData integrations.
//  1) it maps OData datatypes to CCDS' supported datatypes.
//  2) helps align valid operator syntax
//  3) creates various components of the OData URL syntax.
ODataConnector = {
    //Data Entities have Key column sets.  This function takes the XML form, and converts it to a js property-value object.
    getEntityKeyChain: function(keys) {
        var keyChain = {};
        try {
            for (var k = 0; k < keys.getChildElements().length; k++) {
                var key = keys.getChildElementAt(k).getAttributeValue('Name');
                keyChain[key] = true;
            }
        } catch (e) {
            ai.log.logVerbose('no keys detected');
        }
        return keyChain;
    },

    //Standard OData datatypes.
    typeMap: {
        "Edm.Binary": 'int',
        "Edm.Boolean": 'boolean',
        "Edm.Byte": 'int',
        "Edm.Date": 'datetime',
        "Edm.DateTime": 'datetime',

        //experimental area
        "Edm.Geography": 'text',
        "Edm.GeographyPoint": 'text',
        "Edm.GeographyLineString": 'text'
    },

    //takes a set of one or more custom defined filters, then builds the OData filter syntax '$filter='
    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;
    },

    //'$top=' is used for defining a page size, with importData.
    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 incrementing the page offset.
    skip: function(offset) {
        return '$skip=' + offset;
    },

    //this function takes an operator string as input, then attempts to match it to an appropriate OData operator.
    operationMap: function(op){
        var operation;
        // switch-case using JavaScript fall-through.
        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;
    }
};

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

Import Structure

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

function importStructure(context) {

    function addColumn(table, column, index) {

        let stagingColumn = table.addColumn(column.name);
        stagingColumn.setDisplayName(column.name);

        let type = column.type;

        stagingColumn.setDisplayOrder(index);

        switch (ODataTypeMap[type]) {
            case "text":
                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:
                stagingColumn.setTextColumnType(250);
        }
    }

    const ODataTypeMap = {
        "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": 'text', //modified to text as AI doesn't support 64-bit integers.
        "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'
    };

    // Step 1: Get the structure builder.
    const builder = context.getStructureBuilder();


    const namespace = 'Microsoft.Dynamics.DataEntities'; //D365 OData namespace -- probably needs to come from onOpenTag, where tag.name = 'Schema', then tag.attributes.Namespace
    const entityContainer = {}; //big placeholder for all entity sets and entity types / properties.
    let properties = [];


    let parser = null;
    const callbacks = {};


    let entityType;


    callbacks.onOpenTag = function(tag) {

        if (tag.name == 'EntityType') {

            //construct entity type for container
            let typeName = tag.attributes.Name; //find entity type name
            entityType = namespace + '.' + typeName; //fully qualified namespace construction of the entity type

            entityContainer[entityType] = {}; //new object built from the fully qualified name... helps later.

        } else if (tag.name == 'Property') {

            //build a property - to be used in structure as a column on a table.
            let property = {};
            property.name = tag.attributes.Name;
            property.type = tag.attributes.Type;

            properties.push(property);

        } else if (tag.name == 'EntitySet') {

            let entitySet = tag.attributes.Name;
            let entityTypeName = tag.attributes.EntityType; //while the result is the same, this must be a different declaration from entityType above for onCloseTag purposes.

            //develop the association between an entity type and its proper name.
            entityContainer[entityTypeName].name = entitySet;
        }
    };


    callbacks.onCloseTag = function(tagname) {

        if (tagname == 'EntityType') {
            entityContainer[entityType].columns = properties; //assign properties array to the EntityType
            properties = []; //reset the array for the next EntityType
        }
    };


    const url = context.getDataSource().getSetting('D365 Environment URL').getValue() + '/data/$metadata';
    const method = 'GET';
    const body = '';
    const headers = {'Accept': 'application/xml'};


    parser = ai.xml.sax.createFromUrl(url, method, body, headers, callbacks);
    parser.readToEnd();

    for (let i in entityContainer) {

        let table = builder.addTable(entityContainer[i].name);
        table.setDisplayName(entityContainer[i].name);

        for (let j in entityContainer[i].columns) {
            addColumn(table, entityContainer[i].columns[j], j);
        }
    }
}



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

Preview Data

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

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

    var body, headers, method, url;

    //get dataService specific base values
    body = dataService.body(context);
    headers = dataService.headers(context);
    method = dataService.method(context);

    //when there's a custom setting defined, use it.  otherwise, simply iterate over pages in the service response.
    try {
        url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.maxRows(context) + '&' + ODataConnector.skip(0) + '&' + ODataConnector.filter(customSettings[tableId].getFilters(context)) + '&cross-company=true';
    } catch (e) {
        url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.maxRows(context) + '&' + ODataConnector.skip(0)  + '&cross-company=true';
    }

    //validate URL in logs before it goes out.
    //ai.log.logVerbose('url -->', '' + url);

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

    //determine if we should proceed.
    if (res.getHttpCode() != '200') {
        ai.log.logError('unable to retrieve data from data service -->', '' + url + '; responseBody ' + res.getBody());
        throw 'unable to retrieve data from data service';
    }

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

    //the data points to load come from the response's 'value' object.  so we iterate over all elements in contents.value
    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();

            //D365 generates 64-bit integers in some columns.  since AI's setSmartParsingEnabled doesn't convert int to string, we simply blanket all integers as text, the allow setSmartParsingEnabled to create a 32-bit int when applicable.
            if (Number.isInteger(row[columnId]) === true) {
                cells.push('' + row[columnId]);
            } else {
                cells.push(row[columnId]);
            }
        }
        rowset.addRow(cells);
    }
}
customSettings = {
        //data entity to apply filtering against
    LedgerJournalLines: {
        filters: [{
            getColName: function() {
                return 'TransDate';
            },
            getVal: function(context) {
                return config.getStartDate(context, 'yyyy-mm-dd');
            },
            getOperator: function() {
                var operator = '>=';
                return ODataConnector.operationMap(operator);
            }
        }, {
            getColName: function() {
                return 'TransDate';
            },
            getVal: function(context) {
                return config.getEndDate(context, 'yyyy-mm-dd');
            },
            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;
        }
    },
           //data entity to apply filtering against
    ExchangeRates: {
        filters: [{
            getColName: function() {
                return 'StartDate';
            },
            getVal: function(context) {
                return config.getStartDate(context, 'yyyy-mm-dd');
            },
            getOperator: function() {
                var operator = '>=';
                return ODataConnector.operationMap(operator);
            }
        }, {
            getColName: function() {
                return 'StartDate';
            },
            getVal: function(context) {
                return config.getEndDate(context, 'yyyy-mm-dd');
            },
            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;
        }
    }
};

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

Import Data

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

function importData(context) {
    const rowset = context.getRowset();
    rowset.setSmartParsingEnabled(true);
    const tableId = rowset.getTableId();


    //get dataService specific base values
    const body = dataService.body(context);
    const headers = dataService.headers(context);
    const method = dataService.method(context);
    let url = '';

    //request initializations;
    const pageSize = 10000;
    let offset = 0;
    let count = 10000;
    let res;
    let contents;

    //pageSize == count by design, but the loop changes count after looking at the response size.
    while (count == pageSize) {

        //when there's a custom setting defined, use it.  otherwise, simply iterate over pages in the service response.
        try {
            url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.limit(pageSize) + '&' + ODataConnector.skip(offset) + '&' + ODataConnector.filter(customSettings[tableId].getFilters(context))  + '&cross-company=true';
        } catch (e) {
            url = dataService.url(context) + ODataConnector.select(context) + '&' + ODataConnector.limit(pageSize) + '&' + ODataConnector.skip(offset)  + '&cross-company=true';
        }

        //validate URL in logs before it goes out.
        ai.log.logVerbose('url -->', '' + url);

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

        if (res.getHttpCode() != '200') {
            ai.log.logError('unable to retrieve data from data service -->', 'URL: ' + url);
            ai.log.logError('unable to retrieve data from data service -->', 'HTTP Code: ' + res.getHttpCode());
            ai.log.logError('unable to retrieve data from data service -->', 'Response: ' + res.getBody());
            throw 'unable to retrieve data from data service';
        }

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

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

                //D365 generates 64-bit integers in some columns.  since AI's setSmartParsingEnabled doesn't convert int to string, we simply blanket all integers as text, the allow setSmartParsingEnabled to create a 32-bit int when applicable.
                if (Number.isInteger(row[columnId]) === true) {
                    cells.push('' + row[columnId]);
                } else if(columnId.indexOf('dataAreaId') > -1 || columnId.indexOf('DataAreaId') > -1) {
                    //AI has case-sensitive join requirements, so we force one very common "key" column to always be one case.
                    cells.push(row[columnId].toUpperCase());
                }
                else {
                    cells.push(row[columnId]);
                }
            }
            rowset.addRow(cells);
        }

        //increment page to read, change count to potentially break loop.
        offset = offset + pageSize;
        count = contents.value.length;
    }
}