summaryrefslogtreecommitdiff
path: root/src/database
diff options
context:
space:
mode:
Diffstat (limited to 'src/database')
-rw-r--r--src/database/dbadmin.py64
-rw-r--r--src/database/dbmission.py113
-rw-r--r--src/database/dbsearch.py88
-rw-r--r--src/database/dbuser.py72
4 files changed, 168 insertions, 169 deletions
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])