Document Base Extensions examples Tutorial 2 (Reports)

These JavaScript extension examples are included in the Document-based Extensions - JavaScript Tutorial 2 file.
Use them to learn how JavaScript extensions work in Banana Accounting.
This code is part of the Extensions documentation and serves as reference for GPTs that generate new extensions.

Complete examples

Account statement - account and period choice

// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialaccountstatement
// @description = Tutorial: Return an account statement for the given account and period
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1

function exec() {
    //Create the report
    var report = Banana.Report.newReport('Report title');

    //Function call to add the footer to the report
    addFooter(report);

    //Function call to take a list of all the account numbers
    var accountList = getAccountsList();

    //Open a dialog window asking to select an item from the list. The selected item is then saved into a variable
    var accountNumber = Banana.Ui.getItem('Account selection', 'Select an account', accountList, 0, false);

    //Open a dialog windows taskint the user to choose a period
    //Return an object with the attributes 'startDate', 'endDate' and 'hasSelection' or undefined if the user clicked cancel
    var date = Banana.Ui.getPeriod('Period selection', '2015-01-01', '2015-12-31');

    //Check if user has clicked cancel, then use the default startDate/endDate (whole year)
    if (date) {
        var openingDate = date.startDate;
        var closureDate = date.endDate;
    }


    //--------------//
    //	1. TITLE	//
    //--------------//
    //Take the description of the given account and add it to the paragraph
    var accountDescription = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Description');
    report.addParagraph("Account statement" + " - " + accountNumber + " " + accountDescription, "styleTitle styleBottomBorder");
    report.addParagraph(" ");


    //--------------//
    //	2. TABLE 	//
    //--------------//
    //Create a table object with all transactions for the given account and period
    var transTab = Banana.document.currentCard(accountNumber, openingDate, closureDate);

    //Create the table that will be printed on the report
    var table = report.addTable("myTable");

    //Add column titles to the table report
    var tableHeader = table.getHeader();
    tableRow = tableHeader.addRow();
    tableRow.addCell("Date", "styleTableHeader");
    tableRow.addCell("Description", "styleTableHeader");
    tableRow.addCell("Contra Account", "styleTableHeader");
    tableRow.addCell("Debit amount", "styleTableHeader");
    tableRow.addCell("Credit amount", "styleTableHeader");
    tableRow.addCell("Balance", "styleTableHeader");

    //Add the values taken from each row of the table (except the last one) to the respective cells of the table
    for (var i = 0; i < transTab.rowCount - 1; i++) {
        var tRow = transTab.row(i);
        tableRow = table.addRow();
        tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')));
        tableRow.addCell(tRow.value("JDescription"));
        tableRow.addCell(tRow.value("JContraAccount"), "styleAccount");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount");
        if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount");
        } else {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount");
        }
    }

    //We add last row (totals) separately because we want to apply a special style only to this row
    for (var i = transTab.rowCount - 1; i < transTab.rowCount; i++) {
        var tRow = transTab.row(i);
        tableRow = table.addRow();
        tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')), "styleTotal");
        tableRow.addCell(tRow.value("JDescription"), "styleTotal");
        tableRow.addCell(tRow.value("JContraAccount"), "styleAccount styleTotal");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount styleTotal");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount styleTotal");
        if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount styleTotal");
        } else {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount styleTotal");
        }
    }

    //Functin call to create all the styles
    var stylesheet = createStyleSheet();

    //Print the report
    Banana.Report.preview(report, stylesheet);
}



//This function adds a Footer to the report
function addFooter(report) {
    report.getFooter().addClass(".img");
    report.getFooter().addImage("documents:logo");
}


//This function take from Banana table 'Accounts' all the account numbers and create a list
function getAccountsList() {
    var arrList = [];
    for (var i = 0; i < Banana.document.table('Accounts').rowCount; i++) {
        var tRow = Banana.document.table('Accounts').row(i);
        if (tRow.value("Account")) {
            arrList.push(tRow.value("Account"));
        }
    }
    return arrList;
}


