Business Central.Export.Company

Used for exporting Business Central Company data. All other exports depend on this process. It does not truncate the table — it only adds new entries. The values this process inserts are used as identifiers between tables, so the primary key of this table cannot be changed freely, as doing so may require realigning other tables that reference it.

INFO

This process requires the Business Central Integration Library.

DANGER

The referenced script.library path must be aligned with the upload location of Business Central Integration Library in the file system

Process Code

script.library(script.variableGet("BusinessCentralIntegrationLibraryPath"));

function pre() {
    // This function is called once before the process is executed.
    // Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');
}

function begin() {
    // This function is called once at the start of the process
    script.log('process execution started.');


    let tableName = `business_central_company`.toLowerCase();

    let batch = null;
    let tableFields = null;
    let tableReady = false;

    // --- Ensure schema/table exist -------------------------------------
    if (!schemaExists(ds, schema)) {
        datasource.update(ds, `CREATE SCHEMA ${schema}`);
    }

    let apiurl = `${navURL}companies`;
    let result = NAV(apiurl, "GET", "");

    if (result == null) {
        console.log("Warning: API Request: " + apiurl);
        return;
    }

    let results = result.value;
    if (!results || results.length == 0) {
        return;
    }

    // First rows we see decide the table's shape.
    if (!tableReady) {
        let sampleRow = Object.assign({}, results[0]);

        let objFieldTypes = {
            "lastModifiedDateTime": "DATETIME",
            "business_central_company_id": "INT(11)"
        };

        if (tableExists(ds, schema, tableName)) {
            // table already exists - use its real column list/order rather
            // than re-deriving from the API response
            tableFields = getTableFields(ds, schema, tableName);
        } else {
            createTable(ds, schema, tableName, sampleRow, objFieldTypes);
            tableFields = Object.keys(sampleRow);
        }


        batch = datasource.createBatch(ds, `${schema}.${tableName}`, tableFields);
        tableReady = true;
    }

    for (let i = 0; i < results.length; i++) {
        let row = results[i];
        let business_central_company_id = i + 1
        // row["business_central_company_id"] = business_central_company_id;
        row.lastModifiedDateTime = convertToMySQLDatetime(row.lastModifiedDateTime);

        let rowData = [];
        for (let f = 0; f < tableFields.length; f++) {
            let value = row[tableFields[f]];
            if (typeof value == "object" && value != null) {
                value = JSON.stringify(value);
            }
            rowData.push(value === undefined ? null : value);
        }
        let checkSql = `SELECT * FROM ${schema}.${tableName} WHERE business_central_company_id = ?`
        let check = JSON.parse(datasource.select(ds, checkSql, [business_central_company_id]))
        if (check.length == 0) {
            console.log(`Inserting business_central_company_id: ${business_central_company_id}`)
            batch.insert(rowData);
        } else {
            console.log(`Skipping business_central_company_id: ${business_central_company_id}`)
        }
    }


    if (batch != null) {
        batch.flush();
    } else {
        console.log(`No data returned for endpoint "${endpoint}" - table "${tableName}" left untouched.`);
    }

}


function data(record) {
    // This function is called once for each line of data on the second cycle
    // Use this to build dimensions and push data into cubes
}

function end() {
    // This function is called once at the end of the process
    script.log('process execution finished.');
}