<?php
	#########################################################################################
	# Fonctions AJAX de Pnyx																#
	# Auteur : B. Leblanc																	#
	# Création : 2008																		#
	# Modification : 2008																	#
	# 																						#
	# Pnyx - une application Web (PHP/MySQL) d'élections virtuelles conçue pour	des	élèves	#
	# du primaire et du secondaire, dans lequel ils sont amenés à faire des	propositions	#
	# en ligne et voter pour leur favorite, sous la supervision d'un enseignant.			#
	#																						#
    # Copyright (C) <2008 - 2009>															#
	# <Service national du RÉCIT de l'univers social de la Commission scolaire de la		#
	# Pointe-de-l'Île>																		#
	#																						#
	# This program is free software; you can redistribute it and/or modify it under the 	#
	# terms of the GNU General Public License as published by the Free Software Foundation.	#
	# This program is distributed in the hope that it will be useful, but WITHOUT ANY 		#
	# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A 		#
	# PARTICULAR PURPOSE.  See the GNU General Public License for more details.				#
	# You should have received a copy of the GNU General Public License along with this 	#
	# program as the file LICENSE.txt; if not, please see 									#
	# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.									#
	# 																						#
	# Service national du RÉCIT de l'univers social											#
	# http://www.recitus.qc.ca																#
	# Contact e-mail : steve-hide@address.com												#
	#########################################################################################

//$_POST = array_map(utf8_decode(), $_POST);
require "constantes.php";

//header('Content-Type: text/html; charset=ISO-8859-1');

$connection = mysql_connect($HOTE,$USAGER,$MOTPASSE) or die("Impossible de se connecter : " . mysql_error());

// Sélectionne une base de données MySQL, si cela n'est pas possible alors fin de l'exécution du code
		if (!mysql_select_db($BD)){
		   die("Impossible d'acc&eacute;der &agrave; la base de donn&eacute;es  : " . mysql_error());
		}

session_name("pnyx");		
session_start();

$html = "";

$requete_sql = "SELECT projet_id
				FROM projet
				WHERE statut = \"actif\"
				";
$reponse_projet = mysql_query($requete_sql);
$ligne_projet = mysql_fetch_array($reponse_projet);

if($_POST['niv'] != ""){
	$clause_niveau = "AND F.niveau = '" . $_POST['niv'] . "'";
}
else{
	$clause_niveau = "";
}

if($_POST['cat'] != ""){
	$clause_cat = "AND F.categorie = '" . $_POST['cat'] . "'";
}
else{
	$clause_cat = "";
}

if($_POST['grp'] != ""){
	$clause_groupe = "AND G.groupe_id = '" . $_POST['grp'] . "'";
}
else{
	$clause_groupe = "";
}

if($_POST['nivs'] != ""){
	$clause_nscol = "AND G.niveau = '" . $_POST['nivs'] . "'";
}
else{
	$clause_nscol = "";
}

if($_POST['type'] != ""){
	$clause_typegrp = "AND G.type = '" . $_POST['type'] . "'";
}
else{
	$clause_typegrp = "";
}

if($_POST['ecole'] != ""){
	$clause_ecole = "AND EC.ecole_id = '" . $_POST['ecole'] . "'";
}
else{
	$clause_ecole = "";
}

if($_POST['cs'] != ""){
	$clause_cs = "AND EC.ecole_id = '" . $_POST['cs'] . "'";
}
else{
	$clause_cs = "";
}

if($_POST['region'] != ""){
	$clause_region = "AND RE.region_id = '" . $_POST['region'] . "'";
}
else{
	$clause_region = "";
}

