<?php
$DB_DATABASE = "";
$prefix = "iitech_";
if(isset($_POST['database'])){
	$DB_DATABASE = $prefix.$_POST['database'];
}

// include db connect class
require_once __DIR__ . '/include/new_db_connect.php';

// connecting to db
$bdd = connect($DB_DATABASE);

$matricule = "";
$niveau = "";
$annee = 3;
$idSemestre = "";

if(isset($_POST['matricule'])){
	$matricule = $_POST['matricule'];
}
if(isset($_POST['niveau'])){
    $niveau = $_POST['niveau'];
}
if(isset($_POST['idSemestre'])){
    $idSemestre = $_POST['idSemestre'];
}

$res = 'A';

$query = "SELECT *  FROM tabinscriunite WHERE matricule = :matr AND annee <= :annee and niveauFormation = :niveau 
AND resultat = :res";
$statement = $bdd->prepare($query);
$statement->bindParam(':matr', $matricule, PDO::PARAM_STR);
$statement->bindParam(':annee', $annee, PDO::PARAM_STR);
$statement->bindParam(':niveau', $niveau, PDO::PARAM_STR);
$statement->bindParam(':res', $res, PDO::PARAM_STR);
$statement->execute();

$maSomme1 = 0;

while($row=$statement->fetch(PDO::FETCH_ASSOC)){
	$pi2         	= $row['premiere_inscription'];
	$code_unite2 	= $row['code_unite'];
	$idF 	   		= $row['idFiliere'];	
	$query = "SELECT credit FROM unites WHERE code_unite = :code AND idFiliere = :id and annee_acad = :acad AND annee = :annee AND niveauFormation = :niveau";
	$statement1 = $bdd->prepare($query);
	$statement1->bindParam(':code', $code_unite2, PDO::PARAM_STR);
	$statement1->bindParam(':id', $idF, PDO::PARAM_STR);
	$statement1->bindParam(':acad', $pi2, PDO::PARAM_STR);
	$statement1->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement1->bindParam(':niveau', $niveau, PDO::PARAM_STR);
	$statement1->execute();
	$row1=$statement1->fetch(PDO::FETCH_ASSOC);
	$maSomme1 = $maSomme1 + $row1['credit'];	
}

$query = "SELECT *  FROM tabinscriunite WHERE matricule = :matr AND annee = :annee and niveauFormation = :niveau 
AND resultat = :res";
$statement = $bdd->prepare($query);
$statement->bindParam(':matr', $matricule, PDO::PARAM_STR);
$statement->bindParam(':annee', $annee, PDO::PARAM_STR);
$statement->bindParam(':niveau', $niveau, PDO::PARAM_STR);
$statement->bindParam(':res', $res, PDO::PARAM_STR);
$statement->execute();

$maSomme2 = 0;

while($row=$statement->fetch(PDO::FETCH_ASSOC)){
	$pi2         	= $row['premiere_inscription'];
	$code_unite2 	= $row['code_unite'];
	$idF 	   		= $row['idFiliere'];	
	$query = "SELECT credit FROM unites WHERE code_unite = :code AND idFiliere = :id and annee_acad = :acad AND annee = :annee AND niveauFormation = :niveau";
	$statement1 = $bdd->prepare($query);
	$statement1->bindParam(':code', $code_unite2, PDO::PARAM_STR);
	$statement1->bindParam(':id', $idF, PDO::PARAM_STR);
	$statement1->bindParam(':acad', $pi2, PDO::PARAM_STR);
	$statement1->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement1->bindParam(':niveau', $niveau, PDO::PARAM_STR);
	$statement1->execute();
	$row1=$statement1->fetch(PDO::FETCH_ASSOC);
	$maSomme2 = $maSomme2 + $row1['credit'];	
}

$query = "SELECT * FROM  tabinscription WHERE matricule = :matricule AND annee = :annee AND niveauFormation = :niveau";
$statement = $bdd->prepare($query);
$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
$statement->bindParam(':annee', $annee, PDO::PARAM_STR);
$statement->bindParam(':niveau', $niveau, PDO::PARAM_STR);
$statement->execute();
$tab=$statement->fetch(PDO::FETCH_ASSOC);

$annee_acad = $tab['annee_acad'];	 
$idfiliere 	= $tab['idFiliere'];	

$percent     = "";
$resultat    = "";

$nombreUE = 0;
$m0 = 0;

