Merge branch 'xorg/maint' into xorg/master
[platal.git] / 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 ?>