def save(self): if Student.get(self.id): execute_database_command( 'UPDATE students SET ' 'fullname = %s, ' 'language_code = %s, ' f'''registered_utc = '{self.registered_utc}' ''' 'WHERE id = %s', (self.fullname, self.language_code, self.id)) else: execute_database_command( 'INSERT INTO students (id, fullname, language_code, registered_utc) ' f'''VALUES (%s, %s, %s, '{self.registered_utc}')''', (self.id, self.fullname, self.language_code)) return self
def save(self): if Task.get(self.id): execute_database_command( 'UPDATE tasks SET ' 'classroom_id = %s, ' 'name = %s, ' f'''created_utc = '{self.created_utc}' ''' 'WHERE id = %s', (self.classroom_id, self.name, self.id)) else: task_id = execute_database_command( 'INSERT INTO tasks (classroom_id, name, created_utc) ' f'''VALUES (%s, %s, '{self.created_utc}') RETURNING id''', (self.classroom_id, self.name))[0][0][0] return Task(self.classroom_id, self.name, self.created_utc, task_id)
def get(task_id): try: id, classroom_id, name, created_utc = execute_database_command( 'SELECT * FROM tasks WHERE id=%s', (task_id, ))[0][0] return Task(classroom_id, name, created_utc, id) except IndexError: return None
def get(telegram_id): try: id, fullname, language_code, registered_utc = execute_database_command( 'SELECT * FROM students WHERE id=%s', (telegram_id, ))[0][0] return Student(id, fullname, language_code, registered_utc) except IndexError: return None
def get_submissions_for_review_count(self): try: return execute_database_command( '''SELECT COUNT(s.id) FROM tasks t JOIN submissions s ON t.id = s.task_id WHERE t.id=%s AND s.status=%s''', (self.id, SubmissionStatus.REVIEW.value))[0][0][0] except IndexError: return None
def get_task_status(self, task_id): try: return execute_database_command( '''SELECT s.status FROM tasks t JOIN submissions s ON t.id = s.task_id WHERE s.student_id=%s AND t.id=%s''', (self.id, task_id))[0][0][0] except IndexError: return 'NONE'
def get_classroom_assessments(self, classroom_id): try: return execute_database_command( '''SELECT t.name, t.created_utc, s.assessment FROM tasks t JOIN submissions s ON t.id = s.task_id WHERE s.student_id=%s AND t.classroom_id=%s AND s.assessment IS NOT NULL''', (self.id, classroom_id))[0] except IndexError: return None
def save(self): if TaskMessage.get(self.id): execute_database_command( 'UPDATE task_messages SET ' 'task_id = %s, ' 'teacher_id = %s, ' 'message_id = %s, ' f'''created_utc = '{self.created_utc}' ''' 'WHERE id = %s', (self.task_id, self.teacher_id, self.message_id, self.id)) else: task_message_id = execute_database_command( 'INSERT INTO task_messages (task_id, teacher_id, message_id, created_utc) ' f'''VALUES (%s, %s, %s, '{self.created_utc}') RETURNING id''', (self.task_id, self.teacher_id, self.message_id))[0][0][0] return TaskMessage(self.task_id, self.teacher_id, self.message_id, self.created_utc, task_message_id)
def get(submission_message_id): try: id, teacher_id, submission_id, message_id, created_utc = execute_database_command( 'SELECT * FROM submission_review_messages WHERE id=%s', (submission_message_id, ))[0][0] return SubmissionReviewMessage(submission_id, teacher_id, message_id, created_utc, id) except IndexError: return None
def get(task_message_id): try: id, teacher_id, task_id, message_id, created_utc = execute_database_command( 'SELECT * FROM task_messages WHERE id=%s', (task_message_id, ))[0][0] return TaskMessage(task_id, teacher_id, message_id, created_utc, id) except IndexError: return None
def get_task_assessments(self, task_id): try: return execute_database_command( '''SELECT st.fullname, s.assessment FROM tasks t JOIN submissions s ON t.id = s.task_id JOIN students st ON s.student_id=st.id JOIN classrooms cl ON t.classroom_id=cl.id WHERE cl.teacher_id=%s AND t.id=%s AND s.assessment IS NOT NULL''', (self.id, task_id))[0] except IndexError: return None
def get_classroom_students(classroom_id): try: students = execute_database_command( '''SELECT s.id, s.fullname, s.language_code, s.registered_utc FROM students s JOIN classroom_students cl_s ON s.id = cl_s.student_id WHERE cl_s.classroom_id=%s''', (classroom_id, ))[0] return [Student(s[0], s[1], s[2], s[3]) for s in students] except IndexError: return None
def get_submission_for_task(self, task_id): try: s = execute_database_command( '''SELECT s.task_id, s.student_id, s.status, s.comment_message_id, s.assessment, s.created_utc, s.id FROM tasks t JOIN submissions s ON t.id = s.task_id WHERE s.student_id=%s AND t.id=%s''', (self.id, task_id))[0][0] return Submission(s[0], s[1], s[2], s[3], s[4], s[5], s[6]) except IndexError: return None
def check_classroom_student(self, classroom_id): try: student_id = execute_database_command( '''SELECT s.id FROM students s JOIN classroom_students cl_s ON s.id = cl_s.student_id WHERE s.id=%s AND cl_s.classroom_id=%s''', (self.id, classroom_id))[0][0] return True if student_id else False except IndexError: return False
def save(self): if SubmissionReviewMessage.get(self.id): execute_database_command( 'UPDATE submission_review_messages SET ' 'submission_id = %s, ' 'teacher_id = %s, ' 'message_id = %s, ' f'''created_utc = '{self.created_utc}' ''' 'WHERE id = %s', (self.submission_id, self.teacher_id, self.message_id, self.id)) else: submission_message_id = execute_database_command( 'INSERT INTO submission_review_messages (submission_id, teacher_id, message_id, created_utc) ' f'''VALUES (%s, %s, %s, '{self.created_utc}') RETURNING id''', (self.submission_id, self.teacher_id, self.message_id))[0][0][0] return SubmissionReviewMessage(self.submission_id, self.teacher_id, self.message_id, self.created_utc, submission_message_id)
def get_messages(self): try: task_messages = execute_database_command( '''SELECT tm.task_id, tm.teacher_id, tm.message_id, tm.created_utc, tm.id FROM tasks t JOIN task_messages tm ON t.id = tm.task_id WHERE t.id=%s''', (self.id, ))[0] return [ TaskMessage(tm[0], tm[1], tm[2], tm[3], tm[4]) for tm in task_messages ] except IndexError: return None
def get_classrooms(self): try: classrooms = execute_database_command( '''SELECT cl.teacher_id, cl.name, cl.slug, cl.created_utc, cl.id FROM teachers t JOIN classrooms cl ON t.id = cl.teacher_id WHERE t.id=%s''', (self.id, ))[0] return [ Classroom(cl[0], cl[1], cl[2], cl[3], cl[4]) for cl in classrooms ] except IndexError: return None
def get_messages(self): try: submission_messages = execute_database_command( '''SELECT sm.submission_id, sm.student_id, sm.message_id, sm.created_utc, sm.id FROM submissions s JOIN submission_messages sm ON s.id = sm.submission_id WHERE s.id=%s''', (self.id, ))[0] return [ SubmissionMessage(sm[0], sm[1], sm[2], sm[3], sm[4]) for sm in submission_messages ] except IndexError: return None
def get_submissions_reviewed(self): try: submissions = execute_database_command( '''SELECT s.task_id, s.student_id, s.status, s.comment_message_id, s.assessment, s.created_utc, s.id FROM tasks t JOIN submissions s ON t.id = s.task_id WHERE t.id=%s AND s.status=%s''', (self.id, SubmissionStatus.REVIEWED.value))[0] return [ Submission(s[0], s[1], s[2], s[3], s[4], s[5], s[6]) for s in submissions ] except IndexError: return None
def get_review_messages(self): try: submission_review_messages = execute_database_command( '''SELECT srm.submission_id, srm.teacher_id, srm.message_id, srm.created_utc, srm.id FROM submissions s JOIN submission_review_messages srm ON s.id = srm.submission_id WHERE s.id=%s''', (self.id, ))[0] return [ SubmissionReviewMessage(srm[0], srm[1], srm[2], srm[3], srm[4]) for srm in submission_review_messages ] except IndexError: return None
def save(self): if Submission.get(self.id): execute_database_command( 'UPDATE submissions SET ' 'task_id = %s, ' 'student_id = %s, ' 'status = %s, ' 'comment_message_id = %s, ' 'assessment = %s, ' f'''created_utc = '{self.created_utc}' ''' 'WHERE id = %s', (self.task_id, self.student_id, self.status, self.comment_message_id, self.assessment, self.id)) else: submission_id = execute_database_command( 'INSERT INTO submissions (task_id, student_id, status, comment_message_id, assessment, created_utc) ' f'''VALUES (%s, %s, %s, %s, %s, '{self.created_utc}') RETURNING id''', (self.task_id, self.student_id, self.status, self.comment_message_id, self.assessment))[0][0][0] return Submission(self.task_id, self.student_id, self.status, self.comment_message_id, self.assessment, self.created_utc, submission_id)
def get(submission_id): try: id, task_id, student_id, status, comment_message_id, assessment, created_utc = execute_database_command( 'SELECT * FROM submissions WHERE id=%s', (submission_id, ))[0][0] return Submission(task_id, student_id, status, comment_message_id, assessment, created_utc, id) except IndexError: return None
def delete(self): execute_database_command('DELETE from submissions WHERE id=%s', (self.id, ))
def delete(self): execute_database_command('DELETE from tasks WHERE id=%s', (self.id, ))