[report] Financial Statements: Accumulated periodic balance based on filters
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index 62d1940..fd88afd 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -8,11 +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, from_beginning=True)
-
- asset = get_data(filters.company, "Asset", "Debit", period_list, filters.accumulated_value)
- liability = get_data(filters.company, "Liability", "Credit", period_list, filters.accumulated_value)
- equity = get_data(filters.company, "Equity", "Credit", period_list, filters.accumulated_value)
+ period_list = get_period_list(filters.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)
+ equity = get_data(filters.company, "Equity", "Credit", period_list, only_current_fiscal_year=False)
+
provisional_profit_loss = get_provisional_profit_loss(asset, liability, equity,
period_list, filters.company)
@@ -23,13 +24,13 @@
if provisional_profit_loss:
data.append(provisional_profit_loss)
- columns = get_columns(filters.periodicity,period_list,filters.accumulated_value)
+ columns = get_columns(filters.periodicity, period_list, company=filters.company)
return columns, data
def get_provisional_profit_loss(asset, liability, equity, period_list, company):
if asset and (liability or equity):
- total_column=0
+ total=0
provisional_profit_loss = {
"account_name": "'" + _("Provisional Profit / Loss (Credit)") + "'",
"account": None,
@@ -51,8 +52,8 @@
if provisional_profit_loss[period.key]:
has_value = True
- total_column=total_column+provisional_profit_loss[period.key]
- provisional_profit_loss["total"]=total_column
+ total += flt(provisional_profit_loss[period.key])
+ provisional_profit_loss["total"] = total
if has_value:
return provisional_profit_loss
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.js b/erpnext/accounts/report/cash_flow/cash_flow.js
index c8fb04c..464bd17 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.js
+++ b/erpnext/accounts/report/cash_flow/cash_flow.js
@@ -4,3 +4,9 @@
frappe.require("assets/erpnext/js/financial_statements.js");
frappe.query_reports["Cash Flow"] = erpnext.financial_statements;
+
+frappe.query_reports["Cash Flow"]["filters"].push({
+ "fieldname": "accumulated_values",
+ "label": __("Accumulated Values"),
+ "fieldtype": "Check"
+})
\ No newline at end of file
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.py b/erpnext/accounts/report/cash_flow/cash_flow.py
index 1fda16a..681e563 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -48,12 +48,14 @@
cash_flow_accounts.append(financing_accounts)
# compute net profit / loss
- income = get_data(filters.company, "Income", "Credit", period_list, ignore_closing_entries=True)
- expense = get_data(filters.company, "Expense", "Debit", period_list, ignore_closing_entries=True)
+ income = get_data(filters.company, "Income", "Credit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ expense = get_data(filters.company, "Expense", "Debit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
data = []
-
company_currency = frappe.db.get_value("Company", filters.company, "default_currency")
for cash_flow_account in cash_flow_accounts:
@@ -77,7 +79,8 @@
section_data.append(net_profit_loss)
for account in cash_flow_account['account_types']:
- account_data = get_account_type_based_data(filters.company, account['account_type'], period_list)
+ account_data = get_account_type_based_data(filters.company,
+ account['account_type'], period_list, filters.accumulated_values)
account_data.update({
"account_name": account['label'],
"indent": 1,
@@ -91,13 +94,14 @@
period_list, company_currency)
add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
- columns = get_columns(period_list)
+ columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
return columns, data
-def get_account_type_based_data(company, account_type, period_list):
+def get_account_type_based_data(company, account_type, period_list, accumulated_values):
data = {}
+ total = 0
for period in period_list:
gl_sum = frappe.db.sql_list("""
select sum(credit) - sum(debit)
@@ -105,7 +109,8 @@
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['from_date'], period['to_date'], account_type))
+ """, (company, period["year_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]
@@ -113,12 +118,11 @@
amount *= -1
else:
amount = 0
+
+ total += amount
+ data.setdefault(period["key"], amount)
- data.update({
- "from_date": period['from_date'],
- "to_date": period['to_date'],
- period["key"]: amount
- })
+ data["total"] = total
return data
@@ -128,12 +132,14 @@
"account": None,
"currency": currency
}
-
for row in data:
if row.get("parent_account"):
for period in period_list:
total_row.setdefault(period.key, 0.0)
total_row[period.key] += row.get(period.key, 0.0)
+
+ total_row.setdefault("total", 0.0)
+ total_row["total"] += row["total"]
out.append(total_row)
out.append({})
\ No newline at end of file
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 2852063..d84f18f 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -3,23 +3,25 @@
from __future__ import unicode_literals
import frappe
-from frappe import _, _dict
-from frappe.utils import (cint, flt, getdate, get_first_day, get_last_day,
+from frappe import _
+from frappe.utils import (flt, getdate, get_first_day, get_last_day,
add_months, add_days, formatdate)
-def get_period_list(fiscal_year, periodicity, from_beginning=False):
- """Get a list of dict {"to_date": to_date, "key": key, "label": label}
+def get_period_list(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))
- start_date = getdate(fy_start_end_date[0])
- end_date = getdate(fy_start_end_date[1])
-
+ # 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 = [_dict({"to_date": end_date, "key": fiscal_year, "label": fiscal_year})]
+ 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,
@@ -29,12 +31,14 @@
period_list = []
- # start with first day, so as to avoid year to_dates like 2-April if ever they occur
- to_date = get_first_day(start_date)
-
+ start_date = year_start_date
for i in xrange(12 / months_to_add):
- to_date = add_months(to_date, 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)
@@ -42,59 +46,48 @@
# to_date should be the last day of the new to_date's month
to_date = get_last_day(to_date)
- if to_date <= end_date:
+ if to_date <= year_end_date:
# the normal case
- period_list.append(_dict({ "to_date": to_date }))
-
- # if it ends before a full year
- if to_date == end_date:
- break
-
+ period.to_date = to_date
else:
# if a fiscal year ends before a 12 month period
- period_list.append(_dict({ "to_date": end_date }))
+ period.to_date = year_end_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()
if periodicity == "Monthly":
label = formatdate(opts["to_date"], "MMM YYYY")
else:
- label = get_label(periodicity,opts["to_date"])
+ label = get_label(periodicity, opts["from_date"], opts["to_date"])
+
opts.update({
"key": key.replace(" ", "_").replace("-", "_"),
"label": label,
- "year_start_date": start_date,
- "year_end_date": end_date
+ "year_start_date": year_start_date,
+ "year_end_date": year_end_date
})
- if from_beginning:
- # set start date as None for all fiscal periods, used in case of Balance Sheet
- opts["from_date"] = None
- else:
- opts["from_date"] = start_date
-
return period_list
-def get_label(periodicity,to_date):
+def get_label(periodicity, from_date, to_date):
if periodicity=="Yearly":
- months_to_add=-11
- elif periodicity=="Half-Yearly":
- months_to_add=-5
- elif periodicity=="Quarterly":
- months_to_add=-2
- from_date = add_months(to_date, months_to_add)
- if periodicity=="Yearly":
-
- label = formatdate(from_date, "YYYY")+"-"+formatdate(to_date, "YYYY")
+ if formatdate(from_date, "YYYY") == formatdate(to_date, "YYYY"):
+ label = formatdate(from_date, "YYYY")
+ else:
+ label = formatdate(from_date, "YYYY") + "-" + formatdate(to_date, "YYYY")
else:
- label = from_date.strftime("%b")+"-"+formatdate(to_date, "MMM YYYY")
+ label = formatdate(from_date, "MMM YY") + "-" + formatdate(to_date, "MMM YY")
return label
-def get_data(company, root_type, balance_must_be, period_list, accumulated_value, ignore_closing_entries=False):
- accumulated_value_ischecked = cint(accumulated_value)
+def get_data(company, root_type, balance_must_be, period_list,
+ accumulated_values=1, only_current_fiscal_year=True, ignore_closing_entries=False):
accounts = get_accounts(company, root_type)
if not accounts:
return None
@@ -106,37 +99,39 @@
gl_entries_by_account = {}
for root in frappe.db.sql("""select lft, rgt from tabAccount
where root_type=%s and ifnull(parent_account, '') = ''""", root_type, as_dict=1):
- set_gl_entries_by_account(company, period_list[0]["from_date"],
- period_list[-1]["to_date"],root.lft, root.rgt, gl_entries_by_account,
- ignore_closing_entries=ignore_closing_entries)
+
+ set_gl_entries_by_account(company,
+ period_list[0]["year_start_date"] if only_current_fiscal_year else None,
+ period_list[-1]["to_date"],
+ 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_value_ischecked)
- accumulate_values_into_parents(accounts, accounts_by_name, period_list)
+ calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values)
+ accumulate_values_into_parents(accounts, accounts_by_name, period_list, accumulated_values)
out = prepare_data(accounts, balance_must_be, period_list, company_currency)
-
+
if out:
add_total_row(out, balance_must_be, period_list, company_currency)
return out
-def calculate_values(accounts_by_name, gl_entries_by_account, period_list):
+def calculate_values(accounts_by_name, gl_entries_by_account, period_list, accumulated_values):
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
if entry.posting_date <= period.to_date:
- d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
+ if accumulated_values or entry.posting_date >= period.from_date:
+ d[period.key] = d.get(period.key, 0.0) + flt(entry.debit) - flt(entry.credit)
-def accumulate_values_into_parents(accounts, accounts_by_name, period_list, accumulated_value_ischecked):
+def accumulate_values_into_parents(accounts, accounts_by_name, period_list, accumulated_values):
"""accumulate children's values in parent accounts"""
for d in reversed(accounts):
if d.parent_account:
for period in period_list:
accounts_by_name[d.parent_account][period.key] = accounts_by_name[d.parent_account].get(period.key, 0.0) + \
d.get(period.key, 0.0)
- if accumulated_value_ischecked == 0:
- break
def prepare_data(accounts, balance_must_be, period_list, company_currency):
out = []
@@ -146,14 +141,14 @@
for d in accounts:
# add to output
has_value = False
- total_column=0
+ total = 0
row = {
"account_name": d.account_name,
"account": d.name,
"parent_account": d.parent_account,
"indent": flt(d.indent),
- "from_date": year_start_date,
- "to_date": year_end_date,
+ "year_start_date": year_start_date,
+ "year_end_date": year_end_date,
"currency": company_currency
}
for period in period_list:
@@ -166,16 +161,15 @@
if abs(row[period.key]) >= 0.005:
# ignore zero values
has_value = True
- total_column=total_column+row[period.key]
+ total += flt(row[period.key])
if has_value:
- row["total"]=total_column
+ row["total"] = total
out.append(row)
return out
def add_total_row(out, balance_must_be, period_list, company_currency):
- total_column=0
total_row = {
"account_name": "'" + _("Total ({0})").format(balance_must_be) + "'",
"account": None,
@@ -187,13 +181,12 @@
for period in period_list:
total_row.setdefault(period.key, 0.0)
total_row[period.key] += row.get(period.key, 0.0)
- total_column=total_column+total_row[period.key]
- out[0][period.key] = ""
- out[0]["total"] = ""
+ row[period.key] = ""
+
+ total_row.setdefault("total", 0.0)
+ total_row["total"] += flt(row["total"])
+ row["total"] = ""
- total_row["total"]=total_column
- out.append(total_row)
-
out.append(total_row)
# blank row after Total
@@ -275,8 +268,7 @@
return gl_entries_by_account
-def get_columns(periodicity,period_list,accumulated_value):
- accumulated_value_ischecked = cint(accumulated_value)
+def get_columns(periodicity, period_list, accumulated_values=1, company=None):
columns = [{
"fieldname": "account",
"label": _("Account"),
@@ -284,15 +276,24 @@
"options": "Account",
"width": 300
}]
+ if company:
+ columns.append({
+ "fieldname": "currency",
+ "label": _("Currency"),
+ "fieldtype": "Link",
+ "options": "Currency",
+ "hidden": 1
+ })
for period in period_list:
columns.append({
"fieldname": period.key,
"label": period.label,
"fieldtype": "Currency",
+ "options": "currency",
"width": 150
})
if periodicity!="Yearly":
- if accumulated_value_ischecked == 0:
+ if not accumulated_values:
columns.append({
"fieldname": "total",
"label": _("Total"),
diff --git a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
index 9c70a65..fcbc469 100644
--- a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
+++ b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
@@ -4,3 +4,9 @@
frappe.require("assets/erpnext/js/financial_statements.js");
frappe.query_reports["Profit and Loss Statement"] = erpnext.financial_statements;
+
+frappe.query_reports["Profit and Loss Statement"]["filters"].push({
+ "fieldname": "accumulated_values",
+ "label": __("Accumulated Values"),
+ "fieldtype": "Check"
+})
\ No newline at end of file
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 42dead4..7c33db2 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
@@ -10,8 +10,11 @@
def execute(filters=None):
period_list = get_period_list(filters.fiscal_year, filters.periodicity)
- income = get_data(filters.company, "Income", "Credit", period_list, filters.accumulated_value, ignore_closing_entries=True)
- expense = get_data(filters.company, "Expense", "Debit", period_list, filters.accumulated_value, ignore_closing_entries=True)
+ income = get_data(filters.company, "Income", "Credit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+ expense = get_data(filters.company, "Expense", "Debit", period_list,
+ accumulated_values=filters.accumulated_values, ignore_closing_entries=True)
+
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
data = []
@@ -20,12 +23,13 @@
if net_profit_loss:
data.append(net_profit_loss)
- columns = get_columns(filters.periodicity,period_list,filters.accumulated_value)
+ columns = get_columns(filters.periodicity, period_list, filters.accumulated_values, filters.company)
return columns, data
def get_net_profit_loss(income, expense, period_list, company):
if income and expense:
+ total = 0
net_profit_loss = {
"account_name": "'" + _("Net Profit / Loss") + "'",
"account": None,
@@ -41,9 +45,8 @@
if net_profit_loss[period.key]:
has_value=True
- total_column=total_column+net_profit_loss[period.key]
- net_profit_loss["total"]=total_column
+ total += flt(net_profit_loss[period.key])
+ net_profit_loss["total"] = total
if has_value:
-
return net_profit_loss
diff --git a/erpnext/public/js/financial_statements.js b/erpnext/public/js/financial_statements.js
index f980e2e..07d494a 100644
--- a/erpnext/public/js/financial_statements.js
+++ b/erpnext/public/js/financial_statements.js
@@ -30,24 +30,14 @@
],
"default": "Yearly",
"reqd": 1
- },
- {
- "fieldname": "accumulated_value",
- "label": __("Accumulated Values"),
- "fieldtype": "Check"
- }
- ,
- {
- "fieldname": "accumulated_value",
- "label": __("Accumulated Values"),
- "fieldtype": "Check"
- }
+ }
],
"formatter": function(row, cell, value, columnDef, dataContext, default_formatter) {
if (columnDef.df.fieldname=="account") {
value = dataContext.account_name;
- columnDef.df.link_onclick = "erpnext.financial_statements.open_general_ledger(" + JSON.stringify(dataContext) + ")";
+ columnDef.df.link_onclick =
+ "erpnext.financial_statements.open_general_ledger(" + JSON.stringify(dataContext) + ")";
columnDef.df.is_tree = true;
}
@@ -70,8 +60,8 @@
frappe.route_options = {
"account": data.account,
"company": frappe.query_report.filters_by_name.company.get_value(),
- "from_date": data.from_date,
- "to_date": data.to_date
+ "from_date": data.year_start_date,
+ "to_date": data.year_end_date
};
frappe.set_route("query-report", "General Ledger");
},