refactor: move Bin queries to qb/orm (#28522)
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 48b1cc5..da9c66d 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -4,6 +4,8 @@
import frappe
from frappe.model.document import Document
+from frappe.query_builder import Case
+from frappe.query_builder.functions import Coalesce, Sum
from frappe.utils import flt, nowdate
@@ -19,34 +21,42 @@
- flt(self.reserved_qty_for_production) - flt(self.reserved_qty_for_sub_contract))
def get_first_sle(self):
- sle = frappe.db.sql("""
- select * from `tabStock Ledger Entry`
- where item_code = %s
- and warehouse = %s
- order by timestamp(posting_date, posting_time) asc, creation asc
- limit 1
- """, (self.item_code, self.warehouse), as_dict=1)
- return sle and sle[0] or None
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ first_sle = (
+ frappe.qb.from_(sle)
+ .select("*")
+ .where((sle.item_code == self.item_code) & (sle.warehouse == self.warehouse))
+ .orderby(sle.posting_date, sle.posting_time, sle.creation)
+ .limit(1)
+ ).run(as_dict=True)
+
+ return first_sle and first_sle[0] or None
def update_reserved_qty_for_production(self):
'''Update qty reserved for production from Production Item tables
in open work orders'''
- self.reserved_qty_for_production = frappe.db.sql('''
- SELECT
- CASE WHEN ifnull(skip_transfer, 0) = 0 THEN
- SUM(item.required_qty - item.transferred_qty)
- ELSE
- SUM(item.required_qty - item.consumed_qty)
- END
- FROM `tabWork Order` pro, `tabWork Order Item` item
- WHERE
- item.item_code = %s
- and item.parent = pro.name
- and pro.docstatus = 1
- and item.source_warehouse = %s
- and pro.status not in ("Stopped", "Completed")
- and (item.required_qty > item.transferred_qty or item.required_qty > item.consumed_qty)
- ''', (self.item_code, self.warehouse))[0][0]
+
+ wo = frappe.qb.DocType("Work Order")
+ wo_item = frappe.qb.DocType("Work Order Item")
+
+ self.reserved_qty_for_production = (
+ frappe.qb
+ .from_(wo)
+ .from_(wo_item)
+ .select(Case()
+ .when(wo.skip_transfer == 0, Sum(wo_item.required_qty - wo_item.transferred_qty))
+ .else_(Sum(wo_item.required_qty - wo_item.consumed_qty))
+ )
+ .where(
+ (wo_item.item_code == self.item_code)
+ & (wo_item.parent == wo.name)
+ & (wo.docstatus == 1)
+ & (wo_item.source_warehouse == self.warehouse)
+ & (wo.status.notin(["Stopped", "Completed"]))
+ & ((wo_item.required_qty > wo_item.transferred_qty)
+ | (wo_item.required_qty > wo_item.consumed_qty))
+ )
+ ).run()[0][0] or 0.0
self.set_projected_qty()
@@ -55,36 +65,53 @@
def update_reserved_qty_for_sub_contracting(self):
#reserved qty
- reserved_qty_for_sub_contract = frappe.db.sql('''
- select ifnull(sum(itemsup.required_qty),0)
- from `tabPurchase Order` po, `tabPurchase Order Item Supplied` itemsup
- where
- itemsup.rm_item_code = %s
- and itemsup.parent = po.name
- and po.docstatus = 1
- and po.is_subcontracted = 'Yes'
- and po.status != 'Closed'
- and po.per_received < 100
- and itemsup.reserve_warehouse = %s''', (self.item_code, self.warehouse))[0][0]
- #Get Transferred Entries
- materials_transferred = frappe.db.sql("""
- select
- ifnull(sum(CASE WHEN se.is_return = 1 THEN (transfer_qty * -1) ELSE transfer_qty END),0)
- from
- `tabStock Entry` se, `tabStock Entry Detail` sed, `tabPurchase Order` po
- where
- se.docstatus=1
- and se.purpose='Send to Subcontractor'
- and ifnull(se.purchase_order, '') !=''
- and (sed.item_code = %(item)s or sed.original_item = %(item)s)
- and se.name = sed.parent
- and se.purchase_order = po.name
- and po.docstatus = 1
- and po.is_subcontracted = 'Yes'
- and po.status != 'Closed'
- and po.per_received < 100
- """, {'item': self.item_code})[0][0]
+ po = frappe.qb.DocType("Purchase Order")
+ supplied_item = frappe.qb.DocType("Purchase Order Item Supplied")
+
+ reserved_qty_for_sub_contract = (
+ frappe.qb
+ .from_(po)
+ .from_(supplied_item)
+ .select(Sum(Coalesce(supplied_item.required_qty, 0)))
+ .where(
+ (supplied_item.rm_item_code == self.item_code)
+ & (po.name == supplied_item.parent)
+ & (po.docstatus == 1)
+ & (po.is_subcontracted == "Yes")
+ & (po.status != "Closed")
+ & (po.per_received < 100)
+ & (supplied_item.reserve_warehouse == self.warehouse)
+ )
+ ).run()[0][0] or 0.0
+
+ se = frappe.qb.DocType("Stock Entry")
+ se_item = frappe.qb.DocType("Stock Entry Detail")
+
+ materials_transferred = (
+ frappe.qb
+ .from_(se)
+ .from_(se_item)
+ .from_(po)
+ .select(Sum(
+ Case()
+ .when(se.is_return == 1, se_item.transfer_qty * -1)
+ .else_(se_item.transfer_qty)
+ ))
+ .where(
+ (se.docstatus == 1)
+ & (se.purpose == "Send to Subcontractor")
+ & (Coalesce(se.purchase_order, "") != "")
+ & ((se_item.item_code == self.item_code)
+ | (se_item.original_item == self.item_code))
+ & (se.name == se_item.parent)
+ & (po.name == se.purchase_order)
+ & (po.docstatus == 1)
+ & (po.is_subcontracted == "Yes")
+ & (po.status != "Closed")
+ & (po.per_received < 100)
+ )
+ ).run()[0][0] or 0.0
if reserved_qty_for_sub_contract > materials_transferred:
reserved_qty_for_sub_contract = reserved_qty_for_sub_contract - materials_transferred
@@ -160,4 +187,4 @@
'indented_qty': indented_qty,
'planned_qty': planned_qty,
'projected_qty': projected_qty
- })
\ No newline at end of file
+ })