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))