ask-mate-python/data_manager/dm_comments.py at dc8e4d7a3f3191474cf1bbb71dc1173b1d66c057 · CodecoolBase/ask-mate-python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
from connection import connection_handler
import datetime


@connection_handler
def add_comment_to_question(cursor, form_data, question_id, users_id):
    time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute("""
                    INSERT INTO comment
                    (edited_count, message, question_id, submission_time, users_id)
                    VALUES (0, %(message)s, %(question_id)s, %(time)s, %(users_id)s)
                    """,
                   {'message': form_data['message'], 'question_id': question_id, 'time': time,
                    'users_id': users_id})


@connection_handler
def add_comment_to_answer(cursor, form_data, answer_id, question_id, users_id):
    time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute("""
                    INSERT INTO comment
                    (edited_count, message, answer_id, submission_time, question_id, users_id)
                    VALUES (0, %(message)s, %(answer_id)s, %(time)s, %(question_id)s, %(users_id)s)
                    """,
                   {'message': form_data['message'], 'answer_id': answer_id,
                    'time': time, 'question_id': question_id, 'users_id': users_id})


@connection_handler
def show_question_comments_by_id(cursor, question_id):
    cursor.execute("""
                    SELECT c.*, u.username FROM comment as c
                    LEFT JOIN users as u ON c.users_id = u.id
                    WHERE   question_id = %(question_id)s AND answer_id IS NULL
                    """,
                   {'question_id': question_id})
    comments = cursor.fetchall()
    return comments


@connection_handler
def show_answer_comments_by_id(cursor, question_id):
    cursor.execute("""
                    SELECT c.*, u.username FROM comment as c
                    LEFT JOIN users as u ON c.users_id = u.id
                    WHERE question_id = %(question_id)s;
                    """,
                   {'question_id': question_id})
    comments = cursor.fetchall()
    return comments


@connection_handler
def get_comment_by_id(cursor, comment_id):
    cursor.execute("""
                    SELECT c.*, u.username FROM comment as c
                    LEFT JOIN users as u ON c.users_id = u.id
                    WHERE c.id = %(comment_id)s;
                    """,
                   {'comment_id': comment_id})
    comment = cursor.fetchone()
    return comment


@connection_handler
def update_comment_by_id(cursor, form_data, comment_id):
    time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute("""
                    UPDATE comment
                    SET message = %(message)s, edited_count = edited_count + 1, submission_time = %(time)s
                    WHERE id = %(comment_id)s;
                    SELECT question_id FROM comment
                    WHERE id = %(comment_id)s;
                    """,
                   {'message': form_data['message'], 'time': time, 'comment_id': comment_id})
    question_id = cursor.fetchone()
    return question_id


@connection_handler
def delete_comment_by_id(cursor, comment_id):
    cursor.execute("""
                    DELETE FROM comment
                    WHERE id = %(comment_id)s;
                    """,
                   {'comment_id': comment_id})