def getPreparedLectureResults(self, lecture_results):
results = AutoVivification()
for res in lecture_results:
results[res.student_id][res.Exam.id] = res.points
for exam in self.exams:
for student_results in list(results.values()):
student_results[exam.category] = student_results.get(exam.category,0)+(student_results.get(exam.id,0) or 0)
return results
def getStatistics(self,
tutorials=None,
students=None,
statistics=None,
prefix='lec'):
if statistics == None:
statistics = AutoVivification()
session = Session.object_session(self)
if not students:
students = self.lecture.lecture_students_for_tutorials(
tutorials).all()
pointsQuery = self.exercise_points.filter(ExerciseStudent.student_id.in_([s.student_id for s in students]))\
.filter(ExerciseStudent.points!=None)
pointsStmt = pointsQuery.subquery()
exerciseStatistics = session.query(\
pointsStmt.c.exercise.label('exercise_id'),
func.count(pointsStmt.c.student).label('count'),
func.avg(pointsStmt.c.points).label('avg'),
func.variance(pointsStmt.c.points).label('variance')
).group_by(pointsStmt.c.exercise)
examPoints = session.query(\
pointsStmt.c.student.label('student_id'),
func.sum(pointsStmt.c.points).label('points'),
).group_by(pointsStmt.c.student).subquery()
examStatistics = session.query(\
func.count(examPoints.c.student_id).label('count'),
func.avg(examPoints.c.points).label('avg'),
func.variance(examPoints.c.points).label('variance'),
).one()
statistics['exam'] = {
prefix + '_avg':
examStatistics.avg,
prefix + '_std':
math.sqrt(examStatistics.variance)
if examStatistics.variance else None,
prefix + '_count':
examStatistics.count,
'max':
self.getMaxpoints()
}
for e in self.exercises:
statistics[e.id] = {
prefix + '_avg': None,
prefix + '_std': None,
prefix + '_count': 0,
'max': e.maxpoints
}
for e in exerciseStatistics.all():
statistics[e.exercise_id] = {
prefix + '_avg': e.avg,
prefix + '_std': math.sqrt(e.variance) if e.variance else None,
prefix + '_count': e.count
}
return statistics
def getStatisticsBySubjects(self,
tutorials=None,
students=None,
statistics=None,
prefix='lec'):
session = Session.object_session(self)
if not students:
students = self.lecture.lecture_students_for_tutorials(tutorials)
exercise_points = session.query(ExerciseStudent,
ExerciseStudent.student)
pointsQuery = self.exercise_points.filter(ExerciseStudent.student_id.in_([s.student_id for s in students]))\
.filter(ExerciseStudent.exercise_id.in_([e.id for e in self.exercises]))\
.filter(ExerciseStudent.points!=None).subquery()
pointsStmt = session.query(User.subject, pointsQuery).join(
pointsQuery, pointsQuery.c.student == User.id).subquery()
exerciseStatistics = session.query(\
pointsStmt.c.exercise.label('exercise_id'),
pointsStmt.c.subject.label('subject'),
func.count(pointsStmt.c.student).label('count'),
func.avg(pointsStmt.c.points).label('avg'),
func.variance(pointsStmt.c.points).label('variance')
).group_by(pointsStmt.c.exercise, pointsStmt.c.subject).all()
examPoints = session.query(\
pointsStmt.c.student.label('student_id'),
pointsStmt.c.subject.label('subject'),
func.sum(pointsStmt.c.points).label('points'),
).group_by(pointsStmt.c.student, pointsStmt.c.subject).subquery()
examStatistics = session.query(\
examPoints.c.subject.label('subject'),
func.count(examPoints.c.student_id).label('count'),
func.avg(examPoints.c.points).label('avg'),
func.variance(examPoints.c.points).label('variance'),
).group_by(examPoints.c.subject).all()
if statistics == None:
statistics = AutoVivification()
maxpoints = self.getMaxpoints()
for res in examStatistics:
statistics[res.subject]['exam'] = {
prefix + '_avg': res.avg,
prefix + '_std':
math.sqrt(res.variance) if res.variance else None,
prefix + '_count': res.count,
'max': maxpoints
}
for e in exerciseStatistics:
statistics[e.subject][e.exercise_id] = {
prefix + '_avg': e.avg,
prefix + '_std': math.sqrt(e.variance) if e.variance else None,
prefix + '_count': e.count,
'max': session.query(Exercise).get(e.exercise_id).maxpoints
}
return statistics