chore: Use account closing balance in set gl entries
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.py b/erpnext/accounts/report/balance_sheet/balance_sheet.py
index 07552e3..c831b78 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.py
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.py
@@ -25,6 +25,8 @@
company=filters.company,
)
+ filters.period_start_date = period_list[0]["year_start_date"]
+
currency = filters.presentation_currency or frappe.get_cached_value(
"Company", filters.company, "default_currency"
)
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 8c6fe43..d9c3980 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -419,45 +419,46 @@
):
"""Returns a dict like { "account": [gl entries], ... }"""
- additional_conditions = get_additional_conditions(from_date, ignore_closing_entries, filters)
+ gl_entries = []
+ account = frappe.qb.DocType("Account")
- accounts = frappe.db.sql_list(
- """select name from `tabAccount`
- where lft >= %s and rgt <= %s and company = %s""",
- (root_lft, root_rgt, company),
+ accounts = (
+ frappe.qb.from_(account)
+ .select(account.name)
+ .where(account.lft >= root_lft)
+ .where(account.rgt <= root_rgt)
+ .where(account.company == company)
+ .run(as_dict=True)
)
- if accounts:
- additional_conditions += " and account in ({})".format(
- ", ".join(frappe.db.escape(d) for d in accounts)
- )
+ accounts_list = [account.name for account in accounts]
- gl_filters = {
- "company": company,
- "from_date": from_date,
- "to_date": to_date,
- "finance_book": cstr(filters.get("finance_book")),
- }
+ if accounts_list:
- if filters.get("include_default_book_entries"):
- gl_filters["company_fb"] = frappe.get_cached_value("Company", company, "default_finance_book")
+ # For balance sheet
+ if not from_date:
+ from_date = filters["period_start_date"]
+ last_period_closing_voucher = frappe.db.get_all(
+ "Period Closing Voucher",
+ filters={"docstatus": 1, "company": filters.company, "posting_date": ("<", from_date)},
+ fields=["posting_date", "name"],
+ order_by="posting_date desc",
+ limit=1,
+ )
+ if last_period_closing_voucher:
+ gl_entries += get_accounting_entries(
+ "Account Closing Balance",
+ from_date,
+ to_date,
+ accounts_list,
+ filters,
+ ignore_closing_entries,
+ last_period_closing_voucher[0].name,
+ )
+ from_date = add_days(last_period_closing_voucher[0].posting_date, 1)
- for key, value in filters.items():
- if value:
- gl_filters.update({key: value})
-
- gl_entries = frappe.db.sql(
- """
- select posting_date, account, debit, credit, is_opening, fiscal_year,
- debit_in_account_currency, credit_in_account_currency, account_currency from `tabGL Entry`
- where company=%(company)s
- {additional_conditions}
- and posting_date <= %(to_date)s
- and is_cancelled = 0""".format(
- additional_conditions=additional_conditions
- ),
- gl_filters,
- as_dict=True,
+ gl_entries += get_accounting_entries(
+ "GL Entry", from_date, to_date, accounts_list, filters, ignore_closing_entries
)
if filters and filters.get("presentation_currency"):
@@ -469,34 +470,81 @@
return gl_entries_by_account
-def get_additional_conditions(from_date, ignore_closing_entries, filters):
- additional_conditions = []
+def get_accounting_entries(
+ doctype,
+ from_date,
+ to_date,
+ accounts,
+ filters,
+ ignore_closing_entries,
+ period_closing_voucher=None,
+):
+ gl_entry = frappe.qb.DocType(doctype)
+ query = (
+ frappe.qb.from_(gl_entry)
+ .select(
+ gl_entry.account,
+ gl_entry.debit,
+ gl_entry.credit,
+ gl_entry.is_opening,
+ gl_entry.fiscal_year,
+ gl_entry.debit_in_account_currency,
+ gl_entry.credit_in_account_currency,
+ gl_entry.account_currency,
+ )
+ .where(gl_entry.company == filters.company)
+ )
+ query = query.where(gl_entry.account.isin(accounts))
+
+ if doctype == "GL Entry":
+ query = query.select(gl_entry.posting_date)
+ query = query.where(gl_entry.is_cancelled == 0)
+ query = query.where(gl_entry.posting_date <= to_date)
+ else:
+ query = query.select(gl_entry.closing_date.as_("posting_date"))
+ query = query.where(gl_entry.period_closing_voucher == period_closing_voucher)
+
+ query = apply_additional_conditions(doctype, query, from_date, ignore_closing_entries, filters)
+
+ entries = query.run(as_dict=True)
+
+ return entries
+
+
+def apply_additional_conditions(doctype, query, from_date, ignore_closing_entries, filters):
+ gl_entry = frappe.qb.DocType(doctype)
accounting_dimensions = get_accounting_dimensions(as_list=False)
if ignore_closing_entries:
- additional_conditions.append("ifnull(voucher_type, '')!='Period Closing Voucher'")
+ if doctype == "GL Entry":
+ query = query.where(gl_entry.voucher_type != "Period Closing Voucher")
+ else:
+ query = query.where(gl_entry.is_period_closing_voucher_entry == 0)
- if from_date:
- additional_conditions.append("posting_date >= %(from_date)s")
+ if from_date and doctype == "GL Entry":
+ query = query.where(gl_entry.posting_date >= from_date)
if filters:
if filters.get("project"):
if not isinstance(filters.get("project"), list):
filters.project = frappe.parse_json(filters.get("project"))
- additional_conditions.append("project in %(project)s")
+ query = query.where(gl_entry.project.isin(filters.project))
if filters.get("cost_center"):
filters.cost_center = get_cost_centers_with_children(filters.cost_center)
- additional_conditions.append("cost_center in %(cost_center)s")
+ query = query.where(gl_entry.cost_center.isin(filters.cost_center))
if filters.get("include_default_book_entries"):
- additional_conditions.append(
- "(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
+ query = query.where(
+ (gl_entry.finance_book.isin([cstr(filters.finance_book), cstr(filters.company_fb), ""]))
+ | (gl_entry.finance_book.isnull())
)
else:
- additional_conditions.append("(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)")
+ query = query.where(
+ (gl_entry.finance_book.isin([cstr(filters.company_fb), ""])) | (gl_entry.finance_book.isnull())
+ )
if accounting_dimensions:
for dimension in accounting_dimensions:
@@ -505,11 +553,10 @@
filters[dimension.fieldname] = get_dimension_with_children(
dimension.document_type, filters.get(dimension.fieldname)
)
- additional_conditions.append("{0} in %({0})s".format(dimension.fieldname))
- else:
- additional_conditions.append("{0} in %({0})s".format(dimension.fieldname))
- return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
+ query = query.where(gl_entry[dimension.fieldname].isin(filters[dimension.fieldname]))
+
+ return query
def get_cost_centers_with_children(cost_centers):
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index dbfbcc9..e8822a7 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -510,6 +510,7 @@
"Subcontracting Order Item",
"Subcontracting Receipt",
"Subcontracting Receipt Item",
+ "Accounts Closing Balance",
]
# get matching queries for Bank Reconciliation