$ordre = "";
if($_POST['choix'] == "date_asc"){
	$ordre = "ORDER BY F.date_valide ASC";
}
else if($_POST['choix'] == "date_desc"){
	$ordre = "ORDER BY F.date_valide DESC";
}
else if($_POST['choix'] == "prop_asc"){
	$ordre = "ORDER BY F.slogan ASC";
}
else if($_POST['choix'] == "prop_desc"){
	$ordre = "ORDER BY F.slogan DESC";
}
else if($_POST['choix'] == "auteur_asc"){
	$ordre = "ORDER BY U.login ASC";
}
else if($_POST['choix'] == "auteur_desc"){
	$ordre = "ORDER BY U.login DESC";
}
else if($_POST['choix'] == "cat_asc"){
	$ordre = "ORDER BY C.nom ASC";
}
else if($_POST['choix'] == "cat_desc"){
	$ordre = "ORDER BY C.nom DESC";
}
else if($_POST['choix'] == "ecole_asc"){
	$ordre = "ORDER BY EC.nom ASC";
}
else if($_POST['choix'] == "ecole_desc"){
	$ordre = "ORDER BY EC.nom DESC";
}
else if($_POST['choix'] == "groupe_asc"){
	$ordre = "ORDER BY G.nom ASC";
}
else if($_POST['choix'] == "groupe_desc"){
	$ordre = "ORDER BY G.nom DESC";
}
else if($_POST['choix'] == "app_asc"){
	$ordre = "ORDER BY F.app_moyenne ASC";
}
else if($_POST['choix'] == "app_desc"){
	$ordre = "ORDER BY F.app_moyenne DESC";
}

$requete_sql = "SELECT F.*
				FROM (((((((fiche AS F LEFT JOIN eleve AS E ON F.eleve_id = E.eleve_id)
									  LEFT JOIN usager AS U ON E.usager_id = U.usager_id)
									  LEFT JOIN groupe AS G ON E.groupe_id = G.groupe_id)
									  LEFT JOIN responsable AS R ON G.resp_id = R.resp_id)
									  LEFT JOIN ecole AS EC ON R.ecole_id = EC.ecole_id)
									  LEFT JOIN commission_scolaire AS CS ON EC.comscol_id = CS.comscol_id)
									  LEFT JOIN region AS RE ON CS.region_id = RE.region_id)
									  LEFT JOIN categorie_prop AS C ON F.categorie = C.cat_id
				WHERE E.projet_id = '" . $ligne_projet['projet_id'] . "'
				AND F.valide = 1
				$clause_niveau
				$clause_cat
				$clause_groupe
				$clause_nscol
				$clause_typegrp
				$clause_ecole
				$clause_cs
				$clause_region
				$ordre";
$reponse_fiche = mysql_query($requete_sql);

$positionTab = 0;
$tabProp = array();
while($ligne_fiche = mysql_fetch_array($reponse_fiche)){	
	$tabProp[$positionTab] = $ligne_fiche["fiche_id"];
	$positionTab++;
}

$HTTP_SESSION_VARS['listeProp'] = $tabProp;

