refactor: rewrite `Stock Ledger Report` queries in `QB`
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index e18d4c7..b836e9c 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -318,20 +318,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
@@ -343,29 +348,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)
@@ -440,16 +438,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: