From 5ddf8de3691fde866c5a48b440cffa2990b2014c Mon Sep 17 00:00:00 2001
From: Debulois <quentin@debulois.fr>
Date: Thu, 3 Feb 2022 13:02:21 +0100
Subject: Initial commit

---
 Database/meth_dbsearch.php | 78 ++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 78 insertions(+)
 create mode 100644 Database/meth_dbsearch.php

(limited to 'Database/meth_dbsearch.php')

diff --git a/Database/meth_dbsearch.php b/Database/meth_dbsearch.php
new file mode 100644
index 0000000..0a325aa
--- /dev/null
+++ b/Database/meth_dbsearch.php
@@ -0,0 +1,78 @@
+<?php
+// ****************************************************************************
+// 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
+require_once(dirname( __FILE__ )."/dbmain.php");
+// Extension de cette classe avec dbmain
+class DbSearch extends DbMain {
+    // Récupérer toutes les infos de la table userAccount
+    final public function search_all() {
+        $reqGetAll = "SELECT * FROM ".$this->tableUserAccount;
+        $data = $this->exec_cmd($reqGetAll, array())->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+
+    // 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 = ?";
+        $data = $this->exec_cmd($reqSearchUser, array($id))->fetch(PDO::FETCH_ASSOC);
+        return $data;
+    }
+
+    // Récupération des infos d'un utilisateur par son id
+    final public function get_user_info_by_id($id) {
+        $reqGetUserInfo = "SELECT lastname, firstname, job, degree, 
+                                capability, description, phoneNumber,
+                                adress, zipCode, city 
+            FROM ".$this->tableUserInfo." WHERE userId = ?";
+        $data = $this->exec_cmd($reqGetUserInfo, array($id))->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+
+    // 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."
+            INNER JOIN ".$this->tableUserAccount." 
+            ON ".$this->tableUserInfo.".userId = ".$this->tableUserAccount.".id
+            WHERE isConsultant = '1' AND lastname LIKE CONCAT('%', ?, '%')";
+        $data = $this->exec_cmd($reqSearchConsultant, array($research))->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+    
+    // Récupération des notes d'un consultant par son nom
+    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
+            WHERE lastname = ?";
+        $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+
+    // 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 = ?";
+        $data = $this->exec_cmd($reqGetAllMission, array($id))->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+
+    // Compte le nombre de mission d'un consultant par son nom
+    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
+            WHERE lastname = ?";
+        $data = $this->exec_cmd($reqCountMission, array($name))->fetchAll(PDO::FETCH_ASSOC);
+        return $data;
+    }
+}
+?>
\ No newline at end of file
-- 
cgit v1.2.3