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
