/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$graph = StatisticGraph::getObject('ConnectionsPerHour');
$html .= $graph->getReportUI($this->stats);
$graph = StatisticGraph::getObject('VisitsPerWeekday');
$html .= $graph->getReportUI($this->stats);
$graph = StatisticGraph::getObject('VisitsPerMonth');
$html .= $graph->getReportUI($this->stats);
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery(" connections.{$distinguish_users_by}, SUM(incoming+outgoing) AS total, SUM(incoming) AS total_incoming, SUM(outgoing) AS total_outgoing ", false);
$sql = "{$candidate_connections_sql} GROUP BY connections.{$distinguish_users_by} ORDER BY total DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $frequent_users_stats, false);
if ($frequent_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Incoming") . "</th>";
$html .= " <th>" . _("Outgoing") . "</th>";
$html .= " <th>" . _("Total") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($frequent_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
$html .= " <td>" . Utils::convertBytesToWords($row['total_incoming']) . "</td>";
$html .= " <td>" . Utils::convertBytesToWords($row['total_outgoing']) . "</td>";
$html .= " <td>" . Utils::convertBytesToWords($row['total']) . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* Monthly registration graph */
$graph = StatisticGraph::getObject('RegistrationsPerMonth');
$html .= $graph->getReportUI($this->stats);
/* End Monthly registration graph */
/* Cumulative registration graph */
$graph = StatisticGraph::getObject('RegistrationsCumulative');
$html .= $graph->getReportUI($this->stats);
/* End cumulative registration graph */
/* First connection per node */
$html .= "<fieldset>";
$html .= "<legend>" . _("First connection per node") . "</legend>";
$node_usage_stats = null;
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
/* The following query will retreive the list of the REAL first connection of each user, no matter where or when.*/
$sql_real_first_connections = $this->stats->getSqlRealFirstConnectionsQuery('connections.conn_id', false);
//$db->execSql($sql_real_first_connections, $tmp, true);
$real_first_connections_table_name = "real_first_conn_table_name_" . session_id();
$real_first_connections_table_sql = "CREATE TABLE {$real_first_connections_table_name} AS ({$sql_real_first_connections});\n";
//$real_first_connections_table_sql .= "CREATE INDEX {$real_first_connections_table_name}_idx ON $real_first_connections_table_name (conn_id); \n";
$db->execSqlUpdate($real_first_connections_table_sql, false);
/* Now retrieves the oldest connection matching the report restriction, and only keep it if it's really the user's first connection */
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT ON(connections.{$distinguish_users_by}) connections.{$distinguish_users_by}, conn_id, connections.node_id, nodes.name,timestamp_in ");
//$db->execSql($candidate_connections_sql, $tmp, true);
$first_connection_table_sql = "{$candidate_connections_sql} ORDER BY connections.{$distinguish_users_by}, timestamp_in\n";
//$db->execSql($first_connection_table_sql, $node_usage_stats, true);
$first_connection_table_name = "first_connection_table_name_" . session_id();
$registration_node_table_sql = "CREATE TEMP TABLE {$first_connection_table_name} AS ({$first_connection_table_sql});\n \n";
//$registration_node_table_sql .= "CREATE INDEX {$first_connection_table_name}_idx ON $first_connection_table_name (node_id)";
$db->execSqlUpdate($registration_node_table_sql, false);
$registration_node_table_sql = "SELECT COUNT ({$first_connection_table_name}.{$distinguish_users_by}) AS total_first_connections, node_id, name FROM {$first_connection_table_name} JOIN {$real_first_connections_table_name} ON ({$first_connection_table_name}.conn_id={$real_first_connections_table_name}.conn_id) GROUP BY node_id, name ORDER BY total_first_connections DESC;";
$db->execSql($registration_node_table_sql, $node_usage_stats, false);
$registration_node_table_sql = "DROP TABLE {$first_connection_table_name};";
$db->execSqlUpdate($registration_node_table_sql, false);
$real_first_connections_table_sql = "DROP TABLE {$real_first_connections_table_name};";
$db->execSqlUpdate($real_first_connections_table_sql, false);
if ($node_usage_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th>" . _("Node") . "</th>";
$html .= " <th>" . _("# of new user first connection") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($node_usage_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['name']}</td>\n";
$html .= " <td>" . $row['total_first_connections'] . "</td>";
$html .= "</tr>";
$total += $row['total_first_connections'];
}
$html .= "<tfoot>";
$html .= "<tr>";
$html .= " <th>" . _("Total") . ":</th>";
$html .= " <th>" . $total . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <td colspan=2>" . _("Note: This is actually a list of how many new user's first connection occured at each hotspot, taking report restrictions into account. It includes non-validated users who successfully connected.") . "</td>";
$html .= "</tr>";
$html .= "</tfoot>";
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
/* End first connection per node */
return parent::getReportUI($html);
}
/** Constructor
* @param $statistics_object Mandatory to give the report it's context */
protected function __construct(Statistics $statistics_object)
{
parent::__construct($statistics_object);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$selected_network = $this->stats->getSelectedNetworks();
if (count($selected_network) == 0) {
$html .= _("Sorry, this report requires you to select individual networks");
} else {
//pretty_print_r($this->stats->getSelectedNodes ());
foreach ($selected_network as $network_id => $networkObject) {
$html .= "<fieldset>";
$html .= "<legend>" . $networkObject->getName() . "</legend>";
$html .= "<table>";
$html .= "<tr>";
$html .= " <th>" . _("Name") . "</th>";
$html .= " <td>" . $networkObject->getName() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Creation date") . "</th>";
$html .= " <td>" . $networkObject->getCreationDate() . "</td>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Homepage") . "</th>";
$html .= " <td>" . $networkObject->getWebSiteURL() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Tech support email") . "</th>";
$html .= " <td>" . $networkObject->getTechSupportEmail() . "</td>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Validation grace time") . "</th>";
$html .= " <td>" . Utils::convertSecondsToWords($networkObject->getValidationGraceTime()) . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Validation email") . "</th>";
$html .= " <td>" . $networkObject->getValidationEmailFromAddress() . "</td>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Allows multiple login") . "?</th>";
$html .= " <td>" . ($networkObject->getMultipleLoginAllowed() ? 'yes' : 'no') . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Splash only nodes allowed") . "?</th>";
$html .= " <td>" . ($networkObject->getSplashOnlyNodesAllowed() ? 'yes' : 'no') . "</td>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Custom portal redirect nodes allowed") . "?</th>";
$html .= " <td>" . ($networkObject->getCustomPortalRedirectAllowed() ? 'yes' : 'no') . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Number of users") . ":</th>";
$html .= " <td>" . $networkObject->getNumUsers() . "</td>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Number of validated users") . ":</th>";
$html .= " <td>" . $networkObject->getNumValidUsers() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Number of users currently online") . ":</th>";
$html .= " <td>" . $networkObject->getNumOnlineUsers() . "</td>";
$html .= "</tr>";
$html .= "</table>";
$html .= "</fieldset>";
}
//End foreach
}
//End else
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("COUNT(DISTINCT connections.node_id) AS num_hotspots_visited, {$distinguish_users_by}");
$sql = "{$candidate_connections_sql} GROUP BY {$distinguish_users_by} ORDER BY num_hotspots_visited DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $mobile_users_stats, false);
if ($mobile_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Nodes visited") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($mobile_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
//$html .= " <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
$html .= " <td>" . $row['num_hotspots_visited'] . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$node_usage_stats = null;
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT connections.{$distinguish_users_by}, connections.node_id, nodes.name, date_trunc('day', timestamp_in) as rounded_date");
//$db->execSql($candidate_connections_sql, $tmp, true);
$daily_visit_table_name = "daily_visit_table_name_" . session_id();
$daily_visit_table_sql = "CREATE TEMP TABLE {$daily_visit_table_name} AS ({$candidate_connections_sql});\n \n";
$daily_visit_table_sql .= "CREATE INDEX {$daily_visit_table_name}_idx ON {$daily_visit_table_name} (node_id)";
$db->execSqlUpdate($daily_visit_table_sql, false);
$daily_visit_table_sql = "SELECT COUNT ({$distinguish_users_by}) AS total_visits, node_id, name FROM {$daily_visit_table_name} GROUP BY node_id, name ORDER BY total_visits DESC;";
$db->execSql($daily_visit_table_sql, $node_usage_stats, false);
$daily_visit_table_sql = "DROP TABLE {$daily_visit_table_name};";
$db->execSqlUpdate($daily_visit_table_sql, false);
// $candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("nodes.name,connections.node_id,COUNT(connections.node_id) AS connections ");
// $sql = "$candidate_connections_sql GROUP BY connections.node_id,nodes.name ORDER BY connections DESC";
// $db->execSql($sql, $node_usage_stats, false);
if ($node_usage_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th>" . _("Node") . "</th>";
$html .= " <th>" . _("Visits") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($node_usage_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['name']}</td>\n";
$html .= " <td>" . $row['total_visits'] . "</td>";
$html .= "</tr>";
$total += $row['total_visits'];
}
$html .= "<tfoot>";
$html .= "<tr>";
$html .= " <th>" . _("Total") . ":</th>";
$html .= " <th>" . $total . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <td colspan=2>" . _("Note: A visit is like counting connections, but only counting one connection per day for each user at a single node") . ":</td>";
$html .= "</tr>";
$html .= "</tfoot>";
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* First connection per node */
$html .= "<fieldset>";
$html .= "<legend>" . _("User activity") . "</legend>";
$node_usage_stats = null;
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
/* Now retrieves the oldest connection matching the report restriction, and only keep it if it's really the user's first connection */
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT ON(connections.{$distinguish_users_by}) connections.{$distinguish_users_by}, timestamp_in, (NOW() - interval '1 month') ", true);
//$db->execSql($candidate_connections_sql, $tmp, true);
//Format is $breakdown_col_array['column_alias']=array("Column label", 'interval is SQL format');
$breakdown_col_array['last_day'] = array(_("Last day"), '1 day');
$breakdown_col_array['last_week'] = array(_("Last week"), '1 week');
$breakdown_col_array['last_month'] = array(_("Last month"), '1 month');
$breakdown_col_array['last_3_month'] = array(_("Last 3 month"), '3 month');
$breakdown_col_array['last_6_months'] = array(_("Last 6 months"), '6 months');
$breakdown_col_array['last_year'] = array(_("Last year"), '1 year');
$breakdown_col_array['forever'] = array(_("Ever"), '2000 year');
$first = true;
$col_sql = null;
foreach ($breakdown_col_array as $col_name => $col_info) {
$first ? $first = false : ($col_sql .= ", ");
$col_sql .= "COUNT(CASE WHEN (timestamp_in > (NOW() - interval '{$col_info[1]}')) THEN TRUE END) AS {$col_name} \n";
}
$network_constraint = $this->stats->getSqlNetworkConstraint('networks.network_id');
$user_constraint = $this->stats->getSqlUserConstraint();
/* For validated users */
$last_connection_table_sql = "{$candidate_connections_sql} AND users.account_status = " . ACCOUNT_STATUS_ALLOWED . " GROUP BY connections.{$distinguish_users_by}, timestamp_in ORDER BY connections.{$distinguish_users_by}, timestamp_in DESC \n";
//$db->execSql($last_connection_table_sql, $node_usage_stats, false);
$breakdown_sql = "SELECT \n {$col_sql} FROM ({$last_connection_table_sql}) AS last_connection_table\n";
$db->execSqlUniqueRes($breakdown_sql, $validated_user_breakdown_row, false);
$num_validated_users_sql = "SELECT COUNT(users.user_id) \n";
$num_validated_users_sql .= "FROM users JOIN networks on (users.account_origin = networks.network_id) \n";
$num_validated_users_sql .= "WHERE 1=1 {$network_constraint} {$user_constraint} AND users.account_status = " . ACCOUNT_STATUS_ALLOWED;
$db->execSqlUniqueRes($num_validated_users_sql, $num_validated_users_row, false);
/* For non-validated users */
$last_connection_table_sql = "{$candidate_connections_sql} AND users.account_status != " . ACCOUNT_STATUS_ALLOWED . " GROUP BY connections.{$distinguish_users_by}, timestamp_in ORDER BY connections.{$distinguish_users_by}, timestamp_in DESC \n";
//$db->execSql($last_connection_table_sql, $node_usage_stats, false);
$breakdown_sql = "SELECT \n {$col_sql} FROM ({$last_connection_table_sql}) AS last_connection_table\n";
$db->execSqlUniqueRes($breakdown_sql, $unvalidated_user_breakdown_row, false);
$num_unvalidated_users_sql = "SELECT COUNT(users.user_id) \n";
$num_unvalidated_users_sql .= "FROM users JOIN networks on (users.account_origin = networks.network_id) \n";
$num_unvalidated_users_sql .= "WHERE 1=1 {$network_constraint} {$user_constraint} AND users.account_status != " . ACCOUNT_STATUS_ALLOWED;
$db->execSqlUniqueRes($num_unvalidated_users_sql, $num_unvalidated_users_row, false);
if ($validated_user_breakdown_row) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th colspan=2>" . sprintf(_("Activity report for the %d validated users"), $num_validated_users_row['count']) . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Period") . "</th>";
$html .= " <th>" . _("# of users who used the network") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($breakdown_col_array as $col_name => $col_info) {
$first ? $first = false : ($col_sql .= ", ");
$col_sql .= "COUNT(CASE WHEN (timestamp_in > (NOW() - interval '{}')) THEN TRUE END) AS {$col_name} \n";
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$col_info[0]}</td>\n";
$html .= " <td>" . $validated_user_breakdown_row[$col_name] . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
}
if ($unvalidated_user_breakdown_row) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th colspan=2>" . sprintf(_("Activity report for the %d non-validated users"), $num_unvalidated_users_row['count']) . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <th>" . _("Period") . "</th>";
$html .= " <th>" . _("# of users who used the network") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($breakdown_col_array as $col_name => $col_info) {
$first ? $first = false : ($col_sql .= ", ");
$col_sql .= "COUNT(CASE WHEN (timestamp_in > (NOW() - interval '{}')) THEN TRUE END) AS {$col_name} \n";
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$col_info[0]}</td>\n";
$html .= " <td>" . $unvalidated_user_breakdown_row[$col_name] . "</td>";
$html .= "</tr>";
}
$html .= "<tfoot>";
$html .= "<tr>";
$html .= "</tr>";
$html .= "</tfoot>";
$html .= "</table>";
}
$html .= "<p class='warningmsg'>" . _("warning: This report does not count connections at Splash-Only nodes") . "</p>";
$html .= "</fieldset>";
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
global $account_status_to_text;
global $token_to_text;
// Init values
$html = "";
$_date_from = "";
$_date_to = "";
// Process input
if (isset($_REQUEST['date_from'])) {
$_date_from = $_REQUEST['date_from'];
}
if (isset($_REQUEST['date_to'])) {
$_date_to = $_REQUEST['date_to'];
}
$selected_users = $this->stats->getSelectedUsers();
if ($selected_users) {
foreach ($selected_users as $user_id => $userObject) {
if ($userObject) {
$userinfo = null;
$user_id = $db->escapeString($user_id);
$sql = "SELECT * FROM users WHERE user_id='{$user_id}'";
$db->execSqlUniqueRes($sql, $userinfo, false);
if ($userinfo == null) {
throw new Exception(sprintf(_("User id: %s could not be found in the database"), $user_id));
}
$userinfo['account_status_description'] = $account_status_to_text[$userinfo['account_status']];
$html .= "<fieldset>\n";
$html .= "<legend>" . _("Profile") . "</legend>\n";
$html .= "<table>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Username") . ":</th>\n";
$html .= " <td>" . $userinfo['username'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Email") . ":</th>\n";
$html .= " <td>" . $userinfo['email'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Network") . ":</th>\n";
$html .= " <td><a href='?date_from={$_date_from}&date_to={$_date_to}&network_id={$userinfo['account_origin']}'>{$userinfo['account_origin']}</a></td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Unique ID") . ":</th>\n";
$html .= " <td>" . $userinfo['user_id'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Member since") . ":</th>\n";
$html .= " <td>" . strftime("%c", strtotime($userinfo['reg_date'])) . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Account Status") . ":</th>\n";
$html .= " <td>" . $userinfo['account_status_description'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Prefered Locale") . ":</th>\n";
$html .= " <td>" . $userinfo['prefered_locale'] . "</td>\n";
$html .= "</tr>\n";
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
/******* Connections **********/
$html .= "<fieldset>\n";
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("*");
$sql = "{$candidate_connections_sql} ORDER BY timestamp_in DESC";
$db->execSql($sql, $connections, false);
$html .= "<legend>" . sprintf(_("%d Connections"), count($connections)) . "</legend>\n";
// Variables init
$even = 0;
$total = array();
$total['incoming'] = 0;
$total['outgoing'] = 0;
$total['time_spent'] = 0;
if (count($connections) == 0) {
$html .= _("No information found matching the report configuration");
} else {
$html .= "<table class='smaller'>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Logged in") . "</th>\n";
$html .= " <th>" . _("Time spent") . "</th>\n";
$html .= " <th>" . _("Token status") . "</th>\n";
$html .= " <th>" . _("Node") . "</th>\n";
$html .= " <th>" . _("IP") . "</th>\n";
$html .= " <th>" . _("D") . "</th>\n";
$html .= " <th>" . _("U") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
foreach ($connections as $connection) {
$timestamp_in = !empty($connection['timestamp_in']) ? strtotime($connection['timestamp_in']) : null;
$timestamp_out = !empty($connection['timestamp_out']) ? strtotime($connection['timestamp_out']) : null;
$nodeObject = Node::getObject($connection['node_id']);
$total['incoming'] += $connection['incoming'];
$total['outgoing'] += $connection['outgoing'];
$connection['token_status_description'] = $token_to_text[$connection['token_status']];
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>" . strftime("%c", $timestamp_in) . "</td>\n";
if (!empty($timestamp_in) && !empty($timestamp_out)) {
$total['time_spent'] += $timestamp_out - $timestamp_in;
$html .= "<td>" . Utils::convertSecondsToWords($timestamp_out - $timestamp_in) . "</td>\n";
} else {
$html .= "<td>" . _("N/A") . "</td>\n";
}
$html .= " <td>" . $connection['token_status'] . "</td>\n";
$html .= " <td><a href='?date_from={$_date_from}&date_to={$_date_to}&node_id={$nodeObject->getId()}'>{$nodeObject->getName()}</a></td>\n";
$html .= " <td>" . $connection['user_ip'] . "</td>\n";
$html .= " <td>" . Utils::convertBytesToWords($connection['incoming']) . "</td>\n";
$html .= " <td>" . Utils::convertBytesToWords($connection['outgoing']) . "</td>\n";
$html .= "</tr>\n";
}
$html .= "<tr>\n";
$html .= " <th>" . _("Total") . ":</th>\n";
$html .= " <th>" . Utils::convertSecondsToWords($total['time_spent']) . "</th>\n";
$html .= " <td></td>\n";
$html .= " <td></td>\n";
$html .= " <td></td>\n";
$html .= " <th>" . Utils::convertBytesToWords($total['incoming']) . "</th>\n";
$html .= " <th>" . Utils::convertBytesToWords($total['outgoing']) . "</th>\n";
$html .= "</tr>\n";
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
if ($this->stats->getDistinguishUsersBy() == 'user_id') {
/******* MAC addresses **********/
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("user_mac,count(user_mac) as nb ");
$sql = "{$candidate_connections_sql} group by user_mac order by nb desc";
$db->execSql($sql, $rows, false);
$html .= "<fieldset>\n";
$html .= "<legend>" . sprintf(_("%d MAC addresses"), count($rows)) . "</legend>\n";
$html .= "<table>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("MAC") . "</th>\n";
$html .= " <th>" . _("Count") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
$even = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['user_mac']}</td>\n";
$html .= " <td>" . $row['nb'] . "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "</table>\n";
$html .= "</fieldset>\n";
} else {
/******* Usernames **********/
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("connections.user_id,username, count(connections.user_id) as nb ", true);
$sql = "{$candidate_connections_sql} group by connections.user_id, username order by nb desc, connections.user_id,username";
$db->execSql($sql, $rows, false);
$html .= "<fieldset>\n";
$html .= "<legend>" . sprintf(_("%d users"), count($rows)) . "</legend>\n";
$html .= "<table>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Username") . "</th>\n";
$html .= " <th>" . _("Count") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
$even = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['username']}</td>\n";
$html .= " <td>" . $row['nb'] . "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
}
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT {$distinguish_users_by}, date_trunc('day', timestamp_in) AS date");
$sql = "SELECT COUNT(*) AS active_days, {$distinguish_users_by} FROM ({$candidate_connections_sql} GROUP BY date,{$distinguish_users_by}) AS user_active_days GROUP BY {$distinguish_users_by} ORDER BY active_days DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $frequent_users_stats, false);
if ($frequent_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Different days connected") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($frequent_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
//$html .= " <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
$html .= " <td>" . $row['active_days'] . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* Users who signed up here */
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
/* The following query will retreive the list of the REAL first connection of each user, no matter where or when.*/
$sql_real_first_connections = $this->stats->getSqlRealFirstConnectionsQuery('connections.conn_id', false);
//$db->execSql($sql_real_first_connections, $tmp, true);
$real_first_connections_table_name = "real_first_conn_table_name_" . session_id();
$real_first_connections_table_sql = "CREATE TABLE {$real_first_connections_table_name} AS ({$sql_real_first_connections});\n";
$real_first_connections_table_sql .= "CREATE INDEX {$real_first_connections_table_name}_idx ON {$real_first_connections_table_name} (conn_id); \n";
$db->execSqlUpdate($real_first_connections_table_sql, false);
/* Now retrieves the oldest connection matching the report restriction, and only keep it if it's really the user's first connection */
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("connections.{$distinguish_users_by},users.username,users.reg_date, conn_id, nodes.name ", true);
$db->execSql($candidate_connections_sql, $tmp, false);
$first_connection_table_name = "first_conn_table_name_" . session_id();
$registration_node_table_sql = "CREATE TEMP TABLE {$first_connection_table_name} AS ({$candidate_connections_sql});\n \n";
//$registration_node_table_sql .= "CREATE INDEX {$first_connection_table_name}_idx ON $first_connection_table_name (conn_id)";
$db->execSqlUpdate($registration_node_table_sql, false);
//$sql = "select 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";
$sql = "SELECT * FROM {$first_connection_table_name} JOIN {$real_first_connections_table_name} USING (conn_id) ORDER BY reg_date DESC";
$rows = null;
$db->execSql($sql, $rows, false);
$registration_node_table_sql = "DROP TABLE {$first_connection_table_name};";
$db->execSqlUpdate($registration_node_table_sql, false);
$real_first_connections_table_sql = "DROP TABLE {$real_first_connections_table_name};";
$db->execSqlUpdate($real_first_connections_table_sql, false);
$html .= "<fieldset>";
$html .= "<legend>" . _("Users who signed up here") . "</legend>";
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= "<th>" . _("Node") . "</th>";
if ($distinguish_users_by == 'user_id') {
$html .= "<th>" . _("Username") . "</th>";
} else {
$html .= "<th>" . _("MAC address") . "</th>";
}
$html .= "<th>" . _("Registration date") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
$total = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$total++;
$html .= " <td>{$row['name']}</td>\n";
if ($distinguish_users_by == 'user_id') {
$html .= " <td>{$row['username']}</td>\n";
} else {
$html .= " <td>{$row['user_mac']}</td>\n";
}
$html .= " <td>" . strftime("%c", strtotime($row['reg_date'])) . "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "<tr>\n";
$html .= " <th>" . _("Total") . ":</th>\n";
$html .= " <th>" . $total . "</th>\n";
$html .= "</tr>\n";
$html .= "</table>\n";
$html .= "</fieldset>\n";
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$selected_nodes = $this->stats->getSelectedNodes();
if (count($selected_nodes) == 0) {
$html .= _("Sorry, this report requires you to select individual nodes");
} else {
//pretty_print_r($this->stats->getSelectedNodes ());
foreach ($selected_nodes as $node_id => $nodeObject) {
$html .= "<fieldset>";
$html .= "<legend>" . $nodeObject->getName() . "</legend>";
/* Status */
$html .= "<fieldset>";
$html .= "<legend>" . _("Status") . "</legend>";
$html .= "<table>";
$db->execSql("SELECT node_id, name, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, last_heartbeat_ip, CASE WHEN ((CURRENT_TIMESTAMP-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);
$html .= $rows[0]['is_up'] == 't' ? "<tr class='even up'>" : "<tr class='even down'>";
$html .= " <th>" . _("WifiDog status") . "</th>";
$html .= " <td>";
$html .= $rows[0]['is_up'] == 't' ? "UP" : "<span class='down'>DOWN</span>";
$html .= "</td>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Last heartbeat") . "</th>";
$html .= " <td>" . sprintf(_("%s ago"), Utils::convertSecondsToWords(time() - strtotime($nodeObject->getLastHeartbeatTimestamp()))) . "</td>";
$html .= "</tr>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("WifiDog version") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatUserAgent() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("WifiDog uptime") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatWifidogUptime() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("System uptime") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatSysUptime() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("System load") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatSysLoad() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("System free memory") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatSysMemfree() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("IP Address") . "</th>";
$html .= " <td>" . $nodeObject->getLastHeartbeatIP() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Number of users online") . "</th>";
$html .= " <td>" . $nodeObject->getNumOnlineUsers() . "</td>";
$html .= "</tr>";
$html .= "</table>";
$html .= "</fieldset>";
/* End Status */
/* Profile */
$html .= "<fieldset>";
$html .= "<legend>" . _("Profile") . "</legend>";
$html .= "<table>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Name") . "</th>";
$html .= " <td>" . $nodeObject->getName() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Node ID") . "</th>";
$html .= " <td>" . $nodeObject->getId() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Deployment Status") . "</th>";
$html .= " <td>" . $nodeObject->getDeploymentStatus() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Deployment date") . "</th>";
$html .= " <td>" . $nodeObject->getCreationDate() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Description") . "</th>";
$html .= " <td>" . $nodeObject->getDescription() . "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Network") . "</th>";
$html .= " <td>" . $nodeObject->getNetwork()->getName() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("GIS Location") . "</th>";
$html .= " <td>";
if ($nodeObject->getGisLocation()->getLatitude() && $nodeObject->getGisLocation()->getLongitude()) {
$html .= $nodeObject->getGisLocation()->getLatitude() . " " . $nodeObject->getGisLocation()->getLongitude();
$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\");'>";
} else {
$html .= _("NOT SET");
}
$html .= " </td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Homepage") . "</th>";
$html .= " <td><a href='" . $nodeObject->getWebSiteURL() . "'>" . $nodeObject->getWebSiteURL() . "</a></td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Address") . "</th>";
$html .= " <td>";
$html .= trim($nodeObject->getCivicNumber() . " " . $nodeObject->getStreetName()) . "<br>";
$html .= trim($nodeObject->getCity() . " " . $nodeObject->getProvince()) . "<br>";
$html .= trim($nodeObject->getCountry() . " " . $nodeObject->getPostalCode());
$html .= "</td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Telephone") . "</th>";
$html .= " <td>" . $nodeObject->getTelephone() . "</td>";
$html .= "</tr>";
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Email") . "</th>";
$html .= " <td><a href='mailto:" . $nodeObject->getEmail() . "'>" . $nodeObject->getEmail() . "</a></td>";
$html .= "</tr>";
$html .= "<tr class='even'>";
$html .= " <th>" . _("Transit Info") . "</th>";
$html .= " <td>" . $nodeObject->getTransitInfo() . "</td>";
$html .= "</tr>";
$html .= "</table>";
$html .= "</fieldset>";
/* End Profile */
/* Statistics */
$html .= "<fieldset>";
$html .= "<legend>" . _("Statistics") . "</legend>";
$html .= "<table>";
$date_constraint = $this->stats->getSqlDateConstraint();
$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) {$date_constraint} GROUP BY date_trunc('day', timestamp_in)) AS daily_connections_table) / (EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP-(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);
$html .= "<tr class='even'>";
$html .= " <th>" . _("Average visits per day") . ":</th>";
$html .= " <td>" . $rows[0]['connections_per_day'] . " " . _("(for the selected period)") . " </td>";
$html .= "</tr>";
$db->execSql("SELECT SUM(incoming) AS in, SUM(outgoing) AS out FROM connections WHERE node_id='{$node_id}' {$date_constraint}", $rows, false);
$html .= "<tr class='odd'>";
$html .= " <th>" . _("Traffic") . ":</th>";
$html .= " <td>";
$html .= _("Incoming") . ": " . Utils::convertBytesToWords($rows[0]['in']);
$html .= "<br>";
$html .= _("Outgoing") . ": " . Utils::convertBytesToWords($rows[0]['out']);
$html .= "<br>";
$html .= _("(for the selected period)");
$html .= "</td>";
$html .= "</table>";
$html .= "</fieldset>";
$html .= "</fieldset>";
/* End Statistics */
}
//End foreach
}
//End else
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* User visits */
// Only Super admin
if (!User::getCurrentUser()->DEPRECATEDisSuperAdmin()) {
$html .= "<p class='error'>" . _("Access denied") . "</p>";
} else {
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
if ($distinguish_users_by == 'user_id') {
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("connections.{$distinguish_users_by}, count(distinct connections.user_mac) as nb_mac, COUNT(DISTINCT connections.user_id) AS nb_users, username,count(connections.{$distinguish_users_by}) as nb_cx,max(timestamp_in) as last_seen", true);
$sql = "{$candidate_connections_sql} GROUP BY connections.{$distinguish_users_by},username ORDER BY nb_cx desc,username";
} else {
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("connections.{$distinguish_users_by}, count(distinct connections.user_mac) as nb_mac, COUNT(DISTINCT connections.user_id) AS nb_users, count(connections.{$distinguish_users_by}) as nb_cx,max(timestamp_in) as last_seen", true);
$sql = "{$candidate_connections_sql} GROUP BY connections.{$distinguish_users_by} ORDER BY nb_cx desc";
}
$db->execSql($sql, $rows, false);
$html .= "<fieldset>";
$html .= "<legend>" . _("Number of unique Users:") . count($rows) . "</legend>";
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$html .= "<th>" . _("Username") . "</th>";
$html .= "<th>" . _("MAC Count") . "</th>";
} else {
$html .= "<th>MAC</th>";
$html .= "<th>Users count</th>";
}
$html .= "<th>" . _("Cx Count") . "</th>";
$html .= "<th>" . _("Last seen") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
foreach ($rows as $row) {
$html .= "<tr>\n";
if ($distinguish_users_by == 'user_id') {
$html .= " <td>{$row['username']}</td>\n";
$html .= " <td>" . $row['nb_mac'] . "</td>\n";
} else {
$html .= " <td>{$row['user_mac']}</td>\n";
$html .= " <td>" . $row['nb_users'] . "</td>\n";
}
$html .= " <td>" . $row['nb_cx'] . "</td>\n";
$html .= " <td>" . strftime("%Y-%m-%d %H:%M:%S", strtotime($row['last_seen'])) . "</td>\n";
$html .= "</tr>\n";
}
$html .= "</table>";
$html .= "</fieldset>";
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("users.username, nodes.name, EXTRACT('EPOCH' FROM date_trunc('second',timestamp_out-timestamp_in)) AS time_spent, EXTRACT('EPOCH' FROM timestamp_in) AS timestamp_in, connections.user_id, user_mac ", true);
$sql = "{$candidate_connections_sql} ORDER BY timestamp_in DESC";
$db->execSql($sql, $rows, false);
$number_of_connections = count($rows);
$html .= "<fieldset>";
$html .= "<legend>" . _("Number of non-unique connections:") . $number_of_connections . "</legend>";
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= "<th>" . _("Node name") . "</th>";
$html .= "<th>" . _("Username") . "</th>";
$html .= "<th>" . _("MAC") . "</th>";
$html .= "<th>" . _("Date") . "</th>";
$html .= "<th>" . _("Time spent") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['name']}</td>\n";
$html .= " <td>{$row['username']}</td>\n";
$html .= " <td>{$row['user_mac']}</td>\n";
$html .= " <td>" . strftime("%c", $row['timestamp_in']) . "</td>\n";
$html .= " <td>";
if (!empty($row['time_spent'])) {
$html .= Utils::convertSecondsToWords($row['time_spent']);
} else {
$html .= _("Unknown");
}
$html .= "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
return parent::getReportUI($html);
}
