def manage(): if request.method == 'GET': db.connect() curr_topics = queries.get_staff_curr_topics(session['user']) curr_topics = clean_topic_tuples(curr_topics) db.close() return render_template('manage_topics.html', heading='Manage Topics', title='Manage Topics', curr_topics=curr_topics) data = json.loads(request.data) topic = [] visible = [] for x in data: topic.append(x) visible.append(data[x]) db.connect() for i in range(len(topic)): db.update_rows('topics', [visible[i]], ['visible'], ['name'], [topic[i]]) db.close() return jsonify({'status': 'ok'})
def allowed_file_access(filename): ''' Check if a file access should be permitted ''' if 'user' not in session: raise KeyError('Not logged in') if not is_at_least_role(UserRole.STUDENT): # public users shouldn't have access to any file uploads return False if is_at_least_role(UserRole.STAFF): # allow staff to have access to anything return True # students should only have access to files they have submitted # or files in tasks within courses they are part of # or material files within courses they are part of # as long as the task and/or material is marked as visible try: name = FileUpload(filename=filename).get_name() except LookupError as e: if config.DEBUG: print(f'Request file: {e}') abort(404) db.connect() submitted_file = db.select_columns('submissions', ['path'], ['student', 'path'], [session['id'], name]) task_files = queries.get_allowed_task_attachments(session['id']) materials = queries.get_allowed_material_attachments(session['id']) db.close() if submitted_file or (task_files and name in task_files) or \ (materials and name in materials): return True else: return False
def delete_task(): data = json.loads(request.data) task_id = data['taskId'] db.connect() submissions = db.select_columns('submissions', ['name'], ['task'], [task_id]) if submissions: db.close() return error('Cannot delete this task!' + '<br>Students have already made submissions') task_path = db.select_columns('task_attachments', ['path'], ['task'], [task_id]) if task_path: file_upload = FileUpload(filename=task_path[0][0]) file_upload.remove_file() db.delete_rows('tasks', ['id'], [task_id]) db.delete_rows('task_attachments', ['task'], [task_id]) db.delete_rows('task_criteria', ['task'], [task_id]) db.delete_rows('allowed_files', ['task'], [task_id]) db.delete_rows('submission_types', ['task'], [task_id]) db.close() return jsonify({'status': 'ok', "message": "Task deleted"})
def task_status(): db.connect() task = build_task(request.args.get('task_id', -1, type=int)) if not task: db.close() return error("Could not find task") students = get_student_statuses(task) db.close() return jsonify({'status': 'ok', 'students': students})
def staff_dashboard(): db.connect() curr_requests = [{'stu_id': r[0], 'topic_id': r[1], 'stu_name': r[2], 'stu_email': r[3], 'topic_name': r[4], 'date_created': r[5]} for r in queries.get_curr_topic_requests(session['user'])] curr_requests = sorted(curr_requests, key=lambda i: i['date_created'], reverse=True) # the way of deciding between current and past students # is by testing start/end date and current unix timestamp curr_students = [] past_students = [] # get students who I am supervising super_students = queries.get_current_super_students(session['user']) # get students who I am assessing assess_students = queries.get_current_assess_students(session['user']) # now group up the students & role types for tup_student in super_students: i = list(tup_student) i.append('Supervisor') if datetime.now().timestamp() < i.pop(4): curr_students.append(i) else: past_students.append(i) for tup_student in assess_students: i = list(tup_student) i.append('Assessor') if datetime.now().timestamp() < i.pop(4): curr_students.append(i) else: past_students.append(i) curr_students.sort(key=lambda x: zid_sort(x[1])) past_students.sort(key=lambda x: zid_sort(x[1])) # for the approve/reject topic dropdown potential_assessors = filter(lambda s: s['id'] != session['id'], queries.get_users_of_type('supervisor') + queries.get_users_of_type('course_admin')) db.close() return render_template('home_staff.html', heading='My Dashboard', title='My Dashboard', curr_requests=curr_requests, curr_students=curr_students, past_students=past_students, assessors=potential_assessors)
def delete_topic(): data = json.loads(request.data) topic_id = data['topicId'] db.connect() db.delete_rows('topics', ['id'], [topic_id]) db.delete_rows('topic_to_area', ['topic'], [topic_id]) db.delete_rows('announcements', ['topic'], [topic_id]) db.delete_rows('prerequisites', ['topic'], [topic_id]) db.close() return jsonify({'status': 'ok', "message": "Topic deleted"})
def fill_create_course(): if not is_at_least_role(UserRole.COURSE_ADMIN): return dict() db.connect() _, end_year = queries.get_year_range() start_year = datetime.datetime.now().year num_terms = queries.get_terms_per_year(start_year) db.close() return dict(start_year=start_year, end_year=end_year, num_terms=num_terms)
def view_submission(): student_id = request.args.get('submissions', None, type=int) if student_id is None: abort(400) db.connect() student_info = db.select_columns('users', ['name', 'email'], ['id'], [student_id]) if not len(student_info): db.close() abort(404) # get tasks for this student tasks = [] student_tasks = queries.get_student_submissions(student_id) for task in student_tasks: submit_date_text = timestamp_to_string(task[4], True) file_url = None if task[3]: file_url = FileUpload(filename=task[3]).get_url() status = get_sub_status(student_id, task[0]) if 'approval' in task[2]: tasks.append((task[1], submit_date_text, status, file_url, task[0], student_id)) else: criteria = db.select_columns('task_criteria', ['id', 'max_mark'], ['task'], [task[0]]) staff_mark = 0 total_max_mark = 0 for c in criteria: total_max_mark += c[1] mark = db.select_columns('marks', ['mark'], ['criteria', 'student', 'marker'], [c[0], student_id, session['id']]) if len(mark) != 0: staff_mark += mark[0][0] else: staff_mark = -1 if staff_mark <= 0: staff_mark = '- ' tasks.append((task[1], submit_date_text, str(staff_mark) + '/' + str(total_max_mark), file_url, task[0], student_id)) db.close() zid = student_info[0][1].split('@')[0] heading = f'Submissions - {student_info[0][0]} ({zid})' return render_template('submission_staff.html', heading=heading, title=heading, submissions=tasks)
def upload_enroll_user(): data = json.loads(request.data) if 'table' in data and data['table'] == 'update_account_types': db.connect() if not re.match(config.EMAIL_FORMAT, data['email']): db.close() return error(f"""Invalid email address<br> {config.EMAIL_FORMAT_ERROR}""") update_account_type(data['email'], data['name'], data['account_type'], session['current_co']) db.close() return jsonify({'status': 'ok'})
def update_course_offering(): data = json.loads(request.data) db.connect() course_id = data[0]['value'] session_id = data[1]['value'] res = db.select_columns('course_offerings', ['id'], ['session', 'course'], [session_id, course_id]) db.close() if len(res) > 0: session['current_co'] = res[0][0] return jsonify({'status': 'ok'}) else: return error("Failed to find course offering")
def check_delete_task(): data = json.loads(request.data) task_id = data['taskId'] db.connect() submissions = db.select_columns('submissions', ['name'], ['task'], [task_id]) db.close() if submissions: msg = 'Cannot delete this task!<br>' \ + 'Students have already made submissions' return error(msg) return jsonify({'status': 'ok', "message": "Task deleted"})
def lookup_request(): data = json.loads(request.data) if session['acc_type'] == 'student' and \ session['id'] != data['student_id']: return error('Lookup failure') db.connect() topic_req = queries.lookup_topic_request(data.get('student_id', -1), data.get('topic_id', -1))[0] db.close() # Convert to ms for javascript topic_req['reqDate'] = topic_req['reqDate'] * 1000 return jsonify(topic_req)
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 delete_material(): data = json.loads(request.data) material_id = data['materialId'] db.connect() material_path = db.select_columns('material_attachments', ['path'], ['material'], [material_id]) if material_path: file_upload = FileUpload(filename=material_path[0][0]) file_upload.remove_file() db.delete_rows('materials', ['id'], [material_id]) db.delete_rows('material_attachments', ['material'], [material_id]) db.close() return jsonify({'status': 'ok', "message": "Material deleted"})
def confirm(): confirm_code = request.args.get('confirm_code', '') user = request.args.get('user', '') db.connect() # get the user's confirm code & creation date res = db.select_columns( 'users', ['confirm_code', 'date_created', 'email', 'id'], ['name'], [user] ) expired = False now = datetime.now().timestamp() if len(res) and res[0][1] + config.ACCOUNT_EXPIRY < now: expired = True # expire unactivated accounts every 24 hours db.delete_rows('users', ['name'], [user]) flash('This activation link has expired!<br>' + 'You must register your account again.', 'error') if not expired and len(res) and confirm_code == res[0][0]: # clear confirm code to "mark" account as activated user_id = res[0][3] res = db.select_columns( 'update_account_types', ['id', 'new_name', 'account_type', 'course_offering'], ['email'], [res[0][2]] ) if len(res) > 0: db.update_rows( 'users', ['', res[0][1], res[0][2]], ['confirm_code', 'name', 'account_type'], ['name'], [user] ) if res[0][3] is not None: account_types = get_all_account_types() course_role = 'staff' if account_types['student'] == res[0][2]: course_role = 'student' course_role_id = db.select_columns( 'course_roles', ['id'], ['name'], [course_role] ) enroll_user(user_id, res[0][3], course_role_id[0][0]) db.delete_rows('update_account_types', ['id'], [res[0][0]]) else: db.update_rows('users', [''], ['confirm_code'], ['name'], [user]) flash('Account activated! You can now log in.', 'success') db.close() return redirect(url_for('.login'))
def get_chips(): topic_id = request.args.get('update', None, type=str) if topic_id: topic_id = topic_id.split('-')[2] db.connect() topic_areas = db.select_columns('topic_areas', ['name']) prereqs = db.select_columns('courses', ['code'], ['prereq'], [1]) chips_topic = {} for topic in topic_areas: chips_topic[topic[0]] = None chips_prereqs = {} for prereq in prereqs: chips_prereqs[prereq[0]] = None if topic_id: old_areas = [] old_prereqs = [] areas = queries.get_topic_areas(topic_id) prereqs = queries.get_prereqs_by_topic(topic_id) for area in areas: old_areas.append({'tag': area[0]}) for prereq in prereqs: old_prereqs.append(({'tag': prereq[0]})) db.close() return jsonify({ 'status': 'ok', 'chips_topic': chips_topic, 'chips_prereqs': chips_prereqs, 'old_areas': old_areas, 'old_prereqs': old_prereqs }) else: db.close() return jsonify({ 'status': 'ok', 'chips_topic': chips_topic, 'chips_prereqs': chips_prereqs })
def get_sessions(): data = json.loads(request.data) db.connect() res = db.select_columns('course_offerings', ['session'], ['course'], [data]) sessions = [] for co in res: ses_details = db.select_columns('sessions', ['year', 'term'], ['id'], [co[0]])[0] if ses_details[0] < 2019: ses_string = str(ses_details[0])[2:] + 'S' + str(ses_details[1]) else: ses_string = str(ses_details[0])[2:] + 'T' + str(ses_details[1]) sessions.append((ses_string, co[0])) db.close() if len(session) > 0: return jsonify({'status': 'ok', 'data': sessions}) else: return error("Failed to find sessions")
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 upload_enroll_file(): try: enroll_file = FileUpload(req=request) except KeyError: return error('Could not find a file to upload') if enroll_file.get_extention() != '.csv': return error('File type must be in .csv format') if enroll_file.get_size() > config.MAX_FILE_SIZE: return error( f'File exceeds the maximum size of {config.MAX_FILE_SIZE} MB') enroll_file.commit() db.connect() error_string = update_from_file(enroll_file.get_path(), session['current_co'], 'student') db.close() enroll_file.remove_file() if error_string != "": return error(error_string) return jsonify({'status': 'ok'})
def check_delete_topic(): data = json.loads(request.data) topic_id = data['topicId'] db.connect() # checking if a student has been enrolled in topic student_topic = db.select_columns('student_topic', ['student'], ['topic'], [topic_id]) if student_topic: db.close() return error( 'Cannot delete this topic!<br>There are enrolled students') # checking if a there is any pending topic requests pending = 'pending' pending_id = db.select_columns('request_statuses', ['id'], ['name'], [pending]) topic_request = db.select_columns('topic_requests', ['student'], ['topic', 'status'], [topic_id, pending_id[0][0]]) if topic_request: db.close() return error( 'Cannot delete this topic!<br>There are pending topic requests.') db.close() return jsonify({'status': 'ok', "message": "Topic deleted"})
def get_chips(): db.connect() topic_area = db.select_columns('topic_areas', ['name']) account_types = get_all_account_types() supervisors = db.select_columns('users', ['name'], ['account_type'], [account_types['supervisor']]) supervisors += db.select_columns('users', ['name'], ['account_type'], [account_types['course_admin']]) chips_topic_area = {} for topic in topic_area: chips_topic_area[topic[0]] = None chips_supervisor = {} for sup in supervisors: chips_supervisor[sup[0]] = None db.close() return jsonify({ 'status': 'ok', 'chipsTopic': chips_topic_area, 'chipsSuper': chips_supervisor })
def reset_request(): try: fields = ['email_reset'] email = get_fields(request.form, fields) email = email[0] 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}') db.connect() res = db.select_columns('users', ['name', 'id'], ['email'], [email]) if len(res) == 0: db.close() return jsonify({'status': 'ok'}) reset_id = str(uuid.uuid1()) db.update_rows('users', [reset_id], ['reset_code'], ['id'], [res[0][1]]) reset_link = url_for('.reset', user=res[0][1], resetID=reset_id, _external=True) send_email( to=email, name=res[0][0], subject='Reset Password', messages=[ 'You have submitted a request ' + 'to reset your password on TMS.', f'Your account is {email}.', f'Click <a href="{reset_link}">here</a>' + ' to reset your password.' ]) db.close() return jsonify({'status': 'ok'})
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 change_user_password(): try: fields = ['new-password', 'new-confirm-password'] password, confirm_pass = get_fields(request.form, fields) except ValueError as e: return e.args[0] if len(password) < 8: return error('Password must be at least 8 characters long!', 'new-password') if password != confirm_pass: return error('Passwords do not match!', 'new-confirm-password') acc_id = session['id'] hash_pass = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()) db.connect() db.update_rows('users', [hash_pass], ['password'], ['id'], [acc_id]) db.close() return jsonify({'status': 'ok'})
def login(): if 'user' in session: # if already logged in, redirect to home page return redirect(url_for('home.dashboard')) if request.method == 'GET': return render_template('login.html', title='Login', hide_navbar=True) try: email, password = get_fields(request.form, ['email', 'password']) except ValueError as e: return e.args[0] db.connect() res = db.select_columns('users', ['password', 'account_type', 'id', 'name', 'confirm_code'], ['email'], [email]) if not len(res): db.close() return error('Unknown email!', 'email') hashed_password = res[0] if not bcrypt.checkpw(password.encode('utf-8'), hashed_password[0]): db.close() return error('Incorrect password!', 'password') if res[0][4] != '': db.close() return error('You must first confirm your account!') # get the current user's account type acc_type = db.select_columns('account_types', ['name'], ['id'], [res[0][1]])[0][0] session['user'] = email session['name'] = res[0][3] session['id'] = res[0][2] session['acc_type'] = acc_type db.close() return jsonify({'status': 'ok'})
def reset(): if request.method == 'GET': user_id = request.args.get('user', None) reset_id = request.args.get('resetID', None) db.connect() res = db.select_columns('users', ['id'], ['reset_code'], [reset_id]) db.close() if not len(res) or user_id != str(res[0][0]): return redirect(url_for('auth.login')) return render_template('reset_password.html', title='Reset Password', hide_navbar=True, user_id=user_id, reset_id=reset_id) data = json.loads(request.data) user_id = data['user_id'] reset_id = data['reset_id'] new_pass = data['new_pass'] new_confirm = data['new_confirm'] if len(new_pass) < 8: return error('Password must be at least 8 characters long!', 'new-password') if new_pass != new_confirm: return error('Passwords do not match!', 'new-confirm-password') db.connect() reset_id_test = db.select_columns('users', ['reset_code'], ['id'], [user_id])[0][0] hash_pass = bcrypt.hashpw(new_pass.encode('utf-8'), bcrypt.gensalt()) if reset_id == reset_id_test: db.update_rows('users', [hash_pass, ''], ['password', 'reset_code'], ['id'], [user_id]) else: db.close() return error('You are not allowed to change this password!') db.close() return jsonify({'status': 'ok'})
def task_info(): db.connect() task_id = request.args.get('task_id', None, type=int) if not task_id: db.close() return abort(400) task = build_task(task_id) if not task: db.close() return abort(404) students = get_student_statuses(task) for s in students: if s['submission_date']: s['submission_date'] = timestamp_to_string(s['submission_date']) db.close() students.sort(key=lambda x: zid_sort(x['email'])) return render_template('task_stats.html', task=task, students=students, heading=f"{task['name']} - Statistics", title=f"{task['name']} - Statistics")
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 exportMarks(): db.connect() data = json.loads(request.data) studentIds = data['studentIds'] taskIds = data['taskIds'] details = {} ass_and_sup = [] for ids in studentIds: student_query = db.select_columns('users', ['name', 'email', 'id'], ['id'], [ids]) ass_and_sup_query = queries.get_user_ass_sup(ids) if ass_and_sup_query: ass_and_sup = (ass_and_sup_query[0][0], ass_and_sup_query[0][1]) else: ass_and_sup = (None, None) for task in taskIds: task_query = db.select_columns('tasks', ['name', 'id'], ['id'], [task]) ass_name = db.select_columns('users', ['name'], ['id'], [ass_and_sup[0]]) super_name = db.select_columns('users', ['name'], ['id'], [ass_and_sup[1]]) if not ass_name: ass_name = [('Not Assigned', )] if not super_name: super_name = [('Not Assigned', )] details[(student_query[0][2], task_query[0][1])] = [ student_query[0][0], student_query[0][1].split('@')[0], task_query[0][0], '-', '-', ass_and_sup[0], ass_and_sup[1], task_query[0][1], ass_name[0][0], super_name[0][0] ] task_criteria = [] for task in taskIds: task_criteria_query = db.select_columns('task_criteria', ['id', 'task'], ['task'], [task]) for crit in task_criteria_query: task_criteria.append(crit) for crit in task_criteria: for student in studentIds: # assessor if (details[(student, crit[1])][5] is not None): marks_query = db.select_columns( 'marks', ['mark'], ['criteria', 'student', 'marker'], [crit[0], student, details[(student, crit[1])][5]]) if marks_query: if (details[(student, crit[1])][3] == '-'): details[(student, crit[1])][3] = marks_query[0][0] else: details[(student, crit[1])][3] = details[(student, crit[1])][3]\ + marks_query[0][0] # supervisor if (details[(student, crit[1])][6] is not None): marks_query = db.select_columns( 'marks', ['mark'], ['criteria', 'student', 'marker'], [crit[0], student, details[(student, crit[1])][6]]) if marks_query: if (details[(student, crit[1])][4] == '-'): details[(student, crit[1])][4] = marks_query[0][0] else: details[(student, crit[1])][4] = details[(student, crit[1])][4]\ + marks_query[0][0] db.close() return jsonify({'status': 'ok', 'details': list(details.values())})
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'})