Merge pull request #32613 from rohitwaghchaure/group-warehouse-filter
fix: group warehouse filter not working for Batch-wise Balance history report
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index ab784ca..6e06d23 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -9,6 +9,7 @@
from frappe.contacts.address_and_contact import load_address_and_contact
from frappe.utils import cint, flt
from frappe.utils.nestedset import NestedSet
+from pypika.terms import ExistsCriterion
from erpnext.stock import get_warehouse_account
@@ -266,3 +267,23 @@
frappe.throw(_("Warehouse not found against the account {0}").format(account))
return warehouses
+
+
+# Will be use for frappe.qb
+def apply_warehouse_filter(query, sle, filters):
+ if warehouse := filters.get("warehouse"):
+ warehouse_table = frappe.qb.DocType("Warehouse")
+
+ lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
+ chilren_subquery = (
+ frappe.qb.from_(warehouse_table)
+ .select(warehouse_table.name)
+ .where(
+ (warehouse_table.lft >= lft)
+ & (warehouse_table.rgt <= rgt)
+ & (warehouse_table.name == sle.warehouse)
+ )
+ )
+ query = query.where(ExistsCriterion(chilren_subquery))
+
+ return query
diff --git a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
index 8a13300..291c6b5 100644
--- a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
+++ b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
@@ -5,6 +5,9 @@
import frappe
from frappe import _
from frappe.utils import cint, flt, getdate
+from pypika import functions as fn
+
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
def execute(filters=None):
@@ -64,36 +67,42 @@
return columns
-def get_conditions(filters):
- conditions = ""
+# get all details
+def get_stock_ledger_entries(filters):
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
- if filters.get("to_date"):
- conditions += " and posting_date <= '%s'" % filters["to_date"]
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ sle.warehouse,
+ sle.batch_no,
+ sle.posting_date,
+ fn.Sum(sle.actual_qty).as_("actual_qty"),
+ )
+ .where(
+ (sle.docstatus < 2)
+ & (sle.is_cancelled == 0)
+ & (sle.batch_no.isnotnull())
+ & (sle.batch_no != "")
+ )
+ .groupby(sle.voucher_no, sle.batch_no, sle.item_code, sle.warehouse)
+ .orderby(sle.item_code, sle.warehouse)
+ )
+
+ if to_date := filters.get("to_date"):
+ query = query.where(sle.posting_date <= to_date)
else:
frappe.throw(_("'To Date' is required"))
- for field in ["item_code", "warehouse", "batch_no", "company"]:
+ query = apply_warehouse_filter(query, sle, filters)
+ for field in ["item_code", "batch_no", "company"]:
if filters.get(field):
- conditions += " and {0} = {1}".format(field, frappe.db.escape(filters.get(field)))
+ query = query.where(sle[field] == filters.get(field))
- return conditions
-
-
-# get all details
-def get_stock_ledger_entries(filters):
- conditions = get_conditions(filters)
- return frappe.db.sql(
- """
- select item_code, batch_no, warehouse, posting_date, sum(actual_qty) as actual_qty
- from `tabStock Ledger Entry`
- where is_cancelled = 0 and docstatus < 2 and ifnull(batch_no, '') != '' %s
- group by voucher_no, batch_no, item_code, warehouse
- order by item_code, warehouse"""
- % conditions,
- as_dict=1,
- )
+ return query.run(as_dict=True)
def get_item_warehouse_batch_map(filters, float_precision):
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 679d234..0fc642e 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -10,10 +10,10 @@
from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, date_diff, flt, getdate
from frappe.utils.nestedset import get_descendants_of
-from pypika.terms import ExistsCriterion
import erpnext
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
from erpnext.stock.utils import add_additional_uom_columns, is_reposting_item_valuation_in_progress
@@ -270,18 +270,8 @@
if company := filters.get("company"):
query = query.where(sle.company == company)
- if warehouse := filters.get("warehouse"):
- lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
- chilren_subquery = (
- frappe.qb.from_(warehouse_table)
- .select(warehouse_table.name)
- .where(
- (warehouse_table.lft >= lft)
- & (warehouse_table.rgt <= rgt)
- & (warehouse_table.name == sle.warehouse)
- )
- )
- query = query.where(ExistsCriterion(chilren_subquery))
+ if filters.get("warehouse"):
+ query = apply_warehouse_filter(query, sle, filters)
elif warehouse_type := filters.get("warehouse_type"):
query = (
query.join(warehouse_table)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index e18d4c7..a951197 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -6,11 +6,11 @@
from frappe import _
from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, flt
-from pypika.terms import ExistsCriterion
from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import get_stock_balance_for
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.utils import (
is_reposting_item_valuation_in_progress,
update_included_uom_in_report,
@@ -295,20 +295,7 @@
if filters.get(field):
query = query.where(sle[field] == filters.get(field))
- if warehouse := filters.get("warehouse"):
- lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
-
- warehouse_table = frappe.qb.DocType("Warehouse")
- chilren_subquery = (
- frappe.qb.from_(warehouse_table)
- .select(warehouse_table.name)
- .where(
- (warehouse_table.lft >= lft)
- & (warehouse_table.rgt <= rgt)
- & (warehouse_table.name == sle.warehouse)
- )
- )
- query = query.where(ExistsCriterion(chilren_subquery))
+ query = apply_warehouse_filter(query, sle, filters)
return query.run(as_dict=True)