def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ f = get_filter(self.doctype, f) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) if 'ifnull(' in f.fieldname: column_name = f.fieldname else: column_name = '{tname}.{fname}'.format(tname=tname, fname=f.fieldname) can_be_null = True # prepare in condition if f.operator.lower() in ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'): values = f.value or '' # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None: ref_doctype = frappe.get_meta(f.doctype).get_field( f.fieldname).options result = [] lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ('descendants of', 'not descendants of'): result = frappe.get_all(ref_doctype, filters={ 'lft': ['>', lft], 'rgt': ['<', rgt] }, order_by='`lft` ASC') else: # Get ancestor elements of a DocType with a tree structure result = frappe.get_all(ref_doctype, filters={ 'lft': ['<', lft], 'rgt': ['>', rgt] }, order_by='`lft` DESC') fallback = "''" value = [ frappe.db.escape((v.name or '').strip(), percent=False) for v in result ] if len(value): value = "({0})".format(", ".join(value)) else: value = "('')" # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = 'not in' if f.operator.lower() in ( 'not ancestors of', 'not descendants of') else 'in' elif f.operator.lower() in ('in', 'not in'): values = f.value or '' if not isinstance(values, (list, tuple)): values = values.split(",") fallback = "''" value = [ frappe.db.escape((v or '').strip(), percent=False) for v in values ] if len(value): value = "({0})".format(", ".join(value)) else: value = "('')" else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator in ('>', '<') and (f.fieldname in ('creation', 'modified')): value = cstr(f.value) fallback = "NULL" elif f.operator.lower() in ('between') and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0001-01-01 00:00:00'" elif df and df.fieldtype == "Date": value = frappe.db.format_date(f.value) fallback = "'0001-01-01'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = frappe.db.format_datetime(f.value) fallback = "'0001-01-01 00:00:00'" elif df and df.fieldtype == "Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() in ("like", "not like") or ( isinstance(f.value, string_types) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value == None else f.value fallback = "''" if f.operator.lower() in ("like", "not like") and isinstance( value, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") elif f.operator == '=' and df and df.fieldtype in [ 'Link', 'Data' ]: # TODO: Refactor if possible value = f.value or "''" fallback = "''" else: value = flt(f.value) fallback = 0 # escape value if isinstance( value, string_types) and not f.operator.lower() == 'between': value = "{0}".format(frappe.db.escape(value, percent=False)) if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ('=', 'like')) or 'ifnull(' in column_name.lower()): condition = '{column_name} {operator} {value}'.format( column_name=column_name, operator=f.operator, value=value) else: condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ f = get_filter(self.doctype, f) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) if 'ifnull(' in f.fieldname: column_name = f.fieldname else: column_name = '{tname}.`{fname}`'.format(tname=tname, fname=f.fieldname) can_be_null = True # prepare in condition if f.operator.lower() in ('in', 'not in'): values = f.value or '' if not isinstance(values, (list, tuple)): values = values.split(",") fallback = "''" value = (frappe.db.escape((v or '').strip(), percent=False) for v in values) value = '("{0}")'.format('", "'.join(value)) else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator.lower() == 'between' and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype == "Date": value = getdate(f.value).strftime("%Y-%m-%d") fallback = "'0000-00-00'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f") fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype == "Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() in ("like", "not like") or ( isinstance(f.value, string_types) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value == None else f.value fallback = '""' if f.operator.lower() in ("like", "not like") and isinstance( value, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") else: value = flt(f.value) fallback = 0 # put it inside double quotes if isinstance( value, string_types) and not f.operator.lower() == 'between': value = '"{0}"'.format(frappe.db.escape(value, percent=False)) if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ('=', 'like')) or 'ifnull(' in column_name.lower()): condition = '{column_name} {operator} {value}'.format( column_name=column_name, operator=f.operator, value=value) else: condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ f = get_filter(self.doctype, f) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) if 'ifnull(' in f.fieldname: column_name = f.fieldname else: column_name = '{tname}.{fname}'.format(tname=tname, fname=f.fieldname) can_be_null = True # prepare in condition if f.operator.lower() in ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'): values = f.value or '' # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None: ref_doctype = frappe.get_meta(f.doctype).get_field( f.fieldname).options result = [] lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ('descendants of', 'not descendants of'): result = frappe.db.sql_list( """select name from `tab{0}` where lft>%s and rgt<%s order by lft asc""".format(ref_doctype), (lft, rgt)) else: # Get ancestor elements of a DocType with a tree structure result = frappe.db.sql_list( """select name from `tab{0}` where lft<%s and rgt>%s order by lft desc""".format(ref_doctype), (lft, rgt)) fallback = "''" value = (frappe.db.escape((v or '').strip(), percent=False) for v in result) value = '("{0}")'.format('", "'.join(value)) # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = 'not in' if f.operator.lower() in ( 'not ancestors of', 'not descendants of') else 'in' elif f.operator.lower() in ('in', 'not in'): values = f.value or '' if isinstance(values, frappe.string_types): values = values.split(",") fallback = "''" value = (frappe.db.escape((v or '').strip(), percent=False) for v in values) value = '("{0}")'.format('", "'.join(value)) else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator.lower() == 'between' and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0000-00-00 00:00:00'" elif f.operator.lower() == "is": if f.value == 'set': f.operator = '!=' elif f.value == 'not set': f.operator = '=' value = "" fallback = '""' can_be_null = True if 'ifnull' not in column_name: column_name = 'ifnull({}, {})'.format( column_name, fallback) elif df and df.fieldtype == "Date": value = getdate(f.value).strftime("%Y-%m-%d") fallback = "'0000-00-00'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f") fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype == "Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() in ("like", "not like") or ( isinstance(f.value, string_types) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value == None else f.value fallback = '""' if f.operator.lower() in ("like", "not like") and isinstance( value, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") else: value = flt(f.value) fallback = 0 # put it inside double quotes if isinstance( value, string_types) and not f.operator.lower() == 'between': value = '"{0}"'.format(frappe.db.escape(value, percent=False)) if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ('=', 'like')) or 'ifnull(' in column_name.lower()): condition = '{column_name} {operator} {value}'.format( column_name=column_name, operator=f.operator, value=value) else: condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ f = get_filter(self.doctype, f) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) if 'ifnull(' in f.fieldname: column_name = f.fieldname else: column_name = '{tname}.{fname}'.format(tname=tname, fname=f.fieldname) can_be_null = True # prepare in condition if f.operator.lower() in ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'): values = f.value or '' # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None : ref_doctype = frappe.get_meta(f.doctype).get_field(f.fieldname).options result=[] lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ('descendants of', 'not descendants of') : result = frappe.db.sql_list("""select name from `tab{0}` where lft>%s and rgt<%s order by lft asc""".format(ref_doctype), (lft, rgt)) else : # Get ancestor elements of a DocType with a tree structure result = frappe.db.sql_list("""select name from `tab{0}` where lft<%s and rgt>%s order by lft desc""".format(ref_doctype), (lft, rgt)) fallback = "''" value = (frappe.db.escape((v or '').strip(), percent=False) for v in result) value = '("{0}")'.format('", "'.join(value)) # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = 'not in' if f.operator.lower() in ('not ancestors of', 'not descendants of') else 'in' elif f.operator.lower() in ('in', 'not in'): values = f.value or '' if not isinstance(values, (list, tuple)): values = values.split(",") fallback = "''" value = (frappe.db.escape((v or '').strip(), percent=False) for v in values) value = '("{0}")'.format('", "'.join(value)) else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator.lower() == 'between' and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype=="Date": value = getdate(f.value).strftime("%Y-%m-%d") fallback = "'0000-00-00'" elif (df and df.fieldtype=="Datetime") or isinstance(f.value, datetime): value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f") fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype=="Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() == "is": if f.value == 'set': f.operator = '!=' elif f.value == 'not set': f.operator = '=' value = "" fallback = '""' can_be_null = True if 'ifnull' not in column_name: column_name = 'ifnull({}, {})'.format(column_name, fallback) elif f.operator.lower() in ("like", "not like") or (isinstance(f.value, string_types) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value==None else f.value fallback = '""' if f.operator.lower() in ("like", "not like") and isinstance(value, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") else: value = flt(f.value) fallback = 0 # put it inside double quotes if isinstance(value, string_types) and not f.operator.lower() == 'between': value = '"{0}"'.format(frappe.db.escape(value, percent=False)) if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ('=', 'like')) or 'ifnull(' in column_name.lower()): condition = '{column_name} {operator} {value}'.format( column_name=column_name, operator=f.operator, value=value) else: condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ f = get_filter(self.doctype, f) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) column_name = '{tname}.{fname}'.format(tname=tname, fname=f.fieldname) can_be_null = True # prepare in condition if f.operator in ('in', 'not in'): values = f.value if not isinstance(values, (list, tuple)): values = values.split(",") fallback = "''" value = (frappe.db.escape(v.strip(), percent=False) for v in values) value = '("{0}")'.format('", "'.join(value)) else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype=="Check": can_be_null = False if df and df.fieldtype=="Date": value = getdate(f.value).strftime("%Y-%m-%d") fallback = "'0000-00-00'" elif df and df.fieldtype=="Datetime": value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f") fallback = "'0000-00-00 00:00:00'" elif df and df.fieldtype=="Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator in ("like", "not like") or (isinstance(f.value, basestring) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value==None else f.value fallback = '""' if f.operator in ("like", "not like") and isinstance(value, basestring): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") else: value = flt(f.value) fallback = 0 # put it inside double quotes if isinstance(value, basestring): value = '"{0}"'.format(frappe.db.escape(value, percent=False)) if f.fieldname in ("creation", "modified"): column_name = "date_format({tname}.{fname}, '%Y-%m-%d')".format(tname=tname, fname=f.fieldname) if self.ignore_ifnull or not can_be_null: condition = '{column_name} {operator} {value}'.format( column_name=column_name, operator=f.operator, value=value) else: condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ from frappe.boot import get_additional_filters_from_hooks additional_filters_config = get_additional_filters_from_hooks() f = get_filter(self.doctype, f, additional_filters_config) tname = "`tab" + f.doctype + "`" if not tname in self.tables: self.append_table(tname) if "ifnull(" in f.fieldname: column_name = f.fieldname else: column_name = "{tname}.`{fname}`".format(tname=tname, fname=f.fieldname) can_be_null = True if f.operator.lower() in additional_filters_config: f.update( get_additional_filter_field(additional_filters_config, f, f.value)) # prepare in condition if f.operator.lower() in ( "ancestors of", "descendants of", "not ancestors of", "not descendants of", ): values = f.value or "" # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None: ref_doctype = frappe.get_meta(f.doctype).get_field( f.fieldname).options result = [] lft, rgt = "", "" if f.value: lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ("descendants of", "not descendants of"): result = frappe.get_all(ref_doctype, filters={ "lft": [">", lft], "rgt": ["<", rgt] }, order_by="`lft` ASC") else: # Get ancestor elements of a DocType with a tree structure result = frappe.get_all(ref_doctype, filters={ "lft": ["<", lft], "rgt": [">", rgt] }, order_by="`lft` DESC") fallback = "''" value = [ frappe.db.escape((v.name or "").strip(), percent=False) for v in result ] if len(value): value = "({0})".format(", ".join(value)) else: value = "('')" # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = ("not in" if f.operator.lower() in ("not ancestors of", "not descendants of") else "in") elif f.operator.lower() in ("in", "not in"): values = f.value or "" if isinstance(values, frappe.string_types): values = values.split(",") fallback = "''" value = [ frappe.db.escape((v or "").strip(), percent=False) for v in values ] if len(value): value = "({0})".format(", ".join(value)) else: value = "('')" else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator.lower() in ("previous", "next", "timespan"): date_range = get_date_range(f.operator.lower(), f.value) f.operator = "Between" f.value = date_range fallback = "'0001-01-01 00:00:00'" if f.operator in (">", "<") and (f.fieldname in ("creation", "modified")): value = cstr(f.value) fallback = "NULL" elif f.operator.lower() in ("between") and ( f.fieldname in ("creation", "modified") or (df and (df.fieldtype == "Date" or df.fieldtype == "Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0001-01-01 00:00:00'" elif f.operator.lower() == "is": if f.value == "set": f.operator = "!=" elif f.value == "not set": f.operator = "=" value = "" fallback = "''" can_be_null = True if "ifnull" not in column_name: column_name = "ifnull({}, {})".format( column_name, fallback) elif df and df.fieldtype == "Date": value = frappe.db.format_date(f.value) fallback = "'0001-01-01'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = frappe.db.format_datetime(f.value) fallback = "'0001-01-01 00:00:00'" elif df and df.fieldtype == "Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() in ("like", "not like") or ( isinstance(f.value, string_types) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value == None else f.value fallback = "''" if f.operator.lower() in ("like", "not like") and isinstance( value, string_types): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") elif (f.operator == "=" and df and df.fieldtype in ["Link", "Data"]): # TODO: Refactor if possible value = f.value or "''" fallback = "''" elif f.fieldname == "name": value = f.value or "''" fallback = "''" else: value = flt(f.value) fallback = 0 # escape value if isinstance( value, string_types) and not f.operator.lower() == "between": value = "{0}".format(frappe.db.escape(value, percent=False)) if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ("=", "like")) or "ifnull(" in column_name.lower()): if f.operator.lower() == "like" and frappe.conf.get( "db_type") == "postgres": f.operator = "ilike" condition = "{column_name} {operator} {value}".format( column_name=column_name, operator=f.operator, value=value) else: condition = "ifnull({column_name}, {fallback}) {operator} {value}".format( column_name=column_name, fallback=fallback, operator=f.operator, value=value) return condition
def prepare_filter_condition(self, f): """Returns a filter condition in the format: ifnull(`tabDocType`.`fieldname`, fallback) operator "value" """ from frappe.boot import get_additional_filters_from_hooks additional_filters_config = get_additional_filters_from_hooks() f = get_filter(self.doctype, f, additional_filters_config) tname = ('`tab' + f.doctype + '`') if not tname in self.tables: self.append_table(tname) if 'ifnull(' in f.fieldname: column_name = f.fieldname else: column_name = f"{tname}.{f.fieldname}" can_be_null = True if f.operator.lower() in additional_filters_config: f.update( get_additional_filter_field(additional_filters_config, f, f.value)) # prepare in condition if f.operator.lower() in ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'): values = f.value or '' # TODO: handle list and tuple # if not isinstance(values, (list, tuple)): # values = values.split(",") ref_doctype = f.doctype if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None: ref_doctype = frappe.get_meta(f.doctype).get_field( f.fieldname).options result = [] lft, rgt = '', '' if f.value: lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"]) # Get descendants elements of a DocType with a tree structure if f.operator.lower() in ('descendants of', 'not descendants of'): result = frappe.get_all(ref_doctype, filters={ 'lft': ['>', lft], 'rgt': ['<', rgt] }, order_by='`lft` ASC') else: # Get ancestor elements of a DocType with a tree structure result = frappe.get_all(ref_doctype, filters={ 'lft': ['<', lft], 'rgt': ['>', rgt] }, order_by='`lft` DESC') fallback = "''" value = [ frappe.db.escape((v.name or '').strip(), percent=False) for v in result ] if len(value): value = f"({', '.join(value)})" else: value = "('')" # changing operator to IN as the above code fetches all the parent / child values and convert into tuple # which can be directly used with IN operator to query. f.operator = 'not in' if f.operator.lower() in ( 'not ancestors of', 'not descendants of') else 'in' elif f.operator.lower() in ('in', 'not in'): values = f.value or '' if isinstance(values, str): values = values.split(",") fallback = "''" value = [ frappe.db.escape((v or '').strip(), percent=False) for v in values ] if len(value): value = f"({', '.join(value)})" else: value = "('')" else: df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname}) df = df[0] if df else None if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"): can_be_null = False if f.operator.lower() in ('previous', 'next', 'timespan'): date_range = get_date_range(f.operator.lower(), f.value) f.operator = "Between" f.value = date_range fallback = "'0001-01-01 00:00:00'" if (f.fieldname in ('creation', 'modified')): value = cstr(f.value) fallback = "NULL" elif f.operator.lower() in ('between') and \ (f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))): value = get_between_date_filter(f.value, df) fallback = "'0001-01-01 00:00:00'" elif f.operator.lower() == "is": if f.value == 'set': f.operator = '!=' elif f.value == 'not set': f.operator = '=' value = "" fallback = "''" can_be_null = True if 'ifnull' not in column_name: column_name = f'ifnull({column_name}, {fallback})' elif df and df.fieldtype == "Date": value = frappe.db.format_date(f.value) fallback = "'0001-01-01'" elif (df and df.fieldtype == "Datetime") or isinstance( f.value, datetime): value = frappe.db.format_datetime(f.value) fallback = "'0001-01-01 00:00:00'" elif df and df.fieldtype == "Time": value = get_time(f.value).strftime("%H:%M:%S.%f") fallback = "'00:00:00'" elif f.operator.lower() in ("like", "not like") or ( isinstance(f.value, str) and (not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])): value = "" if f.value == None else f.value fallback = "''" if f.operator.lower() in ("like", "not like") and isinstance( value, str): # because "like" uses backslash (\) for escaping value = value.replace("\\", "\\\\").replace("%", "%%") elif f.operator == '=' and df and df.fieldtype in [ 'Link', 'Data' ]: # TODO: Refactor if possible value = f.value or "''" fallback = "''" elif f.fieldname == 'name': value = f.value or "''" fallback = "''" else: value = flt(f.value) fallback = 0 if isinstance(f.value, Column): quote = '"' if frappe.conf.db_type == 'postgres' else "`" value = f"{tname}.{quote}{f.value.name}{quote}" # escape value elif isinstance(value, str) and not f.operator.lower() == 'between': value = f"{frappe.db.escape(value, percent=False)}" if (self.ignore_ifnull or not can_be_null or (f.value and f.operator.lower() in ('=', 'like')) or 'ifnull(' in column_name.lower()): if f.operator.lower() == 'like' and frappe.conf.get( 'db_type') == 'postgres': f.operator = 'ilike' condition = f'{column_name} {f.operator} {value}' else: condition = f'ifnull({column_name}, {fallback}) {f.operator} {value}' return condition