def test_tolerant_remove(self):
"""Ignores error if item not found in list."""
assert not util.tolerant_remove([], 'foo')
a = ['foo', 'bar', 'baz']
assert util.tolerant_remove(a, 'bar') is True
assert a == ['foo', 'baz']
def get_students_query( # noqa
advisor_plan_mappings=None,
coe_advisor_ldap_uids=None,
coe_ethnicities=None,
coe_genders=None,
coe_prep_statuses=None,
coe_probation=None,
coe_underrepresented=None,
ethnicities=None,
entering_terms=None,
expected_grad_terms=None,
genders=None,
gpa_ranges=None,
group_codes=None,
in_intensive_cohort=None,
is_active_asc=None,
is_active_coe=None,
last_name_ranges=None,
last_term_gpa_ranges=None,
levels=None,
majors=None,
midpoint_deficient_grade=None,
scope=(),
search_phrase=None,
sids=(),
transfer=None,
underrepresented=None,
unit_ranges=None,
):
# If no specific scope is required by criteria, default to the admin view.
if not scope:
scope = ['ADMIN']
query_tables = _student_query_tables_for_scope(scope)
if not query_tables:
return None, None, None
query_filter = ' WHERE true'
query_bindings = {}
# Name or SID search
if search_phrase:
words = search_phrase.upper().split()
# A numeric string indicates an SID search.
if len(words) == 1 and re.match(r'^\d+$', words[0]):
query_filter += ' AND (sas.sid LIKE :sid_phrase)'
query_bindings.update({'sid_phrase': f'{words[0]}%'})
# If a single word, search on both name and email.
elif len(words) == 1:
name_string = ''.join(re.split('\W', words[0]))
email_string = search_phrase.lower()
query_tables += f"""
LEFT JOIN {student_schema()}.student_names n
ON n.name LIKE :name_string
AND n.sid = sas.sid"""
query_filter += ' AND (sas.email_address LIKE :email_string OR n.name IS NOT NULL)'
query_bindings.update({
'email_string': f'{email_string}%',
'name_string': f'{name_string}%'
})
# If multiple words, search name only.
else:
for i, word in enumerate(words):
query_tables += f"""
JOIN {student_schema()}.student_names n{i}
ON n{i}.name LIKE :name_phrase_{i}
AND n{i}.sid = sas.sid"""
word = ''.join(re.split('\W', word))
query_bindings.update({f'name_phrase_{i}': f'{word}%'})
if ethnicities:
query_tables += f""" JOIN {student_schema()}.ethnicities e ON e.sid = sas.sid"""
if genders or underrepresented is not None:
query_tables += f""" JOIN {student_schema()}.demographics d ON d.sid = sas.sid"""
if sids:
query_filter += f' AND sas.sid = ANY(:sids)'
query_bindings.update({'sids': sids})
# Generic SIS criteria
if gpa_ranges:
sql_ready_gpa_ranges = [
f"numrange({gpa_range['min']}, {gpa_range['max']}, '[]')"
for gpa_range in gpa_ranges
]
query_filter += _number_ranges_to_sql('sas.gpa', sql_ready_gpa_ranges)
if last_term_gpa_ranges:
sql_ready_term_gpa_ranges = [
f"numrange({gpa_range['min']}, {gpa_range['max']}, '[]')"
for gpa_range in last_term_gpa_ranges
]
query_filter += _number_ranges_to_sql('set.term_gpa',
sql_ready_term_gpa_ranges)
query_tables += f"""
JOIN {student_schema()}.student_enrollment_terms set
ON set.sid = sas.sid AND set.term_id = :previous_term_id"""
query_bindings.update(
{'previous_term_id': previous_term_id(current_term_id())})
query_filter += _number_ranges_to_sql('sas.units',
unit_ranges) if unit_ranges else ''
if last_name_ranges:
query_filter += _last_name_ranges_to_sql(last_name_ranges)
if entering_terms:
query_filter += ' AND sas.entering_term = ANY(:entering_terms)'
query_bindings.update({'entering_terms': entering_terms})
if ethnicities:
query_filter += ' AND e.ethnicity = ANY(:ethnicities)'
query_bindings.update({'ethnicities': ethnicities})
if expected_grad_terms:
query_filter += ' AND sas.expected_grad_term = ANY(:expected_grad_terms)'
query_bindings.update({'expected_grad_terms': expected_grad_terms})
if underrepresented is not None:
query_filter += ' AND d.minority IS :underrepresented'
query_bindings.update({'underrepresented': underrepresented})
if genders:
query_filter += ' AND d.gender = ANY(:genders)'
query_bindings.update({'genders': genders})
if levels:
query_filter += ' AND sas.level = ANY(:levels)'
query_bindings.update({'levels': [level_to_code(l) for l in levels]})
if majors:
# Only modify the majors list clone
_majors = majors.copy()
major_filters = []
# Afaik, no student can declare a major and remain undeclared. However, in the interest of surfacing
# front-end bugs we do not use an 'if...else' below. We expect the front-end to be smart.
if tolerant_remove(_majors, 'Declared'):
major_filters.append('NOT maj.major ~* \'undeclared\'')
if tolerant_remove(_majors, 'Undeclared'):
major_filters.append('maj.major ~* \'undeclared\'')
if _majors:
major_filters.append('maj.major = ANY(:majors)')
query_filter += ' AND (' + ' OR '.join(major_filters) + ')'
query_tables += f' LEFT JOIN {student_schema()}.student_majors maj ON maj.sid = sas.sid'
query_bindings.update({'majors': _majors})
if midpoint_deficient_grade is True:
query_tables += f""" JOIN {student_schema()}.student_enrollment_terms ser
ON ser.sid = sas.sid
AND ser.term_id = :term_id
AND ser.midpoint_deficient_grade = TRUE"""
query_bindings.update({'term_id': current_term_id()})
if transfer is True:
query_filter += ' AND sas.transfer = TRUE'
if advisor_plan_mappings:
advisor_plan_filters = []
for idx, mapping in enumerate(advisor_plan_mappings):
advisor_sid = mapping['advisor_sid']
query_bindings.update({f'advisor_sid_{idx}': advisor_sid})
if mapping['academic_plan_code'] == '*':
advisor_plan_filters.append(
f'advs.advisor_sid = :advisor_sid_{idx}')
else:
academic_plan_code = mapping['academic_plan_code']
query_bindings.update(
{f'academic_plan_code_{idx}': academic_plan_code})
advisor_plan_filters.append(
f'(advs.advisor_sid = :advisor_sid_{idx} AND advs.academic_plan_code = :academic_plan_code_{idx})',
)
query_tables += f""" JOIN {advisor_schema()}.advisor_students advs ON advs.student_sid = sas.sid"""
query_tables += ' AND (' + ' OR '.join(advisor_plan_filters) + ')'
# ASC criteria
query_filter += f' AND s.active IS {is_active_asc}' if is_active_asc is not None else ''
query_filter += f' AND s.intensive IS {in_intensive_cohort}' if in_intensive_cohort is not None else ''
if group_codes:
query_filter += ' AND s.group_code = ANY(:group_codes)'
query_bindings.update({'group_codes': group_codes})
# COE criteria
if coe_advisor_ldap_uids:
query_filter += ' AND s.advisor_ldap_uid = ANY(:coe_advisor_ldap_uids)'
query_bindings.update({'coe_advisor_ldap_uids': coe_advisor_ldap_uids})
if coe_ethnicities:
query_filter += ' AND s.ethnicity = ANY(:coe_ethnicities)'
query_bindings.update({'coe_ethnicities': coe_ethnicities})
if coe_genders:
query_filter += ' AND s.gender = ANY(:coe_genders)'
query_bindings.update({'coe_genders': coe_genders})
if coe_prep_statuses:
query_filter += ' AND (' + ' OR '.join(
[f's.{cps} IS TRUE' for cps in coe_prep_statuses]) + ')'
if coe_probation is not None:
query_filter += f' AND s.probation IS {coe_probation}'
if coe_underrepresented is not None:
query_filter += f' AND s.minority IS {coe_underrepresented}'
if is_active_coe is False:
query_filter += f" AND s.status IN ('D','P','U','W','X','Z')"
elif is_active_coe is True:
query_filter += f" AND s.status NOT IN ('D','P','U','W','X','Z')"
return query_tables, query_filter, query_bindings
def get_students_query(
advisor_ldap_uids=None,
coe_prep_statuses=None,
ethnicities=None,
genders=None,
gpa_ranges=None,
group_codes=None,
in_intensive_cohort=None,
is_active_asc=None,
last_name_range=None,
levels=None,
majors=None,
scope=(),
search_phrase=None,
underrepresented=None,
unit_ranges=None,
): # noqa
query_tables = _student_query_tables_for_scope(scope)
if not query_tables:
return None, None, None
query_filter = ' WHERE true'
query_bindings = {}
# Name or SID search
if search_phrase:
phrase = ' '.join(f'{word}%' for word in search_phrase.split())
query_filter += """
AND (sas.sid ILIKE :phrase OR
(sas.first_name || ' ' || sas.last_name) ILIKE :phrase OR
(sas.first_name || ' ' || sas.last_name) ILIKE :phrase_padded OR
(sas.last_name || ' ' || sas.first_name) ILIKE :phrase OR
(sas.last_name || ' ' || sas.first_name) ILIKE :phrase_padded)
"""
query_bindings.update({
'phrase': phrase,
'phrase_padded': f'% {phrase}',
})
# Generic SIS criteria
query_filter += _numranges_to_sql('sas.gpa', gpa_ranges) if gpa_ranges else ''
query_filter += _numranges_to_sql('sas.units', unit_ranges) if unit_ranges else ''
query_filter += _query_filter_last_name_range(last_name_range)
if levels:
query_filter += ' AND sas.level = ANY(:levels)'
query_bindings.update({'levels': [level_to_code(l) for l in levels]})
if majors:
# Only modify the majors list clone
_majors = majors.copy()
major_filters = []
# Afaik, no student can declare a major and remain undeclared. However, in the interest of surfacing
# front-end bugs we do not use an 'if...else' below. We expect the front-end to be smart.
if tolerant_remove(_majors, 'Declared'):
major_filters.append('NOT maj.major ~* \'undeclared\'')
if tolerant_remove(_majors, 'Undeclared'):
major_filters.append('maj.major ~* \'undeclared\'')
if _majors:
major_filters.append('maj.major = ANY(:majors)')
query_filter += ' AND (' + ' OR '.join(major_filters) + ')'
query_tables += f' LEFT JOIN {student_schema()}.student_majors maj ON maj.sid = sas.sid'
query_bindings.update({'majors': _majors})
# ASC criteria
query_filter += f' AND s.active IS {is_active_asc}' if is_active_asc is not None else ''
query_filter += f' AND s.intensive IS {in_intensive_cohort}' if in_intensive_cohort is not None else ''
if group_codes:
query_filter += ' AND s.group_code = ANY(:group_codes)'
query_bindings.update({'group_codes': group_codes})
# COE criteria
if advisor_ldap_uids:
query_filter += ' AND s.advisor_ldap_uid = ANY(:advisor_ldap_uids)'
query_bindings.update({'advisor_ldap_uids': advisor_ldap_uids})
if coe_prep_statuses:
query_filter += ' AND (' + ' OR '.join([f's.{cps} IS TRUE' for cps in coe_prep_statuses]) + ')'
if ethnicities:
query_filter += ' AND s.ethnicity = ANY(:ethnicities)'
query_bindings.update({'ethnicities': ethnicities})
if genders:
query_filter += ' AND s.gender = ANY(:genders)'
query_bindings.update({'genders': genders})
query_filter += f' AND s.minority IS {underrepresented}' if underrepresented is not None else ''
return query_tables, query_filter, query_bindings