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 ""
if filters:
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}
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"),
("%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, "%%%s%%" % txt, start, page_len))
def address_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
fields = ['name', 'address_line1', 'city', 'country']
fields = get_fields("Address", fields)
searchfields = frappe.get_meta("Address").get_search_fields()
searchfields = " or ".join(
[field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
"""select {fields} from `tabAddress`
where docstatus < 2
and ({scond}) and disabled=0
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
idx desc,
address_title
limit %(start)s, %(page_len)s""".format(
**{
"fields":
", ".join(fields),
"scond":
searchfields,
"mcond":
get_match_cond(doctype),
"fcond":
get_filters_cond(doctype, filters, conditions).replace(
'%', '%%'),
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_customer_primary_contact(doctype, txt, searchfield, start, page_len,
filters):
from erpnext.controllers.queries import get_fields
return frappe.get_all(
"Contact",
filters=[
["Dynamic Link", "link_doctype", "=", "Customer"],
["Dynamic Link", "link_name", "=",
filters.get('customer')],
["Contact", "name", "like", "%" + txt + "%"],
],
fields=get_fields("Contact", ["name"]),
as_list=True)
def get_alternative_items(doctype, txt, searchfield, start, page_len, filters):
fields = get_fields(
"Item Alternative",
["alternative_item_code", "item_code", "alternative_item_name"])
return frappe.db.sql(
"""
select
{fields}
from
`tabItem Alternative`
where
item_code = %(item_code)s and alternative_item_code like %(txt)s
limit %(start)s, %(page_len)s""".format(**{
'fields': ", ".join(fields),
'key': searchfield
}), {
'txt': "%%%s%%" % txt,
'item_code': filters.get('item_code'),
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
