def get_students_grades(training_id: int):
"""
Retrieves student grades for specific training
@param training_id - searched training id
@return list of student grades
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
'd.id AS student_id, '
'd.first_name AS first_name, '
'd.last_name AS last_name, '
'd.email AS email, '
'a.hours AS hours, '
'concat(d.first_name, \' \', d.last_name) as full_name '
'FROM training t, attendance a, auth_user d '
'WHERE d.id = a.student_id '
'AND a.training_id = %(training_id)s '
'AND t.id = %(training_id)s '
'UNION DISTINCT '
'SELECT '
'd.id AS student_id, '
'd.first_name AS first_name, '
'd.last_name AS last_name, '
'd.email AS email, '
'COALESCE(a.hours, 0) AS hours, '
'concat(d.first_name, \' \', d.last_name) as full_name '
'FROM training t, enroll e, auth_user d, student s '
'LEFT JOIN attendance a ON a.student_id = s.user_id AND a.training_id = %(training_id)s '
'WHERE s.user_id = e.student_id '
'AND d.id = e.student_id '
'AND s.is_ill = FALSE '
'AND t.id = %(training_id)s '
'AND t.group_id = e.group_id ', {"training_id": training_id})
return dictfetchall(cursor)
def get_clubs(student: Optional[Student] = None):
"""
Retrieves existing clubs
@return list of all club
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
'g.id AS id, '
'g.name AS name, '
'sport.name AS sport_name, '
's.name AS semester, '
'capacity, description, trainer_id, is_club, '
'count(e.id) AS current_load '
'FROM sport, semester s, "group" g '
'LEFT JOIN enroll e ON e.group_id = g.id '
'WHERE s.id = current_semester() '
'AND sport_id = sport.id '
'AND semester_id = s.id '
'AND is_club = TRUE '
'AND sign(%(medical_group_id_sign)s) = sign(g.minimum_medical_group_id) '
'GROUP BY g.id, sport.id, s.id', {
"medical_group_id_sign": 1 if student is None else student.medical_group_id
})
return dictfetchall(cursor)
def get_trainings_for_trainer(trainer: Trainer, start: datetime,
end: datetime):
"""
Retrieves existing trainings in the given range for given student
@param trainer - searched trainer
@param start - range start date
@param end - range end date
@return list of trainings for trainer
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
't.id AS id, '
't.start AS start, '
't."end" AS "end", '
'g.id AS group_id, '
'g.name AS group_name, '
'tc.name AS training_class, '
'TRUE AS can_grade '
'FROM "group" g, training t LEFT JOIN training_class tc ON t.training_class_id = tc.id '
'WHERE ((t.start > %(start)s AND t.start < %(end)s) OR (t."end" > %(start)s AND t."end" < %(end)s) OR (t.start < %(start)s AND t."end" > %(end)s)) '
'AND t.group_id = g.id '
'AND g.trainer_id = %(trainer_id)s '
'AND g.semester_id = current_semester()', {
"start": start,
"end": end,
"trainer_id": trainer.pk
})
return dictfetchall(cursor)
def get_sport_schedule(
sport_id: int,
student: Optional[Student] = None,
):
"""
Retrieves existing schedules for the given sport type
@param sport_id - searched sport id
@param student - student, acquiring groups. Groups will be based on medical group
@return list of trainings info
"""
with connection.cursor() as cursor:
cursor.execute('SELECT '
'g.id AS group_id, '
'g.name AS group_name, '
'count(e.id) AS current_load, '
'g.capacity AS capacity, '
's.weekday AS weekday, '
's.start AS start, '
's."end" AS "end", '
'tc.name AS training_class '
'FROM sport sp, "group" g LEFT JOIN enroll e ON e.group_id = g.id, schedule s '
'LEFT JOIN training_class tc ON s.training_class_id = tc.id '
'WHERE g.sport_id = sp.id '
'AND g.semester_id = current_semester() '
'AND s.group_id = g.id '
'AND sp.id = %(sport_id)s '
'AND sign(%(medical_group_id_sign)s) = sign(g.minimum_medical_group_id) '
'GROUP BY g.id, s.id, tc.id',
{
"sport_id": sport_id,
"medical_group_id_sign": 1 if student is None else student.medical_group_id
}
)
return dictfetchall(cursor)
def get_trainings_for_student(student: Student, start: datetime,
end: datetime):
"""
Retrieves existing trainings in the given range for given student
@param student - searched student
@param start - range start date
@param end - range end date
@return list of trainings for student
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
't.id AS id, '
't.start AS start, '
't."end" AS "end", '
'g.id AS group_id, '
'g.name AS group_name, '
'tc.name AS training_class, '
'COALESCE(a.hours, 0) AS hours, '
'FALSE AS can_grade '
'FROM enroll e, "group" g, sport s, training t '
'LEFT JOIN attendance a ON a.student_id = %s AND a.training_id = t.id '
'LEFT JOIN training_class tc ON t.training_class_id = tc.id '
'WHERE t.start > %s AND t."end" < %s '
'AND g.sport_id = s.id '
'AND (hours > 0 OR s.name != %s) '
'AND t.group_id = g.id '
'AND e.group_id = g.id '
'AND e.student_id = %s '
'AND g.semester_id = current_semester()',
(student.pk, start, end, settings.OTHER_SPORT_NAME, student.pk))
return dictfetchall(cursor)
def get_trainings_for_student(student: Student, start: datetime,
end: datetime):
"""
Retrieves existing trainings in the given range for given student
@param student - searched student
@param start - range start date
@param end - range end date
@return list of trainings for student
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
't.id AS id, '
't.start AS start, '
't."end" AS "end", '
'g.id AS group_id, '
'g.name AS group_name, '
'tc.name AS training_class, '
'FALSE AS can_grade '
'FROM enroll e, "group" g, sport s, training t '
'LEFT JOIN training_class tc ON t.training_class_id = tc.id '
'WHERE ((t.start > %(start)s AND t.start < %(end)s) OR (t."end" > %(start)s AND t."end" < %(end)s) OR (t.start < %(start)s AND t."end" > %(end)s)) '
'AND g.sport_id = s.id '
'AND s.name != %(extra_sport)s '
'AND t.group_id = g.id '
'AND e.group_id = g.id '
'AND e.student_id = %(student_id)s '
'AND g.semester_id = current_semester() '
'UNION DISTINCT '
'SELECT '
't.id AS id, '
't.start AS start, '
't."end" AS "end", '
'g.id AS group_id, '
'COALESCE(t.custom_name, g.name) AS group_name, '
'tc.name AS training_class, '
'FALSE AS can_grade '
'FROM attendance a, "group" g, training t '
'LEFT JOIN training_class tc ON t.training_class_id = tc.id '
'WHERE ((t.start > %(start)s AND t.start < %(end)s) OR (t."end" > %(start)s AND t."end" < %(end)s) OR (t.start < %(start)s AND t."end" > %(end)s)) '
'AND a.student_id = %(student_id)s '
'AND t.group_id = g.id '
'AND a.training_id = t.id '
'AND g.semester_id = current_semester()', {
"start": start,
"end": end,
"extra_sport": settings.OTHER_SPORT_NAME,
"student_id": student.pk
})
return dictfetchall(cursor)
def get_detailed_hours(student: Student, semester: Semester):
"""
Retrieves statistics of hours in one semester
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT g.name AS "group", t.custom_name AS custom_name, t.start AS "timestamp", a.hours AS hours '
'FROM training t, "group" g, attendance a '
'WHERE a.student_id = %s '
'AND a.training_id = t.id '
'AND t.group_id = g.id '
'AND g.semester_id = %s '
'ORDER BY t.start', (student.pk, semester.pk))
return dictfetchall(cursor)
def get_trainer_groups(trainer: Trainer):
"""
For a given trainer return all groups he/she is training in current semester
@return list of group trainer is trainings in current semester
"""
with connection.cursor() as cursor:
cursor.execute('SELECT '
'g.id AS id, '
'g.name AS name, '
's.name AS sport_name '
'FROM "group" g, sport s '
'WHERE g.semester_id = current_semester() '
'AND g.sport_id = s.id '
'AND g.trainer_id = %s', (trainer.pk,))
return dictfetchall(cursor)
def get_student_groups(student: Student):
"""
Retrieves groups, where student is enrolled
@return list of group dicts
"""
with connection.cursor() as cursor:
cursor.execute('SELECT '
'g.id AS id, '
'g.name AS name, '
's.name AS sport_name '
'FROM enroll e, "group" g, sport s '
'WHERE g.semester_id = current_semester() '
'AND e.group_id = g.id '
'AND e.student_id = %s '
'AND s.id = g.sport_id ', (student.pk,))
return dictfetchall(cursor)
def get_brief_hours(student: Student):
"""
Retrieves statistics of hours per different semesters
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
's.id AS semester_id, '
's.name AS semester_name, '
's.start AS semester_start, '
's.end AS semester_end, '
'sum(a.hours) AS hours '
'FROM semester s, training t, "group" g, attendance a '
'WHERE a.student_id = %s '
'AND a.training_id = t.id '
'AND t.group_id = g.id '
'AND g.semester_id = s.id '
'GROUP BY s.id', (student.pk, ))
return dictfetchall(cursor)
def get_student_last_attended_dates(group_id: int):
"""
Retrieves last attended dates for students
@param group_id - searched group id
@return list of students and their last attended training timestamp
"""
with connection.cursor() as cursor:
cursor.execute(
'SELECT '
'd.id AS student_id, '
'd.first_name AS first_name, '
'd.last_name AS last_name, '
'd.email AS email, '
'max(t.start) AS last_attended, '
'concat(d.first_name, \' \', d.last_name) as full_name '
'FROM enroll e, auth_user d '
'LEFT JOIN attendance a ON a.student_id = d.id '
'LEFT JOIN training t ON a.training_id = t.id AND t.group_id = %(group_id)s '
'WHERE e.group_id = %(group_id)s '
'AND e.student_id = d.id '
'GROUP BY d.id', {"group_id": group_id})
return dictfetchall(cursor)
def get_sc_training_groups():
"""
Finds sc training groups for the current semester
@return list of group dict
"""
with connection.cursor() as cursor:
cursor.execute('SELECT '
'g.id AS id, '
'g.name AS name, '
's.name AS sport_name '
'FROM "group" g, sport s '
'WHERE g.semester_id = current_semester() '
'AND g.sport_id = s.id '
'AND g.name IN (%s, %s, %s) '
'ORDER BY g.name',
(settings.SC_TRAINERS_GROUP_NAME_FREE,
settings.SC_TRAINERS_GROUP_NAME_PAID,
settings.SELF_TRAINING_GROUP_NAME,
)
)
row = dictfetchall(cursor)
if row is None or len(row) != 3:
raise ValueError("Unable to find SC training groups")
return row