if($idSemestre== 0) {
	$percent     = $tab['pourcentageCredit'];
	$resultat    = $tab['resultat'];	
	$reqNBUE = "SELECT code_unite from unites WHERE idFiliere = :id and annee_acad = :acad AND annee = :annee 
	AND niveauFormation = :niveau";
	$statement1 = $bdd->prepare($reqNBUE);
	$statement1->bindParam(':id', $idfiliere, PDO::PARAM_STR);
	$statement1->bindParam(':acad', $annee_acad, PDO::PARAM_STR);
	$statement1->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement1->bindParam(':niveau', $niveau, PDO::PARAM_STR);
	$statement1->execute();
	$nombreUE = $statement1->rowCount();
	
	$query = "SELECT code_unite from tabinscriunite WHERE idFiliere = :id AND annee = :annee AND matricule = :matricule 
	AND resultat = :res";
	$statement2 = $bdd->prepare($query);
	$statement2->bindParam(':id', $idfiliere, PDO::PARAM_STR);
	$statement2->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement2->bindParam(':matricule', $matricule, PDO::PARAM_STR);
	$statement2->bindParam(':res', $res, PDO::PARAM_STR);
	$statement2->execute();
	$m0 = $statement2->rowCount();
}else{
	if($idSemestre % 2 == 0) {//semestre pair
		$percent   = $tab['percentS2'];	
		$resultat  = $tab['resultatS2'];
	}else if($idSemestre % 2 == 1){//semestre impair
		$percent   = $tab['percentS1'];
		$resultat  = $tab['resultatS1'];	
	}
	$reqNBUE = "SELECT code_unite from unites WHERE idFiliere = :id and annee_acad = :acad AND annee = :annee 
	AND niveauFormation = :niveau and semestre = :sem";
	$statement1 = $bdd->prepare($reqNBUE);
	$statement1->bindParam(':id', $idfiliere, PDO::PARAM_STR);
	$statement1->bindParam(':acad', $annee_acad, PDO::PARAM_STR);
	$statement1->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement1->bindParam(':niveau', $niveau, PDO::PARAM_STR);
	$statement1->bindParam(':sem', $idSemestre, PDO::PARAM_STR);
	$statement1->execute();
	$nombreUE = $statement1->rowCount();
	
	$query = "SELECT code_unite from tabinscriunite WHERE idFiliere = :id AND annee = :annee AND matricule = :matricule 
	AND resultat = :res AND code_unite IN (SELECT  code_unite FROM unites WHERE semestre = :sem AND annee = :annee2 AND idFiliere = :id2 
	AND annee_acad = :acad)";
	$statement2 = $bdd->prepare($query);
	$statement2->bindParam(':id', $idfiliere, PDO::PARAM_STR);
	$statement2->bindParam(':annee', $annee, PDO::PARAM_STR);
	$statement2->bindParam(':matricule', $matricule, PDO::PARAM_STR);
	$statement2->bindParam(':res', $res, PDO::PARAM_STR);
	$statement2->bindParam(':sem', $idSemestre, PDO::PARAM_STR);
	$statement2->bindParam(':annee2', $annee, PDO::PARAM_STR);
	$statement2->bindParam(':id2', $idfiliere, PDO::PARAM_STR);
	$statement2->bindParam(':acad', $annee_acad, PDO::PARAM_STR);
	$statement2->execute();
	$m0 = $statement2->rowCount();
}



$query = "select max(inscription_actuelle) as ia from tabinscriunite where idFiliere = :id AND annee = :annee AND niveauFormation = :niveau AND matricule = :matricule";
$statement = $bdd->prepare($query);
$statement->bindParam(':id', $idfiliere, PDO::PARAM_STR);
$statement->bindParam(':annee', $annee, PDO::PARAM_STR);
$statement->bindParam(':niveau', $niveau, PDO::PARAM_STR);
$statement->bindParam(':matricule', $matricule, PDO::PARAM_STR);
$statement->execute();
$tab=$statement->fetch(PDO::FETCH_ASSOC);
$ia = $tab['ia'];

$modulo = 180;
if($maSomme2 != 0) {
	if($maSomme1%$modulo != 0) {
		$message = "In progress";
	}else{
		$message = "Validated";
	}	
}else{
	$message = "In progress";
}
$periode = $annee_acad - 1;
$info = "Year 3 : $periode-$ia :: $message";
$arr1 = array('percent' => $percent, 'resultat' => $resultat, 'ueValide' => $m0, 'ueTotal' => $nombreUE , 'idSemestre' => $idSemestre, 'creditValide1' => $maSomme1, 'creditValide2' => $maSomme2, 'infocursus' => $info);

$jsonResultat1  = array();
$jsonResultat1['unites'][]=$arr1;

$json   = array();

$json['ue'][]   = $jsonResultat1;
echo json_encode($json); 
?>
