def insert_files(files, task_id): file_types = get_all_file_types() for file in files: file_id = file if file in file_types: file_id = file_types[file] db.insert_single('submission_types', [file_id, task_id])
def create_course(): db.connect() curr_year = datetime.now().year num_terms = queries.get_terms_per_year(curr_year) course = {'offerings': [False for _ in range(num_terms)]} try: res = get_fields(request.form, ['title', 'code', 'description', 'year'], ['year']) for i in range(num_terms): if str(i + 1) in request.form: course['offerings'][i] = True except ValueError as e: db.close() return e.args[0] course['title'] = res[0] course['code'] = res[1] course['description'] = res[2] if not re.match(config.COURSE_CODE_FORMAT, course['code']): db.close() return error("Invalid course code", 'code') course['year'] = int(res[3]) if curr_year > course['year']: db.close() return error(f"Year must be at least {curr_year}") if True not in course['offerings']: db.close() return error('You must select at least one term offering') sessions = queries.get_course_sessions(course['code']) sessions = filter(lambda s: s[0] == course['year'], sessions) for year, term in sessions: if course['offerings'][term - 1]: db.close() return error(f"{course['code']} already offered in {year} T{term}") db.insert_single('courses', [course['code'], course['title'], course['description']], ['code', 'name', 'description']) res = db.select_columns('courses', ['id'], ['code'], [course['code']]) course['id'] = res[0][0] query = [] for i in range(len(course['offerings'])): if not course['offerings'][i]: continue res = db.select_columns('sessions', ['id'], ['year', 'term'], [course['year'], i + 1]) session_id = res[0][0] query.append(('course_offerings', [course['id'], session_id], ['course', 'session'])) db.insert_multiple(query) db.close() return jsonify({'status': 'ok'})
def gen_topic_prereqs(topic_id, course_ids): if not random.randrange(0, 15): # skip a couple of topics so they don't have prereqs return course_ids = random.sample(course_ids, random.randrange(1, 4)) for course_id in course_ids: db.insert_single( 'prerequisites', [0, topic_id, course_id], ['type', 'topic', 'course'] )
def request_new_topic(): if session['acc_type'] != 'student': # only students are allowed to request topics # disallow ALL other users from requesting return error('You must be a student to request a topic!') try: fields = ['topic', 'message'] topic, message = get_fields(request.form, fields) except ValueError as e: return e.args[0] db.connect() res = db.select_columns('topics', ['id', 'name', 'supervisor', 'visible'], ['id'], [topic]) topic_name = res[0][1] supervisor = res[0][2] if not len(res): db.close() return error('No such topic exists!') if not int(res[0][3]): db.close() return error('This topic is not available to request!') res = db.select_columns('request_statuses', ['id'], ['name'], ['pending']) user_id = session['id'] now = datetime.now().timestamp() try: db.insert_single( 'topic_requests', [user_id, topic, res[0][0], now, message], ['student', 'topic', 'status', 'date_created', 'text']) except sqlite3.IntegrityError: db.close() return error('You have already requested this topic!') res = db.select_columns('users', ['name', 'email'], ['id'], [supervisor]) hr_tag = '<hr style="border: 1px dashed;">' send_email(to=res[0][1], name=res[0][0], subject='New Topic Request', messages=[ 'A student has requested a thesis topic on offer by you.', f'The topic is titled "{topic_name}".', f'A message from the student is attached below:{hr_tag}', message.replace('\n', '<br>'), f'{hr_tag}You can approve or reject the topic request ' + f'<a href="{config.SITE_HOME}">here</a>.' ]) db.close() return jsonify({'status': 'ok'})
def gen_student_topics(): types = get_all_account_types() # get first supervisor sup_ids = db.select_columns( 'users', ['id'], ['account_type'], [types['supervisor']] ) # # Add students supervisor_0 is supervising # # get possible topics topics = db.select_columns('topics', ['id', 'supervisor'],) # get all students students = db.select_columns('users', ['id'], ['account_type'], [types['student']]) student_ids = [] request_student_ids = [] for student in students: number = random.randrange(10) if number > 2: student_ids.append(student[0]) else: request_student_ids.append(student[0]) gen_topic_requests(request_student_ids) # enroll current and past students students # tot_curr_stu = 3 # student_ids = list(range(0, tot_curr_stu)) # student_ids.extend(list(range(int(len(students)/2), # int(len(students)/2+tot_curr_stu)))) for student_id in student_ids: if len(sup_ids) < 2: break number = random.randrange(len(topics)) topic = topics[number][0] supervisor_id = topics[number][1] number = random.randrange(len(sup_ids)) assessor_id = sup_ids[number][0] while assessor_id == supervisor_id: number = random.randrange(len(sup_ids)) assessor_id = sup_ids[number][0] db.insert_single('student_topic', [student_id, topic, assessor_id], ['student', 'topic', 'assessor'])
def gen_courses(): with open('db/prereq.json') as f: courses = json.load(f) for c in courses: db.insert_single('courses', [c['code'], c['name'], 1], ['code', 'name', 'prereq']) with open('db/courses.json') as f: courses = json.load(f) for c in courses: db.insert_single('courses', [c['code'], c['name'], 0], ['code', 'name', 'prereq'])
def gen_task_outline(): tasks = db.select_columns('tasks', ['id'], None, None) upload_dir = Path(config.STATIC_PATH) / Path(config.FILE_UPLOAD_DIR) upload_dir.mkdir(exist_ok=True) sample_attachment = Path('db/sample_attachment.pdf') for task in tasks: stem = Path(str(uuid.uuid4()) + 'sample_attachment.pdf') path = upload_dir / stem copyfile(sample_attachment, path) db.insert_single( 'task_attachments', [task[0], str(stem)], ['task', 'path'] )
def gen_topic_areas(topic_id, areas): query = [] for area in areas: area = area.title().replace('Hci', 'HCI').replace('And', 'and') res = db.select_columns('topic_areas', ['name'], ['name'], [area]) if len(res) == 0: db.insert_single( 'topic_areas', [area], ['name'] ) area_id = db.select_columns( 'topic_areas', ['id'], ['name'], [area] )[0][0] db.insert_single( 'topic_to_area', [topic_id, area_id], ['topic', 'topic_area'] )
def gen_material_attachments(): materials = db.select_columns('materials', ['id']) upload_dir = Path(config.STATIC_PATH) / Path(config.FILE_UPLOAD_DIR) upload_dir.mkdir(exist_ok=True) sample_material = Path('db/sample_material.pdf') for material in materials: stem = Path(str(uuid.uuid4()) + 'sample_material.pdf') path = upload_dir / stem copyfile(sample_material, path) db.insert_single( 'material_attachments', [material[0], str(stem)], ['material', 'path'] )
def submit_text_task(): task_id = request.form.get('task', -1) text = request.form.get('text-submission', -1) db.connect() task = build_task(task_id) res = db.select_columns('enrollments', ['user'], ['user', 'course_offering'], [session['id'], task['offering']]) if not res: db.close() return error("User not enrolled in task's course") if not request.form.get('certify', 'false') == 'true': db.close() return error("You must certify this is all your own work") if datetime.now().timestamp() >= task['deadline']: db.close() return error("Submissions closed!<br>You can no longer submit") mark_method_id = None if task['mark_method']['name'] == 'requires approval': mark_method_id = db.select_columns('request_statuses', ['id'], ['name'], ['pending'])[0][0] elif task['mark_method']['name'] == 'requires mark': mark_method_id = db.select_columns('request_statuses', ['id'], ['name'], ['pending mark'])[0][0] # check if text is too long if (len(text.strip().split(' ')) > task["word_limit"]): db.close() return error(f'Your submission exceeds the word limit') res = db.select_columns('submissions', ['*'], ['student', 'task'], [session['id'], task['id']]) if res: # if there's already a submission, delete it db.delete_rows('submissions', ['student', 'task'], [session['id'], task['id']]) db.insert_single('submissions', [ session['id'], task['id'], task['name'], text, datetime.now().timestamp(), mark_method_id ], ['student', 'task', 'name', 'text', 'date_modified', 'status']) db.close() return jsonify({'status': 'ok'})
def gen_topic_requests(student_ids): # # Add students supervisor_0 is being requested by # request_statuses = get_all_request_types() pending_id = request_statuses['pending'] # get possible topics topics = db.select_columns('topics', ['id']) for i in student_ids: topic_id = random.randrange(len(topics)) db.insert_single('topic_requests', [i, topics[topic_id][0], pending_id, datetime.datetime.now().timestamp(), 'FAKE_GEN_DATA'], ['student', 'topic', 'status', 'date_created', 'text'])
def respond_request(): data = get_fields(request.form, ['response', 'student-id', 'topic']) db.connect() req_status = 'approved' if data[0] == 'accept' else 'rejected' if req_status == 'approved': if 'assessor' not in request.form: db.close() return error('You must specify an assessor') db.delete_rows('student_topic', ['student'], [data[1]]) db.insert_single('student_topic', [data[1], data[2], request.form['assessor']], ['student', 'topic', 'assessor']) queries.respond_topic(data[1], data[2], req_status, datetime.now().timestamp()) res = db.select_columns('users', ['email', 'name'], ['id'], [data[1]])[0] student = {'email': res[0], 'name': res[1]} topic = db.select_columns('topics', ['name'], ['id'], [data[2]])[0][0] db.close() send_email(student['email'], student['name'], 'Topic Reply', [f'Your topic request for "{topic}" has been {req_status}.']) return jsonify({'status': 'ok'})
def gen_course_offering(): with open('db/courses.json') as f: for c in json.load(f): res = db.select_columns('courses', ['id'], ['code'], [c['code']]) assert len(res) > 0 course_id = res[0][0] if(c['semester']): # create offerings for thesis A/B in years before 2019 session_ids = db.select_columns_operator('sessions', ['id'], ['year'], ['2019'], '<') for session_id in session_ids: db.insert_single('course_offerings', [course_id, session_id[0]], ['course', 'session']) else: # create offering for thesis A/B/C in years after 2018 session_ids = db_queries.get_session_ids_in_range(2019, 2021) for session_id in session_ids: db.insert_single('course_offerings', [course_id, session_id[0]], ['course', 'session']) session_id = db.select_columns( 'sessions', ['id'], ['year', 'term'], [2019, 1] )[0][0] course_id = db.select_columns( 'courses', ['id'], ['code'], ['COMP4931'] )[0][0] db.insert_single( 'course_offerings', [session_id, course_id], ['session', 'course'] )
def gen_enrollments(): types = get_all_account_types() # get all sessions in the years (2018, 2019, 2020) and put each year into # a list all_years = [] for year in range(2018, 2021): res = db.select_columns('sessions', ['id'], ['year'], [year]) all_years.append(res) # get all courses for old semesters courses_sem = [] for code in ['COMP4930', 'COMP4931']: courses_sem += db.select_columns('courses', ['id'], ['code'], [code]) # get all courses for old semesters courses_tri = [] for code in ['COMP4951', 'COMP4952', 'COMP4953']: courses_tri += db.select_columns('courses', ['id'], ['code'], [code]) # get all students students = db.select_columns('users', ['id'], ['account_type'], [types['student']]) # create entries in the enrollments table for i, (student, ) in enumerate(students): # decide what year current student is enrolled in if i < int(len(students)/2): sessions = all_years[1] # 2019 courses = courses_tri # uncomment to generate 2020 students # elif i < 3*int(len(students)/4): # sessions = all_years[2] # 2020 # courses = courses_tri else: sessions = all_years[0] # 2018 courses = courses_sem # enroll the student for i, (course, ) in enumerate(courses): course_offering = db.select_columns('course_offerings', ['id'], ['session', 'course'], [sessions[i][0], course]) assert len(course_offering) > 0 course_offering = course_offering[0][0] db.insert_single('enrollments', [student, course_offering], ['user', 'course_offering'])
def submit_file_task(): task_id = request.form.get('task', -1) db.connect() res = db.select_columns('tasks', [ 'deadline', 'marking_method', 'visible', 'course_offering', 'size_limit' ], ['id'], [task_id]) if not res: db.close() return error("Task not found") task = build_task(task_id) res = db.select_columns('enrollments', ['user'], ['user', 'course_offering'], [session['id'], task['offering']]) if not res: db.close() return error("User not enrolled in task's course") if not request.form.get('certify', 'false') == 'true': db.close() return error("You must certify this is all your own work") if datetime.now().timestamp() >= task['deadline']: db.close() return error("Submissions closed!<br>You can no longer submit") if task['mark_method']['name'] == 'requires approval': res = db.select_columns('request_statuses', ['id'], ['name'], ['pending']) elif task['mark_method']['name'] == 'requires mark': res = db.select_columns('request_statuses', ['id'], ['name'], ['pending mark']) pending_status_id = res[0][0] try: sent_file = FileUpload(req=request) except KeyError: db.close() return error("You must supply a file for submission") if sent_file.get_extention() not in task['accepted_files']: db.close() accept_files = ', '.join([f[1:] for f in task['accepted_files']]) return error(f"File must be formatted as {accept_files}") if sent_file.get_size() > task['file_limit']: sent_file.remove_file() db.close() return error( f'File exceeds the maximum size of {task["file_limit"]} MB') sent_file.commit() res = db.select_columns('submissions', ['path'], ['student', 'task'], [session['id'], task['id']]) if res: db.delete_rows('submissions', ['student', 'task'], [session['id'], task['id']]) # If the file doesn't exists don't worry as we are deleting # the submission anyway try: prev_submission = FileUpload(filename=res[0][0]) prev_submission.remove_file() except LookupError: pass db.insert_single('submissions', [ session['id'], task['id'], sent_file.get_original_name(), str(sent_file.get_name()), datetime.now().timestamp(), pending_status_id ], ['student', 'task', 'name', 'path', 'date_modified', 'status']) db.close() return jsonify({'status': 'ok'})
def mark_task(): if request.method == 'GET': task_id = request.args.get('task', None, type=int) student_id = request.args.get('student', None, type=int) if task_id is None or student_id is None: abort(400) db.connect() if session['id'] not in get_students_staff(student_id): db.close() abort(403) task = build_task(task_id) if not task: db.close() abort(404) res = db.select_columns('users', ['name', 'email'], ['id'], [student_id]) if not res: db.close() abort(404) student = {'id': student_id, 'name': res[0][0], 'email': res[0][1]} task_criteria = db.select_columns('task_criteria', ['id', 'task', 'name', 'max_mark'], ['task'], [task_id]) submission = build_student_submission(student_id, task_id) marked_feedback = [] for criteria in task_criteria: marked_feedback.append( db.select_columns('marks', ['mark', 'feedback'], ['criteria', 'student', 'marker'], [criteria[0], student_id, session['id']])) task_criteria_id = [] for criteria in task_criteria: task_criteria_id.append(criteria[0]) task_max = [] for criteria in task_criteria: task_max.append(criteria[3]) check_if_assessor = db.select_columns('student_topic', ['student'], ['assessor', 'student'], [session['id'], student_id]) is_assessor = False if check_if_assessor: is_assessor = True db.close() heading = f"{task['course_name']} - {task['name']}" return render_template('task_base.html', topic_request_text=config.TOPIC_REQUEST_TEXT, heading=heading, title=task['name'], taskCriteria=task_criteria, student=student, submission=submission, studentId=student_id, taskCriteriaId=task_criteria_id, taskMax=task_max, markedFeedback=marked_feedback, task=task, marker=True, isAssessor=is_assessor) data = json.loads(request.data) marks = data['marks'] feedback = data['feedback'] task_id = data['taskId'] student_id = data['studentId'] task_criteria = data['taskCriteria'] task_max = data['taskMax'] db.connect() if session['id'] not in get_students_staff(student_id): db.close() abort(403) try: check = data['approveCheck'] if (not check): res = db.select_columns('request_statuses', ['id'], ['name'], ['rejected']) db.update_rows('submissions', [res[0][0]], ['status'], ['student', 'task'], [student_id, task_id]) else: res = db.select_columns('request_statuses', ['id'], ['name'], ['approved']) db.update_rows('submissions', [res[0][0]], ['status'], ['student', 'task'], [student_id, task_id]) marks = [100] if feedback[0] == '': feedback = [None] except KeyError: pass for i in range(len(marks)): try: val = int(marks[i]) if val < 0 or val > 100: db.close() return error('Marks must be between 0-100') if val > task_max[i]: db.close() return error(f'Mark {val} exceeds max mark of {task_max[i]}') except ValueError: db.close() return error('Please enter an integer value for marks') for f in feedback: if f == '': db.close() return error('Please enter some feedback') for i in range(len(marks)): try: db.insert_single('marks', [ task_criteria[i], student_id, session['id'], marks[i], feedback[i] ], ['criteria', 'student', 'marker', 'mark', 'feedback']) except sqlite3.IntegrityError: db.update_rows('marks', [marks[i], feedback[i]], ['mark', 'feedback'], ['criteria', 'student', 'marker'], [task_criteria[i], student_id, session['id']]) marked_method = db.select_columns('marking_methods', ['id'], ['name'], ["requires mark"])[0][0] is_mark_type = len( db.select_columns('tasks', ['id'], ['id', 'marking_method'], [task_id, marked_method])) if is_mark_type: new_sub_status = "pending mark" if queries.is_fully_marked(student_id, task_id): new_sub_status = "marked" elif queries.is_partially_marked(student_id, task_id): new_sub_status = "partially marked" status_id = db.select_columns('request_statuses', ['id'], ['name'], [new_sub_status])[0][0] db.update_rows('submissions', [status_id], ['status'], ['student', 'task'], [student_id, task_id]) # send email student = db.select_columns('users', ['name', 'email'], ['id'], [student_id])[0] task_name = db.select_columns('tasks', ['name'], ['id'], [task_id])[0][0] marker = session['name'] subject = f'Marks Entered for Task "{task_name}"' msg1 = f'Your submission for task "{task_name}"' + \ f' has just been marked by {marker}.' view_link = url_for('.view_task', task=task_id, _external=True) msg2 = f'You can view your marks and feedback ' + \ f'<a href="{view_link}">here</a>.' send_email(to=student[1], name=student[0], subject=subject, messages=[msg1, msg2]) db.close() return jsonify({'status': 'ok'})
def gen_tasks(): with open('db/tasks.json') as f: tasks = json.load(f) for t in tasks: res = db.select_columns('courses', ['id'], ['code'], [t['course']]) assert len(res) > 0 course_id = res[0][0] res = db.select_columns('marking_methods', ['id'], ['name'], ['requires {}'.format(t['marking'])]) assert len(res) > 0 mark_method_id = res[0][0] res = db.select_columns('submission_methods', ['id'], ['name'], ['{} submission'.format(t['submission'])]) sub_method_id = res[0][0] word_limit = t.get('word-limit', random.randrange(400, 5000)) offerings = db.select_columns('course_offerings', ['id', 'session'], ['course'], [course_id]) for offer_id, session_id in offerings: date = db.select_columns('sessions', ['start_date', 'end_date'], ['id'], [session_id]) due = datetime.datetime.fromtimestamp( random.randrange(date[0][0], date[0][1]) ).replace(hour=23, minute=59, second=59 ).timestamp() db.insert_single( 'tasks', [t['name'], offer_id, due, t['description'], mark_method_id, word_limit, sub_method_id], ['name', 'course_offering', 'deadline', 'description', 'marking_method', 'word_limit', 'submission_method'] ) res = db.select_columns('tasks', ['id'], ['name', 'course_offering'], [t['name'], offer_id]) assert len(res) > 0 task_id = res[0][0] if 'files' not in t: continue for ft in t['files']: res = db.select_columns('file_types', ['id'], ['name'], [ft]) assert len(res) > 0 ft_id = res[0][0] db.insert_single('submission_types', [ft_id, task_id], ['file_type', 'task'])
def gen_tasks2(): res = db.select_columns('course_offerings', ['*']) marking_methods = get_all_marking_types() submission_methods = get_all_submission_types() file_types = get_all_file_types() task_id = 1 for co in res: course = db.select_columns( 'courses', ['code', 'name'], ['id'], [co[1]] )[0] session = db.select_columns( 'sessions', ['start_date', 'end_date'], ['id'], [co[2]] )[0] start_date = datetime.datetime.fromtimestamp(session[0]) end_date = datetime.datetime.fromtimestamp(session[1]) task_name = "" if "thesis a" in course[1].lower() or "part a" in course[1].lower(): task_name = "Thesis Abstract" description = "Present your initial idea" word_limit = 1000 marking_method = marking_methods['requires approval'] submission_method = submission_methods['text submission'] dif = datetime.timedelta(days=(7*2), minutes=-1) deadline = datetime.datetime.timestamp(start_date + dif) db.insert_single( 'tasks', [task_id, task_name, deadline, description, marking_method, word_limit, co[0], submission_method], ['id', 'name', 'deadline', 'description', 'marking_method', 'word_limit', 'course_offering', 'submission_method'] ) task_id += 1 task_name = course[1] + " Presentation" description = "Please present the progress that you have made in "\ + course[1] size_limit = 5 marking_method = marking_methods['requires mark'] submission_method = submission_methods['file submission'] files = [file_types['.pdf']] dif = datetime.timedelta(days=(7*8), minutes=-1) deadline = datetime.datetime.timestamp(start_date + dif) db.insert_single( 'tasks', [task_id, task_name, deadline, description, marking_method, size_limit, co[0], submission_method], ['id', 'name', 'deadline', 'description', 'marking_method', 'size_limit', 'course_offering', 'submission_method'] ) insert_files(files, task_id) task_id += 1 task_name = course[1] + " Report" description = "Please write up a report to cover all of your progress"\ + " made in " + course[1] size_limit = 10 dif = datetime.timedelta(days=(7*11), minutes=-1) deadline = datetime.datetime.timestamp(start_date + dif) db.insert_single( 'tasks', [task_id, task_name, deadline, description, marking_method, size_limit, co[0], submission_method], ['id', 'name', 'deadline', 'description', 'marking_method', 'size_limit', 'course_offering', 'submission_method'] ) insert_files(files, task_id) task_id += 1
def create(): course_id = request.args.get('course_offering_id', None, type=int) if request.method == 'GET': if course_id is None: abort(400) db.connect() res = db.select_columns('course_offerings', ['id'], ['id'], [course_id]) if not len(res): db.close() abort(404) file_types = db.select_columns('file_types', ['name']) file_types = list(map(lambda x: x[0], file_types)) allowed_file_types = ','.join(file_types) heading = 'Create Task' default_fields = { 'task-name': '', 'deadline': '', 'task-description': '', 'submission-type': 'text', 'word-limit': '', 'maximum-file-size': '', 'accepted-file-type': '', 'marking-method': 'accept', 'criteria': [], 'task_attachments': [] } # if updating old task then load old task data old_task_id = request.args.get('update', None, type=int) if old_task_id is not None: res = queries.get_past_task_data(old_task_id) if res is not None: res = res[0] heading = 'Edit Task' # basic task details default_fields['task-name'] = res[0] time_format = '%d/%m/%Y %H:%M' due_date = datetime.fromtimestamp(res[1]) default_fields['deadline'] = due_date.strftime(time_format) default_fields['task-description'] = res[2] attachments = db.select_columns('task_attachments', ['path'], ['task'], [old_task_id]) for r in attachments: file = [FileUpload(filename=r[0])] default_fields['task_attachments'] = file # submission method specific if res[3] == 'text submission': default_fields['word-limit'] = res[4] else: default_fields['submission-type'] = 'file' default_fields['maximum-file-size'] = int(res[5]) default_fields['accepted-file-type'] = res[6] # marking method specifics if res[7] == 'requires mark': default_fields['marking-method'] = 'criteria' crit = db.select_columns('task_criteria', ['name, max_mark'], ['task'], [old_task_id]) if crit is not None: default_fields['criteria'] = crit db.close() if default_fields['maximum-file-size'] == '': default_fields['maximum-file-size'] = 5 if default_fields['accepted-file-type'] == '': default_fields['accepted-file-type'] = '.pdf' return render_template('create_task.html', heading=heading, title=heading, file_types=file_types, course_id=course_id, max_file_size=config.MAX_FILE_SIZE, max_word_limit=config.MAX_WORD_LIMIT, accepted_file_types=allowed_file_types, old_task_id=old_task_id, default_fields=default_fields) try: fields = [ 'task-name', 'deadline', 'task-description', 'submission-type', 'word-limit', 'maximum-file-size', 'accepted-file-type', 'marking-method', 'num-criteria', 'course-id', 'file-name', 'old_task_id', 'delete_old_attachment' ] task_name, deadline, task_description, submission_type, \ word_limit, max_file_size, accepted_ftype, marking_method, \ num_criteria, course_id, file_name, old_task_id, \ delete_old_attachment = \ get_fields(request.form, fields, optional=['word-limit', 'file-name'], ints=['maximum-file-size', 'num-criteria', 'word-limit', 'course-id', 'delete_old_attachment']) except ValueError as e: return e.args[0] try: old_task_id = int(old_task_id) except ValueError as e: old_task_id = None try: deadline = datetime.strptime(deadline, '%d/%m/%Y %H:%M').timestamp() except ValueError: return error('Invalid date format for deadline!') if submission_type == 'file': max_size = config.MAX_FILE_SIZE if not (1 <= max_file_size <= max_size): return error( f'Maximum file size must be between 1 and {max_size}!') elif submission_type == 'text': try: word_limit = get_fields(request.form, ['word-limit'], ints=['word-limit'])[0] except ValueError as e: return e.args[0] max_word_limit = config.MAX_WORD_LIMIT if not (1 <= word_limit <= max_word_limit): return error(f'Word limit must be between 1 and {max_word_limit}!') else: return error('Unknown submission type!') if marking_method == 'criteria': if num_criteria < 1: return error('At least one marking criterion is required!') else: criteria = [f'criteria-{i}' for i in range(1, num_criteria + 1)] marks = [f'maximum-mark-{i}' for i in range(1, num_criteria + 1)] try: criteria = get_fields(request.form, criteria) marks = get_fields(request.form, marks, ints=marks) except ValueError as e: return e.args[0] if sum([mark for mark in marks]) != 100: return error('Marks must add to 100!') elif marking_method != 'accept': return error('Unknown marking method!') db.connect() res = db.select_columns('course_offerings', ['id'], ['id'], [course_id]) if not len(res): db.close() return error('Cannot create task for unknown course!') res = db.select_columns('tasks', ['id', 'name'], ['name', 'course_offering'], [task_name, course_id]) if len(res) and res[0][0] != old_task_id: db.close() return error('A task with that name already exists in this course!') # retrieve some foreign keys for insertion res = db.select_columns('file_types', ['id'], ['name'], [accepted_ftype]) if not len(res): db.close() return error('Invalid or unsupported file type!') file_type_id = res[0][0] # upload file if present sent_file = None if len(file_name): try: sent_file = FileUpload(req=request) except KeyError: db.close() return error('Could not find a file to upload') res = db.select_columns('file_types', ['name']) file_types = list(map(lambda x: x[0], res)) if sent_file.get_extention() not in file_types: db.close() accept_files = ', '.join(file_types) return error(f'Accepted file types are: {accept_files}') if sent_file.get_size() > config.MAX_FILE_SIZE: sent_file.remove_file() db.close() return error( f'File exceeds the maximum size of {config.MAX_FILE_SIZE} MB') sent_file.commit() if (len(file_name) and old_task_id is not None) or delete_old_attachment: old = db.select_columns('task_attachments', ['path'], ['task'], [old_task_id]) if old: db.delete_rows('task_attachments', ['task'], [old_task_id]) try: prev_submission = FileUpload(filename=old[0][0]) prev_submission.remove_file() except LookupError: # If the file doesn't exists don't worry as we are deleting # the attachment anyway pass res = db.select_columns('submission_methods', ['id'], ['name'], ['{} submission'.format(submission_type)]) submission_method_id = res[0][0] marking_method = 'approval' if marking_method == 'accept' else 'mark' res = db.select_columns('marking_methods', ['id'], ['name'], ['requires {}'.format(marking_method)]) mark_method_id = res[0][0] # commit task if old_task_id is not None: # update an existing task db.update_rows('tasks', [ task_name, course_id, deadline, task_description, max_file_size, submission_method_id, mark_method_id, word_limit ], [ 'name', 'course_offering', 'deadline', 'description', 'size_limit', 'submission_method', 'marking_method', 'word_limit' ], ['id'], [old_task_id]) else: # add a new task` db.insert_single('tasks', [ task_name, course_id, deadline, task_description, max_file_size, 0, submission_method_id, mark_method_id, word_limit ], [ 'name', 'course_offering', 'deadline', 'description', 'size_limit', 'visible', 'submission_method', 'marking_method', 'word_limit' ]) res = db.select_columns('tasks', ['id'], ['name', 'course_offering'], [task_name, course_id]) task_id = res[0][0] if sent_file: db.insert_single('task_attachments', [task_id, sent_file.get_name()], ['task', 'path']) # delete old entries in other tables if old_task_id is not None: db.delete_rows('submission_types', ['task'], [old_task_id]) res = db.select_columns('task_criteria', ['id'], ['task'], [old_task_id]) for r in res: db.delete_rows('marks', ['criteria'], [r[0]]) db.delete_rows('task_criteria', ['task'], [old_task_id]) # commit accepted file type db.insert_single('submission_types', [file_type_id, task_id], ['file_type', 'task']) # commit marking criteria marking_criteria = [] if marking_method == 'approval': marking_criteria.append( ('task_criteria', [task_id, 'Approval', 100], ['task', 'name', 'max_mark'])) else: for i in range(len(criteria)): marking_criteria.append( ('task_criteria', [task_id, criteria[i], marks[i]], ['task', 'name', 'max_mark'])) db.insert_multiple(marking_criteria) db.close() return jsonify({'status': 'ok'})
def upload_material(): try: fields = [ 'file-label', 'file-name', 'course-offering', 'old-material-id', 'delete-old-file' ] file_label, file_name, course_offering, old_material_id, \ delete_old_file = \ get_fields(request.form, fields, optional=['word-limit', 'file-name'], ints=['course-offering']) except ValueError as e: return e.args[0] try: old_material_id = int(old_material_id) except ValueError as e: old_material_id = None # check if no file when there should be one if file_name == '' and \ (delete_old_file == 'true' or old_material_id is None): return error('File is required!') db.connect() # check if course offering is valid res = db.select_columns('course_offerings', ['id'], ['id'], [course_offering]) if not len(res): db.close() return error('Cannot attach material to unknown course offering') # check if material with same label exists in course res = db.select_columns('materials', ['id'], ['name', 'course_offering'], [file_label, course_offering]) if len(res) and old_material_id != res[0][0]: db.close() return error('An item with that label already exists in this course') # otherwise, we can insert the material into the course if len(file_name): try: sent_file = FileUpload(req=request) except KeyError: db.close() return error('Could not find a file to upload') res = db.select_columns('file_types', ['name']) file_types = list(map(lambda x: x[0], res)) if sent_file.get_extention() not in file_types: db.close() accept_files = ', '.join(file_types) return error(f'Accepted file types are: {accept_files}') if sent_file.get_size() > config.MAX_FILE_SIZE: sent_file.remove_file() db.close() return error( f'File exceeds the maximum size of {config.MAX_FILE_SIZE} MB') sent_file.commit() if delete_old_file == 'true': old = db.select_columns('material_attachments', ['path'], ['material'], [old_material_id]) if old: db.delete_rows('material_attachments', ['material'], [old_material_id]) try: prev_submission = FileUpload(filename=old[0][0]) prev_submission.remove_file() except LookupError: # If the file doesn't exists don't worry as we are deleting # the attachment anyway pass if old_material_id is not None: # update existing material entries db.update_rows('materials', [file_label], ['name'], ['id'], [old_material_id]) db.update_rows('materials', [file_label], ['name'], ['id'], [old_material_id]) if delete_old_file == 'true': db.insert_single( 'material_attachments', [old_material_id, sent_file.get_name()], ['material', 'path']) else: # add material and file path to db db.insert_single('materials', [course_offering, file_label, 0], ['course_offering', 'name', 'visible']) res = db.select_columns('materials', ['id'], ['name', 'course_offering'], [file_label, course_offering]) db.insert_single('material_attachments', [res[0][0], sent_file.get_name()], ['material', 'path']) db.close() return jsonify({'status': 'ok'})
def gen_enrollments2(): sessions = db.select_columns( 'sessions', ['id', 'year', 'term'], ['term'], [1] ) sessions_t2 = db.select_columns( 'sessions', ['id', 'year', 'term'], ['term'], [2] ) sessions_t3 = db.select_columns( 'sessions', ['id', 'year', 'term'], ['term'], [3] ) types = get_all_account_types() student_ids = db.select_columns( 'users', ['id'], ['account_type'], [types['student']] ) for student in student_ids: number = random.randrange(len(sessions)) ses_id = sessions[number][0] ses_year = sessions[number][1] ses_term = sessions[number][2] number = random.randrange(5) if not number: term2 = random.randrange(2) if term2: temp_sessions = sessions_t2 else: temp_sessions = sessions_t3 number = random.randrange(len(temp_sessions)) ses_id = temp_sessions[number][0] ses_year = temp_sessions[number][1] ses_term = temp_sessions[number][2] number = random.randrange(10) if ses_year < 2019: if number < 2: course_name = 'Thesis Part A+B' co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) else: course_name = 'Thesis Part A' co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) _, course_name, _ = get_next_course(course_name) ses_id, ses_year, ses_term = get_next_ses(ses_year, ses_term) if ses_year > 2020: continue co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) else: if number < 5: course_name = 'Research Thesis A' co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) _, course_name, _ = get_next_course(course_name) ses_id, ses_year, ses_term = get_next_ses(ses_year, ses_term) if ses_year > 2020: continue co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) _, course_name, _ = get_next_course(course_name) ses_id, ses_year, ses_term = get_next_ses(ses_year, ses_term) if ses_year > 2020: continue co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) else: course_name = 'Computer Science Thesis A' co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) _, course_name, _ = get_next_course(course_name) ses_id, ses_year, ses_term = get_next_ses(ses_year, ses_term) if ses_year > 2020: continue co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] ) _, course_name, _ = get_next_course(course_name) ses_id, ses_year, ses_term = get_next_ses(ses_year, ses_term) if ses_year > 2020: continue co_id = get_co_id(ses_id, course_name) db.insert_single( 'enrollments', [student[0], co_id], ['user', 'course_offering'] )
def create(): if request.method == 'GET': topic_id = request.args.get('update', None, type=int) if topic_id: db.connect() topic_info = db.select_columns('topics', ['name', 'description'], ['id'], [topic_id])[0] # 404 if no such topic id if not len(topic_info): db.close() abort(404) db.close() return render_template('create_topic.html', heading='Edit Topic', title='Edit Topic', topic_id=topic_id, topic_info=topic_info) else: return render_template('create_topic.html', heading='Create Topic', title='Create Topic') try: data = json.loads(request.data) topic = data['topic'] areas = [area['tag'] for area in data['topic_area']] prereqs = [prereq['tag'] for prereq in data['prereqs']] details = data['details'] except ValueError as e: return e.args[0] # check if there is an edit param, if there is, get the topic id update_id = request.args.get('update', None, type=str) if update_id: update_id = update_id.split('-')[2] # make sure the course codes are uppercase and strip for areas and prereqs if len(areas) == 0: return error('You must enter at least one topic area') original_prereqs = prereqs prereqs = [x.upper().strip() for x in prereqs] areas = [x.strip() for x in areas] db.connect() user_id = session['id'] # test if there is such course in the database course_ids = [] i = 0 for prereq in prereqs: course_id = db.select_columns('courses', ['id', 'prereq'], ['code'], [prereq]) if len(course_id) == 0: db.close() err_msg = f'{original_prereqs[i]} is an unknown course code!' if not re.match(config.COURSE_CODE_FORMAT, prereqs[i]): err_msg = f'{original_prereqs[i]} is an invalid course code!' return error(err_msg) if course_id[0][1] == 0: db.close() return error(f'{prereqs[i]} cannot be a prerequisite!') course_ids.append(course_id[0][0]) i += 1 if not update_id: # test if there is such topic in the database res = db.select_columns('topics', ['name'], ['name'], [topic]) # only check the name of the topic if len(res): db.close() return error('A topic with that name already exists!') # now start to insert data into db # insert topic db.insert_single('topics', [topic, user_id, details], ['name', 'supervisor', 'description']) # otherwise, update the topic name and description else: db.update_rows('topics', [topic, details], ['name', 'description'], ['id'], [update_id]) topic_id = db.select_columns('topics', ['id'], ['name'], [topic])[0][0] # if this is an update, delete all the related area and prereqs # then insert the new ones if update_id: db.delete_rows('topic_to_area', ['topic'], [topic_id]) db.delete_rows('prerequisites', ['topic'], [topic_id]) # now get topic areas for area in areas: # get area_id if area in database area_id = db.select_columns('topic_areas', ['id'], ['name'], [area]) # else add area to database and get the id if not area_id: db.insert_single('topic_areas', [area], ['name']) area_id = db.select_columns('topic_areas', ['id'], ['name'], [area]) db.insert_single('topic_to_area', [topic_id, area_id[0][0]], ['topic', 'topic_area']) else: # add to linking table db.insert_single('topic_to_area', [topic_id, area_id[0][0]], ['topic', 'topic_area']) # now insert prereqs for i in range(len(course_ids)): db.insert_single('prerequisites', [0, topic_id, course_ids[i]], ['type', 'topic', 'course']) db.close() return jsonify({'status': 'ok'})
def register(): if request.method == 'GET': return render_template('register.html', title='Register', hide_navbar=True) try: fields = ['email', 'password', 'confirm-password'] email, password, confirm = get_fields(request.form, fields) except ValueError as e: return e.args[0] if not re.match(config.EMAIL_FORMAT, email): return error( f'Invalid email format!<br>{config.EMAIL_FORMAT_ERROR}', 'email') db.connect() res = db.select_columns('users', ['email', 'date_created', 'confirm_code'], ['email'], [email]) now = datetime.now().timestamp() if len(res): if res[0][2] != '' and res[0][1] + config.ACCOUNT_EXPIRY < now: # expire unactivated accounts every 24 hours db.delete_rows('users', ['email'], [email]) else: db.close() return error('This email has already been registered!', 'email') if len(password) < 8: msg = 'Password must be at least 8 characters long!' db.close() return error(msg, 'password') if password != confirm: db.close() return error('Passwords do not match!', 'confirm-password') hashed_pass = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()) name = email.split('@')[0] # get the id for a public account acc_type = db.select_columns('account_types', ['id'], ['name'], ['public']) confirm_code = uuid.uuid1() activation_link = url_for('.confirm', user=name, confirm_code=confirm_code, _external=True) send_email(to=email, name=email, subject='Confirm Account Registration', messages=[ 'You recently registered for an account on TMS.', 'To activiate your account, click ' + f'<a href="{activation_link}">here</a>.', 'This link will expire in 24 hours.' ]) db.insert_single( 'users', [name, hashed_pass, email, acc_type[0][0], str(confirm_code), now], ['name', 'password', 'email', 'account_type', 'confirm_code', 'date_created'] ) db.close() return jsonify({'status': 'ok'})