def run_query(self, filters):
'''
Run comparing populations query and return the results
:rtype: dict
:returns: results from database
'''
with ReportingDbConnection(tenant=self.tenant,
state_code=self.state_code,
is_public=self.is_public) as connector:
query = self.get_query(connector, filters)
results = connector.get_result(query)
return results[0].get(Constants.COUNT) if results else 0
def run_query(self, **params):
'''
Run comparing populations query and return the results
:rtype: dict
:returns: results from database
'''
with ReportingDbConnection(tenant=self.tenant,
state_code=self.state_code,
is_public=self.is_public) as connector:
query_helper = QueryHelper(connector, **params)
query = query_helper.get_query()
results = connector.get_result(query)
return results
def get_asmt_academic_years(state_code,
tenant=None,
years_back=None,
is_public=False):
'''
Gets academic years.
'''
if not years_back or years_back <= 0:
years_back = DEFAULT_YEAR_BACK
with ReportingDbConnection(tenant=tenant,
state_code=state_code,
is_public=is_public) as connection:
dim_asmt = connection.get_table(Constants.DIM_ASMT)
query = select([dim_asmt.c.asmt_period_year]).distinct().order_by(
dim_asmt.c.asmt_period_year.desc())
results = connection.execute(query).fetchmany(size=years_back)
return list(r[Constants.ASMT_PERIOD_YEAR] for r in results)
def get_custom_metadata(state_code, tenant=None, is_public=False):
'''
Query assessment custom metadata from database
:param string stateCode: state code
:param string tenant: tenant info for database connection
:rtype: dict
:returns: dict of custom metadata with subject id as key and metadata as its value
'''
cstm_meta_map = {}
min_cell_size = None
branding = None
with ReportingDbConnection(tenant=tenant,
state_code=state_code,
is_public=is_public) as connector:
# query custom metadata by state code
dim_asmt_cstm = connector.get_table(Constants.CUSTOM_METADATA)
query = select([dim_asmt_cstm.c.asmt_custom_metadata.label(Constants.ASMT_CUSTOM_METADATA)],
from_obj=[dim_asmt_cstm])\
.where(dim_asmt_cstm.c.state_code == state_code)
results = connector.get_result(query)
if results:
result = results[0]
custom_metadata = result.get(Constants.ASMT_CUSTOM_METADATA)
if custom_metadata:
custom_metadata = json.loads(custom_metadata)
min_cell_size = custom_metadata.get(Constants.MIN_CELL_SIZE)
branding = custom_metadata.get(Constants.BRANDING)
subjects = custom_metadata.get(Constants.SUBJECTS)
if subjects:
for subject in subjects:
cstm_meta_map[subject] = subjects[subject]
# format by subject, we will always return a map of colors and minimum cell size
result = {Constants.BRANDING: branding}
subject_map = get_subjects_map()
for key, value in subject_map.items():
metadata = cstm_meta_map.get(key, {})
result[value] = {
Constants.COLORS: metadata.get(Constants.COLORS),
Constants.MIN_CELL_SIZE: min_cell_size
}
return result
def get_breadcrumbs_context(state_code=None,
district_id=None,
school_id=None,
asmt_grade=None,
student_name=None,
tenant=None,
is_public=False):
'''
Given certain known information, returns breadcrumbs context
It'll always return "home" breadcrumbs into results
'''
formatted_results = [{'type': 'home', 'name': 'Home'}]
results = None
if state_code:
with ReportingDbConnection(tenant=tenant,
state_code=state_code,
is_public=is_public) as connector:
dim_inst_hier = connector.get_table('dim_inst_hier')
# Limit result count to one
# We limit the results to one since we'll get multiple rows with the same values
# Think of the case of querying for state name and id, we'll get all the schools in that state
query = select([
dim_inst_hier.c.state_code.label(Constants.STATE_CODE),
dim_inst_hier.c.district_name.label(Constants.DISTRICT_NAME),
dim_inst_hier.c.school_name.label(Constants.SCHOOL_NAME)
],
from_obj=[dim_inst_hier],
limit=1)
query = query.where(
and_(dim_inst_hier.c.rec_status == Constants.CURRENT))
# Currently, we only have state_id from comparing population report
if state_code is not None:
query = query.where(
and_(dim_inst_hier.c.state_code == state_code))
if district_id is not None:
query = query.where(
and_(dim_inst_hier.c.district_id == district_id))
if school_id is not None:
query = query.where(
and_(dim_inst_hier.c.school_id == school_id))
# run it and format the results
results = connector.get_result(query)
if results:
result = results[0]
# return an hierarchical ordered list
formatted_results.append({
'type':
'state',
'name':
STATE_NAMES.get(result[Constants.STATE_CODE], 'Example State'),
'stateCode':
result[Constants.STATE_CODE],
'id':
result[Constants.STATE_CODE]
})
if district_id is not None:
formatted_results.append({
'type': 'district',
'name': result[Constants.DISTRICT_NAME],
'id': district_id
})
if school_id is not None:
formatted_results.append({
'type': 'school',
'name': result[Constants.SCHOOL_NAME],
'id': school_id
})
if asmt_grade is not None:
formatted_results.append({
'type': 'grade',
'name': asmt_grade,
'id': asmt_grade
})
if student_name is not None:
formatted_results.append({
'type': 'student',
'name': student_name
})
return {'items': formatted_results}
def test_public_connection(self):
PublicDBConnection.CONFIG_NAMESPACE = 'edware.db'
with ReportingDbConnection(tenant=get_unittest_tenant_name(),
state_code='NC',
is_public=True) as instance:
self.assertIsInstance(instance, PublicDBConnection)
def test_protected_connection(self):
with ReportingDbConnection(tenant=get_unittest_tenant_name(),
state_code='NC',
is_public=False) as instance:
self.assertIsInstance(instance, EdCoreDBConnection)
def _get_aggregate_dim_for_interim(stateCode=None,
districtId=None,
schoolId=None,
asmtYear=None,
tenant=None,
subject_key=None,
subject=None,
is_public=False):
'''
Query for institution or grades that have asmts for the year provided
:param string stateCode
:param string districtId
:param string schoolId
:param string asmtType
:param string asmtYear
:param string tenant: tenant info for database connection
:rtype: rset
:returns: set of records with district guids
'''
def create_where_clause(fact_table, asmt):
where = and_(
fact_table.c.asmt_year == asmtYear,
fact_table.c.state_code == stateCode,
fact_table.c.rec_status == 'C', fact_table.c.asmt_type == asmt,
fact_table.c.inst_hier_rec_id == dim_inst_hier.c.inst_hier_rec_id,
fact_table.c.asmt_subject == subject)
return where
rows = {}
with ReportingDbConnection(tenant=tenant,
state_code=stateCode,
is_public=is_public) as connector:
# query custom metadata by state code
dim_inst_hier = connector.get_table(Constants.DIM_INST_HIER)
fact_asmt_outcome = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
fact_block_asmt_outcome = connector.get_table(
Constants.FACT_BLOCK_ASMT_OUTCOME)
s_fao = exists(['*'], from_obj=[dim_inst_hier]).where(
create_where_clause(fact_asmt_outcome,
AssessmentType.INTERIM_COMPREHENSIVE))
s_fbao = exists(['*'], from_obj=[dim_inst_hier]).where(
create_where_clause(fact_block_asmt_outcome,
AssessmentType.INTERIM_ASSESSMENT_BLOCKS))
if districtId is None and schoolId is None:
query_fao = get_select_for_state_view(dim_inst_hier,
stateCode).where(s_fao)
query_fbao = get_select_for_state_view(dim_inst_hier,
stateCode).where(s_fbao)
query = query_fao.union(query_fbao)
elif districtId is not None and schoolId is not None:
fao_query = get_select_for_school_view(
fact_asmt_outcome, stateCode, districtId, schoolId, asmtYear,
AssessmentType.INTERIM_COMPREHENSIVE, subject)
fbao_query = get_select_for_school_view(
fact_block_asmt_outcome, stateCode, districtId, schoolId,
asmtYear, AssessmentType.INTERIM_ASSESSMENT_BLOCKS, subject)
query = fao_query.union(fbao_query)
else:
query_fao = get_select_for_district_view(dim_inst_hier, stateCode,
districtId).where(s_fao)
query_fbao = get_select_for_district_view(dim_inst_hier, stateCode,
districtId).where(s_fbao)
query = query_fao.union(query_fbao)
results = connector.get_result(query)
for result in results:
params = {
Constants.ID: result.get(Constants.ID),
Constants.STATECODE: stateCode
}
if districtId is not None:
params[Constants.DISTRICTGUID] = districtId
if schoolId is not None:
params[Constants.SCHOOLGUID] = schoolId
data = {
Constants.ID: result.get(Constants.ID),
Constants.ROWID: result.get(Constants.ID),
Constants.NAME: result.get(Constants.NAME),
Constants.PARAMS: params,
Constants.RESULTS: {}
}
data[Constants.RESULTS][subject_key] = {
Constants.ASMT_SUBJECT: subject,
Constants.TOTAL: -1,
Constants.HASINTERIM: True
}
rows[data[Constants.ID]] = data
return rows