<!DOCTYPE html>
<html>
<head>
    <title>Admin interface</title>
    <link rel="stylesheet" href="style.css" type="text/css"/>
</head>
<body>
    <div class="container">
        <div class="centerH">
            <nav>
                <a href="?path=users">Users</a>
                <a href="?path=reports">Reports</a>
            </nav>
            <div>
                <?php
                require_once '../include/DbConnect.php';

                // opening db connection
                $db = new DbConnect();
                $connection = $db->connect();
                $prefix = "";
                $prefixFile = fopen("../databasePrefix.txt", "r") or die("Cannot find databasePrefix.txt in root directory!");
                if ($prefixFile == null)
                    return false;
                if (!filesize("../databasePrefix.txt") == 0)
                    $prefix = fread($prefixFile, filesize("../databasePrefix.txt"));
                fclose($prefixFile);

                if(isset($_GET["path"]))
                {
                    $path = $_GET["path"];
                    if($path == "users" || $path == "reports")
                    {
                        switch($path)
                        {
                            case "users":
                                $table = $prefix . $path;
                                echo '<h3>',$table,'</h3>';
                                $data = mysqli_query($connection, 'SELECT usr.Id, usr.Username, COUNT(DISTINCT locs.Id) AS "Locations logged", COUNT(DISTINCT msgs.Id) AS "Messages logged" FROM '.$prefix.'users AS usr LEFT JOIN '.$prefix.'locations AS locs ON locs.UserId = usr.Id LEFT JOIN '.$prefix.'messages AS msgs ON msgs.UserId = usr.Id GROUP BY usr.Id');

                                $dir = "../logCalls/";
                                $callFiles = array();
                                // Open a directory, and read its contents
                                if (is_dir($dir)){
                                    if ($dh = opendir($dir)){
                                        while (($file = readdir($dh)) !== false){
                                            if($file != "." && $file != ".." && $file != "logCall.php")
                                                array_push($callFiles, $file);
                                        }
                                        closedir($dh);
                                    }
                                }

                                writeUsersTable($data, $callFiles);
                                break;
                            case "reports":
                                $table = $prefix . $path;
                                echo '<h3>',$table,'</h3>';
                                $data = "";
                                ?>
                                <a href="?path=reports&order=asc">Ascending order</a>
                                <a href="?path=reports&order=desc">Descending order</a>
                                <?php
                                $ascOrder = false;
                                if(isset($_GET["order"]) && $_GET["order"] == "asc")
                                    $ascOrder = true;
                                $order = $ascOrder ? 'ascending':'descending';

                                echo "<h2>1.	Users sorted in {$order} order of the score.</h2>";
                                $data = mysqli_query($connection, 'SELECT Id, Username, MaxDifficultyLevel, QuestionCount, Score, AverageTime FROM '.$prefix.'users ORDER BY Score '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>2.	Users sorted in {$order} order of solving the quiz.</h2>";
                                $data = mysqli_query($connection, 'SELECT Id, Username, MaxDifficultyLevel, QuestionCount, Score, AverageTime FROM '.$prefix.'users ORDER BY QuestionCount '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>3.	Users sorted in {$order} order of the %score.</h2>";
                                $data = mysqli_query($connection, 'SELECT Id, Username, MaxDifficultyLevel, QuestionCount, Score, AverageTime FROM '.$prefix.'users WHERE QuestionCount > 0 ORDER BY Score/QuestionCount '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>4.	Questions sorted in {$order} order of being answered correctly.</h2>";
                                $data = mysqli_query($connection, 'SELECT qus.Id, QuestionText, Option1, Option2, Option3, Option4, RightOption FROM '.$prefix.'reports INNER JOIN '.$prefix.'questions AS qus ON qus.Id=QuestionId WHERE Attempts=1 GROUP BY QuestionId ORDER BY COUNT(*)'.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>5.	Questions sorted in {$order} order of the time taken to solve them.</h2>";
                                $data = mysqli_query($connection, 'SELECT qus.Id, QuestionText, Option1, Option2, Option3, Option4, RightOption FROM '.$prefix.'reports INNER JOIN '.$prefix.'questions AS qus ON qus.Id=QuestionId GROUP BY QuestionId ORDER BY AVG(Time) '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>6.	Users sorted in {$order} order of the the number of times they have logged onto the server.</h2>";
                                $data = mysqli_query($connection, 'SELECT UserId, Username, COUNT(*) AS "Times logged" FROM '.$prefix.'sessions INNER JOIN '.$prefix.'users AS usr ON usr.Id=UserId GROUP BY UserId ORDER BY COUNT(*) '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>7.	Users sorted in {$order} order of their session length.</h2>";
                                $data = mysqli_query($connection, 'SELECT UserId, Username, (SessionEnded - SessionAdded) AS "Avg session length (in s)" FROM '.$prefix.'sessions INNER JOIN '.$prefix.'users AS usr ON usr.Id=UserId WHERE SessionEnded IS NOT NULL GROUP BY UserId ORDER BY AVG(SessionEnded - SessionAdded) '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>8.	Users sorted in {$order} order of the number of different IPs they are using.</h2>";
                                $data = mysqli_query($connection, 'SELECT UserId, Username, COUNT(DISTINCT IpAddress) AS "Number of IpAddresses" FROM '.$prefix.'sessions INNER JOIN '.$prefix.'users AS usr ON usr.Id=UserId GROUP BY UserId ORDER BY COUNT(DISTINCT IpAddress) '.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>9.	Category/Model/Technology sorted in {$order} order of the number of times it has been answered correctly.</h2>";
                                $data = mysqli_query($connection, 'SELECT cmt.Id, cmt.Title AS "Category title" FROM '.$prefix.'reports INNER JOIN '.$prefix.'questions AS qus ON qus.Id=QuestionId INNER JOIN '.$prefix.'categories AS cmt ON cmt.Id=qus.CategoryId WHERE Attempts=1 GROUP BY CategoryId ORDER BY COUNT(*)'.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                $data = mysqli_query($connection, 'SELECT cmt.Id, cmt.Title AS "Model title" FROM '.$prefix.'reports INNER JOIN '.$prefix.'questions AS qus ON qus.Id=QuestionId INNER JOIN '.$prefix.'models AS cmt ON cmt.Id=qus.CategoryId WHERE Attempts=1 GROUP BY CategoryId ORDER BY COUNT(*)'.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                $data = mysqli_query($connection, 'SELECT cmt.Id, cmt.Title AS "Technology title" FROM '.$prefix.'reports INNER JOIN '.$prefix.'questions AS qus ON qus.Id=QuestionId INNER JOIN '.$prefix.'technologies AS cmt ON cmt.Id=qus.CategoryId WHERE Attempts=1 GROUP BY CategoryId ORDER BY COUNT(*)'.($ascOrder?'ASC':'DESC').' LIMIT 10');
                                writeTable($data);
                                echo "<h2>10.	Usage pattern based on week/month/quarter/year.</h2>";
                                echo "<h2>11.	Month wise report on the number of questions that have been added to the database.</h2>";
                                $data = mysqli_query($connection, 'SELECT COUNT(*) AS "month" FROM '.$prefix.'questions WHERE MONTH(DateAdded) = MONTH(now())');
                                echo "In this month ".mysqli_fetch_row($data)[0]." questions are added!";
                                break;
                            case "backupDb":

                                break;
                        }
                    }
                }

                function writeTable($data)
                {
                    echo '<table cellpadding="0" cellspacing="10px" class="db-table">';
                    echo '<tr>';

                    for($i = 0; $i < mysqli_num_fields($data); $i++) {
                        $field_info = mysqli_fetch_field($data);
                        echo "<th>{$field_info->name}</th>";
                    }

                    echo '</tr>';
                    if(mysqli_num_rows($data))
                        while($row2 = mysqli_fetch_row($data)) {
                            echo '<tr>';
                            foreach($row2 as $key=>$value) {
                                echo '<td>',$value,'</td>';
                            }
                            echo '</tr>';
                        }
                    echo '</table><br/>';
                }

                function writeUsersTable($data, $callFiles)
                {
                    echo '<table cellpadding="0" cellspacing="10px" class="db-table">';
                    echo '<tr>';

                    for($i = 0; $i < mysqli_num_fields($data); $i++) {
                        $field_info = mysqli_fetch_field($data);
                        echo "<th>{$field_info->name}</th>";
                    }
                    echo "<th>Calls logged</th>";

                    echo '</tr>';
                    if(mysqli_num_rows($data))
                        while($row2 = mysqli_fetch_row($data)) {
                            echo '<tr>';
                            echo '<a href="dass">';
                            $username = "jkladhsjdas";
                            $id = -1;
                            foreach($row2 as $key=>$value){
                                if($key == 0)
                                    $id = $value;
                                if($key == 1)
                                {
                                    $username = $value;
                                    echo '<td><a href="user.php?id=' . $id . '">',$value,'</a></td>';
                                }else{
                                    echo '<td>',$value,'</td>';
                                }
                            }
                            $counter = 0;
                            foreach($callFiles as $callFile)
                                if(strpos($callFile, $username) === 0)
                                    $counter++;
                            echo '<td>', $counter,'</td>';
                            echo '</a>';
                            echo '</tr>';
                        }
                    echo '</table><br/>';
                }
                ?>
            </div>
        </div>
    </div>
</body>
</html>

<?php
/**
 * Created by PhpStorm.
 * User: Enver
 * Date: 08.04.2017.
 * Time: 12:06
 */