def respond_topic(student_id, topic_id, status, timestamp): db.custom_query( """UPDATE topic_requests SET status = (SELECT id FROM request_statuses WHERE name = ?), date_responded = ? WHERE student = ? AND topic = ?""", [status, timestamp, student_id, topic_id])
def get_terms_per_year(year): res = db.custom_query( """ SELECT DISTINCT(term) FROM sessions WHERE year = ?""", [year]) return len(res)
def get_task_criteria(task_id): res = db.custom_query(""" SELECT tc.name, tc.max_mark FROM tasks t INNER JOIN task_criteria tc ON tc.task = t.id WHERE t.id = "{id}"; """.format(id=task_id)) return res
def get_users_of_type(acc_type): res = db.custom_query( """ SELECT users.id, users.name, email FROM users INNER JOIN account_types ON users.account_type = account_types.id WHERE account_types.name = ? """, [acc_type]) return [{'id': r[0], 'name': r[1], 'email': r[2]} for r in res]
def get_material_and_attachment(task_id): res = db.custom_query(""" SELECT ta.path FROM tasks t INNER JOIN task_attachments ta ON ta.task = t.id WHERE t.id = "{task_id}"; """.format(task_id=task_id)) return res
def get_tasks_accepted_files(topic_id): res = db.custom_query( """ SELECT file_types.name FROM file_types INNER JOIN submission_types st ON st.file_type = file_types.id WHERE st.task = ? """, [topic_id]) return [r[0] for r in res]
def get_submission_status(student_id, task_id): res = db.custom_query(""" SELECT rs.name FROM submissions INNER JOIN request_statuses rs ON rs.id = status WHERE student = "{student_id}" AND task = "{task_id}"; """.format(student_id=student_id, task_id=task_id)) return res
def get_course_offering_details(): res = db.custom_query(""" SELECT co.id, c.code, s.term, s.year, c.id, s.id, c.name FROM course_offerings co INNER JOIN sessions s ON s.id = co.session INNER JOIN courses c on c.id = co.course """) return res
def get_course_sessions(course_code): res = db.custom_query( """SELECT sessions.year, sessions.term FROM courses INNER JOIN course_offerings AS co ON co.course = courses.id INNER JOIN sessions ON sessions.id = co.session WHERE courses.code = ?""", [course_code]) return [(r[0], r[1]) for r in res]
def get_user_ass_sup(user_id): res = db.custom_query(""" SELECT st.assessor, t.supervisor FROM users u INNER JOIN student_topic st ON st.student = u.id INNER JOIN topics t ON t.id = st.topic WHERE u.id = "{id}"; """.format(id=user_id)) return res
def get_prereqs_by_topic(topic_id): res = db.custom_query(""" SELECT c.code FROM courses c INNER JOIN prerequisites p ON c.id = p.course INNER JOIN topics t ON p.topic = t.id WHERE t.id = "{topic_id}" """.format(topic_id=topic_id)) return res
def get_students_assessor(student_id): res = db.custom_query(""" SELECT staff.id, staff.name FROM users stu JOIN student_topic topic ON stu.id = topic.student JOIN users staff ON topic.assessor = staff.id WHERE stu.id = "{id}"; """.format(id=student_id)) return res
def get_student_topic(student_id): res = db.custom_query(''' SELECT name FROM topics WHERE id = ( SELECT topic FROM student_topic WHERE student = {student_id} ) ;'''.format(student_id=student_id)) return res
def get_topic_areas(topic_id): res = db.custom_query(""" SELECT ta.name FROM topics t INNER JOIN topic_to_area tta ON tta.topic = t.id INNER JOIN topic_areas ta ON tta.topic_area = ta.id WHERE t.id = "{id}"; """.format(id=topic_id)) return res
def search_topic_areas(area): res = db.custom_query(""" SELECT ta.name, t.id FROM topics t INNER JOIN topic_to_area tta ON tta.topic = t.id INNER JOIN topic_areas ta ON tta.topic_area = ta.id WHERE ta.name = "{topic_area}"; """.format(topic_area=area)) return res
def get_student_enrollments(co_id): res = db.custom_query(""" SELECT u.id, u.name, u.email, t.name FROM users u INNER JOIN enrollments e ON e.user = u.id LEFT JOIN student_topic st ON st.student = u.id LEFT JOIN topics t ON t.id = st.topic WHERE e.course_offering = "{co_id}"; """.format(co_id=co_id)) return res
def get_students_supervisor(student_id): res = db.custom_query(""" SELECT staff.id, staff.name FROM users stu JOIN student_topic st ON stu.id = st.student JOIN topics t ON st.topic =t.id JOIN users staff ON t.supervisor = staff.id WHERE stu.id = "{id}"; """.format(id=student_id)) return res
def get_student_submissions(student_id): res = db.custom_query(""" SELECT t.id, t.name, mm.name, s.path, s.date_modified FROM users u INNER JOIN submissions s ON s.student = u.id INNER JOIN tasks t ON t.id = s.task INNER JOIN marking_methods mm ON t.marking_method = mm.id WHERE u.id = "{student_id}"; """.format(student_id=student_id)) return res
def get_staff_curr_topics(email): res = db.custom_query(""" SELECT t.name, ta.name, t.visible, t.id FROM users u INNER JOIN topics t ON u.id = t.supervisor INNER JOIN topic_to_area tta ON t.id = tta.topic INNER JOIN topic_areas ta ON tta.topic_area = ta.id WHERE u.email = "{email}" """.format(email=email)) return res
def get_allowed_material_attachments(user_id): res = db.custom_query(''' SELECT path FROM material_attachments WHERE material IN ( SELECT id FROM materials WHERE visible = 1 AND course_offering IN ( SELECT course_offering FROM enrollments WHERE user = {user_id} ) );'''.format(user_id=user_id)) return list(map(lambda x: x[0], res))
def is_partially_marked(student_id, task_id): res = db.custom_query(''' SELECT COUNT(m.student) > 0 FROM tasks t INNER JOIN submissions s ON t.id = s.task INNER JOIN task_criteria tc ON tc.task = t.id INNER JOIN marks m ON tc.id = m.criteria AND s.student = m.student WHERE t.id = {task_id} AND s.student = {student_id} ;'''.format(student_id=student_id, task_id=task_id)) return res[0][0]
def get_past_task_data(task_id): res = db.custom_query(''' SELECT t.name, t.deadline, t.description, sm.name, t.word_limit, t.size_limit, ft.name, mm.name FROM tasks t INNER JOIN submission_methods sm ON sm.id = t.submission_method INNER JOIN marking_methods mm ON mm.id = t.marking_method LEFT JOIN submission_types st ON t.id = st.task LEFT JOIN file_types ft ON st.file_type = ft.id WHERE t.id={task_id} ;'''.format(task_id=task_id)) return res
def get_general_task_info(task_id): res = db.custom_query(""" SELECT c.name, t.name, t.deadline, t.description, sm.name, mm.name, t.size_limit, t.word_limit FROM tasks t INNER JOIN course_offerings co ON t.course_offering = co.id INNER JOIN courses c ON co.course = c.id INNER JOIN marking_methods mm ON t.marking_method = mm.id INNER JOIN submission_methods sm ON t.submission_method = sm.id WHERE t.id = "{id}"; """.format(id=task_id)) return res
def get_user_materials(user_id): res = db.custom_query(""" SELECT m.id, m.name, s.start_date, s.end_date, c.name FROM users u INNER JOIN enrollments e ON e.user = u.id INNER JOIN course_offerings co ON co.id = e.course_offering INNER JOIN sessions s ON s.id = co.session INNER JOIN materials m on m.course_offering = co.id INNER JOIN courses c on c.id = co.course WHERE u.id = "{id}" AND m.visible = 1; """.format(id=user_id)) return res
def get_user_tasks(user_id): res = db.custom_query(""" SELECT t.id, t.name, c.name, mm.name, t.deadline FROM users u INNER JOIN enrollments e ON e.user = u.id INNER JOIN course_offerings co ON co.id = e.course_offering INNER JOIN courses c ON c.id = co.course INNER JOIN tasks t ON t.course_offering = co.id INNER JOIN marking_methods mm ON mm.id = t.marking_method WHERE u.id = "{id}" AND t.visible = "1"; """.format(id=user_id)) return res
def get_marks_table(student_id, staff_id, task_id): res = db.custom_query(""" SELECT tc.name, m.mark, tc.max_mark, m.feedback FROM users stu INNER JOIN marks m ON stu.id = m.student INNER JOIN users staff ON m.marker = staff.id INNER JOIN task_criteria tc ON m.criteria = tc.id INNER JOIN tasks t ON t.id = tc.task WHERE stu.id = "{student_id}" AND staff.id = "{staff_id}" AND tc.task = "{task_id}"; """.format(student_id=student_id, staff_id=staff_id, task_id=task_id)) return res
def get_curr_topic_requests(email): res = db.custom_query( """ SELECT stu.id, t.id, stu.name, stu.email, t.name, tr.date_created FROM users stu INNER JOIN topic_requests tr ON stu.id = tr.student INNER JOIN topics t ON t.id = tr.topic INNER JOIN users sup ON t.supervisor = sup.id INNER JOIN request_statuses rs ON tr.status = rs.id WHERE sup.email = ? AND rs.name = "pending"; """, [email]) return res
def get_current_assess_students(email): res = db.custom_query(""" SELECT stu.name, stu.email, t.name, stu.id, MAX(sess.end_date) FROM users stu INNER JOIN student_topic st ON st.student = stu.id INNER JOIN topics t ON t.id = st.topic INNER JOIN users sup ON sup.id = st.assessor INNER JOIN enrollments en ON en.user = stu.id INNER JOIN course_offerings co ON co.id = en.course_offering INNER JOIN sessions sess ON sess.id = co.session WHERE sup.email = "{my_email}" GROUP BY stu.id; """.format(my_email=email)) return res
def lookup_topic_request(student_id, topic_id): res = db.custom_query( """ SELECT users.name, users.email, topics.name, tq.date_created, request_statuses.name FROM topic_requests tq INNER JOIN topics ON topics.id = tq.topic INNER JOIN users ON tq.student = users.id INNER JOIN request_statuses ON tq.status = request_statuses.id WHERE tq.student = ? AND tq.topic = ? """, [student_id, topic_id]) return [{ 'userName': r[0], 'email': r[1], 'topicName': r[2], 'reqDate': r[3], 'reqStatus': r[4] } for r in res]
def get_year_range(): res = db.custom_query("SELECT DISTINCT(year) FROM sessions") years = [r[0] for r in res] return (min(years), max(years))