From 5ca5b78cef1359ca29295992c61042d6ea5375d2 Mon Sep 17 00:00:00 2001
From: Debulois <quentin@debulois.fr>
Date: Fri, 15 Apr 2022 16:09:55 +0200
Subject: Initial commit, traduction des fichiers databases de PHP et système
 de vérification des mots de passe sur la bdd en local
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 app_alphajob/database/dbsearch.py | 140 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 140 insertions(+)
 create mode 100644 app_alphajob/database/dbsearch.py

(limited to 'app_alphajob/database/dbsearch.py')

diff --git a/app_alphajob/database/dbsearch.py b/app_alphajob/database/dbsearch.py
new file mode 100644
index 0000000..15d4a24
--- /dev/null
+++ b/app_alphajob/database/dbsearch.py
@@ -0,0 +1,140 @@
+############################################################################
+#                                                                          #
+# Description: Ensemble de méthodes pour étendre dbmain afin               #
+#              d'effectuer des recherches dans la bdd                      #
+#                                                                          #
+############################################################################
+
+# Infos pour les """join""":
+# https:#www.freecodecamp.org/news/sql-joins-tutorial/
+
+# Import de dbmain
+from . import dbmain
+
+
+# Extension de cette classe avec dbmain
+class DbSearch(dbmain.DbMain):
+    # ****************************************************************************
+    # RECUPERATION DES INFORMATIONS RELATIVES AUX UTILISATEURS
+    # ****************************************************************************
+    # Récupération des infos d'un compte par son id
+    def get_user_account_by_userid(self, userId):
+        reqSearchUser = """
+            SELECT 
+                userId, email, inscriptionDate, userStatus
+            FROM 
+                """ + self.TABLEUSERACCOUNT + """
+            WHERE 
+                userId = %s"""
+        self.exec_cmd(reqSearchUser, [userId])
+        return self.cur.fetchall()
+
+    # Récupération des infos d'un utilisateur par son id
+    def get_user_info_by_userid(self, userId):
+        reqGetUserInfo = """
+            SELECT 
+                userId, lastname, firstname, degree, 
+                capability, description, phoneNumber,
+                adress, zipCode, city 
+            FROM 
+                """ + self.TABLEUSERINFO + """ 
+            WHERE 
+                userId = %s"""
+        self.exec_cmd(reqGetUserInfo, [userId])
+        return self.cur.fetchall()
+
+    # ****************************************************************************
+    # RECUPERATION DES INFORMATIONS RELATIVES AUX PROS
+    # ****************************************************************************
+    def get_all_pro_by_jobid(self, jobId):
+        reqSearchConsultant = """
+            SELECT 
+                """ + self.TABLEUSERINFO + """.userId, 
+                lastname, firstname, capability 
+            FROM 
+                """ + self.TABLEUSERINFO + """
+            INNER JOIN
+                """ + self.TABLEUSERJOB + """
+            ON
+                """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERJOB + """.userId
+            WHERE 
+                jobCategoryId = %s"""
+        self.exec_cmd(reqSearchConsultant, [jobId])
+        return self.cur.fetchall()
+
+    # Récupération des infos d'un pro par son nom
+    def get_pro_info_by_lastname(self, lastname):
+        reqSearchConsultant = """
+            SELECT 
+                """ + self.TABLEUSERINFO + """.userId, 
+                lastname, firstname, capability 
+            FROM 
+                """ + self.TABLEUSERINFO + """
+            INNER JOIN 
+                """ + self.TABLEUSERACCOUNT + """ 
+            ON 
+                """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERACCOUNT + """.userId
+            WHERE 
+                userStatus = '1' 
+            AND 
+                lastname LIKE CONCAT('%', %s, '%')"""
+        self.exec_cmd(reqSearchConsultant, [lastname])
+        return self.cur.fetchall()
+
+    # Récupération des emploies associés à un pro
+    def get_pro_job_by_proid(self, proId):
+        reqGetAll = """
+            SELECT 
+                """ + self.TABLEUSERJOB + """.jobCategoryId, 
+                jobCategoryNameEn, jobCategoryNameFr 
+            FROM 
+                """ + self.TABLEUSERJOB + """
+            INNER JOIN 
+                """ + self.TABLEJOBCATEGORY + """
+            ON 
+                """ + self.TABLEUSERJOB + """.jobCategoryId = """ + self.TABLEJOBCATEGORY + """.jobCategoryId
+            WHERE 
+                userId = %s"""
+        self.exec_cmd(reqGetAll, [proId])
+        return self.cur.fetchall()
+
+    # Récupération des notes d'un consultant par son nom
+    def get_pro_note_by_proid(self, proId):
+        reqCountMission = """
+            SELECT 
+                note 
+            FROM 
+                """ + self.TABLEMISSION + """
+            INNER JOIN 
+                """ + self.TABLEUSERINFO + """ 
+            ON 
+                """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId
+            WHERE 
+                proId = %s"""
+        self.exec_cmd(reqCountMission, [proId])
+        return self.cur.fetchall()
+
+    # ****************************************************************************
+    # RECUPERATION DES INFORMATIONS RELATIVES AUX EMPLOIS
+    # ****************************************************************************
+    # Récupérer toutes les infos de la table jobCategory
+    def get_job_all(self):
+        reqGetAllJobs = """
+            SELECT 
+                * 
+            FROM 
+                """ + self.TABLEJOBCATEGORY
+        self.exec_cmd(reqGetAllJobs)
+        return self.cur.fetchall()
+
+    # Récupérer toutes les infos de la table jobCategory
+    def get_job_by_jobid(self, jobId):
+        reqGetJobinfo = """
+            SELECT 
+                * 
+            FROM 
+                """ + self.TABLEJOBCATEGORY + """
+            WHERE
+                jobCategoryId = %s"""
+        self.exec_cmd(reqGetJobinfo, [jobId])
+        return self.cur.fetchall()
-- 
cgit v1.2.3