Exemple #1
0
def students_assigned(course_id, section):
    cur = get_db().cursor()
    cur.execute("""SELECT * FROM users
                   JOIN student_sessions AS ss
                   ON users.id = ss.student_id
                   WHERE ss.course_id = %s AND ss.section = %s;""",
                (course_id, section))

    return cur.fetchall()
def test_file_upload(client):

    # Anonymous user should get redirected
    response = client.post('/course/216/session/1/assignment/2/submit')

    assert response.status_code == 302

    with client:
        client.post('/login',
                    data={
                        'email': '*****@*****.**',
                        'password': '******'
                    })
        # As a student, upload a file
        response = client.post(
            '/course/216/session/1/assignment/2/submit',
            data={'file': (io.BytesIO(b"This is a test file"), 'test.txt')},
            follow_redirects=True)
        assert response.status_code == 200
        assert b'Assignment submitted successfully' in response.data

        # Check that an uploaded file appears in the file system
        file = open('portal/uploads/1-test.txt')
        assert 'This is a test file' in file.read()
        file.close()

        # Check that a different file uploaded with the same name doesn't
        # overwrite the original file

        client.post('/login',
                    data={
                        'email': '*****@*****.**',
                        'password': '******'
                    })

        response = client.post(
            '/course/216/session/1/assignment/2/submit',
            data={'file': (io.BytesIO(b"Hello World"), 'test.txt')})

        file = open('portal/uploads/1-test.txt')
        assert 'This is a test file' in file.read()
        file.close()

        file = open('portal/uploads/3-test.txt')
        assert 'Hello World' in file.read()
        file.close()

        # Check that the filenames got added to the submissions table

        with db.get_db() as con:
            with con.cursor() as cur:

                cur.execute('SELECT * FROM submissions')
                submissions = cur.fetchall()

        assert submissions[1]['file_name'] == '1-test.txt'
        assert submissions[2]['file_name'] == '3-test.txt'
Exemple #3
0
def courses_view(cour_id):
    """Shows details of a course to teacher"""
    cur = get_db().cursor()

    cur.execute("SELECT * FROM courses WHERE teacher_id = %s AND id = %s;",
                (g.user['id'], cour_id))

    course = cur.fetchone()
    return render_template('portal/courses/viewcourse.html', course=course)
def teachers_assignments(class_list):
    for cla in class_list:
        with get_db() as con:
            with con.cursor() as cur:
                cur.execute(
                    "SELECT assignment_id, assignment_name, points_earned, points_available, instructions, due_date, sesh_id FROM assignments WHERE sesh_id  = %s",
                    (cla, ))
                teach_assignment = cur.fetchall()
                return teach_assignment
Exemple #5
0
def load_logged_in_user():
    user_id = session.get('user_id')

    if user_id is None:
        g.user = None
    else:
        g.user = get_db().execute(
            'SELECT * FROM users WHERE id = ?', (user_id,)
        ).fetchone()
Exemple #6
0
def get_student(id):
    with db.get_db() as con:
        with con.cursor() as cur:
            cur.execute("SELECT * FROM users WHERE id = %s", (id,))
            student = cur.fetchone()

    if student is None:
        abort(404)

    return student
Exemple #7
0
def get_assignment(id):
    with db.get_db() as con:
        with con.cursor() as cur:
            cur.execute("SELECT * FROM assignments WHERE id = %s", (id,))
            assignment = cur.fetchone()

    if assignment is None:
        abort(404)

    return assignment
Exemple #8
0
def test_add_user(runner, monkeypatch, app):
    with app.app_context():
        with db.get_db() as con:
            with con.cursor() as cur:
                check = cur.execute("SELECT * FROM users WHERE email = 'test'")
                assert check == None
                add_user('test', 'test', 'teacher', 'test')
                check = cur.execute("SELECT * FROM users WHERE email = 'test'")
                check = cur.fetchone()
                assert check is not None
