ptut-vhost/build/database/repo/professor.php

570 lines
16 KiB
PHP

<?php
/**
* Created by PhpStorm.
* User: lucas
* Date: 20/02/18
* Time: 20:31
*/
namespace database\repo;
use database\core\Repo_i;
use database\core\Repo;
class professor extends Repo_i {
/* (1) Creates a new professor
*
* @lastName<String> The professor's lastName
* @firstName<String> The professor's firstName
* @category<int> The professor's category ID
* @hoursToDo<int> The professor's number of hours to do
* @initials<int> The professor's initials
* @isAdmin<bool> Whether the professor is an admin
* @casLogin<String> The professor's CAS username
*
* @return prof_id<int> The professor's UID (or -1 on error)
*
---------------------------------------------------------*/
public function create(string $lastName, string $firstName, int $category, int $hoursToDo = 0, ?string $initials = null , bool $isAdmin = false , ?string $casLogin = null ) : ?int{
/* (1) Create professor into local database
---------------------------------------------------------*/
/* (1) Prepare Statement */
$st = $this->pdo->prepare("INSERT INTO
Professeur(`casLogin`, `lastName`, `firstName`, `abreviation`, `admin`, `hoursToDo`, `Categorie_idCategorie`)
VALUE (:casLogin, :lastName, :firstName, :abrev, :is_admin, :hoursToDo, :cat);");
/* (2) Manage statement error */
if( is_bool($st) )
return NULL;
/* (3) Bind params and execute */
$success = $st->execute([
':casLogin' => $casLogin,
':lastName' => $lastName,
':firstName' => $firstName,
':abrev' => $initials,
':is_admin' => $isAdmin ? 1 : 0,
':hoursToDo' => $hoursToDo,
':cat' => $category
]);
/* (4) If execution error -> dispatch */
if( !$success )
return NULL;
/* (5) Store id */
$id_prof = $this->pdo->lastInsertId();
/* (6) Exit now if no meta database to udpate */
if( is_null($casLogin) )
return $id_prof;
/* (2) Synchronize meta database
---------------------------------------------------------*/
/** @var meta $meta_repo */
$meta_repo = Repo::getRepo('meta');
/* (1) If user does not exist */
if( !$meta_repo->prof_exists($casLogin) ){
/* (2) Try to create -> dispatch error */
if( !$meta_repo->create_prof($casLogin, $firstName, $lastName) )
return NULL;
}
/* (3) If link already exists -> done */
if( $meta_repo->link_exists($casLogin, $_SESSION['CurrentDepartmentId']) )
return $id_prof;
/* (4) Else: try to link prof to dep -> dispatch error */
if( !$meta_repo->link($casLogin, $_SESSION['CurrentDepartmentId']) )
return NULL;
/* (5) If reached here -> Success */
return $id_prof;
}
/* (2) Check if a professor exists (by its names)
*
* @lastName<String> The professor's lastName
* @firstName<String> The professor's firstName
*
* @return prof_id<int> The professor's UID (or NULL on error)
*
---------------------------------------------------------*/
public function exists(string $lastName, string $firstName, ?string $casLogin = null) : ?int{
/* (1) Manage if @casLogin given/ignored */
$cond = is_null($casLogin) ? '' : 'OR `casLogin` = :casLogin';
$parm = is_null($casLogin) ? [] : [':casLogin' => $casLogin];
/* (2) Prepare Statement */
$st = $this->pdo->prepare("SELECT idProfesseur
FROM Professeur
WHERE ( firstName = :firstName AND lastName = :lastName )
$cond;");
/* (3) Statement eror */
if( is_bool($st) )
return NULL;
/* (4) Bind params and execute */
$params = array_merge([
':firstName' => $firstName,
':lastName' => $lastName
], $parm);
$success = $st->execute($params);
/* (5) Return NULL on error */
if( !$success )
return NULL;
/* (7) Return @prof_id or NULL if nothing found */
return $st->fetch()['idProfesseur'] ?: NULL;
}
/* (3) Updates a professor's data
*
* @idProf<int> The professor's UID
* @lastName<String> [OPT] The professor's new lastName
* @firstName<String> [OPT] The professor's new firstName
* @category<int> [OPT] The professor's new category ID
* @hoursToDo<int> [OPT] The professor's new number of hours to do
* @initials<int> [OPT] The professor's new initials
* @isAdmin<bool> [OPT] Whether the professor is an admin
* @casLogin<String> [OPT] The professor's new CAS username
*
* @return updated<bool> Whether the updated have been successful
*
---------------------------------------------------------*/
public function update(int $id, ?String $lastName, ?String $firstName, ?int $category, ?int $hoursToDo, ?String $initials, ?bool $isAdmin, ?String $casLogin) : bool{
/* (1) Check professor data
---------------------------------------------------------*/
/* (1) Try to fetch professor's data */
$prof = $this->get($id);
/* (2) Error: no professor found */
if( count($prof) === 0 )
return FALSE;
/* (3) Take first matching professor */
$prof = $prof[0];
/* (4) Extract old CAS login */
$oldCasLogin = $prof['casLogin'];
/* (2) Update current database
---------------------------------------------------------*/
/* (1) Build request */
$build_rq = [];
$bind_param = [ ':idProfesseur' => $id ];
if( !is_null($lastName) ){ $build_rq[] = '`lastName` = :lastName'; $bind_param[':lastName'] = $lastName; }
if( !is_null($firstName) ){ $build_rq[] = '`firstName` = :firstName'; $bind_param[':firstName'] = $firstName; }
if( !is_null($category) ){ $build_rq[] = '`Categorie_idCategorie` = :category'; $bind_param[':category'] = $category; }
if( !is_null($hoursToDo) ){ $build_rq[] = '`hoursToDo` = :hoursToDo'; $bind_param[':hoursToDo'] = $hoursToDo; }
if( !is_null($initials) ){ $build_rq[] = '`abreviation` = :initials'; $bind_param[':initials'] = $initials; }
if( !is_null($isAdmin) ){ $build_rq[] = '`admin` = :isAdmin'; $bind_param[':isAdmin'] = $isAdmin?1:0; }
if( !is_null($casLogin) ){ $build_rq[] = '`casLogin` = :casLogin'; $bind_param[':casLogin'] = $casLogin; }
/* (2) ERROR if no updated field */
if( count($build_rq) <= 0 || count($bind_param) <= 1 )
return FALSE;
/* (3) Build request */
$sql_rq = "UPDATE `Professeur` SET ".implode(', ', $build_rq)." WHERE `idProfesseur` = :idProfesseur";
/* (4) Prepare statement */
$st = $this->pdo->prepare($sql_rq);
/* (5) Return execution success */
$success = $st->execute($bind_param);
/* (6) Manage execution error */
if( !$success )
return FALSE;
/* (7) If do not have to update 'meta' database -> success */
if( is_null($casLogin) )
return TRUE;
/** @var meta $meta_repo */
$meta_repo = Repo::getRepo('meta');
/* (3) Synchronize meta database -> remove old value
---------------------------------------------------------*/
if( !is_null($oldCasLogin) && strlen($oldCasLogin) > 0 ){
/* Proccess only if professor exists */
if( $meta_repo->prof_exists($oldCasLogin) ){
/* (1) If link exists -> remove it */
if( $meta_repo->link_exists($oldCasLogin, $_SESSION['CurrentDepartmentId']) ){
/* (2) Try to unlink-> ignore error */
$meta_repo->unlink($oldCasLogin, $_SESSION['CurrentDepartmentId']);
}
/* (3) If has no more department -> remove professor */
if( count( $meta_repo->get_prof_departments($oldCasLogin) ) == 0 ){
/* (4) Try to remove professor -> dispatch error */
if( !$meta_repo->delete_prof($oldCasLogin) )
return FALSE;
}
}
}
/* (4) Synchronize meta database -> create new
---------------------------------------------------------*/
if( !is_null($casLogin) && strlen($casLogin) > 0 ){
/* (1) If user does not exist */
if( !$meta_repo->prof_exists($casLogin) ){
/* (2) Try to create -> dispatch error */
if( !$meta_repo->create_prof($casLogin, $prof['firstName'], $prof['lastName']) )
return FALSE;
}
/* (3) If link already exists -> done */
if( $meta_repo->link_exists($casLogin, $_SESSION['CurrentDepartmentId']) )
return TRUE;
/* (4) Else: try to link prof to dep -> dispatch error */
if( !$meta_repo->link($casLogin, $_SESSION['CurrentDepartmentId']) )
return FALSE;
}
/* (5) If reached here -> Success */
return TRUE;
}
/* (4) Return whether a professor is an admin
*
* @idProf<int> The professor's UID
*
* @return is_admin<bool> Whether the professor is an admin (FALSE if does not exist)
*
---------------------------------------------------------*/
public function isAdmin(int $id) : bool{
/* (1) Prepare statement */
$st = $this->pdo->prepare("SELECT `admin` FROM `Professeur` WHERE `idProfesseur` = :id AND `admin` = 1");
/* (2) Bind params and execute statement */
$success = $st->execute([ ':id' => $id ]);
/* (3) FALSE on error */
if( !$success )
return FALSE;
/* (4) Return whether we have a result or not */
return $st->fetch() == 1;
}
/* (5) Gets a professor by its UID ||| getAll
*
* @prof_id<int> [OPT] The professor's UID, if not set, getAll()
*
* @return teachers<array> The professors matching id (NULL on error)
*
---------------------------------------------------------*/
public function get(?int $prof_id=null) : array{
/* (1) Manage if no id given */
$cond = is_null($prof_id) ? '' : ' WHERE `idProfesseur` = :id';
$parm = is_null($prof_id) ? [] : [':id' => $prof_id];
/* (2) Prepare Statement */
$st = $this->pdo->prepare("SELECT * FROM `Professeur`$cond ORDER BY firstName, lastName 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;
}
/* (6) Gets a professor by its CAS login
*
* @cas_login<String> The professor's CAS login
*
* @return teacher<array> The professor's data (NULL on error / not found)
*
---------------------------------------------------------*/
public function getByLogin(String $cas_login) : ?array{
/* (1) Prepare Statement */
$st = $this->pdo->prepare("SELECT * FROM `Professeur` WHERE `casLogin` = :cas_login");
/* (2) Check if statement error */
if( is_bool($st) )
return NULL;
/* (3) Bind params and execute statement */
$success = $st->execute([ ':cas_login' => $cas_login ]);
/* (4) Manage error */
if( !$success )
return NULL;
/* (5) Get data */
$fetched = $st->fetch();
/* (6) Return NULL on no result */
if( $fetched === false )
return NULL;
/* (7) Return data */
return $fetched;
}
/* (7) Gets a professor by its UID ||| getAll
*
* @prof_id<int> [OPT] The professor's UID, if not set, getAll()
*
* @return teachers<array> The professors matching id (NULL on error)
*
---------------------------------------------------------*/
public function getWithVH(?int $prof_id=null) : array{
/* (1) Manage if no id given */
$cond = is_null($prof_id) ? '' : 'Prof.idProfesseur = :id AND';
$parm = is_null($prof_id) ? [] : [':id' => $prof_id];
/* (2) Prepare Statement */
$st = $this->pdo->prepare("SELECT
Prof.idProfesseur, Prof.firstName, Prof.lastName, Prof.casLogin, Prof.abreviation, Prof.admin, Prof.hoursToDo,
VHCours, VHTd, VHTp,
Cat.idCategorie idCat,
Cat.labelCategorie categorie
FROM
Professeur Prof,
Categorie Cat,
(SELECT IFNULL(SUM(Cours.volume),0) VHCours, Prof.idProfesseur idProf
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.idProfesseur, U.disabled
HAVING (U.disabled = 0 OR U.disabled IS NULL)) VHCours,
(SELECT IFNULL(SUM(TD.volume),0) VHTd , Prof.idProfesseur idProf
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.idProfesseur, U2.disabled
HAVING (U2.disabled = 0 OR U2.disabled IS NULL)) VHTd,
(SELECT IFNULL(SUM(TP.volume),0) VHTp, Prof.idProfesseur idProf
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.idProfesseur, U3.disabled
HAVING (U3.disabled = 0 OR U3.disabled IS NULL)) VHTp
WHERE $cond Prof.Categorie_idCategorie = Cat.idCategorie
AND VHCours.idProf = Prof.idProfesseur
AND VHTp.idProf = Prof.idProfesseur
AND VHTd.idProf = Prof.idProfesseur
GROUP BY
Prof.idProfesseur
ORDER BY Prof.firstName, Prof.lastName 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) Compute additional data */
foreach ($fetched as &$prof){
/* (1) If not in category 1 nor 3 -> equivalentTD = TD + 2/3.TP + 1.5.COURS */
if(in_array($prof["idCat"],[1,3])){
$prof["equiTD"] = $prof["VHTd"] + $prof["VHTp"] + 1.5*$prof["VHCours"];
if($prof["equiTD"] > $prof["hoursToDo"]){
$partTP = $prof["VHTp"] / $prof["equiTD"];
$valReelleTP = $partTP * $prof["hoursToDo"];
$prof["equiTD"] = round(1.5*$prof["VHCours"] + $prof["VHTd"] + $valReelleTP + ($prof["VHTp"] - $valReelleTP)*(2/3),2);
}
$prof['VHComp'] = round($prof['equiTD'] - $prof['hoursToDo'], 2);
$prof['VHComp'] = ( $prof['VHComp'] < 0 ) ? 0 : $prof['VHComp'];
}else{
$prof["equiTD"] = $prof["VHTd"] + (2/3)*$prof["VHTp"] + 1.5*$prof["VHCours"];
if(is_numeric($prof["hoursToDo"]) and $prof["hoursToDo"] > 0){
$prof['VHComp'] = round($prof['equiTD'] - $prof['hoursToDo'], 2);
$prof['VHComp'] = ( $prof['VHComp'] < 0 ) ? 0 : $prof['VHComp'];
}else{
$prof['VHComp'] = 0;
}
}
}
/* (8) Return data */
return $fetched;
}
/* (8) Deletes a professor
*
* @return deleted<bool> Whether the professor have been deleeted successfully
*
---------------------------------------------------------*/
public function delete(int $id) : bool{
/** @var meta $meta_repo */
$meta_repo = Repo::getRepo('meta');
/* (1) Try to fetch professor data
---------------------------------------------------------*/
/* (1) Try to fetch */
$prof = $this->get($id);
/* (2) Error: if not found */
if( count($prof) === 0 )
return false;
/* (3) Take first matching professor */
$prof = $prof[0];
/* (4) Extract @casLogin */
$casLogin = $prof['casLogin'];
/* (2) Remove professor from current database
---------------------------------------------------------*/
/* (1) Prepare statement */
$st = $this->pdo->prepare("DELETE FROM `Professeur` WHERE `idProfesseur` = :id");
/* (2) Manage statement error */
if( is_bool($st) )
return false;
/* (3) Return the execution status */
$success = $st->execute([ ':id' => $id ]);
/* (4) Error: on execution error */
if( !$success )
return false;
/* (5) Success if no @casLogin to update in meta */
if( !$meta_repo->prof_exists($casLogin) )
return TRUE;
/* (3) Synchronize meta database
---------------------------------------------------------*/
/* Proccess only if professor exists */
if( $meta_repo->prof_exists($casLogin) ){
/* (1) If link exists -> remove it */
if( $meta_repo->link_exists($casLogin, $_SESSION['CurrentDepartmentId']) ){
/* (2) Try to unlink-> ignore error */
$meta_repo->unlink($casLogin, $_SESSION['CurrentDepartmentId']);
}
/* (3) If has no more department -> remove professor */
if( count( $meta_repo->get_prof_departments($casLogin) ) == 0 ){
/* (4) Try to remove professor -> dispatch error */
if( !$meta_repo->delete_prof($casLogin) )
return FALSE;
}
}
return TRUE;
}
}