Merge pull request #32849 from frappe/mergify/bp/develop/pr-32831
fix: mysql syntax issue (backport #32831)
diff --git a/erpnext/stock/doctype/pick_list/pick_list.py b/erpnext/stock/doctype/pick_list/pick_list.py
index 9c1c7e5..5de7fed 100644
--- a/erpnext/stock/doctype/pick_list/pick_list.py
+++ b/erpnext/stock/doctype/pick_list/pick_list.py
@@ -10,6 +10,8 @@
from frappe import _
from frappe.model.document import Document
from frappe.model.mapper import map_child_doc
+from frappe.query_builder import Case
+from frappe.query_builder.functions import Locate
from frappe.utils import cint, floor, flt, today
from frappe.utils.nestedset import get_descendants_of
@@ -686,31 +688,22 @@
@frappe.whitelist()
def get_pending_work_orders(doctype, txt, searchfield, start, page_length, filters, as_dict):
- return frappe.db.sql(
- """
- SELECT
- `name`, `company`, `planned_start_date`
- FROM
- `tabWork Order`
- WHERE
- `status` not in ('Completed', 'Stopped')
- AND `qty` > `material_transferred_for_manufacturing`
- AND `docstatus` = 1
- AND `company` = %(company)s
- AND `name` like %(txt)s
- ORDER BY
- (case when locate(%(_txt)s, name) > 0 then locate(%(_txt)s, name) else 99999 end) name
- LIMIT
- %(start)s, %(page_length)s""",
- {
- "txt": "%%%s%%" % txt,
- "_txt": txt.replace("%", ""),
- "start": start,
- "page_length": frappe.utils.cint(page_length),
- "company": filters.get("company"),
- },
- as_dict=as_dict,
- )
+ wo = frappe.qb.DocType("Work Order")
+ return (
+ frappe.qb.from_(wo)
+ .select(wo.name, wo.company, wo.planned_start_date)
+ .where(
+ (wo.status.notin(["Completed", "Stopped"]))
+ & (wo.qty > wo.material_transferred_for_manufacturing)
+ & (wo.docstatus == 1)
+ & (wo.company == filters.get("company"))
+ & (wo.name.like("%{0}%".format(txt)))
+ )
+ .orderby(Case().when(Locate(txt, wo.name) > 0, Locate(txt, wo.name)).else_(99999))
+ .orderby(wo.name)
+ .limit(cint(page_length))
+ .offset(start)
+ ).run(as_dict=as_dict)
@frappe.whitelist()