ptut-vhost/build/database/repo/tp.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();


	}

}