Business Central.Export.generalLedgerEntries

Used for exporting dimension generalLedgerEntries and the expanded dimensionsetlines into seperate tables. Loads for one month at a time, completing a complete delete, and then reloading all data from Business Central.

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"));

var dateFilterField = "postingDate"

function pre() {
    // This function is called once before the process is executed.
    // Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');
    script.prompt("Load Year", "loadYear", "2023");
    script.prompt("Load Month", "loadMonth", "2");
}

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

    const endpoint = "generalLedgerEntries";
    const mainTableName = `business_central_${endpoint}`.toLowerCase();
    const dimTableName = `${mainTableName}_dimensionsetlines`;

    const { lastDayOfPriorMonth, firstDayOfMonth, firstDayOfNextMonth } = getAdjacentMonths(loadYear, loadMonth);

    let companies = getCompanies();

    // Per-table state, keyed by the actual table name.
    let tables = {
        [mainTableName]: { sampleRow: null, batch: null, tableFields: null },
        [dimTableName]: { sampleRow: null, batch: null, tableFields: null }
    };

    let tableReady = false;

    function flushAllBatches() {
        for (let t in tables) {
            if (tables[t].batch != null) tables[t].batch.flush();
        }
    }

    function insertRow(table, row) {
        let rowData = [];
        for (let f = 0; f < table.tableFields.length; f++) {
            let value = row[table.tableFields[f]];
            if (typeof value === "object" && value != null) {
                value = JSON.stringify(value);
            }
            rowData.push(value === undefined ? null : value);
        }
        table.batch.insert(rowData);
    }

    // Inserts one GL entry row into the main table, and one row per
    // dimension set line into the dimension lines table. dimensionSetLines
    // is an array of dimension line objects on the source row, so it's
    // normalized here into one child row per line rather than stored as
    // a single blob on the parent row.
    function processRow(row, business_central_company_id) {
        row["business_central_company_id"] = business_central_company_id;
        row.lastModifiedDateTime = convertToMySQLDatetime(row.lastModifiedDateTime);
        insertRow(tables[mainTableName], row);

        let dimLines = row.dimensionSetLines || [];
        for (let d = 0; d < dimLines.length; d++) {
            let dimRow = Object.assign({}, dimLines[d]);
            dimRow.postingDate = row.postingDate;
            dimRow.generalLedgerEntries_id = row.id;
            dimRow.business_central_company_id = business_central_company_id;
            insertRow(tables[dimTableName], dimRow);
        }
    }

    for (let k = 0; k < companies.length; k++) {
        if (script.IsCancelled()) {
            flushAllBatches();
            return;
        }

        let coId = companies[k].id;
        let coName = companies[k].company_name;
        let business_central_company_id = companies[k].business_central_company_id;

        let apiurl = `${navURL}companies(${coId})/${endpoint}?\$filter=${dateFilterField}%20ge%20${firstDayOfMonth.replace(/ /gi, "%20")}%20and%20${dateFilterField}%20lt%20${firstDayOfNextMonth.replace(/ /gi, "%20")}\&$expand=dimensionSetLines`;
        let result = NAV(apiurl, "GET", "");

        if (result == null) {
            script.log(`Warning: API request "${apiurl}" for company "${coName}" failed.`);
            continue;
        }

        let results = result.value;
        if (!results || results.length === 0) {
            continue;
        }
        let nextPage = result['@odata.nextLink'];

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

            // dimensionSetLines is an array of dimension line objects - sample
            // the first element's shape, not the array itself.
            let firstDimLine = (sampleRow.dimensionSetLines && sampleRow.dimensionSetLines[0]) || {};
            let dimSampleRow = Object.assign({}, firstDimLine);
            dimSampleRow.postingDate = sampleRow.postingDate;
            dimSampleRow.generalLedgerEntries_id = sampleRow.id;
            dimSampleRow.business_central_company_id = business_central_company_id;

            tables[mainTableName].sampleRow = sampleRow;
            tables[dimTableName].sampleRow = dimSampleRow;

            let objFieldTypes = {
                "lastModifiedDateTime": "DATETIME",
                "business_central_company_id": "INT(11)",
                "postingDate": "DATE",
                "debitAmount": "decimal(10,2)",
                "creditAmount": "decimal(10,2)",
                "additionalCurrencyDebitAmount": "decimal(10,2)",
                "additionalCurrencyCreditAmount": "decimal(10,2)",
                "dimensionSetLines": "JSON"
            };

            for (let t in tables) {
                let table = tables[t];

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

                // full refresh of this month's data for this company
                let delSql = `DELETE FROM \`${schema}\`.\`${t}\` WHERE business_central_company_id = ? AND ${dateFilterField} > ? AND ${dateFilterField} < ?`
                console.log(delSql)

                datasource.update(
                    ds,
                    delSql,
                    [business_central_company_id, lastDayOfPriorMonth, firstDayOfNextMonth]
                );

                table.batch = datasource.createBatch(ds, `${schema}.${t}`, table.tableFields);
            }

            tableReady = true;
        }

        for (let i = 0; i < results.length; i++) {
            if (script.IsCancelled()) {
                flushAllBatches();
                return;
            }
            processRow(results[i], business_central_company_id);
        }



        // Follow pagination for this company until there are no more pages.
        while (nextPage != null) {
            if (script.IsCancelled()) {
                flushAllBatches();
                return;
            }

            result = NAV(nextPage, "GET", "");
            if (result == null) {
                script.log(`Warning: refresh of GL entries page for company "${coName}" failed.`);
                break;
            }

            results = result.value;
            nextPage = result['@odata.nextLink'];

            for (let i = 0; i < results.length; i++) {
                if (script.IsCancelled()) {
                    flushAllBatches();
                    return;
                }
                processRow(results[i], business_central_company_id);
            }
        }
    }

    flushAllBatches();
}

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.');
}

function getAdjacentMonths(year, month) {
    // Get the last day of the prior month
    const lastDayOfPriorMonth = new Date(year, month - 1, 0);
    const firstDayOfMonth = new Date(year, month - 1, 1);
    const firstDayOfNextMonth = new Date(year, month, 1);

    return {
        lastDayOfPriorMonth: lastDayOfPriorMonth.toISOString().slice(0, 10),
        firstDayOfMonth: firstDayOfMonth.toISOString().slice(0, 10),
        firstDayOfNextMonth: firstDayOfNextMonth.toISOString().slice(0, 10),
    };
}```