chore: rewrite query using query builder
diff --git a/erpnext/crm/report/lead_details/lead_details.py b/erpnext/crm/report/lead_details/lead_details.py
index 7b8c43b..98dfbec 100644
--- a/erpnext/crm/report/lead_details/lead_details.py
+++ b/erpnext/crm/report/lead_details/lead_details.py
@@ -4,6 +4,7 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import Concat_ws, Date
def execute(filters=None):
@@ -69,53 +70,41 @@
def get_data(filters):
- return frappe.db.sql(
- """
- SELECT
- `tabLead`.name,
- `tabLead`.lead_name,
- `tabLead`.status,
- `tabLead`.lead_owner,
- `tabLead`.territory,
- `tabLead`.source,
- `tabLead`.email_id,
- `tabLead`.mobile_no,
- `tabLead`.phone,
- `tabLead`.owner,
- `tabLead`.company,
- concat_ws(', ',
- trim(',' from `tabAddress`.address_line1),
- trim(',' from tabAddress.address_line2)
- ) AS address,
- `tabAddress`.state,
- `tabAddress`.pincode,
- `tabAddress`.country
- FROM
- `tabLead` left join `tabDynamic Link` on (
- `tabLead`.name = `tabDynamic Link`.link_name and
- `tabDynamic Link`.parenttype = 'Address')
- left join `tabAddress` on (
- `tabAddress`.name=`tabDynamic Link`.parent)
- WHERE
- company = %(company)s
- AND DATE(`tabLead`.creation) BETWEEN %(from_date)s AND %(to_date)s
- {conditions}
- ORDER BY
- `tabLead`.creation asc """.format(
- conditions=get_conditions(filters)
- ),
- filters,
- as_dict=1,
+ lead = frappe.qb.DocType("Lead")
+ address = frappe.qb.DocType("Address")
+ dynamic_link = frappe.qb.DocType("Dynamic Link")
+
+ query = (
+ frappe.qb.from_(lead)
+ .left_join(dynamic_link)
+ .on((lead.name == dynamic_link.link_name) & (dynamic_link.parenttype == "Address"))
+ .left_join(address)
+ .on(address.name == dynamic_link.parent)
+ .select(
+ lead.name,
+ lead.lead_name,
+ lead.status,
+ lead.lead_owner,
+ lead.territory,
+ lead.source,
+ lead.email_id,
+ lead.mobile_no,
+ lead.phone,
+ lead.owner,
+ lead.company,
+ (Concat_ws(", ", address.address_line1, address.address_line2)).as_("address"),
+ address.state,
+ address.pincode,
+ address.country,
+ )
+ .where(lead.company == filters.company)
+ .where(Date(lead.creation).between(filters.from_date, filters.to_date))
)
-
-def get_conditions(filters):
- conditions = []
-
if filters.get("territory"):
- conditions.append(" and `tabLead`.territory=%(territory)s")
+ query = query.where(lead.territory == filters.get("territory"))
if filters.get("status"):
- conditions.append(" and `tabLead`.status=%(status)s")
+ query = query.where(lead.status == filters.get("status"))
- return " ".join(conditions) if conditions else ""
+ return query.run(as_dict=1)