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