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