Merge pull request #6126 from rohitwaghchaure/multiyear_financial_statements
[Enhancement] Multi year financial statements
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index 4efd098..23fa762 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -8,7 +8,7 @@
from erpnext.accounts.report.financial_statements import (get_period_list, get_columns, get_data)
def execute(filters=None):
- period_list = get_period_list(filters.fiscal_year, filters.periodicity)
+ period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity)
asset = get_data(filters.company, "Asset", "Debit", period_list, only_current_fiscal_year=False)
liability = get_data(filters.company, "Liability", "Credit", period_list, only_current_fiscal_year=False)
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.js b/erpnext/accounts/report/cash_flow/cash_flow.js
index f5ddd15..455664f 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.js
+++ b/erpnext/accounts/report/cash_flow/cash_flow.js
@@ -2,7 +2,8 @@
// For license information, please see license.txt
frappe.require("assets/erpnext/js/financial_statements.js", function() {
- frappe.query_reports["Cash Flow"] = erpnext.financial_statements;
+ frappe.query_reports["Cash Flow"] = $.extend({},
+ erpnext.financial_statements);
frappe.query_reports["Cash Flow"]["filters"].push({
"fieldname": "accumulated_values",
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.py b/erpnext/accounts/report/cash_flow/cash_flow.py
index 3407526..24c5cd2 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -6,10 +6,11 @@
from frappe import _
from erpnext.accounts.report.financial_statements import (get_period_list, get_columns, get_data)
from erpnext.accounts.report.profit_and_loss_statement.profit_and_loss_statement import get_net_profit_loss
+from erpnext.accounts.utils import get_fiscal_year
def execute(filters=None):
- period_list = get_period_list(filters.fiscal_year, filters.periodicity)
+ period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity)
operation_accounts = {
"section_name": "Operations",
@@ -49,9 +50,9 @@
# compute net profit / loss
income = get_data(filters.company, "Income", "Credit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
expense = get_data(filters.company, "Expense", "Debit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
@@ -98,33 +99,39 @@
return columns, data
-
def get_account_type_based_data(company, account_type, period_list, accumulated_values):
data = {}
total = 0
for period in period_list:
+ start_date = get_start_date(period, accumulated_values)
gl_sum = frappe.db.sql_list("""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
and account in ( SELECT name FROM tabAccount WHERE account_type = %s)
- """, (company, period["year_start_date"] if accumulated_values else period['from_date'],
+ """, (company, start_date if accumulated_values else period['from_date'],
period['to_date'], account_type))
-
+
if gl_sum and gl_sum[0]:
amount = gl_sum[0]
if account_type == "Depreciation":
amount *= -1
else:
amount = 0
-
+
total += amount
data.setdefault(period["key"], amount)
-
+
data["total"] = total
return data
+def get_start_date(period, accumulated_values):
+ start_date = period["year_start_date"]
+ if accumulated_values:
+ start_date = get_fiscal_year(period.to_date)[1]
+
+ return start_date
def add_total_row_account(out, data, label, period_list, currency):
total_row = {
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index c930952..f6968a1 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -3,61 +3,71 @@
from __future__ import unicode_literals
import frappe
+import math
from frappe import _
-from frappe.utils import (flt, getdate, get_first_day, get_last_day,
- add_months, add_days, formatdate)
+from frappe.utils import (flt, getdate, get_first_day, get_last_day, date_diff,
+ add_months, add_days, formatdate, cint)
-def get_period_list(fiscal_year, periodicity):
+def get_period_list(from_fiscal_year, to_fiscal_year, periodicity):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
- fy_start_end_date = frappe.db.get_value("Fiscal Year", fiscal_year, ["year_start_date", "year_end_date"])
- if not fy_start_end_date:
- frappe.throw(_("Fiscal Year {0} not found.").format(fiscal_year))
+ from_fy_start_end_date = frappe.db.get_value("Fiscal Year", from_fiscal_year, ["year_start_date", "year_end_date"])
+ to_fy_start_end_date = frappe.db.get_value("Fiscal Year", to_fiscal_year, ["year_start_date", "year_end_date"])
+
+ if not from_fy_start_end_date:
+ frappe.throw(_("Start Year {0} not found.").format(from_fiscal_year))
+
+ if not to_fy_start_end_date:
+ frappe.throw(_("End Year {0} not found.").format(to_fiscal_year))
# start with first day, so as to avoid year to_dates like 2-April if ever they occur]
- year_start_date = get_first_day(getdate(fy_start_end_date[0]))
- year_end_date = getdate(fy_start_end_date[1])
-
- if periodicity == "Yearly":
- period_list = [frappe._dict({"from_date": year_start_date, "to_date": year_end_date,
- "key": fiscal_year, "label": fiscal_year})]
- else:
- months_to_add = {
- "Half-Yearly": 6,
- "Quarterly": 3,
- "Monthly": 1
- }[periodicity]
+ year_start_date = getdate(from_fy_start_end_date[0])
+ year_end_date = getdate(to_fy_start_end_date[1])
- period_list = []
+ validate_fiscal_year(year_start_date, year_end_date)
- start_date = year_start_date
- for i in xrange(12 / months_to_add):
- period = frappe._dict({
- "from_date": start_date
- })
- to_date = add_months(start_date, months_to_add)
- start_date = to_date
-
- if to_date == get_first_day(to_date):
- # if to_date is the first day, get the last day of previous month
- to_date = add_days(to_date, -1)
- else:
- # to_date should be the last day of the new to_date's month
- to_date = get_last_day(to_date)
+ months_to_add = {
+ "Yearly": 12,
+ "Half-Yearly": 6,
+ "Quarterly": 3,
+ "Monthly": 1
+ }[periodicity]
- if to_date <= year_end_date:
- # the normal case
- period.to_date = to_date
- else:
- # if a fiscal year ends before a 12 month period
- period.to_date = year_end_date
-
- period_list.append(period)
-
- if period.to_date == year_end_date:
- break
-
+ period_list = []
+
+ start_date = year_start_date
+ months = get_months(year_start_date, year_end_date)
+
+ for i in xrange(months / months_to_add):
+ period = frappe._dict({
+ "from_date": start_date
+ })
+
+ to_date = add_months(start_date, months_to_add)
+ start_date = to_date
+
+ if to_date == get_first_day(to_date):
+ # if to_date is the first day, get the last day of previous month
+ to_date = add_days(to_date, -1)
+ else:
+ # to_date should be the last day of the new to_date's month
+ to_date = get_last_day(to_date)
+
+ if to_date <= year_end_date:
+ # the normal case
+ period.to_date = to_date
+ else:
+ # if a fiscal year ends before a 12 month period
+ period.to_date = year_end_date
+
+ period.to_date_fiscal_year = get_date_fiscal_year(period.to_date)
+
+ period_list.append(period)
+
+ if period.to_date == year_end_date:
+ break
+
# common processing
for opts in period_list:
key = opts["to_date"].strftime("%b_%Y").lower()
@@ -75,6 +85,14 @@
return period_list
+def validate_fiscal_year(start_date, end_date):
+ if date_diff(end_date, start_date) <= 0:
+ frappe.throw(_("End Year cannot be before Start Year"))
+
+def get_months(start_date, end_date):
+ diff = (12 * end_date.year + end_date.month) - (12 * start_date.year + start_date.month)
+ return diff + 1
+
def get_label(periodicity, from_date, to_date):
if periodicity=="Yearly":
if formatdate(from_date, "YYYY") == formatdate(to_date, "YYYY"):
@@ -87,7 +105,8 @@
return label
def get_data(company, root_type, balance_must_be, period_list,
- accumulated_values=1, only_current_fiscal_year=True, ignore_closing_entries=False):
+ accumulated_values=1, only_current_fiscal_year=True, ignore_closing_entries=False,
+ ignore_accumulated_values_for_fy=False):
accounts = get_accounts(company, root_type)
if not accounts:
return None
@@ -106,7 +125,7 @@
root.lft, root.rgt,
gl_entries_by_account, ignore_closing_entries=ignore_closing_entries)
- calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values)
+ calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values, ignore_accumulated_values_for_fy)
accumulate_values_into_parents(accounts, accounts_by_name, period_list, accumulated_values)
out = prepare_data(accounts, balance_must_be, period_list, company_currency)
out = filter_out_zero_value_rows(out, parent_children_map)
@@ -116,18 +135,26 @@
return out
-def calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values):
+def calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values, ignore_accumulated_values_for_fy):
for entries in gl_entries_by_account.values():
for entry in entries:
d = accounts_by_name.get(entry.account)
for period in period_list:
# check if posting date is within the period
+
+ fiscal_year = get_date_fiscal_year(entry.posting_date)
if entry.posting_date <= period.to_date:
- if accumulated_values or entry.posting_date >= period.from_date:
+ if (accumulated_values or entry.posting_date >= period.from_date) and \
+ (fiscal_year == period.to_date_fiscal_year or not ignore_accumulated_values_for_fy):
d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
-
+
if entry.posting_date < period_list[0].year_start_date:
d["opening_balance"] = d.get("opening_balance", 0.0) + flt(entry.debit) - flt(entry.credit)
+
+def get_date_fiscal_year(date):
+ from erpnext.accounts.utils import get_fiscal_year
+
+ return get_fiscal_year(date)[0]
def accumulate_values_into_parents(accounts, accounts_by_name, period_list, accumulated_values):
"""accumulate children's values in parent accounts"""
diff --git a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
index 62d6e69..3629d4d 100644
--- a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
+++ b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.py
@@ -8,12 +8,12 @@
from erpnext.accounts.report.financial_statements import (get_period_list, get_columns, get_data)
def execute(filters=None):
- period_list = get_period_list(filters.fiscal_year, filters.periodicity)
+ period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year, filters.periodicity)
income = get_data(filters.company, "Income", "Credit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
expense = get_data(filters.company, "Expense", "Debit", period_list,
- accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
@@ -54,7 +54,7 @@
return net_profit_loss
def get_chart_data(filters, columns, income, expense, net_profit_loss):
- x_intervals = ['x'] + [d.get("label") for d in columns[2:-1]]
+ x_intervals = ['x'] + [d.get("label") for d in columns[2:]]
income_data, expense_data, net_profit = [], [], []
diff --git a/erpnext/public/js/financial_statements.js b/erpnext/public/js/financial_statements.js
index c1e53a2..4127e6e 100644
--- a/erpnext/public/js/financial_statements.js
+++ b/erpnext/public/js/financial_statements.js
@@ -1,37 +1,7 @@
frappe.provide("erpnext.financial_statements");
erpnext.financial_statements = {
- "filters": [
- {
- "fieldname":"company",
- "label": __("Company"),
- "fieldtype": "Link",
- "options": "Company",
- "default": frappe.defaults.get_user_default("Company"),
- "reqd": 1
- },
- {
- "fieldname":"fiscal_year",
- "label": __("Fiscal Year"),
- "fieldtype": "Link",
- "options": "Fiscal Year",
- "default": frappe.defaults.get_user_default("fiscal_year"),
- "reqd": 1
- },
- {
- "fieldname": "periodicity",
- "label": __("Periodicity"),
- "fieldtype": "Select",
- "options": [
- { "value": "Monthly", "label": __("Monthly") },
- { "value": "Quarterly", "label": __("Quarterly") },
- { "value": "Half-Yearly", "label": __("Half-Yearly") },
- { "value": "Yearly", "label": __("Yearly") }
- ],
- "default": "Monthly",
- "reqd": 1
- }
- ],
+ "filters": get_filters(),
"formatter": function(row, cell, value, columnDef, dataContext, default_formatter) {
if (columnDef.df.fieldname=="account") {
value = dataContext.account_name;
@@ -71,6 +41,8 @@
"initial_depth": 3,
onload: function(report) {
// dropdown for links to other financial statements
+ erpnext.financial_statements.filters = get_filters()
+
report.page.add_inner_button(__("Balance Sheet"), function() {
var filters = report.get_values();
frappe.set_route('query-report', 'Balance Sheet', {company: filters.company});
@@ -85,3 +57,45 @@
}, 'Financial Statements');
}
};
+
+function get_filters(){
+ return [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_fiscal_year",
+ "label": __("Start Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_fiscal_year",
+ "label": __("End Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1
+ },
+ {
+ "fieldname": "periodicity",
+ "label": __("Periodicity"),
+ "fieldtype": "Select",
+ "options": [
+ { "value": "Monthly", "label": __("Monthly") },
+ { "value": "Quarterly", "label": __("Quarterly") },
+ { "value": "Half-Yearly", "label": __("Half-Yearly") },
+ { "value": "Yearly", "label": __("Yearly") }
+ ],
+ "default": "Monthly",
+ "reqd": 1
+ }
+ ]
+}