perf: Apply closing balance in Trial Balance report
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 3af01fd..96cb38b 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -4,7 +4,8 @@
import frappe
from frappe import _
-from frappe.utils import cstr, flt, formatdate, getdate
+from frappe.query_builder.functions import Sum
+from frappe.utils import add_days, cstr, flt, formatdate, getdate
import erpnext
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
@@ -140,45 +141,88 @@
def get_rootwise_opening_balances(filters, report_type):
- additional_conditions = ""
- if not filters.show_unclosed_fy_pl_balances:
- additional_conditions = (
- " and posting_date >= %(year_start_date)s" if report_type == "Profit and Loss" else ""
+ gle = []
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
+ gle = get_opening_balance("Closing Balance", filters, report_type, accounting_dimensions)
+
+ last_period_closing_voucher_date = frappe.db.get_value(
+ "Period Closing Voucher", {"docstatus": 1, "company": filters.company}, "max(posting_date)"
+ )
+
+ # Check If need to get opening balance from GL Entry
+ if getdate(last_period_closing_voucher_date) < getdate(add_days(filters.from_date, -1)):
+ filters.from_date = add_days(last_period_closing_voucher_date, 1)
+ gle = get_opening_balance("GL Entry", filters, report_type, accounting_dimensions)
+
+ opening = frappe._dict()
+ for d in gle:
+ opening.setdefault(d.account, d)
+
+ return opening
+
+
+def get_opening_balance(doctype, filters, report_type, accounting_dimensions):
+ closing_balance = frappe.qb.DocType(doctype)
+ account = frappe.qb.DocType("Account")
+
+ opening_balance = (
+ frappe.qb.from_(closing_balance)
+ .select(
+ closing_balance.account,
+ Sum(closing_balance.debit).as_("opening_debit"),
+ Sum(closing_balance.credit).as_("opening_credit"),
)
+ .where(
+ (closing_balance.company == filters.company)
+ & (
+ closing_balance.account.isin(
+ frappe.qb.from_("account").select("name").where(account.report_type == report_type)
+ )
+ )
+ )
+ .groupby(closing_balance.account)
+ )
+
+ if doctype == "Closing Balance":
+ opening_balance = opening_balance.where(closing_balance.closing_date < filters.from_date)
+ else:
+ opening_balance = opening_balance.where(closing_balance.posting_date < filters.from_date)
+
+ if not filters.show_unclosed_fy_pl_balances and report_type == "Profit and Loss":
+ opening_balance = opening_balance.where(closing_balance.closing_date >= filters.year_start_date)
if not flt(filters.with_period_closing_entry):
- additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'"
+ if doctype == "Closing Balance":
+ opening_balance = opening_balance.where(closing_balance.is_period_closing_voucher_entry == 0)
+ else:
+ opening_balance = opening_balance.where(
+ closing_balance.voucher_type != "Period Closing Voucher"
+ )
if filters.cost_center:
lft, rgt = frappe.db.get_value("Cost Center", filters.cost_center, ["lft", "rgt"])
- additional_conditions += """ and cost_center in (select name from `tabCost Center`
- where lft >= %s and rgt <= %s)""" % (
- lft,
- rgt,
+ cost_center = frappe.qb.DocType("Cost Center")
+ opening_balance = opening_balance.where(
+ closing_balance.cost_center.in_(
+ frappe.qb.from_(cost_center)
+ .select("name")
+ .where((cost_center.lft >= lft) & (cost_center.rgt <= rgt))
+ )
)
if filters.project:
- additional_conditions += " and project = %(project)s"
+ opening_balance = opening_balance.where(closing_balance.project == filters.project)
if filters.get("include_default_book_entries"):
- additional_conditions += (
- " AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
+ opening_balance = opening_balance.where(
+ (closing_balance.finance_book.isin([cstr(filters.finance_book), cstr(filters.company_fb), ""]))
+ | (closing_balance.finance_book.isnull())
)
else:
- additional_conditions += " AND (finance_book in (%(finance_book)s, '') OR finance_book IS NULL)"
-
- accounting_dimensions = get_accounting_dimensions(as_list=False)
-
- query_filters = {
- "company": filters.company,
- "from_date": filters.from_date,
- "to_date": filters.to_date,
- "report_type": report_type,
- "year_start_date": filters.year_start_date,
- "project": filters.project,
- "finance_book": filters.finance_book,
- "company_fb": frappe.get_cached_value("Company", filters.company, "default_finance_book"),
- }
+ opening_balance = opening_balance.where(
+ (closing_balance.finance_book.isin([cstr(filters.finance_book), ""]))
+ | (closing_balance.finance_book.isnull())
+ )
if accounting_dimensions:
for dimension in accounting_dimensions:
@@ -187,35 +231,17 @@
filters[dimension.fieldname] = get_dimension_with_children(
dimension.document_type, filters.get(dimension.fieldname)
)
- additional_conditions += " and {0} in %({0})s".format(dimension.fieldname)
+ opening_balance = opening_balance.where(
+ closing_balance[dimension.fieldname].isin(filters[dimension.fieldname])
+ )
else:
- additional_conditions += " and {0} in %({0})s".format(dimension.fieldname)
+ opening_balance = opening_balance.where(
+ closing_balance[dimension.fieldname].isin(filters[dimension.fieldname])
+ )
- query_filters.update({dimension.fieldname: filters.get(dimension.fieldname)})
+ gle = opening_balance.run(as_dict=1)
- gle = frappe.db.sql(
- """
- select
- account, sum(debit) as opening_debit, sum(credit) as opening_credit
- from `tabGL Entry`
- where
- company=%(company)s
- {additional_conditions}
- and (posting_date < %(from_date)s or (ifnull(is_opening, 'No') = 'Yes' and posting_date <= %(to_date)s))
- and account in (select name from `tabAccount` where report_type=%(report_type)s)
- and is_cancelled = 0
- group by account""".format(
- additional_conditions=additional_conditions
- ),
- query_filters,
- as_dict=True,
- )
-
- opening = frappe._dict()
- for d in gle:
- opening.setdefault(d.account, d)
-
- return opening
+ return gle
def calculate_values(accounts, gl_entries_by_account, opening_balances, filters, company_currency):