Improve performance for warehouse tree (#15207)
* Fix conflicts
* Modify query structure
* Remove whitespaces
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index 0eee6ba..ea476e6 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -139,7 +139,7 @@
@frappe.whitelist()
def get_children(doctype, parent=None, company=None, is_root=False):
- from erpnext.stock.utils import get_stock_value_on
+ from erpnext.stock.utils import get_stock_value_from_bin
if is_root:
parent = ""
@@ -154,7 +154,7 @@
# return warehouses
for wh in warehouses:
- wh["balance"] = get_stock_value_on(warehouse=wh.value)
+ wh["balance"] = get_stock_value_from_bin(warehouse=wh.value)
return warehouses
@frappe.whitelist()
@@ -170,4 +170,4 @@
@frappe.whitelist()
def convert_to_group_or_ledger():
args = frappe.form_dict
- return frappe.get_doc("Warehouse", args.docname).convert_to_group_or_ledger()
\ No newline at end of file
+ return frappe.get_doc("Warehouse", args.docname).convert_to_group_or_ledger()
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index 9b2fcb7..6140c17 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -9,21 +9,45 @@
class InvalidWarehouseCompany(frappe.ValidationError): pass
+def get_stock_value_from_bin(warehouse=None, item_code=None):
+ values = {}
+ conditions = ""
+ if warehouse:
+ conditions += """ and warehouse in (
+ select w2.name from `tabWarehouse` w1
+ join `tabWarehouse` w2 on
+ w1.name = %(warehouse)s
+ and w2.lft between w1.lft and w1.rgt
+ ) """
+
+ values['warehouse'] = warehouse
+
+ if item_code:
+ conditions += " and item_code = %(item_code)s"
+
+ values['item_code'] = item_code
+
+ query = "select sum(stock_value) from `tabBin` where 1 = 1 %s" % conditions
+
+ stock_value = frappe.db.sql(query, values)
+
+ return stock_value
+
def get_stock_value_on(warehouse=None, posting_date=None, item_code=None):
if not posting_date: posting_date = nowdate()
values, condition = [posting_date], ""
if warehouse:
-
+
lft, rgt, is_group = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt", "is_group"])
-
+
if is_group:
values.extend([lft, rgt])
condition += "and exists (\
select name from `tabWarehouse` wh where wh.name = sle.warehouse\
and wh.lft >= %s and wh.rgt <= %s)"
-
+
else:
values.append(warehouse)
condition += " AND warehouse = %s"
@@ -43,7 +67,7 @@
for sle in stock_ledger_entries:
if not sle_map.has_key((sle.item_code, sle.warehouse)):
sle_map[(sle.item_code, sle.warehouse)] = flt(sle.stock_value)
-
+
return sum(sle_map.values())
@frappe.whitelist()
@@ -73,17 +97,17 @@
values, condition = [item_code], ""
if warehouse:
lft, rgt, is_group = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt", "is_group"])
-
+
if is_group:
values.extend([lft, rgt])
condition += "and exists (\
select name from `tabWarehouse` wh where wh.name = tabBin.warehouse\
and wh.lft >= %s and wh.rgt <= %s)"
-
+
else:
values.append(warehouse)
condition += " AND warehouse = %s"
-
+
actual_qty = frappe.db.sql("""select sum(actual_qty) from tabBin
where item_code=%s {0}""".format(condition), values)[0][0]
@@ -219,4 +243,3 @@
def is_group_warehouse(warehouse):
if frappe.db.get_value("Warehouse", warehouse, "is_group"):
frappe.throw(_("Group node warehouse is not allowed to select for transactions"))
-