refactor: rewrite `Warehouse wise Item Balance Age and Value Report` queries in `QB`
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
index a54373f..eedf1a0 100644
--- a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Count
from frappe.utils import flt
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
@@ -98,7 +99,7 @@
def validate_filters(filters):
if not (filters.get("item_code") or filters.get("warehouse")):
- sle_count = flt(frappe.db.sql("""select count(name) from `tabStock Ledger Entry`""")[0][0])
+ sle_count = flt(frappe.qb.from_("Stock Ledger Entry").select(Count("name")).run()[0][0])
if sle_count > 500000:
frappe.throw(_("Please set filter based on Item or Warehouse"))
if not filters.get("company"):
@@ -108,25 +109,16 @@
def get_warehouse_list(filters):
from frappe.core.doctype.user_permission.user_permission import get_permitted_documents
- condition = ""
- user_permitted_warehouse = get_permitted_documents("Warehouse")
- value = ()
- if user_permitted_warehouse:
- condition = "and name in %s"
- value = set(user_permitted_warehouse)
- elif not user_permitted_warehouse and filters.get("warehouse"):
- condition = "and name = %s"
- value = filters.get("warehouse")
+ wh = frappe.qb.DocType("Warehouse")
+ query = frappe.qb.from_(wh).select(wh.name).where(wh.is_group == 0)
- return frappe.db.sql(
- """select name
- from `tabWarehouse` where is_group = 0
- {condition}""".format(
- condition=condition
- ),
- value,
- as_dict=1,
- )
+ user_permitted_warehouse = get_permitted_documents("Warehouse")
+ if user_permitted_warehouse:
+ query = query.where(wh.name.isin(set(user_permitted_warehouse)))
+ elif filters.get("warehouse"):
+ query = query.where(wh.name == filters.get("warehouse"))
+
+ return query.run(as_dict=True)
def add_warehouse_column(columns, warehouse_list):