def assign_submit():
"""Finalize assignment of assignments to a session with a due date"""
if request.method == 'POST':
# Get all the necessary form data
date = request.form['date']
assign_id = request.form['assign_id']
session_id = request.form['session_id']
# Validate all of the data
if (validate(assign_id, 'assignments')
and validate(session_id, 'sessions') and validate_date(date)):
# If validation succeeds, add the new session assignment to the database
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
INSERT INTO session_assignments (session_id, assignment_id, due_date)
VALUES (%s, %s, %s)
""", (session_id, assign_id, date))
# Add a default assignment grade for each student of 0
cur.execute(
"""
INSERT INTO assignment_grades(owner_id, assigned_id, grades)
VALUES((SELECT DISTINCT student_id FROM roster WHERE session_id = %s),
(SELECT DISTINCT work_id FROM session_assignments WHERE session_id = %s AND assignment_id = %s),
%s);
SELECT * FROM assignment_grades
""", (session_id, session_id, assign_id, 0))
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
return redirect(url_for('teacher.sessions'))
def session_remove():
"""Remove students from a session roster during creation or editing"""
if request.method == 'POST':
if session.get('class_session'):
ids = []
error = None
# Confirm that each checked box is a valid student user
for id in request.form.getlist('id'):
if validate(id, 'users'):
ids.append(int(id))
else:
# If any id fails validation, prepare an error and stop looping
error = "Something went wrong."
break
# If validation passes, delete students from the roster in the DB
if not error:
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
DELETE FROM roster
WHERE student_id = ANY(%s) AND session_id = %s
""", (ids, session['class_session']))
else:
# If validation fails, prepare an error to be shown to the user
flash(error)
if not session.get('edit'):
return redirect(url_for('teacher.make_session'))
else:
return redirect(url_for('teacher.session_edit'))
def submit_assignments():
"""Finalize edits on an assignment and update the database"""
if request.method == 'POST':
# Grab all the necessary form data
name = request.form['name']
desc = request.form['description']
points = request.form['points']
id = request.form['submit']
# Validate all of the submitted data
if (validate(id, 'assignments') and validate_text(name, 50)
and validate_text(desc, 300)
and validate_number(points, 100000)):
# If validation passes, update the database
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
UPDATE assignments
SET name = %s, description = %s, points = %s
WHERE id = %s
""", (name, desc, points, id))
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
return redirect(url_for('teacher.assignments'))
def view_assignments():
# TODO: add docstring / comments
if request.method == 'POST':
code = request.form['view-grade']
if validate(code, 'sessions'):
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT a.name, a.description, a.points, c.course_name, a.id, p.work_id
FROM sessions s JOIN session_assignments p
ON s.id = p.session_id
JOIN assignments a
ON p.assignment_id = a.id
JOIN courses c
ON c.id = s.course_id
WHERE s.id = %s
""", (code, ))
assignments = cur.fetchall()
return render_template(
'layouts/teacher/assignments/view-assignments.html',
assignments=assignments)
else:
flash("Something went wrong.")
return redirect(url_for('teacher.courses'))
def grade():
"""Display a students assignment information so a teacher can grade the student"""
if request.method == 'POST':
# Get the assignment_id from the form
code = request.form['grade']
# Validate that a correct id is being used
if validate(code, 'assignments'):
# If validation succeeds, select neccessary data for an assignment to allow a teacher to give a student a grade
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT r.name, r.description, r.points, u.first_name, u.last_name, u.id, a.work_id
FROM session_assignments a JOIN assignments r
ON a.assignment_id = r.id
JOIN roster d
ON a.session_id = d.session_id
JOIN users u
ON d.student_id = u.id
WHERE a.assignment_id = %s
""", (code, ))
informations = cur.fetchall()
return render_template(
'layouts/teacher/assignments/teacher-assignments.html',
informations=informations)
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
return redirect(url_for('teacher.courses'))
def assign_work():
"""Display all of the assignments that can be assigned to selected session"""
if request.method == 'POST':
# Get the id for the target session from the form
session_id = request.form['session_id']
# Confirm that the teacher owns the target session
if validate(session_id, 'sessions'):
# On validation success, collect all valid assignments for target session
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT * FROM assignments
WHERE course_id IN (SELECT course_id FROM sessions WHERE id = %s)
AND id NOT IN (SELECT assignment_id FROM session_assignments
WHERE session_id = %s)
""", (session_id, session_id))
assigns = cur.fetchall()
return render_template(
'layouts/teacher/assignments/assign-work.html',
assigns=assigns,
session_id=session_id)
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
return redirect(url_for('teacher.sessions'))
def view_assignments():
"""Display a list of assignments for a specific session"""
if request.method == 'POST':
# Get the session_id from the form
code = request.form['view-grade']
# Validate that a valid session id is being used
if validate(code, 'sessions'):
# If validation succeeds, grab the details for each assignment that belongs to a specific session
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT a.name, a.description, a.points, c.course_name, a.id, p.work_id
FROM sessions s JOIN session_assignments p
ON s.id = p.session_id
JOIN assignments a
ON p.assignment_id = a.id
JOIN courses c
ON c.id = s.course_id
WHERE s.id = %s
""", (code, ))
assignments = cur.fetchall()
return render_template(
'layouts/teacher/assignments/view-assignments.html',
assignments=assignments)
else:
# If validation fails, prepare an error to be shown to the user
flash("Something went wrong.")
return redirect(url_for('teacher.courses'))
def grade():
# TODO: add docstring / comments
if request.method == 'POST':
code = request.form['grade']
if validate(code, 'assignments'):
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT r.name, r.description, r.points, u.first_name, u.last_name, u.id, a.work_id
FROM session_assignments a JOIN assignments r
ON a.assignment_id = r.id
JOIN roster d
ON a.session_id = d.session_id
JOIN users u
ON d.student_id = u.id
WHERE a.assignment_id = %s
""", (code, ))
informations = cur.fetchall()
return render_template(
'layouts/teacher/assignments/teacher-assignments.html',
informations=informations)
else:
flash('Something went wrong.')
return redirect(url_for('teacher.courses'))
def sessions():
"""Display a list of sessions that the logged-in user owns and can delete"""
if request.method == 'POST':
items = []
error = None
# Collect all the checked form items and validate ownership
for item in request.form.getlist('id'):
if validate(item, 'sessions'):
items.append(int(item))
else:
# If validation fails, set an error and stop the loop
error = 'Something went wrong.'
break
# If all validation succeeds, delete selected sessions from the DB
if not error:
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
DELETE FROM sessions
WHERE id = ANY(%s)
""", (items, ))
else:
# If validation fails, prepare an error to be shown to the user
flash(error)
# Grab all sessions that the teacher owns from the DB along with associated
# course information
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT s.id, s.session_name, c.course_code, c.course_name, c.major
FROM sessions s JOIN courses c
ON s.course_id = c.id
WHERE c.teacher_id = %s
ORDER BY c.major, s.session_name
""", (g.user['id'], ))
sessions = cur.fetchall()
return render_template('layouts/teacher/sessions/sessions.html',
sessions=sessions)
def assignments():
"""Display owned assignments to logged in teachers and allow deletion of assignments"""
if request.method == 'POST':
items = []
error = None
# Collect and validate each checked item from form
for item in request.form.getlist('id'):
if validate(item, 'assignments'):
items.append(int(item))
else:
# If any id fails validation, set an error and stop looping
error = 'Something went wrong.'
break
# If validation passes, delete selected items from DB
if not error:
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
DELETE FROM assignments
WHERE id = ANY(%s)
""", (items, ))
else:
# If validation fails, prepare an error to be shown to the user
flash(error)
# Grab assignment information from the database
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT c.course_name, a.name, a.id
FROM assignments a JOIN courses c
ON a.course_id = c.id
WHERE c.teacher_id = %s
ORDER BY a.id
""", (g.user['id'], ))
assignments = cur.fetchall()
return render_template('layouts/teacher/assignments/assignments.html',
assignments=assignments)
def course_edit(id):
"""Update course information using user input as data"""
if validate(id, 'courses'):
if request.method == "POST":
class_code = request.form['code']
class_name = request.form['name']
class_subject = request.form['major']
class_description = request.form['description']
if (
validate_text(class_code, 10) and
validate_text(class_name, 50) and
validate_text(class_subject, 5) and
validate_text(class_description, 150)
):
# If all data is successfully validated, update the DB
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("""
UPDATE courses
SET course_code = %s, course_name = %s , major= %s, description= %s, teacher_id= %s
WHERE id = %s
""",
(class_code, class_name, class_subject, class_description, g.user['id'], id)
)
return redirect(url_for('teacher.courses'))
else:
# If validation fails, prepare an error to be shown to the user
flash("Something went wrong.")
# Grab the current course information so the user knows what they're changing
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("""
SELECT * FROM courses
WHERE id = %s
""", (id,))
course = cur.fetchone()
return render_template('layouts/teacher/courses/edit-course.html', course=course)
return redirect(url_for('teacher.courses'))
def create_assignments():
"""Display a form for creating new assignments and create them using user input"""
if request.method == 'POST':
# Collect the necessary form data
name = request.form['name']
desc = request.form['description']
points = request.form['points']
course = request.form['course']
# Validate the collected data
if (validate_text(name, 50) and validate_text(desc, 300)
and validate_number(points, 100000)
and validate(course, 'courses')):
# If validation succeeds, create a new record in the database
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
INSERT INTO assignments (name, description, points, course_id)
VALUES (%s, %s, %s, %s)
""", (name, desc, points, course))
return redirect(url_for('teacher.assignments'))
else:
# If validation fails, prepare an error to be shown to the user
flash("Something went wrong.")
# Grab all of the logged in teacher's course information for select input
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT * FROM courses
WHERE teacher_id = %s
""", (g.user['id'], ))
courses = cur.fetchall()
return render_template(
'layouts/teacher/assignments/create-assignments.html', courses=courses)
def courses():
"""Return a view that contains course information that can be deleted on POST"""
if request.method == 'POST':
ids =[]
error = None
# Get all ids from checked boxes and validate them
for id in request.form.getlist('id'):
if validate(id, 'courses'):
ids.append(int(id))
else:
# If any id fails validation, set an error and stop looping
error = "Something went wrong."
break
if not error:
# If the validation went through, open a database connection and delete
# the selected courses
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("""
DELETE FROM courses
WHERE id = ANY(%s)
""", (ids,))
else:
# If the validation produced an error, prepare it to be shown to the user
flash(error)
# Open a DB connection and grab all the course information for logged in teacher
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("""
SELECT * FROM courses
WHERE teacher_id = %s
""", (g.user['id'],))
courses = cur.fetchall()
return render_template('layouts/teacher/courses/courses.html', courses=courses)
def edit_assignments():
"""Collect information on selected assignment to display edit form"""
if request.method == 'POST':
# Get the requested assignment ID from the form
assignment_id = request.form['edit']
# Check that the requested assignment belongs to the logged-in teacher
if validate(assignment_id, 'assignments'):
# Get the assignment information from the database
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT * FROM assignments
WHERE id = %s
""", (assignment_id, ))
info = cur.fetchone()
return render_template(
'layouts/teacher/assignments/edit-assignments.html', info=info)
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong')
return redirect(url_for('teacher.assignments'))
def session_add():
"""Add students to a session roster during creation or editing"""
if request.method == 'POST':
if session.get('class_session'):
ids = []
error = None
# Confirm that every checked box id is for a valid student
for id in request.form.getlist('id'):
if validate(id, 'users'):
ids.append(int(id))
else:
# If any id is not valid, set an error and stop the loop
error = "Something went wrong."
break
# If validation completes successfully, add all of the students to
# to the roster
if not error:
with db.get_db() as con:
with con.cursor() as cur:
for id in ids:
cur.execute(
"""
INSERT INTO roster (student_id, session_id)
VALUES (%s, %s)
""", (id, session['class_session']))
else:
# If validation fails, prepare an error to be shown to the user
flash(error)
if not session.get('edit'):
return redirect(url_for('teacher.make_session'))
else:
return redirect(url_for('teacher.session_edit'))
def session_edit():
"""Set global session edit state and show the session edit form"""
if request.method == "POST":
# Get target session id from form
session_id = request.form['edit']
# Confirm that the logged-in teacher owns the session
if validate(session_id, 'sessions'):
# Set global session values to create session editing state
session['class_session'] = session_id
session['edit'] = True
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
# If the session is in the edit state, fetch info from the DB to be shown
# on the editing form
if session.get('edit'):
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT * FROM sessions s JOIN courses c
ON s.course_id = c.id
WHERE s.id = %s
""", (session['class_session'], ))
session_info = cur.fetchone()
# As with creation, all students should be available to be added to
# the roster for Gen. Ed. classes
if session_info['course_id'] == 'GEN':
cur.execute(
"""
SELECT last_name, first_name, id FROM users
WHERE role = 'student'
AND id NOT IN (SELECT student_id FROM roster WHERE session_id = %s)
""", (session['class_session'], ))
else:
cur.execute(
"""
SELECT last_name, first_name, id FROM users
WHERE role = 'student' AND major = %s
AND id NOT IN (SELECT student_id FROM roster WHERE session_id = %s)
""", (session_info['major'], session['class_session']))
students = cur.fetchall()
cur.execute(
"""
SELECT u.last_name, u.first_name, u.id FROM roster r JOIN users u
ON r.student_id = u.id
WHERE r.session_id = %s
""", (session['class_session'], ))
roster = cur.fetchall()
return render_template('layouts/teacher/sessions/edit-sessions.html',
session_info=session_info,
students=students,
roster=roster)
return redirect(url_for('teacher.home'))
def make_session():
"""Begin creation of a course session"""
if request.method == "POST":
# Obtain id of parent course for session
course_id = request.form['course_id']
# Validate that the teacher owns the parent course
if validate(course_id, 'courses'):
# Add the course ID to the global HTTP session variable
session['course_id'] = course_id
# Add an incomplete session entry to the database to enable roster creation;
# then, select that new session.
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
INSERT INTO sessions (course_id)
VALUES (%s);
SELECT * FROM sessions
WHERE course_id = %s
ORDER BY id DESC
""", (course_id, course_id))
# Get the new session ID and add it to the global session
# variable to create a session-creation state
session_id = cur.fetchone().get('id')
session['class_session'] = session_id
else:
# If validation fails, prepare an error to be shown to the user
flash('Something went wrong.')
# Check that session creation is currently underway
if session.get('class_session'):
# Get course information, valid student users, and students currently on
# the session roster from the database
with db.get_db() as con:
with con.cursor() as cur:
# Grab the parent course information from the database
cur.execute(
"""
SELECT * FROM courses
WHERE id = %s
""", (session['course_id'], ))
course = cur.fetchone()
# If the parent course major is general education, select all students
# not already on the roster; otherwise, select all students with the
# same major as the parent course who are not already on the roster
if course['major'] == 'GEN':
cur.execute(
"""
SELECT * FROM users
WHERE role = 'student' and id NOT IN (SELECT student_id from ROSTER WHERE session_id = %s)
""", (session['class_session'], ))
else:
cur.execute(
"""
SELECT * FROM users
WHERE major = %s AND role = 'student' AND id NOT IN (SELECT student_id FROM roster WHERE session_id = %s)
""", (course['major'], session['class_session']))
students = cur.fetchall()
# Grab the current roster for the session
cur.execute(
"""
SELECT u.last_name, u.first_name, u.id FROM roster r JOIN users u
ON r.student_id = u.id
WHERE r.session_id = %s
""", (session['class_session'], ))
roster = cur.fetchall()
return render_template('layouts/teacher/sessions/create-sessions.html',
students=students,
roster=roster)
else:
return redirect(url_for('teacher.courses'))
def test_validate():
# Validate called with an unexpected table name should return False without
# attempting to connect to database
assert not validate(5, 'nonetable')
