def warehouse_query(doctype, txt, searchfield, start, page_len, filters): # Should be used when item code is passed in filters. conditions, bin_conditions = [], [] filter_dict = get_doctype_wise_filters(filters) sub_query = """ select round(`tabBin`.actual_qty, 2) from `tabBin` where `tabBin`.warehouse = `tabWarehouse`.name {bin_conditions} """.format(bin_conditions=get_filters_cond( doctype, filter_dict.get("Bin"), bin_conditions)) response = frappe.db.sql( """select `tabWarehouse`.name, CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty from `tabWarehouse` where `tabWarehouse`.`{key}` like %(txt)s {fcond} {mcond} order by `tabWarehouse`.name desc limit %(start)s, %(page_len)s """.format(sub_query=sub_query, key=frappe.db.escape(searchfield), fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions), mcond=get_match_cond(doctype)), { "txt": "%%%s%%" % frappe.db.escape(txt), "start": start, "page_len": page_len }) return response
def get_users_for_project(doctype, txt, searchfield, start, page_len, filters): conditions = [] return frappe.db.sql( """select name, concat_ws(' ', first_name, middle_name, last_name) from `tabUser` where enabled=1 and name not in ("Guest", "Administrator") and ({key} like %(txt)s or full_name like %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, full_name), locate(%(_txt)s, full_name), 99999), idx desc, name, full_name limit %(start)s, %(page_len)s""".format( **{ 'key': searchfield, 'fcond': get_filters_cond(doctype, filters, conditions), 'mcond': get_match_cond(doctype) }), { 'txt': "%%%s%%" % txt, '_txt': txt.replace("%", ""), 'start': start, 'page_len': page_len })
def se_get_allowed_warehouses(doctype, txt, searchfield, start, page_len, filters): conditions = [] wh_map_names = frappe.get_all("SPN User Warehouse Map", {"name": frappe.session.user}) warehouse_clause = "" if len(wh_map_names) > 0: wh_map = frappe.get_doc("SPN User Warehouse Map", wh_map_names[0]) if wh_map and len(wh_map.warehouses) > 0: warehouse_clause = "and name in (" + ",".join( [("'" + wh.warehouse + "'") for wh in wh_map.warehouses]) + ")" return frappe.db.sql( """select name, warehouse_name from `tabWarehouse` where ({key} like %(txt)s or name like %(txt)s) {fcond} {mcond} {whcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), idx desc, name limit %(start)s, %(page_len)s""".format( **{ 'key': searchfield, 'fcond': get_filters_cond(doctype, filters, conditions), 'mcond': get_match_cond(doctype), 'whcond': warehouse_clause }), { 'txt': "%%%s%%" % txt, '_txt': txt.replace("%", ""), 'start': start, 'page_len': page_len })
def item_query(doctype, txt, searchfield, start, page_len, filters): conditions = [] return frappe.db.sql("""select tabItem.name, if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, if(length(tabItem.description) > 40, \ concat(substr(tabItem.description, 1, 40), "..."), description) as decription, CASE tabItem.item_group when "Plumbing" then concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>TH:</b> ", ifnull(tabItem.thickness,0)," <b>SI:</b> ", ifnull(tabItem.size,0)) when "Pump" then concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>O-IN:</b> ", ifnull(tabItem.outlet_in_inches,0)," <b>O-MM:</b> ", ifnull(tabItem.outlet_in_millimeter,0)," <b>HP:</b> ", ifnull(tabItem.hp,0)," <b>PH:</b> ", ifnull(tabItem.phase,0)," <b>ST:</b> ", ifnull(tabItem.stage,0)) when "Electrical" or "Unit Component" or "Auto Pump System" then concat("<b>RR:</b> ",ifnull(tabItem.relay_range, 0), " <b>SI:</b>",ifnull(tabItem.size,0)) else concat(" ") END as phase from tabItem where tabItem.docstatus < 2 and ifnull(tabItem.has_variants, 0)=0 and tabItem.disabled=0 and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00') and (tabItem.`{key}` LIKE %(txt)s or tabItem.item_name LIKE %(txt)s or tabItem.description LIKE %(txt)s or tabItem.variant_of_item LIKE %(txt)s or tabItem.diameter_in_inches LIKE %(txt)s or tabItem.outlet_in_inches LIKE %(txt)s or tabItem.stage LIKE %(txt)s or tabItem.thickness LIKE %(txt)s or tabItem.size LIKE %(txt)s or tabItem.diameter_in_millimeter LIKE %(txt)s or tabItem.outlet_in_millimeter LIKE %(txt)s or tabItem.phase LIKE %(txt)s or tabItem.hp LIKE %(txt)s or tabItem.relay_range LIKE %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999), name, item_name limit %(start)s, %(page_len)s """.format(key=searchfield, fcond=get_filters_cond(doctype, filters, conditions), mcond=get_match_cond(doctype)), { "today": nowdate(), "txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len })
def kp_sinv_item_query(doctype, txt, searchfield, start, page_len, filters=None): kp_filters = filters filters = {} conditions = [] x = None try: x = frappe.db.sql("""select tabItem.name,tabItem.item_group, if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, if(length(tabItem.description) > 40, \ concat(substr(tabItem.description, 1, 40), "..."), description) as decription from tabItem INNER JOIN `tabItem Customer Detail` AS B ON tabItem.name = B.parent where tabItem.excise_chapter = '{exc}' AND B.customer_name = '{cn}' and tabItem.docstatus < 2 and tabItem.has_variants=0 and tabItem.disabled=0 and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00') and (tabItem.`{key}` LIKE %(txt)s or tabItem.item_group LIKE %(txt)s or tabItem.item_name LIKE %(txt)s or tabItem.description LIKE %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, tabItem.name), locate(%(_txt)s, tabItem.name), 99999), if(locate(%(_txt)s, tabItem.item_name), locate(%(_txt)s, tabItem.item_name), 99999), tabItem.idx desc, tabItem.name, tabItem.item_name limit %(start)s, %(page_len)s """.format(key=searchfield, fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'), mcond=get_match_cond(doctype).replace('%', '%%'), exc=kp_filters.get("excise_chapter"), cn=kp_filters.get("cust_name")), { "today": nowdate(), "txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len }) except Exception, e: frappe.msgprint(e)
def get_students(): conditions = [] return frappe.db.sql( """select first_name,middle_name) from `tabStudent` where enabled=1 {fcond} {mcond} order by name, full_name limit %(start)s, %(page_len)s""".format( **{ 'key': searchfield, 'fcond': get_filters_cond(doctype, filters, conditions), 'mcond': get_match_cond(doctype) }), { 'txt': "%%%s%%" % txt, '_txt': txt.replace("%", ""), 'start': start, 'page_len': page_len })
def get_users_for_project(doctype, txt, searchfield, start, page_len, filters): conditions = [] return frappe.db.sql("""select name, concat_ws(' ', first_name, middle_name, last_name) from `tabUser` where enabled=1 and name not in ("Guest", "Administrator") and ({key} like %(txt)s or full_name like %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, full_name), locate(%(_txt)s, full_name), 99999), idx desc, name, full_name limit %(start)s, %(page_len)s""".format(**{ 'key': searchfield, 'fcond': get_filters_cond(doctype, filters, conditions), 'mcond': get_match_cond(doctype) }), { 'txt': "%%%s%%" % txt, '_txt': txt.replace("%", ""), 'start': start, 'page_len': page_len })