Neste artigo
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:
- Methods, that apply to all documents, see Banana.Document (Base),
- Method specific to the Accounting class.
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
- Period (for example 2M = 2 months) is a number followed by one of the following charachters
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
- BClass (for the double entry accounting only)
- 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
- 1000&&JContraAccount=2000 returns all transactions of the account 1000 that have a contra account 2000.
- 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
- BClass (for the double entry accounting only)
- 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
- 1000&&JContraAccount=2000 return all transctions of the account 1000 that have a contra account 2000.
- 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
- Specifiy the frequency to be returned, is one of the following charachters
- 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 per account. It contains the accounting information used for all accounting-related calculations.
The software generates the Journal by creating one line for each account movement. The Journal data is then used for calculations and reporting.
See an Example extension for journal reporting.
Journal Structure
Each account movement corresponds to a separate line in the Journal.
- Opening amounts.
A line is generated for each account with an opening balance. - Double-entry transactions or income and expense accounts.
- Transactions with Debit and Credit accounts.
The Journal will generate two lines.- One for the Debit account, with the corresponding amount.
- One for the Credit account, with the corresponding amount.
- Transactions with Debit and Credit accounts plus VAT.
The Journal will generate three lines:- One for the Debit account, with the corresponding amount.
- One for the Credit account, with the corresponding amount.
- One for the VAT account, with the corresponding amount.
- Transactions with cost centers.
For each cost center used (CC1, CC2, CC3), a separate Journal line is created with the cost center account and its corresponding amount. - The amount is the effective amount registered on the account.
- Exclusive or inclusive of VAT, depending on the operation.
- Positive amounts indicate debits, while negative amounts indicate credits.
- In case of an error, the amount is set to zero.
- Segments are stored in dedicated columns (Segment1, Segment2, … Segment10).
- Transactions with Debit and Credit accounts.
Current and Budget Data
Each Journal line specifies the data source origin; it can be Current or Budget.
If both transaction and budget data exist, the Journal includes separate lines for Current and Budget transactions for each account.
journal([originType = ORIGINTYPE_NONE, int accountType = ACCOUNTTYPE_NONE])
This function returns a Table object containing all registered amounts for the specified parameters.
Parameters:
- originType - Specifies which transaction rows to filter. Possible values:
- ORIGINTYPE_NONE (default)
No filter is applied; all rows are returned, including both current and budget transactions.
For current transactions ORIGINTYPE_NONE = 1
For budget transactoins ORIGINTYPE_NONE = 2 - ORIGINTYPE_CURRENT
Returns only current transactions; ORIGINTYPE_CURRENT = 1. - ORIGINTYPE_BUDGET
Returns only budget transactions; ORIGINTYPE_BUDGET = 2.
- ORIGINTYPE_NONE (default)
- accountType - Specifies which account rows to filter. Possible values:
- ACCOUNTTYPE_NONE (default)
No filter is applied; all account rows are returned. - ACCOUNTTYPE_NORMAL
Returns only normal account rows. - ACCOUNTTYPE_CC1
Returns only rows for Cost Center 1 (CC1). - ACCOUNTTYPE_CC2
Returns only rows for Cost Center 2 (CC2). - ACCOUNTTYPE_CC3
Returns only rows for Cost Center 3 (CC3). - ACCOUNTTYPE_CC
Returns all Cost Center rows (CC1, CC2, CC3), equivalent to using ACCOUNTTYPE_CC1 | ACCOUNTTYPE_CC2 | ACCOUNTTYPE_CC3.
- ACCOUNTTYPE_NONE (default)
// Get all transactions for normal accounts
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL);
Returned Table Structure:
The returned table contains all the columns of the Transactions table, plus the following additional columns:
- JOriginType - Defines the transaction's origin type:
- ORIGINTYPE_CURRENT
Value is 1 (Current transactions). - ORIGINTYPE_BUDGET
Value is 2 (Budget transactions).
- ORIGINTYPE_CURRENT
- JOriginFile - The file name where the transaction originates.
- JTableOrigin - The source table.
- JRowOrigin - The row number in the Transactions table (rows start from 0).
- JRepeatNumber - The progressive repetition number for budget transactions.
- JOperationType - Indicates the type of transaction:
- OPERATIONTYPE_NONE
Value is 0.
Transaction not used for calculation. - OPERATIONTYPE_OPENING
Value is 1.
Transaction used for opening balance calculation; the row is generated from:- The Opening balance column in the Accounts table.
- Transactions with the DocType column with value '01'.
- OPERATIONTYPE_CARRYFORWARD
Value is 2.
OperationType used only in account cards.
It is the opening balance or the balance of the account prior to the begin of the period of the account card.
If the account card is for the month of september, the carryforward amount would be the account balance prior to any transactions of september. - OPERATIONTYPE_TRANSACTION
Value is 3.
The row is a normal transaction and is generated from:- Transactions table if JOriginType = ORIGINTYPE_CURRENT.
- Budget table if JOriginType = ORIGINTYPE_BUDGET.
- OPERATIONTYPE_INVOICESETTLEMENT
Value is 21.
These are rows that are genereted when creating Customer or Suppliers cards.
They are used to settle invoices, payed.
- OPERATIONTYPE_NONE
- JDate - The transaction date.
- JDescription - The transaction description.
- JAccount - The account for this transaction. One row per account (AccountDebit, AccountCredit, AccountVat, CC1, CC2, CC3). Segments are not included.
- JAccountDescription - The account description.
- JAccountClass - The BClass number of the account.
- JAccountGr - The Group (Gr) of the account.
- JAccountGrDescription - The Group description of the account.
- JAccountGrPath - The complete Group hierarchy path. It includes all the Gr tree.
- JAccountCurrency - The currency of the account.
- JAccountType - The account type as defined above (ACCOUNTTYPE_NORMAL, ACCOUNTTYPE_CC1, …).
- JAmount - The exact amount in basic currency (positive = debit, negative = credit).
- JAmountAccountCurrency - The amount in account currency (positive = debit, negative = credit).
- JTransactionCurrency - The currency of the transaction.
- JAmountTransactionCurrency - The amount in transaction currency. For account with currency not in transactions currency the exchange rate of the transaction is used.
- JTransactionCurrencyConversionRate - The conversion rate to transaction currency.
- Multiply the transaction amount in basic currency by this rate to get the transaction currency amount.
- This rate has 12 significant figures to minimize conversion differences; only in the case of very large conversions can there be conversion differences.
- JVatIsVatOperation - 'true' if this row has a VAT code.
- JVatCodeWithoutSign - The VAT code, excluding a preceding "-" (e.g., "-V10" becomes "V10"). Useful for grouping transactions by the same VatCode.
- JVatCodeDescription - Description of the VAT code.
- JVatCodeWithMinus - 'true' if the VAT code has a "-" prefix.
- JVatCodeNegative - 'true' if the VAT amount is negative (deductible VAT).
- JVatTaxable - The VatTaxable amount, following the sign of JVatCodeNegative.
- VatTwinAccount - The account where the net amount (excluding VAT) is registered.
- Example: If the gross amount is CHF 1100 (100 VAT + 1000 net), VatTwinAccount is the account where CHF 1000 is registered.
- The sign of VatTwinAccount follows the VatAccount:
- If VAT is registered in debit, VatTwinAccount = AccountDebit.
- If VAT is registered in credit, VatTwinAccount = AccountCredit.
- JContraAccount - The contra account (based on the other accounts and the sequence in the Transactions table).
- JContraAccountType - Specifies the contra account type:
- CONTRAACCOUNTTYPE_NONE
No contra account. - CONTRAACCOUNTTYPE_DIRECT
When debit and credit accounts exist on the same row. - CONTRAACCOUNTTYPE_MULTIPLEFIRST
The first row of a transaction involving multiple accounts (first transaction after a row with debit and credit accounts or with a different date). - CONTRAACCOUNTTYPE_MULTIPLEFOLLOW
A subsequent row following a CONTRAACCOUNTTYPE_MULTIPLEFIRST, with the same date. - CONTRAACCOUNTTYPE_VAT
VAT Account row.
- CONTRAACCOUNTTYPE_NONE
- JContraAccountGroup – The row number corresponding to CONTRAACCOUNTTYPE_MULTIPLEFIRST.
- JCC1 - Cost Center 1 (without preceding sign).
- JCC2 - Cost Center 2 (without preceding sign).
- JCC3 - Cost Center 3 (without preceding sign).
- JSegment1 ... JSegment10 - Segments related to the account.
- JDebitAmount - Debit amount in basic currency.
- JCreditAmount - Credit amount in basic currency.
- JDebitAmountAccountCurrency - Debit amount in account currency.
- JCreditAmountAccountCurrency - Credit amount in account currency.
- JBalance - Balance (for account cards) in basic currency.
- JBalanceAccountCurrency - Balance (for account cards) in account currency.
Examples:
// Get the Journal table of all transactions for normal accounts
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL);
//Read Journal table row by row
for (var i = 0; i < journal.rowCount; i++) {
var tRow = journal.row(i);
//Check if the type of transaction is a normal transaction generated from the Transactions or Budget table
if (tRow.value('JOperationType') == Banana.document.OPERATIONTYPE_TRANSACTION) {
var jDate = tRow.value('JDate'); // the transaction date
var jDescription = tRow.value('JDescription'); // the transaction description
var jAccount = tRow.value('JAccount'); // the transaction account, one row per account
var jContraAccount = tRow.value('JContraAccount'); // the transaction contra account
var jAmount = tRow.value('JAmount'); // the transaction amount
//...
}
}
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 :
- explanations of the function currentBalance.
- Vat Extension Example
- Example files are available on github/General/CaseStudies.
- Solutions making use of the VAT api.
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
- vatTaxable the amount of the taxable column
- 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 :
- explanations of the function currentBalances.
- Example files are available on github/General/CaseStudies.
- Solutions making use of the VAT api.
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
- vatTaxable the amount of the taxable column
- 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
- Specifiy the frequency to be returned, is one of the following charachters
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('','');