<?php

/*
 * Variables to Edit
 */

$db_user     = "phpmyadmin";
$db_passwd = "";
$db_name     = "wifi_stats";

/*
 * End Variables to Edit
 */

/*
 * Other Global variables
 */

// delete the this section when publishing to DansGuardian wifi tutorial
$branch = `/bin/hostname`;
switch ($branch)
{
    case strpos($branch, "a-gateway"):
        $branch = "Algansee";
        break;
    case strpos($branch, "b-gateway"):
        $branch = "Bronson";
        break;
    case strpos($branch, "filter"):
        $branch = "Coldwater";
        break;
    case strpos($branch, "www"):
        $branch = "Coldwater";
        break;
    case strpos($branch, "q-gateway"):
        $branch = "Quincy";
        break;
    case strpos($branch, "s-gateway"):
        $branch = "Sherwood";
        break;
    case strpos($branch, "u-gateway"):
        $branch = "Union Twp.";
        break;
}
// end delete


$now     = date("j-n-Y-G");
$now_dot = explode ("-", $now);

$current_day   = $now_dot[0];
$current_month = $now_dot[1];
$current_year  = $now_dot[2];
$current_hour  = $now_dot[3];

$yesterday     = date("j-n-Y", strtotime("-1 days"));
$yesterday_dot = explode ("-", $yesterday);

$yesterday_day   = $yesterday_dot[0];
$yesterday_month = $yesterday_dot[1];
$yesterday_year  = $yesterday_dot[2];

if ($current_month == 1)
{
    $last_month      = 12;
    $last_month_year = $current_year - 1;
}
else
{
    $last_month      = $current_month - 1;
    $last_month_year = $current_year;
}

$last_year = $current_year - 1;

/*
 * End Other Global variables
 */

// ########################################################################

function db_connect()
{   
    global $db_user, $db_passwd, $db_name;

	// set up the database connection; print a message in case of error
	$db = mysqli_connect("localhost", $db_user, $db_passwd, $db_name) or die ("Could not connect to MySQL server");
	
	/* check connection */
    if (mysqli_connect_errno()) 
    {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
	
	return $db;
}

// ########################################################################

function perform_query($query)
{
    // open the db connection
    $db = db_connect();
    
	// perform the query and enter the result into a variable; print a message in case of error
	$result = mysqli_query($db, $query) or die("Query Error");
	
	// close the database connection
    mysqli_close($db);
	
	return $result;
}

// ########################################################################

function days_of_the_week($int)
{
    switch($int)
    {
        case 0:
            $weekday = "Sunday";
            break;
        case 1:                        
            $weekday = "Monday";
            break;
        case 2:                        
            $weekday = "Tuesday";
            break;
        case 3:                        
            $weekday = "Wednesday";
            break;
        case 4:                        
            $weekday = "Thursday";
            break;
        case 5:                        
            $weekday = "Friday";
            break; 
        case 6:                        
            $weekday = "Saturday";
            break;    
    }
    
    return $weekday;
}

// ########################################################################

function months_of_the_year($int)
{
    switch($int)
    {
        case 1:                        
            $month = "January";
            break;
        case 2:                        
            $month = "February";
            break;
        case 3:                        
            $month = "March";
            break;
        case 4:                        
            $month = "April";
            break;
        case 5:                        
            $month = "May";
            break; 
        case 6:                        
            $month = "June";
            break;   
        case 7:                        
            $month = "July";
            break;
        case 8:                        
            $month = "August";
            break;
        case 9:                        
            $month = "September";
            break;
        case 10:                        
            $month = "October";
            break;
        case 11:                        
            $month = "November";
            break;
        case 12:                        
            $month = "December";
            break;
    }
    
    return $month;
}

// ########################################################################

function mysqli_result($search, $row, $field)
{
    $i=0; 
    $result = null; 
    
    while($results=mysqli_fetch_array($search))
    {
        if ($i==$row)
        {
            $result=$results[$field];
        }
        $i++;
    }
    
    return $result;
} 

// ########################################################################

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
    <head>
        <title><?php echo $branch; ?> Wireless Internet Statistics</title>
        <style type="text/css">
            body
            {
                font-family: sans-serif;
                font-size: .75em;
            }
            table
            {
                border-collapse:collapse;
            }
            table, th, td
            {
                border: 1px solid black;
            }
            td
            {
                text-align: center;
            }
            th, td
            {
                padding: .25em;
            }
            td.uses
            {
                text-align: right;
            }
            th
            {
                background-color: lightgray;
            }
            .normal
            {
                font-weight: normal;
            }       
        </style>
    </head>
    <body>  

        <h1>
            <?php echo $branch; ?> 
        </h1>

        <h3>
            Today's Total - <span class="normal"><?php echo $current_month . "/" . $current_day . "/" . $current_year; ?></span>
        </h3>

<?php

$result     = perform_query("SELECT * FROM `day` WHERE `day` = '$current_day' AND `month` = '$current_month' AND `year` = '$current_year'");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{
    $total = mysqli_result($result, 0, "uses");
?>
        <p>
            <?php echo $total; ?> 
        </p>
<?php
}
?>      
        <hr />
        
        <h3>
            Today's Hourly - <span class="normal"><?php echo $current_month . "/" . $current_day . "/" . $current_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Hour</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php

$result     = perform_query("SELECT * FROM `hour` WHERE `day` = '$current_day' AND `month` = '$current_month' AND `year` = '$current_year'");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $hour = $row["hour"];
        $uses = $row["uses"];
        
        echo "                <tr>\n";
        echo "                    <td>";
        echo $hour . ":00 - ";
        echo (int) $hour + 1 . ":00</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";
    }
}
?>
            </tbody>
        </table>        

        <hr />
        
        <h3>
            Yesterday's Total - <span class="normal"><?php echo $yesterday_month . "/" . $yesterday_day . "/" . $yesterday_year;?></span>
        </h3>
        
<?php

$result     = perform_query("SELECT * FROM `day` WHERE `day` = '$yesterday_day' AND `month` = '$yesterday_month' AND `year` = '$yesterday_year' ");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{
    $total = mysqli_result($result, $total_rows - 1, "uses");
?>
        <p>
            <?php echo $total; ?> 
        </p>
<?php
}
else
{
?>
        <p>
            Statistics not available for <?php echo $yesterday_month . "/" . $yesterday_day . "/" . $yesterday_year; ?> 
        </p>
<?php
}
?> 
        <hr />

        <h3>
            Yesterday's Hourly - <span class="normal"><?php echo $yesterday_month . "/" . $yesterday_day . "/" . $yesterday_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Hour</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php

$result     = perform_query("SELECT * FROM `hour` WHERE `day` = '$yesterday_day' AND `month` = '$yesterday_month' AND `year` = '$yesterday_year'");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $hour = $row["hour"];
        $uses = $row["uses"];
        
        echo "                <tr>\n";
        echo "                    <td>";
        echo $hour . ":00 - ";
        echo (int) $hour + 1 . ":00</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";
    }
}
else
{
?>
                <tr>
                    <td colspan="2">Statistics not available for <?php echo $yesterday_month . "/" . $yesterday_day . "/" . $yesterday_year; ?></td>
                </tr>
<?php
}
?> 
            </tbody>
        </table>        
        
        <hr />

        <h3>
            Daily Uses This Month - <span class="normal"><?php echo $current_month . "/" . $current_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Weekday</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php
$result     = perform_query("SELECT * FROM `day` WHERE `month` = '$current_month' AND `year` = '$current_year' ORDER BY `day` ASC");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $uses   = $row["uses"];
        $day     = $row["day"];
        $weekday = $row["weekday"];
        $weekday = days_of_the_week($weekday);
        
        echo "                <tr>\n";
        echo "                    <td>$day</td>\n";
        echo "                    <td>$weekday</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";

        switch($weekday)
        {
            case "Saturday":
?>
                <tr>
                     <td colspan="3">&nbsp;</td>
                </tr>
<?php
                break;
        }
    }
}
?> 
            </tbody>
        </table> 
        
        <hr />
        
        <h3>
            Daily Uses Last Month - <span class="normal"><?php echo $last_month . "/" . $last_month_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Weekday</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php
$result     = perform_query("SELECT * FROM `day` WHERE `month` = '$last_month' AND `year` = '$last_month_year' ORDER BY `day` ASC");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $uses   = $row["uses"];
        $day     = $row["day"];
        $weekday = $row["weekday"];
        $weekday = days_of_the_week($weekday);
        
        echo "                <tr>\n";
        echo "                    <td>$day</td>\n";
        echo "                    <td>$weekday</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";

        switch($weekday)
        {
            case "Saturday":
?>
                <tr>
                     <td colspan="3">&nbsp;</td>
                </tr>
<?php
                break;
        }
    }
}
else
{
?>
            <tr>
                <td colspan="3">Statistics not available for <?php echo $last_month . "/" . $last_month_year; ?></td>
            </tr>
<?php
}
?> 
            </tbody>
        </table> 
        
        <hr />
        
        <h3>
            Monthly Uses This Year - <span class="normal"><?php echo $current_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Month</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php
        
$result     = perform_query("SELECT * FROM `month` WHERE `year` = '$current_year' ORDER BY `month` ASC");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $month = $row["month"];
        $uses = $row["uses"];
        $month = months_of_the_year($month);
        
        echo "                <tr>\n";
        echo "                    <td>$month</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";
    }
}
?> 
            </tbody>
        </table>
        
        <hr />
        
        <h3>
            Monthly Uses Last Year - <span class="normal"><?php echo $last_year; ?></span>
        </h3>
        
        <table>
            <thead>
                <tr>
                    <th>Month</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php
        
$result     = perform_query("SELECT * FROM `month` WHERE `year` = '$last_year' ORDER BY `month` ASC");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $month = $row["month"];
        $uses = $row["uses"];
        $month = months_of_the_year($month);
        
        echo "                <tr>\n";
        echo "                    <td>$month</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";
    }
}
else
{
?>
            <tr>
                <td colspan="2">Statistics are not available for <?php echo $last_year; ?></td>
            </tr>
<?php
}
?> 
            </tbody>
        </table>
        
        <hr />
        
        <h3>
            Yearly Totals
        </h3>

        <table>
            <thead>
                <tr>
                    <th>Year</th>
                    <th>Uses</th>
                </tr>
            </thead>
            <tbody>
<?php
        
$result     = perform_query("SELECT * FROM `year` ORDER BY `year` DESC");
$total_rows = mysqli_num_rows($result);

if ($total_rows)
{    
    foreach ($result as $row)
    {
        $year = $row["year"];
        $uses = $row["uses"];
        
        echo "                <tr>\n";
        echo "                    <td>$year</td>\n";
        echo "                    <td class=\"uses\">$uses</td>\n";
        echo "                </tr>\n";
    }
}
?> 
            </tbody>
        </table>
        
         <hr />
        
        <h3>
            Hourly Averages
        </h3>

        <table>
            <thead>
                <tr>
                    <th>Hour</th>
                    <th>Sunday</th>
                    <th>Monday</th>
                    <th>Tuesday</th>
                    <th>Wednesday</th>
                    <th>Thursday</th>
                    <th>Friday</th>
                    <th>Saturday</th>
                </tr>
            </thead>
            <tbody>
        <?php

    // iterate over the hours of the day
    for ($i = 0; $i < 24; $i++)
    {
        echo "                <tr>\n";
        
    
        echo "                    <td>" . $i . ":00 - ";
        echo (int) $i + 1; 
        echo ":00</td>\n";
    
         // iterate over the days of the week
         for ($j = 0; $j < 7; $j++)
         {
            
            $result     = perform_query("SELECT round(avg(uses), 2) AS 'uses' from `hour` WHERE `hour` = $i and `weekday` = $j");
$total_rows = mysqli_num_rows($result);

            if ($total_rows)
            {    
                foreach ($result as $row)
                {
                    $uses = $row["uses"];
        
                    echo "<td class=\"uses\">$uses</td>\n";
                }
            }
        }
        
        echo "                </tr>\n";
    }
?> 
            </tbody>
        </table>
    </body>
</html>