def build_filter_string(self, where=None, joined=None, is_on=False):
''' Builds WHERE clauses or added ON conditions '''
simple_operators = {
'EQ': "=", "NE": "!=", "LE": "<=", "LT": "<", "GE": ">=",
"GT": ">", "LK": "LIKE"
}
boolean_operators = {
"NL": "IS NULL", "NN": "IS NOT NULL"
}
suffix = ''
if joined is not None:
suffix = self.get_join_suffix(joined)
if where is None or (joined is None and is_on):
return ''
arr_result = []
for each_clause in where:
w_clause = each_clause.replace('\\-', '|')
w_clause = w_clause.split('-')
w_clause = [f.replace('|', '-') for f in w_clause]
if w_clause[0].upper() == 'AND' or w_clause[0].upper() == 'OR':
arr_result.append(w_clause[0])
elif QueryBuilder.validate_clause(w_clause, joined, is_on, suffix):
if w_clause[0].upper() in simple_operators:
arr_result.append(
f'{w_clause[1]} '
f'{simple_operators[w_clause[0].upper()]} '
f'{w_clause[2]}'
)
elif w_clause[0].upper() in boolean_operators:
arr_result.append(
f'{w_clause[1]} '
f'{boolean_operators[w_clause[0].upper()]}'
)
elif w_clause[0].upper() == 'IN':
arr_result.append(f'{w_clause[1]} IN ({",".join(w_clause[2:])})')
elif w_clause[0].upper() in ['EQON', 'NEON', 'LEON', 'GEON', 'LTON', 'GTON']:
resulting_string = f"regexp_replace(CAST({w_clause[1]} AS STRING), '[^[:digit:]]','')"
if len(w_clause) == 5: # Substring
resulting_string = f"substring({resulting_string}, {w_clause[3]}, {w_clause[4]})"
arr_result.append(f"{resulting_string} {simple_operators.get(w_clause[0].upper()[:2])} '{w_clause[2]}'")
elif w_clause[0].upper() in ['EQLPONSTR', 'NELPONSTR', 'LELPONSTR', 'GELPONSTR', 'LTLPONSTR', 'GTLPONSTR']:
resulting_string = f"regexp_replace(CAST({w_clause[1]} AS STRING), '[^[:digit:]]','')"
if len(w_clause) == 7: # Substring
resulting_string = f"substring(LPAD({resulting_string}, {w_clause[3]}, '{w_clause[4]}'), {w_clause[5]}, {w_clause[6]})"
arr_result.append(f"{resulting_string} {simple_operators.get(w_clause[0].upper()[:2])} '{w_clause[2]}'")
elif w_clause[0].upper() in ['EQSTR', 'NESTR', 'LESTR', 'GESTR', 'LTSTR', 'GTSTR']:
arr_result.append(f"substring(CAST({w_clause[1]} AS STRING), {w_clause[3]}, {w_clause[4]}) {simple_operators.get(w_clause[0].upper()[:2])} {w_clause[2]}")
elif w_clause[0].upper() in ['EQLPSTR', 'NELPSTR', 'LELPSTR', 'GELPSTR', 'LTLPSTR', 'GTLPSTR']:
arr_result.append(f"substring(LPAD(CAST({w_clause[1]} AS VARCHAR({w_clause[3]})), {w_clause[3]}, '{w_clause[4]}'), {w_clause[5]}, {w_clause[6]}) {simple_operators.get(w_clause[0].upper()[:2])} {w_clause[2]}")
elif w_clause[0].upper() in ['EQLPINT', 'NELPINT', 'LELPINT', 'GELPINT', 'LTLPINT', 'GTLPINT']:
arr_result.append(f"CAST(substring(LPAD(CAST({w_clause[1]} AS VARCHAR({w_clause[3]})), {w_clause[3]}, '{w_clause[4]}'), {w_clause[5]}, {w_clause[6]}) AS INTEGER) {simple_operators.get(w_clause[0].upper()[:2])} {w_clause[2]}")
elif w_clause[0].upper() in ['EQSZ', 'NESZ', 'LESZ', 'GESZ', 'LTSZ', 'GTSZ']:
arr_result.append(f"LENGTH(CAST({w_clause[1]} AS STRING)) {simple_operators.get(w_clause[0].upper()[:2])} {w_clause[2]}")
return ' '.join(arr_result)
def build_filter_string(self, where=None, joined=None, is_on=False):
""" Builds WHERE clauses or added ON conditions """
suffix = ''
if joined is not None:
suffix = self.get_join_suffix(joined)
if where is None or (joined is None and is_on):
return ''
arr_result = []
for each_clause in where:
w_clause = each_clause.replace('\\-', '|')
w_clause = w_clause.split('-')
w_clause = [f.replace('|', '-') for f in w_clause]
if w_clause[0].upper() == 'AND' or w_clause[0].upper() == 'OR':
arr_result.append(w_clause[0])
continue
if not QueryBuilder.validate_clause(w_clause, joined, is_on,
suffix):
continue
criteria = self.build_criteria(w_clause)
if criteria:
arr_result.append(criteria)
return ' '.join(arr_result)
def test_invalid_out_join_sufixo(self):
''' Sinaliza negativamente quando é um filtro fora do
join e tem um sufixo '''
result = QueryBuilder.validate_clause(['eq', 'any_mun'], 'municipio',
False, '_mun')
self.assertEqual(result, False)
def test_invalid_join_sem_sufixo(self):
''' Sinaliza negativamente quando é um filtro de join
e não tem um sufixo '''
result = QueryBuilder.validate_clause(['eq', 'any'], 'municipio', True,
'_mun')
self.assertEqual(result, False)
def test_valid_join_com_sufixo(self):
''' Sinaliza positivamente quando é um filtro de join
e tem um sufixo '''
result = QueryBuilder.validate_clause(['eq', 'any_mun'], 'municipio',
True, '_mun')
self.assertEqual(result, True)
def test_valid_no_join(self):
''' Sinaliza positivamente quando não é um join '''
result = QueryBuilder.validate_clause(['eq', 'any'], None, None, None)
self.assertEqual(result, True)