fix: Aggregation with previous closing balance
diff --git a/erpnext/accounts/doctype/closing_balance/closing_balance.py b/erpnext/accounts/doctype/closing_balance/closing_balance.py
index 7dccaca..f4cbab1 100644
--- a/erpnext/accounts/doctype/closing_balance/closing_balance.py
+++ b/erpnext/accounts/doctype/closing_balance/closing_balance.py
@@ -1,8 +1,6 @@
# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt
-from typing import List
-
import frappe
from frappe.model.document import Document
@@ -12,45 +10,113 @@
class ClosingBalance(Document):
- def aggregate_with_last_closing_balance(self, accounting_dimensions: List[str]):
- closing_balance = frappe.qb.DocType("Closing Balance")
+ pass
- query = (
- frappe.qb.from_(closing_balance)
- .select(closing_balance.debit, closing_balance.credit)
- .where(
- closing_balance.closing_date < self.closing_date,
- )
+
+def make_closing_entries(closing_entries, voucher_name):
+ accounting_dimensions = get_accounting_dimensions()
+ company = closing_entries[0].get("company")
+ closing_date = closing_entries[0].get("closing_date")
+
+ previous_closing_entries = get_previous_closing_entries(
+ company, closing_date, accounting_dimensions
+ )
+ combined_entries = closing_entries + previous_closing_entries
+
+ merged_entries = aggregate_with_last_closing_balance(combined_entries, accounting_dimensions)
+
+ for key, value in merged_entries.items():
+ cle = frappe.new_doc("Closing Balance")
+ cle.update(value)
+ cle.update(value["dimensions"])
+ cle.update(
+ {
+ "period_closing_voucher": voucher_name,
+ "closing_date": closing_date,
+ }
+ )
+ cle.submit()
+
+
+def aggregate_with_last_closing_balance(entries, accounting_dimensions):
+ merged_entries = {}
+ for entry in entries:
+ key, key_values = generate_key(entry, accounting_dimensions)
+ merged_entries.setdefault(
+ key,
+ {
+ "debit": 0,
+ "credit": 0,
+ "debit_in_account_currency": 0,
+ "credit_in_account_currency": 0,
+ },
+ )
+
+ merged_entries[key]["dimensions"] = key_values
+ merged_entries[key]["debit"] += entry.get("debit")
+ merged_entries[key]["credit"] += entry.get("credit")
+ merged_entries[key]["debit_in_account_currency"] += entry.get("debit_in_account_currency")
+ merged_entries[key]["credit_in_account_currency"] += entry.get("credit_in_account_currency")
+
+ return merged_entries
+
+
+def generate_key(entry, accounting_dimensions):
+ key = [
+ entry.get("account"),
+ entry.get("account_currency"),
+ entry.get("cost_center"),
+ entry.get("project"),
+ entry.get("finance_book"),
+ entry.get("is_period_closing_voucher_entry"),
+ ]
+
+ key_values = {
+ "account": entry.get("account"),
+ "account_currency": entry.get("account_currency"),
+ "cost_center": entry.get("cost_center"),
+ "project": entry.get("project"),
+ "finance_book": entry.get("finance_book"),
+ "is_period_closing_voucher_entry": entry.get("is_period_closing_voucher_entry"),
+ }
+ for dimension in accounting_dimensions:
+ key.append(entry.get(dimension))
+ key_values[dimension] = entry.get(dimension)
+
+ return tuple(key), key_values
+
+
+def get_previous_closing_entries(company, closing_date, accounting_dimensions):
+ entries = []
+ last_period_closing_voucher = frappe.db.get_all(
+ "Period Closing Voucher",
+ filters={"docstatus": 1, "company": company, "posting_date": ("<", closing_date)},
+ fields=["name"],
+ order_by="posting_date desc",
+ limit=1,
+ )
+
+ if last_period_closing_voucher:
+ closing_balance = frappe.qb.DocType("Closing Balance")
+ query = frappe.qb.from_(closing_balance).select(
+ closing_balance.account,
+ closing_balance.account_currency,
+ closing_balance.debit,
+ closing_balance.credit,
+ closing_balance.debit_in_account_currency,
+ closing_balance.credit_in_account_currency,
+ closing_balance.cost_center,
+ closing_balance.project,
+ closing_balance.finance_book,
+ closing_balance.is_period_closing_voucher_entry,
)
for dimension in accounting_dimensions:
- query = query.where(closing_balance[dimension] == self.get(dimension))
+ query = query.select(closing_balance[dimension])
- query = query.orderby(closing_balance.closing_date, order=frappe.qb.desc).limit(1)
+ query = query.where(
+ closing_balance.period_closing_voucher == last_period_closing_voucher[0].name
+ )
+ entries = query.run(as_dict=1)
- last_closing_balance = query.run(as_dict=1)
-
- if last_closing_balance:
- self.debit += last_closing_balance[0].debit
- self.credit += last_closing_balance[0].credit
-
-
-def make_closing_entries(
- closing_entries, is_period_closing_voucher_entry=False, voucher_name=None
-):
- accounting_dimensions = get_accounting_dimensions()
- for entry in closing_entries:
- cle = frappe.new_doc("Closing Balance")
- cle.update(entry)
-
- if is_period_closing_voucher_entry:
- cle.update(
- {
- "closing_date": entry.get("posting_date"),
- "is_period_closing_voucher_entry": 1,
- "period_closing_voucher": voucher_name,
- }
- )
-
- cle.aggregate_with_last_closing_balance(accounting_dimensions)
- cle.submit()
+ return entries
diff --git a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
index 57e22a1..9b1378f 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -21,8 +21,14 @@
def on_submit(self):
self.db_set("gle_processing_status", "In Progress")
- self.make_gl_entries()
- self.make_closing_entries()
+ get_opening_entries = False
+
+ if not frappe.db.exists(
+ "Period Closing Voucher", {"company": self.company, "docstatus": 1, "name": ("!=", self.name)}
+ ):
+ get_opening_entries = True
+
+ self.make_gl_entries(get_opening_entries=get_opening_entries)
def on_cancel(self):
self.db_set("gle_processing_status", "In Progress")
@@ -88,34 +94,30 @@
)
)
- def make_gl_entries(self):
+ def make_gl_entries(self, get_opening_entries=False):
gl_entries = self.get_gl_entries()
+ closing_entries = self.get_grouped_gl_entries(get_opening_entries=get_opening_entries)
if gl_entries:
if len(gl_entries) > 5000:
- frappe.enqueue(process_gl_entries, gl_entries=gl_entries, voucher_name=self.name, queue="long")
+ frappe.enqueue(
+ process_gl_entries,
+ gl_entries=gl_entries,
+ closing_entries=closing_entries,
+ voucher_name=self.name,
+ queue="long",
+ )
frappe.msgprint(
_("The GL Entries will be processed in the background, it can take a few minutes."),
alert=True,
)
else:
- process_gl_entries(gl_entries, voucher_name=self.name)
+ process_gl_entries(gl_entries, closing_entries, voucher_name=self.name)
- def make_closing_entries(self):
- closing_entries = self.get_grouped_gl_entries()
-
- if closing_entries:
- if len(closing_entries) > 5000:
- frappe.enqueue(process_closing_entries, gl_entries=closing_entries, queue="long")
- frappe.msgprint(
- _("The Opening Entries will be processed in the background, it can take a few minutes."),
- alert=True,
- )
- else:
- process_closing_entries(closing_entries)
-
- def get_grouped_gl_entries(self):
+ def get_grouped_gl_entries(self, get_opening_entries=False):
closing_entries = []
- for acc in self.get_balances_based_on_dimensions(group_by_account=True, for_aggregation=True):
+ for acc in self.get_balances_based_on_dimensions(
+ group_by_account=True, for_aggregation=True, get_opening_entries=get_opening_entries
+ ):
closing_entries.append(self.get_closing_entries(acc))
return closing_entries
@@ -142,6 +144,7 @@
def get_gle_for_pl_account(self, acc):
gl_entry = self.get_gl_dict(
{
+ "closing_date": self.posting_date,
"account": acc.account,
"cost_center": acc.cost_center,
"finance_book": acc.finance_book,
@@ -154,6 +157,7 @@
if flt(acc.bal_in_account_currency) > 0
else 0,
"credit": abs(flt(acc.bal_in_company_currency)) if flt(acc.bal_in_company_currency) > 0 else 0,
+ "is_period_closing_voucher_entry": 1,
},
item=acc,
)
@@ -163,6 +167,7 @@
def get_gle_for_closing_account(self, acc):
gl_entry = self.get_gl_dict(
{
+ "closing_date": self.posting_date,
"account": self.closing_account_head,
"cost_center": acc.cost_center,
"finance_book": acc.finance_book,
@@ -175,6 +180,7 @@
if flt(acc.bal_in_account_currency) < 0
else 0,
"credit": abs(flt(acc.bal_in_company_currency)) if flt(acc.bal_in_company_currency) < 0 else 0,
+ "is_period_closing_voucher_entry": 1,
},
item=acc,
)
@@ -211,11 +217,11 @@
gl_entry.update({dimension: acc.get(dimension)})
def get_balances_based_on_dimensions(
- self, group_by_account=False, report_type=None, for_aggregation=False
+ self, group_by_account=False, report_type=None, for_aggregation=False, get_opening_entries=False
):
"""Get balance for dimension-wise pl accounts"""
- qb_dimension_fields = ["cost_center", "finance_book"]
+ qb_dimension_fields = ["cost_center", "finance_book", "project"]
self.accounting_dimensions = get_accounting_dimensions()
for dimension in self.accounting_dimensions:
@@ -263,9 +269,21 @@
(gl_entry.company == self.company)
& (gl_entry.is_cancelled == 0)
& (gl_entry.account.isin(accounts))
- & (gl_entry.posting_date.between(self.get("year_start_date"), self.posting_date))
)
+ if get_opening_entries:
+ query = query.where(
+ gl_entry.posting_date.between(self.get("year_start_date"), self.posting_date)
+ | gl_entry.is_opening
+ == "Yes"
+ )
+ else:
+ query = query.where(
+ gl_entry.posting_date.between(self.get("year_start_date"), self.posting_date)
+ & gl_entry.is_opening
+ == "No"
+ )
+
if for_aggregation:
query = query.where(gl_entry.voucher_type != "Period Closing Voucher")
@@ -285,13 +303,13 @@
frappe.log_error(e)
-def process_gl_entries(gl_entries, voucher_name=None):
+def process_gl_entries(gl_entries, closing_entries, voucher_name=None):
from erpnext.accounts.doctype.closing_balance.closing_balance import make_closing_entries
from erpnext.accounts.general_ledger import make_gl_entries
try:
make_gl_entries(gl_entries, merge_entries=False)
- make_closing_entries(gl_entries, is_period_closing_voucher_entry=True, voucher_name=voucher_name)
+ make_closing_entries(gl_entries + closing_entries, voucher_name=voucher_name)
frappe.db.set_value(
"Period Closing Voucher", gl_entries[0].get("voucher_no"), "gle_processing_status", "Completed"
)
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index ca1ec08..52b1b05 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -328,4 +328,4 @@
erpnext.patches.v14_0.set_pick_list_status
erpnext.patches.v15_0.update_asset_value_for_manual_depr_entries
erpnext.patches.v14_0.create_accounting_dimensions_for_closing_balance
-erpnext.patches.v14_0.update_closing_balances #13
+erpnext.patches.v14_0.update_closing_balances
diff --git a/erpnext/patches/v14_0/update_closing_balances.py b/erpnext/patches/v14_0/update_closing_balances.py
index 6a9334b..0442b36 100644
--- a/erpnext/patches/v14_0/update_closing_balances.py
+++ b/erpnext/patches/v14_0/update_closing_balances.py
@@ -10,6 +10,7 @@
def execute():
company_wise_order = {}
+ get_opening_entries = True
for pcv in frappe.db.get_all(
"Period Closing Voucher",
fields=["company", "posting_date", "name"],
@@ -23,7 +24,8 @@
pcv_doc.year_start_date = get_fiscal_year(
pcv.posting_date, pcv.fiscal_year, company=pcv.company
)[1]
- pcv_doc.make_closing_entries()
gl_entries = pcv_doc.get_gl_entries()
- make_closing_entries(gl_entries, is_period_closing_voucher_entry=True, voucher_name=pcv.name)
+ closing_entries = pcv_doc.get_grouped_gl_entries(get_opening_entries=get_opening_entries)
+ make_closing_entries(gl_entries + closing_entries, voucher_name=pcv.name)
company_wise_order[pcv.company].append(pcv.posting_date)
+ get_opening_entries = False