class TimePreference(Base):
__tablename__ = 'time_preferences'
lecture_id = Column('lecture',
Integer,
ForeignKey(Lecture.id),
primary_key=True)
lecture = relationship(Lecture,
backref=backref('time_preferences', lazy='dynamic'))
student_id = Column('student',
Integer,
ForeignKey(User.id),
primary_key=True)
student = relationship(User, backref='time_preferences')
time = Column(ColumnWrapper(TutorialTime)(length=7), primary_key=True)
penalty = Column(Integer)
def __init__(self,
lecture=None,
student=None,
time=None,
penalty=None,
primary_key=None):
if primary_key:
self.lecture_id = primary_key[0]
self.student_id = primary_key[1]
self.time = primary_key[2]
else:
self.lecture = lecture
self.student = student
self.time = time
self.penalty = penalty
class Tutorial(Base):
__tablename__ = 'tutorials'
id = Column(Integer, primary_key=True)
lecture_id = Column('lecture', Integer, ForeignKey(Lecture.id))
lecture = relationship(Lecture,
backref=backref('tutorials_q', lazy='dynamic'))
tutor_id = Column('tutor', Integer, ForeignKey(User.id))
tutor = relationship(User, lazy='joined')
place = Column(Text)
max_students = Column(Integer, nullable=False, default=0)
comment = Column(Text)
@property
def students(self):
session = Session.object_session(self)
return session.query(User).filter(
User.lecture_students.any(LectureStudent.tutorial == self))
@property
def tutor_name(self):
if self.tutor:
return self.tutor.last_name
else:
return '-'
# weekday and time of tutorial
# format: "D HH:MM"
# where
# D - day: 0..6, where 0 is Monday
# HH - hour
# MM - minute
time = Column(ColumnWrapper(TutorialTime)(length=7))
date = Column(Date)
is_special = Column(Boolean, nullable=False, default=False)
class TimePreference(Base):
__tablename__ = 'time_preferences'
lecture_id = Column('lecture',
Integer,
ForeignKey(Lecture.id),
primary_key=True)
lecture = relationship(Lecture,
backref=backref('time_preferences', lazy='dynamic'))
student_id = Column('student',
Integer,
ForeignKey(User.id),
primary_key=True)
student = relationship(User, backref='time_preferences')
time = Column(ColumnWrapper(TutorialTime)(length=7), primary_key=True)
penalty = Column(Integer)
class Lecture(Base):
__tablename__ = 'lectures'
id = Column(Integer, primary_key=True)
# Should be removed some day. Stays here just to make muesli3 work.
assistant_id = Column('assistant', Integer,
ForeignKey(User.id, ondelete='SET NULL'))
old_assistant = relationship(User,
backref=backref('lectures_as_assistant_old',
order_by='Lecture.term',
lazy='dynamic'))
assistants = relationship(User,
secondary=lecture_assistants_table,
backref=backref("lectures_as_assistant",
order_by='Lecture.term',
lazy='dynamic'))
name = Column(Text)
# lecture type
# 'lecture'
# 'seminar'
type = Column(Text, nullable=False, default='lecture')
# term
# Format: yyyyt
# where "yyyy" is the year the term starts and "t" is "1" for summer term
# and "2" for winter term
term = Column(ColumnWrapper(Term)(length=5))
# lecture id used in LSF.
lsf_id = Column(Text)
lecturer = Column(Text)
url = Column(Text)
password = Column(Text)
is_visible = Column(Boolean, nullable=False, default=True)
# tutorial subscription mode:
# 'off' - no subscription allowed
# 'direct' - direct subscription to tutorials
# 'prefs' - preferences based subscription to tutorials
# 'static' - no subscription, no unsubscription
mode = Column(Text, nullable=False, default='off')
minimum_preferences = Column(Integer, default=None)
tutor_rights = Column(Text, nullable=False, default=editOwnTutorials)
tutorials = relationship('Tutorial',
order_by='Tutorial.time,Tutorial.comment')
tutors = relationship(User,
secondary=lecture_tutors_table,
backref="lectures_as_tutor")
@property
def students(self):
session = Session.object_session(self)
return session.query(User).filter(
User.lecture_students.any(LectureStudent.lecture_id == self.id))
def lecture_students_for_tutorials(self, tutorials=[], order=True):
ls = self.lecture_students
if order:
ls = ls.join(LectureStudent.student).order_by(
User.last_name, User.first_name)
if tutorials:
ls = ls.filter(
LectureStudent.tutorial_id.in_([tut.id for tut in tutorials])
) # sqlalchemy.or_(*[LectureStudent.tutorial_id==tut.id for tut in tutorials]))
return ls
# @property
# def tutors(self):
# session = Session.object_session(self)
# return session.query(User).filter(User.lecture_tutors.any(LectureTutor.lecture==self))
def prepareTimePreferences(self, user=None):
session = Session.object_session(self)
if self.mode == "prefs":
times = session.query(sqlalchemy.func.sum(Tutorial.max_students), Tutorial.time).\
filter(Tutorial.lecture == self).\
group_by(Tutorial.time)
times = [{
'weekday': result[1].weekday(),
'timeofday': result[1].time(),
'time': result[1],
'max_students': result[0]
} for result in times]
for time in times:
if user:
pref = session.query(TimePreference).get(
(self.id, user.id, time['time'].value))
if not pref:
time['penalty'] = 100
else:
time['penalty'] = pref.penalty
else:
time['penalty'] = 100
if user:
if session.new or session.dirty or session.deleted:
session.commit()
else:
times = []
return times
def pref_subjects(self):
session = Session.object_session(self)
return session.query(sqlalchemy.func.count(User.id), User.subject).\
filter(User.time_preferences.any(TimePreference.lecture_id == self.id)).\
group_by(User.subject).order_by(User.subject)
def subjects(self):
session = Session.object_session(self)
return session.query(sqlalchemy.func.count(User.id), User.subject).\
filter(User.lecture_students.any(LectureStudent.lecture_id == self.id)).\
group_by(User.subject).order_by(User.subject)
def getLectureResults(self, tutorials=[], students=None):
session = Session.object_session(self)
if not students:
students = self.lecture_students_for_tutorials(tutorials)
exercises = session.query(Exercise).filter(
Exercise.exam_id.in_([e.id for e in self.exams])).all()
lecture_results = session.query(\
sqlalchemy.func.sum(ExerciseStudent.points).label('points'),
ExerciseStudent.student_id.label('student_id'),
Exam, Exam.id)\
.filter(ExerciseStudent.exercise_id.in_([e.id for e in exercises]))\
.filter(ExerciseStudent.student_id.in_([s.student_id for s in students]))\
.join(Exercise, ExerciseStudent.exercise).join(Exam, Exercise.exam)\
.group_by(ExerciseStudent.student_id, Exam)
return lecture_results
def getLectureResultsByCategory(self, *args, **kwargs):
session = Session.object_session(self)
results = self.getLectureResults(*args, **kwargs).subquery()
return session.query(func.sum(results.c.points).label('points'), results.c.student_id, results.c.category)\
.group_by(results.c.category, results.c.student_id)
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 getGradingResults(self, tutorials=[], students=None):
session = Session.object_session(self)
return session.query(StudentGrade).filter(
StudentGrade.grading_id.in_([g.id for g in self.gradings]))