refactor: rewrite `Supplier Quotation Comparison Report` queries in `QB`
diff --git a/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py b/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
index 3013b6d..a728290 100644
--- a/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
+++ b/erpnext/buying/report/supplier_quotation_comparison/supplier_quotation_comparison.py
@@ -16,8 +16,7 @@
return [], []
columns = get_columns(filters)
- conditions = get_conditions(filters)
- supplier_quotation_data = get_data(filters, conditions)
+ supplier_quotation_data = get_data(filters)
data, chart_data = prepare_data(supplier_quotation_data, filters)
message = get_message()
@@ -25,50 +24,51 @@
return columns, data, message, chart_data
-def get_conditions(filters):
- conditions = ""
+def get_data(filters):
+ sq = frappe.qb.DocType("Supplier Quotation")
+ sq_item = frappe.qb.DocType("Supplier Quotation Item")
+
+ query = (
+ frappe.qb.from_(sq_item)
+ .from_(sq)
+ .select(
+ sq_item.parent,
+ sq_item.item_code,
+ sq_item.qty,
+ sq_item.stock_qty,
+ sq_item.amount,
+ sq_item.uom,
+ sq_item.stock_uom,
+ sq_item.request_for_quotation,
+ sq_item.lead_time_days,
+ sq.supplier.as_("supplier_name"),
+ sq.valid_till,
+ )
+ .where(
+ (sq_item.parent == sq.name)
+ & (sq_item.docstatus < 2)
+ & (sq.company == filters.get("company"))
+ & (sq.transaction_date.between(filters.get("from_date"), filters.get("to_date")))
+ )
+ .orderby(sq.transaction_date, sq_item.item_code)
+ )
+
if filters.get("item_code"):
- conditions += " AND sqi.item_code = %(item_code)s"
+ query = query.where(sq_item.item_code == filters.get("item_code"))
if filters.get("supplier_quotation"):
- conditions += " AND sqi.parent in %(supplier_quotation)s"
+ query = query.where(sq_item.parent.isin(filters.get("supplier_quotation")))
if filters.get("request_for_quotation"):
- conditions += " AND sqi.request_for_quotation = %(request_for_quotation)s"
+ query = query.where(sq_item.request_for_quotation == filters.get("request_for_quotation"))
if filters.get("supplier"):
- conditions += " AND sq.supplier in %(supplier)s"
+ query = query.where(sq.supplier.isin(filters.get("supplier")))
if not filters.get("include_expired"):
- conditions += " AND sq.status != 'Expired'"
+ query = query.where(sq.status != "Expired")
- return conditions
-
-
-def get_data(filters, conditions):
- supplier_quotation_data = frappe.db.sql(
- """
- SELECT
- sqi.parent, sqi.item_code,
- sqi.qty, sqi.stock_qty, sqi.amount,
- sqi.uom, sqi.stock_uom,
- sqi.request_for_quotation,
- sqi.lead_time_days, sq.supplier as supplier_name, sq.valid_till
- FROM
- `tabSupplier Quotation Item` sqi,
- `tabSupplier Quotation` sq
- WHERE
- sqi.parent = sq.name
- AND sqi.docstatus < 2
- AND sq.company = %(company)s
- AND sq.transaction_date between %(from_date)s and %(to_date)s
- {0}
- order by sq.transaction_date, sqi.item_code""".format(
- conditions
- ),
- filters,
- as_dict=1,
- )
+ supplier_quotation_data = query.run(as_dict=True)
return supplier_quotation_data