The code of the UE * @label The UE label (name) * @required If the UE is required * @volumeCours The UE required volume of COURSES * @volumeTD The UE required volume of TD * @volumeTP The UE required volume of TP * @disabled [OPT] If it is disabled * @defaultFormation [OPT] If there is a foreign key for a default formation (if only one formation) * * @return created_code 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 The UE code * @label [OPT] The UE's new label * @required [OPT] Whether the UE is required * @volumeCours [OPT] The UE's new volume of COURSES * @volumeTD [OPT] The UE's new volume of TD * @volumeTP [OPT] The UE's new volume of TP * @disabled [OPT] Whether the UE is disabled * @defaultFormation [OPT] The default formation foreign key (-1 to unset) * * @return updated Whether the update have been successful * ---------------------------------------------------------*/ public function update(string $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($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 The UE code * * @return deleted 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 The UE code * * @return disabled 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 The UE code * * @return enabled 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 [OPT] The UE code, if not set, getAll() * * @return ues The UEs matching code (NULL on error) * ---------------------------------------------------------*/ public function get(?String $code=null) : array{ /* (1) Manage if no id given */ $cond = is_null($code) ? '' : 'AND `ue.`code` = :code'; $parm = is_null($code) ? [] : [':code' => $code]; /* (2) Prepare Statement */ $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); $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 FROM UE ue LEFT JOIN Formation fdef ON ue.Formation_idFormation = fdef.idFormation LEFT JOIN ( SELECT ue2.code code, CONCAT('[',GROUP_CONCAT(fform.idFormation), ']') formations FROM UE ue2, Formation fform WHERE ( fform.idFormation IN ( SELECT DISTINCT Formation_idFormation as idForm FROM GroupeCours WHERE Cours_idCours IN ( SELECT idCours FROM Cours WHERE UE_code = ue2.code GROUP BY idForm ) ) OR fform.idFormation IN ( SELECT DISTINCT Formation_idFormation as idForm FROM GroupeTD WHERE TD_idTD IN ( SELECT idTD FROM TD WHERE UE_code = ue2.code GROUP BY idForm ) ) OR fform.idFormation IN ( SELECT DISTINCT Formation_idFormation as idForm FROM GroupeTP WHERE TP_idTP IN ( SELECT idTP FROM TP WHERE UE_code = ue2.code GROUP BY idForm ) ) ) GROUP BY `ue2`.`code` ) formlist ON formlist.code = ue.code $cond GROUP BY `ue`.`code` 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 The UE code * * @return professors 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; } }