$position = 0;
$debut = $_POST['debut'];
$position += $debut;
foreach($tabProp as $positionTab => $fiche_id) {

	if($positionTab < ($debut + 10) && $positionTab >= $debut){	
		$requete_sql = "SELECT *
						FROM fiche
						WHERE fiche_id = $fiche_id
						";
		$reponse_fiche = mysql_query($requete_sql);
		$ligne_fiche = mysql_fetch_array($reponse_fiche);
					
		$requete_sql = "SELECT *
						FROM eleve E, usager U
						WHERE E.eleve_id = \"$ligne_fiche[eleve_id]\"
						AND U.usager_id = E.usager_id
						";
		$reponse_eleve = mysql_query($requete_sql);
		$ligne_eleve = mysql_fetch_array($reponse_eleve);
				
		$requete_sql = "SELECT *
						FROM groupe
						WHERE groupe_id = \"$ligne_eleve[groupe_id]\"
						";
		$reponse_grp = mysql_query($requete_sql);
		$ligne_grp = mysql_fetch_array($reponse_grp);
				
		$requete_sql = "SELECT E.nom
						FROM ecole E, responsable R
						WHERE R.resp_id = \"$ligne_grp[resp_id]\"
						AND E.ecole_id = R.ecole_id
						";
		$reponse_ecole = mysql_query($requete_sql);
		$ligne_ecole = mysql_fetch_array($reponse_ecole);
	
		$html .= "<h4><a href='gen_rech_prog2.php?type_action=aff_prop&amp;id_prop=" . $ligne_fiche['fiche_id'] . "&amp;pos=" . $position . "'>" . utf8_encode($ligne_fiche['slogan']) . "</a></h4>";
		$html .= "<table cellpadding=\"0\" cellspacing=\"0\" width=\"600\" border=\"0\" align=\"center\">";
		$html .= "<tr>";
		$html .= "<td width=\"200\" align=\"center\" valign=\"top\">";
		$html .= "<a href='gen_rech_prog2.php?type_action=aff_prop&amp;id_prop=" . $ligne_fiche['fiche_id'] . "&amp;pos=" . $position . "'>";
	
		if ($ligne_fiche['affiche'] == ""){
			$affiche = "images/gen_affiche.jpg";
		} else {
			$affiche = $ligne_fiche['affiche'];
		}
		
		list($width) = getimagesize($affiche);
		if ($width > 200){
			$html .= "<img src='".$affiche."' alt='affiche' width='200' border='0'/>";
		} else {
			$html .= "<img src='".$affiche."' alt='affiche' width='200' border='0' />";
		}
	
		$html .= "</a>";
		$html .= "</td>";
		$html .= "<td align=\"center\">";
		$html .= "<table cellpadding=\"0\" cellspacing=\"0\" width=\"95%\" align=\"right\">";
		$html .= "<tr>";
		$html .= "<td width=\"30%\" valign=\"top\">Auteur :</td>";
		$html .= "<td width='70%'>" . utf8_encode($ligne_eleve['login']) . "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">Niveau : </td>";
		$html .= "<td>" . utf8_encode($ligne_fiche['niveau']) . "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">Cat&eacute;gorie : </td>";
		$html .= "<td>";
		
		$requete_sql = "SELECT nom
						FROM categorie_prop
						WHERE cat_id = \"$ligne_fiche[categorie]\"
						";
		$reponse_catNom = mysql_query($requete_sql);
		$ligne_catNom = mysql_fetch_array($reponse_catNom);
	
		$html .= utf8_encode($ligne_catNom['nom']);
		$html .= "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">&Eacute;cole : </td>";
		$html .= "<td>" . utf8_encode($ligne_ecole['nom']) . "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">Niveau scolaire : </td>";
		$html .= "<td>" . utf8_encode($ligne_grp['niveau']) . "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">Groupe : </td>";
		$html .= "<td>" . utf8_encode($ligne_grp['nom']) . "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">Appr&eacute;ciation : </td>";
		$html .= "<td>";
	
		if ($ligne_fiche['app_moyenne'] < 0.50){
			$html .= "<img src='images/etoile_0.jpg' alt='0' />";
		} else if ($ligne_fiche['app_moyenne'] < 0.99){
			$html .= "<img src='images/etoile_0_5.jpg' alt='0,5' />";
		} else if ($ligne_fiche['app_moyenne'] < 1.50){
			$html .= "<img src='images/etoile_1.jpg' alt='1' />";
		} else if ($ligne_fiche['app_moyenne'] < 2.00){
			$html .= "<img src='images/etoile_1_5.jpg' alt='1,5' />";
		} else if ($ligne_fiche['app_moyenne'] < 2.50){
			$html .= "<img src='images/etoile_2.jpg' alt='2' />";
		} else if ($ligne_fiche['app_moyenne'] < 3.00){
			$html .= "<img src='images/etoile_2_5.jpg' alt='2,5' />";
		} else if ($ligne_fiche['app_moyenne'] < 3.50){
			$html .= "<img src='images/etoile_3.jpg' alt='3' />";
		} else if ($ligne_fiche['app_moyenne'] < 4.00){
			$html .= "<img src='images/etoile_3_5.jpg' alt='3,5' />";
		} else if ($ligne_fiche['app_moyenne'] < 4.50){
			$html .= "<img src='images/etoile_4.jpg' alt='4' />";
		} else if ($ligne_fiche['app_moyenne'] < 5.00){
			$html .= "<img src='images/etoile_4_5.jpg' alt='4,5' />";
		} else {
			$html .= "<img src='images/etoile_5.jpg' alt='5' />";
		}
		
		$html .= "<br />";
		$html .= "</td>";
		$html .= "</tr>";
		$html .= "<tr>";
		$html .= "<td valign=\"top\">&nbsp;</td>";
		$html .= "<td>";
									
		if ($ligne_fiche['app_nb'] > 1){
			$html .= "&nbsp;".$ligne_fiche['app_nb']." appr&eacute;ciations";
		} else if ($ligne_fiche['app_nb'] == 1){
			$html .= "&nbsp;".$ligne_fiche['app_nb']." appr&eacute;ciation";
		}
		
		$html .= "<br />";
		$html .= "</td>";
		$html .= "</tr>";
		$html .= "</table>";
		$html .= "</td>";
		$html .= "</tr>";
		$html .= "</table>";
		$html .= "<hr align=\"center\" width=\"400\" />";
		$position++;
	}
}
	
mysql_close($connection);

echo $html;
?>