mti
/
ptut-vhost
1
0
Fork 0
Code Issues Pull Requests Releases Wiki Activity
ptut-vhost/build/database/repo/ue.php

302 lines
10 KiB
PHP
Raw Permalink Blame History

<?php
/**
* Created by PhpStorm.
* User: lucas
* Date: 20/02/18
* Time: 20:30
*/
namespace database\repo;
use database\core\Repo_i;
class ue extends Repo_i {
/* (1) Create a new UE
*
* @code<String> The code of the UE
* @label<String> The UE label (name)
* @required<bool> If the UE is required
* @volumeCours<float> The UE required volume of COURSES
* @volumeTD<float> The UE required volume of TD
* @volumeTP<float> The UE required volume of TP
* @disabled<bool> [OPT] If it is disabled
* @defaultFormation<int> [OPT] If there is a foreign key for a default formation (if only one formation)
*
* @return created_code<String> Code of the created UE (NULL on error)
*
---------------------------------------------------------*/
public function create(string $code, string $label, bool $required, float $volumeCours, float $volumeTD, float $volumeTP, bool $disabled = false, ?int $defaultFormation = null) : ?string {
/* (1) Prepare request */
$st = $this->pdo->prepare("INSERT INTO UE(`code`, `label`, `required`, `volumeCours`, `volumeTD`, `volumeTP`, `disabled`, `Formation_idFormation`)
VALUE(:code, :label, :required, :volCours, :volTD, :volTP, :disabled, :idFormation) ");
/* (2) Manage statement error */
if( $st === false )
return null;
/* (3) Bind params and execute request */
$success = $st->execute([
':code' => $code,
':label' => $label,
':required' => $required ? 1 : 0,
':volCours' => $volumeCours,
':volTD' => $volumeTD,
':volTP' => $volumeTP,
':disabled' => $disabled ? 1 : 0,
':idFormation' => $defaultFormation
]);
/* (4) Manage execution error */
if( !$success )
return null;
/* (5) Return insert id */
return $code;
}
/* (2) Updates an UE data
*
* @code<String> The UE code
* @new_code<String> [OPT] The new UE code
* @label<String> [OPT] The UE's new label
* @required<bool> [OPT] Whether the UE is required
* @volumeCours<float> [OPT] The UE's new volume of COURSES
* @volumeTD<float> [OPT] The UE's new volume of TD
* @volumeTP<float> [OPT] The UE's new volume of TP
* @disabled<bool> [OPT] Whether the UE is disabled
* @defaultFormation<int> [OPT] The default formation foreign key (-1 to unset)
*
* @return updated<bool> Whether the update have been successful
*
---------------------------------------------------------*/
public function update(string $code, ?String $new_code, ?String $label, ?bool $required, ?float $volumeCours, ?float $volumeTD, ?float $volumeTP, ?bool $disabled, ?int $defaultFormation) : bool{
/* (1) Build request */
$build_rq = [];
$bind_param = [ ':code' => $code ];
if( !is_null($new_code) ){ $build_rq[] = '`code` = :new_code'; $bind_param[':new_code'] = $new_code; }
if( !is_null($label) ){ $build_rq[] = '`label` = :label'; $bind_param[':label'] = $label; }
if( !is_null($required) ){ $build_rq[] = '`required` = :required'; $bind_param[':required'] = $required?1:0; }
if( !is_null($volumeCours) ){ $build_rq[] = '`volumeCours` = :volumeCours'; $bind_param[':volumeCours'] = $volumeCours; }
if( !is_null($volumeTD) ){ $build_rq[] = '`volumeTD` = :volumeTD'; $bind_param[':volumeTD'] = $volumeTD; }
if( !is_null($volumeTP) ){ $build_rq[] = '`volumeTP` = :volumeTP'; $bind_param[':volumeTP'] = $volumeTP; }
if( !is_null($disabled) ){ $build_rq[] = '`disabled` = :disabled'; $bind_param[':disabled'] = $disabled?1:0; }
// not null @defaultFormation -> set it
if( !is_null($defaultFormation) ){
// if @defaultFormation is (-1) -> unset
if( $defaultFormation < 0 ){ $build_rq[] = '`Formation_idFormation` = NULL'; }
// else -> set to new value
else{ $build_rq[] = '`Formation_idFormation` = :defaultFormation'; $bind_param[':defaultFormation'] = $defaultFormation; }
}
/* (2) ERROR if no updated field */
if( count($build_rq) <= 0 )
return FALSE;
/* (3) Build request */
$sql_rq = "UPDATE `UE` SET ".implode(', ', $build_rq)." WHERE `code` = :code";
/* (4) Prepare statement */
$st = $this->pdo->prepare($sql_rq);
/* (5) Return execution success */
return $st->execute($bind_param);
}
/* (3) Deletes an UE
*
* @code<String> The UE code
*
* @return deleted<bool> Whether the UE have been deleeted successfully
*
---------------------------------------------------------*/
public function delete(string $code) : bool {
/* (1) Prepare statement */
$st = $this->pdo->prepare('DELETE FROM UE WHERE code = :code');
/* (2) Manage error */
if( $st === false )
return false;
/* (3) Bind params and execute request */
return $st->execute([ ':code' => $code ]);
}
/* (4) Disables an UE
*
* @code<String> The UE code
*
* @return disabled<bool> Whether the UE have been disabled successfully
*
---------------------------------------------------------*/
public function disable(string $code) : bool {
return $this->update($code, null, null, null, null, null, true, null);
}
/* (5) Enables an UE
*
* @code<String> The UE code
*
* @return enabled<bool> Whether the UE have been enabled successfully
*
---------------------------------------------------------*/
public function enable(string $code) : bool {
return $this->update($code, null, null, null, null, null, false, null);
}
/* (6) Gets a UE by its code || getAll
*
* @code<String> [OPT] The UE code, if not set, getAll()
*
* @return ues<array> The UEs matching code (NULL on error)
*
---------------------------------------------------------*/
public function get(?String $code=null) : array{
/* (1) Manage if no id given */
$cond = is_null($code) ? '' : 'HAVING `ue`.`code` = :code';
$parm = is_null($code) ? [] : [':code' => $code];
/* (2) Prepare Statement */
$st = $this->pdo->prepare("
SELECT
ue.code,
ue.label,
ue.disabled,
ue.required,
ue.volumeCours,
ue.volumeTD,
ue.volumeTP,
IFNULL(ue.Formation_idFormation, -1) idForm,
fdef.labelFormation labelForm,
IFNULL(formlist.formations, '[]') formations,
IFNULL(formlist.nbrCours,0) nbrCours,
IFNULL(formlist.nbrTD,0) nbrTD,
IFNULL(formlist.nbrTP,0) nbrTP,
IFNULL(formlist.modCours,0) modCours,
IFNULL(formlist.modTD,0) modTD,
IFNULL(formlist.modTP,0) modTP,
IFNULL(formlist.nbrProfCours,0) nbrProfCours,
IFNULL(formlist.nbrProfTD,0) nbrProfTD,
IFNULL(formlist.nbrProfTP,0) nbrProfTP
FROM UE ue
LEFT JOIN Formation fdef ON ue.Formation_idFormation = fdef.idFormation
LEFT JOIN (
SELECT ue2.code code, CONCAT('[',GROUP_CONCAT(DISTINCT Formation.idFormation), ']') formations,
count(DISTINCT C.idCours) nbrCours, count(DISTINCT T.idTD) nbrTD, count(DISTINCT T2.idTP) nbrTP,
MOD(volC.vol,ue2.volumeCours) modCours,
MOD(volTD.vol,ue2.volumeTD) modTD,
MOD(volTP.vol,ue2.volumeTP) modTP,
count(DISTINCT C.Professeur_idProfesseur,C.idCours) nbrProfCours,
count(DISTINCT T.Professeur_idProfesseur,T.idTD) nbrProfTD,
count(DISTINCT T2.Professeur_idProfesseur,T2.idTP) nbrProfTP
FROM UE ue2
LEFT JOIN (SELECT sum(CO.volume) vol, CO.UE_code FROM Cours CO GROUP BY CO.UE_code) volC ON ue2.code = volC.UE_code
LEFT JOIN (SELECT sum(TD2.volume) vol, TD2.UE_code FROM TD TD2 GROUP BY TD2.UE_code) volTD ON ue2.code = volTD.UE_code
LEFT JOIN (SELECT sum(TP2.volume) vol, TP2.UE_code FROM TP TP2 GROUP BY TP2.UE_code) volTP ON ue2.code = volTP.UE_code
LEFT JOIN Cours C ON ue2.code = C.UE_code
LEFT JOIN TD T ON ue2.code = T.UE_code
LEFT JOIN TP T2 ON ue2.code = T2.UE_code
LEFT JOIN GroupeCours C2 ON C.idCours = C2.Cours_idCours
LEFT JOIN GroupeTD TD2 ON T.idTD = TD2.TD_idTD
LEFT JOIN GroupeTP TP2 ON T2.idTP = TP2.TP_idTP
JOIN Formation ON C2.Formation_idFormation = Formation.idFormation
OR TD2.Formation_idFormation = Formation.idFormation
OR TP2.Formation_idFormation = Formation.idFormation
GROUP BY `ue2`.`code`
) formlist ON formlist.code = ue.code
GROUP BY `ue`.`code`
$cond
ORDER BY `ue`.`label` 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;
}
/* (7) Gets all professors who teaches a UE by code
*
* @code<String> The UE code
*
* @return professors<array> The professors' UID matching the @code of the UE
*
---------------------------------------------------------*/
public function getProfessors(String $code) : array{
/* (1) Prepare statement */
$st = $this->pdo->prepare("SELECT p.idProfesseur
FROM Professeur p, UE u
WHERE (
p.idProfesseur IN ( SELECT p_cr.idProfesseur FROM Professeur p_cr, Cours c WHERE c.Professeur_idProfesseur = p_cr.idProfesseur AND c.UE_code = u.code )
OR p.idProfesseur IN ( SELECT p_td.idProfesseur FROM Professeur p_td, TD t WHERE t.Professeur_idProfesseur = p_td.idProfesseur AND t.UE_code = u.code )
OR p.idProfesseur IN ( SELECT p_tp.idProfesseur FROM Professeur p_tp, TP t WHERE t.Professeur_idProfesseur = p_tp.idProfesseur AND t.UE_code = u.code )
)
AND u.code = :ue_code;");
/* (2) Bind params and execute statement */
if( is_bool($st) ) return [];
$success = $st->execute([ ':ue_code' => $code ]);
/* (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;
}
public function exportUE() : array{
$st = $this->pdo->prepare("SELECT * FROM UE
LEFT JOIN Formation
ON UE.Formation_idFormation = Formation.idFormation
ORDER BY Formation_idFormation IS NULL DESC, Formation_idFormation ASC");
$st->execute();
return $st->fetchAll();
}
}
Reference in New Issue View Git Blame Copy Permalink