def test_pre_filter_dyn_operator(self):
from corehq.apps.reports.daterange import get_daterange_start_end_dates
column = Mock()
column.name = 'dob_field'
column.between.return_value = 'foo'
table = Mock()
table.c = [column]
start_date, end_date = get_daterange_start_end_dates('lastmonth')
value = {'operator': 'lastmonth', 'operand': [None]}
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'array',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {
'dob_slug_0': str(start_date),
'dob_slug_1': str(end_date),
})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_dyn_operator(self):
from corehq.apps.reports.daterange import get_daterange_start_end_dates
column = Mock()
column.name = 'dob_field'
column.between.return_value = 'foo'
table = Mock()
table.c = [column]
start_date, end_date = get_daterange_start_end_dates('lastmonth')
value = {'operator': 'lastmonth', 'operand': [None]}
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'array',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {
'dob_slug_0': str(start_date),
'dob_slug_1': str(end_date),
})
self.assertEqual(filter_value.to_sql_filter().build_expression(table),
'foo')
def test_pre_filter_between_operator(self):
column = Mock()
column.name = 'dob_field'
column.between.return_value = 'foo'
table = Mock()
table.c = [column]
value = {
'operator': 'between',
'operand': ['2017-03-13', '2017-04-11']
}
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'date',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {
'dob_slug_0': '2017-03-13',
'dob_slug_1': '2017-04-11'
})
self.assertEqual(filter_value.to_sql_filter().build_expression(table),
'foo')
def test_pre_filter_value(self):
pre_value = 'yes'
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug': 'yes'})
def test_pre_filter_value(self):
pre_value = 'yes'
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug': 'yes'})
def test_pre_filter_distinct_from_operator(self):
value = {'operator': 'distinct from', 'operand': 'test'}
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug': 'test'})
def test_pre_filter_distinct_from_operator(self):
value = {'operator': 'distinct from', 'operand': 'test'}
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug': 'test'})
def test_pre_filter_invalid_operator(self):
value = {'operator': 'in', 'operand': 'no'} # "in" is invalid for scalar operand
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
with self.assertRaises(TypeError):
filter_value.to_sql_filter()
def test_pre_filter_invalid_operator(self):
value = {'operator': 'in', 'operand': 'no'} # "in" is invalid for scalar operand
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
with self.assertRaises(TypeError):
filter_value.to_sql_filter()
def test_pre_filter_value_null(self):
pre_value = None
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
}
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {})
self.assertEqual(str(filter_value.to_sql_filter().build_expression()),
'at_risk_field IS NULL')
def test_pre_filter_value_array(self):
pre_value = ['yes', 'maybe']
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'array',
'pre_value': pre_value
}
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug_0': 'yes', 'at_risk_slug_1': 'maybe'})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'at_risk_field IN (:at_risk_slug_0, :at_risk_slug_1)'
)
def test_pre_filter_value_array(self):
pre_value = ['yes', 'maybe']
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'array',
'pre_value': pre_value
}
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug_0': 'yes', 'at_risk_slug_1': 'maybe'})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'at_risk_field IN (:at_risk_slug_0, :at_risk_slug_1)'
)
def test_pre_filter_value_null(self):
pre_value = None
filter_ = {
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
}
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'at_risk_field IS NULL'
)
def test_pre_filter_between_operator(self):
value = {'operator': 'between', 'operand': ['2017-03-13', '2017-04-11']}
filter_ = {
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'date',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'dob_slug_0': '2017-03-13', 'dob_slug_1': '2017-04-11'})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'dob_field BETWEEN :dob_slug_0 AND :dob_slug_1'
)
def test_pre_filter_between_operator(self):
value = {'operator': 'between', 'operand': ['2017-03-13', '2017-04-11']}
filter_ = {
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'date',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'dob_slug_0': '2017-03-13', 'dob_slug_1': '2017-04-11'})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'dob_field BETWEEN :dob_slug_0 AND :dob_slug_1'
)
def test_pre_filter_value_null(self):
column = Mock()
column.name = 'at_risk_field'
column.is_.return_value = 'foo'
table = Mock()
table.c = [column]
pre_value = None
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_value_array(self):
column = Mock()
column.name = 'at_risk_field'
column.in_.return_value = 'foo'
table = Mock()
table.c = [column]
pre_value = ['yes', 'maybe']
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'array',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug_0': 'yes', 'at_risk_slug_1': 'maybe'})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_value_null(self):
column = Mock()
column.name = 'at_risk_field'
column.is_.return_value = 'foo'
table = Mock()
table.c = [column]
pre_value = None
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_value_array(self):
column = Mock()
column.name = 'at_risk_field'
column.in_.return_value = 'foo'
table = Mock()
table.c = [column]
pre_value = ['yes', 'maybe']
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'array',
'pre_value': pre_value
})
filter_value = PreFilterValue(filter_, {'operand': pre_value})
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug_0': 'yes', 'at_risk_slug_1': 'maybe'})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_value_empty(self):
pre_values = ['', None]
operators = ['', '=']
for pre_value in pre_values:
for operator in operators:
filter_ = {
'type': 'pre',
'field': 'empty_field',
'slug': 'empty_field_slug',
'datatype': 'string',
'pre_value': pre_value,
'pre_operator': operator
}
filter_value = PreFilterValue(filter_, {'operand': pre_value, 'operator': operator})
self.assertEqual(filter_value.to_sql_values(), {'empty_field_slug': ''})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'empty_field = :empty_field_slug OR empty_field IS NULL'
)
def test_pre_filter_distinct_from_operator(self):
column = Mock()
column.name = 'at_risk_field'
column.is_distinct_from.return_value = 'foo'
table = Mock()
table.c = [column]
value = {'operator': 'distinct from', 'operand': 'test'}
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'at_risk_field',
'slug': 'at_risk_slug',
'datatype': 'string',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'at_risk_slug': 'test'})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_value_exists(self):
pre_values = ['', None]
operator = '!='
for pre_value in pre_values:
filter_ = {
'type': 'pre',
'field': 'exists_field',
'slug': 'exists_field_slug',
'datatype': 'string',
'pre_value': pre_value,
'pre_operator': operator
}
filter_value = PreFilterValue(filter_, {'operand': pre_value, 'operator': operator})
self.assertEqual(filter_value.to_sql_values(), {'exists_field_slug': ''})
# != '' filters out null data in postgres which is why the expression doesn't need to check nulls
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'exists_field != :exists_field_slug'
)
def test_pre_filter_between_operator(self):
column = Mock()
column.name = 'dob_field'
column.between.return_value = 'foo'
table = Mock()
table.c = [column]
value = {'operator': 'between', 'operand': ['2017-03-13', '2017-04-11']}
filter_ = ReportFilter.wrap({
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'date',
'pre_value': value['operand'],
'pre_operator': value['operator'],
})
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {'dob_slug_0': '2017-03-13', 'dob_slug_1': '2017-04-11'})
self.assertEqual(filter_value.to_sql_filter().build_expression(table), 'foo')
def test_pre_filter_dyn_operator(self):
from corehq.apps.reports.daterange import get_daterange_start_end_dates
start_date, end_date = get_daterange_start_end_dates('lastmonth')
value = {'operator': 'lastmonth', 'operand': [None]}
filter_ = {
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'array',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {
'dob_slug_0': str(start_date),
'dob_slug_1': str(end_date),
})
self.assertEqual(str(filter_value.to_sql_filter().build_expression()),
'dob_field BETWEEN :dob_slug_0 AND :dob_slug_1')
def test_pre_filter_dyn_operator(self):
from corehq.apps.reports.daterange import get_daterange_start_end_dates
start_date, end_date = get_daterange_start_end_dates('lastmonth')
value = {'operator': 'lastmonth', 'operand': [None]}
filter_ = {
'type': 'pre',
'field': 'dob_field',
'slug': 'dob_slug',
'datatype': 'array',
'pre_value': value['operand'],
'pre_operator': value['operator'],
}
filter_value = PreFilterValue(filter_, value)
self.assertEqual(filter_value.to_sql_values(), {
'dob_slug_0': str(start_date),
'dob_slug_1': str(end_date),
})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'dob_field BETWEEN :dob_slug_0 AND :dob_slug_1'
)
def test_pre_filter_value_exists_date_types(self):
pre_values = ['', None]
operator = '!='
date_types = [DATA_TYPE_DATE, DATA_TYPE_DATETIME]
for pre_value in pre_values:
for datatype in date_types:
filter_ = {
'type': 'pre',
'field': 'exists_field',
'slug': 'exists_field_slug',
'datatype': datatype,
'pre_value': pre_value,
'pre_operator': operator
}
filter_value = PreFilterValue(filter_, {
'operand': pre_value,
'operator': operator
})
self.assertEqual(filter_value.to_sql_values(), {})
self.assertEqual(
str(filter_value.to_sql_filter().build_expression()),
'exists_field IS NOT NULL')