Exemple #9
0
def course(id):
    course = get_course(id)

    if g.user['role'] == 'teacher':
        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute('SELECT * FROM assignments WHERE course_id = %s',
                            (course['id'], ))
                assignments = cur.fetchall()
                cur.execute('SELECT * FROM sessions WHERE course_id = %s',
                            (course['id'], ))
                sessions = cur.fetchall()
        return render_template('courses/course.html',
                               assignments=assignments,
                               course=course,
                               sessions=sessions)

    elif g.user['role'] == 'student':
        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute(
                    """
                SELECT a.id,
                       a.assignment_name,
                       a.course_id,
                       a.total_points,
                       s.points_scored,
                       s.student_id,
                       s.graded
                FROM assignments AS a
                JOIN submissions AS s ON a.id = s.assignment_id
                WHERE s.student_id = %s AND a.course_id = %s""", (
                        g.user['id'],
                        id,
                    ))
                assignments = cur.fetchall()

        return render_template('courses/course.html',
                               course=course,
                               assignments=assignments)

    else:
        abort(401)
Exemple #10
0
def user_assignments(course_id, section):
    cur = get_db().cursor()

    # Pulls out all assignments for the course
    cur.execute(
        """SELECT * FROM assignments
                   WHERE course_id = %s
                   AND section = %s;""", (course_id, section))

    assignments = cur.fetchall()
    return assignments
Exemple #11
0
def courses():
    """View for the courses"""
    # display the courses they own with a query
    cur = get_db().cursor()

    cur.execute("SELECT * FROM courses WHERE teacher_id = %s;",
                (g.user['id'], ))
    teacher_courses = cur.fetchall()

    return render_template('portal/courses/courses.html',
                           teacher_courses=teacher_courses)
def delete_assignment(id):
    get_assignment(id)
    with get_db() as con:
        with con.cursor() as cur:
            cur.execute(
                'DELETE FROM user_assignments WHERE assignment_id = %s', [id])
            con.commit()
            cur.execute('DELETE FROM assignments WHERE assignment_id = %s',
                        [id])
            con.commit()
    return redirect(url_for('assign.assignment_page'))
Exemple #13
0
def list_courses():
    sessions = []

    if g.user['role'] == 'teacher':
        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute('SELECT * FROM courses WHERE instructor_id = %s',
                            (g.user['id'], ))
                courses = cur.fetchall()

        for each in courses:
            with db.get_db() as con:
                with con.cursor() as cur:
                    cur.execute('SELECT * FROM sessions WHERE course_id = %s',
                                (each['id'], ))
                    sessions.append(cur.fetchall())

        return render_template('courses/list.html',
                               courses=courses,
                               sessions=sessions)

    elif g.user['role'] == 'student':
        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute(
                    """SELECT c.course_number,
                    	    c.course_title,
                    	    s.letter,
                    	    s.meets,
                            c.id
                    FROM sessions AS s
                    JOIN courses AS c ON s.course_id = c.id
                    JOIN user_sessions AS us ON s.id = us.session_id
                    WHERE us.student_id = %s""", (g.user[0], ))
                student_sessions = cur.fetchall()

        return render_template('courses/list.html',
                               student_sessions=student_sessions)

    else:
        abort(401)
Exemple #14
0
def check_graded(assign_id, student_id):
    cur = get_db().cursor()
    cur.execute("""SELECT * FROM Grades
                   WHERE assignment_id = %s
                   AND student_sessions_id = %s;""",
                (assign_id, student_id[0]))
    graded = cur.fetchall()

    if graded:
        return graded[0]
    else:
        return None
Exemple #15
0
def get_student_assignments(course_id, section):
    cur = get_db().cursor()

    # Pulls out all assignments for the course
    cur.execute("""SELECT * FROM assignments a JOIN grades g
                   ON (a.id = g.assignment_id)
                   WHERE a.course_id = %s
                   AND a.section = %s;""",
                (course_id, section))

    assignments = cur.fetchall()
    return assignments
Exemple #16
0
def test_register(client, app):
    assert client.get('/auth/register').status_code == 200
    response = client.post('/auth/register',
                           data={
                               'username': '******',
                               'password': '******'
                           })
    assert 'http://localhost/auth/login' == response.headers['Location']

    with app.app_context():
        assert get_db().execute(
            "select * from user where username = '******'", ).fetchone() is not None
Exemple #17
0
def test_author_required(app, client, auth):
    # change the post author to another user
    with app.app_context():
        db = get_db()
        db.execute('UPDATE post SET author_id = 2 WHERE id = 1')
        db.commit()

    auth.login()
    # current user can't modify other user's post
    assert client.post('/1/update').status_code == 403
    assert client.post('/1/delete').status_code == 403
    # current user doesn't see edit link
    assert b'href="/1/update"' not in client.get('/').data
Exemple #18
0
def load_logged_in_user():
    user_id = session.get('user_id')

    if user_id is None:
        g.user = None
    else:
        db = get_db()
        cur = db.cursor()
        cur.execute(
            'SELECT * FROM users WHERE id = %s', (user_id,)
        )
        g.user = cur.fetchone()
        cur.close()
Exemple #19
0
def test_delete_course(app, client, auth):
    with app.app_context():
        db = get_db()

        cur = db.cursor()

        auth.teacher_login()

        response = client.post('/deletecourse', data={'course_to_delete': 4})
        cur.execute("SELECT * FROM courses WHERE name = 'Security and Ethics';")
        check = cur.fetchone()

        assert check is None
Exemple #20
0
def get_post(id, check_author=True):
    post = get_db().execute(
        ' SELECT p.id, tittle, body, created, author_id, username'
        ' FROM post p JOIN users u ON p.author_id = u.id'
        ' WHERE p.id = ?', (id, )).fetchone()

    if post is None:
        abort(404, "Post id {0} doesn't exist.".format(id))

    if check_author and post['author_id'] != g.user['id']:
        abort(403)

    return post
def index(id):
    course = get_course(id)
    user = get_user(id)
    con = get_db()
    cur = con.cursor(cursor_factory=DictCursor)
    cur.execute("SELECT * FROM assignments WHERE course_id = %s", (id, ))
    assignments = cur.fetchall()
    cur.close()

    return render_template('/assignments/index.html',
                           assignments=assignments,
                           user=user,
                           course=course)
def input_grades(id):
    points_earned = request.form['earned']
    student = request.form['student']
    with get_db() as con:
        with con.cursor() as cur:
            print(student)
            cur.execute(
                "UPDATE user_assignments SET point_earned = %s  WHERE student_id = %s AND assignment_id  = %s",
                (
                    points_earned,
                    student,
                    id,
                ))
            con.commit()
Exemple #23
0
def app():
    db_fd, db_path = tempfile.mkstemp()

    app = create_app({
        'TESTING': True,
        'DATABASE': db_path,
    })

    with app.app_context():
        init_db()
        get_db().executescript(_data_sql)

    yield app

    os.close(db_fd)
    os.unlink(db_path)

    @pytest.fixture
    def client(app):
        return app.test_client()

    @pytest.fixture
    def runner(app):
        return app.test_cli_runner()
def test_edit_course_teacher(client, course, auth, app):
    auth.login_teacher()
    course.create('test', 'testing')
    assert client.get('courses/1/edit').status_code == 200
    client.post('courses/1/edit',
                data={
                    'course_number': 'test2',
                    'course_title': 'testing2'
                })
    with app.app_context():
        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute("SELECT * FROM courses WHERE id = 1")
                course = cur.fetchone()
    assert course['course_number'] == 'test2'
