Initial commit

Change-Id: I6a4444e3c193dae437cd7929f4c39aba7b749efa
diff --git a/contrib/app_acct_tools/display_stats.php b/contrib/app_acct_tools/display_stats.php
new file mode 100644
index 0000000..31beb12
--- /dev/null
+++ b/contrib/app_acct_tools/display_stats.php
@@ -0,0 +1,269 @@
+<?php
+
+# The copyright of this file is the same as the freeDiameter project. Licence is BSD.
+
+#------------------------------------------------------------------------------------------
+# DATABASE:
+
+/* The Connection String used to access that database:
+  Example: "host=localhost dbname=app_acct user=freediameter password=foo" */
+$CONNSTR=""; 
+
+/* The name of the table containing the processed data (from process_records.php script) */
+$PROCESSED="processed";
+
+#------------------------------------------------------------------------------------------
+
+?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
+<html lang="en-US">
+<head>
+    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
+
+    <title>Accounting Data</title>
+    <meta name="description" content="This page gives you access to your personal accounting data.">
+    <meta name="keywords" content="accounting">
+
+    <style type="text/css">
+        body { text-align:center; }
+	table.colored { border-collapse:collapse; margin-left:auto; margin-right:auto; }
+	table.colored td, table.colored th { border:1px solid green; padding-left:.5em; padding-right:.5em;}
+	.colored th { background-color:green; color:white; }
+	hr { width:15px; margin-top:2em;margin-bottom:2em; }
+    </style>
+</head>
+
+<body >
+ <h1>Statistical accounting data</h1>
+  <form method="GET">
+  <table class="colored">
+    <tr>
+      <th colspan="3">
+        Filtering options
+      </th>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_starttime"<?php if (isset($_GET["c_starttime"])) echo " checked"; ?>></td>
+      <td>Limit to sessions starting from (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
+      <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>
+    </tr>
+    <tr>
+      <td><input type="checkbox" name="c_endtime"<?php if (isset($_GET["c_endtime"])) echo " checked"; ?>></td>
+      <td>Limit to sessions starting until (<a href="http://www.postgresql.org/docs/8.4/static/datatype-datetime.html">hint</a>):</td>
+      <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>
+    </tr>
+    <tr>
+      <th colspan="3">
+        Apply this filter: <input type="submit" name="Submit">
+      </th>
+    </tr>
+  </table>
+  </form>
+  <p>Note well: this page displays only data about <em>terminated</em> sessions.</p>
+<?php
+
+/* Connect to the database */
+$dbconn = pg_connect($CONNSTR)
+    or die('Could not connect: ' . pg_last_error() . "\n");
+    
+if ($_GET["c_starttime"] && $_GET["t_starttime"]) {
+	$START_TIME=$_GET["t_starttime"];
+}
+if ($_GET["c_endtime"] && $_GET["t_endtime"]) {
+	$END_TIME=$_GET["t_endtime"];
+}
+$sql_cond="";
+if ($START_TIME) {
+	$sql_cond = "sess_start >= '".pg_escape_string($START_TIME)."'";
+}
+if ($END_TIME) {
+	if ($sql_cond)
+		$sql_cond .= " AND ";
+	$sql_cond .= "sess_start =< '".pg_escape_string($END_TIME)."'";
+}
+
+
+/* Function to format download size (from php.net) */
+function human_readable( $size )
+{
+   $count = 0;
+   $format = array("B","KB","MB","GB","TB","PB","EB","ZB","YB");
+   while(($size/1024)>1 && $count<8)
+   {
+       $size=$size/1024;
+       $count++;
+   }
+   if( $size >= 100 ) $decimals = 0;
+   elseif ($size >= 10 ) $decimals = 1;
+   else  $decimals = 2;
+   $return = number_format($size,$decimals,'.',' ')." ".$format[$count];
+   return $return;
+}
+
+?>
+  
+  <hr />
+  
+<?php
+    
+/* First query: global data */
+$sql = 'SELECT SUM(downl_bytes) as total_down, SUM(upl_bytes) as total_up,';
+$sql .= ' to_char(min(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as first_sess,';
+$sql .= ' to_char(max(sess_start), \'YYYY-MM-DD&nbsp;HH24:MI:SS&nbsp;TZ\') as last_sess,';
+$sql .= ' count(distinct user_name) as nb_users,';
+$sql .= ' count(distinct nas_info) as nb_ap';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+if (pg_num_rows($result) == 0) {
+	echo "<p><em>No data is available in the selected period.</em></p>\n";
+	die("</body></html>\n");
+} 
+
+$data = pg_fetch_array($result, null, PGSQL_ASSOC);
+?>
+  <table class="colored">
+    <tr>
+      <th colspan="2">Total accounted data</th>
+    </tr>
+<?php
+echo "    <tr><td>First session started:</td><td>".$data["first_sess"]."</td></tr>\n";
+echo "    <tr><td>Last session started:</td><td>".$data["last_sess"]."</td></tr>\n";
+echo "    <tr><td>Total data downloaded:</td><td><strong>".human_readable($data["total_down"])."</strong></td></tr>\n";
+echo "    <tr><td>Total data uploaded:</td><td><strong>".human_readable($data["total_up"])."</strong></td></tr>\n";
+echo "    <tr><td>Number of users who connected at least once:</td><td>".$data["nb_users"]."</td></tr>\n";
+echo "    <tr><td>Number of access points involved:</td><td>".$data["nb_ap"]."</td></tr>\n";
+echo "  </tr>\n";
+echo " </table>\n";
+
+pg_free_result($result);
+
+?>
+  
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-5 <strong>downloading</strong> users</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(downl_bytes) as total_down, user_name';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY user_name';
+$sql .= ' ORDER BY total_down desc, user_name';
+$sql .= ' LIMIT 5';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
+	echo "      <td>".human_readable($data["total_down"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+  
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-5 <strong>uploading</strong> users</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(upl_bytes) as total_up, user_name';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY user_name';
+$sql .= ' ORDER BY total_up desc, user_name';
+$sql .= ' LIMIT 5';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["user_name"]))."</td>\n";
+	echo "      <td>".human_readable($data["total_up"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-8 access devices (by number of users)</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT count(distinct user_name) as unc, nas_info';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY nas_info';
+$sql .= ' ORDER BY unc desc, nas_info';
+$sql .= ' LIMIT 8';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
+	echo "      <td>".$data["unc"]."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+  
+  <table class="colored">
+    <tr>
+      <th colspan="2">Top-8 access devices (by traffic: up+down)</th>
+    </tr>
+<?php
+    
+/* Now, get the top 5 downloaders */
+$sql = 'SELECT SUM(upl_bytes) + SUM(downl_bytes) as traffic, nas_info';
+$sql .= ' FROM "'.$PROCESSED.'"';
+if ($sql_cond)
+	$sql .= ' WHERE '.$sql_cond;
+$sql .= ' GROUP BY nas_info';
+$sql .= ' ORDER BY traffic desc, nas_info';
+$sql .= ' LIMIT 8';
+
+/* Execute the query */
+$result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error() . "\n");
+while ($data = pg_fetch_array($result, null, PGSQL_ASSOC)) {
+	echo "    <tr>\n";
+	echo "      <td>".htmlentities(pg_unescape_bytea($data["nas_info"]))."</td>\n";
+	echo "      <td>".human_readable($data["traffic"])."</td>\n";
+	echo "    </tr>\n";
+}
+pg_free_result($result);
+?>
+  </table>
+
+  <hr />
+<?php
+/* Closing connection */
+pg_close($dbconn);
+?>
+<p><small>Data generated by scripts from the <a href="http://www.freediameter.net">freeDiameter</a> project.</small></p>
+</body>
+</html>