[minor][fix] Show all warehouses with item actual qty in warehouse set query
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index be0cb02..626c20e 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -5,6 +5,7 @@
import frappe
from frappe.desk.reportview import get_match_cond, get_filters_cond
from frappe.utils import nowdate
+from collections import defaultdict
# searches for active employees
@@ -353,25 +354,41 @@
@frappe.whitelist()
def warehouse_query(doctype, txt, searchfield, start, page_len, filters):
# Should be used when item code is passed in filters.
- conditions = []
- response = frappe.db.sql("""select distinct `tabWarehouse`.name,
- CONCAT_WS(" : ", "Actual Qty", ifnull(round(`tabBin`.actual_qty, 2), 0))
- from `tabWarehouse` INNER JOIN `tabBin`
- on `tabWarehouse`.name = `tabBin`.warehouse
- where
- `tabWarehouse`.`{key}` like %(txt)s
- {fcond} {mcond}
- order by
- `tabWarehouse`.name, `tabBin`.actual_qty desc
- limit
- %(start)s, %(page_len)s """.format(
- key=frappe.db.escape(searchfield),
- fcond=get_filters_cond(doctype, filters, conditions),
- mcond=get_match_cond(doctype)
- ),
- {
- "txt": "%%%s%%" % frappe.db.escape(txt),
- "start": start,
- "page_len": page_len,
- })
+ conditions, bin_conditions = [], []
+ filter_dict = get_doctype_wise_filters(filters)
+
+ sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin`
+ where `tabBin`.warehouse = `tabWarehouse`.name
+ {bin_conditions} """.format(
+ bin_conditions=get_filters_cond(doctype, filter_dict.get("Bin"), bin_conditions))
+
+ response = frappe.db.sql("""select `tabWarehouse`.name,
+ CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty
+ from `tabWarehouse`
+ where
+ `tabWarehouse`.`{key}` like %(txt)s
+ {fcond} {mcond}
+ order by
+ `tabWarehouse`.name desc
+ limit
+ %(start)s, %(page_len)s
+ """.format(
+ sub_query=sub_query,
+ key=frappe.db.escape(searchfield),
+ fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions),
+ mcond=get_match_cond(doctype)
+ ),
+ {
+ "txt": "%%%s%%" % frappe.db.escape(txt),
+ "start": start,
+ "page_len": page_len
+ })
return response
+
+
+def get_doctype_wise_filters(filters):
+ # Helper function to seperate filters doctype_wise
+ filter_dict = defaultdict(list)
+ for row in filters:
+ filter_dict[row[0]].append(row)
+ return filter_dict