def add_new_registration():
user_name = request.form['new_registration']
registration_date = datetime.today().strftime("%Y-%m-%d")
query = """INSERT INTO users (user_name, registration_date) VALUES ('%s', '%s')""" % (
user_name, registration_date)
ui.handle_query(query)
return redirect('/')
def user_page(user_id):
user_name = ui.handle_query("""SELECT user_name FROM users WHERE id=%s""" %
(user_id))
question_list = ui.handle_query("""SELECT id, title
FROM question
WHERE users_id='%s';""" % (user_id))
answer_list = ui.handle_query("""SELECT a.message, q.id
FROM answer a
LEFT JOIN question q ON a.question_id=q.id
WHERE a.users_id=%s;""" % (user_id))
comment_list = ui.handle_query("""SELECT c.message, q.id
FROM comment c
LEFT JOIN question q ON c.question_id=q.id
WHERE c.users_id=%s AND c.question_id IS NOT NULL
UNION
SELECT c.message, q.id
FROM comment c
LEFT JOIN answer a ON a.id=c.answer_id
LEFT JOIN question q ON a.question_id=q.id
WHERE c.users_id=%s AND c.answer_id IS NOT NULL;"""
% (user_id, user_id))
return render_template('user_page.html',
user_name=user_name,
question_list=question_list,
answer_list=answer_list,
comment_list=comment_list)
def add_new_answer():
new_answer_user = request.form['new_answer_user']
user_id = ui.handle_query(
"""SELECT id FROM users WHERE user_name='{}';""".format(
new_answer_user))
ui.handle_query(
"""INSERT INTO answer (submission_time, vote_number, question_id, message, users_id)
VALUES ('{}', {}, {}, '{}', {});""".format(
str(datetime.now())[:-7], 0, request.form['question_id'],
request.form['new_answer_message'], user_id[0][0]))
return redirect('/question/' + request.form["question_id"])
def create_new_question():
new_question_user = request.form['new_question_user']
user_id = ui.handle_query(
"""SELECT id FROM users WHERE user_name='{}';""".format(
new_question_user))
ui.handle_query(
"""INSERT INTO question (submission_time, view_number, vote_number, title, message, users_id)
VALUES ('{}', {}, {}, '{}', '{}', {});""".format(
str(datetime.now())[:-7], 0, 0, request.form['new_question_title'],
request.form['new_question_message'], user_id[0][0]))
return redirect('/')
def add_new_question_comment():
new_question_comment_user = request.form['new_question_comment_user']
user_id = ui.handle_query(
"""SELECT id FROM users WHERE user_name='{}';""".format(
new_question_comment_user))
ui.handle_query(
"""INSERT INTO comment (question_id, answer_id, message, submission_time, users_id)
VALUES ({}, {}, '{}', '{}', {});""".format(
request.form['question_id'], 'NULL',
request.form['new_comment_message'],
str(datetime.now())[:-7], user_id[0][0]))
return redirect('/question/' + request.form["question_id"])
def add_new_answer_comment():
new_answer_comment_user = request.form['new_answer_comment_user']
user_id = ui.handle_query(
"""SELECT id FROM users WHERE user_name='{}';""".format(
new_answer_comment_user))
ui.handle_query(
"""INSERT INTO comment (answer_id, message, submission_time, users_id)
VALUES ({}, '{}', '{}', {});""".format(
request.form['answer_id'], request.form['new_comment_message'],
str(datetime.now())[:-7], user_id[0][0]))
question_id = ui.get_record_from_sql_db(
'answer', 'id=%s' % (request.form["answer_id"]))[0][3]
return redirect('/question/' + str(question_id))
def vote_question_up():
id = request.args.get('id')
question_list = ui.get_record_from_sql_db('question', "id=%s" % (id))
vote_number = question_list[0][3] + 1
ui.update_record('question', "vote_number=%s" % (vote_number),
"id=%s" % (id))
query = """SELECT users_id \
FROM question
WHERE id = %s""" % (id)
user_id = ui.handle_query(query)[0][0]
query = """UPDATE users \
SET reputation = reputation + 5 \
WHERE id = %s""" % user_id
ui.handle_query(query)
return redirect('/')
def new_answer_comment(answer_id):
users = ui.handle_query(
"""SELECT user_name FROM users ORDER BY user_name;""")
return render_template('new_comment.html',
answer_id=answer_id,
comment_type="answer",
users=users)
def new_question_comment(question_id):
users = ui.handle_query(
"""SELECT user_name FROM users ORDER BY user_name;""")
return render_template('new_comment.html',
question_id=question_id,
comment_type="question",
users=users)
def vote_answer_down():
id = request.args.get('id')
answer_list = ui.get_record_from_sql_db('answer', "id=%s" % (id))
vote_number = answer_list[0][2] - 1
ui.update_record('answer', "vote_number=%s" % (vote_number),
"id=%s" % (id))
question_id = answer_list[0][3]
query = """SELECT users_id \
FROM answer
WHERE id = %s""" % (id)
user_id = ui.handle_query(query)[0][0]
query = """UPDATE users \
SET reputation = reputation - 2 \
WHERE id = %s""" % user_id
ui.handle_query(query)
return display_question(question_id, count_view=False)
def tags():
tag_list = ui.handle_query("""SELECT t.id, t.name, COUNT(qt.question_id)
FROM tag t
LEFT JOIN question_tag qt
ON qt.tag_id=t.id
GROUP BY t.id
ORDER BY COUNT(qt.question_id) DESC;""")
return render_template('tag_page.html', tag_list=tag_list)
def new_answer(question_id):
users = ui.handle_query(
"""SELECT user_name FROM users ORDER BY user_name;""")
return render_template('new_answer.html',
question_id=question_id,
users=users)
def list_users():
query = """SELECT * \
FROM users \
ORDER BY id"""
user_list = ui.handle_query(query)
return render_template('list_users.html', user_list=user_list)
def new_question():
users = ui.handle_query(
"""SELECT user_name FROM users ORDER BY user_name;""")
return render_template('new_question.html', users=users)
