From 1eaf21fd423e5dedf1ed4361b0e9b6cbbcfc9fa0 Mon Sep 17 00:00:00 2001 From: Debulois Date: Sun, 1 May 2022 11:44:09 +0200 Subject: Ajout gestion des missions coté client & pro MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- src/database/dbadmin.py | 64 +++++++++++++------------- src/database/dbmission.py | 113 +++++++++++++++++++++++----------------------- src/database/dbsearch.py | 88 ++++++++++++++++++------------------ src/database/dbuser.py | 72 ++++++++++++++--------------- 4 files changed, 168 insertions(+), 169 deletions(-) (limited to 'src/database') diff --git a/src/database/dbadmin.py b/src/database/dbadmin.py index 7b00cd5..6fcd3f5 100644 --- a/src/database/dbadmin.py +++ b/src/database/dbadmin.py @@ -18,9 +18,9 @@ class DbAdmin(dbmain.DbMain): # Récupérer toutes les infos de la table userAccount def get_all_users_account(self) -> list: reqGetAll: str = """ - SELECT - * - FROM + SELECT + * + FROM """ + self.TABLEUSERACCOUNT self.exec_cmd(reqGetAll) result = self.cur.fetchall() @@ -33,10 +33,10 @@ class DbAdmin(dbmain.DbMain): # https:#stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table reqCheckExistId: str = """ SELECT EXISTS( - SELECT 1 FROM - """ + self.TABLEUSERACCOUNT + """ - WHERE - userId = %s + SELECT 1 FROM + """ + self.TABLEUSERACCOUNT + """ + WHERE + userId = %s LIMIT 1 )""" self.exec_cmd(reqCheckExistId, [userId]) @@ -62,10 +62,10 @@ class DbAdmin(dbmain.DbMain): # Préparation et execution de la requète pour voir si la categorie existe en anglais reqCheckjobCategoryNameEn: str = """ SELECT EXISTS( - SELECT 1 FROM - """ + self.TABLEJOBCATEGORY + """ - WHERE - jobCategoryNameEn = %s + SELECT 1 FROM + """ + self.TABLEJOBCATEGORY + """ + WHERE + jobCategoryNameEn = %s LIMIT 1 )""" self.exec_cmd(reqCheckjobCategoryNameEn, [jobNameEn]) @@ -75,10 +75,10 @@ class DbAdmin(dbmain.DbMain): # Préparation et execution de la requète pour voir si la categorie existe en Français reqCheckjobCategoryNameFr: str = """ SELECT EXISTS( - SELECT 1 FROM - """ + self.TABLEJOBCATEGORY + """ - WHERE - jobCategoryNameFr = %s + SELECT 1 FROM + """ + self.TABLEJOBCATEGORY + """ + WHERE + jobCategoryNameFr = %s LIMIT 1 )""" self.exec_cmd(reqCheckjobCategoryNameFr, [jobNameFr]) @@ -87,10 +87,10 @@ class DbAdmin(dbmain.DbMain): if not reqResultFr[0][0]: # Préparation de la requète pour l'ajout reqjobCategoryNameAdd: str = """ - INSERT INTO + INSERT INTO """ + self.TABLEJOBCATEGORY + """ - (jobCategoryNameEn, jobCategoryNameFr) - VALUES + (jobCategoryNameEn, jobCategoryNameFr) + VALUES (%s ,%s)""" # Exécution de la requète self.exec_cmd(reqjobCategoryNameAdd, [jobNameEn, jobNameFr], True) @@ -108,10 +108,10 @@ class DbAdmin(dbmain.DbMain): # Préparation de la requète pour voir si l'utilisateur existe reqCheckExistId: str = """ SELECT EXISTS( - SELECT 1 FROM - """ + self.TABLEJOBCATEGORY + """ - WHERE - jobCategoryId = %s + SELECT 1 FROM + """ + self.TABLEJOBCATEGORY + """ + WHERE + jobCategoryId = %s LIMIT 1 )""" # Exécution de la requète, fetchall pour avoir le résultat @@ -121,9 +121,9 @@ class DbAdmin(dbmain.DbMain): if (reqResult[0][0]): # Préparation de la requète pour suppression reqDelJobCategory: str = """ - DELETE FROM - """ + self.TABLEJOBCATEGORY + """ - WHERE + DELETE FROM + """ + self.TABLEJOBCATEGORY + """ + WHERE jobCategoryId = %s""" # Exécution de la requète self.exec_cmd(reqDelJobCategory, [jobId], True) @@ -141,10 +141,10 @@ class DbAdmin(dbmain.DbMain): # Préparation de la requète pour voir si l'utilisateur existe reqCheckExistId: str = """ SELECT EXISTS( - SELECT 1 FROM - """ + self.TABLEMISSION + """ - WHERE - missionId = %s + SELECT 1 FROM + """ + self.TABLEMISSION + """ + WHERE + missionId = %s LIMIT 1 )""" # Exécution de la requète @@ -154,9 +154,9 @@ class DbAdmin(dbmain.DbMain): if (reqResult[0][0]): # Préparation de la requète pour suppression reqDelMission: str = """ - DELETE FROM - """ + self.TABLEMISSION + """ - WHERE + DELETE FROM + """ + self.TABLEMISSION + """ + WHERE missionId = %s""" # Exécution de la requète self.exec_cmd(reqDelMission, [missionId], True) diff --git a/src/database/dbmission.py b/src/database/dbmission.py index 7f6620e..9d23288 100644 --- a/src/database/dbmission.py +++ b/src/database/dbmission.py @@ -24,10 +24,10 @@ class DbMission(dbmain.DbMain): def register_mission(self, subject, clientId, proId, jobId): # Préparation de la requète pour enregistrement de la mission reqAddMission = """ - INSERT INTO """ + self.TABLEMISSION + """ - (date, subject, clientId, proId, jobCategoryId) - VALUES - (?, ?, ?, ?, ?)""" + INSERT INTO """ + self.TABLEMISSION + """ + (date, subject, clientId, proId, jobCategoryId) + VALUES + (%s, %s, %s, %s, %s)""" # Exécution de la requète self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), @@ -43,54 +43,53 @@ class DbMission(dbmain.DbMain): def accept_mission_by_missionid(self, missionId): # Préparation de la requète pour enregistrement de la mission reqAddMission = """ - UPDATE - """ + self.TABLEMISSION + """ + UPDATE + """ + self.TABLEMISSION + """ SET - acceptedByPro = ? - WHERE - missionId = ?""" + acceptedByPro = %s + WHERE + missionId = %s""" # Exécution de la requète - self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId]) + self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId], True) return 1 def validate_mission_pro_by_missionid(self, missionId): # Préparation de la requète pour enregistrement de la mission reqAddMission = """ - UPDATE - """ + self.TABLEMISSION + """ + UPDATE + """ + self.TABLEMISSION + """ SET - validatedByPro = ? - WHERE - missionId = ?""" + validatedByPro = %s + WHERE + missionId = %s""" # Exécution de la requète - self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId]) + self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId], True) return 1 def validate_mission_client_by_missionid(self, missionId): # Préparation de la requète pour enregistrement de la mission reqAddMission = """ - UPDATE - """ + self.TABLEMISSION + """ + UPDATE + """ + self.TABLEMISSION + """ SET - validatedByClient = ? - WHERE - missionId = ?""" + validatedByClient = %s + WHERE + missionId = %s""" # Exécution de la requète - self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId]) + self.exec_cmd(reqAddMission, [datetime.now().strftime("%Y-%m-%d %H:%M:%S"), missionId], True) return 1 - def review_mission(self, missionId, review, note): + def review_mission(self, missionId: int, review: str, note: int) -> None: # Préparation de la requète pour enregistrement de la mission reqAddMission = """ - UPDATE - """ + self.TABLEMISSION + """ + UPDATE + """ + self.TABLEMISSION + """ SET - review = ?, note = ? - WHERE - missionId = ?""" + review = %s, note = %s + WHERE + missionId = %s""" # Exécution de la requète - self.exec_cmd(reqAddMission, [review, note, missionId]) - return 1 + self.exec_cmd(reqAddMission, [review, note, missionId], True) # **************************************************************************** # RECUPERATION DES INFORMATIONS RELATIVES AUX MISSIONS @@ -98,18 +97,18 @@ class DbMission(dbmain.DbMain): # Récupérations des missions d'un pro par son id def get_mission_by_proid(self, proId): reqGetMissionsProId = """ - SELECT + SELECT missionId, date, subject, note, review, - acceptedByPro, validatedByClient, - validatedByPro, jobCategoryId, proId, lastname - FROM + acceptedByPro, validatedByClient, + validatedByPro, jobCategoryId, proId, lastname + FROM """ + self.TABLEMISSION + """ - LEFT JOIN - """ + self.TABLEUSERINFO + """ - ON + LEFT JOIN + """ + self.TABLEUSERINFO + """ + ON """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId - WHERE - proId = ?""" + WHERE + proId = %s""" self.exec_cmd(reqGetMissionsProId, [proId]) result = self.cur.fetchall() return result @@ -117,18 +116,18 @@ class DbMission(dbmain.DbMain): # Récupérations des missions d'un client par son id def get_mission_by_clientid(self, clientId): reqGetMissionsClientId = """ - SELECT + SELECT missionId, date, subject, note, review, - acceptedByPro, validatedByClient, - validatedByPro, jobCategoryId, proId, lastname - FROM + acceptedByPro, validatedByClient, + validatedByPro, jobCategoryId, proId, lastname + FROM """ + self.TABLEMISSION + """ - LEFT JOIN - """ + self.TABLEUSERINFO + """ - ON + LEFT JOIN + """ + self.TABLEUSERINFO + """ + ON """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId - WHERE - clientId = ?""" + WHERE + clientId = %s""" self.exec_cmd(reqGetMissionsClientId, [clientId]) result = self.cur.fetchall() return result @@ -136,9 +135,9 @@ class DbMission(dbmain.DbMain): # Récupérations des missions d'un client par son id def get_all_missions(self): reqGetAllMission = """ - SELECT - * - FROM + SELECT + * + FROM """ + self.TABLEMISSION self.exec_cmd(reqGetAllMission) result = self.cur.fetchall() @@ -147,14 +146,14 @@ class DbMission(dbmain.DbMain): # Compte le nombre de mission d'un consultant par son nom def count_pro_missions_by_proid(self, proId): reqCountMission = """ - SELECT COUNT(*) FROM + SELECT COUNT(*) FROM """ + self.TABLEMISSION + """ - INNER JOIN - """ + self.TABLEUSERINFO + """ - ON + INNER JOIN + """ + self.TABLEUSERINFO + """ + ON """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId - WHERE - proId = ?""" + WHERE + proId = %s""" self.exec_cmd(reqCountMission, [proId]) result = self.cur.fetchall() return result diff --git a/src/database/dbsearch.py b/src/database/dbsearch.py index 3089bf1..0561c9d 100644 --- a/src/database/dbsearch.py +++ b/src/database/dbsearch.py @@ -20,11 +20,11 @@ class DbSearch(dbmain.DbMain): # Récupération des infos d'un compte par son id def get_user_account_by_userid(self, userId: int) -> list: reqSearchUser = """ - SELECT + SELECT userId, email, inscriptionDate, userStatus - FROM + FROM """ + self.TABLEUSERACCOUNT + """ - WHERE + WHERE userId = %s""" self.exec_cmd(reqSearchUser, [userId]) return self.cur.fetchall() @@ -32,13 +32,13 @@ class DbSearch(dbmain.DbMain): # Récupération des infos d'un utilisateur par son id def get_user_info_by_userid(self, userId: int) -> list: reqGetUserInfo = """ - SELECT - userId, lastname, firstname, degree, + SELECT + userId, lastname, firstname, degree, capability, description, phoneNumber, - adress, zipCode, city - FROM - """ + self.TABLEUSERINFO + """ - WHERE + adress, zipCode, city + FROM + """ + self.TABLEUSERINFO + """ + WHERE userId = %s""" self.exec_cmd(reqGetUserInfo, [userId]) return self.cur.fetchall() @@ -48,16 +48,16 @@ class DbSearch(dbmain.DbMain): # **************************************************************************** def get_all_pro_by_jobid(self, jobId): reqSearchConsultant = """ - SELECT - """ + self.TABLEUSERINFO + """.userId, - lastname, firstname, capability - FROM + SELECT + """ + self.TABLEUSERINFO + """.userId, + lastname, firstname, capability + FROM """ + self.TABLEUSERINFO + """ INNER JOIN """ + self.TABLEUSERJOB + """ ON """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERJOB + """.userId - WHERE + WHERE jobCategoryId = %s""" self.exec_cmd(reqSearchConsultant, [jobId]) return self.cur.fetchall() @@ -65,18 +65,18 @@ class DbSearch(dbmain.DbMain): # 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 + SELECT + """ + self.TABLEUSERINFO + """.userId, + lastname, firstname, capability + FROM """ + self.TABLEUSERINFO + """ - INNER JOIN - """ + self.TABLEUSERACCOUNT + """ - ON + INNER JOIN + """ + self.TABLEUSERACCOUNT + """ + ON """ + self.TABLEUSERINFO + """.userId = """ + self.TABLEUSERACCOUNT + """.userId - WHERE - userStatus = '1' - AND + WHERE + userStatus = '1' + AND lastname LIKE CONCAT('%', %s, '%')""" self.exec_cmd(reqSearchConsultant, [lastname]) return self.cur.fetchall() @@ -84,16 +84,16 @@ class DbSearch(dbmain.DbMain): # 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 + SELECT + """ + self.TABLEUSERJOB + """.jobCategoryId, + jobCategoryNameEn, jobCategoryNameFr + FROM """ + self.TABLEUSERJOB + """ - INNER JOIN + INNER JOIN """ + self.TABLEJOBCATEGORY + """ - ON + ON """ + self.TABLEUSERJOB + """.jobCategoryId = """ + self.TABLEJOBCATEGORY + """.jobCategoryId - WHERE + WHERE userId = %s""" self.exec_cmd(reqGetAll, [proId]) return self.cur.fetchall() @@ -101,15 +101,15 @@ class DbSearch(dbmain.DbMain): # Récupération des notes d'un consultant par son nom def get_pro_note_by_proid(self, proId): reqCountMission = """ - SELECT - note - FROM + SELECT + note + FROM """ + self.TABLEMISSION + """ - INNER JOIN - """ + self.TABLEUSERINFO + """ - ON + INNER JOIN + """ + self.TABLEUSERINFO + """ + ON """ + self.TABLEMISSION + """.proId = """ + self.TABLEUSERINFO + """.userId - WHERE + WHERE proId = %s""" self.exec_cmd(reqCountMission, [proId]) return self.cur.fetchall() @@ -120,9 +120,9 @@ class DbSearch(dbmain.DbMain): # Récupérer toutes les infos de la table jobCategory def get_job_all(self) -> list: reqGetAllJobs = """ - SELECT - * - FROM + SELECT + * + FROM """ + self.TABLEJOBCATEGORY self.exec_cmd(reqGetAllJobs) return self.cur.fetchall() @@ -130,9 +130,9 @@ class DbSearch(dbmain.DbMain): # Récupérer toutes les infos de la table jobCategory def get_job_by_jobid(self, jobId: int) -> list: reqGetJobinfo = """ - SELECT - * - FROM + SELECT + * + FROM """ + self.TABLEJOBCATEGORY + """ WHERE jobCategoryId = %s""" diff --git a/src/database/dbuser.py b/src/database/dbuser.py index a57845c..9fbfeb6 100644 --- a/src/database/dbuser.py +++ b/src/database/dbuser.py @@ -18,7 +18,7 @@ class DbUser(dbmain.DbMain): # **************************************************************************** # GESTION DES UTILISATEURS # **************************************************************************** - # Enregistrement d'un nouvel utilisateur + # Enregistrement d'un nouvel utilisateur def register_user(self, email: str, password: str, userStatus: int) -> int: # On vérifie si l'email existe deja dans la bdd exist = self.check_exist_email(email) @@ -28,10 +28,10 @@ class DbUser(dbmain.DbMain): passwordHash = self.crypt_pass(password) # Préparation de la requète reqAddUser = """ - INSERT INTO - """ + self.TABLEUSERACCOUNT + """ - (email, password, inscriptionDate, userStatus) - VALUES + INSERT INTO + """ + self.TABLEUSERACCOUNT + """ + (email, password, inscriptionDate, userStatus) + VALUES (%s, %s, %s, %s)""" # Execution de la requète self.exec_cmd( @@ -52,11 +52,11 @@ class DbUser(dbmain.DbMain): def check_user_credential(self, email, password): # Préparation de la requète pour récupérer les infos ou l'email est présent reqCheckCredential = """ - SELECT - userId, password - FROM + SELECT + userId, password + FROM """ + self.TABLEUSERACCOUNT + """ - WHERE + WHERE email = %s """ # Execution de la requète self.exec_cmd(reqCheckCredential, [email]) @@ -71,32 +71,32 @@ class DbUser(dbmain.DbMain): isPro: bool, oldJobs: list = None, newJobs: list = None) -> None: # Préparation de la requète pour mise à jour des infos reqAddInfoClient = """ - REPLACE INTO - """ + self.TABLEUSERINFO + """ + REPLACE INTO + """ + self.TABLEUSERINFO + """ (lastname, firstname, phoneNumber, adress, zipCode, city, userId) - VALUES + VALUES (%s, %s, %s, %s, %s, %s, %s)""" reqAddInfoPro = """ - REPLACE INTO - """ + self.TABLEUSERINFO + """ + REPLACE INTO + """ + self.TABLEUSERINFO + """ (lastname, firstname, phoneNumber, adress, zipCode, city, degree, capability, description, userId) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" reqAddInfoProJobs = """ - INSERT INTO - """ + self.TABLEUSERJOB + """ - (jobCategoryId, userId) - VALUES + INSERT INTO + """ + self.TABLEUSERJOB + """ + (jobCategoryId, userId) + VALUES (%s, %s)""" reqRemoveInfoProJobs = """ - DELETE FROM - """ + self.TABLEUSERJOB + """ - WHERE + DELETE FROM + """ + self.TABLEUSERJOB + """ + WHERE jobCategoryId = %s - AND + AND userId = %s""" if not isPro: userInfos.append(userId) @@ -118,11 +118,11 @@ class DbUser(dbmain.DbMain): def check_token_autologin(self, email, token): # Préparation de la requète pour récupérer les infos ou l'email est présent reqCheckToken = """ - SELECT - userId, tokenAutoLogin - FROM - """ + self.TABLEUSERACCOUNT + """ - WHERE + SELECT + userId, tokenAutoLogin + FROM + """ + self.TABLEUSERACCOUNT + """ + WHERE email = %s""" # Exécution de la requète self.exec_cmd(reqCheckToken, [email]) @@ -135,11 +135,11 @@ class DbUser(dbmain.DbMain): def update_token_autologin(self, email, token): # Préparation de la requète pour mise à jour du jeton d'auto-connection reqUpdateToken = """ - UPDATE - """ + self.TABLEUSERACCOUNT + """ - SET + UPDATE + """ + self.TABLEUSERACCOUNT + """ + SET tokenAutoLogin = %s - WHERE + WHERE email = %s""" # Execution de la requète self.exec_cmd(reqUpdateToken, [token, email]) @@ -148,11 +148,11 @@ class DbUser(dbmain.DbMain): def del_token_autologin(self, email): # Préparation de la requète pour supression du jeton d'auto-connection reqDeleteToken = """ - UPDATE - """ + self.TABLEUSERACCOUNT + """ - SET - tokenAutoLogin = NULL - WHERE + UPDATE + """ + self.TABLEUSERACCOUNT + """ + SET + tokenAutoLogin = NULL + WHERE email = %s""" # Execution de la requète self.exec_cmd(reqDeleteToken, [email]) -- cgit v1.2.3