<?php
#=
/****************************************************************************
Copyright 2002, Daniel Deycard, Jacques Messines, Alain Pomirol
(CATICE Académie de Bordeaux)

This file is part of GiBii/Melia.

GiBii/Melia 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; either version 2 of the License, or
(at your option) any later version.

GiBii/Melia 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 GiBii/Melia; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

****************************************************************************/
include("../include/vars_adm.php");
include("../include/util.inc.php");
include("../include/get.inc.php");
include("../include/utiljs.php");
opensession("Sadmin",false);
set_time_limit(0);

print "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n";
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
       <title>Activité étab</title>
<link rel="stylesheet" href="../include/general.css">
</head>
<body class="arial_sadmin" bgcolor="white" text="black" link="white" vlink="white" alink="blue">
<?php

$debug=0;

function lignetitres() {
global $PHP_SELF;
print "<tr class='rubrique'>\n";
print "\t<td><a href=\"$PHP_SELF?tri=etab_id\"><img src=\"./images/haut.gif\" border=\"0\" align=\"middle\"></a>&nbsp;N°&nbsp;<a href=\"$PHP_SELF?tri=etab_id&order=d\"><img src=\"./images/bas.gif\" border=\"0\" align=\"middle\"></a></td>";
print "\t<td><a href=\"$PHP_SELF?tri=rne\"><img src=\"./images/haut.gif\" border=\"0\" align=\"middle\"></a>&nbsp;Rne&nbsp;<a href=\"$PHP_SELF?tri=rne&order=d\"><img src=\"./images/bas.gif\" border=\"0\" align=\"middle\"></a></td>";
print "<td><a href=\"$PHP_SELF?tri=nom\"><img src=\"./images/haut.gif\" border=\"0\" align=\"middle\"></a>&nbsp;Nom&nbsp;<a href=\"$PHP_SELF?tri=nom&order=d\"><img src=\"./images/bas.gif\" border=\"0\" align=\"middle\"></a></td>";
print "<td><a href=\"$PHP_SELF?tri=ville\"><img src=\"./images/haut.gif\" border=\"0\" align=\"middle\"></a>&nbsp;Ville&nbsp;<a href=\"$PHP_SELF?tri=ville&order=d\"><img src=\"./images/bas.gif\" border=\"0\" align=\"middle\"></a></td>";
print "<td><a href=\"$PHP_SELF?tri=codepostal\"><img src=\"./images/haut.gif\" border=\"0\" align=\"middle\"></a>&nbsp;Code postal&nbsp;<a href=\"$PHP_SELF?tri=codepostal&order=d\"><img src=\"./images/bas.gif\" border=\"0\" align=\"middle\"></a></td>";
print "<td>&nbsp;Nb élèves&nbsp;</td>";
print "<td>Nb demandes</td>";
print "<td>Dem. par élève</td>";
print "<td>&nbsp;Nb de profs</td>";
print "<td>&nbsp;Nb profs actifs&nbsp;</td>";
print "<td>% profs actifs</td>";
print "<td>Graph</td>";
print "<td>Nb réponses</td>";	
print "<td>Rép. par prof</td></tr>\n";	
}

function getprofsactifs($etab) {
global $sess_ascol,$debug;
static $nbprofs=array();
if (empty($nbprofs)) {
  //On suppose la connexion ouverte
  //$debug++;
  $sql="SELECT etab_id, count(distinct profs.prof_id) as nbp FROM profs, lpi ".
               " WHERE annee='$sess_ascol'
                   and profs.prof_id=lpi.prof_id 
				   and profs.tag<>-1 GROUP BY etab_id";
  $res=query($sql);
  while (list($et,$pid)=mysql_fetch_array($res)) $nbprofs[$et]=$pid;
  }
if (isset($nbprofs[$etab])) return $nbprofs[$etab];
else return 0;
}

function getdemandes($etab) {
global $sess_ascol,$debug;
static $nbdem=array();
if (empty($nbdem)) {
  //$debug++;
  //demandes en cours de l'établissement
  $sql="SELECT etab_id, count(message_id) as nbd FROM demandes, eleves WHERE demandes.eleve_id=eleves.eleve_id
  				AND eleves.annee='$sess_ascol'
				 and eleves.tag<>-1 GROUP BY etab_id";
  $res=query($sql);
  while (list($et, $nbd)=mysql_fetch_array($res)) $nbdem[$et]=$nbd;
  }
if (isset($nbdem[$etab])) return $nbdem[$etab];
else return 0;
}

