Account balance in debit and credit columns in trial balance and financial analytics report
diff --git a/erpnext/accounts/page/accounts_browser/accounts_browser.js b/erpnext/accounts/page/accounts_browser/accounts_browser.js
index d82e55c..e68e43c 100644
--- a/erpnext/accounts/page/accounts_browser/accounts_browser.js
+++ b/erpnext/accounts/page/accounts_browser/accounts_browser.js
@@ -32,7 +32,7 @@
'<li>'+
'<b>'+frappe._('To create a Bank Account:')+'</b>'+
frappe._('Go to the appropriate group (usually Application of Funds > Current Assets > Bank Accounts)')+
- frappe._('and create a new Account Ledger (by clicking on Add Child) of type "Bank or Cash"')+
+ frappe._('and create a new Account Ledger (by clicking on Add Child) of type "Bank"')+
'</li>'+
'<li>'+
'<b>'+frappe._('To create a Tax Account:')+'</b>'+
@@ -193,8 +193,9 @@
options:'Group\nLedger', description: frappe._('Further accounts can be made under Groups,')+
frappe._('but entries can be made against Ledger')},
{fieldtype:'Select', fieldname:'account_type', label:frappe._('Account Type'),
- options: ['', 'Fixed Asset', 'Bank or Cash', 'Expense Account', 'Tax',
- 'Income Account', 'Chargeable'].join('\n'),
+ options: ['', 'Bank', 'Cash', 'Warehouse', 'Receivable', 'Payable',
+ 'Equity', 'Cost of Goods Sold', 'Fixed Asset', 'Expense Account',
+ 'Income Account', 'Tax', 'Chargeable'].join('\n'),
description: frappe._("Optional. This setting will be used to filter in various transactions.") },
{fieldtype:'Float', fieldname:'tax_rate', label:frappe._('Tax Rate')},
{fieldtype:'Button', fieldname:'create_new', label:frappe._('Create New') }
diff --git a/erpnext/accounts/page/financial_analytics/financial_analytics.js b/erpnext/accounts/page/financial_analytics/financial_analytics.js
index dcc4a51..27ca19d 100644
--- a/erpnext/accounts/page/financial_analytics/financial_analytics.js
+++ b/erpnext/accounts/page/financial_analytics/financial_analytics.js
@@ -16,20 +16,27 @@
erpnext.FinancialAnalytics = erpnext.AccountTreeGrid.extend({
filters: [
- {fieldtype:"Select", label: frappe._("PL or BS"), options:["Profit and Loss", "Balance Sheet"],
+ {
+ fieldtype:"Select", label: frappe._("PL or BS"),
+ options:["Profit and Loss", "Balance Sheet"],
filter: function(val, item, opts, me) {
if(item._show) return true;
// pl or bs
- var out = (val!='Balance Sheet') ? item.is_pl_account=='Yes' : item.is_pl_account!='Yes';
+ var out = (val=='Balance Sheet') ?
+ item.report_type=='Balance Sheet' : item.report_type=='Profit and Loss';
if(!out) return false;
return me.apply_zero_filter(val, item, opts, me);
- }},
- {fieldtype:"Select", label: frappe._("Company"), link:"Company", default_value: "Select Company...",
+ }
+ },
+ {
+ fieldtype:"Select", label: frappe._("Company"),
+ link:"Company", default_value: "Select Company...",
filter: function(val, item, opts) {
return item.company == val || val == opts.default_value || item._show;
- }},
+ }
+ },
{fieldtype:"Select", label: frappe._("Fiscal Year"), link:"Fiscal Year",
default_value: "Select Fiscal Year..."},
{fieldtype:"Date", label: frappe._("From Date")},
@@ -46,13 +53,93 @@
formatter: this.check_formatter},
{id: "name", name: frappe._("Account"), field: "name", width: 300,
formatter: this.tree_formatter},
- {id: "opening", name: frappe._("Opening"), field: "opening", hidden: true,
- formatter: this.currency_formatter}
+ {id: "opening_dr", name: frappe._("Opening (Dr)"), field: "opening_dr",
+ hidden: true, formatter: this.currency_formatter, balance_type: "Dr"},
+ {id: "opening_cr", name: frappe._("Opening (Cr)"), field: "opening_cr",
+ hidden: true, formatter: this.currency_formatter, balance_type: "Cr"},
];
- this.make_date_range_columns();
+ this.make_date_range_columns(true);
this.columns = std_columns.concat(this.columns);
},
+ make_date_range_columns: function() {
+ this.columns = [];
+
+ var me = this;
+ var range = this.filter_inputs.range.val();
+ this.from_date = dateutil.user_to_str(this.filter_inputs.from_date.val());
+ this.to_date = dateutil.user_to_str(this.filter_inputs.to_date.val());
+ var date_diff = dateutil.get_diff(this.to_date, this.from_date);
+
+ me.column_map = {};
+ me.last_date = null;
+
+ var add_column = function(date, balance_type) {
+ me.columns.push({
+ id: date + "_" + balance_type.toLowerCase(),
+ name: dateutil.str_to_user(date),
+ field: date + "_" + balance_type.toLowerCase(),
+ date: date,
+ balance_type: balance_type,
+ formatter: me.currency_formatter,
+ width: 100
+ });
+ }
+
+ var build_columns = function(condition) {
+ // add column for each date range
+ for(var i=0; i <= date_diff; i++) {
+ var date = dateutil.add_days(me.from_date, i);
+ if(!condition) condition = function() { return true; }
+
+ if(condition(date)) {
+ $.each(["Dr", "Cr"], function(i, v) {
+ add_column(date, v)
+ });
+ }
+ me.last_date = date;
+
+ if(me.columns.length) {
+ me.column_map[date] = me.columns[me.columns.length-1];
+ }
+ }
+ }
+
+ // make columns for all date ranges
+ if(range=='Daily') {
+ build_columns();
+ } else if(range=='Weekly') {
+ build_columns(function(date) {
+ if(!me.last_date) return true;
+ return !(dateutil.get_diff(date, me.from_date) % 7)
+ });
+ } else if(range=='Monthly') {
+ build_columns(function(date) {
+ if(!me.last_date) return true;
+ return dateutil.str_to_obj(me.last_date).getMonth() != dateutil.str_to_obj(date).getMonth()
+ });
+ } else if(range=='Quarterly') {
+ build_columns(function(date) {
+ if(!me.last_date) return true;
+ return dateutil.str_to_obj(date).getDate()==1 && in_list([0,3,6,9], dateutil.str_to_obj(date).getMonth())
+ });
+ } else if(range=='Yearly') {
+ build_columns(function(date) {
+ if(!me.last_date) return true;
+ return $.map(frappe.report_dump.data['Fiscal Year'], function(v) {
+ return date==v.year_start_date ? true : null;
+ }).length;
+ });
+
+ }
+
+ // set label as last date of period
+ $.each(this.columns, function(i, col) {
+ col.name = me.columns[i+2]
+ ? dateutil.str_to_user(dateutil.add_days(me.columns[i+2].date, -1)) + " (" + me.columns[i].balance_type + ")"
+ : dateutil.str_to_user(me.to_date) + " (" + me.columns[i].balance_type + ")";
+ });
+ },
setup_filters: function() {
var me = this;
this._super();
@@ -69,7 +156,6 @@
},
prepare_balances: function() {
var me = this;
-
// setup cost center map
if(!this.cost_center_by_name) {
this.cost_center_by_name = this.make_name_map(frappe.report_dump.data["Cost Center"]);
@@ -100,29 +186,32 @@
if(gl.voucher_type=='Period Closing Voucher') {
// period closing voucher not to be added
// to profit and loss accounts (else will become zero!!)
- if(account.is_pl_account!='Yes')
- me.add_balance(col.field, account, gl);
+ if(account.report_type=='Balance Sheet')
+ me.add_balance(col.date, account, gl);
} else {
- me.add_balance(col.field, account, gl);
+ me.add_balance(col.date, account, gl);
}
- } else if(account.is_pl_account!='Yes'
+ } else if(account.report_type=='Balance Sheet'
&& (posting_date < dateutil.str_to_obj(me.from_date))) {
- me.add_balance('opening', account, gl);
+ me.add_balance('opening', account, gl);
}
}
});
-
+
// make balances as cumulative
if(me.pl_or_bs=='Balance Sheet') {
$.each(me.data, function(i, ac) {
- if((ac.rgt - ac.lft)==1 && ac.is_pl_account!='Yes') {
+ if((ac.rgt - ac.lft)==1 && ac.report_type=='Balance Sheet') {
var opening = 0;
//if(opening) throw opening;
$.each(me.columns, function(i, col) {
if(col.formatter==me.currency_formatter) {
- ac[col.field] = opening + flt(ac[col.field]);
- opening = ac[col.field];
+ if(col.balance_type=="Dr") {
+ opening = opening + flt(ac[col.date + "_dr"]) -
+ flt(ac[col.date + "_cr"]);
+ me.set_debit_or_credit(ac, col.date, opening);
+ }
}
});
}
@@ -140,40 +229,62 @@
indent: 0,
opening: 0,
checked: false,
- is_pl_account: me.pl_or_bs=="Balance Sheet" ? "No" : "Yes",
+ report_type: me.pl_or_bs,
};
me.item_by_name[net_profit.name] = net_profit;
-
- $.each(me.data, function(i, ac) {
- if(!ac.parent_account && me.apply_filter(ac, "company")) {
- if(me.pl_or_bs == "Balance Sheet") {
- var valid_account = ac.is_pl_account!="Yes";
- var do_addition_for = "Asset";
- } else {
- var valid_account = ac.is_pl_account=="Yes";
- var do_addition_for = "Income";
- }
- if(valid_account) {
- $.each(me.columns, function(i, col) {
- if(col.formatter==me.currency_formatter) {
- if(!net_profit[col.field]) net_profit[col.field] = 0;
- if(ac.root_type==do_addition_for) {
- net_profit[col.field] += ac[col.field];
- } else {
- net_profit[col.field] -= ac[col.field];
- }
- }
- });
- }
+
+ $.each(me.columns, function(i, col) {
+ if(col.formatter==me.currency_formatter) {
+ if(!net_profit[col.id]) net_profit[col.id] = 0;
}
});
+ $.each(me.data, function(i, ac) {
+ if(!ac.parent_account && me.apply_filter(ac, "company") &&
+ ac.report_type==me.pl_or_bs) {
+ $.each(me.columns, function(i, col) {
+ if(col.formatter==me.currency_formatter && col.balance_type=="Dr") {
+ var bal = net_profit[col.date+"_dr"] -
+ net_profit[col.date+"_cr"] +
+ ac[col.date+"_dr"] - ac[col.date+"_cr"];
+ me.set_debit_or_credit(net_profit, col.date, bal);
+ }
+ });
+ }
+ });
this.data.push(net_profit);
+ // $.each(me.data, function(i, v) {
+ // if(v.report_type=="Profit and Loss") console.log(v)
+ // })
}
},
add_balance: function(field, account, gl) {
- account[field] = flt(account[field]) + ((in_list(["Asset", "Expense"],
- account.root_type) ? 1 : -1) * (flt(gl.debit) - flt(gl.credit)));
+ var bal = flt(account[field+"_dr"]) - flt(account[field+"_cr"]) +
+ flt(gl.debit) - flt(gl.credit);
+ this.set_debit_or_credit(account, field, bal);
+ },
+ update_groups: function() {
+ // update groups
+ var me= this;
+ $.each(this.data, function(i, account) {
+ // update groups
+ if((account.group_or_ledger == "Ledger") || (account.rgt - account.lft == 1)) {
+ var parent = me.parent_map[account.name];
+ while(parent) {
+ var parent_account = me.item_by_name[parent];
+ $.each(me.columns, function(c, col) {
+ if (col.formatter == me.currency_formatter && col.balance_type=="Dr") {
+ var bal = flt(parent_account[col.date+"_dr"]) -
+ flt(parent_account[col.date+"_cr"]) +
+ flt(account[col.date+"_dr"]) -
+ flt(account[col.date+"_cr"]);
+ me.set_debit_or_credit(parent_account, col.date, bal);
+ }
+ });
+ parent = me.parent_map[parent];
+ }
+ }
+ });
},
init_account: function(d) {
// set 0 values for all columns
@@ -191,18 +302,21 @@
var pl_or_bs = this.pl_or_bs;
$.each(this.data, function(i, account) {
- var show = pl_or_bs != "Balance Sheet" ? account.is_pl_account=="Yes" : account.is_pl_account!="Yes";
+ var show = pl_or_bs == "Balance Sheet" ?
+ account.report_type=="Balance Sheet" : account.report_type=="Profit and Loss";
if (show && account.checked && me.apply_filter(account, "company")) {
data.push({
label: account.name,
data: $.map(me.columns, function(col, idx) {
- if(col.formatter==me.currency_formatter && !col.hidden) {
- if (pl_or_bs != "Balance Sheet") {
- return [[dateutil.str_to_obj(col.id).getTime(), account[col.field]],
- [dateutil.user_to_obj(col.name).getTime(), account[col.field]]];
- } else {
- return [[dateutil.user_to_obj(col.name).getTime(), account[col.field]]];
- }
+ if(col.formatter==me.currency_formatter && !col.hidden &&
+ col.balance_type=="Dr") {
+ var bal = account[col.date+"_dr"]||account[col.date+"_cr"];
+ if (pl_or_bs != "Balance Sheet") {
+ return [[dateutil.str_to_obj(col.date).getTime(), bal],
+ [dateutil.user_to_obj(col.date).getTime(), bal]];
+ } else {
+ return [[dateutil.user_to_obj(col.date).getTime(), bal]];
+ }
}
}),
points: {show: true},
diff --git a/erpnext/public/js/account_tree_grid.js b/erpnext/public/js/account_tree_grid.js
index b00f236..ab5ce26 100644
--- a/erpnext/public/js/account_tree_grid.js
+++ b/erpnext/public/js/account_tree_grid.js
@@ -39,17 +39,17 @@
this.columns = [
{id: "name", name: frappe._("Account"), field: "name", width: 300, cssClass: "cell-title",
formatter: this.tree_formatter},
- {id: "opening_debit", name: frappe._("Opening (Dr)"), field: "opening_debit", width: 100,
+ {id: "opening_dr", name: frappe._("Opening (Dr)"), field: "opening_dr", width: 100,
formatter: this.currency_formatter},
- {id: "opening_credit", name: frappe._("Opening (Cr)"), field: "opening_credit", width: 100,
+ {id: "opening_cr", name: frappe._("Opening (Cr)"), field: "opening_cr", width: 100,
formatter: this.currency_formatter},
{id: "debit", name: frappe._("Debit"), field: "debit", width: 100,
formatter: this.currency_formatter},
{id: "credit", name: frappe._("Credit"), field: "credit", width: 100,
formatter: this.currency_formatter},
- {id: "closing_debit", name: frappe._("Closing (Dr)"), field: "closing_debit", width: 100,
+ {id: "closing_dr", name: frappe._("Closing (Dr)"), field: "closing_dr", width: 100,
formatter: this.currency_formatter},
- {id: "closing_credit", name: frappe._("Closing (Cr)"), field: "closing_credit", width: 100,
+ {id: "closing_cr", name: frappe._("Closing (Cr)"), field: "closing_cr", width: 100,
formatter: this.currency_formatter}
];
@@ -132,8 +132,8 @@
if (!this.fiscal_year) return;
$.each(this.data, function(i, v) {
- v.opening_debit = v.opening_credit = v.debit
- = v.credit = v.closing_debit = v.closing_credit = 0;
+ v.opening_dr = v.opening_cr = v.debit
+ = v.credit = v.closing_dr = v.closing_cr = 0;
});
$.each(gl, function(i, v) {
@@ -145,29 +145,31 @@
this.update_groups();
},
update_balances: function(account, posting_date, v) {
+ var bal = flt(account.opening_dr) - flt(account.opening_cr) + flt(v.debit) - flt(v.credit);
// opening
if (posting_date < this.opening_date || v.is_opening === "Yes") {
- if (account.is_pl_account === "Yes" &&
+ if (account.report_type === "Profit and Loss" &&
posting_date <= dateutil.str_to_obj(this.fiscal_year[1])) {
// balance of previous fiscal_year should
// not be part of opening of pl account balance
} else {
- if(in_list(["Asset", "Expense"], account.root_type)) {
- account.opening_debit += (v.debit - v.credit);
- } else {
- account.opening_credit += (v.credit - v.debit);
- }
+ this.set_debit_or_credit(account, "opening", bal);
}
} else if (this.opening_date <= posting_date && posting_date <= this.closing_date) {
// in between
- account.debit += v.debit;
- account.credit += v.credit;
+ account.debit += flt(v.debit);
+ account.credit += flt(v.credit);
}
// closing
- if(in_list(["Asset", "Expense"], account.root_type)) {
- account.closing_debit = account.opening_debit + account.debit - account.credit;
+ this.set_debit_or_credit(account, "closing", bal);
+ },
+ set_debit_or_credit: function(account, field, balance) {
+ if(balance > 0) {
+ account[field+"_dr"] = balance;
+ account[field+"_cr"] = 0;
} else {
- account.closing_credit = account.opening_credit - account.debit + account.credit;
+ account[field+"_cr"] = Math.abs(balance);
+ account[field+"_dr"] = 0;
}
},
update_groups: function() {
@@ -181,9 +183,20 @@
var parent_account = me.item_by_name[parent];
$.each(me.columns, function(c, col) {
if (col.formatter == me.currency_formatter) {
- parent_account[col.field] =
- flt(parent_account[col.field])
- + flt(account[col.field]);
+ if(col.field=="opening_dr") {
+ var bal = flt(parent_account.opening_dr) -
+ flt(parent_account.opening_cr) +
+ flt(account.opening_dr) - flt(account.opening_cr);
+ me.set_debit_or_credit(parent_account, "opening", bal);
+ } else if(col.field=="closing_dr") {
+ var bal = flt(parent_account.closing_dr) -
+ flt(parent_account.closing_cr) +
+ flt(account.closing_dr) - flt(account.closing_cr);
+ me.set_debit_or_credit(parent_account, "closing", bal);
+ } else if(in_list(["debit", "credit"], col.field)) {
+ parent_account[col.field] = flt(parent_account[col.field]) +
+ flt(account[col.field]);
+ }
}
});
parent = me.parent_map[parent];
diff --git a/erpnext/startup/report_data_map.py b/erpnext/startup/report_data_map.py
index 5916634..e614c71 100644
--- a/erpnext/startup/report_data_map.py
+++ b/erpnext/startup/report_data_map.py
@@ -18,8 +18,8 @@
# Accounts
"Account": {
- "columns": ["name", "parent_account", "lft", "rgt", "root_type",
- "is_pl_account", "company", "group_or_ledger"],
+ "columns": ["name", "parent_account", "lft", "rgt", "report_type",
+ "company", "group_or_ledger"],
"conditions": ["docstatus < 2"],
"order_by": "lft",
"links": {