Stock Balance: Show records based on all child item groups if filters set based on group node
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 1203625..2db741d 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -67,8 +67,15 @@
else:
frappe.throw(_("'To Date' is required"))
- if filters.get("item_group"):
- conditions += " and item.item_group = '%s'" % frappe.db.escape(filters.get("item_group"), percent=False)
+ if filters.get("item_group"):
+ ig_details = frappe.db.get_value("Item Group", filters.get("item_group"),
+ ["lft", "rgt"], as_dict=1)
+
+ if ig_details:
+ conditions += """
+ and exists (select name from `tabItem Group` ig
+ where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)
+ """ % (ig_details.lft, ig_details.rgt)
if filters.get("item_code"):
conditions += " and sle.item_code = '%s'" % frappe.db.escape(filters.get("item_code"), percent=False)
@@ -89,10 +96,14 @@
if filters.get("item_group"):
join_table_query = "inner join `tabItem` item on item.name = sle.item_code"
- return frappe.db.sql("""select sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
+ return frappe.db.sql("""
+ select
+ sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
sle.company, sle.voucher_type, sle.qty_after_transaction, sle.stock_value_difference
- from `tabStock Ledger Entry` sle force index (posting_sort_index) %s
- where sle.docstatus < 2 %s order by sle.posting_date, sle.posting_time, sle.name""" %
+ from
+ `tabStock Ledger Entry` sle force index (posting_sort_index) %s
+ where sle.docstatus < 2 %s
+ order by sle.posting_date, sle.posting_time, sle.name""" %
(join_table_query, conditions), as_dict=1)
def get_item_warehouse_map(filters):