ContactBridge

Log | Files | Refs | README

spam.py (3137B)

      1 import math
      2 
      3 from database.repository import DbRepository
      4 
      5 
      6 class SpamModel:
      7     def __init__(self):
      8         self.db_repository = DbRepository()
      9 
     10     def get_page(self, page, page_size, sort="id", sort_order="ASC", search=''):
     11         search_term = f"%{search}%"
     12 
     13         common_sql_query_base = f"""
     14                FROM spam
     15                WHERE (name LIKE ? OR email LIKE ? OR subject LIKE ? OR content LIKE ?)
     16            """
     17 
     18         total_messages_query = f"""
     19             SELECT COUNT(*)
     20             {common_sql_query_base}
     21         """
     22 
     23         total_messages = self.db_repository.execute_query(
     24             total_messages_query,
     25             (search_term, search_term, search_term, search_term),
     26             expect_result=True
     27         )
     28 
     29         total_messages_count = total_messages[0][0] if total_messages else 0
     30         offset = (page - 1) * page_size
     31         total_pages = math.ceil(total_messages_count / page_size)
     32 
     33         paginated_messages_query = f"""
     34                 SELECT id, name, email, subject, content, timestamp, relevance
     35                 {common_sql_query_base}
     36                 ORDER BY {sort} {sort_order} 
     37                 LIMIT ? OFFSET ?
     38             """
     39 
     40         paginated_messages = self.db_repository.execute_query(
     41             paginated_messages_query,
     42             (search_term, search_term, search_term, search_term, page_size, offset),
     43             expect_result=True
     44         )
     45 
     46         labeled_messages = [
     47             {
     48                 'id': message[0],
     49                 'name': message[1],
     50                 'email': message[2],
     51                 'subject': message[3],
     52                 'content': message[4],
     53                 'timestamp': message[5],
     54                 'relevance': message[6]
     55             }
     56             for message in paginated_messages
     57         ]
     58 
     59         return labeled_messages, total_pages
     60 
     61     def get(self, message_id):
     62         query = 'SELECT * FROM spam WHERE id = ?'
     63         result = self.db_repository.execute_query(query, (message_id,), expect_result=True)
     64 
     65         if result:
     66             message = result[0]
     67             labeled_message = {
     68                     'id': message[0],
     69                     'name': message[1],
     70                     'email': message[2],
     71                     'subject': message[3],
     72                     'content': message[4],
     73                     'timestamp': message[5],
     74                     'relevance': message[6]
     75                 }
     76             return labeled_message
     77 
     78         return None
     79 
     80     def create(self, parameters):
     81         create_query = """
     82             INSERT INTO spam (name, email, subject, content, relevance) 
     83             VALUES (?, ?, ?, ?, ?)
     84         """
     85         self.db_repository.execute_query(create_query, parameters)
     86 
     87     def update(self, parameters):
     88         update_query = """
     89             UPDATE spam
     90             SET name = ?, email = ?, subject = ?, content = ?, relevance = ?
     91             WHERE id = ?
     92         """
     93         self.db_repository.execute_query(update_query, parameters)
     94 
     95     def delete(self, message_id):
     96         delete_query = "DELETE FROM spam WHERE id = ?"
     97         self.db_repository.execute_query(delete_query, (message_id,))