<?php

//fill the dropdown list
$queryDropdown = "SELECT DISTINCT mac FROM location";
$resultDropdown = filterFunction($queryDropdown);
// if not set then filter the table with default values using this mac address
$query = "SELECT mac,hostid,interface FROM location where mac='C8:50:E9:8C:E2:52' ORDER BY timestamp DESC";
$result = filterFunction($query);

$variablesQuery = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
$resultVariables = filterFunction($variablesQuery);

$variablesQuerySecondTable = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
$resultVariablesSecondTable = filterFunction($variablesQuerySecondTable);

$variablesQueryBody = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='C8:50:E9:8C:E2:52' and datetime BETWEEN '2019-09-20' AND '2019-09-21'";
$resultVariablesBody = filterFunction($variablesQueryBody);

// SQL STATEMENTS FOR THE VARIABLES THAT INCLUDE AVG MIN MAX etc.
$uptimeSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Uptime' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultUptimeSql = filterFunction($uptimeSql);
$rowUptime = mysqli_fetch_row($resultUptimeSql);

$distanceSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Distance' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultDistanceSql = filterFunction($distanceSql);
$rowDistance = mysqli_fetch_row($resultDistanceSql);

$supplyVoltageAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Supply Voltage' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultSupplyVoltageAvg = filterFunction($supplyVoltageAvg);
$rowSupplyVoltage = mysqli_fetch_row($resultSupplyVoltageAvg);

$rxPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'RX Power' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultRXPowerAvg = filterFunction($rxPowerAvg);
$rowRXPower = mysqli_fetch_row($resultRXPowerAvg);

$temperatureAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Temperature' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultTemperature = filterFunction($temperatureAvg);
$rowTemperature = mysqli_fetch_row($resultTemperature);

$txPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'TX Power' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultTXPowerAvg = filterFunction($txPowerAvg);
$rowTXPower = mysqli_fetch_row($resultTXPowerAvg);

$txBiasCurrent = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'TX Bias Current' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultTXBiasCurrent = filterFunction($txBiasCurrent);
$rowTxBiasCurrent = mysqli_fetch_row($resultTXBiasCurrent);

$trafficUploadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Traffic_Upload' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultTrafficUpload = filterFunction($trafficUploadSUM);
$rowTrafficUpload = mysqli_fetch_row($resultTrafficUpload);

$trafficDownloadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Traffic_Download' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultTrafficDownload = filterFunction($trafficDownloadSUM);
$rowTrafficDownload = mysqli_fetch_row($resultTrafficDownload);

$UploadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Upload_Speed' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultUploadSpeed = filterFunction($UploadSpeed);
$rowUploadSpeed = mysqli_fetch_row($resultUploadSpeed);

$DownloadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = 'C8:50:E9:8C:E2:52' AND variable = 'Download Speed' and datetime BETWEEN '2019-09-20' AND '2019-09-21' ORDER BY datetime DESC LIMIT 1";
$resultDownloadSpeed = filterFunction($DownloadSpeed);
$rowDownloadSpeed = mysqli_fetch_row($resultDownloadSpeed);