//The main purpose of this function is to create styles for the report print
function createStyleSheet() {

    var stylesheet = Banana.Report.newStyleSheet();

    //Page style
    var pageStyle = stylesheet.addStyle("@page");
    pageStyle.setAttribute("margin", "20m 15mm 15mm 25mm");

    //Footer style
    style = stylesheet.addStyle(".img");
    style.setAttribute("width", "50%");
    style.setAttribute("height", "auto");
    style.setAttribute("text-align", "center");

    //Title style
    style = stylesheet.addStyle(".styleTitle");
    style.setAttribute("font-size", "14pt");
    style.setAttribute("font-weight", "bold");

    //Bottom border
    style = stylesheet.addStyle(".styleBottomBorder");
    style.setAttribute("border-bottom", "1px solid black");

    //Create a table style adding the border
    style = stylesheet.addStyle("table");
    style.setAttribute("width", "100%");
    stylesheet.addStyle("table.myTable td", "border: thin solid black");

    //Style for the table titles
    style = stylesheet.addStyle(".styleTableHeader");
    style.setAttribute("font-weight", "bold");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("background-color", "#ffd100");
    style.setAttribute("color", "#1b365d");

    //Style for account numbers
    style = stylesheet.addStyle(".styleAccount");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("text-align", "center");

    //Style for amounts
    style = stylesheet.addStyle(".styleAmount");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("text-align", "right");

    //Style for balances
    style = stylesheet.addStyle(".styleBalance");
    style.setAttribute("color", "red");

    //Style for the total of the table
    style = stylesheet.addStyle(".styleTotal");
    style.setAttribute("font-weight", "bold");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("background-color", "#b7c3e0");
    style.setAttribute("border-bottom", "1px double black");

    return stylesheet;
}

Account statement - more accounts print (without accounts/period choice)

// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialaccountstatements
// @description = Tutorial: Return a multiple accounts statements
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1

function exec() {
    //Create the report
    var report = Banana.Report.newReport('Report title');

    //Function call to add the footer to the report
    addFooter(report);

    //First function call to create the page of the report
    createReport(report, '1010', '2015-01-01', '2015-12-31');

    //Add a page break after the first page
    report.addPageBreak();

    //Second function call to create the second page of the report
    createReport(report, '1011', '2015-01-01', '2015-12-31');

    //Functin call to create all the styles
    var stylesheet = createStyleSheet();

    //Print the report
    Banana.Report.preview(report, stylesheet);
}


//The purpose of this function is to create a report using the given accounts number and period
function createReport(report, accountNumber, openingDate, closureDate) {


    //--------------//
    //	1. ADDRESS	//
    //--------------//

    //Read from the table 'Address' some informations and save them
    var namePrefix = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('NamePrefix');
    var firstName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('FirstName');
    var familyName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('FamilyName');
    var organisationName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('OrganisationName');
    var address = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Street');
    var countryCode = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('CountryCode');
    var postalCode = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('PostalCode');
    var locality = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Locality');
    var telephone = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('PhoneMain');
    var email = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('EmailWork');




    //Add a section to the paragraph for the address
    var sectionAddress = report.addSection("styleAddress");

    //Check if there are the desired address informations, then add them to the paragraph
    if (organisationName) {
        sectionAddress.addParagraph(organisationName);
    }

    if (namePrefix) {
        sectionAddress.addParagraph(namePrefix);
    }
    if (firstName && familyName) {
        sectionAddress.addParagraph(firstName + ' ' + familyName);
    }
    if (address) {
        sectionAddress.addParagraph(address);
    }
    if (countryCode && postalCode && locality) {
        sectionAddress.addParagraph(countryCode + ' - ' + postalCode + ' ' + locality);
    }
    if (telephone) {
        sectionAddress.addParagraph('Tel: ' + telephone);
    }
    if (email) {
        sectionAddress.addParagraph('Email: ' + email);
    }


    //--------------//
    //	2. TITLE	//
    //--------------//
    //Take the description of the given account and add it to the paragraph
    var accountDescription = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Description');
    report.addParagraph(" ", "styleParagraph");
    report.addParagraph("Account statement" + " - " + accountNumber + " " + accountDescription, "styleTitle styleBottomBorder");
    report.addParagraph(" ");


    //--------------//
    //	3. TABLE 	//
    //--------------//
    //Create a table object with all transactions for the given account and period
    var transTab = Banana.document.currentCard(accountNumber, openingDate, closureDate);

    //Create the table that will be printed on the report
    var table = report.addTable("myTable");

    //Add column titles to the table report
    var tableHeader = table.getHeader();
    tableRow = tableHeader.addRow();
    tableRow.addCell("Date", "styleTableHeader");
    tableRow.addCell("Description", "styleTableHeader");
    tableRow.addCell("Contra Account", "styleTableHeader");
    tableRow.addCell("Debit amount", "styleTableHeader");
    tableRow.addCell("Credit amount", "styleTableHeader");
    tableRow.addCell("Balance", "styleTableHeader");

    //Add the values taken from each row of the table (except the last one) to the respective cells of the table
    for (var i = 0; i < transTab.rowCount - 1; i++) {
        var tRow = transTab.row(i);
        tableRow = table.addRow();
        tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')));
        tableRow.addCell(tRow.value("JDescription"));
        tableRow.addCell(tRow.value("JContraAccount"), "styleAccount");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount");
        if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount");
        } else {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount");
        }
    }

    //We add last row (totals) separately because we want to apply a special style only to this row
    for (var i = transTab.rowCount - 1; i < transTab.rowCount; i++) {
        var tRow = transTab.row(i);
        tableRow = table.addRow();
        tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')), "styleTotal");
        tableRow.addCell(tRow.value("JDescription"), "styleTotal");
        tableRow.addCell(tRow.value("JContraAccount"), "styleAccount styleTotal");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount styleTotal");
        tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount styleTotal");
        if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount styleTotal");
        } else {
            tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount styleTotal");
        }
    }
}



