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, ignore_permissions=True)) query = """select `tabWarehouse`.name, CONCAT_WS(" : ", "Actual Qty", ifnull( ({sub_query}), 0) ) as actual_qty from `tabWarehouse` where `tabWarehouse`.`{key}` like '{txt}' {fcond} {mcond} order by `tabWarehouse`.name desc limit {start}, {page_len} """.format(sub_query=sub_query, key=dataent.db.escape(searchfield), fcond=get_filters_cond(doctype, filter_dict.get("Warehouse"), conditions), mcond=get_match_cond(doctype), start=start, page_len=page_len, txt=dataent.db.escape('%{0}%'.format(txt))) return dataent.db.sql(query)
def get_event_conditions(doctype, filters=None): """Returns SQL conditions with user permissions and filters for event queries""" from dataent.desk.reportview import get_filters_cond if not dataent.has_permission(doctype): dataent.throw(_("Not Permitted"), dataent.PermissionError) return get_filters_cond(doctype, filters, [], with_match_conditions=True)
def get_events(start, end, filters=None): events = [] employee = dataent.db.get_value("Employee", {"user_id": dataent.session.user}, ["name", "company"], as_dict=True) if employee: employee, company = employee.name, employee.company else: employee = '' company = dataent.db.get_value("Global Defaults", None, "default_company") from dataent.desk.reportview import get_filters_cond conditions = get_filters_cond("Leave Application", filters, []) # show department leaves for employee if "Employee" in dataent.get_roles(): add_department_leaves(events, start, end, employee, company) add_leaves(events, start, end, conditions) add_block_dates(events, start, end, employee, company) add_holidays(events, start, end, employee, company) return events
def employee_query(doctype, txt, searchfield, start, page_len, filters): conditions = [] return dataent.db.sql( """select name, employee_name from `tabEmployee` where status = 'Active' and docstatus < 2 and ({key} like %(txt)s or employee_name like %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, employee_name), locate(%(_txt)s, employee_name), 99999), idx desc, name, employee_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_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters, as_dict): return dataent.db.sql(""" select `tabDelivery Note`.name, `tabDelivery Note`.customer, `tabDelivery Note`.posting_date from `tabDelivery Note` where `tabDelivery Note`.`%(key)s` like %(txt)s and `tabDelivery Note`.docstatus = 1 and status not in ("Stopped", "Closed") %(fcond)s and ( (`tabDelivery Note`.is_return = 0 and `tabDelivery Note`.per_billed < 100) or `tabDelivery Note`.grand_total = 0 or ( `tabDelivery Note`.is_return = 1 and return_against in (select name from `tabDelivery Note` where per_billed < 100) ) ) %(mcond)s order by `tabDelivery Note`.`%(key)s` asc limit %(start)s, %(page_len)s """ % { "key": searchfield, "fcond": get_filters_cond(doctype, filters, []), "mcond": get_match_cond(doctype), "start": start, "page_len": page_len, "txt": "%(txt)s" }, {"txt": ("%%%s%%" % txt)}, as_dict=as_dict)
def get_customer_list(doctype, txt, searchfield, start, page_len, filters=None): if dataent.db.get_default("cust_master_name") == "Customer Name": fields = ["name", "customer_group", "territory"] else: fields = ["name", "customer_name", "customer_group", "territory"] 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 dataent.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 test_ignore_permissions_for_get_filters_cond(self): dataent.set_user('*****@*****.**') self.assertRaises(dataent.PermissionError, get_filters_cond, 'DocType', dict(istable=1), []) self.assertTrue( get_filters_cond('DocType', dict(istable=1), [], ignore_permissions=True)) dataent.set_user('Administrator')
def get_events(start, end, filters=None): events = [] employee = dataent.db.get_value("Employee", {"user_id": dataent.session.user}) if not employee: return events from dataent.desk.reportview import get_filters_cond conditions = get_filters_cond("Attendance", filters, []) add_attendance(events, start, end, conditions=conditions) return events
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False): conditions = [] description_cond = '' if dataent.db.count('Item', cache=True) < 50000: # scan description only if items are less than 50000 description_cond = 'or tabItem.description LIKE %(txt)s' return dataent.db.sql("""select tabItem.name, if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, tabItem.item_group, if(length(tabItem.description) > 40, \ concat(substr(tabItem.description, 1, 40), "..."), description) as decription from tabItem where 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_code LIKE %(txt)s or tabItem.item_group LIKE %(txt)s or tabItem.item_name LIKE %(txt)s or tabItem.item_code IN (select parent from `tabItem Barcode` where barcode LIKE %(txt)s) {description_cond}) {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), idx desc, name, 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('%', '%%'), description_cond=description_cond), { "today": nowdate(), "txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len }, as_dict=as_dict)
def customer_query(doctype, txt, searchfield, start, page_len, filters): conditions = [] cust_master_name = dataent.defaults.get_user_default("cust_master_name") if cust_master_name == "Customer Name": fields = ["name", "customer_group", "territory"] else: fields = ["name", "customer_name", "customer_group", "territory"] meta = dataent.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 = ", ".join(fields) searchfields = " or ".join( [field + " like %(txt)s" for field in searchfields]) return dataent.db.sql( """select {fields} from `tabCustomer` where docstatus < 2 and ({scond}) and disabled=0 {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, customer_name), locate(%(_txt)s, customer_name), 99999), idx desc, name, customer_name limit %(start)s, %(page_len)s""".format( **{ "fields": 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_events(start, end, filters=None): events = [] employee = dataent.db.get_value("Employee", {"user_id": dataent.session.user}, ["name", "company"], as_dict=True) if employee: employee, company = employee.name, employee.company else: employee = '' company = dataent.db.get_value("Global Defaults", None, "default_company") from dataent.desk.reportview import get_filters_cond conditions = get_filters_cond("Shift Assignment", filters, []) add_assignments(events, start, end, conditions=conditions) return events
def bom(doctype, txt, searchfield, start, page_len, filters): conditions = [] return dataent.db.sql( """select tabBOM.name, tabBOM.item from tabBOM where tabBOM.docstatus=1 and tabBOM.is_active=1 and tabBOM.`{key}` like %(txt)s {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), idx desc, name limit %(start)s, %(page_len)s """.format(fcond=get_filters_cond( doctype, filters, conditions), mcond=get_match_cond(doctype), key=dataent.db.escape(searchfield)), { 'txt': "%%%s%%" % dataent.db.escape(txt), '_txt': txt.replace("%", ""), 'start': start or 0, 'page_len': page_len or 20 })