if(isset($_GET['submit'])){

    if(isset($_GET['dropDown'])){
        $selected_Val = $_GET['dropDown'];
        $from = $_GET['from'];
        $to=$_GET['to'];
        // make a SQL Query with the value from the dropDown list, this lists the mac addresses
        $query = "SELECT mac,hostid,interface FROM location where mac='".$selected_Val."' ORDER BY timestamp DESC";
        $result = filterFunction($query);
        // after finishing with the first query, fill the dropdown
        $queryDropdown = "SELECT DISTINCT mac FROM location";
        $resultDropdown = filterFunction($queryDropdown);
        // selecting the distinct variables from the client_item table
        $variablesQuery = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."'  "; //".$selected_Val."
        $resultVariables = filterFunction($variablesQuery);

        $variablesQuerySecondTable = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."'";
        $resultVariablesSecondTable = filterFunction($variablesQuerySecondTable);

        $variablesQueryBody = "SELECT DISTINCT variable,itemid FROM client_item WHERE mac='".$selected_Val."' and datetime BETWEEN '".$from."' AND '".$to."'";
        $resultVariablesBody = filterFunction($variablesQueryBody);

        // SQL STATEMENTS FOR THE VARIABLES THAT INCLUDE AVG MIN MAX etc.
        $uptimeSql = "SELECT  datetime,`value` FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Uptime' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultUptimeSql = filterFunction($uptimeSql);
        $rowUptime = mysqli_fetch_row($resultUptimeSql);

        $distanceSql = "SELECT datetime,`value` FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Distance' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultDistanceSql = filterFunction($distanceSql);
        $rowDistance = mysqli_fetch_row($resultDistanceSql);

        $supplyVoltageAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Supply Voltage' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultSupplyVoltageAvg = filterFunction($supplyVoltageAvg);
        $rowSupplyVoltage = mysqli_fetch_row($resultSupplyVoltageAvg);

        $rxPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'RX Power' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultRXPowerAvg = filterFunction($rxPowerAvg);
        $rowRXPower = mysqli_fetch_row($resultRXPowerAvg);

        $temperatureAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Temperature' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultTemperature = filterFunction($temperatureAvg);
        $rowTemperature = mysqli_fetch_row($resultTemperature);

        $txPowerAvg = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'TX Power' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultTXPowerAvg = filterFunction($txPowerAvg);
        $rowTXPower = mysqli_fetch_row($resultTXPowerAvg);

        $txBiasCurrent = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'TX Bias Current' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultTXBiasCurrent = filterFunction($txBiasCurrent);
        $rowTxBiasCurrent = mysqli_fetch_row($resultTXBiasCurrent);

        $trafficUploadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Traffic_Upload' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultTrafficUpload = filterFunction($trafficUploadSUM);
        $rowTrafficUpload = mysqli_fetch_row($resultTrafficUpload);

        $trafficDownloadSUM = "SELECT datetime,SUM(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Traffic_Download' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultTrafficDownload = filterFunction($trafficDownloadSUM);
        $rowTrafficDownload = mysqli_fetch_row($resultTrafficDownload);

        $UploadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Upload_Speed' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultUploadSpeed = filterFunction($UploadSpeed);
        $rowUploadSpeed = mysqli_fetch_row($resultUploadSpeed);

        $DownloadSpeed = "SELECT datetime,AVG(`value`) FROM `client_item` WHERE mac = '".$selected_Val."' AND variable = 'Download Speed' and datetime BETWEEN '".$from."' AND '".$to."' ORDER BY datetime DESC LIMIT 1";
        $resultDownloadSpeed = filterFunction($DownloadSpeed);
        $rowDownloadSpeed = mysqli_fetch_row($resultDownloadSpeed);


    }else{
        echo "<div class=\"alert alert-danger alert-dismissible fade show\">
    <strong>Error!</strong> Please select a mac address.
    <button type=\"button\" class=\"close\" data-dismiss=\"alert\">&times;</button>
</div>";
    }


}
function filterFunction($query){
    $connect = mysqli_connect("localhost","root","","praksa");
    $filter_query = mysqli_query($connect,$query);
    return $filter_query;
}
?>
<html>
<head>
    <title>Zabbix</title>

    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" href="style.css">
    <script
        src="https://code.jquery.com/jquery-3.4.1.min.js"
        integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="
        crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>

