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
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
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 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