Revize 9e22e20c

Re #8471 - Implemented filter of certificates
- Implemented filter by certificate type using index (id) from CertificateTypes table
- No multiple types supported

src/dao/certificate_repository.py

@@ -1 @@
-from typing import Dict
+from typing import Dict, List
 from sqlite3 import Connection, Cursor, Error
 
 from src.db_objects.certificate import Certificate
@@ -112 +112 @@
         else:
             return None
 
+    def read_all(self, filter_type: int = None):
+        """
+        Reads (selects) all certificates (with type).
+
+        :param filter_type: ID of certificate type from CertificateTypes table
+
+        :return: list of certificates
+        """
+
+        try:
+            sql_extension = ""
+            values = []
+            if filter_type is not None:
+                sql_extension = (f" WHERE {COL_TYPE_ID} = ("
+                                 f"SELECT {COL_ID} FROM {TAB_CERTIFICATE_TYPES} WHERE {COL_ID} = ?")
+                values = [filter_type]
+
+            sql = (f"SELECT * FROM {TAB_CERTIFICATES}{sql_extension}")
+            self.cursor.execute(sql, values)
+            certificate_rows = self.cursor.fetchall()
+
+            certificates: List[Certificate] = []
+            for certificate_row in certificate_rows:
+                sql = (f"SELECT * FROM {TAB_CERTIFICATE_USAGES} "
+                       f"WHERE {COL_CERTIFICATE_ID} = ?")
+                values = [certificate_row[0]]
+                self.cursor.execute(sql, values)
+                usage_rows = self.cursor.fetchall()
+
+                usage_dict: Dict[int, bool] = {}
+                for usage_row in usage_rows:
+                    usage_dict[usage_row[2]] = True
+
+                certificates.append(Certificate(certificate_row[0],
+                                                certificate_row[1],
+                                                certificate_row[2],
+                                                certificate_row[3],
+                                                certificate_row[4],
+                                                certificate_row[5],
+                                                certificate_row[6],
+                                                certificate_row[7],
+                                                usage_dict))
+        except Error as e:
+            print(e)
+            return None
+
+        if len(certificates) > 0:
+            return certificates
+        else:
+            return None
+
     def update(self, certificate_id: int, certificate: Certificate) -> bool:
         """
         Updates a certificate.