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

<?php
/**
 * Created by PhpStorm.
 * User: lucas
 * Date: 22/02/18
 * Time: 13:27
 */

namespace database\repo;


use database\core\Repo_i;

class cours extends Repo_i {

	/* (1) Create a new Cours
	*
	* @code<String>						The code of the UE containing the Cours
	* @idProf<String>					[OPT] The ID of the prof who teaches the Cours
	* @volume<float>						The number of hours (float)
	* @formations<array>				The list of formations for that Cours (list of int)
	* @return created_code<String>		Code of the created UE (NULL on error)
	*
	* @return created_id<int>			UID of the created Cours (NULL on error)
	*
	---------------------------------------------------------*/
	public function create(string $code, ?int $idProf, float $volume, array $formations) : ?int{

		/* (1) Prepare statement */
		$st = $this->pdo->prepare('INSERT INTO Cours(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 Cours
			$this->linkFormation($form_id, $created_id);
		}

		/* (7) Return @created_id */
		return $created_id;

	}


	/* (2) Unlink a formation from a Cours
	*
	* @idFormation<int>					Id of the formation
	* @idCours<int>						Id of the Cours
	*
	* @return unlinked<bool>			Whether it has been unlinked
	*
	---------------------------------------------------------*/
	public function unlinkFormation(int $idFormation, int $idCours) : bool{

		/* (1) Prepare statement */
		$st = $this->pdo->prepare('DELETE FROM GroupeCours WHERE Cours_idCours = :cours AND Formation_idFormation = :form');

		/* (2) Manage statement error */
		if( is_bool($st) )
			return false;

		/* (3) EXecute and dispatch status */
		return $st->execute([
			':cours' => $idCours,
			':form'  => $idFormation
		]);

	}


	/* (3) Link a formation to a Cours
	*
	* @idFormation<int>					Id of the formation
	* @idCours<int>						Id of the Cours
	*
	* @return linked<bool>			Whether it has been linked
	*
	---------------------------------------------------------*/
	public function linkFormation(int $idFormation, int $idCours) : bool{

		/* (1) Try to remove first if it already exists */
		$this->unlinkFormation($idFormation, $idCours);

		/* (2) Prepare statement */
		$st = $this->pdo->prepare('INSERT INTO GroupeCours(Cours_idCours,Formation_idFormation)
									VALUE(:cours, :form)');

		/* (3) Manage statement error */
		if( is_bool($st) )
			return false;

		/* (4) EXecute and dispatch status */
		return $st->execute([
			':cours' => $idCours,
			':form'  => $idFormation
		]);

	}





	/* (4.1) Updates an existing Cours
	*
	* @idCours<int>					Id of the Cours
	* @idProf<int>					[OPT] Id of the prof (NULL to set to NULL)
	*
	* @return updated<bool>			Whether it has been updated
	*
	---------------------------------------------------------*/
	public function updateProf(int $idCours, ?int $idProf) : bool {

		/* (1) Prepare statement */
		$st = $this->pdo->prepare('UPDATE Cours
			SET   Professeur_idProfesseur = :idProf
			WHERE idCours                 = :idCours');

		/* (2) Manage statement error */
		if( is_bool($st) )
			return false;

		/* (3) Execute and dispatch status */
		return $st->execute([ ':idCours' => $idCours, ':idProf' => $idProf ]);

	}

	/* (4.2) Updates an existing Cours
	*
	* @idCours<int>					Id of the Cours
	* @volume<float>				[OPT] The new number of hours
	*
	* @return updated<bool>			Whether it has been updated
	*
	---------------------------------------------------------*/
	public function updateVolume(int $idCours, float $volume) : bool {

		/* (1) Prepare statement */
		$st = $this->pdo->prepare('UPDATE Cours
			SET   volume  = :volume
			WHERE idCours = :idCours');

		/* (2) Manage statement error */
		if( is_bool($st) )
			return false;

		/* (3) Execute and dispatch status */
		return $st->execute([ ':idCours' => $idCours, ':volume' => $volume ]);

	}



	/* (5) Deletes an existing cours
	*
	* @idCours<int>					Id of the Cours
	*
	* @return deleted<bool>			Whether it has been deleted
	*
	---------------------------------------------------------*/
	public function delete(int $id) :bool {

		/* (1) Prepare statement */
		$st = $this->pdo->prepare("DELETE FROM Cours WHERE idCours = :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.idCours,
				p.idProfesseur idProf,
				p.firstName,
				p.lastName,
				IFNULL(lform.flist, '[]') formations,
				c.volume
			FROM Cours c
			LEFT JOIN ( SELECT gc.Cours_idCours idCours, CONCAT('[', GROUP_CONCAT(DISTINCT gc.Formation_idFormation),']') flist
				FROM GroupeCours gc
				GROUP BY gc.Cours_idCours
				ORDER BY gc.Formation_idFormation DESC
			) lform ON c.idCours = lform.idCours
			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();


	}

}