Merge branch 'develop' into refactor/qb/pick-list
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index 9e6aead..cecc588 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -11,7 +11,7 @@
from frappe.model.document import Document
from frappe.model.mapper import map_child_doc
from frappe.query_builder import Case
-from frappe.query_builder.functions import Locate
+from frappe.query_builder.functions import IfNull, Locate, Sum
from frappe.utils import cint, floor, flt, today
from frappe.utils.nestedset import get_descendants_of
@@ -441,42 +441,30 @@
def get_available_item_locations_for_batched_item(
item_code, from_warehouses, required_qty, company
):
- warehouse_condition = "and warehouse in %(warehouses)s" if from_warehouses else ""
- batch_locations = frappe.db.sql(
- """
- SELECT
- sle.`warehouse`,
- sle.`batch_no`,
- SUM(sle.`actual_qty`) AS `qty`
- FROM
- `tabStock Ledger Entry` sle, `tabBatch` batch
- WHERE
- sle.batch_no = batch.name
- and sle.`item_code`=%(item_code)s
- and sle.`company` = %(company)s
- and batch.disabled = 0
- and sle.is_cancelled=0
- and IFNULL(batch.`expiry_date`, '2200-01-01') > %(today)s
- {warehouse_condition}
- GROUP BY
- sle.`warehouse`,
- sle.`batch_no`,
- sle.`item_code`
- HAVING `qty` > 0
- ORDER BY IFNULL(batch.`expiry_date`, '2200-01-01'), batch.`creation`, sle.`batch_no`, sle.`warehouse`
- """.format(
- warehouse_condition=warehouse_condition
- ),
- { # nosec
- "item_code": item_code,
- "company": company,
- "today": today(),
- "warehouses": from_warehouses,
- },
- as_dict=1,
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ batch = frappe.qb.DocType("Batch")
+
+ query = (
+ frappe.qb.from_(sle)
+ .from_(batch)
+ .select(sle.warehouse, sle.batch_no, Sum(sle.actual_qty).as_("qty"))
+ .where(
+ (sle.batch_no == batch.name)
+ & (sle.item_code == item_code)
+ & (sle.company == company)
+ & (batch.disabled == 0)
+ & (sle.is_cancelled == 0)
+ & (IfNull(batch.expiry_date, "2200-01-01") > today())
+ )
+ .groupby(sle.warehouse, sle.batch_no, sle.item_code)
+ .having(Sum(sle.actual_qty) > 0)
+ .orderby(IfNull(batch.expiry_date, "2200-01-01"), batch.creation, sle.batch_no, sle.warehouse)
)
- return batch_locations
+ if from_warehouses:
+ query = query.where(sle.warehouse.isin(from_warehouses))
+
+ return query.run(as_dict=True)
def get_available_item_locations_for_serial_and_batched_item(