294 lines
6.9 KiB
PHP
294 lines
6.9 KiB
PHP
<?php
|
|
/**
|
|
* Created by PhpStorm.
|
|
* User: lucas
|
|
* Date: 22/02/18
|
|
* Time: 13:27
|
|
*/
|
|
|
|
namespace database\repo;
|
|
|
|
|
|
use database\core\Repo_i;
|
|
|
|
class td extends Repo_i {
|
|
|
|
/* (1) Create a new TD
|
|
*
|
|
* @code<String> The code of the UE containing the TD
|
|
* @idProf<String> [OPT] The ID of the prof who teaches the TD
|
|
* @volume<float> The number of hours (float)
|
|
* @formations<array> The list of formations for that TD (list of int)
|
|
* @return created_code<String> Code of the created UE (NULL on error)
|
|
*
|
|
* @return created_id<int> UID of the created TD (NULL on error)
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function create(string $code, ?int $idProf, float $volume, array $formations) : ?int{
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare('INSERT INTO TD(UE_code, Professeur_idProfesseur, volume)
|
|
VALUE(:UE, :idProf, :vol)');
|
|
|
|
/* (2) Manage statement error */
|
|
if( is_bool($st) )
|
|
return NULL;
|
|
|
|
/* (3) Try to execute request */
|
|
$success = $st->execute([
|
|
':UE' => $code,
|
|
':idProf' => $idProf,
|
|
':vol' => $volume
|
|
]);
|
|
|
|
/* (4) Manage error */
|
|
if( !$success )
|
|
return NULL;
|
|
|
|
/* (5) Store @created_id */
|
|
$created_id = (int) $this->pdo->lastInsertId();
|
|
|
|
|
|
/* (6) We are done if there is no formations */
|
|
if( count($formations) <= 0)
|
|
return $created_id;
|
|
|
|
/* (7) Else -> create each formation */
|
|
foreach($formations as $form_id){
|
|
|
|
// 1. Ignore if wrong format
|
|
if( !is_int($form_id) || $form_id < 0 )
|
|
continue;
|
|
|
|
// 2. Link formation to created TD
|
|
$this->linkFormation($form_id, $created_id);
|
|
}
|
|
|
|
/* (7) Return @created_id */
|
|
return $created_id;
|
|
|
|
}
|
|
|
|
|
|
/* (2) Unlink a formation from a TD
|
|
*
|
|
* @idFormation<int> Id of the formation
|
|
* @idTD<int> Id of the TD
|
|
*
|
|
* @return unlinked<bool> Whether it has been unlinked
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function unlinkFormation(int $idFormation, int $idTD) : bool{
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare('DELETE FROM GroupeTD WHERE TD_idTD = :td AND Formation_idFormation = :form');
|
|
|
|
/* (2) Manage statement error */
|
|
if( is_bool($st) )
|
|
return false;
|
|
|
|
/* (3) EXecute and dispatch status */
|
|
return $st->execute([
|
|
':td' => $idTD,
|
|
':form' => $idFormation
|
|
]);
|
|
|
|
}
|
|
|
|
|
|
/* (3) Link a formation to a TD
|
|
*
|
|
* @idFormation<int> Id of the formation
|
|
* @idTD<int> Id of the TD
|
|
*
|
|
* @return linked<bool> Whether it has been linked
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function linkFormation(int $idFormation, int $idTD) : bool{
|
|
|
|
/* (1) Try to remove first if it already exists */
|
|
$this->unlinkFormation($idFormation, $idTD);
|
|
|
|
/* (2) Prepare statement */
|
|
$st = $this->pdo->prepare('INSERT INTO GroupeTD(TD_idTD,Formation_idFormation)
|
|
VALUE(:td, :form)');
|
|
|
|
/* (3) Manage statement error */
|
|
if( is_bool($st) )
|
|
return false;
|
|
|
|
/* (4) EXecute and dispatch status */
|
|
return $st->execute([
|
|
':td' => $idTD,
|
|
':form' => $idFormation
|
|
]);
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
/* (4.1) Updates an existing TD
|
|
*
|
|
* @idTD<int> Id of the TD
|
|
* @idProf<int> [OPT] Id of the prof (NULL to set to NULL)
|
|
*
|
|
* @return updated<bool> Whether it has been updated
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function updateProf(int $idTD, ?int $idProf) : bool {
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare('UPDATE TD
|
|
SET Professeur_idProfesseur = :idProf
|
|
WHERE idTD = :idTD');
|
|
|
|
/* (2) Manage statement error */
|
|
if( is_bool($st) )
|
|
return false;
|
|
|
|
/* (3) Execute and dispatch status */
|
|
return $st->execute([ ':idTD' => $idTD, ':idProf' => $idProf ]);
|
|
|
|
}
|
|
|
|
/* (4.2) Updates an existing TD
|
|
*
|
|
* @idTD<int> Id of the TD
|
|
* @volume<float> [OPT] The new number of hours
|
|
*
|
|
* @return updated<bool> Whether it has been updated
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function updateVolume(int $idTD, float $volume) : bool {
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare('UPDATE TD
|
|
SET volume = :volume
|
|
WHERE idTD = :idTD');
|
|
|
|
/* (2) Manage statement error */
|
|
if( is_bool($st) )
|
|
return false;
|
|
|
|
/* (3) Execute and dispatch status */
|
|
return $st->execute([ ':idTD' => $idTD, ':volume' => $volume ]);
|
|
|
|
}
|
|
|
|
|
|
|
|
/* (5) Deletes an existing td
|
|
*
|
|
* @idTD<int> Id of the TD
|
|
*
|
|
* @return deleted<bool> Whether it has been deleted
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function delete(int $id) :bool {
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare("DELETE FROM TD WHERE idTD = :id");
|
|
|
|
/* (2) Manage statement error */
|
|
if( is_bool($st) )
|
|
return false;
|
|
|
|
/* (3) Execute and dispatch status */
|
|
return $st->execute([ ':id' => $id ]);
|
|
|
|
}
|
|
|
|
|
|
/* (x) Get groups for a specific UE
|
|
*
|
|
* @code<String> UE code
|
|
*
|
|
* @return groups<array> The list of groups for this UE
|
|
* NULL on error
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function getGroups(String $code) : ?array{
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare("SELECT
|
|
c.UE_code code,
|
|
c.idTD,
|
|
p.idProfesseur idProf,
|
|
p.firstName,
|
|
p.lastName,
|
|
IFNULL(lform.flist, '[]') formations,
|
|
c.volume
|
|
FROM TD c
|
|
LEFT JOIN ( SELECT gc.TD_idTD idTD, CONCAT('[', GROUP_CONCAT(DISTINCT gc.Formation_idFormation),']') flist
|
|
FROM GroupeTD gc
|
|
GROUP BY gc.TD_idTD
|
|
ORDER BY gc.Formation_idFormation DESC
|
|
) lform ON c.idTD = lform.idTD
|
|
LEFT JOIN Professeur p ON p.idProfesseur = c.Professeur_idProfesseur
|
|
WHERE c.UE_code = :code;");
|
|
|
|
/* (2) Check statement */
|
|
if( is_bool($st) )
|
|
return NULL;
|
|
|
|
/* (3) Execute statement */
|
|
$success = $st->execute([':code' => $code]);
|
|
|
|
/* (4) Check error */
|
|
if( !$success )
|
|
return NULL;
|
|
|
|
/* (5) Dispatch fetch result */
|
|
return $st->fetchAll();
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
/* (7) Get groups for a specific Professor
|
|
*
|
|
* @prof_id<int> Professor ID
|
|
*
|
|
* @return groups<array> The list of groups for this Professor
|
|
* NULL on error
|
|
*
|
|
---------------------------------------------------------*/
|
|
public function getForProfessor(int $prof_id) : ?array{
|
|
|
|
/* (1) Prepare statement */
|
|
$st = $this->pdo->prepare("SELECT
|
|
td.UE_code code,
|
|
ue.label label,
|
|
td.idTD,
|
|
IFNULL(lform.flist, '[]') formations,
|
|
td.volume
|
|
FROM TD td
|
|
LEFT JOIN ( SELECT gtd.TD_idTD idTD, CONCAT('[', GROUP_CONCAT(DISTINCT gtd.Formation_idFormation),']') flist
|
|
FROM GroupeTD gtd
|
|
GROUP BY gtd.TD_idTD
|
|
ORDER BY gtd.Formation_idFormation DESC
|
|
) lform ON td.idTD = lform.idTD
|
|
JOIN UE ue ON ue.code = td.UE_code
|
|
WHERE td.Professeur_idProfesseur = :prof_id;");
|
|
|
|
/* (2) Check statement */
|
|
if( is_bool($st) )
|
|
return NULL;
|
|
|
|
/* (3) Execute statement */
|
|
$success = $st->execute([':prof_id' => $prof_id]);
|
|
|
|
/* (4) Check error */
|
|
if( !$success )
|
|
return NULL;
|
|
|
|
/* (5) Dispatch fetch result */
|
|
return $st->fetchAll();
|
|
|
|
|
|
}
|
|
|
|
} |