def get_extract_assessment_item_and_raw_count_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context(
[func.count().label(Constants.COUNT)],
from_obj=[fact_asmt_outcome_vw],
permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return query
def get_extract_assessment_item_and_raw_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
# TODO: Look at removing dim_asmt
query = select_with_context([
fact_asmt_outcome_vw.c.state_code,
fact_asmt_outcome_vw.c.asmt_year,
fact_asmt_outcome_vw.c.asmt_type,
dim_asmt.c.effective_date,
fact_asmt_outcome_vw.c.asmt_subject,
fact_asmt_outcome_vw.c.asmt_grade,
fact_asmt_outcome_vw.c.district_id,
fact_asmt_outcome_vw.c.student_id
], from_obj=[
fact_asmt_outcome_vw.join(dim_asmt,
and_(dim_asmt.c.asmt_rec_id == fact_asmt_outcome_vw.c.asmt_rec_id))
], permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return query
def get_academic_year_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
academic_year_query = select_with_context([student_reg.c.state_code,
student_reg.c.state_name,
student_reg.c.district_id,
student_reg.c.district_name,
student_reg.c.school_id,
student_reg.c.school_name,
student_reg.c.sex,
student_reg.c.enrl_grade,
student_reg.c.dmg_eth_hsp,
student_reg.c.dmg_eth_ami,
student_reg.c.dmg_eth_asn,
student_reg.c.dmg_eth_blk,
student_reg.c.dmg_eth_pcf,
student_reg.c.dmg_eth_wht,
student_reg.c.dmg_prg_iep,
student_reg.c.dmg_prg_lep,
student_reg.c.dmg_prg_504,
student_reg.c.dmg_sts_ecd,
student_reg.c.dmg_sts_mig,
student_reg.c.dmg_multi_race,
student_reg.c.academic_year],
from_obj=[student_reg], permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.where(student_reg.c.academic_year == academic_year)
return academic_year_query
def get_extract_assessment_item_and_raw_count_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([
func.count().label(Constants.COUNT)],
from_obj=[fact_asmt_outcome_vw],
permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return query
def test_select_with_context_or_query(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.state_code],
from_obj=([fact_asmt_outcome_vw]), permission=RolesConstants.PII, state_code='NC')
query = query.where(and_(fact_asmt_outcome_vw.c.school_id == '242'))
results = connection.get_result(query)
self.assertEqual(len(results), 238)
def test_select_with_context_as_src(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.state_code],
from_obj=([fact_asmt_outcome_vw]), limit=1, permission=RolesConstants.SRC_EXTRACTS, state_code='NC')
results = connection.get_result(query.where(fact_asmt_outcome_vw.c.district_id == '228'))
self.assertEqual(len(results), 1)
self.assertEqual(results[0][Constants.STATE_CODE], 'NC')
def test_select_with_context_as_pii(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.school_id],
from_obj=([fact_asmt_outcome_vw]), limit=1, permission=RolesConstants.PII, state_code='NC')
results = connection.get_result(query)
self.assertEqual(len(results), 1)
self.assertIn(results[0][Constants.SCHOOL_ID], ['242', '245'])
def get_assessment_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
asmt_outcome = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
assmt_query = select_with_context([asmt_outcome.c.student_id,
asmt_outcome.c.asmt_subject,
asmt_outcome.c.asmt_type,
asmt_outcome.c.asmt_year],
from_obj=[asmt_outcome],
permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.distinct(asmt_outcome.c.student_id, asmt_outcome.c.asmt_subject, asmt_outcome.c.asmt_type)\
.where(and_(asmt_outcome.c.rec_status == Constants.CURRENT, asmt_outcome.c.asmt_year == academic_year))\
.alias(name=Constants.FACT_ASMT_OUTCOME_VW)
academic_year_query = select_with_context([student_reg.c.state_code,
student_reg.c.state_name,
student_reg.c.district_id,
student_reg.c.district_name,
student_reg.c.school_id,
student_reg.c.school_name,
student_reg.c.sex,
student_reg.c.enrl_grade,
student_reg.c.dmg_eth_hsp,
student_reg.c.dmg_eth_ami,
student_reg.c.dmg_eth_asn,
student_reg.c.dmg_eth_blk,
student_reg.c.dmg_eth_pcf,
student_reg.c.dmg_eth_wht,
student_reg.c.dmg_prg_iep,
student_reg.c.dmg_prg_lep,
student_reg.c.dmg_prg_504,
student_reg.c.dmg_sts_ecd,
student_reg.c.dmg_sts_mig,
student_reg.c.dmg_multi_race,
student_reg.c.academic_year,
assmt_query.c.asmt_subject,
assmt_query.c.asmt_type],
from_obj=[student_reg.join(assmt_query, student_reg.c.student_id == assmt_query.c.student_id)],
permission=RolesConstants.SRC_EXTRACTS,
state_code=state_code)\
.where(student_reg.c.academic_year == academic_year)
return academic_year_query
def test_select_with_context_as_pii(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.school_id],
from_obj=([fact_asmt_outcome_vw]),
limit=1,
permission=RolesConstants.PII,
state_code='NC')
results = connection.get_result(query)
self.assertEqual(len(results), 1)
self.assertIn(results[0][Constants.SCHOOL_ID], ['242', '245'])
def test_select_with_context_or_query(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.state_code],
from_obj=([fact_asmt_outcome_vw]),
permission=RolesConstants.PII,
state_code='NC')
query = query.where(
and_(fact_asmt_outcome_vw.c.school_id == '242'))
results = connection.get_result(query)
self.assertEqual(len(results), 238)
def test_select_with_context_as_srs(self):
with UnittestEdcoreDBConnection() as connection:
fact_asmt_outcome_vw = connection.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
query = select_with_context([fact_asmt_outcome_vw.c.state_code],
from_obj=([fact_asmt_outcome_vw]),
limit=1,
permission=RolesConstants.SRS_EXTRACTS,
state_code='NC')
results = connection.get_result(
query.where(fact_asmt_outcome_vw.c.district_id == '228'))
self.assertEqual(len(results), 1)
self.assertEqual(results[0][Constants.STATE_CODE], 'NC')
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 _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 get_match_id_query(academic_year, state_code):
with EdCoreDBConnection(state_code=state_code) as connection:
student_reg = connection.get_table(Constants.STUDENT_REG)
current_sr = student_reg.alias()
prev_sr = student_reg.alias()
match_id_query = select_with_context([current_sr.c.state_code, prev_sr.c.state_code.label('prev_state_code'), current_sr.c.state_name, current_sr.c.district_id,
prev_sr.c.district_id.label('prev_district_id'), current_sr.c.district_name, current_sr.c.school_id,
prev_sr.c.school_id.label('prev_school_id'), current_sr.c.school_name, current_sr.c.sex, prev_sr.c.sex.label('prev_sex'),
current_sr.c.enrl_grade, prev_sr.c.enrl_grade.label('prev_enrl_grade'), current_sr.c.dmg_eth_hsp, prev_sr.c.dmg_eth_hsp.label('prev_dmg_eth_hsp'),
current_sr.c.dmg_eth_ami, prev_sr.c.dmg_eth_ami.label('prev_dmg_eth_ami'), current_sr.c.dmg_eth_asn, prev_sr.c.dmg_eth_asn.label('prev_dmg_eth_asn'),
current_sr.c.dmg_eth_blk, prev_sr.c.dmg_eth_blk.label('prev_dmg_eth_blk'), current_sr.c.dmg_eth_pcf, prev_sr.c.dmg_eth_pcf.label('prev_dmg_eth_pcf'),
current_sr.c.dmg_eth_wht, prev_sr.c.dmg_eth_wht.label('prev_dmg_eth_wht'), current_sr.c.dmg_prg_iep,
prev_sr.c.dmg_prg_iep.label('prev_dmg_prg_iep'), current_sr.c.dmg_prg_lep, prev_sr.c.dmg_prg_lep.label('prev_dmg_prg_lep'),
current_sr.c.dmg_prg_504, prev_sr.c.dmg_prg_504.label('prev_dmg_prg_504'), current_sr.c.dmg_sts_ecd,
prev_sr.c.dmg_sts_ecd.label('prev_dmg_sts_ecd'), current_sr.c.dmg_sts_mig,
prev_sr.c.dmg_sts_mig.label('prev_dmg_sts_mig'), current_sr.c.dmg_multi_race, prev_sr.c.dmg_multi_race.label('prev_dmg_multi_race'), current_sr.c.academic_year],
from_obj=[current_sr.join(prev_sr, and_(current_sr.c.student_id == prev_sr.c.student_id))], permission=RolesConstants.SRS_EXTRACTS, state_code=state_code)\
.where(and_(current_sr.c.academic_year == academic_year, prev_sr.c.academic_year == academic_year - 1))
return match_id_query
def get_extract_assessment_item_and_raw_query(params, extract_type):
"""
private method to generate SQLAlchemy object or sql code for extraction of
students for item level/raw data
:param params: for query parameters asmt_year, asmt_type, asmt_subject,
asmt_grade
:param extract_type: Type of extraction: Item Level, Raw Data Level,
Student Assessment
"""
state_code = params.get(Constants.STATECODE)
with EdCoreDBConnection(state_code=state_code) as connector:
dim_asmt = connector.get_table(Constants.DIM_ASMT)
fact_asmt_outcome_vw = connector.get_table(
Constants.FACT_ASMT_OUTCOME_VW)
# TODO: Look at removing dim_asmt
query = select_with_context(
[
fact_asmt_outcome_vw.c.state_code,
fact_asmt_outcome_vw.c.asmt_year,
fact_asmt_outcome_vw.c.asmt_type, dim_asmt.c.effective_date,
fact_asmt_outcome_vw.c.asmt_subject,
fact_asmt_outcome_vw.c.asmt_grade,
fact_asmt_outcome_vw.c.district_id,
fact_asmt_outcome_vw.c.student_id
],
from_obj=[
fact_asmt_outcome_vw.join(
dim_asmt,
and_(dim_asmt.c.asmt_rec_id ==
fact_asmt_outcome_vw.c.asmt_rec_id))
],
permission=get_required_permission(extract_type),
state_code=state_code)
query = _assessment_item_and_raw_where_clause_builder(
query, fact_asmt_outcome_vw, params)
return 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_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 __prepare_query(connector, params):
'''
Returns query for individual student report
'''
assessment_guid = params.get(Constants.ASSESSMENTGUID)
student_id = params.get(Constants.STUDENTGUID)
state_code = params.get(Constants.STATECODE)
date_taken = params.get(Constants.DATETAKEN)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_year = params.get(Constants.ASMTYEAR)
fact_asmt_outcome_vw = connector.get_table('fact_asmt_outcome_vw')
dim_student = connector.get_table('dim_student')
dim_asmt = connector.get_table('dim_asmt')
query = select_with_context([
fact_asmt_outcome_vw.c.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.enrl_grade.label('grade'),
fact_asmt_outcome_vw.c.district_id.label('district_id'),
fact_asmt_outcome_vw.c.school_id.label('school_id'),
fact_asmt_outcome_vw.c.state_code.label('state_code'),
fact_asmt_outcome_vw.c.date_taken.label('date_taken'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
dim_asmt.c.asmt_period.label('asmt_period'),
dim_asmt.c.asmt_period_year.label('asmt_period_year'),
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_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"),
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_grade.label('asmt_grade'),
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.date_taken_day.label('date_taken_day'),
fact_asmt_outcome_vw.c.date_taken_month.label('date_taken_month'),
fact_asmt_outcome_vw.c.date_taken_year.label('date_taken_year'),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label('asmt_perf_lvl'),
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_claim_1_score_min.label('asmt_claim_1_score_min'),
dim_asmt.c.asmt_claim_2_score_min.label('asmt_claim_2_score_min'),
dim_asmt.c.asmt_claim_3_score_min.label('asmt_claim_3_score_min'),
dim_asmt.c.asmt_claim_4_score_min.label('asmt_claim_4_score_min'),
dim_asmt.c.asmt_claim_1_score_max.label('asmt_claim_1_score_max'),
dim_asmt.c.asmt_claim_2_score_max.label('asmt_claim_2_score_max'),
dim_asmt.c.asmt_claim_3_score_max.label('asmt_claim_3_score_max'),
dim_asmt.c.asmt_claim_4_score_max.label('asmt_claim_4_score_max'),
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'),
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.acc_asl_video_embed.label(
'acc_asl_video_embed'),
fact_asmt_outcome_vw.c.acc_noise_buffer_nonembed.label(
'acc_noise_buffer_nonembed'),
fact_asmt_outcome_vw.c.acc_print_on_demand_items_nonembed.label(
'acc_print_on_demand_items_nonembed'),
fact_asmt_outcome_vw.c.acc_braile_embed.label('acc_braile_embed'),
fact_asmt_outcome_vw.c.acc_closed_captioning_embed.label(
'acc_closed_captioning_embed'),
fact_asmt_outcome_vw.c.acc_text_to_speech_embed.label(
'acc_text_to_speech_embed'),
fact_asmt_outcome_vw.c.acc_abacus_nonembed.label(
'acc_abacus_nonembed'),
fact_asmt_outcome_vw.c.acc_alternate_response_options_nonembed.label(
'acc_alternate_response_options_nonembed'),
fact_asmt_outcome_vw.c.acc_calculator_nonembed.label(
'acc_calculator_nonembed'),
fact_asmt_outcome_vw.c.acc_multiplication_table_nonembed.label(
'acc_multiplication_table_nonembed'),
fact_asmt_outcome_vw.c.acc_print_on_demand_nonembed.label(
'acc_print_on_demand_nonembed'),
fact_asmt_outcome_vw.c.acc_read_aloud_nonembed.label(
'acc_read_aloud_nonembed'),
fact_asmt_outcome_vw.c.acc_scribe_nonembed.label(
'acc_scribe_nonembed'),
fact_asmt_outcome_vw.c.acc_speech_to_text_nonembed.label(
'acc_speech_to_text_nonembed'),
fact_asmt_outcome_vw.c.acc_streamline_mode.label(
'acc_streamline_mode'),
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=state_code)
query = query\
.where(
and_(
fact_asmt_outcome_vw.c.student_id == student_id,
fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
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]))))))
if assessment_guid is not None:
query = query.where(dim_asmt.c.asmt_guid == assessment_guid)
if date_taken is not None:
query = query.where(
fact_asmt_outcome_vw.c.date_taken == str(date_taken))
if asmt_type is not None:
query = query.where(dim_asmt.c.asmt_type == asmt_type)
if asmt_year is not None:
query = query.where(fact_asmt_outcome_vw.c.asmt_year == asmt_year)
query = query.order_by(dim_asmt.c.asmt_subject.desc(),
dim_asmt.c.asmt_period_year.desc())
return query
def __prepare_query_iab(connector, params):
'''
Returns query for individual student report for IAB
'''
assessment_guid = params.get(Constants.ASSESSMENTGUID)
asmt_year = params.get(Constants.ASMTYEAR)
student_id = params.get(Constants.STUDENTGUID)
state_code = params.get(Constants.STATECODE)
fact_block_asmt_outcome = connector.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
query = select_with_context(
[
fact_block_asmt_outcome.c.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_block_asmt_outcome.c.enrl_grade.label('enrl_grade'),
fact_block_asmt_outcome.c.district_id.label('district_id'),
fact_block_asmt_outcome.c.school_id.label('school_id'),
fact_block_asmt_outcome.c.state_code.label('state_code'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
dim_asmt.c.asmt_period.label('asmt_period'),
dim_asmt.c.asmt_period_year.label('asmt_period_year'),
fact_block_asmt_outcome.c.date_taken.label('date_taken'),
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_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"),
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_block_asmt_outcome.c.asmt_grade.label('asmt_grade'),
fact_block_asmt_outcome.c.date_taken_day.label('date_taken_day'),
fact_block_asmt_outcome.c.date_taken_month.label(
'date_taken_month'),
fact_block_asmt_outcome.c.date_taken_year.label('date_taken_year'),
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_claim_1_score_min.label('asmt_claim_1_score_min'),
dim_asmt.c.asmt_claim_2_score_min.label('asmt_claim_2_score_min'),
dim_asmt.c.asmt_claim_3_score_min.label('asmt_claim_3_score_min'),
dim_asmt.c.asmt_claim_4_score_min.label('asmt_claim_4_score_min'),
dim_asmt.c.asmt_claim_1_score_max.label('asmt_claim_1_score_max'),
dim_asmt.c.asmt_claim_2_score_max.label('asmt_claim_2_score_max'),
dim_asmt.c.asmt_claim_3_score_max.label('asmt_claim_3_score_max'),
dim_asmt.c.asmt_claim_4_score_max.label('asmt_claim_4_score_max'),
fact_block_asmt_outcome.c.asmt_claim_1_score.label(
'asmt_claim_1_score'),
fact_block_asmt_outcome.c.asmt_claim_1_score_range_min.label(
'asmt_claim_1_score_range_min'),
fact_block_asmt_outcome.c.asmt_claim_1_score_range_max.label(
'asmt_claim_1_score_range_max'),
fact_block_asmt_outcome.c.asmt_claim_1_perf_lvl.label(
'asmt_claim_1_perf_lvl'),
fact_block_asmt_outcome.c.administration_condition.label(
'administration_condition'),
func.coalesce(fact_block_asmt_outcome.c.complete,
True).label('complete')
],
from_obj=[
fact_block_asmt_outcome.
join(
dim_student,
and_(fact_block_asmt_outcome.c.student_rec_id ==
dim_student.c.student_rec_id)).join(
dim_asmt,
and_(dim_asmt.c.asmt_rec_id ==
fact_block_asmt_outcome.c.asmt_rec_id))
],
permission=RolesConstants.PII,
state_code=state_code)
query = query.where(
and_(fact_block_asmt_outcome.c.student_id == student_id,
fact_block_asmt_outcome.c.rec_status == Constants.CURRENT,
dim_asmt.c.asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS))
query = query.where(
and_(
or_(
fact_block_asmt_outcome.c.administration_condition == null(),
fact_block_asmt_outcome.c.administration_condition.in_([
Constants.ADMINISTRATION_CONDITION_STANDARDIZED,
Constants.ADMINISTRATION_CONDITION_NON_STANDARDIZED
]))))
if assessment_guid is not None:
query = query.where(dim_asmt.c.asmt_guid == assessment_guid)
if asmt_year is not None:
query = query.where(fact_block_asmt_outcome.c.asmt_year == asmt_year)
query = query.order_by(dim_asmt.c.asmt_subject.desc(),
fact_block_asmt_outcome.c.asmt_grade.desc(),
fact_block_asmt_outcome.c.date_taken.desc())
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 __prepare_query_iab(connector, params):
'''
Returns query for individual student report for IAB
'''
assessment_guid = params.get(Constants.ASSESSMENTGUID)
asmt_year = params.get(Constants.ASMTYEAR)
student_id = params.get(Constants.STUDENTGUID)
state_code = params.get(Constants.STATECODE)
fact_block_asmt_outcome = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
dim_student = connector.get_table(Constants.DIM_STUDENT)
dim_asmt = connector.get_table(Constants.DIM_ASMT)
query = select_with_context([fact_block_asmt_outcome.c.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_block_asmt_outcome.c.enrl_grade.label('enrl_grade'),
fact_block_asmt_outcome.c.district_id.label('district_id'),
fact_block_asmt_outcome.c.school_id.label('school_id'),
fact_block_asmt_outcome.c.state_code.label('state_code'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
dim_asmt.c.asmt_period.label('asmt_period'),
dim_asmt.c.asmt_period_year.label('asmt_period_year'),
fact_block_asmt_outcome.c.date_taken.label('date_taken'),
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_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"),
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_block_asmt_outcome.c.asmt_grade.label('asmt_grade'),
fact_block_asmt_outcome.c.date_taken_day.label('date_taken_day'),
fact_block_asmt_outcome.c.date_taken_month.label('date_taken_month'),
fact_block_asmt_outcome.c.date_taken_year.label('date_taken_year'),
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_claim_1_score_min.label('asmt_claim_1_score_min'),
dim_asmt.c.asmt_claim_2_score_min.label('asmt_claim_2_score_min'),
dim_asmt.c.asmt_claim_3_score_min.label('asmt_claim_3_score_min'),
dim_asmt.c.asmt_claim_4_score_min.label('asmt_claim_4_score_min'),
dim_asmt.c.asmt_claim_1_score_max.label('asmt_claim_1_score_max'),
dim_asmt.c.asmt_claim_2_score_max.label('asmt_claim_2_score_max'),
dim_asmt.c.asmt_claim_3_score_max.label('asmt_claim_3_score_max'),
dim_asmt.c.asmt_claim_4_score_max.label('asmt_claim_4_score_max'),
fact_block_asmt_outcome.c.asmt_claim_1_score.label('asmt_claim_1_score'),
fact_block_asmt_outcome.c.asmt_claim_1_score_range_min.label('asmt_claim_1_score_range_min'),
fact_block_asmt_outcome.c.asmt_claim_1_score_range_max.label('asmt_claim_1_score_range_max'),
fact_block_asmt_outcome.c.asmt_claim_1_perf_lvl.label('asmt_claim_1_perf_lvl'),
fact_block_asmt_outcome.c.administration_condition.label('administration_condition'),
func.coalesce(fact_block_asmt_outcome.c.complete, True).label('complete')],
from_obj=[fact_block_asmt_outcome
.join(dim_student, and_(fact_block_asmt_outcome.c.student_rec_id == dim_student.c.student_rec_id))
.join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_block_asmt_outcome.c.asmt_rec_id))], permission=RolesConstants.PII, state_code=state_code)
query = query.where(and_(fact_block_asmt_outcome.c.student_id == student_id, fact_block_asmt_outcome.c.rec_status == Constants.CURRENT, dim_asmt.c.asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS))
query = query.where(and_(or_(fact_block_asmt_outcome.c.administration_condition == null(), fact_block_asmt_outcome.c.administration_condition.in_([Constants.ADMINISTRATION_CONDITION_STANDARDIZED,
Constants.ADMINISTRATION_CONDITION_NON_STANDARDIZED]))))
if assessment_guid is not None:
query = query.where(dim_asmt.c.asmt_guid == assessment_guid)
if asmt_year is not None:
query = query.where(fact_block_asmt_outcome.c.asmt_year == asmt_year)
query = query.order_by(dim_asmt.c.asmt_subject.desc(), fact_block_asmt_outcome.c.asmt_grade.desc(), fact_block_asmt_outcome.c.date_taken.desc())
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
def __prepare_query(connector, params):
'''
Returns query for individual student report
'''
assessment_guid = params.get(Constants.ASSESSMENTGUID)
student_id = params.get(Constants.STUDENTGUID)
state_code = params.get(Constants.STATECODE)
date_taken = params.get(Constants.DATETAKEN)
asmt_type = params.get(Constants.ASMTTYPE)
asmt_year = params.get(Constants.ASMTYEAR)
fact_asmt_outcome_vw = connector.get_table('fact_asmt_outcome_vw')
dim_student = connector.get_table('dim_student')
dim_asmt = connector.get_table('dim_asmt')
query = select_with_context([
fact_asmt_outcome_vw.c.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.enrl_grade.label('grade'),
fact_asmt_outcome_vw.c.district_id.label('district_id'),
fact_asmt_outcome_vw.c.school_id.label('school_id'),
fact_asmt_outcome_vw.c.state_code.label('state_code'),
fact_asmt_outcome_vw.c.date_taken.label('date_taken'),
dim_asmt.c.asmt_subject.label('asmt_subject'),
dim_asmt.c.asmt_period.label('asmt_period'),
dim_asmt.c.asmt_period_year.label('asmt_period_year'),
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_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"),
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_grade.label('asmt_grade'),
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.date_taken_day.label('date_taken_day'),
fact_asmt_outcome_vw.c.date_taken_month.label('date_taken_month'),
fact_asmt_outcome_vw.c.date_taken_year.label('date_taken_year'),
fact_asmt_outcome_vw.c.asmt_perf_lvl.label('asmt_perf_lvl'),
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_claim_1_score_min.label('asmt_claim_1_score_min'),
dim_asmt.c.asmt_claim_2_score_min.label('asmt_claim_2_score_min'),
dim_asmt.c.asmt_claim_3_score_min.label('asmt_claim_3_score_min'),
dim_asmt.c.asmt_claim_4_score_min.label('asmt_claim_4_score_min'),
dim_asmt.c.asmt_claim_1_score_max.label('asmt_claim_1_score_max'),
dim_asmt.c.asmt_claim_2_score_max.label('asmt_claim_2_score_max'),
dim_asmt.c.asmt_claim_3_score_max.label('asmt_claim_3_score_max'),
dim_asmt.c.asmt_claim_4_score_max.label('asmt_claim_4_score_max'),
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'),
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.acc_asl_video_embed.label('acc_asl_video_embed'),
fact_asmt_outcome_vw.c.acc_noise_buffer_nonembed.label('acc_noise_buffer_nonembed'),
fact_asmt_outcome_vw.c.acc_print_on_demand_items_nonembed.label('acc_print_on_demand_items_nonembed'),
fact_asmt_outcome_vw.c.acc_braile_embed.label('acc_braile_embed'),
fact_asmt_outcome_vw.c.acc_closed_captioning_embed.label('acc_closed_captioning_embed'),
fact_asmt_outcome_vw.c.acc_text_to_speech_embed.label('acc_text_to_speech_embed'),
fact_asmt_outcome_vw.c.acc_abacus_nonembed.label('acc_abacus_nonembed'),
fact_asmt_outcome_vw.c.acc_alternate_response_options_nonembed.label('acc_alternate_response_options_nonembed'),
fact_asmt_outcome_vw.c.acc_calculator_nonembed.label('acc_calculator_nonembed'),
fact_asmt_outcome_vw.c.acc_multiplication_table_nonembed.label('acc_multiplication_table_nonembed'),
fact_asmt_outcome_vw.c.acc_print_on_demand_nonembed.label('acc_print_on_demand_nonembed'),
fact_asmt_outcome_vw.c.acc_read_aloud_nonembed.label('acc_read_aloud_nonembed'),
fact_asmt_outcome_vw.c.acc_scribe_nonembed.label('acc_scribe_nonembed'),
fact_asmt_outcome_vw.c.acc_speech_to_text_nonembed.label('acc_speech_to_text_nonembed'),
fact_asmt_outcome_vw.c.acc_streamline_mode.label('acc_streamline_mode'),
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=state_code)
query = query\
.where(
and_(
fact_asmt_outcome_vw.c.student_id == student_id,
fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
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]))))))
if assessment_guid is not None:
query = query.where(dim_asmt.c.asmt_guid == assessment_guid)
if date_taken is not None:
query = query.where(fact_asmt_outcome_vw.c.date_taken == str(date_taken))
if asmt_type is not None:
query = query.where(dim_asmt.c.asmt_type == asmt_type)
if asmt_year is not None:
query = query.where(fact_asmt_outcome_vw.c.asmt_year == asmt_year)
query = query.order_by(dim_asmt.c.asmt_subject.desc(), dim_asmt.c.asmt_period_year.desc())
return query
