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

<?php
/**
 * Created by PhpStorm.
 * User: lucas
 * Date: 05/03/18
 * Time: 19:39
 */

namespace database\repo;


use database\core\Repo;
use database\core\Repo_i;

class department extends Repo_i
{

	public function getErrors() : array {

		$results = [];

		/* (1) Find Groups without formation bound */
		$GroupWithoutFormation = $this->pdo->prepare("
			(SELECT 'NO_FORMATION_ASSIGNED' errorType, 'Cours' entityType, idCours id FROM Cours WHERE idCours NOT IN (SELECT Cours_idCours FROM GroupeCours))
		UNION
			(SELECT 'NO_FORMATION_ASSIGNED' errorType, 'TD' entityType, idTD id FROM TD WHERE idTD NOT IN (SELECT TD_idTD FROM GroupeTD))
		UNION
			(SELECT 'NO_FORMATION_ASSIGNED' errorType, 'TP' entityType, idTP id FROM TP WHERE idTP NOT IN (SELECT TP_idTP FROM GroupeTP))");

		$GroupWithoutFormation->execute([]);

		$results = array_merge($results,$GroupWithoutFormation->fetchAll());

		/* (2) Find Groups without a professor bound */

		$GroupWithoutProfessor = $this->pdo->prepare("
		(SELECT 'NO_PROFESSOR_ASSIGNED' errorType, 'Cours' entityType, idCours id FROM Cours WHERE Professeur_idProfesseur IS NULL)
			UNION
		(SELECT 'NO_PROFESSOR_ASSIGNED' errorType, 'TD' entityType, idTD id FROM TD WHERE Professeur_idProfesseur IS NULL)
			UNION
		(SELECT 'NO_PROFESSOR_ASSIGNED' errorType, 'TP' entityType, idTP id FROM TP WHERE Professeur_idProfesseur IS NULL)");

		$GroupWithoutProfessor->execute([]);

		$results = array_merge($results,$GroupWithoutProfessor->fetchAll());

		/* (3) Find Groups a null VH */

		$GroupWithNullVH = $this->pdo->prepare("
		(SELECT 'NULL_VH' errorType, 'Cours' entityType, idCours id FROM Cours WHERE volume = 0)
			UNION
		(SELECT 'NULL_VH' errorType, 'TD' entityType, idTD id FROM TD WHERE volume = 0)
			UNION
		(SELECT 'NULL_VH' errorType, 'TP' entityType, idTP id FROM TP WHERE volume = 0)");

		$GroupWithNullVH->execute([]);

		$results = array_merge($results,$GroupWithNullVH->fetchAll());

		/* (4) Find UE with an incorrect VH in the groups compared to what's supposed to be */

		$UEWithIncorrectGroupVH = $this->pdo->prepare("
		(SELECT 'UNEQUIVALENT_VH' errorType, 'Cours' entityType, U.code
		FROM UE U
		WHERE
  			0 != (SELECT MOD(SUM(volume),U.volumeCours) modCours FROM Cours WHERE UE_code = U.code GROUP BY Cours.UE_code)
  		)

		UNION

		(SELECT 'UNEQUIVALENT_VH' errorType, 'TD' entityType, U.code
 		FROM UE U
 		WHERE
   			0 != (SELECT MOD(SUM(volume),U.volumeTD) modCours FROM TD WHERE UE_code = U.code GROUP BY TD.UE_code)
   		)

		UNION

		(SELECT 'UNEQUIVALENT_VH' errorType, 'TP' entityType, U.code
 		FROM UE U
 		WHERE
   			0 != (SELECT MOD(SUM(volume),U.volumeTP) modCours FROM TP WHERE UE_code = U.code GROUP BY TP.UE_code)
   		)");

		$UEWithIncorrectGroupVH->execute([]);

		$results = array_merge($results,$UEWithIncorrectGroupVH->fetchAll());

		return $results;
	}

	public function getStats() : array{

		/* (1) Potentiel horaire du département */
		$Potentiel = $this->pdo->prepare("SELECT SUM(hoursToDo) potentiel
													FROM Professeur
													WHERE Categorie_idCategorie IN (1,2,3)
													GROUP BY Categorie_idCategorie IN (1,2,3);");

		$Potentiel->execute([]);

		$results = $Potentiel->fetch();

		/* (2) Sous-service (professeurs ayant moins d'heure prévu que d'heure du) */
		/** @var professor $ProfRepo */
		$ProfRepo = Repo::getRepo("professor");

		$profs = $ProfRepo->getWithVH(null);

		$results["sous_service"] = 0;
		$results["heures_comp"] = 0;
		$results["heures_vacataire"] = 0;

		foreach ($profs as $prof){
			if(in_array($prof["idCat"],[1,2,3,4]) and $prof["equiTD"] < $prof["hoursToDo"]){
				$results["sous_service"] += $prof["hoursToDo"] - $prof["equiTD"];

			/* (3) Heures effectués par des vacataires */
			}else if(in_array($prof["idCat"],[5,6])){
				$results["heures_vacataire"] += $prof["equiTD"];
			}

			/* (4) Heures complementaires */

			if(is_numeric($prof["VHComp"])){
				$results["heures_comp"] += $prof["VHComp"];
			}

		}

		/* (5) Heures effectuées pour des départements exterieurs */
		//je crois que j'ai créé un montre - Lucas - 2018
		$HDepExt = $this->pdo->prepare("
		SELECT U.disabled disabled,
		  count(U.code) nbrUe,
		  SUM((IFNULL(T1.VolumeCours,0)*IFNULL(tauxInfoCours,0) + IFNULL(T2.VolumeTD,0)*IFNULL(tauxInfoTD,0) + IFNULL(T3.VolumeTP,0)*IFNULL(tauxInfoTP,0))) totalInfo,
		  SUM((IFNULL(T1.VolumeCours,0)*(1-IFNULL(tauxInfoCours,0)) + IFNULL(T2.VolumeTD,0)*(1-IFNULL(tauxInfoTD,0)) + IFNULL(T3.VolumeTP,0)*(1-IFNULL(tauxInfoTP,0)))) totalExterieur
		FROM UE U
		  LEFT JOIN (SELECT SUM(F.isInternal)/count(F.idFormation) tauxInfoCours,SUM(C.volume)*1.5 VolumeCours, C.UE_code code_UE
					 FROM Cours C
					   JOIN GroupeCours C2 ON C.idCours = C2.Cours_idCours
					   JOIN Formation F ON C2.Formation_idFormation = F.idFormation
					 GROUP BY C.UE_code) T1 ON U.code = T1.code_UE
		  LEFT JOIN (SELECT SUM(F.isInternal)/count(F.idFormation) tauxInfoTD,SUM(T1.volume) VolumeTD, T1.UE_code code_UE
					 FROM TD T1
					   JOIN GroupeTD GT ON T1.idTD = GT.TD_idTD
					   JOIN Formation F ON GT.Formation_idFormation = F.idFormation
					 GROUP BY T1.UE_code) T2 ON U.code = T2.code_UE
		  LEFT JOIN (SELECT SUM(F.isInternal)/count(F.idFormation) tauxInfoTP,SUM(T1.volume) VolumeTP, T1.UE_code code_UE
					 FROM TP T1
					   JOIN GroupeTP TP2 ON T1.idTP = TP2.TP_idTP
					   JOIN Formation F ON TP2.Formation_idFormation = F.idFormation
					 GROUP BY T1.UE_code) T3 ON U.code = T3.code_UE
		GROUP BY U.disabled");

		$HDepExt->execute([]);

		$HDepExtData = $HDepExt->fetchAll();

		$results = array_merge($results,[
			"heures_exterieur" => $HDepExtData[0]["totalExterieur"],
			"heures_ue_desactive" => $HDepExtData[1]["totalExterieur"] + $HDepExtData[1]["totalInfo"],
			"nbr_ue_desactive" => $HDepExtData[1]["nbrUe"]
		]);

		return $results;

	}

	public function restore(string $dbName, string $SQL) : bool{

		//get the list of command to execute
		$this->pdo->exec("DROP DATABASE $dbName;");
		$this->pdo->exec("CREATE DATABASE $dbName;");
		$this->pdo->exec("USE $dbName;");

		return $this->pdo->exec($SQL);
	}

	private static function generatePreviewDBName(string $bdName, string $backupName) : string{
		return "preview_{$bdName}_".sha1($bdName.$backupName);
	}

	public function previewExists(string $dbName, string $backupname) : ?string {
		$previewDBName = static::generatePreviewDBName($dbName,$backupname);

		$st = $this->pdo->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName");
		$st->execute(["dbName" => $previewDBName]);

		if($st->fetch() === false) return null;
		else return $previewDBName;
	}

	public function createPreview(string $dbName, string $backupname) : string {
		$previewDBName = static::generatePreviewDBName($dbName,$backupname);

		$this->pdo->exec("CREATE OR REPLACE DATABASE $previewDBName");

		return $previewDBName;
	}

}