//This function adds a Footer to the report
function addFooter(report) {
    report.getFooter().addClass(".img");
    report.getFooter().addImage("documents:logo");
}


//The main purpose of this function is to create styles for the report print
function createStyleSheet() {

    var stylesheet = Banana.Report.newStyleSheet();

    //Page style
    var pageStyle = stylesheet.addStyle("@page");
    pageStyle.setAttribute("margin", "20m 15mm 15mm 25mm");

    //Footer style
    style = stylesheet.addStyle(".img");
    style.setAttribute("width", "50%");
    style.setAttribute("height", "auto");
    style.setAttribute("text-align", "center");

    //Title style
    style = stylesheet.addStyle(".styleTitle");
    style.setAttribute("font-size", "14pt");
    style.setAttribute("font-weight", "bold");

    //Bottom border
    style = stylesheet.addStyle(".styleBottomBorder");
    style.setAttribute("border-bottom", "1px solid black");

    //Create a table style adding the border
    style = stylesheet.addStyle("table");
    style.setAttribute("width", "100%");
    stylesheet.addStyle("table.myTable td", "border: thin solid black");

    //Style for the table titles
    style = stylesheet.addStyle(".styleTableHeader");
    style.setAttribute("font-weight", "bold");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("background-color", "#ffd100");
    style.setAttribute("color", "#1b365d");

    //Style for account numbers
    style = stylesheet.addStyle(".styleAccount");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("text-align", "center");

    //Style for amounts
    style = stylesheet.addStyle(".styleAmount");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("text-align", "right");

    //Style for balances
    style = stylesheet.addStyle(".styleBalance");
    style.setAttribute("color", "red");

    //Style for the total of the table
    style = stylesheet.addStyle(".styleTotal");
    style.setAttribute("font-weight", "bold");
    style.setAttribute("padding-bottom", "5px");
    style.setAttribute("background-color", "#b7c3e0");
    style.setAttribute("border-bottom", "1px double black");

    style = stylesheet.addStyle(".styleAddress");
    style.setAttribute("position", "absolute");
    style.setAttribute("left", "0mm");
    style.setAttribute("top", "0mm");
    style.setAttribute("width", "80mm");
    style.setAttribute("height", "30mm");
    style.setAttribute("overflow-shrink-max", "0.6");
    style.setAttribute("overflow", "shrink");

    //Add a space between the date and the title
    style = stylesheet.addStyle(".styleParagraph");
    style.setAttribute("margin", "112px");

    return stylesheet;
}

Export

Export a value from Banana into a text file (.txt)

// Banana Accounting Extension Javascript
// @id = ch.banana.apps.export
// @api = 1.0
// @doctype = *.*
// @description = Tutorial: Export a value from Banana into a text file (*.txt)
// @task = export.file
// @exportfiletype = txt
// @timeout = -1

// It is possible to export data only into external files.
// In this example we take an account balance and we export it into a txt file.
// A dialog window asks the user to insert the file name and choose where to save it.

function exec() {

    //Take the balance (opening + debit-credit) for the given account and period
    var exportResult = Banana.document.currentBalance('1000', '2015-01-05', '2015-02-07').balance;

    //Specifies a value to be returned that will be exported into the txt file
    return exportResult;

}

Export Tutorial2 javascript codes

// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exportjavascriptcodestutorial2.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the tutorial 2 examples
// @task = export.file
// @doctype = *.*
// @docproperties = 
// @timeout = -1
// @exportfiletype = js
//Main function
function exec() {

    //Check if we are on an opened document
    if (!Banana.document) {
        return;
    }

    //Take the table Documents
    var documents = Banana.document.table('Documents');

    //We check if the table Documents exists, then we can take all the codes 
    //If the table Documents doesn't exists, then we stop the script execution
    if (!documents) {
        return;
    } else {

        //We use this variable to save all the codes
        var jsCode = '';

        jsCode += '// Copyright [2018] [Banana.ch SA - Lugano Switzerland]' + '\n';
        jsCode += '// ' + '\n';
        jsCode += '// Licensed under the Apache License, Version 2.0 (the "License");' + '\n';
        jsCode += '// you may not use this file except in compliance with the License.' + '\n';
        jsCode += '// You may obtain a copy of the License at' + '\n';
        jsCode += '// ' + '\n';
        jsCode += '//     http://www.apache.org/licenses/LICENSE-2.0'++ '\n';
        jsCode += '// ' + '\n';
        jsCode += '// Unless required by applicable law or agreed to in writing, software' + '\n';
        jsCode += '// distributed under the License is distributed on an "AS IS" BASIS,' + '\n';
        jsCode += '// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.' + '\n';
        jsCode += '// See the License for the specific language governing permissions and' + '\n';
        jsCode += '// limitations under the License.' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';

        //Function call to get all the tutorial's codes
        jsCode = getJavascriptCode(jsCode, documents);
    }

    return jsCode;
}




//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {

    //Read row by row the table Documents
    var len = documents.rowCount;
    for (var i = 0; i < len; i++) {

        //Create a variable for the row
        var tRow = documents.row(i);

        //We get some values
        var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
        var id = tRow.value("RowId"); //the id of the example
        var description = tRow.value("Description"); //the description of the example
        var attachments = tRow.value("Attachments"); //the javascript code of the example

        //We consider only the rows that contain an id, a description and an attachment
        if (id && description && attachments) {

            //At the beginning of each code, we insert some information
            jsCode += "/** " + '\n';
            jsCode += "    File:        " + fName + '\n';
            jsCode += "    Id:          " + id + '\n';
            jsCode += "    Description: " + description + '\n';
            jsCode += "*/" + '\n';

            //we add the code of the attachments
            jsCode += attachments;

            //At the end of each code, we insert some new lines
            jsCode += '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';
        }
    }

    //Finally we return the variable containing all the codes of all the examples
    return jsCode;
}

Export Tutorial2 javascript codes to HTML

// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exporthtmljavascriptcodestutorial2.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the Tutorial 2 examples in HTML
// @task = export.file
// @doctype = *.*
// @docproperties = 
// @timeout = -1
// @exportfiletype = html

//Main function
function exec() {

    //Check if we are on an opened document
    if (!Banana.document) {
        return;
    }

    //Take the table Documents
    var documents = Banana.document.table('Documents');

    //We check if the table Documents exists, then we can take all the codes 
    //If the table Documents doesn't exists, then we stop the script execution
    if (!documents) {
        return;
    } else {

        //We use this variable to save all the codes
        var jsCode = '';

        //Function call to get all the tutorial's codes
        jsCode = getJavascriptCode(jsCode, documents);
    }

    return jsCode;
}

//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {

    //Read row by row the table Documents
    var len = documents.rowCount;
    for (var i = 0; i < len; i++) {

        //Create a variable for the row
        var tRow = documents.row(i);

        //We get some values
        var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
        var section = tRow.value("Section"); // the section column used to define the title type
        var id = tRow.value("RowId"); //the id of the example
        var description = tRow.value("Description"); //the description of the example
        var attachments = tRow.value("Attachments"); //the javascript code of the example
        attachments = attachments.replace(/</g,'<'); // replace the < symbol
        attachments = attachments.replace(/>/g,'>'); // replace the > symbol

        //For tilte h2 we conside only rows with section h2
        if (section && section === "h2") {
            jsCode += '<h2>' + description + '</h2>\n';
        }
        
        //For js codes we consider only rows with section h3, id, description and attachment
        else if (section && section === "h3" && id && description && attachments) {
            jsCode += '<h3>' + description + '</h3>\n';
            jsCode += '<pre><code class="language-javascript">\n';
            jsCode += '// Banana Accounting Extension Javascript' + '\n';
            jsCode += attachments;
            jsCode += '\n';
            jsCode += '</code></pre>\n';
        }
    }

    //Finally we return the variable containing all the codes of all the examples
    return jsCode;
}