modules/stats.php

   1 <?php
   2 /***************************************************************************
   3  *  Copyright (C) 2003-2011 Polytechnique.org                              *
   4  *  http://opensource.polytechnique.org/                                   *
   5  *                                                                         *
   6  *  This program is free software; you can redistribute it and/or modify   *
   7  *  it under the terms of the GNU General Public License as published by   *
   8  *  the Free Software Foundation; either version 2 of the License, or      *
   9  *  (at your option) any later version.                                    *
  10  *                                                                         *
  11  *  This program is distributed in the hope that it will be useful,        *
  12  *  but WITHOUT ANY WARRANTY; without even the implied warranty of         *
  13  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the          *
  14  *  GNU General Public License for more details.                           *
  15  *                                                                         *
  16  *  You should have received a copy of the GNU General Public License      *
  17  *  along with this program; if not, write to the Free Software            *
  18  *  Foundation, Inc.,                                                      *
  19  *  59 Temple Place, Suite 330, Boston, MA  02111-1307  USA                *
  20  ***************************************************************************/
  21
  22 function serv_to_str($params) {
  23     $flags = explode(',',$params);
  24     $trad = Array('web' => 'site web', 'mail'=> 'redirection mail',
  25                   'smtp' => 'serveur sécurisé d\'envoi de mails',
  26                   'nntp' => 'serveur des forums de discussion');
  27     $ret = Array();
  28     foreach ($flags as $flag) {
  29         $ret[] = $trad[$flag];
  30     }
  31     return implode(', ',$ret);
  32 }
  33
  34 class StatsModule extends PLModule
  35 {
  36     function handlers()
  37     {
  38         return array(
  39             'stats'                 => $this->make_hook('stats',     AUTH_COOKIE, 'user'),
  40             'stats/evolution'       => $this->make_hook('evolution', AUTH_COOKIE, 'user'),
  41             'stats/graph'           => $this->make_hook('graph',     AUTH_COOKIE, 'user'),
  42             'stats/graph/evolution' => $this->make_hook('graph_evo', AUTH_COOKIE, 'user'),
  43             'stats/promos'          => $this->make_hook('promos',    AUTH_COOKIE, 'user'),
  44
  45             'stats/coupures'        => $this->make_hook('coupures',  AUTH_PUBLIC),
  46         );
  47     }
  48
  49     function handler_stats($page)
  50     {
  51         $page->changeTpl('stats/index.tpl');
  52     }
  53
  54     function handler_evolution($page, $days = 365)
  55     {
  56         $page->changeTpl('stats/evolution_inscrits.tpl');
  57         $page->assign('days', $days);
  58     }
  59
  60     function handler_graph_evo($page, $days = 365)
  61     {
  62         $day_length = 24 * 3600;
  63
  64         // Retrieve the registration count per days during the given date range.
  65         $res = XDB::iterRow('SELECT  IF(registration_date > DATE_SUB(NOW(), INTERVAL {?} DAY),
  66                                         TO_DAYS(registration_date) - TO_DAYS(NOW()),
  67                                         - {?}) AS day,
  68                                      COUNT(a.uid) AS nb
  69                                FROM  accounts         AS a
  70                          INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
  71                          INNER JOIN  profiles         AS p  ON (ap.pid = p.pid)
  72                               WHERE  a.state = \'active\' AND p.deathdate IS NULL
  73                            GROUP BY  day',
  74                             (int)$days, 1 + (int)$days);
  75
  76         // The first contains the registration count before the starting date (J - $days)
  77         list(, $init_nb) = $res->next();
  78         $total   = $init_nb;
  79         $num_day = - $days - 1;
  80
  81         $registered = '';
  82         for ($i = -$days; $i <= 0; ++$i) {
  83             if ($num_day < $i) {
  84                 if(!list($num_day, $nb) = $res->next()) {
  85                     $num_day = 0;
  86                     $nb = 0;
  87                 }
  88             }
  89             if ($num_day == $i) {
  90                 $total += $nb;
  91             }
  92             $registered .= date('d/m/y', $i * $day_length + time()) . ' ' . $total . "\n";
  93         }
  94
  95         //Genere le graphique à la volée avec GNUPLOT
  96         pl_cached_dynamic_content_headers("image/png");
  97
  98         $delt = ($total - $init_nb) / 10;
  99         $delt = $delt ? $delt : 5;
 100         $ymin = round($init_nb - $delt, 0);
 101         $ymax = round($total   + $delt, 0);
 102
 103         $gnuplot = <<<EOF2
 104 gnuplot <<EOF
 105
 106 set term png small color
 107 set size 640/480
 108 set xdata time
 109 set timefmt "%d/%m/%y"
 110
 111 set format x "%d/%m\\n%Y"
 112 set yr [$ymin:$ymax]
 113
 114 set title "Nombre d'inscrits"
 115
 116 plot "-" using 1:2 title 'inscrits' with lines;
 117 {$registered}
 118 EOF
 119 EOF2;
 120
 121         passthru($gnuplot);
 122         exit;
 123     }
 124
 125     function handler_graph($page, $promo = null)
 126     {
 127         if (in_array($promo, array(Profile::DEGREE_X, Profile::DEGREE_M, Profile::DEGREE_D))) {
 128             $cycle = Profile::$cycles[$promo] . 's';
 129             $res = XDB::iterRow("SELECT  pe.promo_year, SUM(a.state = 'active') / COUNT(*) * 100
 130                                    FROM  accounts                      AS a
 131                              INNER JOIN  account_profiles              AS ap  ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
 132                              INNER JOIN  profiles                      AS p   ON (p.pid = ap.pid)
 133                              INNER JOIN  profile_education             AS pe  ON (pe.pid = ap.pid AND FIND_IN_SET('primary', pe.flags))
 134                              INNER JOIN  profile_education_degree_enum AS ped ON (pe.degreeid = ped.id)
 135                                   WHERE  p.deathdate IS NULL AND ped.degree = {?}
 136                                GROUP BY  pe.promo_year",
 137                                 $promo);
 138
 139             list($promo, $count) = $res->next();
 140             $first = $promo;
 141             $registered = $promo . ' ' . $count . "\n";
 142             while ($next = $res->next()) {
 143                 list($promo, $count) = $next;
 144                 $registered .= $promo . ' ' . $count . "\n";
 145             }
 146             $last = $promo + 2;
 147
 148             // Generate drawing thanks to Gnuplot.
 149             $gnuplot = <<<EOF2
 150 gnuplot <<EOF
 151
 152 set term png small color
 153 set size 640/480
 154 set timefmt "%d/%m/%y"
 155
 156 set xr [$first:$last]
 157 set yr [0:100]
 158
 159 set title "Proportion de $cycle inscrits par promotion, en %."
 160 set key left top
 161
 162 plot "-" using 1:2 title 'inscrits' with boxes;
 163 {$registered}
 164 EOF
 165 EOF2;
 166
 167         } else {
 168             $day_length = 24 * 3600;
 169             $days = 365;
 170
 171             $res = XDB::query("SELECT  MIN(TO_DAYS(a.registration_date) - TO_DAYS(NOW()))
 172                                  FROM  accounts         AS a
 173                            INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
 174                            INNER JOIN  profile_display  AS pd ON (ap.pid = pd.pid)
 175                                 WHERE  pd.promo = {?} AND a.state = 'active'",
 176                               $promo);
 177             $days = -$res->fetchOneCell();
 178
 179             // Retrieve the registration count per days during the given date range.
 180             $res = XDB::iterRow("SELECT  IF(a.registration_date > DATE_SUB(NOW(), INTERVAL {?} DAY),
 181                                             TO_DAYS(a.registration_date) - TO_DAYS(NOW()),
 182                                             - {?}) AS day,
 183                                          COUNT(a.uid) AS nb
 184                                    FROM  accounts         AS a
 185                              INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
 186                              INNER JOIN  profile_display  AS pd ON (ap.pid = pd.pid)
 187                                   WHERE  pd.promo = {?} AND a.state = 'active'
 188                                GROUP BY  day",
 189                                 (int)$days, 1 + (int)$days, $promo);
 190
 191             // The first line contains the registration count before starting date (D - $days).
 192             list(, $init_nb) = $res->next();
 193             $total = $init_nb;
 194             $registered = '';
 195
 196             list($num_day, $nb) = $res->next();
 197
 198             for ($i = -$days; $i <= 0; ++$i) {
 199                 if ($num_day < $i) {
 200                     if(!list($num_day, $nb) = $res->next()) {
 201                         $num_day = 0;
 202                         $nb = 0;
 203                     }
 204                 }
 205                 if ($num_day == $i) {
 206                     $total += $nb;
 207                 }
 208                 $registered .= date('d/m/y', $i * $day_length + time()) . ' ' . $total . "\n";
 209             }
 210
 211             // Generate drawing thanks to Gnuplot.
 212             $delt = ($total - $init_nb) / 10;
 213             $delt += ($delt < 1);
 214             $ymin = round($init_nb - $delt, 0);
 215             $ymax = round($total   + $delt, 0);
 216
 217             $gnuplot = <<<EOF2
 218 gnuplot <<EOF
 219
 220 set term png small color
 221 set size 640/480
 222 set xdata time
 223 set timefmt "%d/%m/%y"
 224
 225 set format x "%m/%y"
 226 set yr [$ymin:$ymax]
 227
 228 set title "Nombre d'inscrits de la promotion $promo."
 229
 230 plot "-" using 1:2 title 'inscrits' with lines;
 231 {$registered}e
 232 EOF
 233 EOF2;
 234         }
 235
 236         pl_cached_dynamic_content_headers("image/png");
 237         passthru($gnuplot);
 238         exit;
 239     }
 240
 241     function handler_promos($page, $required_promo = null)
 242     {
 243         $page->changeTpl('stats/nb_by_promo.tpl');
 244         $cycles = array('X' => 'Polytechniciens', 'M' => 'Masters', 'D' => 'Docteurs');
 245
 246         $res = XDB::iterRow('SELECT  pd.promo, COUNT(*)
 247                                FROM  accounts         AS a
 248                          INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 249                          INNER JOIN  profiles         AS p  ON (p.pid = ap.pid)
 250                          INNER JOIN  profile_display  AS pd ON (pd.pid = ap.pid)
 251                               WHERE  a.state = \'active\' AND p.deathdate IS NULL AND pd.promo != \'D (en cours)\'
 252                            GROUP BY  pd.promo
 253                            ORDER BY  pd.promo LIKE \'D%\', pd.promo LIKE \'M%\', pd.promo LIKE \'X%\', pd.promo');
 254
 255         $nbpromo = array();
 256         while (list($promo, $count) = $res->next()) {
 257             $prefix = substr($promo, 0, 4) . '-';
 258             $unit = substr($promo, -1);
 259             if(!isset($nbpromo[$cycles[$promo{0}]][$prefix])) {
 260                 $nbpromo[$cycles[$promo{0}]][$prefix] = array('', '', '', '', '', '', '', '', '', ''); // Empty array containing 10 cells.
 261             }
 262             $nbpromo[$cycles[$promo{0}]][$prefix][$unit] = array('promo' => $promo, 'nb' => $count);
 263         }
 264
 265         $count = XDB::fetchOneCell('SELECT  COUNT(*)
 266                                       FROM  accounts         AS a
 267                                 INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 268                                 INNER JOIN  profiles         AS p  ON (p.pid = ap.pid)
 269                                 INNER JOIN  profile_display  AS pd ON (pd.pid = ap.pid)
 270                                      WHERE  a.state = \'active\' AND p.deathdate IS NULL AND pd.promo = \'D (en cours)\'');
 271         $nbpromo[$cycles['D']]['D (en cours)'][0] = array('promo' => 'D (en cours)', 'nb' => $count);
 272
 273         $page->assign_by_ref('nbs', $nbpromo);
 274         $page->assign('promo', $required_promo);
 275     }
 276
 277     function handler_coupures($page, $cp_id = null)
 278     {
 279         $page->changeTpl('stats/coupure.tpl');
 280
 281         if (!is_null($cp_id)) {
 282             $res = XDB::query("SELECT  debut,
 283                                        TIME_FORMAT(duree,'%kh%i') AS duree,
 284                                        resume, description, services
 285                                  FROM  downtimes
 286                                 WHERE  id = {?}", $cp_id);
 287             $cp  = $res->fetchOneAssoc();
 288         }
 289
 290         if(@$cp) {
 291             $cp['lg_services'] = serv_to_str($cp['services']);
 292             $page->assign_by_ref('cp',$cp);
 293         } else {
 294             $beginning_date = date("Ymd", time() - 3600*24*21) . "000000";
 295             $sql = "SELECT  id, debut, resume, services
 296                       FROM  downtimes where debut > '$beginning_date' order by debut desc";
 297             $page->assign('coupures', XDB::iterator($sql));
 298             $res = XDB::iterator("SELECT  host, text
 299                                     FROM  mx_watch
 300                                    WHERE  state != 'ok'");
 301             $page->assign('mxs', $res);
 302         }
 303     }
 304 }
 305
 306 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 307 ?>