</head>
<body>
    <div class="container">
        <form class="form-inline" method="GET" action="index.php">
            <label class="my-1 mr-2" for="inlineFormCustomSelectPref">Preference</label>
            <select class="custom-select my-1 mr-sm-2" name="dropDown" id="inlineFormCustomSelectPref">
                <option selected disabled>Choose...</option>
                <?php while ($row = mysqli_fetch_array($resultDropdown)): ?>
                    <option value="<?php echo $row['mac']?>"><?php echo $row['mac']?></option>
                <?php endwhile; ?>
            </select>
           <div class="form-group mr-2">
               <label for="from">From date:</label>
               <input type="date" class="form-control" id="from" name="from" required/>
               <label for="to">To date:</label>
               <input type="date" class="form-control" id="to" name="to" required/>
           </div>
            <button type="submit" name="submit" class="btn btn-primary my-1">Submit</button>
        </form>
        <div class="row">
            <div class="col-lg-4">
                <table>
                    <thead>
                    <tr>
                        <td>Mac address</td>
                        <td>Host</td>
                        <td>Interface</td>
                    </tr>
                    </thead>
                    <tbody>
                    <?php while ($row = mysqli_fetch_array($result)): ?>
                        <tr>
                            <td><?php echo $row['mac']?></td>
                            <td><?php echo $row['hostid']?></td>
                            <td><?php echo $row['interface']?></td>
                        </tr>
                    <?php endwhile; ?>
                    </tbody>
                </table>
            </div>
            <div class="col-lg-8">
                <table style="width: 500px;height: 40px">
                    <thead>
                        <tr>
                            <?php while($row = mysqli_fetch_array($resultVariablesSecondTable)): ?>
                                <td><?php echo $row['variable']; ?></td>
                            <?php endwhile ; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <?php while($row2 = mysqli_fetch_array($resultVariablesBody)):?>
                                <?php if($row2['variable']=="Supply Voltage") echo "<td>" . "Supply Voltage  " .round($rowSupplyVoltage[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="Distance") echo "<td>" . " Distance  ".$rowDistance[1]."</td>"; ?>
                                <?php if($row2['variable']=="RX Power") echo "<td>" . " RX Power ".round($rowRXPower[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="Temperature") echo "<td>" ." Temperature ".round($rowTemperature[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="TX Bias Current") echo "<td>"." TX Bias Current ".round($rowTxBiasCurrent[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="TX Power") echo "<td>"."TX Power ".round($rowTXPower[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="Uptime") echo "<td>"." Uptime ".$rowUptime[1]."</td>"; ?>
                                <?php if($row2['variable']=="Traffic_Upload") echo "<td>"." Traffic Upload ".$rowTrafficUpload[1]."</td>"; ?>
                                <?php if($row2['variable']=="Traffic_Download") echo "<td>"." Traffic Download ".$rowTrafficDownload[1]."</td>"; ?>
                                <?php if($row2['variable']=="Upload_Speed") echo "<td>"." Upload Speed ".round($rowUploadSpeed[1],4)."</td>"; ?>
                                <?php if($row2['variable']=="Download Speed") echo "<td>"." Download Speed ".round($rowDownloadSpeed[1],4)."</td>"; ?>
                            <?php endwhile; ?>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
        <?php
        if(mysqli_num_rows($resultVariables)!=0){
        if(isset($_GET['dropDown'])){
            $mac = $_GET['dropDown'];
            $fromDate = $_GET['from'];
            $toDate = $_GET['to'];
        }else{
            $mac = 'C8:50:E9:8C:E2:52';
            $fromDate = '2019-09-20';
            $toDate='2019-09-21';
        }
        $dataValues = [];
        $datetimeValues = [];
        ?>
                <?php  while ($row = mysqli_fetch_array($resultVariables)):  ?>
        <?php

        $sql = "SELECT itemid,value,datetime FROM client_item where mac='".$mac."' AND itemid= " . $row['itemid'] . " and datetime BETWEEN '".$fromDate."' AND '".$toDate."' ";
        $resultStatement = filterFunction($sql);
        $dataValues = [];
        $datetimeValues = [];
        while($row1 = mysqli_fetch_array($resultStatement)) {

            $dataValues[] = $row1['value'];
            $datetimeValues[] = $row1['datetime'];

        } ;
        $dataValues = json_encode($dataValues);
        $datetimeValues = json_encode($datetimeValues);
        ?>
                <div class="mt-5">
                    <h3><?php echo $row['variable'] ?></h3>
                    <canvas id="myChart-<?php echo $row['itemid']?>"
                </div>


    </div>
    <script>
       $(document).ready(function () {
           var ctx = document.getElementById("myChart-<?php echo $row['itemid'] ?>");

           var data = {
               labels:<?php echo $datetimeValues ?> ,
               datasets:[{
                   label:"<?php echo $row['variable'] ?>",
                   fill:false,
                   data:<?php echo $dataValues ?>,
                   backgroundColor:"#455C73"
               }],

           };
           var myChart = new Chart(ctx,{
               data:data,
               type:'line'
           })
       })
    </script>

<?php endwhile; ?>
<?php }
        else {
            echo "<div class=\"alert alert-warning\" role=\"alert\">
                      No data was available for this mac address
                  </div>";
}
 ?>

</body>
</html>