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),
  40             'stats/evolution'       => $this->make_hook('evolution', AUTH_COOKIE),
  41             'stats/graph'           => $this->make_hook('graph',     AUTH_COOKIE),
  42             'stats/graph/evolution' => $this->make_hook('graph_evo', AUTH_COOKIE),
  43             'stats/promos'          => $this->make_hook('promos',    AUTH_COOKIE),
  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 "%m/%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
 161 plot "-" using 1:2 title 'inscrits' with boxes;
 162 {$registered}
 163 EOF
 164 EOF2;
 165
 166         } else {
 167             $day_length = 24 * 3600;
 168             $days = 365;
 169
 170             $res = XDB::query("SELECT  MIN(TO_DAYS(a.registration_date) - TO_DAYS(NOW()))
 171                                  FROM  accounts         AS a
 172                            INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
 173                            INNER JOIN  profile_display  AS pd ON (ap.pid = pd.pid)
 174                                 WHERE  pd.promo = {?} AND a.state = 'active'",
 175                               $promo);
 176             $days = -$res->fetchOneCell();
 177
 178             // Retrieve the registration count per days during the given date range.
 179             $res = XDB::iterRow("SELECT  IF(a.registration_date > DATE_SUB(NOW(), INTERVAL {?} DAY),
 180                                             TO_DAYS(a.registration_date) - TO_DAYS(NOW()),
 181                                             - {?}) AS day,
 182                                          COUNT(a.uid) AS nb
 183                                    FROM  accounts         AS a
 184                              INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET('owner', ap.perms))
 185                              INNER JOIN  profile_display  AS pd ON (ap.pid = pd.pid)
 186                                   WHERE  pd.promo = {?} AND a.state = 'active'
 187                                GROUP BY  day",
 188                                 (int)$days, 1 + (int)$days, $promo);
 189
 190             // The first line contains the registration count before starting date (D - $days).
 191             list(, $init_nb) = $res->next();
 192             $total = $init_nb;
 193             $registered = '';
 194
 195             list($num_day, $nb) = $res->next();
 196
 197             for ($i = -$days; $i <= 0; ++$i) {
 198                 if ($num_day < $i) {
 199                     if(!list($num_day, $nb) = $res->next()) {
 200                         $num_day = 0;
 201                         $nb = 0;
 202                     }
 203                 }
 204                 if ($num_day == $i) {
 205                     $total += $nb;
 206                 }
 207                 $registered .= date('d/m/y', $i * $day_length + time()) . ' ' . $total . "\n";
 208             }
 209
 210             // Generate drawing thanks to Gnuplot.
 211             $delt = ($total - $init_nb) / 10;
 212             $delt += ($delt < 1);
 213             $ymin = round($init_nb - $delt, 0);
 214             $ymax = round($total   + $delt, 0);
 215
 216             $gnuplot = <<<EOF2
 217 gnuplot <<EOF
 218
 219 set term png small color
 220 set size 640/480
 221 set xdata time
 222 set timefmt "%d/%m/%y"
 223
 224 set format x "%m/%y"
 225 set yr [$ymin:$ymax]
 226
 227 set title "Nombre d'inscrits de la promotion $promo."
 228
 229 plot "-" using 1:2 title 'inscrits' with lines;
 230 {$registered}e
 231 EOF
 232 EOF2;
 233         }
 234
 235         pl_cached_dynamic_content_headers("image/png");
 236         passthru($gnuplot);
 237         exit;
 238     }
 239
 240     function handler_promos($page, $required_promo = null)
 241     {
 242         $page->changeTpl('stats/nb_by_promo.tpl');
 243
 244         $res = XDB::iterRow('SELECT  pd.promo, COUNT(*)
 245                                FROM  accounts         AS a
 246                          INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 247                          INNER JOIN  profiles         AS p  ON (p.pid = ap.pid)
 248                          INNER JOIN  profile_display  AS pd ON (pd.pid = ap.pid)
 249                               WHERE  a.state = \'active\' AND p.deathdate IS NULL AND pd.promo != \'D (en cours)\'
 250                            GROUP BY  pd.promo
 251                            ORDER BY  pd.promo LIKE \'D%\', pd.promo LIKE \'M%\', pd.promo LIKE \'X%\', pd.promo');
 252
 253         $nbpromo = array();
 254         while (list($promo, $count) = $res->next()) {
 255             $prefix = substr($promo, 0, 4) . '-';
 256             $unit = substr($promo, -1);
 257             if(!isset($nbpromo[$prefix])) {
 258                 $nbpromo[$prefix] = array('', '', '', '', '', '', '', '', '', ''); // Empty array containing 10 cells.
 259             }
 260             $nbpromo[$prefix][$unit] = array('promo' => $promo, 'nb' => $count);
 261         }
 262
 263         $count = XDB::fetchOneCell('SELECT  COUNT(*)
 264                                       FROM  accounts         AS a
 265                                 INNER JOIN  account_profiles AS ap ON (ap.uid = a.uid AND FIND_IN_SET(\'owner\', ap.perms))
 266                                 INNER JOIN  profiles         AS p  ON (p.pid = ap.pid)
 267                                 INNER JOIN  profile_display  AS pd ON (pd.pid = ap.pid)
 268                                      WHERE  a.state = \'active\' AND p.deathdate IS NULL AND pd.promo = \'D (en cours)\'');
 269         $nbpromo['D (en cours)'][0] = array('promo' => 'D (en cours)', 'nb' => $count);
 270
 271         $page->assign_by_ref('nbs', $nbpromo);
 272         $page->assign('promo', $required_promo);
 273     }
 274
 275     function handler_coupures($page, $cp_id = null)
 276     {
 277         $page->changeTpl('stats/coupure.tpl');
 278
 279         if (!is_null($cp_id)) {
 280             $res = XDB::query("SELECT  debut,
 281                                        TIME_FORMAT(duree,'%kh%i') AS duree,
 282                                        resume, description, services
 283                                  FROM  downtimes
 284                                 WHERE  id = {?}", $cp_id);
 285             $cp  = $res->fetchOneAssoc();
 286         }
 287
 288         if(@$cp) {
 289             $cp['lg_services'] = serv_to_str($cp['services']);
 290             $page->assign_by_ref('cp',$cp);
 291         } else {
 292             $beginning_date = date("Ymd", time() - 3600*24*21) . "000000";
 293             $sql = "SELECT  id, debut, resume, services
 294                       FROM  downtimes where debut > '$beginning_date' order by debut desc";
 295             $page->assign('coupures', XDB::iterator($sql));
 296             $res = XDB::iterator("SELECT  host, text
 297                                     FROM  mx_watch
 298                                    WHERE  state != 'ok'");
 299             $page->assign('mxs', $res);
 300         }
 301     }
 302 }
 303
 304 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
 305 ?>