def get(self):
args = self.reqparse.parse_args()
regex_id = args['regex_id']
post = Regex.query.outerjoin(
Rating, Regex.id == Rating.regex_id).add_columns(
func.count(Rating.regex_id).label('views'),
func.avg(func.coalesce(
Rating.mark, 0)).label('avgmark')).filter(
Regex.id == regex_id).group_by(Regex.id).order_by(
func.count(Rating.regex_id).desc(),
func.avg(func.coalesce(Rating.mark,
0)).desc()).first()
return post[0].to_dict(views=post[1], avgmark=float(post[2])), 200
def get(self):
args = self.reqparse.parse_args()
user_id = args['user_id']
views = func.count(Rating.regex_id).label('views')
avgmark = func.avg(func.coalesce(Rating.mark, 0)).label('avgmark')
posts = db.session.query(Regex, views, avgmark).outerjoin(
Rating, Regex.id == Rating.regex_id).group_by(Regex.id).subquery()
user_posts = db.session.query(Rating.mark, posts).join(
posts, posts.c.id == Rating.regex_id).filter(
Rating.user_id == user_id).order_by(desc(
posts.c.views), desc(posts.c.avgmark)).all()
return [{
'id': regex_id,
'expression': regex_expression,
'explanation': regex_explanation,
'date': regex_date,
'author_id': regex_author_id,
'views': regex_views,
'avg_mark': regex_avgmark,
'user_mark': user_mark
} for user_mark, regex_id, regex_expression, regex_explanation,
regex_date, regex_author_id, regex_views, regex_avgmark in
user_posts]
def post(self):
args = self.reqparse.parse_args()
regex = args['regex']
posts = Regex.query.outerjoin(
Rating, Regex.id == Rating.regex_id
).add_columns(
func.count(Rating.regex_id).label('views'), func.avg(func.coalesce(Rating.mark, 0)).label('avgmark')
).filter(
Regex.expression.like(f'{regex}%')
).group_by(
Regex.id
).order_by(
func.count(Rating.regex_id).desc(), func.avg(func.coalesce(Rating.mark, 0)).desc()
).all()
return [post.to_dict(views=views, avg_mark=float(avgmark)) for post, views, avgmark in posts], 200
def get(self):
args = self.reqparse.parse_args()
limit_by, offset, author_id = args['limit_by'], args['offset'], args['author_id']
u = User.query.get_or_404(author_id)
posts = Regex.query.outerjoin(
Rating, Regex.id == Rating.regex_id
).add_columns(
func.count(Rating.regex_id).label('views'), func.avg(func.coalesce(Rating.mark, 0)).label('avgmark')
).filter(
Regex.author_id == u.id
).group_by(
Regex.id
).order_by(
func.count(Rating.regex_id).desc(), func.avg(func.coalesce(Rating.mark, 0)).desc()
).all()
return [post.to_dict(views=views, avg_mark=float(avgmark)) for post, views, avgmark in posts], 200
def get(self):
args = self.reqparse.parse_args()
limit_by, offset = args['limit_by'], args['offset']
posts = Regex.query.outerjoin(
Rating, Regex.id == Rating.regex_id).add_columns(
func.count(Rating.regex_id).label('views'),
func.avg(func.coalesce(
Rating.mark,
0)).label('avgmark')).group_by(Regex.id).order_by(
func.count(Rating.regex_id).desc(),
func.avg(func.coalesce(
Rating.mark,
0)).desc()).limit(limit_by).offset(0 + limit_by *
offset).all()
return [
post.to_dict(views=views, avgmark=float(avgmark))
for post, views, avgmark in posts
], 200
def add(money):
# sql = select([select([func.coalesce(func.sum(Transaction.money), 0) + money]).alias('tmp')])
# sql = select([func.sum(Transaction.money).label('g')])
Tr = aliased(Transaction, name="tr")
sql = select([func.coalesce(func.sum(Tr.balance), 0) + money])
# sql = sa_session.query(func.sum(Transaction.money))
# .as_scalar()
# .label('aa')
# print dir(sql)
# print 'sql', sql
tr = Transaction(money=money)
tr.balance = sql
print "tr.created_at0", tr.created_at
# tr.moeny = 4294967295
# tr.balance = -1
sa_session.add(tr)
print "tr.created_at1", tr.created_at
sa_session.commit()
print "tr.created_at2", tr.created_at
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(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)