def specificCourse(courseId: int):
'''Page for a specified course, dinamically generated'''
dbCurr = db.cursor()
# POST request = a user is trying to enroll in the course
if request.method == 'POST':
if 'email' in session:
dbCurr.execute("INSERT INTO Enrollment VALUES (?, ?, ?)",
(session['email'], courseId, util.getTimestamp()))
flash("You have been enrolled successfully", category='success')
else:
flash("You need to login in order to enroll in this course!",
category='danger')
dbCurr.close()
return redirect(request.url)
# GET request
videos = []
# getting course name
dbCurr.execute("SELECT name FROM Course WHERE id=?", (courseId, ))
if (courseName := dbCurr.next()) == None:
flash("The course you are trying to access doesn't exist!",
category='danger')
dbCurr.close()
return redirect(url_for('courses.homepage'))
def specificLesson(courseId: int, lessonId: int):
'''Page for a specified lesson, displays video'''
# user is not logged in
if not 'email' in session:
flash("You need to login in order to see this lesson",
category='warning')
return redirect(url_for('courses.specificCourse', courseId=courseId))
if (not session.get('enrolled', default=False) and not 'admin' in session):
flash("You need to enroll in the course to see the lessons",
category='warning')
return redirect(url_for('courses.specificCourse', courseId=courseId))
dbCurr = db.cursor()
# POST request
if request.method == 'POST':
if 'admin' in session:
flash("Contributors can't mark videos as played",
category='warning')
dbCurr.close()
return redirect(request.url)
# getting back the videoid
dbCurr.execute(
"SELECT videoid FROM Composition WHERE courseid=? AND lesson=?",
(courseId, lessonId))
videoId = dbCurr.next()[0]
# marking the video as played for a student
ts = util.getTimestamp()
dbCurr.execute(
"INSERT INTO Visualization (email, id, timestamp) VALUES (?, ?, ?)",
(session['email'], videoId, ts))
flash("The video has been marked as played", category='success')
dbCurr.close()
return redirect(request.url)
# GET request
if request.method == 'GET':
# getting course name
dbCurr.execute("SELECT name FROM Course WHERE id=?", (courseId, ))
courseName = dbCurr.next()[0]
# getting video path
dbCurr.execute(
"SELECT path FROM Composition INNER JOIN Video on Composition.videoid = Video.id WHERE lesson = ? AND courseid=?",
(lessonId, courseId))
path = dbCurr.next()[0].split('/')
videoPath = '/'.join(path[-2:])
folderPath = path[-3]
dbCurr.close()
return render_template('courses/lesson.html',
courseName=courseName,
lessonId=lessonId,
videoPath=videoPath,
folderPath=folderPath,
courseId=courseId)
def newQuiz():
'''returns the page where you can create a quiz for your course'''
dbCurr = db.cursor()
# permission check
util.requireAdminLogin()
# GET Request
if request.method == 'GET':
# getting courses
courses = {}
# selecting only the courses that don't have a Test
dbCurr.execute(
"SELECT id, name FROM Course WHERE id NOT IN (SELECT courseid FROM Test)"
)
for courseId, courseName in dbCurr:
courses[courseId] = courseName
dbCurr.close()
return render_template('dashboard/newQuiz.html', courses=courses)
# POST Request
if request.method == 'POST':
# Getting data
quiz = request.get_json()
# Adding questions and answers
for question in quiz['questions']:
# adding the question
dbCurr.execute("INSERT INTO Question (topic) VALUES (?)",
(question['question'], ))
# getting the question id back
questionId = dbCurr.lastrowid
# adding the question to the Test table
dbCurr.execute(
"INSERT INTO Test (courseid, questionid) VALUES (?, ?)",
(quiz['course'], questionId))
# adding the answers for the question
for answer in question['answers']:
# adding the answer in the Answer table
dbCurr.execute("INSERT INTO Answer (topic) VALUES (?)",
(answer['answer'], ))
answerId = dbCurr.lastrowid
# adding the answer and question in the MadeUp table
dbCurr.execute("INSERT INTO MadeUp VALUES (?, ?, ?)",
(questionId, answerId, answer['correct']))
# Getting the response back
response = make_response(
jsonify({'message': 'The quiz has been submitted correctly!'}),
200)
flash("The quiz has been submitted correctly!", category='success')
dbCurr.close()
return response
def homepage():
'''courses homepage, card style'''
dbCurr = db.cursor()
courses = []
dbCurr.execute("SELECT * FROM Course")
for course in dbCurr:
courses.append(course)
dbCurr.close()
return render_template('courses/courses.html', context=courses)
def login():
'''Redirects to login.html. Handles POST requests to login users'''
# GET Request
if request.method == 'GET':
return render_template('login.html')
# POST Request = login attempt
if request.method == 'POST':
email = request.form['email']
password = request.form['password']
# handling unchecked "Remember me" option
if 'stayLogged' in request.form:
if request.form['stayLogged']:
app.permanent_session_lifetime = timedelta(days=1000)
# The database stores hash of hash of both email and password
hhmail = util.doubleHash(email)
hhpass = util.doubleHash(password)
# Connection to db
dbCurr = db.cursor()
hasResult = False
# checks if the email is present in the database
for table in ['Student', 'Contributor']:
dbCurr.execute(
f"SELECT password, name, surname FROM {table} WHERE email=?",
(hhmail, ))
for passwd, name, surname in dbCurr:
hasResult = True
if passwd == hhpass:
# both password and email are valid, logging in
session['email'] = hhmail
session['password'] = hhpass
session['name'] = name
session['surname'] = surname
if table == 'Contributor':
session['admin'] = True
print(f"User with email {hhmail} logged in successfully")
dbCurr.close()
return redirect(url_for('home'))
# email is right, password is wrong, flashing message
flash('Wrong password', category='danger')
# user not registered
if not hasResult:
flash('You are not registered', category='warning')
dbCurr.close()
return redirect(request.url)
def getCourses():
lessons = []
dbCurr = db.cursor()
courseId = request.get_json()['id']
dbCurr.execute(
"SELECT lesson FROM Composition INNER JOIN Course ON Composition.videoid = Course.id WHERE courseid=?",
(courseId, ))
for lessonNum in dbCurr:
lessons.append(lessonNum[0])
dbCurr.close()
return make_response(jsonify({'lessons': lessons}))
def newCourse():
'''Adds new course to the database'''
dbCurr = db.cursor()
# permission check
util.requireAdminLogin()
if request.method == 'GET':
return render_template('dashboard/newCourse.html')
name = request.form['name']
duration = request.form['time']
description = request.form['description']
dbCurr.execute(
'INSERT INTO Course (name, duration, description) VALUES (?, ?, ?)',
(name, duration, description))
dbCurr.close()
return redirect(url_for('dashboard.homepage'))
def main():
#connects to database
# db = util.dbConnect()
dbCurr = db.cursor()
#gather information from the user
email = input("Insert the email of the new contributor: ")
name = input("Insert the name of the new contributor: ")
surname = input("Insert the surname of the new contributor: ")
password = input("Insert the password of the new contributor: ")
#hashing the sensitive informations
hhmail = util.doubleHash(email)
hhpassword = util.doubleHash(password)
#add contributor manually
dbCurr.execute(
"INSERT INTO Contributor (email, password, name, surname) VALUES (?, ?, ?, ?)",
(hhmail, hhpassword, name, surname))
db.close()
def register():
'''Register to the Student table'''
if request.method == 'GET':
return render_template('register.html')
if request.method == 'POST':
# get data from form
name = request.form['name']
surname = request.form['surname']
email = request.form['email']
password = request.form['password']
# double hash password and mail
hhmail = util.doubleHash(email)
# Connection to db
dbCurr = db.cursor()
# checks if the email has already been used
alreadyRegistered = False
for table in ['Student', 'Contributor']:
dbCurr.execute(
f"SELECT EXISTS(SELECT email FROM {table} WHERE email=?)",
(hhmail, ))
if dbCurr.next() != nullTuple:
alreadyRegistered = True
if alreadyRegistered:
# flashing message if the email is already present
flash('This email has already been used.', category='warning')
dbCurr.close()
return redirect(request.url)
# Insert new Student in the database
dbCurr.execute("INSERT INTO Student VALUES (?, ?, ?, ?)",
(hhmail, name, surname, util.doubleHash(password)))
flash("You have been registered. You can now login!",
category='success')
dbCurr.close()
return redirect(url_for('login'))
def quizOutcome(courseId: int):
if request.method == 'GET':
if 'score' in session:
isPassed = True if session['score'] >= 60 else False
return render_template('courses/quizOutcome.html',
isPassed=isPassed,
courseId=courseId)
return render_template('courses/quizOutcome.html', courseId=courseId)
if request.method == 'POST':
if 'score' in session:
# getting course description
dbCurr = db.cursor()
dbCurr.execute("SELECT description FROM Course WHERE id=?",
(courseId, ))
data = {
'name': session['name'],
'surname': session['surname'],
'course': session['courseName'],
'timestamp': util.getTimestamp()[:10],
'description': dbCurr.next()[0]
}
pdf = util.generatePDF(
render_template('courses/pdfTemplate.html', data=data))
response = make_response(pdf)
response.headers['Content-Type'] = 'application/pdf'
response.headers[
'Content-Disposition'] = f'inline; filename={data["course"]}_{data["name"]}_{data["surname"]}.pdf'
# clearing cookie data
session.pop('courseName')
session.pop('score')
dbCurr.close()
return response
return redirect(url_for('courses.specificCourse', courseId=courseId))
def homepage():
'''Displays the dashboard only for authorized users (aka contributors)'''
dbCurr = db.cursor()
# Permission check
util.requireAdminLogin()
# getting the couses
courses = []
dbCurr.execute("SELECT id, name FROM Course")
for _course in dbCurr:
courses.append(_course)
# GET Request
if request.method == 'GET':
dbCurr.close()
return render_template('dashboard/dashboard.html', context=courses)
# POST request = new video upload
# File checks
if 'video' not in request.files:
dbCurr.close()
return redirect(request.url)
file = request.files['video']
# handles unselected file
if file.filename == '':
flash('No file selected', category='warning')
dbCurr.close()
return redirect(request.url)
# handles _ and space char
if [True for match in file.filename if match in [' ', '_']]:
flash('Please do not include _ or spaces in your file name!',
category='warning')
dbCurr.close()
return redirect(request.url)
newFile = False
if file and util.allowedFile(file.filename):
filename = secure_filename(file.filename)
path = join(app.config['UPLOAD_FOLDER'], filename)
files = [
f for f in listdir(app.config['UPLOAD_FOLDER'])
if isfile(join(app.config['UPLOAD_FOLDER'], f))
]
# checks if the file is already present in the filesystem
if filename not in files:
file.save(path)
newFile = True
else:
flash('There is already a file with this name, please rename it',
category='warning')
else:
flash('The extension of the file is not allowed.', category='danger')
description = request.form['description']
if newFile:
# insert new video in table
dbCurr.execute("INSERT INTO Video (description, path) VALUES (?, ?)",
(description, path))
# getting video ID
videoId = dbCurr.lastrowid
# getting course ID
courseId = request.form['course']
# insert new video in release table
dbCurr.execute(
f"INSERT INTO {util.getEnv()['dbSchema']}.Release VALUES (?, ?, ?)",
(session['email'], videoId, util.getTimestamp()))
# insert new video in the course
dbCurr.execute("INSERT INTO Composition VALUES (?, ?, ?)",
(videoId, courseId, int(request.form['lessonNum'])))
dbCurr.close()
return redirect(request.url)
def specificQuiz(courseId: int):
'''Quiz for the specified course'''
if not 'email' in session:
# User is not logged in
flash("You need to login in order to access this page",
category='warning')
return redirect(url_for('courses.specificCourse', courseId=courseId))
dbCurr = db.cursor()
dbCurr.execute(
"SELECT EXISTS(SELECT timestamp FROM Enrollment WHERE email=? AND id=?)",
(session['email'], courseId))
authorized = True if dbCurr.next() != nullTuple else False
if not authorized:
flash("You need to enroll and watch all the lessons first!",
category='warning')
dbCurr.close()
return redirect(url_for('courses.specificCourse', courseId=courseId))
# check if user has seen all the lessons
dbCurr.execute("SELECT COUNT(*) FROM Composition WHERE courseid=?",
(courseId, ))
lessonNum = dbCurr.next()[0]
dbCurr.execute(
"SELECT COUNT(*) FROM (SELECT DISTINCT * FROM Visualization WHERE email=?) as subquery",
(session['email'], ))
lessonViewed = dbCurr.next()[0]
if lessonNum > lessonViewed:
flash("You need to watch all the lessons first!", category='warning')
dbCurr.close()
return redirect(url_for('courses.specificCourse', courseId=courseId))
# Getting the quiz
quiz = []
# Getting course name
dbCurr.execute("SELECT name FROM Course WHERE id=?", (courseId, ))
courseName = dbCurr.next()[0]
# getting questions id for the test
dbCurr.execute(
"SELECT questionid, topic FROM Test INNER JOIN Question ON Test.questionid = Question.id WHERE courseid=?",
(courseId, ))
for questionId, questionText in dbCurr:
question = {
"questionText": questionText,
"questionId": questionId,
"answers": []
}
quiz.append(question)
for _question in quiz:
dbCurr.execute(
"SELECT topic, correct FROM Answer INNER JOIN MadeUp ON Answer.id = MadeUp.answerid WHERE questionid=?",
(_question['questionId'], ))
for topic, correct in dbCurr:
_question['answers'].append({
"answerText":
topic,
"answerCorrect":
True if correct else False
})
# POST Request
if request.method == 'POST':
# converting 'on' and 'off' values to boolean
answers = request.form.to_dict()
for key, value in answers.items():
answers[key] = True if value == 'on' else False
score, testPassed = util.quizChecker(quiz, answers, 60)
if testPassed:
flash(
f'Congratulations, you passed the test with a score of {score}, great job! Download your certification with the button below!',
category='success')
else:
flash(
f'Your score was {score}, you almost got it! Try again next time',
category='danger')
session['courseName'] = courseName
session['courseId'] = courseId
session['score'] = score
dbCurr.close()
return redirect(url_for('courses.quizOutcome', courseId=courseId))
# GET Request
if request.method == 'GET':
dbCurr.close()
if quiz:
return render_template("courses/quiz.html",
courseName=courseName,
quiz=quiz)
flash(
"The quiz has not been added to this course yet, come back later!",
category='warning')
return redirect(url_for('courses.specificCourse', courseId=courseId))