Gitiles
Code Review Sign In
gerrit.opencord.org / freeDiameter-old / 13d9601bf947af3286764506368fc3d04672bfde / . / contrib / app_acct_tools / display_stats.php
blob: 31beb1257862879e19cb12c5f16aa5c908b56aa1 [file] [log] [blame]
Brian Waters13d96012017-12-08 16:53:31 -0600[diff] [blame^]1<?php
2
3# The copyright of this file is the same as the freeDiameter project. Licence is BSD.
4
5#------------------------------------------------------------------------------------------
6# DATABASE:
7
8/* The Connection String used to access that database:
9 Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
10$CONNSTR="";
11
12/* The name of the table containing the processed data (from process_records.php script) */
13$PROCESSED="processed";
14
15#------------------------------------------------------------------------------------------
16
17?>
18<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
19<html lang="en-US">
20<head>
21 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
22
23 <title>Accounting Data</title>
24 <meta name="description" content="This page gives you access to your personal accounting data.">
25 <meta name="keywords" content="accounting">
26
27 <style type="text/css">
28 body { text-align:center; }
29 table.colored { border-collapse:collapse; margin-left:auto; margin-right:auto; }
30 table.colored td, table.colored th { border:1px solid green; padding-left:.5em; padding-right:.5em;}
31 .colored th { background-color:green; color:white; }
32 hr { width:15px; margin-top:2em;margin-bottom:2em; }
33 </style>
34</head>
35
36<body >
37 <h1>Statistical accounting data</h1>
38 <form method="GET">
39 <table class="colored">
40 <tr>
41 <th colspan="3">
42 Filtering options
43 </th>
44 </tr>
45 <tr>
46 <td><input type="checkbox" name="c_starttime"<?php if (isset($_GET["c_starttime"])) echo " checked"; ?>></td>
47 <td>Limit to sessions starting from (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
48 <td><input type="text" title="YYYY-MM-DD HH:MM:SS" name="t_starttime"<?php if (isset($_GET["t_starttime"])) echo 'value="'.$_GET["t_starttime"].'"'; ?>></td>
49 </tr>
50 <tr>
51 <td><input type="checkbox" name="c_endtime"<?php if (isset($_GET["c_endtime"])) echo " checked"; ?>></td>
52 <td>Limit to sessions starting until (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
53 <td><input type="text" title="YYYY-MM-DD HH:MM:SS" name="t_endtime"<?php if (isset($_GET["t_endtime"])) echo 'value="'.$_GET["t_endtime"].'"'; ?>></td>
54 </tr>
55 <tr>
56 <th colspan="3">
57 Apply this filter: <input type="submit" name="Submit">
58 </th>
59 </tr>
60 </table>
61 </form>
62 <p>Note well: this page displays only data about <em>terminated</em> sessions.</p>
63<?php
64
65/* Connect to the database */
66$dbconn = pg_connect($CONNSTR)
67 or die('Could not connect: ' . pg_last_error() . "\n");
68
69if ($_GET["c_starttime"] && $_GET["t_starttime"]) {
70 $START_TIME=$_GET["t_starttime"];
71}
72if ($_GET["c_endtime"] && $_GET["t_endtime"]) {
73 $END_TIME=$_GET["t_endtime"];
74}
75$sql_cond="";
76if ($START_TIME) {
77 $sql_cond = "sess_start >= '".pg_escape_string($START_TIME)."'";
78}
79if ($END_TIME) {
80 if ($sql_cond)
81 $sql_cond .= " AND ";
82 $sql_cond .= "sess_start =< '".pg_escape_string($END_TIME)."'";
83}
84
85
86/* Function to format download size (from php.net) */
87function human_readable( $size )
88{
89 $count = 0;
90 $format = array("B","KB","MB","GB","TB","PB","EB","ZB","YB");
91 while(($size/1024)>1 && $count<8)
92 {
93 $size=$size/1024;
94 $count++;
95 }
96 if( $size >= 100 ) $decimals = 0;
97 elseif ($size >= 10 ) $decimals = 1;
98 else $decimals = 2;
99 $return = number_format($size,$decimals,'.',' ')." ".$format[$count];
100 return $return;
101}
102
103?>
104
105 <hr />
106
107<?php
108
109/* First query: global data */
110$sql = 'SELECT SUM(downl_bytes) as total_down, SUM(upl_bytes) as total_up,';
111$sql .= ' to_char(min(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as first_sess,';
112$sql .= ' to_char(max(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as last_sess,';
113$sql .= ' count(distinct user_name) as nb_users,';
114$sql .= ' count(distinct nas_info) as nb_ap';
115$sql .= ' FROM "'.$PROCESSED.'"';
116if ($sql_cond)
117 $sql .= ' WHERE '.$sql_cond;
118
119/* Execute the query */
120$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
121if (pg_num_rows($result) == 0) {
122 echo "<p><em>No data is available in the selected period.</em></p>\n";
123 die("</body></html>\n");
124}
125
126$data = pg_fetch_array($result, null, PGSQL_ASSOC);
127?>
128 <table class="colored">
129 <tr>
130 <th colspan="2">Total accounted data</th>
131 </tr>
132<?php
133echo " <tr><td>First session started:</td><td>".$data["first_sess"]."</td></tr>\n";
134echo " <tr><td>Last session started:</td><td>".$data["last_sess"]."</td></tr>\n";
135echo " <tr><td>Total data downloaded:</td><td><strong>".human_readable($data["total_down"])."</strong></td></tr>\n";
136echo " <tr><td>Total data uploaded:</td><td><strong>".human_readable($data["total_up"])."</strong></td></tr>\n";
137echo " <tr><td>Number of users who connected at least once:</td><td>".$data["nb_users"]."</td></tr>\n";
138echo " <tr><td>Number of access points involved:</td><td>".$data["nb_ap"]."</td></tr>\n";
139echo " </tr>\n";
140echo " </table>\n";
141
142pg_free_result($result);
143
144?>
145
146 <hr />
147
148 <table class="colored">
149 <tr>
150 <th colspan="2">Top-5 <strong>downloading</strong> users</th>
151 </tr>
152<?php
153
154/* Now, get the top 5 downloaders */
155$sql = 'SELECT SUM(downl_bytes) as total_down, user_name';
156$sql .= ' FROM "'.$PROCESSED.'"';
157if ($sql_cond)
158 $sql .= ' WHERE '.$sql_cond;
159$sql .= ' GROUP BY user_name';
160$sql .= ' ORDER BY total_down desc, user_name';
161$sql .= ' LIMIT 5';
162
163/* Execute the query */
164$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
165while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
166 echo " <tr>\n";
167 echo " <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
168 echo " <td>".human_readable($data["total_down"])."</td>\n";
169 echo " </tr>\n";
170}
171pg_free_result($result);
172?>
173 </table>
174
175 <hr />
176
177 <table class="colored">
178 <tr>
179 <th colspan="2">Top-5 <strong>uploading</strong> users</th>
180 </tr>
181<?php
182
183/* Now, get the top 5 downloaders */
184$sql = 'SELECT SUM(upl_bytes) as total_up, user_name';
185$sql .= ' FROM "'.$PROCESSED.'"';
186if ($sql_cond)
187 $sql .= ' WHERE '.$sql_cond;
188$sql .= ' GROUP BY user_name';
189$sql .= ' ORDER BY total_up desc, user_name';
190$sql .= ' LIMIT 5';
191
192/* Execute the query */
193$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
194while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
195 echo " <tr>\n";
196 echo " <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
197 echo " <td>".human_readable($data["total_up"])."</td>\n";
198 echo " </tr>\n";
199}
200pg_free_result($result);
201?>
202 </table>
203
204 <hr />
205
206 <table class="colored">
207 <tr>
208 <th colspan="2">Top-8 access devices (by number of users)</th>
209 </tr>
210<?php
211
212/* Now, get the top 5 downloaders */
213$sql = 'SELECT count(distinct user_name) as unc, nas_info';
214$sql .= ' FROM "'.$PROCESSED.'"';
215if ($sql_cond)
216 $sql .= ' WHERE '.$sql_cond;
217$sql .= ' GROUP BY nas_info';
218$sql .= ' ORDER BY unc desc, nas_info';
219$sql .= ' LIMIT 8';
220
221/* Execute the query */
222$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
223while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
224 echo " <tr>\n";
225 echo " <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
226 echo " <td>".$data["unc"]."</td>\n";
227 echo " </tr>\n";
228}
229pg_free_result($result);
230?>
231 </table>
232
233 <hr />
234
235 <table class="colored">
236 <tr>
237 <th colspan="2">Top-8 access devices (by traffic: up+down)</th>
238 </tr>
239<?php
240
241/* Now, get the top 5 downloaders */
242$sql = 'SELECT SUM(upl_bytes) + SUM(downl_bytes) as traffic, nas_info';
243$sql .= ' FROM "'.$PROCESSED.'"';
244if ($sql_cond)
245 $sql .= ' WHERE '.$sql_cond;
246$sql .= ' GROUP BY nas_info';
247$sql .= ' ORDER BY traffic desc, nas_info';
248$sql .= ' LIMIT 8';
249
250/* Execute the query */
251$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
252while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
253 echo " <tr>\n";
254 echo " <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
255 echo " <td>".human_readable($data["traffic"])."</td>\n";
256 echo " </tr>\n";
257}
258pg_free_result($result);
259?>
260 </table>
261
262 <hr />
263<?php
264/* Closing connection */
265pg_close($dbconn);
266?>
267<p><small>Data generated by scripts from the <a href="http://www.freediameter.net">freeDiameter</a> project.</small></p>
268</body>
269</html>