<?php
    include("config.php");
    session_start();
    $my_campus = $_SESSION['campus'];
    $selected_status = "";
    if(isset($_POST['client_id'])){  
        $selected_status = mysqli_real_escape_string($db,$_POST['client_id']);
        $check_selected = true;
        $sql_re1 = "SELECT * FROM requirements WHERE ID='$selected_status'";
            if($result_re = $db->query($sql_re1)){
                while($row_re = $result_re->fetch_array()){
                    $ass_title = $row_re['Title'];
                }
            }
    }
    else{
        $check_selected = false;
    }
    
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>MEMBERS</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
    <style>
        .wrapper{
            width: 100%;
            margin:;
        }
        table tr td:last-child{
            width: 120px;
        }
    </style>
    <style>
* {
  box-sizing: border-box;
}

#myInput {
  background-image: url('/css/searchicon.png');
  background-position: 10px 10px;
  background-repeat: no-repeat;
  width: 100%;
  font-size: 16px;
  padding: 12px 20px 12px 40px;
  border: 1px solid #ddd;
  float: left;
  margin-top: -32px;
}

#myTable {
  border-collapse: collapse;
  width: 100%;
  border: 1px solid #ddd;
  font-size: 18px;
  margin-top: -20px;
}

#myTable th, #myTable td {
  text-align: left;
  padding: 12px;
}

#myTable tr {
  border-bottom: 1px solid #ddd;
}

#myTable tr.header, #myTable tr:hover {
  background-color: #f1f1f1;
}
</style>
    <script>
        $(document).ready(function(){
            $('[data-toggle="tooltip"]').tooltip();   
        });
    </script>
