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, ))
