Merge pull request #32295 from s-aga-r/refactor/report/bom-stock-report
refactor: rewrite `BOM Stock Report` queries in `QB`
diff --git a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
index ec4b25c..550445c 100644
--- a/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
+++ b/erpnext/manufacturing/report/bom_stock_calculated/bom_stock_calculated.py
@@ -146,7 +146,7 @@
)
)
else:
- query = query.where(bin.warehouse == frappe.db.escape(filters.get("warehouse")))
+ query = query.where(bin.warehouse == filters.get("warehouse"))
return query.run(as_dict=True)
diff --git a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
index 34e9826..1e1b435 100644
--- a/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
+++ b/erpnext/manufacturing/report/bom_stock_report/bom_stock_report.py
@@ -4,6 +4,8 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Floor, Sum
+from pypika.terms import ExistsCriterion
def execute(filters=None):
@@ -11,7 +13,6 @@
filters = {}
columns = get_columns()
-
data = get_bom_stock(filters)
return columns, data
@@ -33,59 +34,57 @@
def get_bom_stock(filters):
- conditions = ""
- bom = filters.get("bom")
-
- table = "`tabBOM Item`"
- qty_field = "stock_qty"
-
- qty_to_produce = filters.get("qty_to_produce", 1)
- if int(qty_to_produce) <= 0:
+ qty_to_produce = filters.get("qty_to_produce") or 1
+ if int(qty_to_produce) < 0:
frappe.throw(_("Quantity to Produce can not be less than Zero"))
if filters.get("show_exploded_view"):
- table = "`tabBOM Explosion Item`"
+ bom_item_table = "BOM Explosion Item"
+ else:
+ bom_item_table = "BOM Item"
+
+ bin = frappe.qb.DocType("Bin")
+ bom = frappe.qb.DocType("BOM")
+ bom_item = frappe.qb.DocType(bom_item_table)
+
+ query = (
+ frappe.qb.from_(bom)
+ .inner_join(bom_item)
+ .on(bom.name == bom_item.parent)
+ .left_join(bin)
+ .on(bom_item.item_code == bin.item_code)
+ .select(
+ bom_item.item_code,
+ bom_item.description,
+ bom_item.stock_qty,
+ bom_item.stock_uom,
+ bom_item.stock_qty * qty_to_produce / bom.quantity,
+ Sum(bin.actual_qty).as_("actual_qty"),
+ Sum(Floor(bin.actual_qty / (bom_item.stock_qty * qty_to_produce / bom.quantity))),
+ )
+ .where((bom_item.parent == filters.get("bom")) & (bom_item.parenttype == "BOM"))
+ .groupby(bom_item.item_code)
+ )
if filters.get("warehouse"):
warehouse_details = frappe.db.get_value(
"Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1
)
+
if warehouse_details:
- conditions += (
- " and exists (select name from `tabWarehouse` wh \
- where wh.lft >= %s and wh.rgt <= %s and ledger.warehouse = wh.name)"
- % (warehouse_details.lft, warehouse_details.rgt)
+ wh = frappe.qb.DocType("Warehouse")
+ query = query.where(
+ ExistsCriterion(
+ frappe.qb.from_(wh)
+ .select(wh.name)
+ .where(
+ (wh.lft >= warehouse_details.lft)
+ & (wh.rgt <= warehouse_details.rgt)
+ & (bin.warehouse == wh.name)
+ )
+ )
)
else:
- conditions += " and ledger.warehouse = %s" % frappe.db.escape(filters.get("warehouse"))
+ query = query.where(bin.warehouse == filters.get("warehouse"))
- else:
- conditions += ""
-
- return frappe.db.sql(
- """
- SELECT
- bom_item.item_code,
- bom_item.description ,
- bom_item.{qty_field},
- bom_item.stock_uom,
- bom_item.{qty_field} * {qty_to_produce} / bom.quantity,
- sum(ledger.actual_qty) as actual_qty,
- sum(FLOOR(ledger.actual_qty / (bom_item.{qty_field} * {qty_to_produce} / bom.quantity)))
- FROM
- `tabBOM` AS bom INNER JOIN {table} AS bom_item
- ON bom.name = bom_item.parent
- LEFT JOIN `tabBin` AS ledger
- ON bom_item.item_code = ledger.item_code
- {conditions}
- WHERE
- bom_item.parent = {bom} and bom_item.parenttype='BOM'
-
- GROUP BY bom_item.item_code""".format(
- qty_field=qty_field,
- table=table,
- conditions=conditions,
- bom=frappe.db.escape(bom),
- qty_to_produce=qty_to_produce or 1,
- )
- )
+ return query.run()