</head>
<body>
<center><h2></h2></center><br>
    <div class="wrapper">
        <div class="container-fluid">
            <div class="row">
                    <form id="my-form" action='monitoring.php?' method="post">
                        <select onchange="sort_file_status()" name="client_id" ID="mySelect" class="field input" style="width: 250px; float: left; border-color: white; border-radius: 5px; background-color: white; border: 2px solid #e8e8e8; height: 40px; font-size: 18px; font-weight: bold; margin-top: -20px; margin-left: 15px; padding:5px;" required>
                        <option value="">Select Assignment:</option>
                        <?php
                            $sql3 = "SELECT * FROM requirements WHERE Campus='$my_campus'";
                            if($result3 = $db->query($sql3)){
                                while($row3 = $result3->fetch_array()){
                                    $ass_code = $row3['ID'];
                                    $title = $row3['Title'];
                                    if($selected_status == $ass_code){
                                        echo '<option value="'.$ass_code.'" selected>
                                        '.$title.'
                                    </option>';
                                    }
                                    else{
                                        echo '<option value="'.$ass_code.'">'.$title.'</option>';
                                    }
                                    
                                }
                            }
                        ?>
                        </select>
                    </form>
                    <br><br>
                <div class="col-md-12">
                    <div class="mt-2 clearfix">
                        <input class="responsive" type="text" id="myInput" onkeyup="myFunction()" placeholder="Search" title="Search Filter" style="min-width:300px; width:100%; height:40px; border-radius: 5px;">
                    </div><br>
                    
                    <b><button onclick="ExportToExcel('xlsx')" class="login100-form-btn" style="background-color: #36892f; color: white; float: left; margin-right: 30px; padding-right:20px; padding-left: 20px; padding-top:10px; padding-bottom: 10px; border: none; border-radius: 5px; font-family: Arial; font-size: 16px;">Export <img src="images/excel_icon.png" style="width: 30px; height: 25px; margin-top: -5px;"></button></b></b>
                    <br><br><br>
                    <?php
                        include("config.php");
                        
                        // COUNT THE NUMBER OF SUBMITTED ASSIGNMENTS FROM TASKS
                        $sql_tasks="SELECT * FROM tasks WHERE `Assignment_Code`='$selected_status' AND `File_Status`='approved' AND `Campus`='$my_campus'";
                        $result_tasks=mysqli_query($db,$sql_tasks);
                        if($result_tasks->num_rows > 0){
                            $is_selected = true;
                            $ttl_submitted_ass = mysqli_num_rows($result_tasks);
                        }
                        else{
                            $is_selected = false;
                            $ttl_submitted_ass = 0;
                        }
                        
                        // COUNT THE NUMBER OF OFFICES
                        $sql_off="SELECT * FROM usertype WHERE Status='Active' AND `Campus`='$my_campus'";
                        $result_off=mysqli_query($db,$sql_off);
                        $ttl_off = mysqli_num_rows($result_off);
                        
                        $missing_ass = $ttl_off - $ttl_submitted_ass;
                    ?>
                <?php 
                if(($is_selected == true || $is_selected == false) && $check_selected == true)
                {
                    // DISPLAY NUMBER OF OFFICES OVER FINISHED ASSIGNMENT
                    echo '<span style="width: 150px; margin-top: -10px; color: ; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Done : '.$ttl_submitted_ass.'</span>
                    <span style="width: 150px; margin-top: -10px; color: ; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Missing : '.$missing_ass.'</span>
                    <br><br><br>';
                } 
                ?>
                
                    <?php
                    // Include config file
                    require_once "config.php";
                    
                    // Attempt select query execution
                if($selected_status != ""){
                    $sql = "SELECT * FROM usertype WHERE Status='Active' AND Campus='$my_campus'";
                    if($result = $db->query($sql)){
                        if($result->num_rows > 0){
                            echo '<table class="table table-striped" id="myTable" style="font-size: 15px;">';
                                echo '<thead>';
                                    echo "<tr>";
                                        echo "<th>Department/Office (active)</th>";
                                        echo "<th>".$ass_title."</th>";
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = $result->fetch_array()){
                                    echo "<tr>";
                                        echo "<td>" . $row['Department/Office'] . "</td>";
                                        $dept_off = $row['Department/Office'];
                                         $sql2 = "SELECT * FROM tasks WHERE `Department/Office`='$dept_off' AND `Assignment_Code`='$selected_status' AND `Campus`='$my_campus'";
                                           if($result2 = $db->query($sql2)){
                                               if($result2->num_rows > 0){
                                                while($row2 = $result2->fetch_array()){
                                                    $stat2 = $row2['File_Status'];
                                                     if($stat2 == "approved"){
                                                         echo "<td style='color: #2fb344; font-family: Arial; font-weight: ;'>Done</td>";
                                                     }
                                                     else if($stat2 == "pending"){
                                                         echo "<td style='color: orange; font-family: Arial; font-weight: ;'>Pending</td>";
                                                     }
                                                     else if($stat2 == "returned"){
                                                         echo "<td style='color: #1AA7EC; font-family: Arial; font-weight: ;'>Returned</td>";
                                                     }
                                                }
                                               }
                                               else{
                                                echo "<td style='color: #d9534f; font-family: Arial; font-weight: ;'>Missing</td>";
                                                }
                                           }
                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            // Free result set
                            $result->free();
                        } else{
                            echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
                        }
                    } else{
                        echo "Oops! Something went wrong. Please try again later.";
                    }
                }
                else{
                    echo '<div class="alert alert-danger" style="text-align: left;"><em>Please select specific assignment to generate...</em></div>';
                }
                    
                    // Close connection
                    $db->close();
                    ?>
                </div>
            </div>        
        </div>
    </div>
    <!-- Script used to search data in table-->
    <script>
    function myFunction() {
        var input, filter, table, tr, td, i, txtValue;
        input = document.getElementById("myInput");
        filter = input.value.toUpperCase();
        table = document.getElementById("myTable");
        tr = table.getElementsByTagName("tr");
        for (i = 1; i < tr.length; i++) {
            td = tr[i];
            if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                    tr[i].style.display = "";
                } else {
                    tr[i].style.display = "none";
                }
            }       
         }
    }
    </script>
    
    <script>
        function ExportToExcel(type, fn, dl) {
            var elt = document.getElementById('myTable');
            var wb = XLSX.utils.table_to_book(elt, { sheet: "sheet1" });
            return dl ?
                XLSX.write(wb, { bookType: type, bookSST: true, type: 'base64' }) :
                XLSX.writeFile(wb, fn || ('<?php echo $ass_title; ?> - Monitoring.' + (type || 'xlsx')));
        }
    </script>

    <!-- Used to sort table using selection box -->
    <script>
        function sort_file_status(){
            document.getElementById("my-form").submit();
        }
    </script>
</body>
</html>