feat: Date range in financial statements (#21020)
* feat: Date range in financial statements
* feat: Add date range filter in consolidated financial statement
* fix: Handle API changes in query_report
Co-authored-by: Saqib <nextchamp.saqib@gmail.com>
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index cb1fdc1..a858c19 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -9,6 +9,7 @@
def execute(filters=None):
period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
+ filters.period_start_date, filters.period_end_date, filters.filter_based_on,
filters.periodicity, company=filters.company)
currency = filters.presentation_currency or frappe.get_cached_value('Company', filters.company, "default_currency")
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.js b/erpnext/accounts/report/cash_flow/cash_flow.js
index 904874f..e5d0c89 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.js
+++ b/erpnext/accounts/report/cash_flow/cash_flow.js
@@ -9,7 +9,7 @@
// filter. It won't be used in cash flow for now so we pop it. Please take
// of this if you are working here.
- frappe.query_reports["Cash Flow"]["filters"].splice(5, 1);
+ frappe.query_reports["Cash Flow"]["filters"].splice(8, 1);
frappe.query_reports["Cash Flow"]["filters"].push(
{
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.py b/erpnext/accounts/report/cash_flow/cash_flow.py
index be6e93c..cf0946b 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -17,7 +17,8 @@
return execute_custom(filters=filters)
period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
- filters.periodicity, filters.accumulated_values, filters.company)
+ filters.period_start_date, filters.period_end_date, filters.filter_based_on,
+ filters.periodicity, company=filters.company)
cash_flow_accounts = get_cash_flow_accounts()
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
index 92c5ee9..38fd5fa 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
@@ -13,6 +13,39 @@
"reqd": 1
},
{
+ "fieldname":"filter_based_on",
+ "label": __("Filter Based On"),
+ "fieldtype": "Select",
+ "options": ["Fiscal Year", "Date Range"],
+ "default": ["Fiscal Year"],
+ "reqd": 1,
+ on_change: function() {
+ let filter_based_on = frappe.query_report.get_filter_value('filter_based_on');
+ frappe.query_report.toggle_filter_display('from_fiscal_year', filter_based_on === 'Date Range');
+ frappe.query_report.toggle_filter_display('to_fiscal_year', filter_based_on === 'Date Range');
+ frappe.query_report.toggle_filter_display('period_start_date', filter_based_on === 'Fiscal Year');
+ frappe.query_report.toggle_filter_display('period_end_date', filter_based_on === 'Fiscal Year');
+
+ frappe.query_report.refresh();
+ }
+ },
+ {
+ "fieldname":"period_start_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.nowdate(),
+ "hidden": 1,
+ "reqd": 1
+ },
+ {
+ "fieldname":"period_end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.nowdate(), 12),
+ "hidden": 1,
+ "reqd": 1
+ },
+ {
"fieldname":"from_fiscal_year",
"label": __("Start Year"),
"fieldtype": "Link",
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
index 461291b..b62238b 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
@@ -4,7 +4,7 @@
from __future__ import unicode_literals
import frappe, erpnext
from frappe import _
-from frappe.utils import flt, cint
+from frappe.utils import flt, cint, getdate
from erpnext.accounts.report.utils import get_currency, convert_to_presentation_currency
from erpnext.accounts.report.financial_statements import get_fiscal_year_data, sort_accounts
from erpnext.accounts.report.balance_sheet.balance_sheet import (get_provisional_profit_loss,
@@ -208,17 +208,24 @@
company_currency = get_company_currency(filters)
+ if filters.filter_based_on == 'Fiscal Year':
+ start_date = fiscal_year.year_start_date
+ end_date = fiscal_year.year_end_date
+ else:
+ start_date = filters.period_start_date
+ end_date = filters.period_end_date
+
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(fiscal_year.year_start_date,
- fiscal_year.year_end_date, root.lft, root.rgt, filters,
+ set_gl_entries_by_account(start_date,
+ end_date, root.lft, root.rgt, filters,
gl_entries_by_account, accounts_by_name, ignore_closing_entries=False)
- calculate_values(accounts_by_name, gl_entries_by_account, companies, fiscal_year, filters)
+ calculate_values(accounts_by_name, gl_entries_by_account, companies, start_date, filters)
accumulate_values_into_parents(accounts, accounts_by_name, companies)
- out = prepare_data(accounts, fiscal_year, balance_must_be, companies, company_currency)
+ out = prepare_data(accounts, start_date, end_date, balance_must_be, companies, company_currency)
if out:
add_total_row(out, root_type, balance_must_be, companies, company_currency)
@@ -229,7 +236,7 @@
return (filters.get('presentation_currency')
or frappe.get_cached_value('Company', filters.company, "default_currency"))
-def calculate_values(accounts_by_name, gl_entries_by_account, companies, fiscal_year, filters):
+def calculate_values(accounts_by_name, gl_entries_by_account, companies, start_date, filters):
for entries in gl_entries_by_account.values():
for entry in entries:
key = entry.account_number or entry.account_name
@@ -241,7 +248,7 @@
and entry.company in companies.get(company)):
d[company] = d.get(company, 0.0) + flt(entry.debit) - flt(entry.credit)
- if entry.posting_date < fiscal_year.year_start_date:
+ if entry.posting_date < getdate(start_date):
d["opening_balance"] = d.get("opening_balance", 0.0) + flt(entry.debit) - flt(entry.credit)
def accumulate_values_into_parents(accounts, accounts_by_name, companies):
@@ -295,10 +302,8 @@
`tabAccount` where company = %s and root_type = %s
""" , (filters.get('company'), root_type), as_dict=1)
-def prepare_data(accounts, fiscal_year, balance_must_be, companies, company_currency):
+def prepare_data(accounts, start_date, end_date, balance_must_be, companies, company_currency):
data = []
- year_start_date = fiscal_year.year_start_date
- year_end_date = fiscal_year.year_end_date
for d in accounts:
# add to output
@@ -309,8 +314,8 @@
"account": _(d.account_name),
"parent_account": _(d.parent_account),
"indent": flt(d.indent),
- "year_start_date": year_start_date,
- "year_end_date": year_end_date,
+ "year_start_date": start_date,
+ "year_end_date": end_date,
"currency": company_currency,
"opening_balance": d.get("opening_balance", 0.0) * (1 if balance_must_be == "Debit" else -1)
})
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index e760b79..81effd0 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -18,17 +18,20 @@
from six import itervalues
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
-def get_period_list(from_fiscal_year, to_fiscal_year, periodicity, accumulated_values=False,
+def get_period_list(from_fiscal_year, to_fiscal_year, period_start_date, period_end_date, filter_based_on, periodicity, accumulated_values=False,
company=None, reset_period_on_fy_change=True):
"""Get a list of dict {"from_date": from_date, "to_date": to_date, "key": key, "label": label}
Periodicity can be (Yearly, Quarterly, Monthly)"""
- fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
- validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
-
- # start with first day, so as to avoid year to_dates like 2-April if ever they occur]
- year_start_date = getdate(fiscal_year.year_start_date)
- year_end_date = getdate(fiscal_year.year_end_date)
+ if filter_based_on == 'Fiscal Year':
+ fiscal_year = get_fiscal_year_data(from_fiscal_year, to_fiscal_year)
+ validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year)
+ year_start_date = getdate(fiscal_year.year_start_date)
+ year_end_date = getdate(fiscal_year.year_end_date)
+ else:
+ validate_dates(period_start_date, period_end_date)
+ year_start_date = getdate(period_start_date)
+ year_end_date = getdate(period_end_date)
months_to_add = {
"Yearly": 12,
@@ -42,6 +45,9 @@
start_date = year_start_date
months = get_months(year_start_date, year_end_date)
+ if (months // months_to_add) != (months / months_to_add):
+ months += months_to_add
+
for i in range(months // months_to_add):
period = frappe._dict({
"from_date": start_date
@@ -103,9 +109,18 @@
def validate_fiscal_year(fiscal_year, from_fiscal_year, to_fiscal_year):
- if not fiscal_year.get('year_start_date') and not fiscal_year.get('year_end_date'):
+ if not fiscal_year.get('year_start_date') or not fiscal_year.get('year_end_date'):
+ frappe.throw(_("Start Year and End Year are mandatory"))
+
+ if getdate(fiscal_year.get('year_end_date')) < getdate(fiscal_year.get('year_start_date')):
frappe.throw(_("End Year cannot be before Start Year"))
+def validate_dates(from_date, to_date):
+ if not from_date or not to_date:
+ frappe.throw("From Date and To Date are mandatory")
+
+ if to_date < from_date:
+ frappe.throw("To Date cannot be less than From Date")
def get_months(start_date, end_date):
diff = (12 * end_date.year + end_date.month) - (12 * start_date.year + start_date.month)
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 81fb1e0..7caa764 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
@@ -9,7 +9,8 @@
def execute(filters=None):
period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
- filters.periodicity, filters.accumulated_values, filters.company)
+ filters.period_start_date, filters.period_end_date, filters.filter_based_on, filters.periodicity,
+ company=filters.company)
income = get_data(filters.company, "Income", "Credit", period_list, filters = filters,
accumulated_values=filters.accumulated_values,
diff --git a/erpnext/public/js/financial_statements.js b/erpnext/public/js/financial_statements.js
index dead309..296c628 100644
--- a/erpnext/public/js/financial_statements.js
+++ b/erpnext/public/js/financial_statements.js
@@ -79,6 +79,39 @@
"options": "Finance Book"
},
{
+ "fieldname":"filter_based_on",
+ "label": __("Filter Based On"),
+ "fieldtype": "Select",
+ "options": ["Fiscal Year", "Date Range"],
+ "default": ["Fiscal Year"],
+ "reqd": 1,
+ on_change: function() {
+ let filter_based_on = frappe.query_report.get_filter_value('filter_based_on');
+ frappe.query_report.toggle_filter_display('from_fiscal_year', filter_based_on === 'Date Range');
+ frappe.query_report.toggle_filter_display('to_fiscal_year', filter_based_on === 'Date Range');
+ frappe.query_report.toggle_filter_display('period_start_date', filter_based_on === 'Fiscal Year');
+ frappe.query_report.toggle_filter_display('period_end_date', filter_based_on === 'Fiscal Year');
+
+ frappe.query_report.refresh();
+ }
+ },
+ {
+ "fieldname":"period_start_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.nowdate(),
+ "hidden": 1,
+ "reqd": 1
+ },
+ {
+ "fieldname":"period_end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.nowdate(), 12),
+ "hidden": 1,
+ "reqd": 1
+ },
+ {
"fieldname":"from_fiscal_year",
"label": __("Start Year"),
"fieldtype": "Link",