Balance Sheet
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.html b/erpnext/accounts/report/balance_sheet/balance_sheet.html
new file mode 100644
index 0000000..a6a33f5
--- /dev/null
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.html
@@ -0,0 +1,46 @@
+<style>
+ .balance-sheet-important td {
+ font-weight: bold;
+ }
+
+ .balance-sheet-blank-row td {
+ height: 37px;
+ }
+</style>
+
+<h2 class="text-center">{%= __("Balance Sheet") %}</h2>
+<h4 class="text-center">{%= filters.fiscal_year %}</h3>
+<hr>
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th style="width: 40%"></th>
+ {% for(var i=2, l=report.columns.length; i<l; i++) { %}
+ <th class="text-right">{%= report.columns[i].label %}</th>
+ {% } %}
+ </tr>
+ </thead>
+ <tbody>
+ {% for(var j=0, k=data.length; j<k; j++) { %}
+ {%
+ var row = data[j];
+ var row_class = data[j].parent_account ? "" : "balance-sheet-important";
+ row_class += data[j].account_name ? "" : " balance-sheet-blank-row";
+ %}
+ <tr class="{%= row_class %}">
+ <td>
+ <span style="padding-left: {%= cint(data[j].indent) * 7 %}%">{%= row.account_name %}</span>
+ </td>
+ {% for(var i=2, l=report.columns.length; i<l; i++) { %}
+ <td class="text-right">
+ {% var fieldname = report.columns[i].field; %}
+ {% if (!is_null(row[fieldname])) { %}
+ {%= format_currency(row[fieldname]) %}
+ {% } %}
+ </td>
+ {% } %}
+ </tr>
+ {% } %}
+ </tbody>
+</table>
+<p class="text-right text-muted">Printed On {%= dateutil.str_to_user(dateutil.get_datetime_as_string()) %}</p>
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.js b/erpnext/accounts/report/balance_sheet/balance_sheet.js
index 9bdd298..378a687 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.js
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.js
@@ -1,7 +1,9 @@
// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
// License: GNU General Public License v3. See license.txt
-frappe.query_reports["Balance Sheet"] = {
+frappe.provide("erpnext.balance_sheet");
+
+erpnext.balance_sheet = frappe.query_reports["Balance Sheet"] = {
"filters": [
{
"fieldname":"company",
@@ -26,6 +28,44 @@
"options": "Yearly\nQuarterly\nMonthly",
"default": "Yearly",
"reqd": 1
+ },
+ {
+ "fieldname": "depth",
+ "label": __("Depth"),
+ "fieldtype": "Select",
+ "options": "3\n4\n5",
+ "default": "3"
}
- ]
+ ],
+ "formatter": function(row, cell, value, columnDef, dataContext) {
+ if (columnDef.df.fieldname=="account") {
+ var link = $("<a></a>")
+ .text(dataContext.account_name)
+ .attr("onclick", 'erpnext.balance_sheet.open_general_ledger("' + dataContext.account + '")');
+
+ var span = $("<span></span>")
+ .css("padding-left", (cint(dataContext.indent) * 21) + "px")
+ .append(link);
+
+ value = span.wrap("<p></p>").parent().html();
+
+ } else {
+ value = frappe.query_reports["Balance Sheet"].default_formatter(row, cell, value, columnDef, dataContext);
+ }
+
+ if (!dataContext.parent_account) {
+ value = $(value).css("font-weight", "bold").wrap("<p></p>").parent().html();
+ }
+
+ return value;
+ },
+ "open_general_ledger": function(account) {
+ if (!account) return;
+
+ frappe.route_options = {
+ "account": account,
+ "company": frappe.query_report.filters_by_name.company.get_value()
+ };
+ frappe.set_route("query-report", "General Ledger");
+ }
}
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index dc71e92..3bf424c 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -2,40 +2,76 @@
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
+import babel.dates
import frappe
-from frappe.utils import cstr, flt
+from frappe.utils import (cstr, flt, cint,
+ getdate, get_first_day, get_last_day, add_months, add_days, now_datetime)
from frappe import _
def execute(filters=None):
company = filters.company
fiscal_year = filters.fiscal_year
- depth = 3
- end_date = frappe.db.get_value("Fiscal Year", fiscal_year, "year_end_date")
+ depth = cint(filters.depth) or 3
+ start_date, end_date = frappe.db.get_value("Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
+ period_list = get_period_list(start_date, end_date, filters.get("periodicity") or "Yearly", fiscal_year)
- for root_type, balance_must_be in (("Asset", "Debit"), ("Liability", "Credit"), ("Equity", "Credit")):
+ out = []
+ for (root_type, balance_must_be) in (("Asset", "Debit"), ("Liability", "Credit"), ("Equity", "Credit")):
+ data = []
accounts, account_gl_entries = get_accounts_and_gl_entries(root_type, company, end_date)
if accounts:
accounts, accounts_map = filter_accounts(accounts, depth=depth)
for d in accounts:
- d.debit = d.credit = 0
for account_name in ([d.name] + (d.invisible_children or [])):
for each in account_gl_entries.get(account_name, []):
- d.debit += flt(each.debit)
- d.credit += flt(each.credit)
+ for period_start_date, period_end_date, period_key, period_label in period_list:
+ each.posting_date = getdate(each.posting_date)
+
+ # check if posting date is within the period
+ if ((not period_start_date or (each.posting_date >= period_start_date))
+ and (each.posting_date <= period_end_date)):
+
+ d[period_key] = d.get(period_key, 0.0) + flt(each.debit) - flt(each.credit)
for d in reversed(accounts):
if d.parent_account:
- accounts_map[d.parent_account]["debit"] += d.debit
- accounts_map[d.parent_account]["credit"] += d.credit
+ for period_start_date, period_end_date, period_key, period_label in period_list:
+ accounts_map[d.parent_account][period_key] = accounts_map[d.parent_account].get(period_key, 0.0) + d.get(period_key, 0.0)
- for d in accounts:
- d.balance = d["debit"] - d["credit"]
- if d.balance:
- d.balance *= (1 if balance_must_be=="Debit" else -1)
- print (" " * d["indent"] * 2) + d["account_name"], d["balance"], balance_must_be
+ for i, d in enumerate(accounts):
+ has_value = False
+ row = {"account_name": d["account_name"], "account": d["name"], "indent": d["indent"], "parent_account": d["parent_account"]}
+ for period_start_date, period_end_date, period_key, period_label in period_list:
+ if d.get(period_key):
+ d[period_key] *= (1 if balance_must_be=="Debit" else -1)
- return [], []
+ row[period_key] = d.get(period_key, 0.0)
+ if row[period_key]:
+ has_value = True
+
+ if has_value:
+ data.append(row)
+
+ if data:
+ row = {"account_name": _("Total ({0})").format(balance_must_be), "account": None}
+ for period_start_date, period_end_date, period_key, period_label in period_list:
+ if period_key in data[0]:
+ row[period_key] = data[0].get(period_key, 0.0)
+ data[0][period_key] = ""
+
+ data.append(row)
+
+ # blank row after Total
+ data.append({})
+
+ out.extend(data)
+
+ columns = [{"fieldname": "account", "label": _("Account"), "fieldtype": "Link", "options": "Account", "width": 300}]
+ for period_start_date, period_end_date, period_key, period_label in period_list:
+ columns.append({"fieldname": period_key, "label": period_label, "fieldtype": "Currency", "width": 150})
+
+ return columns, out
def get_accounts_and_gl_entries(root_type, company, end_date):
# root lft, rgt
@@ -97,3 +133,57 @@
add_to_data(None, 0)
return data, accounts_map
+
+def get_period_list(start_date, end_date, periodicity, fiscal_year):
+ """Get a list of tuples that represents (period_start_date, period_end_date, period_key)
+ Periodicity can be (Yearly, Quarterly, Monthly)"""
+
+ start_date = getdate(start_date)
+ end_date = getdate(end_date)
+ today = now_datetime().date()
+
+ if periodicity == "Yearly":
+ period_list = [(None, end_date, fiscal_year, fiscal_year)]
+ else:
+ months_to_add = {
+ "Half-yearly": 6,
+ "Quarterly": 3,
+ "Monthly": 1
+ }[periodicity]
+
+ period_list = []
+
+ # start with first day, so as to avoid year start dates like 2-April if every they occur
+ next_date = get_first_day(start_date)
+
+ for i in xrange(12 / months_to_add):
+ next_date = add_months(next_date, months_to_add)
+
+ if next_date == get_first_day(next_date):
+ # if first day, get the last day of previous month
+ next_date = add_days(next_date, -1)
+ else:
+ # get the last day of the month
+ next_date = get_last_day(next_date)
+
+ # checking in the middle of the fiscal year? don't show future periods
+ if next_date > today:
+ break
+
+ elif next_date <= end_date:
+ key = next_date.strftime("%b_%Y").lower()
+ label = babel.dates.format_date(next_date, "MMM YYYY", locale=(frappe.local.lang or "").replace("-", "_"))
+ period_list.append((None, next_date, key, label))
+
+ # if it ends before a full year
+ if next_date == end_date:
+ break
+
+ else:
+ # if it ends before a full year
+ key = end_date.strftime("%b_%Y").lower()
+ label = babel.dates.format_date(end_date, "MMM YYYY", locale=(frappe.local.lang or "").replace("-", "_"))
+ period_list.append((None, end_date, key, label))
+ break
+
+ return period_list
diff --git a/erpnext/config/accounts.py b/erpnext/config/accounts.py
index 2d757f1..0e3e2d4 100644
--- a/erpnext/config/accounts.py
+++ b/erpnext/config/accounts.py
@@ -196,6 +196,12 @@
"is_query_report": True
},
{
+ "type": "report",
+ "name": "Balance Sheet",
+ "doctype": "GL Entry",
+ "is_query_report": True
+ },
+ {
"type": "page",
"name": "financial-analytics",
"label": _("Financial Analytics"),