Exemple #25
0
def validate_student():
    """Return a list of valid session IDs for the logged in user"""
    with get_db() as con:
        with con.cursor() as cur:
            cur.execute("""
                SELECT s.id FROM sessions s JOIN roster r
                ON s.id = r.session_id
                WHERE r.student_id = %s
            """, (g.user['id'],))
            result = cur.fetchall()
            valid_sessions = []
            for row in result:
                valid_sessions.append(str(row['id']))

    return valid_sessions
def test_edit_sessions(client, auth):
    auth.login_teacher()
    assert client.get('sessions/1/edit').status_code == 200
    with client:
        client.post('sessions/1/edit', data = {'session_time': 'MTWRF', 'student_email': ['*****@*****.**', '*****@*****.**', '*****@*****.**', '*****@*****.**']})

        with db.get_db() as con:
            with con.cursor() as cur:
                cur.execute("SELECT * FROM sessions WHERE id = 1")
                session = cur.fetchone()
                assert session['meets'] == 'MTWRF'

                cur.execute("SELECT * FROM user_sessions WHERE session_id = 1")
                students = cur.fetchall()
                assert len(students) == 4
Exemple #27
0
def home():
    with get_db() as con:
        with con.cursor() as cur:
            cur.execute(
                """
                SELECT c.course_name, c.course_code, s.session_name, s.meeting_days,
                s.meeting_time, s.meeting_place, s.id, r.student_id
                FROM courses c JOIN sessions s
                ON c.id = s.course_id
                JOIN roster r
                ON s.id = r.session_id
                WHERE r.student_id = %s
                """, (g.user['id'], ))
            courses = cur.fetchall()
    return render_template('layouts/student/student-page.html',
                           courses=courses)
Exemple #28
0
def test_delete_sessions(app, client, auth):
    with app.app_context():
        db = get_db()

        cur = db.cursor()

        auth.teacher_login()

        response = client.post('/deletesession',
                               data={
                                   'course_id': 1,
                                   'section': 'A'
                               })
        cur.execute(
            "SELECT * FROM sessions WHERE section = 'A' and course_id = '1';")
        check = cur.fetchone()
        assert check is None
Exemple #29
0
def test_assign_submit(client, auth, app):
    auth.teacher_login()

    # GET requests are redirected
    response = client.get('/teacher/assignments/assign/submit')
    assert 'http://localhost/teacher/sessions' == response.headers['Location']

    # On POST, user should be able to insert assignments into the session_assignments
    # junction table with a due date
    response = client.post('/teacher/assignments/assign/submit',
                           data={
                               'date': '2020-05-08',
                               'session_id': 1,
                               'assign_id': 4
                           })
    with app.app_context():
        with get_db() as con:
            with con.cursor() as cur:
                cur.execute("""
                    SELECT * FROM session_assignments where assignment_id = 4 AND session_id = 1   """
                            )
                assert cur.fetchone() is not None

    # Teachers should not be able to submit assignments to sessions that they do not own
    # or assign assignments that they do not own to any session
    client.post(
        '/teacher/assignments/assign/submit',
        # The teacher does not own this session
        data={
            'date': '2020-05-08',
            'session_id': 3,
            'assign_id': 4
        })
    response = client.get('/teacher/sessions')
    assert b'Something went wrong' in response.data

    client.post(
        '/teacher/assignments/assign/submit',
        # The teacher does not own this assignment
        data={
            'date': '2020-05-08',
            'session_id': 1,
            'assign_id': 5
        })
    response = client.get('/teacher/sessions')
    assert b'Something went wrong' in response.data
Exemple #30
0
def app():
    app = create_app({
        'TESTING': True,
        'DB_NAME': 'portal_test',
        'DB_USER': '******',
    })

    with app.app_context():
        init_db()

        with open(os.path.join(os.path.dirname(__file__), 'data.sql'),
                  'rb') as f:
            with get_db() as con:
                with con.cursor() as cur:
                    cur.execute(f.read())

    yield app