def get_query(self, connector, filters):
'''
Constucts query for querying not stated students count by student id.
:param connector: database connector providing database information
:param dict filters: demographic filters information.
If filters is none, then it will return query which retieves all unique students count.
'''
_fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select([count().label(Constants.COUNT)],
from_obj=[_fact_asmt_outcome_vw])\
.where(and_(_fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT, _fact_asmt_outcome_vw.c.asmt_type == self.asmt_type, _fact_asmt_outcome_vw.c.asmt_year == self.asmt_year))
if self.state_code is not None:
query = query.where(
and_(_fact_asmt_outcome_vw.c.state_code == self.state_code))
if self.district_id is not None:
query = query.where(
and_(_fact_asmt_outcome_vw.c.district_id == self.district_id))
if self.school_id is not None:
query = query.where(
and_(_fact_asmt_outcome_vw.c.school_id == self.school_id))
query = apply_filter_to_query(query, _fact_asmt_outcome_vw, None,
filters)
return query
def test_apply_filter_to_query_with_no_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, {})
self.assertIsNone(query._whereclause)
def test_apply_filter_to_query_with_ethnic_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, {FILTERS_ETHNICITY: [FILTERS_ETHNICITY_AMERICAN]})
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.dmg_eth_derived", str(query._whereclause))
def test_apply_filter_to_query_with_iep_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, {FILTERS_PROGRAM_IEP: [YES]})
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.dmg_prg_iep", str(query._whereclause))
def test_apply_filter_to_query_with_no_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(query, fact_asmt_outcome,
dim_student, {})
self.assertIsNone(query._whereclause)
def test_apply_filter_to_query_with_gender_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(
query, fact_asmt_outcome, dim_student,
{FILTERS_SEX: [FILTERS_SEX_FEMALE, FILTERS_SEX_MALE]})
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.sex", str(query._whereclause))
def test_apply_filter_to_query_with_ethnic_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(
query, fact_asmt_outcome, dim_student,
{FILTERS_ETHNICITY: [FILTERS_ETHNICITY_AMERICAN]})
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.dmg_eth_derived",
str(query._whereclause))
def test_apply_filter_to_query_with_migrant_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
query = apply_filter_to_query(query, fact_asmt_outcome,
dim_student,
{FILTERS_PROGRAM_MIG: [NOT_STATED]})
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.dmg_sts_mig",
str(query._whereclause))
def test_apply_filter_to_query_with_groups(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([dim_student.c.group_1_id],
from_obj=([dim_student]))
query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, {FILTERS_GROUP: ['group1', 'group2']})
self.assertIsNotNone(query._whereclause)
self.assertIn("dim_student.group_1_id", str(query._whereclause))
self.assertIn("dim_student.group_3_id", str(query._whereclause))
self.assertIn("dim_student.group_5_id", str(query._whereclause))
self.assertIn("dim_student.group_7_id", str(query._whereclause))
self.assertIn("dim_student.group_10_id", str(query._whereclause))
def test_apply_filter_to_query_with_multi_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
filters = {FILTERS_SEX: [FILTERS_SEX_FEMALE],
FILTERS_PROGRAM_IEP: [NOT_STATED],
FILTERS_ETHNICITY: [FILTERS_ETHNICITY_MULTI]}
query = apply_filter_to_query(query, fact_asmt_outcome, dim_student, filters)
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.sex", str(query._whereclause))
self.assertIn("fact_asmt_outcome_vw.dmg_eth_derived", str(query._whereclause))
self.assertIn("fact_asmt_outcome_vw.dmg_prg_iep", str(query._whereclause))
def _get_student_ids(state_code, district_id, school_id, asmt_type, params,
asmt_year=None, grade=None, student_ids=None):
with EdCoreDBConnection(state_code=state_code) as connector:
# Get handle to tables
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
if asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS:
fact_table = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
else:
fact_table = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
# Build select
query = select_with_context(
[fact_table.c.student_id.label(Constants.STUDENT_ID), dim_student.c.first_name, dim_student.c.last_name],
from_obj=[
fact_table
.join(dim_student, and_(
fact_table.c.student_rec_id == dim_student.c.student_rec_id,
dim_student.c.rec_status == Constants.CURRENT,
))
.join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id, dim_asmt.c.rec_status == Constants.CURRENT))
],
permission=RolesConstants.PII,
state_code=state_code,
).distinct()
# Add where clauses
query = query.where(fact_table.c.state_code == state_code)
query = query.where(and_(fact_table.c.school_id == school_id))
query = query.where(and_(fact_table.c.district_id == district_id))
query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT))
query = query.where(and_(fact_table.c.asmt_type == asmt_type))
if grade is not None:
query = query.where(and_(fact_table.c.asmt_grade == grade))
if student_ids is not None:
query = query.where(and_(fact_table.c.student_id.in_(student_ids)))
elif asmt_year is not None:
query = query.where(and_(dim_asmt.c.asmt_period_year == asmt_year))
else:
raise InvalidParameterError('Need one of date_taken or asmt_year')
query = apply_filter_to_query(query, fact_table, dim_student, params)
# Add order by clause
query = query.order_by(dim_student.c.last_name).order_by(dim_student.c.first_name)
# Return the result
return connector.get_result(query)
def _get_asmt_records_iab(param, extract_type):
'''
query all asmt_guid and asmt_grade by given extract request params
'''
asmt_type = param.get(Constants.ASMTTYPE)
asmt_subject = param.get(Constants.ASMTSUBJECT)
state_code = param.get(Constants.STATECODE)
district_id = param.get(Constants.DISTRICTGUID)
school_id = param.get(Constants.SCHOOLGUID)
asmt_grade = param.get(Constants.ASMTGRADE)
asmt_year = param.get(Constants.ASMTYEAR)
student_id = param.get(Constants.STUDENTGUID)
# TODO: remove dim_asmt
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_block_asmt_outcome = connector.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_student = connector.get_table(Constants.DIM_STUDENT)
query = select_with_context([dim_asmt.c.asmt_guid.label(Constants.ASMT_GUID),
dim_asmt.c.effective_date.label(Constants.EFFECTIVE_DATE),
dim_asmt.c.asmt_claim_1_name.label(Constants.ASMT_CLAIM_1_NAME),
fact_block_asmt_outcome.c.asmt_grade.label(Constants.ASMT_GRADE)],
from_obj=[dim_asmt
.join(fact_block_asmt_outcome, and_(dim_asmt.c.asmt_rec_id == fact_block_asmt_outcome.c.asmt_rec_id))], permission=get_required_permission(extract_type), state_code=state_code)\
.where(and_(fact_block_asmt_outcome.c.state_code == state_code))\
.where(and_(fact_block_asmt_outcome.c.asmt_type == asmt_type))\
.where(and_(fact_block_asmt_outcome.c.asmt_subject == asmt_subject))\
.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))\
.group_by(dim_asmt.c.asmt_guid, dim_asmt.c.effective_date, dim_asmt.c.asmt_claim_1_name, fact_block_asmt_outcome.c.asmt_grade)
if district_id is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.district_id == district_id))
if school_id is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.school_id == school_id))
if asmt_grade is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.asmt_grade == asmt_grade))
if asmt_year is not None:
query = query.where(
and_(fact_block_asmt_outcome.c.asmt_year == asmt_year))
if student_id:
query = query.where(
and_(fact_block_asmt_outcome.c.student_id.in_(student_id)))
query = apply_filter_to_query(query, fact_block_asmt_outcome,
dim_student, param)
results = connector.get_result(query)
return results
def test_apply_filter_to_query_with_groups(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([dim_student.c.group_1_id],
from_obj=([dim_student]))
query = apply_filter_to_query(
query, fact_asmt_outcome, dim_student,
{FILTERS_GROUP: ['group1', 'group2']})
self.assertIsNotNone(query._whereclause)
self.assertIn("dim_student.group_1_id", str(query._whereclause))
self.assertIn("dim_student.group_3_id", str(query._whereclause))
self.assertIn("dim_student.group_5_id", str(query._whereclause))
self.assertIn("dim_student.group_7_id", str(query._whereclause))
self.assertIn("dim_student.group_10_id", str(query._whereclause))
def _get_asmt_records_iab(param, extract_type):
'''
query all asmt_guid and asmt_grade by given extract request params
'''
asmt_type = param.get(Constants.ASMTTYPE)
asmt_subject = param.get(Constants.ASMTSUBJECT)
state_code = param.get(Constants.STATECODE)
district_id = param.get(Constants.DISTRICTGUID)
school_id = param.get(Constants.SCHOOLGUID)
asmt_grade = param.get(Constants.ASMTGRADE)
asmt_year = param.get(Constants.ASMTYEAR)
student_id = param.get(Constants.STUDENTGUID)
# TODO: remove dim_asmt
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_block_asmt_outcome = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_student = connector.get_table(Constants.DIM_STUDENT)
query = select_with_context([dim_asmt.c.asmt_guid.label(Constants.ASMT_GUID),
dim_asmt.c.effective_date.label(Constants.EFFECTIVE_DATE),
dim_asmt.c.asmt_claim_1_name.label(Constants.ASMT_CLAIM_1_NAME),
fact_block_asmt_outcome.c.asmt_grade.label(Constants.ASMT_GRADE)],
from_obj=[dim_asmt
.join(fact_block_asmt_outcome, and_(dim_asmt.c.asmt_rec_id == fact_block_asmt_outcome.c.asmt_rec_id))], permission=get_required_permission(extract_type), state_code=state_code)\
.where(and_(fact_block_asmt_outcome.c.state_code == state_code))\
.where(and_(fact_block_asmt_outcome.c.asmt_type == asmt_type))\
.where(and_(fact_block_asmt_outcome.c.asmt_subject == asmt_subject))\
.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))\
.group_by(dim_asmt.c.asmt_guid, dim_asmt.c.effective_date, dim_asmt.c.asmt_claim_1_name, fact_block_asmt_outcome.c.asmt_grade)
if district_id is not None:
query = query.where(and_(fact_block_asmt_outcome.c.district_id == district_id))
if school_id is not None:
query = query.where(and_(fact_block_asmt_outcome.c.school_id == school_id))
if asmt_grade is not None:
query = query.where(and_(fact_block_asmt_outcome.c.asmt_grade == asmt_grade))
if asmt_year is not None:
query = query.where(and_(fact_block_asmt_outcome.c.asmt_year == asmt_year))
if student_id:
query = query.where(and_(fact_block_asmt_outcome.c.student_id.in_(student_id)))
query = apply_filter_to_query(query, fact_block_asmt_outcome, dim_student, param)
results = connector.get_result(query)
return results
def build_sub_query(self, extra_columns=[], where_guid=None):
'''
build select columns based on request
'''
query = select(extra_columns + [self._fact_asmt_outcome_vw.c.asmt_subject.label(Constants.ASMT_SUBJECT),
self._fact_asmt_outcome_vw.c.inst_hier_rec_id,
func.count().label(Constants.TOTAL),
self._fact_asmt_outcome_vw.c.asmt_perf_lvl.label(Constants.LEVEL)])\
.where(and_(self._fact_asmt_outcome_vw.c.state_code == self._state_code,
self._fact_asmt_outcome_vw.c.asmt_type == self._asmt_type,
self._fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT,
or_(self._fact_asmt_outcome_vw.c.administration_condition == Constants.ADMINISTRATION_CONDITION_STANDARDIZED, self._fact_asmt_outcome_vw.c.administration_condition == null()),
self._fact_asmt_outcome_vw.c.asmt_year == self._asmt_year))\
.group_by(self._fact_asmt_outcome_vw.c.asmt_subject,
self._fact_asmt_outcome_vw.c.inst_hier_rec_id,
self._fact_asmt_outcome_vw.c.asmt_perf_lvl)
if where_guid is not None:
query = query.where(and_(where_guid))
return apply_filter_to_query(query, self._fact_asmt_outcome_vw,
self._dim_student, self._filters)
def test_apply_filter_to_query_with_multi_filters(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
dim_student = connection.get_table(Constants.DIM_STUDENT)
query = select([fact_asmt_outcome.c.school_id],
from_obj=([fact_asmt_outcome]))
filters = {
FILTERS_SEX: [FILTERS_SEX_FEMALE],
FILTERS_PROGRAM_IEP: [NOT_STATED],
FILTERS_ETHNICITY: [FILTERS_ETHNICITY_MULTI]
}
query = apply_filter_to_query(query, fact_asmt_outcome,
dim_student, filters)
self.assertIsNotNone(query._whereclause)
self.assertIn("fact_asmt_outcome_vw.sex", str(query._whereclause))
self.assertIn("fact_asmt_outcome_vw.dmg_eth_derived",
str(query._whereclause))
self.assertIn("fact_asmt_outcome_vw.dmg_prg_iep",
str(query._whereclause))
def _assessment_item_and_raw_where_clause_builder(query, fact_asmt_outcome_vw,
params):
state_code = params.get(Constants.STATECODE)
asmt_year = params.get(Constants.ASMTYEAR)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_subject = params.get(Constants.ASMTSUBJECT)
asmt_grade = params.get(Constants.ASMTGRADE)
query = query.where(and_(
fact_asmt_outcome_vw.c.state_code == state_code,
fact_asmt_outcome_vw.c.asmt_year == asmt_year,
fact_asmt_outcome_vw.c.asmt_type == asmt_type,
fact_asmt_outcome_vw.c.asmt_subject == asmt_subject,
fact_asmt_outcome_vw.c.asmt_grade == asmt_grade,
fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
# Filters demographics
query = apply_filter_to_query(query, fact_asmt_outcome_vw, None, params)
return query
def _assessment_item_and_raw_where_clause_builder(query, fact_asmt_outcome_vw,
params):
state_code = params.get(Constants.STATECODE)
asmt_year = params.get(Constants.ASMTYEAR)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_subject = params.get(Constants.ASMTSUBJECT)
asmt_grade = params.get(Constants.ASMTGRADE)
query = query.where(
and_(fact_asmt_outcome_vw.c.state_code == state_code,
fact_asmt_outcome_vw.c.asmt_year == asmt_year,
fact_asmt_outcome_vw.c.asmt_type == asmt_type,
fact_asmt_outcome_vw.c.asmt_subject == asmt_subject,
fact_asmt_outcome_vw.c.asmt_grade == asmt_grade,
fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
# Filters demographics
query = apply_filter_to_query(query, fact_asmt_outcome_vw, None, params)
return query
def get_list_of_students_fao(params):
stateCode = str(params[Constants.STATECODE])
districtId = str(params[Constants.DISTRICTGUID])
schoolId = str(params[Constants.SCHOOLGUID])
asmtGrade = params.get(Constants.ASMTGRADE)
asmtSubject = params.get(Constants.ASMTSUBJECT)
asmtYear = params.get(Constants.ASMTYEAR)
with EdCoreDBConnection(state_code=stateCode) as connector:
# get handle to tables
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context(
[
dim_student.c.student_id.label('student_id'),
dim_student.c.first_name.label('first_name'),
dim_student.c.middle_name.label('middle_name'),
dim_student.c.last_name.label('last_name'),
fact_asmt_outcome_vw.c.state_code.label('state_code'),
fact_asmt_outcome_vw.c.enrl_grade.label('enrollment_grade'),
fact_asmt_outcome_vw.c.asmt_grade.label('asmt_grade'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
fact_asmt_outcome_vw.c.date_taken.label('date_taken'),
fact_asmt_outcome_vw.c.asmt_score.label('asmt_score'),
fact_asmt_outcome_vw.c.asmt_score_range_min.label(
'asmt_score_range_min'),
fact_asmt_outcome_vw.c.asmt_score_range_max.label(
'asmt_score_range_max'),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label('asmt_perf_lvl'),
dim_asmt.c.asmt_type.label('asmt_type'),
dim_asmt.c.asmt_score_min.label('asmt_score_min'),
dim_asmt.c.asmt_score_max.label('asmt_score_max'),
dim_asmt.c.asmt_claim_1_name.label('asmt_claim_1_name'),
dim_asmt.c.asmt_claim_2_name.label('asmt_claim_2_name'),
dim_asmt.c.asmt_claim_3_name.label('asmt_claim_3_name'),
dim_asmt.c.asmt_claim_4_name.label('asmt_claim_4_name'),
dim_asmt.c.asmt_perf_lvl_name_1.label("asmt_cut_point_name_1"),
dim_asmt.c.asmt_perf_lvl_name_2.label("asmt_cut_point_name_2"),
dim_asmt.c.asmt_perf_lvl_name_3.label("asmt_cut_point_name_3"),
dim_asmt.c.asmt_perf_lvl_name_4.label("asmt_cut_point_name_4"),
dim_asmt.c.asmt_perf_lvl_name_5.label("asmt_cut_point_name_5"),
dim_asmt.c.asmt_cut_point_1.label("asmt_cut_point_1"),
dim_asmt.c.asmt_cut_point_2.label("asmt_cut_point_2"),
dim_asmt.c.asmt_cut_point_3.label("asmt_cut_point_3"),
dim_asmt.c.asmt_cut_point_4.label("asmt_cut_point_4"),
fact_asmt_outcome_vw.c.asmt_claim_1_score.label(
'asmt_claim_1_score'),
fact_asmt_outcome_vw.c.asmt_claim_2_score.label(
'asmt_claim_2_score'),
fact_asmt_outcome_vw.c.asmt_claim_3_score.label(
'asmt_claim_3_score'),
fact_asmt_outcome_vw.c.asmt_claim_4_score.label(
'asmt_claim_4_score'),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_min.label(
'asmt_claim_1_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_min.label(
'asmt_claim_2_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_min.label(
'asmt_claim_3_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_min.label(
'asmt_claim_4_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_max.label(
'asmt_claim_1_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_max.label(
'asmt_claim_2_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_max.label(
'asmt_claim_3_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_max.label(
'asmt_claim_4_score_range_max'),
# demographic information
fact_asmt_outcome_vw.c.dmg_eth_derived.label('dmg_eth_derived'
),
fact_asmt_outcome_vw.c.dmg_prg_iep.label('dmg_prg_iep'),
fact_asmt_outcome_vw.c.dmg_prg_lep.label('dmg_prg_lep'),
fact_asmt_outcome_vw.c.dmg_prg_504.label('dmg_prg_504'),
fact_asmt_outcome_vw.c.dmg_sts_ecd.label('dmg_sts_ecd'),
fact_asmt_outcome_vw.c.dmg_sts_mig.label('dmg_sts_mig'),
fact_asmt_outcome_vw.c.sex.label('sex'),
# grouping information
dim_student.c.group_1_id.label('group_1_id'),
dim_student.c.group_1_text.label('group_1_text'),
dim_student.c.group_2_id.label('group_2_id'),
dim_student.c.group_2_text.label('group_2_text'),
dim_student.c.group_3_id.label('group_3_id'),
dim_student.c.group_3_text.label('group_3_text'),
dim_student.c.group_4_id.label('group_4_id'),
dim_student.c.group_4_text.label('group_4_text'),
dim_student.c.group_5_id.label('group_5_id'),
dim_student.c.group_5_text.label('group_5_text'),
dim_student.c.group_6_id.label('group_6_id'),
dim_student.c.group_6_text.label('group_6_text'),
dim_student.c.group_7_id.label('group_7_id'),
dim_student.c.group_7_text.label('group_7_text'),
dim_student.c.group_8_id.label('group_8_id'),
dim_student.c.group_8_text.label('group_8_text'),
dim_student.c.group_9_id.label('group_9_id'),
dim_student.c.group_9_text.label('group_9_text'),
dim_student.c.group_10_id.label('group_10_id'),
dim_student.c.group_10_text.label('group_10_text'),
dim_asmt.c.asmt_claim_perf_lvl_name_1.label(
'asmt_claim_perf_lvl_name_1'),
dim_asmt.c.asmt_claim_perf_lvl_name_2.label(
'asmt_claim_perf_lvl_name_2'),
dim_asmt.c.asmt_claim_perf_lvl_name_3.label(
'asmt_claim_perf_lvl_name_3'),
fact_asmt_outcome_vw.c.asmt_claim_1_perf_lvl.label(
'asmt_claim_1_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_2_perf_lvl.label(
'asmt_claim_2_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_3_perf_lvl.label(
'asmt_claim_3_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_4_perf_lvl.label(
'asmt_claim_4_perf_lvl'),
fact_asmt_outcome_vw.c.administration_condition.label(
'administration_condition'),
func.coalesce(fact_asmt_outcome_vw.c.complete,
True).label('complete')
],
from_obj=[
fact_asmt_outcome_vw.join(
dim_student,
and_(fact_asmt_outcome_vw.c.student_rec_id ==
dim_student.c.student_rec_id)).join(
dim_asmt,
and_(dim_asmt.c.asmt_rec_id ==
fact_asmt_outcome_vw.c.asmt_rec_id))
],
permission=RolesConstants.PII,
state_code=stateCode)
query = query.where(fact_asmt_outcome_vw.c.state_code == stateCode)
query = query.where(and_(fact_asmt_outcome_vw.c.school_id == schoolId))
query = query.where(
and_(fact_asmt_outcome_vw.c.district_id == districtId))
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_year == asmtYear))
query = query.where(
and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
query = apply_filter_to_query(query, fact_asmt_outcome_vw, dim_student,
params)
if asmtSubject is not None:
query = query.where(and_(dim_asmt.c.asmt_subject.in_(asmtSubject)))
if asmtGrade is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_grade == asmtGrade))
query = query.where(
and_(
or_(
and_(
fact_asmt_outcome_vw.c.asmt_type.in_(
[AssessmentType.SUMMATIVE]), (or_(
fact_asmt_outcome_vw.c.administration_condition
== Constants.ADMINISTRATION_CONDITION_INVALID,
fact_asmt_outcome_vw.c.administration_condition
== null()))),
and_(
fact_asmt_outcome_vw.c.asmt_type.in_(
[AssessmentType.INTERIM_COMPREHENSIVE])),
(or_(
fact_asmt_outcome_vw.c.administration_condition
== null(),
fact_asmt_outcome_vw.c.administration_condition.in_([
Constants.ADMINISTRATION_CONDITION_STANDARDIZED,
Constants.ADMINISTRATION_CONDITION_NON_STANDARDIZED
]))))))
query = query.order_by(dim_student.c.last_name).order_by(
dim_student.c.first_name).order_by(
desc(fact_asmt_outcome_vw.c.date_taken))
return connector.get_result(query)
def get_list_of_students_fao(params):
stateCode = str(params[Constants.STATECODE])
districtId = str(params[Constants.DISTRICTGUID])
schoolId = str(params[Constants.SCHOOLGUID])
asmtGrade = params.get(Constants.ASMTGRADE)
asmtSubject = params.get(Constants.ASMTSUBJECT)
asmtYear = params.get(Constants.ASMTYEAR)
with EdCoreDBConnection(state_code=stateCode) as connector:
# get handle to tables
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([
dim_student.c.student_id.label('student_id'),
dim_student.c.first_name.label('first_name'),
dim_student.c.middle_name.label('middle_name'),
dim_student.c.last_name.label('last_name'),
fact_asmt_outcome_vw.c.state_code.label('state_code'),
fact_asmt_outcome_vw.c.enrl_grade.label('enrollment_grade'),
fact_asmt_outcome_vw.c.asmt_grade.label('asmt_grade'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
fact_asmt_outcome_vw.c.date_taken.label('date_taken'),
fact_asmt_outcome_vw.c.asmt_score.label('asmt_score'),
fact_asmt_outcome_vw.c.asmt_score_range_min.label('asmt_score_range_min'),
fact_asmt_outcome_vw.c.asmt_score_range_max.label('asmt_score_range_max'),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label('asmt_perf_lvl'),
dim_asmt.c.asmt_type.label('asmt_type'),
dim_asmt.c.asmt_score_min.label('asmt_score_min'),
dim_asmt.c.asmt_score_max.label('asmt_score_max'),
dim_asmt.c.asmt_claim_1_name.label('asmt_claim_1_name'),
dim_asmt.c.asmt_claim_2_name.label('asmt_claim_2_name'),
dim_asmt.c.asmt_claim_3_name.label('asmt_claim_3_name'),
dim_asmt.c.asmt_claim_4_name.label('asmt_claim_4_name'),
dim_asmt.c.asmt_perf_lvl_name_1.label("asmt_cut_point_name_1"),
dim_asmt.c.asmt_perf_lvl_name_2.label("asmt_cut_point_name_2"),
dim_asmt.c.asmt_perf_lvl_name_3.label("asmt_cut_point_name_3"),
dim_asmt.c.asmt_perf_lvl_name_4.label("asmt_cut_point_name_4"),
dim_asmt.c.asmt_perf_lvl_name_5.label("asmt_cut_point_name_5"),
dim_asmt.c.asmt_cut_point_1.label("asmt_cut_point_1"),
dim_asmt.c.asmt_cut_point_2.label("asmt_cut_point_2"),
dim_asmt.c.asmt_cut_point_3.label("asmt_cut_point_3"),
dim_asmt.c.asmt_cut_point_4.label("asmt_cut_point_4"),
fact_asmt_outcome_vw.c.asmt_claim_1_score.label('asmt_claim_1_score'),
fact_asmt_outcome_vw.c.asmt_claim_2_score.label('asmt_claim_2_score'),
fact_asmt_outcome_vw.c.asmt_claim_3_score.label('asmt_claim_3_score'),
fact_asmt_outcome_vw.c.asmt_claim_4_score.label('asmt_claim_4_score'),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_min.label('asmt_claim_1_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_min.label('asmt_claim_2_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_min.label('asmt_claim_3_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_min.label('asmt_claim_4_score_range_min'),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_max.label('asmt_claim_1_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_max.label('asmt_claim_2_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_max.label('asmt_claim_3_score_range_max'),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_max.label('asmt_claim_4_score_range_max'),
# demographic information
fact_asmt_outcome_vw.c.dmg_eth_derived.label('dmg_eth_derived'),
fact_asmt_outcome_vw.c.dmg_prg_iep.label('dmg_prg_iep'),
fact_asmt_outcome_vw.c.dmg_prg_lep.label('dmg_prg_lep'),
fact_asmt_outcome_vw.c.dmg_prg_504.label('dmg_prg_504'),
fact_asmt_outcome_vw.c.dmg_sts_ecd.label('dmg_sts_ecd'),
fact_asmt_outcome_vw.c.dmg_sts_mig.label('dmg_sts_mig'),
fact_asmt_outcome_vw.c.sex.label('sex'),
# grouping information
dim_student.c.group_1_id.label('group_1_id'),
dim_student.c.group_1_text.label('group_1_text'),
dim_student.c.group_2_id.label('group_2_id'),
dim_student.c.group_2_text.label('group_2_text'),
dim_student.c.group_3_id.label('group_3_id'),
dim_student.c.group_3_text.label('group_3_text'),
dim_student.c.group_4_id.label('group_4_id'),
dim_student.c.group_4_text.label('group_4_text'),
dim_student.c.group_5_id.label('group_5_id'),
dim_student.c.group_5_text.label('group_5_text'),
dim_student.c.group_6_id.label('group_6_id'),
dim_student.c.group_6_text.label('group_6_text'),
dim_student.c.group_7_id.label('group_7_id'),
dim_student.c.group_7_text.label('group_7_text'),
dim_student.c.group_8_id.label('group_8_id'),
dim_student.c.group_8_text.label('group_8_text'),
dim_student.c.group_9_id.label('group_9_id'),
dim_student.c.group_9_text.label('group_9_text'),
dim_student.c.group_10_id.label('group_10_id'),
dim_student.c.group_10_text.label('group_10_text'),
dim_asmt.c.asmt_claim_perf_lvl_name_1.label('asmt_claim_perf_lvl_name_1'),
dim_asmt.c.asmt_claim_perf_lvl_name_2.label('asmt_claim_perf_lvl_name_2'),
dim_asmt.c.asmt_claim_perf_lvl_name_3.label('asmt_claim_perf_lvl_name_3'),
fact_asmt_outcome_vw.c.asmt_claim_1_perf_lvl.label('asmt_claim_1_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_2_perf_lvl.label('asmt_claim_2_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_3_perf_lvl.label('asmt_claim_3_perf_lvl'),
fact_asmt_outcome_vw.c.asmt_claim_4_perf_lvl.label('asmt_claim_4_perf_lvl'),
fact_asmt_outcome_vw.c.administration_condition.label('administration_condition'),
func.coalesce(fact_asmt_outcome_vw.c.complete, True).label('complete')
], from_obj=[
fact_asmt_outcome_vw
.join(dim_student, and_(fact_asmt_outcome_vw.c.student_rec_id == dim_student.c.student_rec_id))
.join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_asmt_outcome_vw.c.asmt_rec_id))
], permission=RolesConstants.PII, state_code=stateCode)
query = query.where(fact_asmt_outcome_vw.c.state_code == stateCode)
query = query.where(and_(fact_asmt_outcome_vw.c.school_id == schoolId))
query = query.where(and_(fact_asmt_outcome_vw.c.district_id == districtId))
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_year == asmtYear))
query = query.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
query = apply_filter_to_query(query, fact_asmt_outcome_vw, dim_student, params)
if asmtSubject is not None:
query = query.where(and_(dim_asmt.c.asmt_subject.in_(asmtSubject)))
if asmtGrade is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_grade == asmtGrade))
query = query.where(and_(or_(and_(fact_asmt_outcome_vw.c.asmt_type.in_([AssessmentType.SUMMATIVE]),
(or_(fact_asmt_outcome_vw.c.administration_condition == Constants.ADMINISTRATION_CONDITION_INVALID,
fact_asmt_outcome_vw.c.administration_condition == null()))),
and_(fact_asmt_outcome_vw.c.asmt_type.in_([AssessmentType.INTERIM_COMPREHENSIVE])),
(or_(fact_asmt_outcome_vw.c.administration_condition == null(),
fact_asmt_outcome_vw.c.administration_condition.in_([Constants.ADMINISTRATION_CONDITION_STANDARDIZED,
Constants.ADMINISTRATION_CONDITION_NON_STANDARDIZED]))))))
query = query.order_by(dim_student.c.last_name).order_by(dim_student.c.first_name).order_by(desc(fact_asmt_outcome_vw.c.date_taken))
return connector.get_result(query)
def _get_student_ids(state_code,
district_id,
school_id,
asmt_type,
params,
asmt_year=None,
grade=None,
student_ids=None):
with EdCoreDBConnection(state_code=state_code) as connector:
# Get handle to tables
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
if asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS:
fact_table = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
else:
fact_table = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
# Build select
query = select_with_context(
[
fact_table.c.student_id.label(Constants.STUDENT_ID),
dim_student.c.first_name, dim_student.c.last_name
],
from_obj=[
fact_table.join(
dim_student,
and_(
fact_table.c.student_rec_id ==
dim_student.c.student_rec_id,
dim_student.c.rec_status == Constants.CURRENT,
)).join(
dim_asmt,
and_(
dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
dim_asmt.c.rec_status == Constants.CURRENT))
],
permission=RolesConstants.PII,
state_code=state_code,
).distinct()
# Add where clauses
query = query.where(fact_table.c.state_code == state_code)
query = query.where(and_(fact_table.c.school_id == school_id))
query = query.where(and_(fact_table.c.district_id == district_id))
query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT))
query = query.where(and_(fact_table.c.asmt_type == asmt_type))
if grade is not None:
query = query.where(and_(fact_table.c.asmt_grade == grade))
if student_ids is not None:
query = query.where(and_(fact_table.c.student_id.in_(student_ids)))
elif asmt_year is not None:
query = query.where(and_(dim_asmt.c.asmt_period_year == asmt_year))
else:
raise InvalidParameterError('Need one of date_taken or asmt_year')
query = apply_filter_to_query(query, fact_table, dim_student, params)
# Add order by clause
query = query.order_by(dim_student.c.last_name).order_by(
dim_student.c.first_name)
# Return the result
return connector.get_result(query)
def get_extract_assessment_query(params):
"""
private method to generate SQLAlchemy object or sql code for extraction
:param params: for query parameters asmt_type, asmt_subject, asmt_year,
limit
"""
state_code = params.get(Constants.STATECODE)
district_id = params.get(Constants.DISTRICTGUID)
school_id = params.get(Constants.SCHOOLGUID)
asmt_grade = params.get(Constants.ASMTGRADE)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_year = params.get(Constants.ASMTYEAR)
asmt_subject = params.get(Constants.ASMTSUBJECT)
student = params.get(Constants.STUDENTGUID)
dim_student_label = get_column_mapping(Constants.DIM_STUDENT)
dim_inst_hier_label = get_column_mapping(Constants.DIM_INST_HIER)
dim_asmt_label = get_column_mapping(Constants.DIM_ASMT)
fact_asmt_outcome_vw_label = get_column_mapping(
Constants.FACT_ASMT_OUTCOME_VW)
with EdCoreDBConnection(state_code=state_code) as connector:
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
dim_inst_hier = connector.get_table(Constants.DIM_INST_HIER)
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
# TODO: Look at removing dim_asmt
query = select_with_context([
dim_asmt.c.asmt_guid.label(
dim_asmt_label.get(Constants.ASMT_GUID, Constants.ASMT_GUID)),
fact_asmt_outcome_vw.c.where_taken_id.label(
fact_asmt_outcome_vw_label.get('where_taken_id',
'guid_asmt_location')),
fact_asmt_outcome_vw.c.where_taken_name.label(
fact_asmt_outcome_vw_label.get('where_taken_name',
'name_asmt_location')),
fact_asmt_outcome_vw.c.asmt_grade.label(
fact_asmt_outcome_vw_label.get(Constants.ASMT_GRADE,
Constants.ASMT_GRADE)),
dim_inst_hier.c.state_code.label(
dim_inst_hier_label.get(Constants.STATE_CODE, 'code_state')),
dim_inst_hier.c.district_id.label(
dim_inst_hier_label.get(Constants.DISTRICT_ID,
'name_distrct')),
dim_inst_hier.c.district_name.label(
dim_inst_hier_label.get(Constants.DISTRICT_NAME,
'name_distrct')),
dim_inst_hier.c.school_id.label(
dim_inst_hier_label.get(Constants.SCHOOL_ID, 'guid_school')),
dim_inst_hier.c.school_name.label(
dim_inst_hier_label.get(Constants.SCHOOL_NAME, 'name_school')),
dim_student.c.student_id.label(
dim_student_label.get(Constants.STUDENT_ID, 'guid_student')),
dim_student.c.first_name.label(
dim_student_label.get('first_name', 'first_name')),
dim_student.c.middle_name.label(
dim_student_label.get('middle_name', 'middle_name')),
dim_student.c.last_name.label(
dim_student_label.get('last_name', 'last_name')),
dim_student.c.sex.label(dim_student_label.get('sex', 'sex')),
dim_student.c.birthdate.label(
dim_student_label.get('birthdate', 'dob')),
dim_student.c.external_student_id.label(
dim_student_label.get('external_student_id',
'external_student_id')),
fact_asmt_outcome_vw.c.enrl_grade.label(
fact_asmt_outcome_vw_label.get('enrl_grade',
'enrollment_grade')),
dim_student.c.group_1_id.label(
dim_student_label.get('group_1_id', 'group_1_id')),
dim_student.c.group_1_text.label(
dim_student_label.get('group_1_text', 'group_1_text')),
dim_student.c.group_2_id.label(
dim_student_label.get('group_2_id', 'group_2_id')),
dim_student.c.group_2_text.label(
dim_student_label.get('group_2_text', 'group_2_text')),
dim_student.c.group_3_id.label(
dim_student_label.get('group_3_id', 'group_3_id')),
dim_student.c.group_3_text.label(
dim_student_label.get('group_3_text', 'group_3_text')),
dim_student.c.group_4_id.label(
dim_student_label.get('group_4_id', 'group_4_id')),
dim_student.c.group_4_text.label(
dim_student_label.get('group_4_text', 'group_4_text')),
dim_student.c.group_5_id.label(
dim_student_label.get('group_5_id', 'group_5_id')),
dim_student.c.group_5_text.label(
dim_student_label.get('group_5_text', 'group_5_text')),
dim_student.c.group_6_id.label(
dim_student_label.get('group_6_id', 'group_6_id')),
dim_student.c.group_6_text.label(
dim_student_label.get('group_6_text', 'group_6_text')),
dim_student.c.group_7_id.label(
dim_student_label.get('group_7_id', 'group_7_id')),
dim_student.c.group_7_text.label(
dim_student_label.get('group_7_text', 'group_7_text')),
dim_student.c.group_8_id.label(
dim_student_label.get('group_8_id', 'group_8_id')),
dim_student.c.group_8_text.label(
dim_student_label.get('group_8_text', 'group_8_text')),
dim_student.c.group_9_id.label(
dim_student_label.get('group_9_id', 'group_9_id')),
dim_student.c.group_9_text.label(
dim_student_label.get('group_9_text', 'group_9_text')),
dim_student.c.group_10_id.label(
dim_student_label.get('group_10_id', 'group_10_id')),
dim_student.c.group_10_text.label(
dim_student_label.get('group_10_text', 'group_10_text')),
fact_asmt_outcome_vw.c.date_taken.label(
fact_asmt_outcome_vw_label.get('date_taken', 'date_taken')),
fact_asmt_outcome_vw.c.asmt_score.label(
fact_asmt_outcome_vw_label.get('asmt_score', 'asmt_score')),
fact_asmt_outcome_vw.c.asmt_score_range_min.label(
fact_asmt_outcome_vw_label.get('asmt_score_range_min',
'asmt_score_range_min')),
fact_asmt_outcome_vw.c.asmt_score_range_max.label(
fact_asmt_outcome_vw_label.get('asmt_score_range_max',
'asmt_score_range_max')),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label(
fact_asmt_outcome_vw_label.get('asmt_perf_lvl',
'asmt_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_1_score.label(
fact_asmt_outcome_vw_label.get('asmt_claim_1_score',
'asmt_claim_1_score')),
fact_asmt_outcome_vw.c.asmt_claim_1_perf_lvl.label(
fact_asmt_outcome_vw_label.get('asmt_claim_1_perf_lvl',
'asmt_claim_1_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_min.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_1_score_range_min',
'asmt_claim_1_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_max.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_1_score_range_max',
'asmt_claim_1_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_2_score.label(
fact_asmt_outcome_vw_label.get('asmt_claim_2_score',
'asmt_claim_2_score')),
fact_asmt_outcome_vw.c.asmt_claim_2_perf_lvl.label(
fact_asmt_outcome_vw_label.get('asmt_claim_2_perf_lvl',
'asmt_claim_2_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_min.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_2_score_range_min',
'asmt_claim_2_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_max.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_2_score_range_max',
'asmt_claim_2_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_3_score.label(
fact_asmt_outcome_vw_label.get('asmt_claim_3_score',
'asmt_claim_3_score')),
fact_asmt_outcome_vw.c.asmt_claim_3_perf_lvl.label(
fact_asmt_outcome_vw_label.get('asmt_claim_3_perf_lvl',
'asmt_claim_3_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_min.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_3_score_range_min',
'asmt_claim_3_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_max.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_3_score_range_max',
'asmt_claim_3_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_4_score.label(
fact_asmt_outcome_vw_label.get('asmt_claim_4_score',
'asmt_claim_4_score')),
fact_asmt_outcome_vw.c.asmt_claim_4_perf_lvl.label(
fact_asmt_outcome_vw_label.get('asmt_claim_4_perf_lvl',
'asmt_claim_4_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_min.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_4_score_range_min',
'asmt_claim_4_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_max.label(
fact_asmt_outcome_vw_label.get(
'asmt_claim_4_score_range_max',
'asmt_claim_4_score_range_max')),
fact_asmt_outcome_vw.c.dmg_eth_hsp.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_HSP,
Constants.DMG_ETH_HSP)),
fact_asmt_outcome_vw.c.dmg_eth_ami.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_AMI,
Constants.DMG_ETH_AMI)),
fact_asmt_outcome_vw.c.dmg_eth_asn.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_ASN,
Constants.DMG_ETH_ASN)),
fact_asmt_outcome_vw.c.dmg_eth_blk.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_BLK,
Constants.DMG_ETH_BLK)),
fact_asmt_outcome_vw.c.dmg_eth_pcf.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_PCF,
Constants.DMG_ETH_PCF)),
fact_asmt_outcome_vw.c.dmg_eth_wht.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_WHT,
Constants.DMG_ETH_WHT)),
fact_asmt_outcome_vw.c.dmg_eth_2om.label(
fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_2OM,
Constants.DMG_ETH_2OM)),
fact_asmt_outcome_vw.c.dmg_prg_iep.label(
fact_asmt_outcome_vw_label.get('dmg_prg_iep', 'dmg_prg_iep')),
fact_asmt_outcome_vw.c.dmg_prg_lep.label(
fact_asmt_outcome_vw_label.get('dmg_prg_lep', 'dmg_prg_lep')),
fact_asmt_outcome_vw.c.dmg_prg_504.label(
fact_asmt_outcome_vw_label.get('dmg_prg_504', 'dmg_prg_504')),
fact_asmt_outcome_vw.c.dmg_sts_ecd.label(
fact_asmt_outcome_vw_label.get('dmg_sts_ecd', 'dmg_sts_ecd')),
fact_asmt_outcome_vw.c.dmg_sts_mig.label(
fact_asmt_outcome_vw_label.get('dmg_sts_mig', 'dmg_sts_mig')),
fact_asmt_outcome_vw.c.asmt_type.label(
fact_asmt_outcome_vw_label.get(Constants.ASMT_TYPE,
Constants.ASMT_TYPE)),
fact_asmt_outcome_vw.c.asmt_year.label(
fact_asmt_outcome_vw_label.get(Constants.ASMT_YEAR,
Constants.ASMT_YEAR)),
fact_asmt_outcome_vw.c.asmt_subject.label(
fact_asmt_outcome_vw_label.get(Constants.ASMT_SUBJECT,
Constants.ASMT_SUBJECT)),
fact_asmt_outcome_vw.c.acc_asl_video_embed.label(
fact_asmt_outcome_vw_label.get('acc_asl_video_embed',
'acc_asl_video_embed')),
fact_asmt_outcome_vw.c.acc_noise_buffer_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_noise_buffer_nonembed',
'acc_noise_buffer_nonembed')),
fact_asmt_outcome_vw.c.acc_print_on_demand_items_nonembed.label(
fact_asmt_outcome_vw_label.get(
'acc_print_on_demand_items_nonembed',
'acc_print_on_demand_items_nonembed')),
fact_asmt_outcome_vw.c.acc_braile_embed.label(
fact_asmt_outcome_vw_label.get('acc_braile_embed',
'acc_braile_embed')),
fact_asmt_outcome_vw.c.acc_closed_captioning_embed.label(
fact_asmt_outcome_vw_label.get('acc_closed_captioning_embed',
'acc_closed_captioning_embed')),
fact_asmt_outcome_vw.c.acc_text_to_speech_embed.label(
fact_asmt_outcome_vw_label.get('acc_text_to_speech_embed',
'acc_text_to_speech_embed')),
fact_asmt_outcome_vw.c.acc_abacus_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_abacus_nonembed',
'acc_abacus_nonembed')),
fact_asmt_outcome_vw.c.acc_alternate_response_options_nonembed.
label(
fact_asmt_outcome_vw_label.get(
'acc_alternate_response_options_nonembed',
'acc_alternate_response_options_nonembed')),
fact_asmt_outcome_vw.c.acc_calculator_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_calculator_nonembed',
'acc_calculator_nonembed')),
fact_asmt_outcome_vw.c.acc_multiplication_table_nonembed.label(
fact_asmt_outcome_vw_label.get(
'acc_multiplication_table_nonembed',
'acc_multiplication_table_nonembed')),
fact_asmt_outcome_vw.c.acc_print_on_demand_nonembed.label(
fact_asmt_outcome_vw_label.get(
'acc_print_on_demand_nonembed',
'acc_print_on_demand_nonembed')),
fact_asmt_outcome_vw.c.acc_read_aloud_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_read_aloud_nonembed',
'acc_read_aloud_nonembed')),
fact_asmt_outcome_vw.c.acc_scribe_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_scribe_nonembed',
'acc_scribe_nonembed')),
fact_asmt_outcome_vw.c.acc_speech_to_text_nonembed.label(
fact_asmt_outcome_vw_label.get('acc_speech_to_text_nonembed',
'acc_speech_to_text_nonembed')),
fact_asmt_outcome_vw.c.acc_streamline_mode.label(
fact_asmt_outcome_vw_label.get('acc_streamline_mode',
'acc_streamline_mode')),
fact_asmt_outcome_vw.c.administration_condition.label(
fact_asmt_outcome_vw_label.get('administration_condition',
'administration_condition')),
case([(fact_asmt_outcome_vw.c.complete.is_(True), 'Complete'),
(fact_asmt_outcome_vw.c.complete.is_(False), 'Partial')],
else_=None).label(
fact_asmt_outcome_vw_label.get('complete', 'complete'))
],
from_obj=[
fact_asmt_outcome_vw.join(
dim_student,
and_(fact_asmt_outcome_vw.c.
student_rec_id ==
dim_student.c.student_rec_id)
).join(
dim_asmt,
and_(
dim_asmt.c.asmt_rec_id ==
fact_asmt_outcome_vw.c.
asmt_rec_id,
dim_asmt.c.asmt_type ==
asmt_type)).join(
dim_inst_hier,
and_(dim_inst_hier.c.
inst_hier_rec_id ==
fact_asmt_outcome_vw.
c.inst_hier_rec_id))
],
permission=RolesConstants.SAR_EXTRACTS,
state_code=state_code)
query = query.where(
and_(fact_asmt_outcome_vw.c.state_code == state_code))
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_type == asmt_type))
query = query.where(
and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
if school_id is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.school_id == school_id))
if district_id is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.district_id == district_id))
if asmt_year is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_year == asmt_year))
if asmt_subject is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_subject == asmt_subject))
if asmt_grade is not None:
query = query.where(
and_(fact_asmt_outcome_vw.c.asmt_grade == asmt_grade))
if student:
query = query.where(
and_(fact_asmt_outcome_vw.c.student_id.in_(student)))
query = apply_filter_to_query(query, fact_asmt_outcome_vw, dim_student,
params)
query = query.order_by(dim_student.c.last_name).order_by(
dim_student.c.first_name)
return query
def get_extract_assessment_query(params):
"""
private method to generate SQLAlchemy object or sql code for extraction
:param params: for query parameters asmt_type, asmt_subject, asmt_year,
limit
"""
state_code = params.get(Constants.STATECODE)
district_id = params.get(Constants.DISTRICTGUID)
school_id = params.get(Constants.SCHOOLGUID)
asmt_grade = params.get(Constants.ASMTGRADE)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_year = params.get(Constants.ASMTYEAR)
asmt_subject = params.get(Constants.ASMTSUBJECT)
student = params.get(Constants.STUDENTGUID)
dim_student_label = get_column_mapping(Constants.DIM_STUDENT)
dim_inst_hier_label = get_column_mapping(Constants.DIM_INST_HIER)
dim_asmt_label = get_column_mapping(Constants.DIM_ASMT)
fact_asmt_outcome_vw_label = get_column_mapping(
Constants.FACT_ASMT_OUTCOME_VW)
with EdCoreDBConnection(state_code=state_code) as connector:
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
dim_inst_hier = connector.get_table(Constants.DIM_INST_HIER)
fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
# TODO: Look at removing dim_asmt
query = select_with_context([
dim_asmt.c.asmt_guid.label(dim_asmt_label.get(Constants.ASMT_GUID, Constants.ASMT_GUID)),
fact_asmt_outcome_vw.c.where_taken_id.label(fact_asmt_outcome_vw_label.get('where_taken_id', 'guid_asmt_location')),
fact_asmt_outcome_vw.c.where_taken_name.label(fact_asmt_outcome_vw_label.get('where_taken_name', 'name_asmt_location')),
fact_asmt_outcome_vw.c.asmt_grade.label(fact_asmt_outcome_vw_label.get(Constants.ASMT_GRADE, Constants.ASMT_GRADE)),
dim_inst_hier.c.state_code.label(dim_inst_hier_label.get(Constants.STATE_CODE, 'code_state')),
dim_inst_hier.c.district_id.label(dim_inst_hier_label.get(Constants.DISTRICT_ID, 'name_distrct')),
dim_inst_hier.c.district_name.label(dim_inst_hier_label.get(Constants.DISTRICT_NAME, 'name_distrct')),
dim_inst_hier.c.school_id.label(dim_inst_hier_label.get(Constants.SCHOOL_ID, 'guid_school')),
dim_inst_hier.c.school_name.label(dim_inst_hier_label.get(Constants.SCHOOL_NAME, 'name_school')),
dim_student.c.student_id.label(dim_student_label.get(Constants.STUDENT_ID, 'guid_student')),
dim_student.c.first_name.label(dim_student_label.get('first_name', 'first_name')),
dim_student.c.middle_name.label(dim_student_label.get('middle_name', 'middle_name')),
dim_student.c.last_name.label(dim_student_label.get('last_name', 'last_name')),
dim_student.c.sex.label(dim_student_label.get('sex', 'sex')),
dim_student.c.birthdate.label(dim_student_label.get('birthdate', 'dob')),
dim_student.c.external_student_id.label(dim_student_label.get('external_student_id', 'external_student_id')),
fact_asmt_outcome_vw.c.enrl_grade.label(fact_asmt_outcome_vw_label.get('enrl_grade', 'enrollment_grade')),
dim_student.c.group_1_id.label(dim_student_label.get('group_1_id', 'group_1_id')),
dim_student.c.group_1_text.label(dim_student_label.get('group_1_text', 'group_1_text')),
dim_student.c.group_2_id.label(dim_student_label.get('group_2_id', 'group_2_id')),
dim_student.c.group_2_text.label(dim_student_label.get('group_2_text', 'group_2_text')),
dim_student.c.group_3_id.label(dim_student_label.get('group_3_id', 'group_3_id')),
dim_student.c.group_3_text.label(dim_student_label.get('group_3_text', 'group_3_text')),
dim_student.c.group_4_id.label(dim_student_label.get('group_4_id', 'group_4_id')),
dim_student.c.group_4_text.label(dim_student_label.get('group_4_text', 'group_4_text')),
dim_student.c.group_5_id.label(dim_student_label.get('group_5_id', 'group_5_id')),
dim_student.c.group_5_text.label(dim_student_label.get('group_5_text', 'group_5_text')),
dim_student.c.group_6_id.label(dim_student_label.get('group_6_id', 'group_6_id')),
dim_student.c.group_6_text.label(dim_student_label.get('group_6_text', 'group_6_text')),
dim_student.c.group_7_id.label(dim_student_label.get('group_7_id', 'group_7_id')),
dim_student.c.group_7_text.label(dim_student_label.get('group_7_text', 'group_7_text')),
dim_student.c.group_8_id.label(dim_student_label.get('group_8_id', 'group_8_id')),
dim_student.c.group_8_text.label(dim_student_label.get('group_8_text', 'group_8_text')),
dim_student.c.group_9_id.label(dim_student_label.get('group_9_id', 'group_9_id')),
dim_student.c.group_9_text.label(dim_student_label.get('group_9_text', 'group_9_text')),
dim_student.c.group_10_id.label(dim_student_label.get('group_10_id', 'group_10_id')),
dim_student.c.group_10_text.label(dim_student_label.get('group_10_text', 'group_10_text')),
fact_asmt_outcome_vw.c.date_taken.label(fact_asmt_outcome_vw_label.get('date_taken', 'date_taken')),
fact_asmt_outcome_vw.c.asmt_score.label(fact_asmt_outcome_vw_label.get('asmt_score', 'asmt_score')),
fact_asmt_outcome_vw.c.asmt_score_range_min.label(fact_asmt_outcome_vw_label.get('asmt_score_range_min', 'asmt_score_range_min')),
fact_asmt_outcome_vw.c.asmt_score_range_max.label(fact_asmt_outcome_vw_label.get('asmt_score_range_max', 'asmt_score_range_max')),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label(fact_asmt_outcome_vw_label.get('asmt_perf_lvl', 'asmt_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_1_score.label(fact_asmt_outcome_vw_label.get('asmt_claim_1_score', 'asmt_claim_1_score')),
fact_asmt_outcome_vw.c.asmt_claim_1_perf_lvl.label(fact_asmt_outcome_vw_label.get('asmt_claim_1_perf_lvl', 'asmt_claim_1_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_min.label(fact_asmt_outcome_vw_label.get('asmt_claim_1_score_range_min', 'asmt_claim_1_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_1_score_range_max.label(fact_asmt_outcome_vw_label.get('asmt_claim_1_score_range_max', 'asmt_claim_1_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_2_score.label(fact_asmt_outcome_vw_label.get('asmt_claim_2_score', 'asmt_claim_2_score')),
fact_asmt_outcome_vw.c.asmt_claim_2_perf_lvl.label(fact_asmt_outcome_vw_label.get('asmt_claim_2_perf_lvl', 'asmt_claim_2_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_min.label(fact_asmt_outcome_vw_label.get('asmt_claim_2_score_range_min', 'asmt_claim_2_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_2_score_range_max.label(fact_asmt_outcome_vw_label.get('asmt_claim_2_score_range_max', 'asmt_claim_2_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_3_score.label(fact_asmt_outcome_vw_label.get('asmt_claim_3_score', 'asmt_claim_3_score')),
fact_asmt_outcome_vw.c.asmt_claim_3_perf_lvl.label(fact_asmt_outcome_vw_label.get('asmt_claim_3_perf_lvl', 'asmt_claim_3_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_min.label(fact_asmt_outcome_vw_label.get('asmt_claim_3_score_range_min', 'asmt_claim_3_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_3_score_range_max.label(fact_asmt_outcome_vw_label.get('asmt_claim_3_score_range_max', 'asmt_claim_3_score_range_max')),
fact_asmt_outcome_vw.c.asmt_claim_4_score.label(fact_asmt_outcome_vw_label.get('asmt_claim_4_score', 'asmt_claim_4_score')),
fact_asmt_outcome_vw.c.asmt_claim_4_perf_lvl.label(fact_asmt_outcome_vw_label.get('asmt_claim_4_perf_lvl', 'asmt_claim_4_perf_lvl')),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_min.label(fact_asmt_outcome_vw_label.get('asmt_claim_4_score_range_min', 'asmt_claim_4_score_range_min')),
fact_asmt_outcome_vw.c.asmt_claim_4_score_range_max.label(fact_asmt_outcome_vw_label.get('asmt_claim_4_score_range_max', 'asmt_claim_4_score_range_max')),
fact_asmt_outcome_vw.c.dmg_eth_hsp.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_HSP, Constants.DMG_ETH_HSP)),
fact_asmt_outcome_vw.c.dmg_eth_ami.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_AMI, Constants.DMG_ETH_AMI)),
fact_asmt_outcome_vw.c.dmg_eth_asn.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_ASN, Constants.DMG_ETH_ASN)),
fact_asmt_outcome_vw.c.dmg_eth_blk.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_BLK, Constants.DMG_ETH_BLK)),
fact_asmt_outcome_vw.c.dmg_eth_pcf.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_PCF, Constants.DMG_ETH_PCF)),
fact_asmt_outcome_vw.c.dmg_eth_wht.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_WHT, Constants.DMG_ETH_WHT)),
fact_asmt_outcome_vw.c.dmg_eth_2om.label(fact_asmt_outcome_vw_label.get(Constants.DMG_ETH_2OM, Constants.DMG_ETH_2OM)),
fact_asmt_outcome_vw.c.dmg_prg_iep.label(fact_asmt_outcome_vw_label.get('dmg_prg_iep', 'dmg_prg_iep')),
fact_asmt_outcome_vw.c.dmg_prg_lep.label(fact_asmt_outcome_vw_label.get('dmg_prg_lep', 'dmg_prg_lep')),
fact_asmt_outcome_vw.c.dmg_prg_504.label(fact_asmt_outcome_vw_label.get('dmg_prg_504', 'dmg_prg_504')),
fact_asmt_outcome_vw.c.dmg_sts_ecd.label(fact_asmt_outcome_vw_label.get('dmg_sts_ecd', 'dmg_sts_ecd')),
fact_asmt_outcome_vw.c.dmg_sts_mig.label(fact_asmt_outcome_vw_label.get('dmg_sts_mig', 'dmg_sts_mig')),
fact_asmt_outcome_vw.c.asmt_type.label(fact_asmt_outcome_vw_label.get(Constants.ASMT_TYPE, Constants.ASMT_TYPE)),
fact_asmt_outcome_vw.c.asmt_year.label(fact_asmt_outcome_vw_label.get(Constants.ASMT_YEAR, Constants.ASMT_YEAR)),
fact_asmt_outcome_vw.c.asmt_subject.label(fact_asmt_outcome_vw_label.get(Constants.ASMT_SUBJECT, Constants.ASMT_SUBJECT)),
fact_asmt_outcome_vw.c.acc_asl_video_embed.label(fact_asmt_outcome_vw_label.get('acc_asl_video_embed', 'acc_asl_video_embed')),
fact_asmt_outcome_vw.c.acc_noise_buffer_nonembed.label(fact_asmt_outcome_vw_label.get('acc_noise_buffer_nonembed', 'acc_noise_buffer_nonembed')),
fact_asmt_outcome_vw.c.acc_print_on_demand_items_nonembed.label(fact_asmt_outcome_vw_label.get('acc_print_on_demand_items_nonembed', 'acc_print_on_demand_items_nonembed')),
fact_asmt_outcome_vw.c.acc_braile_embed.label(fact_asmt_outcome_vw_label.get('acc_braile_embed', 'acc_braile_embed')),
fact_asmt_outcome_vw.c.acc_closed_captioning_embed.label(fact_asmt_outcome_vw_label.get('acc_closed_captioning_embed', 'acc_closed_captioning_embed')),
fact_asmt_outcome_vw.c.acc_text_to_speech_embed.label(fact_asmt_outcome_vw_label.get('acc_text_to_speech_embed', 'acc_text_to_speech_embed')),
fact_asmt_outcome_vw.c.acc_abacus_nonembed.label(fact_asmt_outcome_vw_label.get('acc_abacus_nonembed', 'acc_abacus_nonembed')),
fact_asmt_outcome_vw.c.acc_alternate_response_options_nonembed.label(fact_asmt_outcome_vw_label.get('acc_alternate_response_options_nonembed', 'acc_alternate_response_options_nonembed')),
fact_asmt_outcome_vw.c.acc_calculator_nonembed.label(fact_asmt_outcome_vw_label.get('acc_calculator_nonembed', 'acc_calculator_nonembed')),
fact_asmt_outcome_vw.c.acc_multiplication_table_nonembed.label(fact_asmt_outcome_vw_label.get('acc_multiplication_table_nonembed', 'acc_multiplication_table_nonembed')),
fact_asmt_outcome_vw.c.acc_print_on_demand_nonembed.label(fact_asmt_outcome_vw_label.get('acc_print_on_demand_nonembed', 'acc_print_on_demand_nonembed')),
fact_asmt_outcome_vw.c.acc_read_aloud_nonembed.label(fact_asmt_outcome_vw_label.get('acc_read_aloud_nonembed', 'acc_read_aloud_nonembed')),
fact_asmt_outcome_vw.c.acc_scribe_nonembed.label(fact_asmt_outcome_vw_label.get('acc_scribe_nonembed', 'acc_scribe_nonembed')),
fact_asmt_outcome_vw.c.acc_speech_to_text_nonembed.label(fact_asmt_outcome_vw_label.get('acc_speech_to_text_nonembed', 'acc_speech_to_text_nonembed')),
fact_asmt_outcome_vw.c.acc_streamline_mode.label(fact_asmt_outcome_vw_label.get('acc_streamline_mode', 'acc_streamline_mode')),
fact_asmt_outcome_vw.c.administration_condition.label(fact_asmt_outcome_vw_label.get('administration_condition', 'administration_condition')),
case([
(fact_asmt_outcome_vw.c.complete.is_(True), 'Complete'),
(fact_asmt_outcome_vw.c.complete.is_(False), 'Partial')
], else_=None).label(fact_asmt_outcome_vw_label.get('complete', 'complete'))
], from_obj=[
fact_asmt_outcome_vw
.join(dim_student, and_(fact_asmt_outcome_vw.c.student_rec_id == dim_student.c.student_rec_id))
.join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_asmt_outcome_vw.c.asmt_rec_id, dim_asmt.c.asmt_type == asmt_type))
.join(dim_inst_hier, and_(dim_inst_hier.c.inst_hier_rec_id == fact_asmt_outcome_vw.c.inst_hier_rec_id))
], permission=RolesConstants.SAR_EXTRACTS, state_code=state_code)
query = query.where(and_(fact_asmt_outcome_vw.c.state_code == state_code))
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_type == asmt_type))
query = query.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
if school_id is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.school_id == school_id))
if district_id is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.district_id == district_id))
if asmt_year is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_year == asmt_year))
if asmt_subject is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_subject == asmt_subject))
if asmt_grade is not None:
query = query.where(and_(fact_asmt_outcome_vw.c.asmt_grade == asmt_grade))
if student:
query = query.where(and_(fact_asmt_outcome_vw.c.student_id.in_(student)))
query = apply_filter_to_query(
query, fact_asmt_outcome_vw, dim_student, params)
query = query.order_by(dim_student.c.last_name).order_by(
dim_student.c.first_name)
return query