<?php
/**
** Classe RequeteEtudiant
** Usage : Gestion des requetes des etudiants
**/
include_once('Basededonnees.php');

class RequeteEtudiant{

	/***********************\
	*Fonction: recupereRequete
	*Usage: Recuperation de toutes les requetes envoyer au professeur
	\***********************/
	public function recupereRequete($matriculeetu, $bdd){
		$query='SELECT r.idrequete, t.nom, t.prenom, ec.intituleEcu AS matiere, f.intituleFilieres AS filiere, a.annee_etude AS anneeetude, r.requete, r.requetefichier, r.etat, r.statut, r.dateenvoie, r.heureenvoie, r.sujet, n.niveauFormation AS niveau
		FROM requeteetudiant AS r
		INNER JOIN ecu AS ec ON r.idecu= ec.idEC
		INNER JOIN tabetudiant AS t ON t.matricule= r.idetudiant
		INNER JOIN filieres AS f ON f.idFiliere = ec.idFiliere
		INNER JOIN anneeetude AS a ON a.id= ec.annee
		INNER JOIN niveauformation AS n ON n.id= ec.niveauFormation
		WHERE r.idetudiant=? ORDER BY idecu DESC';
		$req= $bdd->prepare($query);
		$req->execute(array($matriculeetu));
		
		return $req;
	}
	
	/***************************\
	* Fonction : supprimeRequete
	* Usage : supprime les elements dont les id sont renvoyés en arguments 
	\***************************/
	public function supprimeRequete($tabidrequete, $bdd){
		$id=implode(',',$tabidrequete);
		$query="DELETE FROM requeteetudiant WHERE idrequete IN (".$id.")";
		$req= $bdd->query($query);
		// $req= $bdd->prepare($query);
		// $req->execute(array('id'=>$id));
		if($req) return true;
		else return false;
	}
	
	/***************************\
	* Fonction : elementRequete
	* Usage : renvoie les éléments de la requete demandée 
	\***************************/
	public function elementRequete($idrequete, $bdd){
		$query="SELECT r.idrequete, t.nom, t.prenom, ec.intituleEcu AS matiere, f.intituleFilieres AS filiere, CONCAT(n.niveauFormation,' / ', a.annee_etude) AS anneeetude, r.requete, r.requetefichier, r.etat, r.statut, r.dateenvoie, r.heureenvoie, r.sujet, r.reponse, r.reponsefichier, r.datereponse, r.heurereponse
		FROM requeteetudiant AS r
		INNER JOIN tabetudiant AS t ON t.matricule= r.idetudiant
		INNER JOIN ecu AS ec ON ec.idEC= r.idecu
		INNER JOIN filieres AS f ON f.idFiliere = ec.idFiliere
		INNER JOIN anneeetude AS a ON a.id= ec.annee
		INNER JOIN niveauformation AS n ON n.id= ec.niveauFormation
		WHERE r.idrequete=:id";
		$req= $bdd->prepare($query);
		$req->execute(array('id'=>$idrequete));
		return $req;
	}
	
	
	/**********************************\
	* Fonction : insertReponseRequete
	* Usage : Elle insert la reponse des professeurs au requetes des etudiants
	\**********************************/
	public function insertReponseRequete($reponse, $idrequete, $lienfichier="", $bdd){
		$query="UPDATE requeteetudiant SET reponse=:reponse, reponsefichier=:fichier, datereponse=:date, heurereponse=:heure WHERE idrequete=:id";
		$req= $bdd->prepare($query);
		$req->execute(array('reponse'=>$reponse, 'fichier'=>$lienfichier, 'id'=>$idrequete, 'date'=>date('m/d/Y'), 'heure'=>date('H:i')));
		if($req)
			return true;
		else
			return false;
	}
	
}
?>
