chore: revert get_reserve_qty method
diff --git a/erpnext/stock/stock_balance.py b/erpnext/stock/stock_balance.py
index ab6e18e..439ed7a 100644
--- a/erpnext/stock/stock_balance.py
+++ b/erpnext/stock/stock_balance.py
@@ -3,11 +3,7 @@
import frappe
-from frappe.query_builder import DocType
-from frappe.query_builder.functions import Sum
-from frappe.query_builder.utils import Table
from frappe.utils import cstr, flt, now, nowdate, nowtime
-from pypika.queries import QueryBuilder
from erpnext.controllers.stock_controller import create_repost_item_valuation_entry
@@ -98,85 +94,51 @@
def get_reserved_qty(item_code, warehouse):
- def append_open_so_query(q: QueryBuilder, child_table: Table) -> QueryBuilder:
- return (
- q.inner_join(SalesOrder)
- .on(SalesOrder.name == child_table.parent)
- .where(SalesOrder.docstatus == 1)
- .where(SalesOrder.status.notin(["On Hold", "Closed"]))
- )
-
- SalesOrder = DocType("Sales Order")
- SalesOrderItem = DocType("Sales Order Item")
- PackedItem = DocType("Packed Item")
-
- dont_reserve_qty_on_sales_return = frappe.db.get_single_value(
- "Selling Settings", "dont_reserve_sales_order_qty_on_sales_return"
- )
-
- tab = (
- frappe.qb.from_(SalesOrderItem)
- .where(SalesOrderItem.item_code == item_code)
- .where(SalesOrderItem.warehouse == warehouse)
- )
- for field, cond in [
- (SalesOrderItem.stock_qty.as_("dnpi_qty"), 1),
- (SalesOrderItem.qty.as_("so_item_qty"), 1),
- (SalesOrderItem.delivered_qty.as_("so_item_delivered_qty"), 1),
- (SalesOrderItem.returned_qty.as_("so_item_returned_qty"), dont_reserve_qty_on_sales_return),
- (SalesOrderItem.parent, 1),
- (SalesOrderItem.name, 1),
- ]:
- if cond:
- tab = tab.select(field)
- tab = append_open_so_query(tab, SalesOrderItem)
-
- dnpi = (
- frappe.qb.from_(PackedItem)
- .select(PackedItem.qty, PackedItem.parent_detail_docname, PackedItem.parent, PackedItem.name)
- .where(PackedItem.item_code == item_code)
- .where(PackedItem.warehouse == warehouse)
- )
- dnpi = append_open_so_query(dnpi, PackedItem)
-
- dnpi_parent = frappe.qb.from_(dnpi).select(dnpi.qty.as_("dnpi_qty"))
- for key, so_item_field, cond in [
- ("so_item_qty", "qty", 1),
- ("so_item_delivered_qty", "delivered_qty", 1),
- ("so_item_returned_qty", "returned_qty", dont_reserve_qty_on_sales_return),
- ]:
- if cond:
- dnpi_parent = dnpi_parent.select(
- (
- frappe.qb.from_(SalesOrderItem)
- .select(SalesOrderItem[so_item_field])
- .where(SalesOrderItem.name == dnpi.parent_detail_docname)
- .where(SalesOrderItem.delivered_by_supplier == 0)
- ).as_(key)
- )
- dnpi_parent = dnpi_parent.select(dnpi.parent, dnpi.name)
-
- dnpi_parent = dnpi_parent + tab
-
- q = (
- frappe.qb.from_(dnpi_parent)
- .select(
- Sum(
- dnpi_parent.dnpi_qty
- * (
+ reserved_qty = frappe.db.sql(
+ """
+ select
+ sum(dnpi_qty * ((so_item_qty - so_item_delivered_qty) / so_item_qty))
+ from
+ (
+ (select
+ qty as dnpi_qty,
(
- dnpi_parent.so_item_qty
- - dnpi_parent.so_item_delivered_qty
- - (dnpi_parent.so_item_returned_qty if dont_reserve_qty_on_sales_return else 0)
- )
- / dnpi_parent.so_item_qty
- )
- )
- )
- .where(dnpi_parent.so_item_qty >= dnpi_parent.so_item_delivered_qty)
+ select qty from `tabSales Order Item`
+ where name = dnpi.parent_detail_docname
+ and (delivered_by_supplier is null or delivered_by_supplier = 0)
+ ) as so_item_qty,
+ (
+ select delivered_qty from `tabSales Order Item`
+ where name = dnpi.parent_detail_docname
+ and delivered_by_supplier = 0
+ ) as so_item_delivered_qty,
+ parent, name
+ from
+ (
+ select qty, parent_detail_docname, parent, name
+ from `tabPacked Item` dnpi_in
+ where item_code = %s and warehouse = %s
+ and parenttype='Sales Order'
+ and item_code != parent_item
+ and exists (select * from `tabSales Order` so
+ where name = dnpi_in.parent and docstatus = 1 and status not in ('On Hold', 'Closed'))
+ ) dnpi)
+ union
+ (select stock_qty as dnpi_qty, qty as so_item_qty,
+ delivered_qty as so_item_delivered_qty, parent, name
+ from `tabSales Order Item` so_item
+ where item_code = %s and warehouse = %s
+ and (so_item.delivered_by_supplier is null or so_item.delivered_by_supplier = 0)
+ and exists(select * from `tabSales Order` so
+ where so.name = so_item.parent and so.docstatus = 1
+ and so.status not in ('On Hold', 'Closed')))
+ ) tab
+ where
+ so_item_qty >= so_item_delivered_qty
+ """,
+ (item_code, warehouse, item_code, warehouse),
)
- reserved_qty = q.run()
return flt(reserved_qty[0][0]) if reserved_qty else 0