function getreponses($etab) {
global $sess_ascol, $debug;
static $nbrep=array();
if (empty($nbrep)) {
  //$debug++;	
  //réponses en cours de l'établissement
  $sql="SELECT etab_id, count(message_id) as nbr FROM reponses, profs ".
               " WHERE reponses.prof_id=profs.prof_id
                 and profs.annee='$sess_ascol'
				 and profs.tag<>-1  GROUP BY etab_id";
  $res=query($sql);
  while (list($et, $nbr)=mysql_fetch_array($res)) $nbrep[$et]=$nbr;
  }
if (isset($nbrep[$etab])) return $nbrep[$etab];
else  return 0;
}


//On fabrique le tableau
print "<p class='titre'>Statistiques d'activité par établissement</p>";
superflush();

switch(@$tri) {
   case "rne":
        $ordre="rne";
        break;
   case "nom":
        $ordre="nom";
        break;
   case "codepostal":
        $ordre="codepostal";
        break;
   case "ville":
        $ordre="ville";
        break;
   case "nbeleves":
        $ordre="nom";
        break;
   default :
        $ordre="etab_id";
}

if (@$order=="d") $ordre=$ordre." DESC";

$conn  = connecte($host,$user,$mdp,$db);
$sql="SELECT etab_id, rne, nom, codepostal, ville ".
             " FROM etablissements ".
             " ORDER BY $ordre ;" ;
$resetab=query ($sql);
$nbetabs=0;
$PHP_SELF=basename($_SERVER['PHP_SELF']);
print "<table border='1' align='center' cellspacing='0' >\n";

lignetitres();

while ($rowetab=mysql_fetch_array($resetab)) {
      $etab_id=$rowetab["etab_id"];
      $nbetabs++;
      $rne=$rowetab["rne"];
      $nom=$rowetab["nom"];
      $codepost=$rowetab["codepostal"];
      $ville=$rowetab["ville"];
      $nbprofs=getnbprofs($etab_id);
      $practifs=getprofsactifs($etab_id);
      //Pourcentage de profs actifs
      if ($nbprofs>0) $ppract=($practifs/$nbprofs)*100;
         else $ppract=0;
      $ppract=round($ppract,0);
      
      if ($ppract==0) $ppp='&nbsp;--';
      else $ppp=$ppract." %";
	  $affichpract=$practifs."</td><td>".$ppp;
      
      $demandes=getdemandes($etab_id);
      //Moyenne de demandes par élève
      $nbelev=getnbelev($etab_id);
      if ($nbelev>0) $pdem=round(($demandes/$nbelev),1);
         else $pdem='&nbsp;--';
		$affichdem=$demandes."</td><td>".$pdem;
		
      //Moyenne de réponses par prof actif
      $reponses=getreponses($etab_id);
      if ($practifs>0) $prep=round(($reponses/$practifs),1);
         else $prep='&nbsp;--';
		$affichrep=$reponses."</td><td>".$prep;
     //couleur de la ligne suivant sa parité
     $cl=($nbetabs%2==0) ? "_paire" : "_impaire";
	 $cltr='sadmin'.$cl;
	 $cltdm='stats_marron'.$cl;
	 $cltdr='stats_rose'.$cl;
	 $cltdo='stats_olive'.$cl;


     //Affichage d'une ligne
     print "\t<tr class='$cltr'>\n";
     print "\t<td align=\"center\">$etab_id</td>\n";
     print "\t<td align=\"center\">$rne</td>\n";
     print "\t<td >$nom</td>\n";
     print "\t<td >$ville</td>\n";
     print "\t<td >$codepost</td>\n";
     print "\t<td >$nbelev</td >\n";
     print "\t<td class='$cltdm'>$demandes</td>\n";
     print "\t<td class='$cltdm'>$pdem</td>\n";
     
	 print "\t<td >$nbprofs</td>\n";
     print "\t<td class='$cltdr'>$practifs</td>\n";
     print "\t<td class='$cltdr'>$ppp</td>\n";
     
	//Les graphiques !
     if ($practifs>0) {
     	print "\t<td>";
	 	print "\t<a href='../gestion/graph_disciplines.php?etab_id=$etab_id' target='_blank'><img src='./images/graphpie.gif' border='0' alt='Par discipline'></a>\n";   
	 	//if ($STATSPROFS) print "\t<a href='../gestion/graph_profs.php?etab_id=$etab_id' target='_blank'><img src='./images/graphpiep.gif' border='0' alt='Par professeur'></a>\n";
	 	print "\t</td>\n";   
	 	}
     else print "<td>&nbsp;--</td>";
     
     print "\t<td class='$cltdo'>$reponses</td>\n";
     print "\t<td class='$cltdo'>$prep</td>\n";
     print "\t</tr>\n";
}

//nouvelle ligne de titres
lignetitres();

 print "</table><br>\n";
 if ($nbetabs>1) print $nbetabs." établissements";
    else print $nbetabs." établissement";
    
//print "<br />$debug requêtes";

?>
</body>
</html>
