ptut-vhost/build/database/repo/tp.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 tp extends Repo_i {
/* (1) Create a new TP
*
* @code<String> The code of the UE containing the TP
* @idProf<String> [OPT] The ID of the prof who teaches the TP
* @volume<float> The number of hours (float)
* @formations<array> The list of formations for that TP (list of int)
* @return created_code<String> Code of the created UE (NULL on error)
*
* @return created_id<int> UID of the created TP (NULL on error)
*
---------------------------------------------------------*/
public function create(string $code, ?int $idProf, float $volume, array $formations) : ?int{
/* (1) Prepare statement */
$st = $this->pdo->prepare('INSERT INTO TP(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 TP
$this->linkFormation($form_id, $created_id);
}
/* (7) Return @created_id */
return $created_id;
}
/* (2) Unlink a formation from a TP
*
* @idFormation<int> Id of the formation
* @idTP<int> Id of the TP
*
* @return unlinked<bool> Whether it has been unlinked
*
---------------------------------------------------------*/
public function unlinkFormation(int $idFormation, int $idTP) : bool{
/* (1) Prepare statement */
$st = $this->pdo->prepare('DELETE FROM GroupeTP WHERE TP_idTP = :tp AND Formation_idFormation = :form');
/* (2) Manage statement error */
if( is_bool($st) )
return false;
/* (3) EXecute and dispatch status */
return $st->execute([
':tp' => $idTP,
':form' => $idFormation
]);
}
/* (3) Link a formation to a TP
*
* @idFormation<int> Id of the formation
* @idTP<int> Id of the TP
*
* @return linked<bool> Whether it has been linked
*
---------------------------------------------------------*/
public function linkFormation(int $idFormation, int $idTP) : bool{
/* (1) Try to remove first if it already exists */
$this->unlinkFormation($idFormation, $idTP);
/* (2) Prepare statement */
$st = $this->pdo->prepare('INSERT INTO GroupeTP(TP_idTP,Formation_idFormation)
VALUE(:tp, :form)');
/* (3) Manage statement error */
if( is_bool($st) )
return false;
/* (4) EXecute and dispatch status */
return $st->execute([
':tp' => $idTP,
':form' => $idFormation
]);
}
/* (4.1) Updates an existing TP
*
* @idTP<int> Id of the TP
* @idProf<int> [OPT] Id of the prof (NULL to set to NULL)
*
* @return updated<bool> Whether it has been updated
*
---------------------------------------------------------*/
public function updateProf(int $idTP, ?int $idProf) : bool {
/* (1) Prepare statement */
$st = $this->pdo->prepare('UPDATE TP
SET Professeur_idProfesseur = :idProf
WHERE idTP = :idTP');
/* (2) Manage statement error */
if( is_bool($st) )
return false;
/* (3) Execute and dispatch status */
return $st->execute([ ':idTP' => $idTP, ':idProf' => $idProf ]);
}
/* (4.2) Updates an existing TP
*
* @idTP<int> Id of the TP
* @volume<float> [OPT] The new number of hours
*
* @return updated<bool> Whether it has been updated
*
---------------------------------------------------------*/
public function updateVolume(int $idTP, float $volume) : bool {
/* (1) Prepare statement */
$st = $this->pdo->prepare('UPDATE TP
SET volume = :volume
WHERE idTP = :idTP');
/* (2) Manage statement error */
if( is_bool($st) )
return false;
/* (3) Execute and dispatch status */
return $st->execute([ ':idTP' => $idTP, ':volume' => $volume ]);
}
/* (5) Deletes an existing tp
*
* @idTP<int> Id of the TP
*
* @return deleted<bool> Whether it has been deleted
*
---------------------------------------------------------*/
public function delete(int $id) :bool {
/* (1) Prepare statement */
$st = $this->pdo->prepare("DELETE FROM TP WHERE idTP = :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.idTP,
p.idProfesseur idProf,
p.firstName,
p.lastName,
IFNULL(lform.flist, '[]') formations,
c.volume
FROM TP c
LEFT JOIN ( SELECT gc.TP_idTP idTP, CONCAT('[', GROUP_CONCAT(DISTINCT gc.Formation_idFormation),']') flist
FROM GroupeTP gc
GROUP BY gc.TP_idTP
ORDER BY gc.Formation_idFormation DESC
) lform ON c.idTP = lform.idTP
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
tp.UE_code code,
ue.label label,
tp.idTP,
IFNULL(lform.flist, '[]') formations,
tp.volume
FROM TP tp
LEFT JOIN ( SELECT gtp.TP_idTP idTP, CONCAT('[', GROUP_CONCAT(DISTINCT gtp.Formation_idFormation),']') flist
FROM GroupeTP gtp
GROUP BY gtp.TP_idTP
ORDER BY gtp.Formation_idFormation DESC
) lform ON tp.idTP = lform.idTP
JOIN UE ue ON ue.code = tp.UE_code
WHERE tp.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();
}
}