Merge pull request #32662 from s-aga-r/refactor/stock/remaining-reports
refactor: rewrite stock reports queries in qb
diff --git a/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py b/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
index f308e9e..a6fc049 100644
--- a/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
+++ b/erpnext/stock/report/itemwise_recommended_reorder_level/itemwise_recommended_reorder_level.py
@@ -3,6 +3,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Abs, Sum
from frappe.utils import flt, getdate
@@ -11,8 +12,6 @@
filters = {}
float_precision = frappe.db.get_default("float_precision")
- condition = get_condition(filters)
-
avg_daily_outgoing = 0
diff = ((getdate(filters.get("to_date")) - getdate(filters.get("from_date"))).days) + 1
if diff <= 0:
@@ -20,8 +19,8 @@
columns = get_columns()
items = get_item_info(filters)
- consumed_item_map = get_consumed_items(condition)
- delivered_item_map = get_delivered_items(condition)
+ consumed_item_map = get_consumed_items(filters)
+ delivered_item_map = get_delivered_items(filters)
data = []
for item in items:
@@ -71,76 +70,86 @@
def get_item_info(filters):
from erpnext.stock.report.stock_ledger.stock_ledger import get_item_group_condition
- conditions = [get_item_group_condition(filters.get("item_group"))]
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- conditions.append("is_stock_item = 1")
-
- return frappe.db.sql(
- """select name, item_name, description, brand, item_group,
- safety_stock, lead_time_days from `tabItem` item where {}""".format(
- " and ".join(conditions)
- ),
- filters,
- as_dict=1,
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(item)
+ .select(
+ item.name,
+ item.item_name,
+ item.description,
+ item.brand,
+ item.item_group,
+ item.safety_stock,
+ item.lead_time_days,
+ )
+ .where(item.is_stock_item == 1)
)
+ if brand := filters.get("brand"):
+ query = query.where(item.brand == brand)
-def get_consumed_items(condition):
+ if conditions := get_item_group_condition(filters.get("item_group"), item):
+ query = query.where(conditions)
+
+ return query.run(as_dict=True)
+
+
+def get_consumed_items(filters):
purpose_to_exclude = [
"Material Transfer for Manufacture",
"Material Transfer",
"Send to Subcontractor",
]
- condition += """
- and (
- purpose is NULL
- or purpose not in ({})
+ se = frappe.qb.DocType("Stock Entry")
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .left_join(se)
+ .on(sle.voucher_no == se.name)
+ .select(sle.item_code, Abs(Sum(sle.actual_qty)).as_("consumed_qty"))
+ .where(
+ (sle.actual_qty < 0)
+ & (sle.is_cancelled == 0)
+ & (sle.voucher_type.notin(["Delivery Note", "Sales Invoice"]))
+ & ((se.purpose.isnull()) | (se.purpose.notin(purpose_to_exclude)))
)
- """.format(
- ", ".join(f"'{p}'" for p in purpose_to_exclude)
+ .groupby(sle.item_code)
)
- condition = condition.replace("posting_date", "sle.posting_date")
+ query = get_filtered_query(filters, sle, query)
- consumed_items = frappe.db.sql(
- """
- select item_code, abs(sum(actual_qty)) as consumed_qty
- from `tabStock Ledger Entry` as sle left join `tabStock Entry` as se
- on sle.voucher_no = se.name
- where
- actual_qty < 0
- and is_cancelled = 0
- and voucher_type not in ('Delivery Note', 'Sales Invoice')
- %s
- group by item_code"""
- % condition,
- as_dict=1,
- )
+ consumed_items = query.run(as_dict=True)
consumed_items_map = {item.item_code: item.consumed_qty for item in consumed_items}
return consumed_items_map
-def get_delivered_items(condition):
- dn_items = frappe.db.sql(
- """select dn_item.item_code, sum(dn_item.stock_qty) as dn_qty
- from `tabDelivery Note` dn, `tabDelivery Note Item` dn_item
- where dn.name = dn_item.parent and dn.docstatus = 1 %s
- group by dn_item.item_code"""
- % (condition),
- as_dict=1,
+def get_delivered_items(filters):
+ parent = frappe.qb.DocType("Delivery Note")
+ child = frappe.qb.DocType("Delivery Note Item")
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(child.item_code, Sum(child.stock_qty).as_("dn_qty"))
+ .where((parent.name == child.parent) & (parent.docstatus == 1))
+ .groupby(child.item_code)
)
+ query = get_filtered_query(filters, parent, query)
- si_items = frappe.db.sql(
- """select si_item.item_code, sum(si_item.stock_qty) as si_qty
- from `tabSales Invoice` si, `tabSales Invoice Item` si_item
- where si.name = si_item.parent and si.docstatus = 1 and
- si.update_stock = 1 %s
- group by si_item.item_code"""
- % (condition),
- as_dict=1,
+ dn_items = query.run(as_dict=True)
+
+ parent = frappe.qb.DocType("Sales Invoice")
+ child = frappe.qb.DocType("Sales Invoice Item")
+ query = (
+ frappe.qb.from_(parent)
+ .from_(child)
+ .select(child.item_code, Sum(child.stock_qty).as_("si_qty"))
+ .where((parent.name == child.parent) & (parent.docstatus == 1) & (parent.update_stock == 1))
+ .groupby(child.item_code)
)
+ query = get_filtered_query(filters, parent, query)
+
+ si_items = query.run(as_dict=True)
dn_item_map = {}
for item in dn_items:
@@ -152,13 +161,10 @@
return dn_item_map
-def get_condition(filters):
- conditions = ""
+def get_filtered_query(filters, table, query):
if filters.get("from_date") and filters.get("to_date"):
- conditions += " and posting_date between '%s' and '%s'" % (
- filters["from_date"],
- filters["to_date"],
- )
+ query = query.where(table.posting_date.between(filters["from_date"], filters["to_date"]))
else:
- frappe.throw(_("From and To dates required"))
- return conditions
+ frappe.throw(_("From and To dates are required"))
+
+ return query
diff --git a/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py b/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
index 854875a..9e75201 100644
--- a/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
+++ b/erpnext/stock/report/product_bundle_balance/product_bundle_balance.py
@@ -4,7 +4,9 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import IfNull
from frappe.utils import flt
+from pypika.terms import ExistsCriterion
from erpnext.stock.report.stock_ledger.stock_ledger import get_item_group_condition
@@ -123,43 +125,65 @@
pb_details = frappe._dict()
item_details = frappe._dict()
- conditions = get_parent_item_conditions(filters)
- parent_item_details = frappe.db.sql(
- """
- select item.name as item_code, item.item_name, pb.description, item.item_group, item.brand, item.stock_uom
- from `tabItem` item
- inner join `tabProduct Bundle` pb on pb.new_item_code = item.name
- where ifnull(item.disabled, 0) = 0 {0}
- """.format(
- conditions
- ),
- filters,
- as_dict=1,
- ) # nosec
+ item = frappe.qb.DocType("Item")
+ pb = frappe.qb.DocType("Product Bundle")
+
+ query = (
+ frappe.qb.from_(item)
+ .inner_join(pb)
+ .on(pb.new_item_code == item.name)
+ .select(
+ item.name.as_("item_code"),
+ item.item_name,
+ pb.description,
+ item.item_group,
+ item.brand,
+ item.stock_uom,
+ )
+ .where(IfNull(item.disabled, 0) == 0)
+ )
+
+ if item_code := filters.get("item_code"):
+ query = query.where(item.item_code == item_code)
+ else:
+ if brand := filters.get("brand"):
+ query = query.where(item.brand == brand)
+ if item_group := filters.get("item_group"):
+ if conditions := get_item_group_condition(item_group, item):
+ query = query.where(conditions)
+
+ parent_item_details = query.run(as_dict=True)
parent_items = []
for d in parent_item_details:
parent_items.append(d.item_code)
item_details[d.item_code] = d
+ child_item_details = []
if parent_items:
- child_item_details = frappe.db.sql(
- """
- select
- pb.new_item_code as parent_item, pbi.item_code, item.item_name, pbi.description, item.item_group, item.brand,
- item.stock_uom, pbi.uom, pbi.qty
- from `tabProduct Bundle Item` pbi
- inner join `tabProduct Bundle` pb on pb.name = pbi.parent
- inner join `tabItem` item on item.name = pbi.item_code
- where pb.new_item_code in ({0})
- """.format(
- ", ".join(["%s"] * len(parent_items))
- ),
- parent_items,
- as_dict=1,
- ) # nosec
- else:
- child_item_details = []
+ item = frappe.qb.DocType("Item")
+ pb = frappe.qb.DocType("Product Bundle")
+ pbi = frappe.qb.DocType("Product Bundle Item")
+
+ child_item_details = (
+ frappe.qb.from_(pbi)
+ .inner_join(pb)
+ .on(pb.name == pbi.parent)
+ .inner_join(item)
+ .on(item.name == pbi.item_code)
+ .select(
+ pb.new_item_code.as_("parent_item"),
+ pbi.item_code,
+ item.item_name,
+ pbi.description,
+ item.item_group,
+ item.brand,
+ item.stock_uom,
+ pbi.uom,
+ pbi.qty,
+ )
+ .where(pb.new_item_code.isin(parent_items))
+ ).run(as_dict=1)
child_items = set()
for d in child_item_details:
@@ -184,58 +208,42 @@
if not items:
return []
- item_conditions_sql = " and sle.item_code in ({})".format(
- ", ".join(frappe.db.escape(i) for i in items)
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ sle2 = frappe.qb.DocType("Stock Ledger Entry")
+
+ query = (
+ frappe.qb.from_(sle)
+ .force_index("posting_sort_index")
+ .left_join(sle2)
+ .on(
+ (sle.item_code == sle2.item_code)
+ & (sle.warehouse == sle2.warehouse)
+ & (sle.posting_date < sle2.posting_date)
+ & (sle.posting_time < sle2.posting_time)
+ & (sle.name < sle2.name)
+ )
+ .select(sle.item_code, sle.warehouse, sle.qty_after_transaction, sle.company)
+ .where((sle2.name.isnull()) & (sle.docstatus < 2) & (sle.item_code.isin(items)))
)
- conditions = get_sle_conditions(filters)
-
- return frappe.db.sql(
- """
- select
- sle.item_code, sle.warehouse, sle.qty_after_transaction, sle.company
- from
- `tabStock Ledger Entry` sle force index (posting_sort_index)
- left join `tabStock Ledger Entry` sle2 on
- sle.item_code = sle2.item_code and sle.warehouse = sle2.warehouse
- and (sle.posting_date, sle.posting_time, sle.name) < (sle2.posting_date, sle2.posting_time, sle2.name)
- where sle2.name is null and sle.docstatus < 2 %s %s"""
- % (item_conditions_sql, conditions),
- as_dict=1,
- ) # nosec
-
-
-def get_parent_item_conditions(filters):
- conditions = []
-
- if filters.get("item_code"):
- conditions.append("item.item_code = %(item_code)s")
+ if date := filters.get("date"):
+ query = query.where(sle.posting_date <= date)
else:
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- if filters.get("item_group"):
- conditions.append(get_item_group_condition(filters.get("item_group")))
-
- conditions = " and ".join(conditions)
- return "and {0}".format(conditions) if conditions else ""
-
-
-def get_sle_conditions(filters):
- conditions = ""
- if not filters.get("date"):
frappe.throw(_("'Date' is required"))
- conditions += " and sle.posting_date <= %s" % frappe.db.escape(filters.get("date"))
-
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 sle.warehouse = wh.name)"
- % (warehouse_details.lft, warehouse_details.rgt)
- ) # nosec
- return conditions
+ if warehouse_details:
+ 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))
+ )
+ )
+
+ return query.run(as_dict=True)
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index a951197..af7f20f 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -305,20 +305,25 @@
def get_items(filters):
+ item = frappe.qb.DocType("Item")
+ query = frappe.qb.from_(item).select(item.name)
conditions = []
- if filters.get("item_code"):
- conditions.append("item.name=%(item_code)s")
+
+ if item_code := filters.get("item_code"):
+ conditions.append(item.name == item_code)
else:
- if filters.get("brand"):
- conditions.append("item.brand=%(brand)s")
- if filters.get("item_group"):
- conditions.append(get_item_group_condition(filters.get("item_group")))
+ if brand := filters.get("brand"):
+ conditions.append(item.brand == brand)
+ if item_group := filters.get("item_group"):
+ if condition := get_item_group_condition(item_group, item):
+ conditions.append(condition)
items = []
if conditions:
- items = frappe.db.sql_list(
- """select name from `tabItem` item where {}""".format(" and ".join(conditions)), filters
- )
+ for condition in conditions:
+ query = query.where(condition)
+ items = [r[0] for r in query.run()]
+
return items
@@ -330,29 +335,22 @@
if not items:
return item_details
- cf_field = cf_join = ""
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(item)
+ .select(item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom)
+ .where(item.name.isin(items))
+ )
+
if include_uom:
- cf_field = ", ucd.conversion_factor"
- cf_join = (
- "left join `tabUOM Conversion Detail` ucd on ucd.parent=item.name and ucd.uom=%s"
- % frappe.db.escape(include_uom)
+ ucd = frappe.qb.DocType("UOM Conversion Detail")
+ query = (
+ query.left_join(ucd)
+ .on((ucd.parent == item.name) & (ucd.uom == include_uom))
+ .select(ucd.conversion_factor)
)
- res = frappe.db.sql(
- """
- select
- item.name, item.item_name, item.description, item.item_group, item.brand, item.stock_uom {cf_field}
- from
- `tabItem` item
- {cf_join}
- where
- item.name in ({item_codes})
- """.format(
- cf_field=cf_field, cf_join=cf_join, item_codes=",".join(["%s"] * len(items))
- ),
- items,
- as_dict=1,
- )
+ res = query.run(as_dict=True)
for item in res:
item_details.setdefault(item.name, item)
@@ -427,16 +425,28 @@
return ""
-def get_item_group_condition(item_group):
+def get_item_group_condition(item_group, item_table=None):
item_group_details = frappe.db.get_value("Item Group", item_group, ["lft", "rgt"], as_dict=1)
if item_group_details:
- return (
- "item.item_group in (select ig.name from `tabItem Group` ig \
- where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)"
- % (item_group_details.lft, item_group_details.rgt)
- )
-
- return ""
+ if item_table:
+ ig = frappe.qb.DocType("Item Group")
+ return item_table.item_group.isin(
+ (
+ frappe.qb.from_(ig)
+ .select(ig.name)
+ .where(
+ (ig.lft >= item_group_details.lft)
+ & (ig.rgt <= item_group_details.rgt)
+ & (item_table.item_group == ig.name)
+ )
+ )
+ )
+ else:
+ return (
+ "item.item_group in (select ig.name from `tabItem Group` ig \
+ where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)"
+ % (item_group_details.lft, item_group_details.rgt)
+ )
def check_inventory_dimension_filters_applied(filters) -> bool: