"; $html .= "

Profile

"; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= "

" . _("Name") . "	" . $nodeObject->getName() . "
" . _("Node ID") . "	" . $nodeObject->getId() . "
" . _("Deployment Status") . "	" . $nodeObject->getDeploymentStatus() . "
" . _("Deployment date") . "	" . $nodeObject->getCreationDate() . "
" . _("Description") . "	" . $nodeObject->getDescription() . "
" . _("Network") . "	" . $nodeObject->getNetwork()->getName() . "
" . _("GIS Location") . "	"; if ($nodeObject->getGisLocation()->getLatitude() && $nodeObject->getGisLocation()->getLongitude()) { $html .= $nodeObject->getGisLocation()->getLatitude() . " " . $nodeObject->getGisLocation()->getLongitude(); $html .= " "; } else { $html .= _("NOT SET"); } $html .= "
" . _("Homepage") . "	" . $nodeObject->getHomePageURL() . "
" . _("Address") . "	"; $html .= trim($nodeObject->getCivicNumber() . " " . $nodeObject->getStreetName()) . " "; $html .= trim($nodeObject->getCity() . " " . $nodeObject->getProvince()) . " "; $html .= trim($nodeObject->getCountry() . " " . $nodeObject->getPostalCode()); $html .= "
" . _("Telephone") . "	" . $nodeObject->getTelephone() . "
" . _("Email") . "	" . $nodeObject->getEmail() . "
" . _("Transit Info") . "	" . $nodeObject->getTransitInfo() . "

"; $html .= ""; $html .= "

"; $html .= "

Status

"; $html .= ""; $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); $html .= ($rows[0]['is_up'] == 't') ? "" : ""; $html .= " "; $html .= " "; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= ""; $html .= " "; $html .= " "; $html .= ""; $html .= "

" . _("WifiDog status") . "	"; $html .= ($rows[0]['is_up'] == 't') ? "UP" : "DOWN"; $html .= "
" . _("Last heartbeat") . "	" . seconds_in_words(time() - strtotime($nodeObject->getLastHeartbeatTimestamp())) . " ago
" . _("WifiDog version") . "	" . $nodeObject->getLastHeartbeatUserAgent() . "
" . _("IP Address") . "	" . $nodeObject->getLastHeartbeatIP() . "

"; $html .= "

"; $html .= "

"; $html .= "

Statistics

"; $html .= ""; $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); $html .= ""; $html .= " "; $html .= " "; $html .= ""; $db->ExecSql("SELECT SUM(incoming) AS in, SUM(outgoing) AS out FROM connections WHERE node_id='{$node_id}' ${date_constraint}", $rows, false); $html .= ""; $html .= " "; $html .= " "; $html .= "

" . _("Average visits per day") . ":	" . $rows[0]['connections_per_day'] . "
" . _("Traffic") . ":	"; $html .= _("Incoming") . ": " . bytes_in_words($rows[0]['in']); $html .= " "; $html .= _("Outgoing") . ": " . bytes_in_words($rows[0]['out']); $html .= " "; $html .= "(for the selected period)"; $html .= "

"; $html .= "

"; $html .= "

"; $html .= "

" . _("Connections per hour of the day") . "

"; $html .= "

"; $html .= "

"; $html .= "

"; $html .= "

" . _("Connections per week day") . "

"; $html .= "

"; $html .= "

"; $html .= "

"; $html .= "

" . _("Connections per month") . "

"; $html .= "

"; $html .= "

"; if (isset($_REQUEST['group_connections']) && $_REQUEST['group_connections'] == "group_connections_by_mac") { $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"; $db->ExecSql($sql, $rows, false); $number_of_macs = count($rows); $html .= "

"; $html .= "

Number of unique MACs: {$number_of_macs}

"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; foreach ($rows as $row) { $html .= "\n"; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= "\n"; } $html .= "

MAC	Users count	Cx count	Last seen
{$row['user_mac']}	" . $row['nb_users'] . "	" . $row['nb_connections'] . "	" . strftime("%Y-%m-%d %H:%M:%S", strtotime($row['last_seen'])) . "

"; $html .= "

"; } elseif (isset($_REQUEST['group_connections']) && $_REQUEST['group_connections'] == "group_connections_by_user") { $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"; $db->ExecSql($sql, $rows, false); $number_of_usernames = count($rows); $html .= "

"; $html .= "

Number of unique Usernames: {$number_of_usernames}

"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; foreach ($rows as $row) { $html .= "\n"; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= "\n"; } $html .= "

Username	MAC Count	Cx Count	Last seen
{$row['username']}	" . $row['nb_mac'] . "	" . $row['nb_cx'] . "	" . strftime("%Y-%m-%d %H:%M:%S", strtotime($row['last_seen'])) . "

"; $html .= "

"; } else { $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"; $db->ExecSql($sql, $rows, false); $number_of_connections = count($rows); $html .= "

"; $html .= "

Number of non-unique connections: {$number_of_connections}

"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $even = 0; if ($rows) { foreach ($rows as $row) { if ($row['timestamp_in']) $timestamp_in = strtotime($row['timestamp_in']); else $timestamp_in = -1; if ($row['timestamp_out']) $timestamp_out = strtotime($row['timestamp_out']); else $timestamp_out = -1; if ($timestamp_in != -1 && $timestamp_out != -1) { $html .= $even ? "\n" : "\n"; if ($even == 0) $even = 1; else $even = 0; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= " \n"; $html .= "\n"; } } } $html .= "

Username	MAC	Date	Time spent
{$row['username']}	{$row['user_mac']}	" . utf8_encode(strftime("%c", strtotime($row['timestamp_in']))) . "	"; if ($timestamp_in != -1 && $timestamp_out != -1) { $html .= seconds_in_words($timestamp_out - $timestamp_in); } $html .= "

\n"; $html .= "

\n"; /* Users who signed up here */ $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"; $db->ExecSql($sql, $rows, false); $html .= "

"; $html .= "

" . _("Users who signed up here") . "

"; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $html .= ""; $even = 0; $total = 0; if ($rows) { foreach ($rows as $row) { $html .= $even ? "\n" : "\n"; if ($even == 0) $even = 1; else $even = 0; $total++; $html .= " \n"; $html .= " \n"; $html .= "\n"; } } $html .= "\n"; $html .= " \n"; $html .= " \n"; $html .= "\n"; $html .= "

" . _("Username") . "	" . _("Registration date") . "
{$row['username']}	" . utf8_encode(strftime("%c", strtotime($row['reg_date']))) . "
" . _("Total") . ":	" . $total . "

\n"; $html .= "

\n"; } ?>