Banana.Document (Accounting)

Documentación •
En este artículo

Methods for accounting's files

Banana is built with object oriented techologies. Each file is a Banana.document class.

Accounting files are elements of class that derive from the Banana.document.
For each file there are:

The following explanations relate to the accounting functions.

Date functions

endPeriod([period])

Return the end date in the form of 'YYYY-MM-DD'.

The endDate and startDate functions are used to retrieve the date of the accounting, so that you can create scripts that work on files from different years.



var dateEnd = Banana.document.endPeriod();
var dateStartFebruary = Banana.document.endPeriod('2M');
  • Period:
    • If period is not present the return value is the end date of the accounting.
    • The period is added to the starting account date, and the last date of the period is returned.
    • Period (for example 2M = 2 months) is a number followed by one of the following charachters
      • D for days
      • M for months
      • Q for quarters
      • S for semesters
      • Y for years
    • Assuming that the Start accounting date is 2015-01-01
      • 1M return 2015-01-02
      • 2M return 2015-02-28
      • 2Q return 2015-06-30
      • 2S return 2015-12-31
      • 2Y return 2016-12-31

 

startPeriod ([period])

Return the end date in the form of 'YYYY-MM-DD'.

The endPeriod and startPeriod functions are used to retrieve the date of the accounting, so that you can create scripts that work on files from different years.



var dateStart = Banana.document.endPeriod();
var dateStart3Q = Banana.document.endPeriod('3Q');
  • Period:
    If period is not present return the start date.
    • Period (for example 2M = 2 months) is a number followed by one of the following charachters
      • D is for Days
      • M for Months
      • Q for Quorters
      • S for Semester
      • Y for year
    • With 1 the starting date of the accounting is returned.
    • Assuming that the Start accounting date is 2015-01-01
      • 1M return 2015-01-01
      • 2M return 2015-02-01
      • 2Q return 2015-04-01 
      • 2S return 2015-07-01
      • 2Y return 2016-01-01

 previousYear([nrYears])

Returns the previous year as a Banana.Document object. If the previous year is not defined or it is not found, it returns null.

  • nrYears is the number of years to go back, default is one.


var previousYearDoc = Banana.document.previousYear();
var previousTwoYearDoc = Banana.document.previousYear(2);

Current accounting functions

