141 lines
4.1 KiB
PHP
141 lines
4.1 KiB
PHP
<?php
|
|
/**
|
|
* Created by PhpStorm.
|
|
* User: lucas
|
|
* Date: 20/02/18
|
|
* Time: 21:00
|
|
*/
|
|
|
|
namespace database\repo;
|
|
|
|
|
|
use database\core\Repo_i;
|
|
|
|
class category extends Repo_i{
|
|
|
|
public function create(int $id, string $label) : bool{
|
|
//create the category or update the label
|
|
$st = $this->pdo->prepare("INSERT INTO Categorie (idCategorie, labelCategorie) VALUE (:id, :label)");
|
|
|
|
return $st->execute([
|
|
"id" => $id,
|
|
"label" => $label
|
|
]);
|
|
|
|
}
|
|
|
|
public function delete(int $id) :bool{
|
|
$st = $this->pdo->prepare("DELETE FROM Categorie WHERE idCategorie = :id");
|
|
|
|
return $st->execute([
|
|
"id" => $id
|
|
]);
|
|
}
|
|
|
|
public function getStats(?int $id = null){
|
|
$st = $this->pdo->prepare("SELECT IFNULL(VHCours,0) VHCours, IFNULL(VHTd,0)VHTd, IFNULL(VHTp,0)VHTp, Cat.idCategorie idCat, count(P.idProfesseur) nbrProf, Cat.labelCategorie labelCat
|
|
FROM Categorie Cat
|
|
LEFT JOIN (SELECT IFNULL(SUM(Cours.volume),0) VHCours, Prof.Categorie_idCategorie idCat
|
|
FROM Professeur Prof
|
|
LEFT JOIN Cours ON Prof.idProfesseur = Cours.Professeur_idProfesseur
|
|
LEFT JOIN UE U ON Cours.UE_code = U.code
|
|
GROUP BY Prof.Categorie_idCategorie, U.disabled
|
|
HAVING U.disabled = 0) VHCours ON VHCours.idCat = Cat.idCategorie
|
|
|
|
LEFT JOIN (SELECT IFNULL(SUM(TD.volume),0) VHTd , Prof.Categorie_idCategorie idCat
|
|
FROM Professeur Prof
|
|
LEFT JOIN TD ON TD.Professeur_idProfesseur = Prof.idProfesseur
|
|
LEFT JOIN UE U2 ON TD.UE_code = U2.code
|
|
GROUP BY Prof.Categorie_idCategorie, U2.disabled
|
|
HAVING U2.disabled = 0) VHTd ON VHTd.idCat = Cat.idCategorie
|
|
|
|
LEFT JOIN (SELECT IFNULL(SUM(TP.volume),0) VHTp, Prof.Categorie_idCategorie idCat
|
|
FROM Professeur Prof
|
|
LEFT JOIN TP ON TP.Professeur_idProfesseur = Prof.idProfesseur
|
|
LEFT JOIN UE U3 ON TP.UE_code = U3.code
|
|
GROUP BY Prof.Categorie_idCategorie, U3.disabled
|
|
HAVING U3.disabled = 0) VHTp ON VHTp.idCat = Cat.idCategorie
|
|
LEFT JOIN Professeur P ON Cat.idCategorie = P.Categorie_idCategorie
|
|
".($id ? " WHERE Cat.idCategorie = :idCat" : "")."
|
|
GROUP BY Cat.idCategorie;");
|
|
|
|
$st->execute($id ? ["idCat" => $id] : []);
|
|
|
|
if($id){
|
|
return $st->fetch() ?: [];
|
|
}else{
|
|
return $st->fetchAll();
|
|
}
|
|
}
|
|
|
|
|
|
/* (4) Gets a category by its UID ||| getAll
|
|
*
|
|
* @cat_id<int> [OPT] The category UID, if not set, getAll()
|
|
*
|
|
* @return categories<array> The categories matching id (NULL on error)
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function get(?int $cat_id=null) : ?array{
|
|
|
|
/* (1) Manage if no id given */
|
|
$cond = is_null($cat_id) ? '' : ' WHERE `idCategorie` = :id';
|
|
$parm = is_null($cat_id) ? [] : [':id' => $cat_id];
|
|
|
|
/* (2) Prepare Statement */
|
|
$st = $this->pdo->prepare("SELECT * FROM `Categorie`$cond ORDER BY `labelCategorie` ASC");
|
|
|
|
/* (3) Bind params and execute statement */
|
|
if( is_bool($st) ) return [];
|
|
$success = $st->execute($parm);
|
|
|
|
/* (4) Manage error */
|
|
if( !$success )
|
|
return [];
|
|
|
|
/* (5) Get data */
|
|
$fetched = $st->fetchAll();
|
|
|
|
/* (6) Return [] on no result */
|
|
if( $fetched === false )
|
|
return [];
|
|
|
|
/* (7) Return data */
|
|
return $fetched;
|
|
|
|
}
|
|
|
|
|
|
/* (5) Gets all professors who teaches a category by ids (array)
|
|
*
|
|
* @cat_id<int> The category id
|
|
*
|
|
* @return professors<array> The professors' UID matching the @cat_id category
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function getProfessors(int $cat_id) : array{
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare("SELECT idProfesseur FROM Professeur WHERE Categorie_idCategorie = :cat_id;");
|
|
|
|
/* (2) Bind params and execute statement */
|
|
if( is_bool($st) ) return [];
|
|
$success = $st->execute([ ':cat_id' => $cat_id ]);
|
|
|
|
/* (3) Manage error */
|
|
if( !$success )
|
|
return [];
|
|
|
|
/* (4) Get data */
|
|
$fetched = $st->fetchAll();
|
|
|
|
/* (5) Return [] on no result */
|
|
if( $fetched === false )
|
|
return [];
|
|
|
|
/* (6) Return data */
|
|
return $fetched;
|
|
|
|
}
|
|
|
|
} |