Merge branch 'develop' of https://github.com/frappe/erpnext into opening_entry
diff --git a/erpnext/accounts/doctype/account_closing_balance/__init__.py b/erpnext/accounts/doctype/account_closing_balance/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/account_closing_balance/__init__.py
diff --git a/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.js b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.js
new file mode 100644
index 0000000..e355914
--- /dev/null
+++ b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+// frappe.ui.form.on("Account Closing Balance", {
+// refresh(frm) {
+
+// },
+// });
diff --git a/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.json b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.json
new file mode 100644
index 0000000..8dacb96
--- /dev/null
+++ b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.json
@@ -0,0 +1,164 @@
+{
+ "actions": [],
+ "creation": "2023-02-21 15:20:59.586811",
+ "default_view": "List",
+ "doctype": "DocType",
+ "document_type": "Document",
+ "engine": "InnoDB",
+ "field_order": [
+ "closing_date",
+ "account",
+ "cost_center",
+ "debit",
+ "credit",
+ "account_currency",
+ "debit_in_account_currency",
+ "credit_in_account_currency",
+ "project",
+ "company",
+ "finance_book",
+ "period_closing_voucher",
+ "is_period_closing_voucher_entry"
+ ],
+ "fields": [
+ {
+ "fieldname": "closing_date",
+ "fieldtype": "Date",
+ "in_filter": 1,
+ "in_list_view": 1,
+ "label": "Closing Date",
+ "oldfieldname": "posting_date",
+ "oldfieldtype": "Date",
+ "search_index": 1
+ },
+ {
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "in_filter": 1,
+ "in_list_view": 1,
+ "in_standard_filter": 1,
+ "label": "Account",
+ "oldfieldname": "account",
+ "oldfieldtype": "Link",
+ "options": "Account",
+ "search_index": 1
+ },
+ {
+ "fieldname": "cost_center",
+ "fieldtype": "Link",
+ "in_filter": 1,
+ "in_list_view": 1,
+ "label": "Cost Center",
+ "oldfieldname": "cost_center",
+ "oldfieldtype": "Link",
+ "options": "Cost Center"
+ },
+ {
+ "fieldname": "debit",
+ "fieldtype": "Currency",
+ "label": "Debit Amount",
+ "oldfieldname": "debit",
+ "oldfieldtype": "Currency",
+ "options": "Company:company:default_currency"
+ },
+ {
+ "fieldname": "credit",
+ "fieldtype": "Currency",
+ "label": "Credit Amount",
+ "oldfieldname": "credit",
+ "oldfieldtype": "Currency",
+ "options": "Company:company:default_currency"
+ },
+ {
+ "fieldname": "account_currency",
+ "fieldtype": "Link",
+ "label": "Account Currency",
+ "options": "Currency"
+ },
+ {
+ "fieldname": "debit_in_account_currency",
+ "fieldtype": "Currency",
+ "label": "Debit Amount in Account Currency",
+ "options": "account_currency"
+ },
+ {
+ "fieldname": "credit_in_account_currency",
+ "fieldtype": "Currency",
+ "label": "Credit Amount in Account Currency",
+ "options": "account_currency"
+ },
+ {
+ "fieldname": "project",
+ "fieldtype": "Link",
+ "label": "Project",
+ "options": "Project"
+ },
+ {
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "in_filter": 1,
+ "in_list_view": 1,
+ "in_standard_filter": 1,
+ "label": "Company",
+ "oldfieldname": "company",
+ "oldfieldtype": "Link",
+ "options": "Company",
+ "search_index": 1
+ },
+ {
+ "fieldname": "finance_book",
+ "fieldtype": "Link",
+ "label": "Finance Book",
+ "options": "Finance Book"
+ },
+ {
+ "fieldname": "period_closing_voucher",
+ "fieldtype": "Link",
+ "in_standard_filter": 1,
+ "label": "Period Closing Voucher",
+ "options": "Period Closing Voucher",
+ "search_index": 1
+ },
+ {
+ "default": "0",
+ "fieldname": "is_period_closing_voucher_entry",
+ "fieldtype": "Check",
+ "label": "Is Period Closing Voucher Entry"
+ }
+ ],
+ "icon": "fa fa-list",
+ "in_create": 1,
+ "links": [],
+ "modified": "2023-03-06 08:56:36.393237",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Account Closing Balance",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts User"
+ },
+ {
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Accounts Manager"
+ },
+ {
+ "export": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Auditor"
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.py b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.py
new file mode 100644
index 0000000..7c84237
--- /dev/null
+++ b/erpnext/accounts/doctype/account_closing_balance/account_closing_balance.py
@@ -0,0 +1,127 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe.model.document import Document
+from frappe.utils import cint, cstr
+
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
+ get_accounting_dimensions,
+)
+
+
+class AccountClosingBalance(Document):
+ pass
+
+
+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_account_closing_balance(
+ combined_entries, accounting_dimensions
+ )
+
+ for key, value in merged_entries.items():
+ cle = frappe.new_doc("Account 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_account_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 = [
+ cstr(entry.get("account")),
+ cstr(entry.get("account_currency")),
+ cstr(entry.get("cost_center")),
+ cstr(entry.get("project")),
+ cstr(entry.get("finance_book")),
+ cint(entry.get("is_period_closing_voucher_entry")),
+ ]
+
+ key_values = {
+ "company": cstr(entry.get("company")),
+ "account": cstr(entry.get("account")),
+ "account_currency": cstr(entry.get("account_currency")),
+ "cost_center": cstr(entry.get("cost_center")),
+ "project": cstr(entry.get("project")),
+ "finance_book": cstr(entry.get("finance_book")),
+ "is_period_closing_voucher_entry": cint(entry.get("is_period_closing_voucher_entry")),
+ }
+ for dimension in accounting_dimensions:
+ key.append(cstr(entry.get(dimension)))
+ key_values[dimension] = cstr(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:
+ account_closing_balance = frappe.qb.DocType("Account Closing Balance")
+ query = frappe.qb.from_(account_closing_balance).select(
+ account_closing_balance.company,
+ account_closing_balance.account,
+ account_closing_balance.account_currency,
+ account_closing_balance.debit,
+ account_closing_balance.credit,
+ account_closing_balance.debit_in_account_currency,
+ account_closing_balance.credit_in_account_currency,
+ account_closing_balance.cost_center,
+ account_closing_balance.project,
+ account_closing_balance.finance_book,
+ account_closing_balance.is_period_closing_voucher_entry,
+ )
+
+ for dimension in accounting_dimensions:
+ query = query.select(account_closing_balance[dimension])
+
+ query = query.where(
+ account_closing_balance.period_closing_voucher == last_period_closing_voucher[0].name
+ )
+ entries = query.run(as_dict=1)
+
+ return entries
diff --git a/erpnext/accounts/doctype/account_closing_balance/test_account_closing_balance.py b/erpnext/accounts/doctype/account_closing_balance/test_account_closing_balance.py
new file mode 100644
index 0000000..fc42677
--- /dev/null
+++ b/erpnext/accounts/doctype/account_closing_balance/test_account_closing_balance.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+# import frappe
+from frappe.tests.utils import FrappeTestCase
+
+
+class TestAccountClosingBalance(FrappeTestCase):
+ pass
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 ca98bee..f6289e7 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/period_closing_voucher.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Sum
from frappe.utils import flt
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
@@ -20,7 +21,14 @@
def on_submit(self):
self.db_set("gle_processing_status", "In Progress")
- self.make_gl_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")
@@ -42,6 +50,14 @@
else:
make_reverse_gl_entries(voucher_type="Period Closing Voucher", voucher_no=self.name)
+ self.delete_closing_entries()
+
+ def delete_closing_entries(self):
+ closing_balance = frappe.qb.DocType("Account Closing Balance")
+ frappe.qb.from_(closing_balance).delete().where(
+ closing_balance.period_closing_voucher == self.name
+ ).run()
+
def validate_account_head(self):
closing_account_type = frappe.get_cached_value("Account", self.closing_account_head, "root_type")
@@ -78,28 +94,48 @@
)
)
- 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, 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)
+ process_gl_entries(gl_entries, closing_entries, voucher_name=self.name)
+
+ 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, get_opening_entries=get_opening_entries
+ ):
+ closing_entries.append(self.get_closing_entries(acc))
+
+ return closing_entries
def get_gl_entries(self):
gl_entries = []
# pl account
- for acc in self.get_pl_balances_based_on_dimensions(group_by_account=True):
+ for acc in self.get_balances_based_on_dimensions(
+ group_by_account=True, report_type="Profit and Loss"
+ ):
if flt(acc.bal_in_company_currency):
gl_entries.append(self.get_gle_for_pl_account(acc))
# closing liability account
- for acc in self.get_pl_balances_based_on_dimensions(group_by_account=False):
+ for acc in self.get_balances_based_on_dimensions(
+ group_by_account=False, report_type="Profit and Loss"
+ ):
if flt(acc.bal_in_company_currency):
gl_entries.append(self.get_gle_for_closing_account(acc))
@@ -108,6 +144,8 @@
def get_gle_for_pl_account(self, acc):
gl_entry = self.get_gl_dict(
{
+ "company": self.company,
+ "closing_date": self.posting_date,
"account": acc.account,
"cost_center": acc.cost_center,
"finance_book": acc.finance_book,
@@ -120,6 +158,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,
)
@@ -129,6 +168,8 @@
def get_gle_for_closing_account(self, acc):
gl_entry = self.get_gl_dict(
{
+ "company": self.company,
+ "closing_date": self.posting_date,
"account": self.closing_account_head,
"cost_center": acc.cost_center,
"finance_book": acc.finance_book,
@@ -141,12 +182,36 @@
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,
)
self.update_default_dimensions(gl_entry, acc)
return gl_entry
+ def get_closing_entries(self, acc):
+ closing_entry = self.get_gl_dict(
+ {
+ "company": self.company,
+ "closing_date": self.posting_date,
+ "period_closing_voucher": self.name,
+ "account": acc.account,
+ "cost_center": acc.cost_center,
+ "finance_book": acc.finance_book,
+ "account_currency": acc.account_currency,
+ "debit_in_account_currency": flt(acc.debit_in_account_currency),
+ "debit": flt(acc.debit),
+ "credit_in_account_currency": flt(acc.credit_in_account_currency),
+ "credit": flt(acc.credit),
+ },
+ item=acc,
+ )
+
+ for dimension in self.accounting_dimensions:
+ closing_entry.update({dimension: acc.get(dimension)})
+
+ return closing_entry
+
def update_default_dimensions(self, gl_entry, acc):
if not self.accounting_dimensions:
self.accounting_dimensions = get_accounting_dimensions()
@@ -154,47 +219,92 @@
for dimension in self.accounting_dimensions:
gl_entry.update({dimension: acc.get(dimension)})
- def get_pl_balances_based_on_dimensions(self, group_by_account=False):
+ def get_balances_based_on_dimensions(
+ self, group_by_account=False, report_type=None, for_aggregation=False, get_opening_entries=False
+ ):
"""Get balance for dimension-wise pl accounts"""
- dimension_fields = ["t1.cost_center", "t1.finance_book"]
+ qb_dimension_fields = ["cost_center", "finance_book", "project"]
self.accounting_dimensions = get_accounting_dimensions()
for dimension in self.accounting_dimensions:
- dimension_fields.append("t1.{0}".format(dimension))
+ qb_dimension_fields.append(dimension)
if group_by_account:
- dimension_fields.append("t1.account")
+ qb_dimension_fields.append("account")
- return frappe.db.sql(
- """
- select
- t2.account_currency,
- {dimension_fields},
- sum(t1.debit_in_account_currency) - sum(t1.credit_in_account_currency) as bal_in_account_currency,
- sum(t1.debit) - sum(t1.credit) as bal_in_company_currency
- from `tabGL Entry` t1, `tabAccount` t2
- where
- t1.is_cancelled = 0
- and t1.account = t2.name
- and t2.report_type = 'Profit and Loss'
- and t2.docstatus < 2
- and t2.company = %s
- and t1.posting_date between %s and %s
- group by {dimension_fields}
- """.format(
- dimension_fields=", ".join(dimension_fields)
- ),
- (self.company, self.get("year_start_date"), self.posting_date),
- as_dict=1,
+ account = frappe.qb.DocType("Account")
+ accounts_query = (
+ frappe.qb.from_(account)
+ .select(account.name)
+ .where((account.company == self.company) & (account.is_group == 0) & (account.docstatus < 2))
)
+ if report_type:
+ accounts_query = accounts_query.where(account.report_type == report_type)
-def process_gl_entries(gl_entries):
+ accounts = accounts_query.run(as_dict=True)
+
+ accounts = [d.name for d in accounts]
+
+ gl_entry = frappe.qb.DocType("GL Entry")
+ query = frappe.qb.from_(gl_entry).select(gl_entry.account, gl_entry.account_currency)
+
+ if not for_aggregation:
+ query = query.select(
+ (Sum(gl_entry.debit_in_account_currency) - Sum(gl_entry.credit_in_account_currency)).as_(
+ "bal_in_account_currency"
+ ),
+ (Sum(gl_entry.debit) - Sum(gl_entry.credit)).as_("bal_in_company_currency"),
+ )
+ else:
+ query = query.select(
+ (Sum(gl_entry.debit_in_account_currency)).as_("debit_in_account_currency"),
+ (Sum(gl_entry.credit_in_account_currency)).as_("credit_in_account_currency"),
+ (Sum(gl_entry.debit)).as_("debit"),
+ (Sum(gl_entry.credit)).as_("credit"),
+ )
+
+ for dimension in qb_dimension_fields:
+ query = query.select(gl_entry[dimension])
+
+ query = query.where(
+ (gl_entry.company == self.company)
+ & (gl_entry.is_cancelled == 0)
+ & (gl_entry.account.isin(accounts))
+ )
+
+ 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")
+
+ for dimension in qb_dimension_fields:
+ query = query.groupby(gl_entry[dimension])
+
+ return query.run(as_dict=1)
+
+
+def process_gl_entries(gl_entries, closing_entries, voucher_name=None):
+ from erpnext.accounts.doctype.account_closing_balance.account_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 + 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/accounts/doctype/period_closing_voucher/test_period_closing_voucher.py b/erpnext/accounts/doctype/period_closing_voucher/test_period_closing_voucher.py
index e9ed2e4..62ae857 100644
--- a/erpnext/accounts/doctype/period_closing_voucher/test_period_closing_voucher.py
+++ b/erpnext/accounts/doctype/period_closing_voucher/test_period_closing_voucher.py
@@ -16,16 +16,17 @@
class TestPeriodClosingVoucher(unittest.TestCase):
def test_closing_entry(self):
frappe.db.sql("delete from `tabGL Entry` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabPeriod Closing Voucher` where company='Test PCV Company'")
company = create_company()
cost_center = create_cost_center("Test Cost Center 1")
jv1 = make_journal_entry(
+ posting_date="2021-03-15",
amount=400,
account1="Cash - TPC",
account2="Sales - TPC",
cost_center=cost_center,
- posting_date=now(),
save=False,
)
jv1.company = company
@@ -33,18 +34,18 @@
jv1.submit()
jv2 = make_journal_entry(
+ posting_date="2021-03-15",
amount=600,
account1="Cost of Goods Sold - TPC",
account2="Cash - TPC",
cost_center=cost_center,
- posting_date=now(),
save=False,
)
jv2.company = company
jv2.save()
jv2.submit()
- pcv = self.make_period_closing_voucher()
+ pcv = self.make_period_closing_voucher(posting_date="2021-03-31")
surplus_account = pcv.closing_account_head
expected_gle = (
@@ -65,6 +66,7 @@
def test_cost_center_wise_posting(self):
frappe.db.sql("delete from `tabGL Entry` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabPeriod Closing Voucher` where company='Test PCV Company'")
company = create_company()
surplus_account = create_account()
@@ -81,6 +83,7 @@
debit_to="Debtors - TPC",
currency="USD",
customer="_Test Customer USD",
+ posting_date="2021-03-15",
)
create_sales_invoice(
company=company,
@@ -91,9 +94,10 @@
debit_to="Debtors - TPC",
currency="USD",
customer="_Test Customer USD",
+ posting_date="2021-03-15",
)
- pcv = self.make_period_closing_voucher(submit=False)
+ pcv = self.make_period_closing_voucher(posting_date="2021-03-31", submit=False)
pcv.save()
pcv.submit()
surplus_account = pcv.closing_account_head
@@ -128,12 +132,13 @@
def test_period_closing_with_finance_book_entries(self):
frappe.db.sql("delete from `tabGL Entry` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabPeriod Closing Voucher` where company='Test PCV Company'")
company = create_company()
surplus_account = create_account()
cost_center = create_cost_center("Test Cost Center 1")
- si = create_sales_invoice(
+ create_sales_invoice(
company=company,
income_account="Sales - TPC",
expense_account="Cost of Goods Sold - TPC",
@@ -142,6 +147,7 @@
debit_to="Debtors - TPC",
currency="USD",
customer="_Test Customer USD",
+ posting_date="2021-03-15",
)
jv = make_journal_entry(
@@ -149,14 +155,14 @@
account2="Sales - TPC",
amount=400,
cost_center=cost_center,
- posting_date=now(),
+ posting_date="2021-03-15",
)
jv.company = company
jv.finance_book = create_finance_book().name
jv.save()
jv.submit()
- pcv = self.make_period_closing_voucher()
+ pcv = self.make_period_closing_voucher(posting_date="2021-03-31")
surplus_account = pcv.closing_account_head
expected_gle = (
@@ -177,14 +183,130 @@
self.assertSequenceEqual(pcv_gle, expected_gle)
- def make_period_closing_voucher(self, submit=True):
+ def test_gl_entries_restrictions(self):
+ frappe.db.sql("delete from `tabGL Entry` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabPeriod Closing Voucher` where company='Test PCV Company'")
+
+ company = create_company()
+ cost_center = create_cost_center("Test Cost Center 1")
+
+ self.make_period_closing_voucher(posting_date="2021-03-31")
+
+ jv1 = make_journal_entry(
+ posting_date="2021-03-15",
+ amount=400,
+ account1="Cash - TPC",
+ account2="Sales - TPC",
+ cost_center=cost_center,
+ save=False,
+ )
+ jv1.company = company
+ jv1.save()
+
+ self.assertRaises(frappe.ValidationError, jv1.submit)
+
+ def test_closing_balance_with_dimensions(self):
+ frappe.db.sql("delete from `tabGL Entry` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabPeriod Closing Voucher` where company='Test PCV Company'")
+ frappe.db.sql("delete from `tabAccount Closing Balance` where company='Test PCV Company'")
+
+ company = create_company()
+ cost_center1 = create_cost_center("Test Cost Center 1")
+ cost_center2 = create_cost_center("Test Cost Center 2")
+
+ jv1 = make_journal_entry(
+ posting_date="2021-03-15",
+ amount=400,
+ account1="Cash - TPC",
+ account2="Sales - TPC",
+ cost_center=cost_center1,
+ save=False,
+ )
+ jv1.company = company
+ jv1.save()
+ jv1.submit()
+
+ jv2 = make_journal_entry(
+ posting_date="2021-03-15",
+ amount=200,
+ account1="Cash - TPC",
+ account2="Sales - TPC",
+ cost_center=cost_center2,
+ save=False,
+ )
+ jv2.company = company
+ jv2.save()
+ jv2.submit()
+
+ pcv1 = self.make_period_closing_voucher(posting_date="2021-03-31")
+
+ closing_balance = frappe.db.get_value(
+ "Account Closing Balance",
+ {
+ "account": "Sales - TPC",
+ "cost_center": cost_center1,
+ "period_closing_voucher": pcv1.name,
+ "is_period_closing_voucher_entry": 0,
+ },
+ ["credit", "credit_in_account_currency"],
+ as_dict=1,
+ )
+
+ self.assertEqual(closing_balance.credit, 400)
+ self.assertEqual(closing_balance.credit_in_account_currency, 400)
+
+ jv3 = make_journal_entry(
+ posting_date="2022-03-15",
+ amount=300,
+ account1="Cash - TPC",
+ account2="Sales - TPC",
+ cost_center=cost_center2,
+ save=False,
+ )
+
+ jv3.company = company
+ jv3.save()
+ jv3.submit()
+
+ pcv2 = self.make_period_closing_voucher(posting_date="2022-03-31")
+
+ cc1_closing_balance = frappe.db.get_value(
+ "Account Closing Balance",
+ {
+ "account": "Sales - TPC",
+ "cost_center": cost_center1,
+ "period_closing_voucher": pcv2.name,
+ "is_period_closing_voucher_entry": 0,
+ },
+ ["credit", "credit_in_account_currency"],
+ as_dict=1,
+ )
+
+ cc2_closing_balance = frappe.db.get_value(
+ "Account Closing Balance",
+ {
+ "account": "Sales - TPC",
+ "cost_center": cost_center2,
+ "period_closing_voucher": pcv2.name,
+ "is_period_closing_voucher_entry": 0,
+ },
+ ["credit", "credit_in_account_currency"],
+ as_dict=1,
+ )
+
+ self.assertEqual(cc1_closing_balance.credit, 400)
+ self.assertEqual(cc1_closing_balance.credit_in_account_currency, 400)
+ self.assertEqual(cc2_closing_balance.credit, 500)
+ self.assertEqual(cc2_closing_balance.credit_in_account_currency, 500)
+
+ def make_period_closing_voucher(self, posting_date=None, submit=True):
surplus_account = create_account()
cost_center = create_cost_center("Test Cost Center 1")
pcv = frappe.get_doc(
{
"doctype": "Period Closing Voucher",
- "transaction_date": today(),
- "posting_date": today(),
+ "transaction_date": posting_date or today(),
+ "posting_date": posting_date or today(),
"company": "Test PCV Company",
"fiscal_year": get_fiscal_year(today(), company="Test PCV Company")[0],
"cost_center": cost_center,
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index 41fdb6a..9fff6f4 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -300,6 +300,9 @@
if gl_map:
check_freezing_date(gl_map[0]["posting_date"], adv_adj)
+ is_opening = any(d.get("is_opening") == "Yes" for d in gl_map)
+ if gl_map[0]["voucher_type"] != "Period Closing Voucher":
+ validate_against_pcv(is_opening, gl_map[0]["posting_date"], gl_map[0]["company"])
for entry in gl_map:
make_entry(entry, adv_adj, update_outstanding, from_repost)
@@ -519,6 +522,9 @@
)
validate_accounting_period(gl_entries)
check_freezing_date(gl_entries[0]["posting_date"], adv_adj)
+
+ is_opening = any(d.get("is_opening") == "Yes" for d in gl_entries)
+ validate_against_pcv(is_opening, gl_entries[0]["posting_date"], gl_entries[0]["company"])
set_as_cancel(gl_entries[0]["voucher_type"], gl_entries[0]["voucher_no"])
for entry in gl_entries:
@@ -566,6 +572,28 @@
)
+def validate_against_pcv(is_opening, posting_date, company):
+ if is_opening and frappe.db.exists(
+ "Period Closing Voucher", {"docstatus": 1, "company": company}
+ ):
+ frappe.throw(
+ _("Opening Entry can not be created after Period Closing Voucher is created."),
+ title=_("Invalid Opening Entry"),
+ )
+
+ last_pcv_date = frappe.db.get_value(
+ "Period Closing Voucher", {"docstatus": 1, "company": company}, "max(posting_date)"
+ )
+
+ if last_pcv_date and getdate(posting_date) <= getdate(last_pcv_date):
+ message = _("Books have been closed till the period ending on {0}").format(
+ formatdate(last_pcv_date)
+ )
+ message += "</br >"
+ message += _("You cannot create any new accounting entries till this date.")
+ frappe.throw(message, title=_("Period Closed"))
+
+
def set_as_cancel(voucher_type, voucher_no):
"""
Set is_cancelled=1 in all original gl entries for the voucher
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 3af01fd..0a9dadb 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,110 @@
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 = []
- if not flt(filters.with_period_closing_entry):
- additional_conditions += " and ifnull(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,
- )
-
- if filters.project:
- additional_conditions += " and project = %(project)s"
-
- if filters.get("include_default_book_entries"):
- additional_conditions += (
- " AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
- )
- else:
- additional_conditions += " AND (finance_book in (%(finance_book)s, '') OR finance_book IS NULL)"
+ last_period_closing_voucher = frappe.db.get_all(
+ "Period Closing Voucher",
+ filters={"docstatus": 1, "company": filters.company, "posting_date": ("<", filters.from_date)},
+ fields=["posting_date", "name"],
+ order_by="posting_date desc",
+ limit=1,
+ )
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"),
- }
+ if last_period_closing_voucher:
+ gle = get_opening_balance(
+ "Account Closing Balance",
+ filters,
+ report_type,
+ accounting_dimensions,
+ period_closing_voucher=last_period_closing_voucher[0].name,
+ )
+ if getdate(last_period_closing_voucher[0].posting_date) < getdate(
+ add_days(filters.from_date, -1)
+ ):
+ filters.from_date = add_days(last_period_closing_voucher, 1)
+ gle = get_opening_balance("GL Entry", filters, report_type, accounting_dimensions)
+ else:
+ 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, period_closing_voucher=None
+):
+ 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 == "Account Closing Balance":
+ if period_closing_voucher:
+ opening_balance = opening_balance.where(
+ closing_balance.period_closing_voucher == period_closing_voucher
+ )
+ else:
+ 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):
+ if doctype == "Account 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"])
+ 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:
+ opening_balance = opening_balance.where(closing_balance.project == filters.project)
+
+ if filters.get("include_default_book_entries"):
+ opening_balance = opening_balance.where(
+ (closing_balance.finance_book.isin([cstr(filters.finance_book), cstr(filters.company_fb), ""]))
+ | (closing_balance.finance_book.isnull())
+ )
+ else:
+ 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 +253,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):
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 2abd65b..0a0c792 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -323,6 +323,8 @@
erpnext.patches.v14_0.change_autoname_for_tax_withheld_vouchers
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
# below 2 migration patches should always run last
erpnext.patches.v14_0.migrate_gl_to_payment_ledger
erpnext.patches.v14_0.migrate_remarks_from_gl_to_payment_ledger
diff --git a/erpnext/patches/v14_0/create_accounting_dimensions_for_closing_balance.py b/erpnext/patches/v14_0/create_accounting_dimensions_for_closing_balance.py
new file mode 100644
index 0000000..43ad0d7
--- /dev/null
+++ b/erpnext/patches/v14_0/create_accounting_dimensions_for_closing_balance.py
@@ -0,0 +1,31 @@
+import frappe
+from frappe.custom.doctype.custom_field.custom_field import create_custom_field
+
+
+def execute():
+ accounting_dimensions = frappe.db.get_all(
+ "Accounting Dimension", fields=["fieldname", "label", "document_type", "disabled"]
+ )
+
+ if not accounting_dimensions:
+ return
+
+ doctype = "Account Closing Balance"
+
+ for d in accounting_dimensions:
+ field = frappe.db.get_value("Custom Field", {"dt": doctype, "fieldname": d.fieldname})
+
+ if field:
+ continue
+
+ df = {
+ "fieldname": d.fieldname,
+ "label": d.label,
+ "fieldtype": "Link",
+ "options": d.document_type,
+ "insert_after": "accounting_dimensions_section",
+ }
+
+ create_custom_field(doctype, df, ignore_validate=True)
+
+ frappe.clear_cache(doctype=doctype)
diff --git a/erpnext/patches/v14_0/update_closing_balances.py b/erpnext/patches/v14_0/update_closing_balances.py
new file mode 100644
index 0000000..0442b36
--- /dev/null
+++ b/erpnext/patches/v14_0/update_closing_balances.py
@@ -0,0 +1,31 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# License: MIT. See LICENSE
+
+
+import frappe
+
+from erpnext.accounts.doctype.closing_balance.closing_balance import make_closing_entries
+from erpnext.accounts.utils import get_fiscal_year
+
+
+def execute():
+ company_wise_order = {}
+ get_opening_entries = True
+ for pcv in frappe.db.get_all(
+ "Period Closing Voucher",
+ fields=["company", "posting_date", "name"],
+ filters={"docstatus": 1},
+ order_by="posting_date",
+ ):
+
+ company_wise_order.setdefault(pcv.company, [])
+ if pcv.posting_date not in company_wise_order[pcv.company]:
+ pcv_doc = frappe.get_doc("Period Closing Voucher", pcv.name)
+ pcv_doc.year_start_date = get_fiscal_year(
+ pcv.posting_date, pcv.fiscal_year, company=pcv.company
+ )[1]
+ gl_entries = pcv_doc.get_gl_entries()
+ 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