refactor: if() to CASE WHEN (#31360)
* refactor: if() to CASE WHEN
* fix: remove duplicate order by
* fix: remove extraneous table
* style: reformat to black spec
Co-authored-by: Ankush Menat <ankush@frappe.io>
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 5ba314e..243ebb6 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -29,8 +29,8 @@
or employee_name like %(txt)s)
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, employee_name) > 0 then locate(%(_txt)s, employee_name) else 99999 end),
idx desc,
name, employee_name
limit %(page_len)s offset %(start)s""".format(
@@ -60,9 +60,9 @@
or company_name like %(txt)s)
{mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, lead_name), locate(%(_txt)s, lead_name), 99999),
- if(locate(%(_txt)s, company_name), locate(%(_txt)s, company_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, lead_name) > 0 then locate(%(_txt)s, lead_name) else 99999 end),
+ (case when locate(%(_txt)s, company_name) > 0 then locate(%(_txt)s, company_name) else 99999 end),
idx desc,
name, lead_name
limit %(page_len)s offset %(start)s""".format(
@@ -96,8 +96,8 @@
and ({scond}) and disabled=0
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, customer_name) > 0 then locate(%(_txt)s, customer_name) else 99999 end),
idx desc,
name, customer_name
limit %(page_len)s offset %(start)s""".format(
@@ -130,11 +130,11 @@
where docstatus < 2
and ({key} like %(txt)s
or supplier_name like %(txt)s) and disabled=0
- and (on_hold = 0 or (on_hold = 1 and CURDATE() > release_date))
+ and (on_hold = 0 or (on_hold = 1 and CURRENT_DATE > release_date))
{mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- if(locate(%(_txt)s, supplier_name), locate(%(_txt)s, supplier_name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
+ (case when locate(%(_txt)s, supplier_name) > 0 then locate(%(_txt)s, supplier_name) else 99999 end),
idx desc,
name, supplier_name
limit %(page_len)s offset %(start)s""".format(
@@ -305,15 +305,15 @@
return frappe.db.sql(
"""select {fields}
- from tabBOM
- where tabBOM.docstatus=1
- and tabBOM.is_active=1
- and tabBOM.`{key}` like %(txt)s
+ from `tabBOM`
+ where `tabBOM`.docstatus=1
+ and `tabBOM`.is_active=1
+ and `tabBOM`.`{key}` like %(txt)s
{fcond} {mcond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
+ (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end),
idx desc, name
- limit %(start)s, %(page_len)s """.format(
+ limit %(page_len)s offset %(start)s""".format(
fields=", ".join(fields),
fcond=get_filters_cond(doctype, filters, conditions).replace("%", "%%"),
mcond=get_match_cond(doctype).replace("%", "%%"),
@@ -348,8 +348,8 @@
`tabProject`.status not in ('Completed', 'Cancelled')
and {cond} {scond} {match_cond}
order by
- if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
- idx desc,
+ (case when locate(%(_txt)s, `tabProject`.name) > 0 then locate(%(_txt)s, `tabProject`.name) else 99999 end),
+ `tabProject`.idx desc,
`tabProject`.name asc
limit {page_len} offset {start}""".format(
fields=", ".join(["`tabProject`.{0}".format(f) for f in fields]),
diff --git a/erpnext/controllers/status_updater.py b/erpnext/controllers/status_updater.py
index 76a25a0..197d2ba 100644
--- a/erpnext/controllers/status_updater.py
+++ b/erpnext/controllers/status_updater.py
@@ -443,7 +443,7 @@
"""update `tab%(target_parent_dt)s`
set %(target_parent_field)s = round(
ifnull((select
- ifnull(sum(if(abs(%(target_ref_field)s) > abs(%(target_field)s), abs(%(target_field)s), abs(%(target_ref_field)s))), 0)
+ ifnull(sum(case when abs(%(target_ref_field)s) > abs(%(target_field)s) then abs(%(target_field)s) else abs(%(target_ref_field)s) end), 0)
/ sum(abs(%(target_ref_field)s)) * 100
from `tab%(target_dt)s` where parent='%(name)s' having sum(abs(%(target_ref_field)s)) > 0), 0), 6)
%(update_modified)s
@@ -455,9 +455,9 @@
if args.get("status_field"):
frappe.db.sql(
"""update `tab%(target_parent_dt)s`
- set %(status_field)s = if(%(target_parent_field)s<0.001,
- 'Not %(keyword)s', if(%(target_parent_field)s>=99.999999,
- 'Fully %(keyword)s', 'Partly %(keyword)s'))
+ set %(status_field)s = (case when %(target_parent_field)s<0.001 then 'Not %(keyword)s'
+ else case when %(target_parent_field)s>=99.999999 then 'Fully %(keyword)s'
+ else 'Partly %(keyword)s' end end)
where name='%(name)s'"""
% args
)