ptut-vhost/build/api/module/excelController.php

444 lines
16 KiB
PHP

<?php
/**
* Created by PhpStorm.
* User: lucas
* Date: 19/02/18
* Time: 21:31
*/
namespace api\module;
use database\core\Repo;
use database\core\Repo_i;
use database\repo\category;
use database\repo\cours;
use database\repo\professor;
use database\repo\td;
use database\repo\tp;
use database\repo\ue;
use error\core\Err;
use error\core\Error;
use PhpOffice\PhpSpreadsheet\Exception;
class excelController
{
private const startLineUE = 5;
private const startLineProf = 10;
public function post($args){
if(isset($_FILES["file"]["tmp_name"])){
//put everything in a try so we catch all PhpExcel exceptions and return a clean API Response
try{
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($_FILES["file"]["tmp_name"]);
$spreadsheet->setActiveSheetIndex(0);
$UESpreadsheet = $spreadsheet->getActiveSheet();
/*
* Setting Up state variables
*/
//current formation
$formation = "";
//current UE code
$UECode = "";
//current UE
$UE = [];
//array containing all the UEs
$allUE = [];
//array containing all the formations
$allFormations = [];
//can the current UE have a default formation
$isDefaultFormationPossible = false;
/*
* declare the lambda that will add finalized UE to the array
*/
$addEU = function() use (&$UECode,&$allUE,&$UE){
//determine if UE is disabled (if cours+td+tp = 0)
$totalVH = 0;
if(is_array($UE["groups"]) && count($UE["groups"]) > 0){
foreach ($UE["groups"] as $groups){
foreach ($groups as $group){
$totalVH += $group["VH"];
}
}
}
$UE["disabled"] = $totalVH == 0;
if($UECode != ""){
if(isset($allUE[$UECode])){
$counter = 1;
while(isset($allUE[$UECode.$counter])) $counter++;
$allUE[$UECode.$counter] = $UE;
}else{
$allUE[$UECode] = $UE;
}
$UE = [];
}
};
/*
* declaring the lambda tha twill extract the list of formation involved in the group
*/
$getFormations = function(?string $group) use (&$formation,&$allFormations) : array{
$group = trim($group);
if($group == ""){
if(!isset($allFormations[$formation])){
$allFormations[$formation] = [
"name" => $formation,
"internal" => strpos($formation,"Info") !== false ? true : false
];
}
return [$formation];
}
//replace the generic "INFO" keyword by the actual formation
$group = str_replace("INFO",$formation,$group);
//split the string
$groups = explode("+",$group);
//trim the strings
$groups = array_map('trim', $groups);
//delete empty strings
$groups = array_filter($groups);
foreach ($groups as $group){
if(!isset($allFormations[$group])){
$allFormations[$group] = [
"name" => $group,
"internal" => strpos(strtolower($group),"info") !== false ? true : false
];
}
}
return $groups;
};
/*
* declaring the lambda that will compute the part of internal students in the course (used for stats purpose)
*/
$getInternalStudentPart = function(?string $group) use (&$formation, $getFormations) : float{
if(!$group) return 1.0;
$groups = $getFormations($group);
if(!in_array($formation,$groups)){
return 0.0;
}else{
return 1/count($groups);
}
};
$exitWithNullException = function(){
exit(json_encode(["error" => true, "message" => "NullCellException"]));
};
//starting the iteration
foreach($UESpreadsheet->getRowIterator() as $row){
//skip the first rows
if($row->getRowIndex() < self::startLineUE) continue;
$firstCellValue = ($UESpreadsheet->getCellByColumnAndRow(1,$row->getRowIndex()) ?? $exitWithNullException())->getValue();
$secondCellValue = ($UESpreadsheet->getCellByColumnAndRow(2,$row->getRowIndex()) ?? $exitWithNullException())->getValue();
//if the first value is not null and the second is, this means we change formation and rest the UUCode
if($secondCellValue == "" and $firstCellValue != "") {$formation = $firstCellValue; $addEU(); $UECode = ""; continue; }
//if no UE is set yet, find it
if($UECode == "" and $firstCellValue != "") $UECode = $firstCellValue;
//if the line is empty this means we change UE
if($firstCellValue == "" and $secondCellValue == "") {$addEU(); $UECode = ""; continue;}
//now we have a formation, a UE and we are sure the line is not empty, let's fill up this bad boy
//if the required field is not set this means we are at the header of the UE
if(!isset($UE["required"])){
$UE["name"] = ($UESpreadsheet->getCellByColumnAndRow(2,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue();
$UE["required"] = ($UESpreadsheet->getCellByColumnAndRow(3,$row->getRowIndex()) ?? $exitWithNullException())->getValue();
$UE["TotalVH"] = ($UESpreadsheet->getCellByColumnAndRow(4,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue();
$UE["CourseVH"] = ($UESpreadsheet->getCellByColumnAndRow(5,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0.0;
$UE["CourseGroup"] = ($UESpreadsheet->getCellByColumnAndRow(6,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0;
$UE["TdVH"] = ($UESpreadsheet->getCellByColumnAndRow(8,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0.0;
$UE["TdGroup"] = ($UESpreadsheet->getCellByColumnAndRow(9,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0;
$UE["TpVH"] = ($UESpreadsheet->getCellByColumnAndRow(11,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0.0;
$UE["TpGroup"] = ($UESpreadsheet->getCellByColumnAndRow(12,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0;
$UE["defaultFormation"] = null;
$isDefaultFormationPossible = true;
continue;
}
//we have a new group! if the array is not set, initialize it
if(!isset($UE["groups"])) $UE["groups"] = [
"Course" => [],
"TD" => [],
"TP" => []
];
//compute Course
if (is_numeric(($UESpreadsheet->getCellByColumnAndRow(5,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue())){
$group = [
"VH" => ($UESpreadsheet->getCellByColumnAndRow(5,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue(),
"internalStudentPart" => $getInternalStudentPart(($UESpreadsheet->getCellByColumnAndRow(6,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"formations" => $getFormations(($UESpreadsheet->getCellByColumnAndRow(6,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"professor" => ($UESpreadsheet->getCellByColumnAndRow(7,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: null
];
if(count($group["formations"]) == 1 and $isDefaultFormationPossible and ($UE["defaultFormation"] == null or $UE["defaultFormation"] == $group["formations"][0])){
$UE["defaultFormation"] = $group["formations"][0];
}else{
$UE["defaultFormation"] = null;
$isDefaultFormationPossible = false;
}
$UE["groups"]["Course"][] = $group;
}
//compute TDs
if(is_numeric(($UESpreadsheet->getCellByColumnAndRow(8,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue())){
$group = [
"VH" => ($UESpreadsheet->getCellByColumnAndRow(8,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue(),
"internalStudentPart" => $getInternalStudentPart(($UESpreadsheet->getCellByColumnAndRow(9,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"formations" => $getFormations(($UESpreadsheet->getCellByColumnAndRow(9,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"professor" => ($UESpreadsheet->getCellByColumnAndRow(10,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: null
];
if(count($group["formations"]) == 1 and $isDefaultFormationPossible and ($UE["defaultFormation"] == null or $UE["defaultFormation"] == $group["formations"][0])){
$UE["defaultFormation"] = $group["formations"][0];
}else{
$UE["defaultFormation"] = null;
$isDefaultFormationPossible = false;
}
$UE["groups"]["TD"][] = $group;
}
//compute TPs
if(is_numeric(($UESpreadsheet->getCellByColumnAndRow(11,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue())){
$group = [
"VH" => ($UESpreadsheet->getCellByColumnAndRow(11,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue(),
"internalStudentPart" => $getInternalStudentPart(($UESpreadsheet->getCellByColumnAndRow(12,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"formations" => $getFormations(($UESpreadsheet->getCellByColumnAndRow(12,$row->getRowIndex()) ?? $exitWithNullException())->getCalculatedValue()),
"professor" => ($UESpreadsheet->getCellByColumnAndRow(13,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: null
];
if(count($group["formations"]) == 1 and $isDefaultFormationPossible and ($UE["defaultFormation"] == null or $UE["defaultFormation"] == $group["formations"][0])){
$UE["defaultFormation"] = $group["formations"][0];
}else{
$UE["defaultFormation"] = null;
$isDefaultFormationPossible = false;
}
$UE["groups"]["TP"][] = $group;
}
}
/*
* Now we compute the professor list to get the full name , the number of hour he must do and the category he is in
*/
$spreadsheet->setActiveSheetIndex(1);
$ProfSpreadshit = $spreadsheet->getActiveSheet();
//array containing all the professors
$allProf = [];
//current professor category
$currentCategory = "";
//category as represented in the excel (as a number)
$currentCategoryIndex = 0;
foreach($ProfSpreadshit->getRowIterator() as $row){
//skip the first lines
if($row->getRowIndex() < self::startLineProf) continue;
$firstCellValue = ($ProfSpreadshit->getCellByColumnAndRow(2,$row->getRowIndex()) ?? $exitWithNullException())->getValue();
$secondCellValue = ($ProfSpreadshit->getCellByColumnAndRow(3,$row->getRowIndex()) ?? $exitWithNullException())->getValue();
//skip empty lines
if(!$firstCellValue and !$secondCellValue) continue;
//if the first cell contains something and not the second, it means that we change category
if($firstCellValue && !$secondCellValue){
$currentCategory = $firstCellValue;
$currentCategoryIndex++;
}
//if the line is valid
if(($ProfSpreadshit->getCellByColumnAndRow(3,$row->getRowIndex()) ?? $exitWithNullException())->getValue() and ($ProfSpreadshit->getCellByColumnAndRow(4,$row->getRowIndex()) ?? $exitWithNullException())->getValue()){
$allProf[($ProfSpreadshit->getCellByColumnAndRow(3,$row->getRowIndex()) ?? $exitWithNullException())->getValue()] = [
"categoryLabel" => $currentCategory,
"categoryIndex" => $currentCategoryIndex,
"lastName" => explode(" ",($ProfSpreadshit->getCellByColumnAndRow(4,$row->getRowIndex()) ?? $exitWithNullException())->getValue(),2)[1],
"firstName" => explode(" ",($ProfSpreadshit->getCellByColumnAndRow(4,$row->getRowIndex()) ?? $exitWithNullException())->getValue(),2)[0],
"hoursToDo" => ($ProfSpreadshit->getCellByColumnAndRow(5,$row->getRowIndex()) ?? $exitWithNullException())->getValue() ?: 0
];
}
}
/*
* adding data to the database
*/
//professors
/** @var professor $profRepo */
$profRepo = Repo::getRepo("professor");
/** @var category $catRepo */
$catRepo = Repo::getRepo("category");
foreach ($allProf as $initials => &$prof){
//create or update the professor category
$catRepo->create($prof["categoryIndex"], $prof["categoryLabel"]);
//create the professor, as some names are missing, we replace them by something else
if(!$prof["lastName"]) $prof["lastName"] = "missingLastName";
if(!$prof["firstName"]) $prof["firstName"] = "missingFirstName";
$prof["dbId"] = $profRepo->exists($prof["lastName"], $prof["firstName"]);
if(!$prof["dbId"]){
$prof["dbId"] = $profRepo->create( $prof["lastName"], $prof["firstName"], $prof["categoryIndex"], $prof["hoursToDo"], $initials);
}
}
//formation and retreive the databse IDs
/** @var \database\repo\formation $formRepo */
$formRepo = Repo::getRepo("formation");
foreach ($allFormations as &$form){
$form["dbId"] = $formRepo->exists( $form["name"]);
if(!$form["dbId"]){
$form["dbId"] = $formRepo->create( $form["name"], $form["internal"]);
}
}
//UEs and corresponding groups
/** @var ue $ueRepo */
$ueRepo = Repo::getRepo("ue");
/** @var cours $coursRepo */
$coursRepo = Repo::getRepo("cours");
/** @var td $tdRepo */
$tdRepo = Repo::getRepo("td");
/** @var tp $tpRepo */
$tpRepo = Repo::getRepo("tp");
$CoursToLink = [];
$TDToLink = [];
$TPToLink = [];
foreach ($allUE as $codeUE => $UE){
if($UE["defaultFormation"]){
$UE["defaultFormationId"] = $allFormations[$UE["defaultFormation"]]["dbId"];
}else{
$UE["defaultFormationId"] = null;
}
$ueRepo->create($codeUE,
$UE["name"],
$UE["required"] == "OBL",
$UE["CourseVH"],
$UE["TdVH"],
$UE["TpVH"],
$UE["disabled"],
$UE["defaultFormationId"]
);
if(isset($UE["groups"])){
foreach ($UE["groups"] as $type => $groups){
foreach ($groups as $group){
$formations = [];
foreach ($group["formations"] as $format){
if(isset($allFormations[$format]["dbId"])){
$formations[] = $allFormations[$format]["dbId"];
}
}
switch ($type){
case "Course":
$CoursToLink[] = ["id" => $coursRepo->create( $codeUE,
$allProf[$group["professor"]]["dbId"],
$UE["disabled"] ? $UE["CourseVH"] : $group["VH"],
[]),
"form" => $formations];
break;
case "TD":
$TDToLink[] = ["id" => $tdRepo->create($codeUE,
$allProf[$group["professor"]]["dbId"],
$UE["disabled"] ? $UE["TdVH"] : $group["VH"],
[]),
"form" => $formations];
break;
case "TP":
$TPToLink[] = ["id" => $tpRepo->create($codeUE,
$allProf[$group["professor"]]["dbId"],
$UE["disabled"] ? $UE["TpVH"] : $group["VH"],
[]),
"form" => $formations];
break;
}
}
}
}
}
Repo::enableStacking();
foreach ($CoursToLink as $cour){
foreach ($cour["form"] as $formation){
$coursRepo->linkFormation($formation,$cour["id"]);
}
}
foreach ($TDToLink as $cour){
foreach ($cour["form"] as $formation){
$tdRepo->linkFormation($formation,$cour["id"]);
}
}
foreach ($TPToLink as $cour){
foreach ($cour["form"] as $formation){
$tpRepo->linkFormation($formation,$cour["id"]);
}
}
Repo::flushStack();
//return [ 'data' => ["professors" => $allProf, "formations" => $allFormations, "UEs" => $allUE ] ];
return["data" => true];
}catch (Exception $e){
return [ 'error' => new Error(Err::UnknownError) ];
}
}else{
return [ 'error' => new Error(Err::UploadError) ];
}
}
}