Mise à jour des fonctions de la bdd avec le nouveau modèle, renommage des fichier pour plus de clarte et ajout des documents et infos

3 files changed, 159 insertions, 78 deletions

diff --git a/Database/dbmain.php b/Database/dbmain.php
index 43e39ef..532bb7c 100644
--- a/Database/dbmain.php
+++ b/Database/dbmain.php
@@ -10,7 +10,7 @@
 // protected: accesible depuis l'extérieur et donc via extend mais non modifiable
 // public:    accessible et modifiable depuis l'extérieur
 // final:     ne peut etre ré-écris (overload)
-// 
+
 class DbMain {
     // Salt pour chiffrement
     private $salt = "cTKXHBoN3oxymlhNem2h";
@@ -19,54 +19,120 @@ class DbMain {
     private $host = "127.0.0.1";
     private $user = "root";
     private $pass = "";
+    private $debug = True;
     // Déclaration des tables
-    private $database = "ExoPhp";
-    // UserAccount, infos du compte nécessaire à la connection
+    private $database = "alphajob";
+
     // Timestamp auto si non renseigné
-    private $createTableUserAccount = "userAccount (
-        id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-        email           VARCHAR(128) NOT NULL,
-        password        VARCHAR(128) NOT NULL,
-        tokenAutoLogin  VARCHAR(128),
-        inscriptionDate TIMESTAMP NOT NULL,
-        isUser          BOOL NOT NULL,
-        isConsultant    BOOL NOT NULL,
-        isAdmin         BOOL NOT NULL
-    )";
-    // Infos complémentaires des utilisateur, liée à userAccount par userId à id
-    private $createTableUserInfo = "userInfo (
-        lastname    VARCHAR(128) NOT NULL,
-        firstname   VARCHAR(128) NOT NULL,
-        job         varchar(128),
-        degree      VARCHAR(128),
-        capability  TEXT,
-        description TEXT,
-        phoneNumber VARCHAR(10),
-        adress      VARCHAR(128),
-        zipCode     VARCHAR(6),
-        city        VARCHAR(128),
-        userId      INT UNSIGNED PRIMARY KEY,
-            FOREIGN KEY (userId) 
-            REFERENCES userAccount(id) 
-    )";
-    // Infos sur les missions effectuées, liée à userAccount par userId et consultantId à id
-    private $createTableMission = "mission (
-        id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-        date         TIMESTAMP NOT NULL,
-        subject      VARCHAR(128),
-        note         INT(1) UNSIGNED,
-        review       TEXT,
-        userId       INT UNSIGNED NOT NULL,
-            FOREIGN KEY (userId)
-            REFERENCES userAccount(id),
-        consultantId INT UNSIGNED NOT NULL,
-            FOREIGN KEY (consultantId)
-            REFERENCES userAccount(id)
-    )";
-    
+    // UserAccount, infos du compte nécessaire à la connection
+    private $createTableUserAccount = "CREATE TABLE IF NOT EXISTS `alphajob`.`userAccount` (
+        `userId`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
+        `email`           VARCHAR(128) NOT NULL,
+        `password`        VARCHAR(128) NOT NULL,
+        `tokenAutoLogin`  VARCHAR(128) NULL DEFAULT NULL,
+        `inscriptionDate` TIMESTAMP NOT NULL,
+        `isClient`        TINYINT(1) NOT NULL,
+        `isPro`           TINYINT(1) NOT NULL,
+        `isAdmin`         TINYINT(1) NOT NULL,
+        PRIMARY KEY (`userId`)
+        )
+        ENGINE = InnoDB
+        DEFAULT CHARACTER SET = utf8;";
+
+    // Infos complémentaires des utilisateur, liée à userAccount par userId
+    private $createTableUserInfo = "CREATE TABLE IF NOT EXISTS `alphajob`.`userInfo` (
+        `lastname`    VARCHAR(128) NOT NULL,
+        `firstname`   VARCHAR(128) NOT NULL,
+        `phoneNumber` VARCHAR(10) NOT NULL,
+        `adress`      VARCHAR(128) NOT NULL,
+        `zipCode`     VARCHAR(6) NOT NULL,
+        `city`        VARCHAR(128) NOT NULL,
+        `job`         VARCHAR(128) NULL DEFAULT NULL,
+        `degree`      VARCHAR(128) NULL DEFAULT NULL,
+        `capability`  TEXT NULL DEFAULT NULL,
+        `description` TEXT NULL DEFAULT NULL,
+        `userId`      INT UNSIGNED NOT NULL,
+        PRIMARY KEY (`userId`),
+        INDEX `fk_userinfo_userId_idx` (`userId` ASC) VISIBLE,
+        CONSTRAINT `fk_userinfo_userId`
+            FOREIGN KEY (`userId`)
+            REFERENCES `alphajob`.`userAccount` (`userId`)
+            ON DELETE CASCADE
+            ON UPDATE CASCADE
+        )
+        ENGINE = InnoDB
+        DEFAULT CHARACTER SET = utf8;";
+
+    // Liste des categories d'emploie possible
+    private $createTableJobCategory = "CREATE TABLE IF NOT EXISTS `alphajob`.`jobCategory` (
+        `jobCategoryId` INT UNSIGNED NOT NULL,
+        `categoryName`  VARCHAR(128) NOT NULL,
+        PRIMARY KEY (`jobCategoryId`)
+        )
+        ENGINE = InnoDB
+        DEFAULT CHARACTER SET = utf8;";
+
+    // Assosications des emploies à un utilisateur
+    private $createTableUserJob = "CREATE TABLE IF NOT EXISTS `alphajob`.`userJob` (
+        `userId`        INT UNSIGNED NOT NULL,
+        `jobCategoryId` INT UNSIGNED NOT NULL,
+        INDEX `fk_userInfo_userId_idx` (`userId` ASC) VISIBLE,
+        INDEX `fk_jobCategory_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE,
+        CONSTRAINT `fk_userJob_userId`
+            FOREIGN KEY (`userId`)
+            REFERENCES  `alphajob`.`userAccount` (`userId`)
+            ON DELETE NO ACTION
+            ON UPDATE CASCADE,
+        CONSTRAINT `fk_userJob_jobCategoryId`
+            FOREIGN KEY (`jobCategoryId`)
+            REFERENCES  `alphajob`.`jobCategory` (`jobCategoryId`)
+            ON DELETE NO ACTION
+            ON UPDATE CASCADE
+        )
+        ENGINE = InnoDB
+        DEFAULT CHARACTER SET = utf8;";
+
+    // Infos sur les missions effectuées, liée à userAccount par userId et consultantId
+    private $createTableMission = "CREATE TABLE IF NOT EXISTS `alphajob`.`mission` (
+        `missionId`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
+        `date`              TIMESTAMP NOT NULL,
+        `subject`           VARCHAR(128) NULL DEFAULT NULL,
+        `note`              INT UNSIGNED NULL DEFAULT NULL,
+        `review`            TEXT NULL DEFAULT NULL,
+        `acceptedByPro`     TIMESTAMP NULL DEFAULT NULL,
+        `validatedByClient` TIMESTAMP NULL DEFAULT NULL,
+        `validatedByPro`    TIMESTAMP NULL DEFAULT NULL,
+        `clientId`          INT UNSIGNED NOT NULL,
+        `proId`             INT UNSIGNED NOT NULL,
+        `jobCategoryId`     INT UNSIGNED NOT NULL,
+        PRIMARY KEY (`missionId`),
+        INDEX `fk_mission_clientId_idx` (`clientId` ASC) VISIBLE,
+        INDEX `fk_mission_proId_idx` (`proId` ASC) VISIBLE,
+        INDEX `fk_mission_jobCategoryId_idx` (`jobCategoryId` ASC) VISIBLE,
+        CONSTRAINT `fk_mission_clientId`
+            FOREIGN KEY (`clientId`)
+            REFERENCES  `alphajob`.`userAccount` (`userId`)
+            ON DELETE NO ACTION
+            ON UPDATE CASCADE,
+        CONSTRAINT `fk_mission_proId`
+            FOREIGN KEY (`proId`)
+            REFERENCES  `alphajob`.`userAccount` (`userId`)
+            ON DELETE NO ACTION
+            ON UPDATE CASCADE,
+        CONSTRAINT `fk_mission_jobCategoryId`
+            FOREIGN KEY (`jobCategoryId`)
+            REFERENCES  `alphajob`.`jobCategory` (`jobCategoryId`)
+            ON DELETE NO ACTION
+            ON UPDATE CASCADE
+        )
+        ENGINE = InnoDB
+        DEFAULT CHARACTER SET = utf8;";
+
     // Noms des tables
     protected $tableUserAccount = "userAccount";
     protected $tableUserInfo = "userInfo";
+    protected $tableJobCategory = "jobCategory";
+    protected $tableUserJob = "userJob";
     protected $tableMission = "mission";
 
     // Création de la DB si elle n'existe pas
@@ -85,13 +151,24 @@ class DbMain {
 
     // Création des tables si elles n'existent pas
     private function create_table() {
-        $reqInitUserAccount = "CREATE TABLE IF NOT EXISTS ".$this->createTableUserAccount;
+        // userAccount
+        $reqInitUserAccount = $this->createTableUserAccount;
         $res = $this->conn->prepare($reqInitUserAccount);
         $res->execute();
-        $reqInitUserInfo = "CREATE TABLE IF NOT EXISTS ".$this->createTableUserInfo;
+        // userInfo
+        $reqInitUserInfo = $this->createTableUserInfo;
         $res = $this->conn->prepare($reqInitUserInfo);
         $res->execute();
-        $reqInitMission = "CREATE TABLE IF NOT EXISTS ".$this->createTableMission;
+        // jobCategory
+        $reqInitJobCategory = $this->createTableJobCategory;
+        $res = $this->conn->prepare($reqInitJobCategory);
+        $res->execute();
+        // userJob
+        $reqInitUserJob = $this->createTableUserJob;
+        $res = $this->conn->prepare($reqInitUserJob);
+        $res->execute();
+        // mission
+        $reqInitMission = $this->createTableMission;
         $res = $this->conn->prepare($reqInitMission);
         $res->execute();
     }
@@ -127,8 +204,8 @@ class DbMain {
     }
 
     // Vérifie si l'id est un consultant dans la table userAccount de la BDD
-    final public function check_exist_consultant($id) {
-        $reqCheckExistConsultant = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ? AND isConsultant = '1')";
+    final public function check_exist_pro($id) {
+        $reqCheckExistConsultant = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ? AND isPro = '1')";
         $data = $this->exec_cmd($reqCheckExistConsultant, array($id))->fetchAll(PDO::FETCH_NUM);
         // Retourne 1 si existe, sinon 0 
         return $data[0][0];
@@ -137,7 +214,11 @@ class DbMain {
     // Fonction "auto", déclenchée à l'instanciation
     final public function __construct() {
         // Essaie de se connecter a la BDD
-        $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass);
+        if ($this->debug) {
+            $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
+        } else {
+            $this->conn = new PDO("mysql:host=".$this->host, $this->user, $this->pass,  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT));
+        }
     }
 }
 ?>
\ No newline at end of file
diff --git a/Database/meth_dbsearch.php b/Database/meth_dbsearch.php
index 0a325aa..a90925c 100644
--- a/Database/meth_dbsearch.php
+++ b/Database/meth_dbsearch.php
@@ -19,8 +19,8 @@ class DbSearch extends DbMain {
 
     // Récupération des infos d'un compte par son id
     final public function search_user_by_id($id) {
-        $reqSearchUser = "SELECT id, email, inscriptionDate, isAdmin, isUser, isConsultant 
-            FROM ".$this->tableUserAccount." WHERE id = ?";
+        $reqSearchUser = "SELECT userId, email, inscriptionDate, isClient, isPro, isAdmin
+            FROM ".$this->tableUserAccount." WHERE userId = ?";
         $data = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC);
         return $data;
     }
@@ -37,10 +37,10 @@ class DbSearch extends DbMain {
 
     // Récupération des infos d'un consultant par son nom
     final public function search_consultant_by_name($research) {
-        $reqSearchConsultant = "SELECT id, lastname, firstname, job, capability FROM ".$this->tableUserInfo."
+        $reqSearchConsultant = "SELECT userId, lastname, firstname, job, capability FROM ".$this->tableUserInfo."
             INNER JOIN ".$this->tableUserAccount." 
-            ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".id
-            WHERE isConsultant = '1' AND lastname LIKE CONCAT('%', ?, '%')";
+            ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".userId
+            WHERE isPro = '1' AND lastname LIKE CONCAT('%', ?, '%')";
         $data = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC);
         return $data;
     }
@@ -49,7 +49,7 @@ class DbSearch extends DbMain {
     final public function get_consultant_note_by_name($name) {
         $reqCountMission = "SELECT note FROM ".$this->tableMission."
             INNER JOIN ".$this->tableUserInfo." 
-            ON ".$this->tableMission.".consultantId = ".$this->tableUserInfo.".userId
+            ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId
             WHERE lastname = ?";
         $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
         return $data;
@@ -58,9 +58,9 @@ class DbSearch extends DbMain {
     // Récupérations des missions d'un consultant par son id
     final public function get_all_mission_by_id($id) {
         $reqGetAllMission = "SELECT subject, note, review, lastname FROM ".$this->tableMission."
-            INNER JOIN ".$this->tableUserInfo." 
-            ON ".$this->tableMission.".userId = ".$this->tableUserInfo.".userId
-            WHERE consultantId = ?";
+            LEFT JOIN ".$this->tableUserInfo." 
+            ON ".$this->tableMission.".missionId = ".$this->tableUserInfo.".userId
+            WHERE proId = ?";
         $data = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC);
         return $data;
     }
@@ -69,7 +69,7 @@ class DbSearch extends DbMain {
     final public function count_consultant_mission_by_name($name) {
         $reqCountMission = "SELECT COUNT(*) FROM ".$this->tableMission."
             INNER JOIN ".$this->tableUserInfo." 
-            ON ".$this->tableMission.".consultantId = ".$this->tableUserInfo.".userId
+            ON ".$this->tableMission.".proId = ".$this->tableUserInfo.".userId
             WHERE lastname = ?";
         $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
         return $data;
diff --git a/Database/meth_dbuser.php b/Database/meth_dbuser.php
index 8e6ec35..cba1683 100644
--- a/Database/meth_dbuser.php
+++ b/Database/meth_dbuser.php
@@ -9,7 +9,7 @@ require_once(dirname( __FILE__ )."/dbmain.php");
 // Extension de cette classe avec dbmain
 class DbUser extends DbMain {
     // Enregistrement d'un nouvel utilisateur 
-    final public function add_user($email, $pass, $is_admin, $is_user, $is_consultant) {
+    final public function add_user($email, $pass, $is_client, $is_pro, $is_admin) {
         // On vérifie si l'email existe deja dans la bdd
         $exist = $this->check_exist_email($email);
         // Si non
@@ -18,16 +18,16 @@ class DbUser extends DbMain {
             $crypt = $this->crypt_pass($pass);
             // Préparation de la requète
             $reqAddUser = "INSERT INTO ".$this->tableUserAccount." 
-                (email, password, inscriptionDate, isAdmin, isUser, isConsultant) 
+                (email, password, inscriptionDate, isClient, isPro, isAdmin) 
                 VALUES (?, ?, ?, ?, ?, ?)";
             // Execution de la requète
             $this->exec_cmd($reqAddUser,
                             array($email,
                                   $crypt,
                                   date("Y-m-d H:i:s"),
-                                  $is_admin,
-                                  $is_user,
-                                  $is_consultant
+                                  $is_client,
+                                  $is_pro,
+                                  $is_admin
                                   )
                             );
             // Retourne 1 pour réussite
@@ -39,17 +39,17 @@ class DbUser extends DbMain {
     }
 
     // Suppresion d'un utilisateur
-    final public function del_user($id) {
+    final public function del_user($userId) {
         // Préparation de la requète pour voir si l'utilisateur existe
-        $reqCheckExistId = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE id = ?)";
+        $reqCheckExistId = "SELECT EXISTS(SELECT * FROM ".$this->tableUserAccount." WHERE userId = ?)";
         // Exécution de la requète
-        $data = $this->exec_cmd($reqCheckExistId, array($id))->fetchAll(PDO::FETCH_NUM);
+        $data = $this->exec_cmd($reqCheckExistId, array($userId))->fetchAll(PDO::FETCH_NUM);
         // Si il existe
         if ($data[0][0]) {
             // Préparation de la requète pour suppression
-            $reqDelUser = "DELETE FROM ".$this->tableUserAccount." WHERE id = ?";
+            $reqDelUser = "DELETE FROM ".$this->tableUserAccount." WHERE userId = ?";
             // Exécution de la requète
-            $this->exec_cmd($reqDelUser, array($id));
+            $this->exec_cmd($reqDelUser, array($userId));
             // Retourne 1 pour réussite
             return 1;
         } else {
@@ -77,20 +77,20 @@ class DbUser extends DbMain {
     }
 
     // Enregistrement d'une mission
-    final public function register_mission($subject, $note, $review, $userId, $consultantId) {
+    final public function register_mission($subject, $note, $review, $clientId, $proId) {
         // Vérification de l'existance de l'id de consultant
-        $exist = $this->check_exist_consultant($consultantId);
+        $exist = $this->check_exist_pro($proId);
         // Si il existe
         if ($exist) {  
             // Préparation de la requète pour enregistrement de la mission
             $reqAddMission = "INSERT INTO ".$this->tableMission." 
-                (date, subject, note, review, userId, consultantId) 
+                (date, subject, note, review, clientId, proId) 
                 VALUES (?, ?, ?, ?, ?, ?)";
             // Exécution de la requète
             $this->exec_cmd($reqAddMission,
                             array(date("Y-m-d H:i:s"), $subject, 
                                   $note, $review, 
-                                  $userId, $consultantId
+                                  $clientId, $proId
                                   )
                             );
             // Retourne 1 pour réussite
@@ -104,7 +104,7 @@ class DbUser extends DbMain {
     // Vérification de la combinaison email - pass pour authentification
     final public function check_credential($email, $pass) {
         // Préparation de la requète pour récupérer les infos ou l'email est présent
-        $reqCheckCredential = "SELECT id, password 
+        $reqCheckCredential = "SELECT userId, password 
             FROM ".$this->tableUserAccount." WHERE email = ? ";
         // Chiffrement du mot de passe
         $crypt = $this->crypt_pass($pass);
@@ -113,7 +113,7 @@ class DbUser extends DbMain {
         // Vérifiaction de correspondance email & pass
         foreach ($data as $rows) {
             if ($rows["password"] == $crypt) {
-                return $rows["id"];
+                return $rows["userId"];
             }
         }
     }
@@ -121,14 +121,14 @@ class DbUser extends DbMain {
     // Vérification de la combinaison email - jeton d'auto-login pour authentification
     final public function check_token_autologin($email, $token) {
         // Préparation de la requète pour récupérer les infos ou l'email est présent
-        $reqCheckToken = "SELECT id, tokenAutoLogin 
+        $reqCheckToken = "SELECT userId, tokenAutoLogin 
             FROM ".$this->tableUserAccount." WHERE email = ?";
         // Exécution de la requète
         $data = $this->exec_cmd($reqCheckToken, array($email))->fetchAll(PDO::FETCH_ASSOC);
         // Vérifiaction de correspondance email & jeton d'auto-connection
         foreach ($data as $rows) {
             if ($rows["tokenAutoLogin"] == $token) {
-                return $rows["id"];
+                return $rows["userId"];
             }
         }
     }