The functions that start with "current" retrieve values calculated based on the actual accounting data, comprising:

  • Opening amounts (Table accounts)
  • Transactions entered in the Transactions table

 currentBalance(account [, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the amounts of opening, debit, credit, total and balance calculated based on the opening and all transactions for this account / group.

The calculations are performed by traversing by creating a journal (see journal() function) with all the transactions, and selecting the transactions with the parameters specified.
The computation is usually very fast. But if you  have a file with many transactions especially the first query could take some time. 



var currentBal = Banana.document.currentBalance('1000','','');
var openingBalance = currentBal.opening;
var endBalance = currentBal.balance;
  • Return value
    Is an object that has
    • opening the amount at the begining of the period (all transactions before)
    • debit the amount of debit transactions for the period
    • credit the amount of credit transactions for the period
    • total the difference between debit-credit for the period
    • balance opening + debit-credit for the period
    • amount it the "normalized" amount based on the bclass of the account or group.
      If there are multiple accounts or groups, takes the first BClass of the first.
      • for BClass 1 or 2 it returns the balance (value at a specific instant).
      • for BClass 3 or 4 it returns the total (value for the duration).
      • For BClass 2 and 4 the amount is inverted.
    • openingCurrency the amount at the begining of the period in the account currency
    • debitCurrency the amount of debit transactions for the period in the account currency
    • creditCurrency the amount of credit transactions for the period in the account currency
    • totalCurrency the difference between debit-credit for the period in the account currency
    • balanceCurrency opening + debit-credit for the period in the account currency
    • rowCount the number of lines that have been found and used for this computation
    • bclass (double entry accounting only) is the bclass of the account or group used to express the amount.
      The bclass is the value entered in the columns bclass.
      It is taken in consideration the first account or group specified. If for example you query two accounts that first has bclass 2 and the second has bclass 1. The bclass would be 2.
      The bclass is assigned by following this steps. :
      • The bclass of the specified account or group.
      • The bclass of the partent group, for the same section.
      • The bclass of the section.
  • Account
    • can be an account id, a cost center, a segment or a group.
    • can be a combination of account and segments, separated by the semicolon ":"
      In this case it returns all the transactions that have the indicated account and segments
      • 1000:A1:B1
    • can be different accounts and multiple segments separated by the "|"
      In this case it includes all transactions that have one of the specified accounts and one of the specified segments
      • 1000|1001
      • 1000|1001:A1:B1
      • 1000|1001:A1|A2:B1
    • can be a wildCardMatching
      Wildcards can be used for accounts, segments, Groups or BClass and in combination
      • ?  Matches any single character.
      • *  Matches zero or more of any characters
      • [...] Set of charachtes
      • "100?" match "1001, 1002, 1003, 100A, ...)
      • "100*" Matches all accounts starting with 100
      • "100*|200*:A?" Matches all accounts starting with 100 or 200 and with segments with A and of two charachters.
      • "[1234]000" Matches "1000 2000 3000 4000"
    • Can be a group or a BClass.
      It includes all the transactions where the account used belongs to a specified Group or BClass.
      It is also possible to use wildcards.
      The program first creates a list of accounts and then uses the account list.
      Do non mix groups relative to normal accounts, with groups relative to cost center or segments. Calculation could provide unexpected results.
      • BClass (for the double entry accounting only)
        • BClass=1
        • BClass=1|2
      • Gr for groups that are in Accounts table.
        • Gr=100
        • Gr=10*
        • Gr=100|101|102
      • GrC for group that are in the Category table of the income and expenses accounting type.
        • GrC=300
        • GrC=300|301
    • Can be Contra Accounts or other fields selection
      Transactions are included only if they have also a value corresponding
      After the "&&" you can insert a field name of the table journal.
      • 1000&&JContraAccount=2000 returns all transactions of the account 1000 that have a contra account 2000.
        As per accounts you can specify multiple contra accounts, BClass=, Gr= with also wildcards.
      • 1000&&JCC1=P1|P2 will use only transactions on account 1000 and that also have the CC1=.P1 or .P2
    • Do not mix accounts or groups with cost centers ("1020|,RF2" or ",RF2|10"). It only returns the cost center amount.
  • StartDate
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If startDate is empty the accounting start date is taken.
  • End date:
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If endDate is empty the accounting end date is taken.
  • function(rowObj, rowNr, table)
    This fuction will be called for each row of the selected account.
    The function should return true if you want this row to be included in the calculation.


function exec( string) {
    // We retrive the total sales (account 4000) only for the cost center P1
    var balanceData = Banana.document.currentBalance('4000','', '', onlyCostCenter);
    // sales is a revenue so is negative and we invert the value
    var salesCC1 = -balanceData.total;
    // display the information
    Banana.Ui.showText("Sales of Project P1: " + salesCC1);
}

// this function returns true only if the row has the cost center code  "P1"
function onlyCostCenter( row, rowNr, table){
   if(row.value('JCC1') === 'P1') {
      return true;
   }
   return false;
}

Examples



Banana.document.currentBalance("1000")              // Account 1000 
Banana.document.currentBalance("1000|1010")         // Account 1000 or  1010 
Banana.document.currentBalance("10*|20*")           // All account that start with 10 or with 20
Banana.document.currentBalance("Gr=10")             // Group 10
Banana.document.currentBalance("Gr=10|20")          // Group 10 or  20
Banana.document.currentBalance(".P1")               // Cost center .P1
Banana.document.currentBalance(";C01|;C02")         // Cost center ;C01 and C2
Banana.document.currentBalance(":S1|S2")            // Segment :S1  and :S2
Banana.document.currentBalance("1000:S1:T1")        // Account 1000 with segment :S1 or ::T1
Banana.document.currentBalance("1000:{}")           // Account 1000 with segment not assigned 
Banana.document.currentBalance("1000:S1|S2:T1|T2")  // Account 1000 with segment :S1 or ::S2 and ::T1 or ::T2
Banana.document.currentBalance("1000&&JCC1=P1")     // Account 1000 and cost center .P1

 currentBalances(account, frequency [, startDate, endDate, function(rowObj, rowNr, table) ])

It returns the time series balance for the specified periods. It is used for chart rendering, with one command you can have the monthly data for an account.

Sum the amounts of opening, debit, credit, total and balance for all transactions for this account and returns the values according to the indicated frequency indicated.

The calculations are perfermed by traversing by creating a journal (see journal() function) with all the transactions , and selecting the transactions with the parameters specified.
The computation is usually very fast. But if you  have a file with many transactions especially the first query could take some time. 



var currentBalances = Banana.document.currentBalances('1000', 'M');
var openingBalance = currentBalances[0].opening;
var endBalance = currentBalances[0].balance;
  • Return value
    Return an array of objects that have
    • opening the amount at the begining of the period (all transactions before)
    • debit the amount of debit transactions for the period
    • credit the amount of credit transactions for the period
    • total the difference between debit-credit for the period
    • balance opening + debit-credit for the period
    • amount it the "normalized" amount based on the bclass of the account or group.
      If there are multiple accounts or groups, takes the first BClass of the first.
      • for BClass 1 or 2 it return the balance (value at a specific instant).
      • for BClass 3 or 4 it return the total (value for the duration).
      • For BClass 2 and 4 the amount is inverted.
    • openingCurrency the amount at the begining of the period in the account currency
    • debitCurrency the amount of debit transactions for the period in the account currency
    • creditCurrency the amount of credit transactions for the period in the account currency
    • totalCurrency the difference between debit-credit for the period in the account currency
    • balanceCurrency opening + debit-credit for the period in the account currency
    • rowCount the number of lines that have bben found and used for this computation
    • bclass (double entry accounting only) is the bclass of the account or group used to express the amount.
      The bclass is the value entered in the columns bclass.
      It is taken in consideration the first account or group specified. If for example you query two accounts, the first has bclass 2 and the second has bclass 1. The bclass would be 2.
      The bclass is assigned by following this steps. :
      • The bclass of the specified account or group.
      • The blcass of the partent group, for the same section.
      • The blcass of the section.
    • startDate period's start date
    • endDate period's end date
  • Account
    • can be an account id, a cost center or a segment.
    • can be a combination of account and segments, separeted by the semicolon ":"
      In this case it returns all the transactions that have the indicated account and segments
      • 1000:A1:B1
    • can be different accounts and multiple segments separated by the "|"
      In this case it includes all transactions that have one of the specified accounts and one of the specified segments
      • 1000|1001
      • 1000|1001:A1:B1
      • 1000|1001:A1|A2:B1
    • can be a wildCardMatching
      Wildcards can be used for accounts, segments, Groups or BClass and in combination
      • ?  Matches any single character.
      • *  Matches zero or more of any characters
      • [...] Set of charachtes
      • "100?" match "1001, 1002, 1003, 100A, ...)
      • "100*" Matches all accounts starting with 100
      • "100*|200*:A?" Matches all accounts starting with 100 or 200 and with segments with A and of two charachters.
      • "[1234]000" Matches "1000 2000 3000 4000"
    • Can be a group or a BClass.
      It includes all the transactions where the account used to belong to a specified Group or BClass.
      It is also possible to use wildcards.
      The program first create a list of accounts and then use the account list.
      Do non mix mix groups relative to normal accounts, with groups relative to cost center or segments. Calculation could provide unexpected results.
      • BClass (for the double entry accounting only)
        • BClass=1
        • BClass=1|2
      • Gr for groups that are in Accounts table.
        • Gr=100
        • Gr=10*
        • Gr=100|101|102
      • GrC for group that are in the Category table of the income and expenses accounting type.
        • GrC=300
        • GrC=300|301
    • Can be Contra Accounts or other fields selection
      Transactions are included only if they have also a value corresponding
      After the "&&" you can insert a field name of the table journal.
      • 1000&&JContraAccount=2000 return all transctions of the account 1000 that have a contra account 2000.
        As per accounts you can specify multiple contra accounts, BClass=, Gr= with also wildcards.
      • 1000&&JCC1=P1|P2 will use only transactions on account 1000 and that also have the CC1=.P1 or .P2
    • Do not mix accounts or groups with cost centers ("1020|,RF2" or ",RF2|10"). It only returns the cost center amount.
  • Frequency
    • Specifiy the frequency to be returned, is one of the following charachters
      • D for daily
      • W for weekly
      • M for montly
      • Q for quarterly
      • S for semeterly
      • Y for yearly
  • StartDate
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If startDate is empty the accounting start date is taken.
  • End date:
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If endDate is empty the accounting end date is taken.
  • function(rowObj, rowNr, table)
    This fuction will be called for each row of the selected account.
    The function should return true if you want this row to be included in the calculation.


function exec( string) {
    // We retrive the montly total sales (account 4000) only for the cost center P1
    var balanceData = Banana.document.currentBalances('4000', 'M', '', '', onlyCostCenter);
    // sales is a revenue so is negative and we invert the value
    var salesCC1 = -balanceData[0].total;
    // display the information
    Banana.Ui.showText("Sales of Project P1: " + salesCC1);
}

// this function return true only if the row has the cost center code  "P1"
function onlyCostCenter( row, rowNr, table){
   if(row.value('JCC1') === 'P1') {
      return true;
   }
   return false;
}

Examples



Banana.document.currentBalances("1000", 'M')
// Montly values for account 1000 and for the accounting start and end period
// See also the examples for the function currentBalance

 currentCard(account [, startDate, endDate, function(rowObj, rowNr, table)])

Return for the given account and period a Table object with the transactions for this account.

Row are sorted by JDate

parameters:

  • account can be any accounts, cost center or segment as specifiend in currentBalance.
  • startDate any date or symbol as specifiend in currentBalance.
  • endDate any date or symbol as specifiend in currentBalance.

Return columns the same as for the Journal() function.



var transactions = Banana.document.currentCard('1000','2015-01-01','2015-12-31');

 currentInterest( account, interestRate, [startDate, endDate, , function(rowObj, rowNr, table)])

Returns the calculated interest on the specified account.
Interest is calculated on the effective number of days for 365 days in the years.

  • account is the account or group (same as in the function currentBalance)
  • interestRate. In percentage "5", "3.25". Decimal separator must be a "." 
    • If positive it calculates the interest on the debit amounts.
    • If negative it calculates the interest on the credit amounts.
  • startDate, endDate, function see the currentBalance description.
    If no parameters are specified it calculate the interest for the whole year.


// calculate the interest debit for the whole period
var interestDebit = Banana.document.currentInterest('1000','5.75');

// calculate the interest credit for the whole period
var interestDebit = Banana.document.currentInterest('1000','-4.75');

Budget Functions

These functions are similar to the "current" functions, but they work on the budget data. They consider:

  • The opening amount (table Accounts and Categories)
  • Transactions entered in the Budget table.

For detailed information check the documentation on the equivalent "current" functions.

When using the API in the column Formula of the Table Budget a special budget API is available that allows you to use predefined periods instead of dates, like:

  • "MC" current month
  • "QC" current trimester
  • "MP" previous month
  • "QP" previous quarter

In the formula "budgetBalance('1000', 'MP'); the program will calculate automatically the start and end date of the month, based on the Date of the budget transaction.  

budgetBalance(account [, startDate, endDate, function(rowObj, rowNr, table)])

Sums the amounts of opening, debit, credit, total and balance for all budget transactions for this accounts .



var budget = Banana.document.budgetBalance('1000');

It works the same as the function currentBalance, but for the budgeting data.

budgetBalances(account, frequency [, startDate, endDate, function(rowObj, rowNr, table)])

Time series function. Sums the amounts of opening, debit, credit, total and balance for all budget transactions for this account and returns the values according to the indicated frequency indicated.



var budgets = Banana.document.budgetBalances('1000', 'M');

It works the same as the function currentBalances, but for the budgeting data.

budgetCard(account [, startDate, endDate, function(rowObj, rowNr, table)])

Returns, for the given account and period, a Table object with the budget account card.



var card = Banana.document.budgetCard('1000');

It works the same as the function currentCard, but for the budgeting data.

budgetExchangeDifference( account, [date, exchangeRate])

Returns the unrealized exchange rate Profit or Loss o the account at the specified date. 

  • account must be a valid account number not in base currency
  • date
    • a date that is used to calculate the balance
    • if empty calculate up to the end of the period
  • exchangeRate
    • if empty use the historic exchange rate for the specified date or the current if not a valid exchange rate for the date are found.
    • if "current" use the current exchange
    • if number for example "1.95" use the specified exchange rate.
  • Return value
    • Positive number (debit) are exchange rate Loss.
    • Negative number (credit) are exchange rate Profit.
    • empty if no difference or if the account has not been found or not a multicurrency accounting file.


// unrealized exchange rate profit or loss for the account 1000
// last balance and current exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000');

// at the end of Semptember and hystoric exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31");

// at the end of Semptember and current exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31", "current");

// at the end of Semptember and specified exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31", "1.65");

budgetInterest( account, interestRate, [startDate, endDate, function(rowObj, rowNr, table)])

Return the calculated interest for the budget transactions. 

It works the same as the function currentInterest, but for the budgeting data.



// calculate the interest debit for the whole period
var interestDebit = Banana.document.budgetInterest('1000','5.75');

// calculate the interest credit for the whole period
var interestDebit = Banana.document.budgetInterest('1000','-4.75');

Projections functions

This function is a mix of actual and budget data. It has a parameter projectionStartDate that defines up to when to use actual data and budget data.

The value of a projection is calculated as follows:

  • It uses the actual data up to the day before the projectionStartDate.
  • From the projectionStartDate it uses the budgeting data.

Assume you have prepared the budget for the year and you have entered accounting data up to the end of March (2015-03-31).
With the projectionBalance function you can have the projected balance up to the end of year, comprised from the actual data up to end of March and the budgeting data starting form 1. April.
In this case the projectionStartDate should be "2015-04-01".

projectionBalance(account, projectionStartDate [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as currentBalance but use the budget data starting from the projectionStartDate.

This function calculates a projection of the end of year result (or specified period) combining the current data and the budget data for the period not yet booked.

If projectionStartDate is empty the result will be the same as currentBalance.

If you have already booked the 1. semester and would like to have a projection up to the end of the year



// We have booked the 1. semester and would like to have
// a projection up to the end of the yer
var cashProjection = Banana.document.projectionBalance('1000','2015-07-01');
var cashEnd = projection.balance;
var salesProjection = Banana.document.projectionBalance('3000','2015-07-01').total;
var salesForYear = -salesProjection.total;

projectionBalances(account, projectionStartDate, frequency [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as currentBalances but use the budget data starting from the projectionStartDate.

projectionCard(account, projectionStartDate [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as currentCard but use the budget data starting from the projectionStartDate.

If projectionStart date is empty result will be the same s currentCard.



var transactions = Banana.document.projectionCard('1000','2015-01-01','','');

 

Descriptions and Reporting functions

accountDescription(account [,column])

Return the Description of the specified account.

  • Account can be an account or a Group (Gr=)
  • Column can be an alternative column name to retrieve.


var descriptionAccount = Banana.document.accountDescription('1000');
var descriptionGroup = Banana.document.accountDescription('Gr=10');
var gr = Banana.document.accountDescription('1000','Gr');

accountsReport([startDate, endDate])

Returns the account report for the specified period. Start and end date can be a string in form 'YYYY-MM-DD' or a date object.



var report = Banana.document.accountsReport();
var report = Banana.document.accountsReport('2017-01-01', '2017-03-31');

Journal

This function retrieves an array of transactions, with one line for each account.
This is the accounting information that is used for all accounting related calculations.

  1. The software first prepares the Journal with one line for each account amount mouvement.
  2. It uses the Journal data to calculate and report.

One line for each account movement

For each account movement there will be a corresponding line in the journal.

  • Opening amounts.
    One line is generated for each account with an opening amount.
  • Double entry transactions or Income and expense accounts.
    • Transactions with Debit and Credit accounts.
      In the Journal there will be two lines, one for each Credit and Debit account, with the relative amount.
    • Transactions with Debit and Credit accounts plus VAT.
      In the Journal there will be three lines, one for each Credit, Debit and VAT account, with the relative amount.
    • Transactions with Cost center.
      For each cost center used CC1, CC2, CC3 a journal line is created with the Cost center account and relative amount.
    • The amount is the effective amount registered on the account.
      • Exclusive or inclusive VAT depending on the operation.
      • Positive amount is debit, Negative amount is Credit.
      • If there is an error the amount is zero.
    • Segments are separated in the specific columns (Segment1, Segment2 ... Segment10).

Current and Budget data

Each Journal line indicates where is the origin of the data. It can be the Current or Budget.
In case you have both transactions and budget data, for each account you will have Journal lines for Current and Budget. 

 journal([originType = ORIGINTYPE_NONE, int accountType = ACCOUNTTYPE_NONE])

Returns, for the given parameters, a Table object with all the amount registered on the accounts.
The journal contains a row for each account used.

  • originType specify the row to be filtered for
    Can be on of
    • ORIGINTYPE_NONE no filter is applied and all rows are returned (current and budget)
    • ORIGINTYPE_CURRENT only the normal transactions are returned
    • ORIGINTYPE_BUDGET only the budget transactions are returned
  • accountType specify the row to be filtered for
    • ACCOUNTTYPE_NONE no filter is applied and all rows are returned
    • ACCOUNTTYPE_NORMAL only rows for normal accounts are returned
    • ACCOUNTTYPE_CC1 only rows for Cost Center 1 are returned
    • ACCOUNTTYPE_CC2 only rows for Cost Center 2 are returned
    • ACCOUNTTYPE_CC3 only rows for Cost Center 1 are returned
    • ACCOUNTTYPE_CC Cost Center rows are returned same as using (ACCOUNTTYPE_CC1 | ACCOUNTTYPE_CC2 | ACCOUNTTYPE_CC3)


// get all transactions for normal accounts
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL );

For each account used in the transaction table (AccountDebit, AccountCredit, CC1, CC2, CC3) the program generates a Journal row with the JAccount column set with the specific account. 
For a double entry account transaction that uses AccountDebit, AccountCredit, AccountVat, CC1, CC2, CC3 the Journal will contain 6 rows. If the transaction has only AccountDebit and AccountCredit only 2 rows will be generated.

The column JAmount  contains the exact amount registered on the specific account. 

The returned table has all the columns of the transaction's table plus the following columns.

The return columns are:

  • Origin Information
    • JOriginType as defined above
      • ORIGINTYPE_CURRENT = 1
      • ORIGINTYPE_BUDGET = 2
    • JOriginFile the file name where the transaction originates.
    • JTableOrigin the source table.
    • JRowOrigin the row number in the transaction's table (rows begin from 0).
    • JRepeatNumber the progressive number of the repetition of budget transactions.
  • JOperationType on of
    • OPERATIONTYPE_NONE = 0
    • OPERATIONTYPE_OPENING = 1
      The row is generated from the opening balance
    • OPERATIONTYPE_CARRYFORWARD = 2
      The row is used from the account card and is the balance of the account at this moment.
    • OPERATIONTYPE_TRANSACTION = 3
      The row is generated from the Transactions table if it is ORIGINTYPE_CURRENT
      or from the budget table if the row is ORIGINTYPE_BUDGET
    •  OPERATIONTYPE_INVOICESETTLEMENT = 21
  • JDate the date of the transaction.
  • JDescription the transaction's description.
  • JAccount the account for this line.
    • There is one row for each account (AccountDebit, AccountCredit, AccountVat, CC1, CC2, CC3).
    • The account is without the Segments.
  • JAccountDescription the Description for this account.
  • JAccountClass the BClass number for this account.
  • JAccountGr the Gr for this account.
  • JAccountGrDescription the Gr for this account.
  • JAccountGrPath the whole Gr path.
  • JAccountCurrency the currency of this account.
  • JAccountType as defined above (ACCOUNTTYPE_NORMAL, ACCOUNTTYPE_CC1, ...)
  • JAmount the amount in basic currency registered on the account (positive is debit, negative is credit).
  • JAmountAccountCurrency the amount in the account currency (positive i debit, regative is credit).
  • JTransactionCurrency the transaction's currency.
  • JAmountTransactionCurrency the amount in transaction's currency.
    For account with currency not in transactions currency the exchange rate of the transaction is used.
  • JTransactionCurrencyConversionRate is the conversion rate to obtain amounts in transaction's currency. 
    Multiply the transcation's amount in basic currency with the JTransactionCurrencyConversionRate  and you will have the amount converted in transaction's currency.
    The conversion rate has 12 significant figures so only by very large conversion should there be conversion differences.
  • JVatIsVatOperation true if this row has a Vat code.
  • JVatCodeWithoutSign the Vat code without the evetually preceeding '-'.  For example "-V10" becomes "V10".
  • JVatCodeDescription the Description for this Vat code.
  • JVatCodeWithMinus true if the Vat code is preceeded by "-".
  • JVatCodeNegative true if the Vat amount is negative (deductible).
  • JVatTaxable the amount VatTaxable with the sign that follows the JVatCodeNegative
  • VatTwinAccount the account where the net amount (without VAT) is being registered . 
    In case of a transaction where the Gross amount is CHF 1100, then the VAT is CHF 100 and the net amount is CHF 1000. The VatTwin account will be the account where the CHF 1000 is being registered.
    We use the name Twin for the fact that the VatTwinAccount follows the sign of the VatAccount.
    If the Vat amount is registered in debit, the VatTwinAccount will be the AccountDebit.
    If the Vat amount is registered in credit, the VatTwinAccount will be the AccountCredit.
  • JContraAccount the contra account.
    The contra account is deducted based on the other accounts and the sequence in the transactions table.
  • JContraAccountType one of the following value:
    • CONTRAACCOUNTTYPE_NONE  for no contra account
    • CONTRAACCOUNTTYPE_DIRECT when there is on the same line credit and debit accounts.
    • CONTRAACCOUNTTYPE_MULTIPLEFIRST the first line of a transaction on more accounts. 
      The first transactions after a line with debit and credit accounts or with a different date.
    • CONTRAACCOUNTTYPE_MULTIPLEFOLLOW the second or following line of a MULTIPLEFIRST with the same date.
    • CONTRAACCOUNTTYPE_VAT  the line for the Vat Account
  • JContraAccountGroup the line number corresponding to the row number of the CONTRAACCOUNTTYPE_MULTIPLEFIRST
  • JCC1 the CC1 without the preceeding sign
  • JCC2 the CC2 without the preceeding sign
  • JCC3 the CC3 without the preceeding sign
  • JSegment1 .. JSegment10 the segment relative to the account
  • JDebitAmount the amount debit in basic currency
  • JCreditAmount the amount credit in basic currency
  • JDebitAmountAccountCurrency the amount debit in account currency
  • JCreditAmountAccountCurrency the amount credit in account currency
  • JBalance the balance amount (for account card) in basic currency
  • JBalanceAccountCurrency the balance amount (for account card) in account currency

journalCustomersSuppliers([originType = ORIGINTYPE_NONE, int accountType = ACCOUNTTYPE_NONE])

Same as journal with additional settlements rows for closing invoices and additional columns:

  • JInvoiceDocType: specifies the type of document (see column DocType)
  • JInvoiceAccountId: customer account id from table accounts
  • JInvoiceCurrency: the currency of the invoice, same as customer account currency from table accounts
  • JInvoiceStatus: paidInvoice (the invoice was offset with the payment that refers to the same document), paidOpening (the invoice was offset with the opening balance of the customer account)
  • JInvoiceDueDate: invoice expiration date
  • JInvoiceDaysPastDue
  • JInvoiceLastReminder
  • JInvoiceLastReminderDate
  • JInvoiceIssueDate
  • JInvoiceExpectedDate
  • JInvoicePaymentDate
  • JInvoiceDuePeriod
  • JInvoiceRowCustomer (1=Customer, 2=Supplier)

 invoicesCustomers()

Returns a table with the customers' invoices from the transaction table. A customer group must be defined and invoices must be numbered using the column DocInvoice.

See:



// Print the content of the table invoicesCustomers 
	var invoicesCustomers = Banana.document.invoicesCustomers();
	if (invoicesCustomers) {
		for (var i = 0; i < invoicesCustomers.rowCount; i++) {
			var tRow = invoicesCustomers.row(i);
			var jsonString = tRow.toJSON();
			if (jsonString.length > 0) {
				var jsonRow = JSON.parse(jsonString);
				for (key in jsonRow) {
					if (jsonRow[key])
						Banana.console.debug(key + ": " + jsonRow[key].toString());
				}
			}
		}
	}

invoicesSuppliers()

Returns a table with the suppliers' invoices from the transaction table. A supplier group must be defined and invoices must be numbered using the column DocInvoice.

See:


// Print the content of the table invoicesSuppliers
var invoicesSuppliers = Banana.document.invoicesSuppliers();
if (invoicesSuppliers ) {
  for (var i = 0; i < invoicesSuppliers.rowCount; i++) {
   var tRow = invoicesSuppliers.row(i);
   var jsonString = tRow.toJSON();
   if (jsonString.length > 0) {
    var jsonRow = JSON.parse(jsonString);
    for (key in jsonRow) {
     if (jsonRow[key])
      Banana.console.debug(key + ": " + jsonRow[key].toString());
    }
   }
  }
}

 Available columns in invoicesCustomers and invoicesSuppliers table

  • CounterpartyId Customer or supplier accountId
  • Invoice Invoice number
  • ObjectType Type of object in field ObjectJsonData (values are: Counterparty, InvoiceDocument, InvoiceLineItem, InvoiceTotal, Transaction)
  • ObjectIndex Internal index used to print the invoice documents
  • ObjectJSonData Contains a json object. Available object types: Counterparty, InvoiceDocument, InvoiceLineItem, InvoiceTotal, Transaction (Example of row with a counterparty object): 

    {"Counterparty":{"customer_info":
    {"balance":"4176000.00","balance_base_currency":"4176000.00","business_name":"Banana.ch","first_name":"Domenico",
    "last_name":"Zucchetti","number":"411001","origin_row":"950","origin_table":"Accounts"}}}
  • Date Invoice document date
  • TransactionDate Original Transaction date
  • Description Original Transaction description
  • Debit Object amount in debit
  • Credit   Object amount in credit
  • Balance Incremental balance
  • Currency Object currency
  • InvoiceExpectedDate    
  • InvoiceDueDate    
  • InvoiceDuePeriod    
  • InvoiceDaysPastDue    
  • InvoicePaymentDate    
  • InvoiceLastReminder    
  • InvoiceLastReminderDate    
  • Status   
  • JTableOrigin    
  • JRowOrigin

 

Exchange rate functions

 exchangeRate( currency, [date])

Returns the exchange rate that converts the amount in currency in basic currency as object with the properties 'date' and 'exchangeRate'. Returns null if no exchange rate is found.

The exchange rate is retrieved from the Currency table, already considering the multiplier.

  • If no date is specified the exchange rate without date is used.
  • If a date is specified it retrieves the exchange rate with the date minor or equal the specified date.

Vat functions

vatBudgetBalance(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period, using the Budget data.



var vatTotal = Banana.document.vatBudgetBalance('V15');

vatBudgetBalances(vatCode, frequency, [, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period, using the Budget data and returns the values according to the indicated frequency indicated.



var vatTotal = Banana.document.vatBudgetBalances('V15', 'Q');

vatCurrentCard(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Retrieve the transactions relative to the specified VatCode.



var vatTransactions = Banana.document.vatCurrentCard('V15');

vatCurrentBalance(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period.
For more info see :

Example: 



var currentVat = Banana.document.vatCurrentBalance('V15','','');
var vatTaxable = currentVat.vatTaxable;
var vatPosted = currentVat.vatPosted;
  • Return value:
    Is an object that has
    • vatTaxable the amount of the taxable column
      (the sign is the same as the vatAmount)
    • vatAmount the amount of vat
    • vatNotDeductible the amount not deductible
    • vatPosted VatAmount - VatNotDeductible
    • rowCount the number of lines that have been found and used for this computation
  • VatCode
    One or more VatCode defined in the tabel Vat Codes.
    Multiple vat code can be separated by "|" for example "V10|V20", or you can use vildcard "V*".

vatCurrentBalances(vatCode, frequency [, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period and returns the values according to the indicated frequency indicated.
For more info see :

Example: 



var currentVat = Banana.document.vatCurrentBalances('V15', 'Q');
var vatTaxable = currentVat[0].vatTaxable;
var vatPosted = currentVat[0].vatPosted;
  • Return value:
    Is an object that has
    • vatTaxable the amount of the taxable column
      (the sign is the same as the vatAmount)
    • vatAmount the amount of vat
    • vatNotDeductible the amount not deductible
    • vatPosted VatAmount - VatNotDeductible
    • rowCount the number of lines that have bben found and used for this computation
  • VatCode
    One or more VatCode defined in the tabel Vat Codes.
    Multiple vat code can be separated by "|" for example "V10|V20", or you can use vildcard "V*".
  • Frequency
    • Specifiy the frequency to be returned, is one of the following charachters
      • D for daily
      • W for weekly
      • M for montly
      • Q for quarterly
      • S for semeterly
      • Y for yearly

vatProjectionBalance(vatCode, projectionStartDate, [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as vatCurrenBalance but use the budget data starting from the projectionStartDate.



var projectionVat = Banana.document.vatProjectionBalance('V15','','');
var vatTaxable = projectionVat.vatTaxable;
var vatPosted = projectionVat.vatPosted;

vatProjectionBalances(vatCode, projectionStartDate, frequency, [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as vatCurrenBalances but use the budget data starting from the projectionStartDate.



var projectionVat = Banana.document.vatProjectionBalances('V15', '2017-03-01', 'Q');
var vatTaxable = projectionVat[0].vatTaxable;
var vatPosted = projectionVat[0].vatPosted;

vatProjectiontCard(vatCode, projectionStartDate, [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as vatCurrentCard but use the budget data starting from the projectionStartDate.



var vatTransactions = Banana.document.vatProjectiontCard('V15','2015-01-01','','');

vatReport([startDate, endDate])

Returns the vat report for the specified period.

Start and end dates are strings in form 'YYYY-MM-DD' or a date object. If startDate is empty the accounting start date is taken. If endDate is empty the accounting end date is taken.



var vatReport = Banana.document.vatReport('','');

 

 

Help us improve the documentation

We welcome feedback on how to improve this page.

Tell us what theme needs a better explanation or how to clarify a topic.

Share this article: Twitter | Facebook | LinkedIn | Email