fix: make queries show searchfields (#21685)
* fix: make queries show searchfields
* Update erpnext/controllers/queries.py
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
* Update queries.py
* Update erpnext/controllers/queries.py
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
* Update erpnext/controllers/queries.py
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
* fix: make fields string for sql
Co-authored-by: Nabin Hait <nabinhait@gmail.com>
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 5febfd6..5fbc460 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -8,11 +8,14 @@
from frappe.utils import nowdate, getdate
from collections import defaultdict
from erpnext.stock.get_item_details import _get_item_tax_template
+from frappe.utils import unique
# searches for active employees
def employee_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
- return frappe.db.sql("""select name, employee_name from `tabEmployee`
+ fields = get_fields("Employee", ["name", "employee_name"])
+
+ return frappe.db.sql("""select {fields} from `tabEmployee`
where status = 'Active'
and docstatus < 2
and ({key} like %(txt)s
@@ -24,6 +27,7 @@
idx desc,
name, employee_name
limit %(start)s, %(page_len)s""".format(**{
+ 'fields': ", ".join(fields),
'key': searchfield,
'fcond': get_filters_cond(doctype, filters, conditions),
'mcond': get_match_cond(doctype)
@@ -34,9 +38,12 @@
'page_len': page_len
})
- # searches for leads which are not converted
+
+# searches for leads which are not converted
def lead_query(doctype, txt, searchfield, start, page_len, filters):
- return frappe.db.sql("""select name, lead_name, company_name from `tabLead`
+ fields = get_fields("Lead", ["name", "lead_name", "company_name"])
+
+ return frappe.db.sql("""select {fields} from `tabLead`
where docstatus < 2
and ifnull(status, '') != 'Converted'
and ({key} like %(txt)s
@@ -50,6 +57,7 @@
idx desc,
name, lead_name
limit %(start)s, %(page_len)s""".format(**{
+ 'fields': ", ".join(fields),
'key': searchfield,
'mcond':get_match_cond(doctype)
}), {
@@ -59,6 +67,7 @@
'page_len': page_len
})
+
# searches for customer
def customer_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
@@ -69,13 +78,9 @@
else:
fields = ["name", "customer_name", "customer_group", "territory"]
- meta = frappe.get_meta("Customer")
- searchfields = meta.get_search_fields()
- searchfields = searchfields + [f for f in [searchfield or "name", "customer_name"] \
- if not f in searchfields]
- fields = fields + [f for f in searchfields if not f in fields]
+ fields = get_fields("Customer", fields)
- fields = ", ".join(fields)
+ searchfields = frappe.get_meta("Customer").get_search_fields()
searchfields = " or ".join([field + " like %(txt)s" for field in searchfields])
return frappe.db.sql("""select {fields} from `tabCustomer`
@@ -88,7 +93,7 @@
idx desc,
name, customer_name
limit %(start)s, %(page_len)s""".format(**{
- "fields": fields,
+ "fields": ", ".join(fields),
"scond": searchfields,
"mcond": get_match_cond(doctype),
"fcond": get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
@@ -99,6 +104,7 @@
'page_len': page_len
})
+
# searches for supplier
def supplier_query(doctype, txt, searchfield, start, page_len, filters):
supp_master_name = frappe.defaults.get_user_default("supp_master_name")
@@ -106,7 +112,8 @@
fields = ["name", "supplier_group"]
else:
fields = ["name", "supplier_name", "supplier_group"]
- fields = ", ".join(fields)
+
+ fields = get_fields("Supplier", fields)
return frappe.db.sql("""select {field} from `tabSupplier`
where docstatus < 2
@@ -119,7 +126,7 @@
idx desc,
name, supplier_name
limit %(start)s, %(page_len)s """.format(**{
- 'field': fields,
+ 'field': ', '.join(fields),
'key': searchfield,
'mcond':get_match_cond(doctype)
}), {
@@ -129,6 +136,7 @@
'page_len': page_len
})
+
def tax_account_query(doctype, txt, searchfield, start, page_len, filters):
company_currency = erpnext.get_company_currency(filters.get('company'))
@@ -153,6 +161,7 @@
return tax_accounts
+
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
@@ -221,10 +230,12 @@
"page_len": page_len
}, as_dict=as_dict)
+
def bom(doctype, txt, searchfield, start, page_len, filters):
conditions = []
+ fields = get_fields("BOM", ["name", "item"])
- return frappe.db.sql("""select tabBOM.name, tabBOM.item
+ return frappe.db.sql("""select {fields}
from tabBOM
where tabBOM.docstatus=1
and tabBOM.is_active=1
@@ -234,6 +245,7 @@
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc, name
limit %(start)s, %(page_len)s """.format(
+ fields=", ".join(fields),
fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
mcond=get_match_cond(doctype).replace('%', '%%'),
key=searchfield),
@@ -244,13 +256,16 @@
'page_len': page_len or 20
})
+
def get_project_name(doctype, txt, searchfield, start, page_len, filters):
cond = ''
if filters.get('customer'):
cond = """(`tabProject`.customer = %s or
ifnull(`tabProject`.customer,"")="") and""" %(frappe.db.escape(filters.get("customer")))
- return frappe.db.sql("""select `tabProject`.name from `tabProject`
+ fields = get_fields("Project", ["name"])
+
+ return frappe.db.sql("""select {fields} from `tabProject`
where `tabProject`.status not in ("Completed", "Cancelled")
and {cond} `tabProject`.name like %(txt)s {match_cond}
order by
@@ -258,6 +273,7 @@
idx desc,
`tabProject`.name asc
limit {start}, {page_len}""".format(
+ fields=", ".join(['`tabProject`.{0}'.format(f) for f in fields]),
cond=cond,
match_cond=get_match_cond(doctype),
start=start,
@@ -268,8 +284,10 @@
def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters, as_dict):
+ fields = get_fields("Delivery Note", ["name", "customer", "posting_date"])
+
return frappe.db.sql("""
- select `tabDelivery Note`.name, `tabDelivery Note`.customer, `tabDelivery Note`.posting_date
+ select %(fields)s
from `tabDelivery Note`
where `tabDelivery Note`.`%(key)s` like %(txt)s and
`tabDelivery Note`.docstatus = 1
@@ -284,6 +302,7 @@
)
%(mcond)s order by `tabDelivery Note`.`%(key)s` asc limit %(start)s, %(page_len)s
""" % {
+ "fields": ", ".join(["`tabDelivery Note`.{0}".format(f) for f in fields]),
"key": searchfield,
"fcond": get_filters_cond(doctype, filters, []),
"mcond": get_match_cond(doctype),
@@ -349,6 +368,7 @@
order by expiry_date, name desc
limit %(start)s, %(page_len)s""".format(cond, match_conditions=get_match_cond(doctype)), args)
+
def get_account_list(doctype, txt, searchfield, start, page_len, filters):
filter_list = []
@@ -371,6 +391,7 @@
fields = ["name", "parent_account"],
limit_start=start, limit_page_length=page_len, as_list=True)
+
def get_blanket_orders(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select distinct bo.name, bo.blanket_order_type, bo.to_date
from `tabBlanket Order` bo, `tabBlanket Order Item` boi
@@ -385,6 +406,7 @@
company = frappe.db.escape(filters.get("company"))
))
+
@frappe.whitelist()
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
@@ -490,6 +512,7 @@
return frappe.db.sql(query, filters)
+
@frappe.whitelist()
def item_manufacturer_query(doctype, txt, searchfield, start, page_len, filters):
item_filters = [
@@ -507,6 +530,7 @@
)
return item_manufacturers
+
@frappe.whitelist()
def get_purchase_receipts(doctype, txt, searchfield, start, page_len, filters):
query = """
@@ -520,6 +544,7 @@
return frappe.db.sql(query, filters)
+
@frappe.whitelist()
def get_purchase_invoices(doctype, txt, searchfield, start, page_len, filters):
query = """
@@ -533,6 +558,7 @@
return frappe.db.sql(query, filters)
+
@frappe.whitelist()
def get_tax_template(doctype, txt, searchfield, start, page_len, filters):
@@ -556,3 +582,13 @@
taxes = _get_item_tax_template(args, taxes, for_validate=True)
return [(d,) for d in set(taxes)]
+
+
+def get_fields(doctype, fields=[]):
+ meta = frappe.get_meta(doctype)
+ fields.extend(meta.get_search_fields())
+
+ if meta.title_field and not meta.title_field.strip() in fields:
+ fields.insert(1, meta.title_field.strip())
+
+ return unique(fields)
diff --git a/erpnext/selling/doctype/customer/customer.py b/erpnext/selling/doctype/customer/customer.py
index 3d172ac..a6889e0 100644
--- a/erpnext/selling/doctype/customer/customer.py
+++ b/erpnext/selling/doctype/customer/customer.py
@@ -337,11 +337,15 @@
return lp_details
def get_customer_list(doctype, txt, searchfield, start, page_len, filters=None):
+ from erpnext.controllers.queries import get_fields
+
if frappe.db.get_default("cust_master_name") == "Customer Name":
fields = ["name", "customer_group", "territory"]
else:
fields = ["name", "customer_name", "customer_group", "territory"]
+ fields = get_fields("Customer", fields)
+
match_conditions = build_match_conditions("Customer")
match_conditions = "and {}".format(match_conditions) if match_conditions else ""
@@ -349,14 +353,17 @@
filter_conditions = get_filters_cond(doctype, filters, [])
match_conditions += "{}".format(filter_conditions)
- return frappe.db.sql("""select %s from `tabCustomer` where docstatus < 2
- and (%s like %s or customer_name like %s)
- {match_conditions}
+ return frappe.db.sql("""
+ select %s
+ from `tabCustomer`
+ where docstatus < 2
+ and (%s like %s or customer_name like %s)
+ {match_conditions}
order by
- case when name like %s then 0 else 1 end,
- case when customer_name like %s then 0 else 1 end,
- name, customer_name limit %s, %s""".format(match_conditions=match_conditions) %
- (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"),
+ case when name like %s then 0 else 1 end,
+ case when customer_name like %s then 0 else 1 end,
+ name, customer_name limit %s, %s
+ """.format(match_conditions=match_conditions) % (", ".join(fields), searchfield, "%s", "%s", "%s", "%s", "%s", "%s"),
("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))