Business Central Integration Library

INFO

This library is required to use the Business Central Processes.

To download this library, click here

Parts of this library will need to be configured based on your Business Central environment.

This includes the following:

• authObj.clientId
• authObj.clientSecret
• authObj.tenantId
• authObj.companyId
• authObj.environment

Library Code

var authObj = {
    "clientId": "XXXXX",
    "clientSecret": "XXXXX",
    "tenantId": "XXXXX",
    "companyId": "XXXXX",
    "environment": "XXXXX"
}

//access token URL
const clientId = authObj["clientId"]
const clientSecret = authObj["clientSecret"]
const company_id = authObj["companyId"]
const tenantId = authObj["tenantId"]
const environment = authObj["environment"]
const scope = 'https://api.businesscentral.dynamics.com/.default';

const navURL = `https://api.businesscentral.dynamics.com/v2.0/${tenantId}/${environment}/api/v2.0/`;
const tokenURL = `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`;

const ds = "Internal Datastore";
const schema = "business_central"
var sql = "";
const companyTable = "business_central_company"

var accessToken = null;
var accessTokenExpiresIn = null;
var accessTokenExpiredAt = null;

/*
Returns true if successful.
*/
function getAccessToken() {
    http.disableHostNameValidation();

    let data = `grant_type=client_credentials&client_id=${encodeURIComponent(clientId)}&client_secret=${encodeURIComponent(clientSecret)}&scope=${encodeURIComponent(scope)}`;

    http.headersAdd('content-type', 'application/x-www-form-urlencoded');

    let res = http.raw(tokenURL, "POST", data);
    let resObj = JSON.parse(res);


    if (resObj.code == 200) {
        let bodyObj = JSON.parse(resObj.body);

        accessToken = bodyObj.access_token;
        accessTokenExpiresIn = bodyObj.expires_in;
        accessTokenExpiredAt = new Date();
        accessTokenExpiredAt.setSeconds(accessTokenExpiredAt.getSeconds() + parseInt(accessTokenExpiresIn));

        return true;
    }
    return false;
}

function NAV(url, method, data) {
    if (accessToken == null) {
        if (!getAccessToken()) {
            console.log("Authentication Failed with NAV");
            return null;
        }
    }

    http.headersAdd("Authorization", "Bearer " + accessToken);
    http.disableHostNameValidation();

    let res = http.raw(url, method, data);
    let resObj = JSON.parse(res);

    if (resObj.code == 200) {
        let bodyObj = JSON.parse(resObj.body);
        return bodyObj;
    } else {
        console.log("NAV Request Failed.");
        console.log(resObj);
        return null;
    }
}

function getCompanies() {
    let records = JSON.parse(datasource.select(ds, `SELECT business_central_company_id,id FROM ${schema}.${companyTable}`, []));
    return records;
}


// ---------------- Table helpers ----------------

let fieldMapping = { //used in conjunction with the fields arg to create a table
    textDefaultField: "VARCHAR(512)",
    dateField: "DATE",
    dateTime: "DATETIME",
    int: "INT(11)",
    decimal: "decimal(10,2)"
};
var defaultDataType = "VARCHAR(512)";

function createTable(ds, schema, tableName, fields = {}, fieldsDataType = {}) {
    let array = [];
    if (tableExists(ds, schema, tableName)) {
        console.log("Table Already Exists");
        return;
    }
    let primaryKey = `\`${tableName}_id\``;
    let createStatement = `CREATE TABLE \`${schema}\`.\`${tableName}\` (
        ${primaryKey} int(11) NOT NULL AUTO_INCREMENT,`;
    for (let property in fields) {
        array.push(property);
        let dataType = fieldsDataType.hasOwnProperty(property) ? fieldsDataType[property] : defaultDataType;
        createStatement += `\`${property}\` ${dataType} DEFAULT NULL, `;
    }
    createStatement += `  PRIMARY KEY (${primaryKey})) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci`;
    if (array.length > 0) {
        console.log(`Creating Table \`${schema}\`.\`${tableName}\``, createStatement);
        datasource.update(ds, createStatement);
    } else {
        console.log("Failed to create table");
    }
}

function tableExists(ds, schema, tableName) {
    let sql = `SELECT * FROM information_schema.tables WHERE table_schema = ? AND table_name = ?`;
    let result = JSON.parse(datasource.select(ds, sql, [schema, tableName]));
    return result.length > 0;
}

function getTableFields(ds, schema, tableName) {
    // returns the table's actual column names, in column order, excluding
    // the auto-increment primary key column added by createTable()
    let sql = `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_schema = ? AND table_name = ? AND COLUMN_NAME != ? ORDER BY ORDINAL_POSITION`;
    let result = JSON.parse(datasource.select(ds, sql, [schema, tableName, `${tableName}_id`]));
    return result.map(row => row.column_name);
}


function dropTable(ds, schema, tableName) {
    let sql = `DROP TABLE \`${schema}\`.\`${tableName}\``;
    console.log(sql);
    datasource.update(ds, sql);
}

function convertToMySQLDatetime(value) {
    // NOTE: this was referenced but not defined in the original script -
    // implemented here on the assumption it converts an ISO datetime
    // string from BC into 'YYYY-MM-DD HH:MM:SS' for MySQL. Adjust if your
    // original implementation did something different (e.g. timezone shift).
    if (!value) return null;
    let d = new Date(value);
    if (isNaN(d.getTime())) return null;
    return d.toISOString().slice(0, 19).replace("T", " ");
}

function schemaExists(ds, schema) {
    const sql = `SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`;
    const result = JSON.parse(datasource.select(ds, sql, [schema]));
    return result.length > 0;
}