root/trunk/wifidog-auth/wifidog/admin/stats_node.inc.php @ 726

Revision 726, 14.0 KB (checked in by aprilp, 8 years ago)

* New stats system with graphs
* Changed the UI a bit to reflect this change
* Updated stylesheet
  • Property svn:eol-style set to native
  • Property svn:executable set to *
  • Property svn:keywords set to Author Date Id Revision
RevLine 
[726]1<?php
2  /********************************************************************\
3   * This program is free software; you can redistribute it and/or    *
4   * modify it under the terms of the GNU General Public License as   *
5   * published by the Free Software Foundation; either version 2 of   *
6   * the License, or (at your option) any later version.              *
7   *                                                                  *
8   * This program is distributed in the hope that it will be useful,  *
9   * but WITHOUT ANY WARRANTY; without even the implied warranty of   *
10   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the    *
11   * GNU General Public License for more details.                     *
12   *                                                                  *
13   * You should have received a copy of the GNU General Public License*
14   * along with this program; if not, contact:                        *
15   *                                                                  *
16   * Free Software Foundation           Voice:  +1-617-542-5942       *
17   * 59 Temple Place - Suite 330        Fax:    +1-617-542-2652       *
18   * Boston, MA  02111-1307,  USA       gnu@gnu.org                   *
19   *                                                                  *
20   \********************************************************************/
21  /**@file stats_node.inc.php
22   * @author Copyright (C) 2005 Philippe April
23   */
24
25require_once 'Image/Graph.php';
26require_once 'Image/Canvas.php';
27
28$html .= "<fieldset class='pretty_fieldset'>";
29$html .= "<legend>Profile</legend>";
30$html .= "<table>";
31
32$html .= "<tr class='odd'>";
33$html .= "  <th>" . _("Name") . "</th>";
34$html .= "  <td>" . $nodeObject->getName() . "</td>";
35$html .= "</tr>";
36$html .= "<tr class='even'>";
37$html .= "  <th>" . _("Node ID") . "</th>";
38$html .= "  <td>" . $nodeObject->getId() . "</td>";
39$html .= "</tr>";
40$html .= "<tr class='odd'>";
41$html .= "  <th>" . _("Deployment Status") . "</th>";
42$html .= "  <td>" . $nodeObject->getDeploymentStatus() . "</td>";
43$html .= "</tr>";
44$html .= "<tr class='even'>";
45$html .= "  <th>" . _("Deployment date") . "</th>";
46$html .= "  <td>" . $nodeObject->getCreationDate() . "</td>";
47$html .= "</tr>";
48$html .= "<tr class='odd'>";
49$html .= "  <th>" . _("Description") . "</th>";
50$html .= "  <td>" . $nodeObject->getDescription() . "</td>";
51$html .= "</tr>";
52$html .= "<tr class='even'>";
53$html .= "  <th>" . _("Network") . "</th>";
54$html .= "  <td>" . $nodeObject->getNetwork()->getName() . "</td>";
55$html .= "</tr>";
56$html .= "<tr class='odd'>";
57$html .= "  <th>" . _("GIS Location") . "</th>";
58$html .= "  <td>";
59if ($nodeObject->getGisLocation()->getLatitude() && $nodeObject->getGisLocation()->getLongitude()) {
60    $html .= $nodeObject->getGisLocation()->getLatitude() . " " . $nodeObject->getGisLocation()->getLongitude();
61    $html .= " <input type='button' name='google_maps_geocode' value='" . _("Map") . "' onClick='window.open(\"hotspot_location_map.php?node_id={$node_id}\", \"hotspot_location\", \"toolbar=0,scrollbars=1,resizable=1,location=0,statusbar=0,menubar=0,width=600,height=600\");'>";
62} else {
63    $html .= _("NOT SET");
64}
65$html .= "  </td>";
66$html .= "</tr>";
67$html .= "<tr class='even'>";
68$html .= "  <th>" . _("Homepage") . "</th>";
69$html .= "  <td><a href='" . $nodeObject->getHomePageURL() . "'>" . $nodeObject->getHomePageURL() . "</a></td>";
70$html .= "</tr>";
71$html .= "<tr class='odd'>";
72$html .= "  <th>" . _("Address") . "</th>";
73$html .= "  <td>";
74$html .= trim($nodeObject->getCivicNumber() . " " . $nodeObject->getStreetName()) . "<br>";
75$html .= trim($nodeObject->getCity() . " " . $nodeObject->getProvince()) . "<br>";
76$html .= trim($nodeObject->getCountry() . " " . $nodeObject->getPostalCode());
77$html .= "</td>";
78$html .= "</tr>";
79$html .= "<tr class='even'>";
80$html .= "  <th>" . _("Telephone") . "</th>";
81$html .= "  <td>" . $nodeObject->getTelephone() . "</td>";
82$html .= "</tr>";
83$html .= "<tr class='odd'>";
84$html .= "  <th>" . _("Email") . "</th>";
85$html .= "  <td><a href='mailto:" . $nodeObject->getEmail() . "'>" . $nodeObject->getEmail() . "</a></td>";
86$html .= "</tr>";
87$html .= "<tr class='even'>";
88$html .= "  <th>" . _("Transit Info") . "</th>";
89$html .= "  <td>" . $nodeObject->getTransitInfo() . "</td>";
90$html .= "</tr>";
91$html .= "<tr class='odd'>";
92$html .= "  <th>" . _("IP Address") . "</th>";
93$html .= "  <td>" . $nodeObject->getLastHeartbeatIP() . "</td>";
94$html .= "</tr>";
95$html .= "</table>";
96$html .= "</fieldset>";
97
98$html .= "<fieldset class='pretty_fieldset'>";
99$html .= "<legend>Status</legend>";
100$html .= "<table>";
101
102$db->ExecSql("SELECT node_id, name, (NOW()-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((NOW()-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up, creation_date FROM nodes WHERE node_id = '{$node_id}'", $rows, false);
103
104$html .= ($rows[0]['is_up'] == 't') ? "<tr class='even'>" : "<tr class='red'>";
105$html .= "  <th>" . _("WifiDog status") . "</th>";
106$html .= "  <td>";
107$html .= ($rows[0]['is_up'] == 't') ? "UP" : "<span class='red'>DOWN</span>";
108$html .= "</td>";
109$html .= "<tr class='odd'>";
110$html .= "  <th>" . _("Last heartbeat") . "</th>";
111$html .= "  <td>" . seconds_in_words(time() - strtotime($nodeObject->getLastHeartbeatTimestamp())) . " ago</td>";
112$html .= "</tr>";
113$html .= "</tr>";
114$html .= "<tr class='even'>";
115$html .= "  <th>" . _("WifiDog version") . "</th>";
116$html .= "  <td>" . $nodeObject->getLastHeartbeatUserAgent() . "</td>";
117$html .= "</tr>";
118$html .= "<tr class='odd'>";
119$html .= "  <th>" . _("IP Address") . "</th>";
120$html .= "  <td>" . $nodeObject->getLastHeartbeatIP() . "</td>";
121$html .= "</tr>";
122$html .= "</table>";
123$html .= "</fieldset>";
124
125$html .= "<fieldset class='pretty_fieldset'>";
126$html .= "<legend>Statistics</legend>";
127$html .= "<table>";
128
129$db->ExecSql("SELECT round(CAST( (SELECT SUM(daily_connections) FROM (SELECT COUNT(DISTINCT user_id) AS daily_connections, date_trunc('day', timestamp_in) FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) GROUP BY date_trunc('day', timestamp_in)) AS daily_connections_table) / (EXTRACT(EPOCH FROM (NOW()-(SELECT timestamp_in FROM connections WHERE node_id='${node_id}' AND (incoming!=0 OR outgoing!=0) ORDER BY timestamp_in LIMIT 1)) )/(3600*24)) AS numeric),2) AS connections_per_day", $rows, false);
130$html .= "<tr class='even'>";
131$html .= "  <th>" . _("Average visits per day") . ":</th>";
132$html .= "  <td>" . $rows[0]['connections_per_day'] . "  </td>";
133$html .= "</tr>";
134
135$db->ExecSql("SELECT SUM(incoming) AS in, SUM(outgoing) AS out FROM connections WHERE node_id='{$node_id}' ${date_constraint}", $rows, false);
136$html .= "<tr class='odd'>";
137$html .= "  <th>" . _("Traffic") . ":</th>";
138$html .= "  <td>";
139$html .= _("Incoming") . ": " .  bytes_in_words($rows[0]['in']);
140$html .= "<br>";
141$html .= _("Outgoing") . ": " .  bytes_in_words($rows[0]['out']);
142$html .= "<br>";
143$html .= "(for the selected period)";
144$html .= "</td>";
145
146$html .= "</table>";
147$html .= "</fieldset>";
148
149$html .= "<fieldset class='pretty_fieldset'>";
150$html .= "<legend>" . _("Connections per hour of the day") . "</legend>";
151$html .= "<div><img src='graph_per_hour.php?node_id={$node_id}&date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}'></div>";
152$html .= "</fieldset>";
153
154$html .= "<fieldset class='pretty_fieldset'>";
155$html .= "<legend>" . _("Connections per week day") . "</legend>";
156$html .= "<div><img src='graph_per_weekday.php?node_id={$node_id}&date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}'></div>";
157$html .= "</fieldset>";
158
159$html .= "<fieldset class='pretty_fieldset'>";
160$html .= "<legend>" . _("Connections per month") . "</legend>";
161$html .= "<div><img src='graph_per_month.php?node_id={$node_id}&date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}'></div>";
162$html .= "</fieldset>";
163
164if (isset($_REQUEST['group_connections']) && $_REQUEST['group_connections'] == "group_connections_by_mac") {
165    $sql = "SELECT user_mac,COUNT(DISTINCT user_id) AS nb_users,COUNT(user_mac) AS nb_connections,MAX(timestamp_in) AS last_seen FROM connections WHERE node_id='{$node_id}' {$date_constraint} GROUP BY user_mac ORDER BY last_seen DESC";
166    $db->ExecSql($sql, $rows, false);
167
168    $number_of_macs = count($rows);
169
170    $html .= "<fieldset class='pretty_fieldset'>";
171    $html .= "<legend>Number of unique MACs: {$number_of_macs}</legend>";
172    $html .= "<table>";
173    $html .= "<thead>";
174    $html .= "<tr>";
175    $html .= "<th>MAC</th>";
176    $html .= "<th>Users count</th>";
177    $html .= "<th>Cx count</th>";
178    $html .= "<th>Last seen</th>";
179    $html .= "</tr>";
180    $html .= "</thead>";
181
182    foreach ($rows as $row) {
183        $html .= "<tr>\n";
184        $html .= "  <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_mac={$row['user_mac']}'>{$row['user_mac']}</a></td>\n";
185        $html .= "  <td>" . $row['nb_users'] . "</td>\n";
186        $html .= "  <td>" . $row['nb_connections'] . "</td>\n";
187        $html .= "  <td>" . strftime("%Y-%m-%d %H:%M:%S", strtotime($row['last_seen'])) . "</td>\n";
188        $html .= "</tr>\n";
189    }
190
191    $html .= "</table>";
192    $html .= "</fieldset>";
193
194} elseif (isset($_REQUEST['group_connections']) && $_REQUEST['group_connections'] == "group_connections_by_user") {
195    $sql = "select distinct(connections.user_id),count(distinct user_mac) as nb_mac,username,count(connections.user_id) as nb_cx,max(timestamp_in) as last_seen from connections,users where users.user_id=connections.user_id and node_id='{$node_id}' {$date_constraint} group by connections.user_id,username order by nb_cx desc,username";
196    $db->ExecSql($sql, $rows, false);
197
198    $number_of_usernames = count($rows);
199
200    $html .= "<fieldset class='pretty_fieldset'>";
201    $html .= "<legend>Number of unique Usernames: {$number_of_usernames}</legend>";
202    $html .= "<table>";
203    $html .= "<thead>";
204    $html .= "<tr>";
205    $html .= "<th>Username</th>";
206    $html .= "<th>MAC Count</th>";
207    $html .= "<th>Cx Count</th>";
208    $html .= "<th>Last seen</th>";
209    $html .= "</tr>";
210    $html .= "</thead>";
211
212    foreach ($rows as $row) {
213        $html .= "<tr>\n";
214        $html .= "  <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
215        $html .= "  <td>" . $row['nb_mac'] . "</td>\n";
216        $html .= "  <td>" . $row['nb_cx'] . "</td>\n";
217        $html .= "  <td>" . strftime("%Y-%m-%d %H:%M:%S", strtotime($row['last_seen'])) . "</td>\n";
218        $html .= "</tr>\n";
219    }
220
221    $html .= "</table>";
222    $html .= "</fieldset>";
223
224} else {
225    $sql = "select *,users.username from connections,users where users.user_id=connections.user_id and node_id='{$node_id}' {$date_constraint} order by timestamp_in desc";
226    $db->ExecSql($sql, $rows, false);
227
228    $number_of_connections = count($rows);
229
230    $html .= "<fieldset class='pretty_fieldset'>";
231    $html .= "<legend>Number of non-unique connections: {$number_of_connections}</legend>";
232    $html .= "<table>";
233    $html .= "<thead>";
234    $html .= "<tr>";
235    $html .= "<th>Username</th>";
236    $html .= "<th>MAC</th>";
237    $html .= "<th>Date</th>";
238    $html .= "<th>Time spent</th>";
239    $html .= "</tr>";
240    $html .= "</thead>";
241
242    $even = 0;
243    if ($rows) {
244        foreach ($rows as $row) {
245            if ($row['timestamp_in'])
246                $timestamp_in = strtotime($row['timestamp_in']);
247            else
248                $timestamp_in = -1;
249
250            if ($row['timestamp_out'])
251                $timestamp_out = strtotime($row['timestamp_out']);
252            else
253                $timestamp_out = -1;
254
255            if ($timestamp_in != -1 && $timestamp_out != -1) {
256                $html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
257                if ($even == 0)
258                    $even = 1;
259                else
260                    $even = 0;
261                $html .= "  <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
262                $html .= "  <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_mac={$row['user_mac']}'>{$row['user_mac']}</a></td>\n";
263                $html .= "  <td>" . utf8_encode(strftime("%c", strtotime($row['timestamp_in']))) . "</td>\n";
264                $html .= "  <td>";
265                if ($timestamp_in != -1 && $timestamp_out != -1) {
266                    $html .= seconds_in_words($timestamp_out - $timestamp_in);
267                }
268                $html .= "</td>\n";
269                $html .= "</tr>\n";
270            }
271        }
272    }
273
274    $html .= "</table>\n";
275    $html .= "</fieldset>\n";
276
277
278    /* Users who signed up here */
279    $sql = "select connections.user_id,users.username,users.reg_date FROM connections,nodes,users where timestamp_in IN (SELECT MIN(timestamp_in) as first_connection FROM connections GROUP BY user_id) ${date_constraint} AND users.user_id=connections.user_id AND connections.node_id='{$node_id}' AND nodes.node_id='{$node_id}' AND reg_date >= creation_date ORDER BY reg_date DESC";
280    $db->ExecSql($sql, $rows, false);
281
282    $html .= "<fieldset class='pretty_fieldset'>";
283    $html .= "<legend>" . _("Users who signed up here") . "</legend>";
284    $html .= "<table>";
285    $html .= "<thead>";
286    $html .= "<tr>";
287    $html .= "<th>" . _("Username") . "</th>";
288    $html .= "<th>" . _("Registration date") . "</th>";
289    $html .= "</tr>";
290    $html .= "</thead>";
291
292    $even = 0;
293    $total = 0;
294    if ($rows) {
295        foreach ($rows as $row) {
296            $html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
297            if ($even == 0)
298                $even = 1;
299            else
300                $even = 0;
301           
302            $total++;
303
304            $html .= "  <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
305            $html .= "  <td>" . utf8_encode(strftime("%c", strtotime($row['reg_date']))) . "</td>\n";
306            $html .= "</tr>\n";
307        }
308    }
309
310    $html .= "<tr>\n";
311    $html .= "  <th>" . _("Total") . ":</th>\n";
312    $html .= "  <th>" . $total . "</th>\n";
313    $html .= "</tr>\n";
314    $html .= "</table>\n";
315    $html .= "</fieldset>\n";
316}
317
318
319?>
Note: See TracBrowser for help on using the browser.