feat: Nested set filtering for accounting dimension (#20860)
* feat: Nested set filtering for accounting dimension
* fix: Remove print statement
diff --git a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
index 522ed4f..462d967 100644
--- a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
+++ b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
@@ -172,7 +172,7 @@
return doclist
def get_accounting_dimensions(as_list=True):
- accounting_dimensions = frappe.get_all("Accounting Dimension", fields=["label", "fieldname", "disabled"])
+ accounting_dimensions = frappe.get_all("Accounting Dimension", fields=["label", "fieldname", "disabled", "document_type"])
if as_list:
return [d.fieldname for d in accounting_dimensions]
@@ -186,6 +186,18 @@
return dimensions
+def get_dimension_with_children(doctype, dimension):
+
+ if isinstance(dimension, list):
+ dimension = dimension[0]
+
+ all_dimensions = []
+ lft, rgt = frappe.db.get_value(doctype, dimension, ["lft", "rgt"])
+ children = frappe.get_all(doctype, filters={"lft": [">=", lft], "rgt": ["<=", rgt]})
+ all_dimensions += [c.name for c in children]
+
+ return all_dimensions
+
@frappe.whitelist()
def get_dimension_filters():
dimension_filters = frappe.db.sql("""
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 0438f6d..240b0d8 100755
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -7,7 +7,7 @@
from frappe.utils import getdate, nowdate, flt, cint, formatdate, cstr, now, time_diff_in_seconds
from collections import OrderedDict
from erpnext.accounts.utils import get_currency_precision
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
# This report gives a summary of all Outstanding Invoices considering the following
@@ -603,7 +603,6 @@
self.add_supplier_filters(conditions, values)
self.add_accounting_dimensions_filters(conditions, values)
-
return " and ".join(conditions), values
def get_order_by_condition(self):
@@ -666,13 +665,16 @@
doctype=doctype, lft=lft, rgt=rgt, key=key)
def add_accounting_dimensions_filters(self, conditions, values):
- accounting_dimensions = get_accounting_dimensions()
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
- if self.filters.get(dimension):
- conditions.append("{0} = %s".format(dimension))
- values.append(self.filters.get(dimension))
+ if self.filters.get(dimension.fieldname):
+ if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
+ self.filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
+ self.filters.get(dimension.fieldname))
+ conditions.append("{0} in %s".format(dimension.fieldname))
+ values.append(tuple(self.filters.get(dimension.fieldname)))
def get_gle_balance(self, gle):
# get the balance of the GL (debit - credit) or reverse balance based on report type
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 35915d0..080a7c9 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -16,7 +16,7 @@
from frappe.utils import (flt, getdate, get_first_day, add_months, add_days, formatdate, cstr)
from six import itervalues
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
+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,
company=None, reset_period_on_fy_change=True):
@@ -389,7 +389,7 @@
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
- accounting_dimensions = get_accounting_dimensions()
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
if ignore_closing_entries:
additional_conditions.append("ifnull(voucher_type, '')!='Period Closing Voucher'")
@@ -412,11 +412,14 @@
additional_conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)")
else:
additional_conditions.append("(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)")
-
+
if accounting_dimensions:
for dimension in accounting_dimensions:
- if filters.get(dimension):
- additional_conditions.append("{0} in (%({0})s)".format(dimension))
+ if filters.get(dimension.fieldname):
+ if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
+ filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
+ filters.get(dimension.fieldname))
+ additional_conditions.append("{0} in %({0})s".format(dimension.fieldname))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index 8bea365..8750c23 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -10,7 +10,7 @@
from erpnext.accounts.utils import get_account_currency
from erpnext.accounts.report.financial_statements import get_cost_centers_with_children
from six import iteritems
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
from collections import OrderedDict
def execute(filters=None):
@@ -194,12 +194,15 @@
if match_conditions:
conditions.append(match_conditions)
- accounting_dimensions = get_accounting_dimensions()
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
- if filters.get(dimension):
- conditions.append("{0} in (%({0})s)".format(dimension))
+ if filters.get(dimension.fieldname):
+ if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
+ filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
+ filters.get(dimension.fieldname))
+ conditions.append("{0} in %({0})s".format(dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index 2be90bc..9864e40 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -6,7 +6,7 @@
from frappe.utils import flt
from frappe import msgprint, _
from frappe.model.meta import get_field_precision
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
def execute(filters=None):
return _execute(filters)
@@ -341,14 +341,18 @@
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
- accounting_dimensions = get_accounting_dimensions()
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
- if filters.get(dimension):
+ if filters.get(dimension.fieldname):
+ if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
+ filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
+ filters.get(dimension.fieldname))
+
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
- and ifnull(`tabSales Invoice Item`.{0}, '') = %({0})s)""".format(dimension)
+ and ifnull(`tabSales Invoice Item`.{0}, '') in %({0})s)""".format(dimension.fieldname)
return conditions
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 69285cc..5fe6b41 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -7,7 +7,7 @@
from frappe.utils import flt, getdate, formatdate, cstr
from erpnext.accounts.report.financial_statements \
import filter_accounts, set_gl_entries_by_account, filter_out_zero_value_rows
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
value_fields = ("opening_debit", "opening_credit", "debit", "credit", "closing_debit", "closing_credit")
@@ -109,7 +109,7 @@
additional_conditions += fb_conditions
- accounting_dimensions = get_accounting_dimensions()
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
query_filters = {
"company": filters.company,
@@ -122,11 +122,14 @@
if accounting_dimensions:
for dimension in accounting_dimensions:
- if filters.get(dimension):
- additional_conditions += """ and {0} in (%({0})s) """.format(dimension)
+ if filters.get(dimension.fieldname):
+ if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
+ filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
+ filters.get(dimension.fieldname))
+ additional_conditions += "and {0} in %({0})s".format(dimension.fieldname)
query_filters.update({
- dimension: filters.get(dimension)
+ dimension.fieldname: filters.get(dimension.fieldname)
})
gle = frappe.db.sql("""