def deletequestion(questionid): loggeduser = get_user() db = get_db() cursor = db.execute("SELECT * FROM questions where id=?", [questionid]) uid = cursor.fetchone() adminuser = False expertuser = False validuser = False #only admin and user who asked the question is able to access this route if not loggeduser: return redirect(url_for('login')) if loggeduser['expert'] == 1 and loggeduser['admin'] == 0: expertuser = True if loggeduser['id'] == uid['askedby_id']: validuser = True if loggeduser['admin'] == 1: adminuser = True if expertuser == True: return redirect(url_for('home')) elif adminuser == True or validuser == True: db.execute("DELETE FROM questions WHERE id=?", [questionid]) db.commit() return redirect(url_for('home'))
def home(): loggeduser = get_user() db = get_db() query = '''SELECT questions.id,questions.question_text,askers.name as asker_name,experts.name as expert_name FROM questions JOIN users AS askers ON askers.id=questions.askedby_id JOIN users AS experts ON experts.id = questions.expert_id WHERE questions.answer_text IS NOT NULL''' cursor = db.execute(query) results = cursor.fetchall() return render_template('home.html', loggeduser=loggeduser, results=results)
def get_user(): user_result = None if 'user' in session: loggeduser = session['user'] db = get_db() cursor = db.execute( "SELECT id,name,password,expert,admin from users where name=?", [loggeduser]) user_result = cursor.fetchone() return user_result
def deleteuser(userid): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if loggeduser['admin'] == 0: return redirect(url_for('home')) db = get_db() db.execute("DELETE FROM users WHERE id=?", [userid]) db.commit() return redirect('/users')
def demote(userid): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if loggeduser['admin'] == 0: return redirect(url_for('home')) db = get_db() db.execute("UPDATE users SET expert=0 WHERE id=?", [userid]) db.commit() return redirect('/users')
def users(): loggeduser = get_user() #protect routes if not loggeduser: return redirect(url_for('login')) if loggeduser['admin'] == 0: return redirect(url_for('home')) db = get_db() cursor = db.execute("SELECT * FROM users") result = cursor.fetchall() return render_template('users.html', loggeduser=loggeduser, result=result)
def question(que_id): loggeduser = get_user() db = get_db() query = '''SELECT questions.askedby_id,questions.id,questions.question_text,questions.answer_text,askers.name AS asker_name, experts.name as expert_name,questions.expert_id as exp_id FROM questions JOIN users AS askers ON askers.id=questions.askedby_id JOIN users AS experts ON experts.id = questions.expert_id WHERE questions.id=?''' cursor = db.execute(query, [que_id]) question = cursor.fetchone() return render_template('question.html', loggeduser=loggeduser, question=question)
def unanswered(): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if loggeduser['expert'] == 0: return redirect(url_for('home')) db = get_db() cursor = db.execute( '''SELECT questions.id,questions.question_text,users.name FROM questions JOIN users ON users.id = questions.askedby_id WHERE questions.answer_text IS NULL AND questions.expert_id=?''', [loggeduser['id']]) questions = cursor.fetchall() return render_template('unanswered.html', loggeduser=loggeduser, questions=questions)
def register(): loggeduser = get_user() try: if request.method == 'POST': user = request.form['user'] passwd = request.form['pass'] hashed_password = generate_password_hash(passwd, method='sha256') db = get_db() db.execute( "INSERT INTO users(name,password,expert,admin) VALUES(?,?,?,?)", [user, hashed_password, 0, 0]) db.commit() db.close() return "<h1>User registered</h1>" except sqlite3.IntegrityError: flash(f"User {user} already exists,try a different username") return render_template('register.html', loggeduser=loggeduser)
def answer_question(question_id): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if loggeduser['expert'] == 0: return redirect(url_for('home')) db = get_db() cursor = db.execute("SELECT id,question_text FROM questions WHERE id=?", [question_id]) question = cursor.fetchone() if request.method == 'POST': answer = request.form['answer'] db.execute("UPDATE questions SET answer_text=? WHERE id=?", [answer, question_id]) db.commit() return redirect(url_for('unanswered')) return render_template('answerquestion.html', loggeduser=loggeduser, question=question)
def update_answer(que_id): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if loggeduser['expert'] == 0: return redirect(url_for('home')) db = get_db() cursor = db.execute("SELECT answer_text from questions Where id = ?", [que_id]) question_text = cursor.fetchone() query = '''SELECT questions.id,questions.question_text,questions.answer_text,askers.name AS asker_name, experts.name as expert_name,questions.expert_id as exp_id FROM questions JOIN users AS askers ON askers.id=questions.askedby_id JOIN users AS experts ON experts.id = questions.expert_id WHERE questions.id=?''' cursor_question = db.execute(query, [que_id]) question = cursor_question.fetchone() return render_template('answerquestion.html', loggeduser=loggeduser, answer=question_text, question=question)
def askedquestions(): loggeduser = get_user() db = get_db() if not loggeduser: return redirect(url_for('login')) if loggeduser['expert'] == 1 and loggeduser['admin'] == 0: return redirect(url_for('home')) if loggeduser['admin'] == 0 and loggeduser['expert'] == 0: cursor = db.execute("SELECT * FROM questions where askedby_id = ?", [loggeduser['id']]) results = cursor.fetchall() elif loggeduser['admin'] == 1: query = '''SELECT questions.id,questions.question_text,askers.name as asker_name,experts.name as expert_name FROM questions JOIN users AS askers ON askers.id=questions.askedby_id JOIN users AS experts ON experts.id = questions.expert_id ''' cursor = db.execute(query) results = cursor.fetchall() return render_template('askedquestions.html', loggeduser=loggeduser, data=results)
def ask_question(): loggeduser = get_user() if not loggeduser: return redirect(url_for('login')) if (loggeduser['admin'] == 1 or loggeduser['expert'] == 1): return redirect(url_for('home')) db = get_db() if request.method == "POST": question = request.form['question-box'] expert_id = request.form['expert'] askedby_id = loggeduser['id'] db.execute( "INSERT INTO questions(question_text,askedby_id,expert_id) VALUES(?,?,?)", [question, askedby_id, expert_id]) db.commit() return redirect(url_for('home')) exp_cursor = db.execute("SELECT * FROM users WHERE expert = 1") expertusers = exp_cursor.fetchall() return render_template('askquestion.html', loggeduser=loggeduser, experts=expertusers)
def login(): loggeduser = get_user() if request.method == 'POST': user = request.form['user'] passwd = request.form['pass'] db = get_db() resultset = db.execute("SELECT name,password from users where name=?", [user]) output = resultset.fetchone() #output will be None if username does not exist in database if output: #this block will be executed if output is not None hashed_password = output['password'] if (check_password_hash(hashed_password, passwd)): #create a user session of usrname value from database session['user'] = output['name'] return redirect(url_for('home')) else: flash("Password did not match") else: flash("User not found") return render_template('login.html', loggeduser=loggeduser)