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)
