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 })