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

133 lines
2.9 KiB
PHP
Raw Permalink Normal View History

<?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 {
public function create(string $codeUE, ?int $idProf, float $volume, array $formations) : int{
//create the group
$st = $this->pdo->prepare("INSERT INTO TP(UE_code, Professeur_idProfesseur, volume)
VALUE(:UE, :idProf, :vol)");
$st->execute([
"UE" => $codeUE,
"idProf" => $idProf,
"vol" => $volume
]);
$idTP = $this->pdo->lastInsertId();
//if there is formations, link them with the group
if(count($formations) > 0){
$linkSt = $this->pdo->prepare("INSERT INTO GroupeTP(Formation_idFormation, TP_idTP)
VALUE (:form, :TP)");
foreach ($formations as $form){
$linkSt->execute([
"form" => $form,
"TP" => $idTP
]);
}
}
return $idTP;
}
public function unlinkFormation(int $idFormation, int $idTP) : bool{
$st = $this->pdo->prepare("DELETE FROM GroupeTP WHERE TP_idTP = :TP AND Formation_idFormation = :form");
return $st->execute([
"TP" => $idTP,
"form" => $idFormation
]);
}
public function linkFormation(int $idFormation, int $idTP) : bool{
$st = $this->pdo->prepare("INSERT INTO GroupeTP(TP_idTP,Formation_idFormation)
VALUE(:TP, :form)");
return $st->execute([
"TP" => $idTP,
"form" => $idFormation
]);
}
public function updateProf(?int $prof) : bool {
$st = $this->pdo->prepare("UPDATE TP SET Professeur_idProfesseur = :prof");
return $st->execute([
"prof" => $prof
]);
}
public function updateVolume(float $volume) : bool {
$st = $this->pdo->prepare("UPDATE TP SET volume = :vol");
return $st->execute([
"vol" => $volume
]);
}
public function delete(int $id) :bool {
$st = $this->pdo->prepare("DELETE FROM TP WHERE idTP = :id");
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
tp.UE_code code,
tp.idTP,
p.idProfesseur idProf,
p.firstName,
p.lastName,
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
LEFT JOIN Professeur p ON p.idProfesseur = tp.Professeur_idProfesseur
WHERE tp.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();
}
}