<?php
    include("config.php");
    session_start();
    $my_campus = $_SESSION['campus'];
    $selected_year = date("Y");
    if(isset($_POST['client_id'])){  
        $selected_year = mysqli_real_escape_string($db,$_POST['client_id']);
        $check_selected = true;
    }
    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;
}

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

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

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

#tbl_exporttable_to_xls tr.header, #tbl_exporttable_to_xls 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">
                <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>
                    
                    <?php
                    //     include("config.php");
                        
                    //     // COUNT THE NUMBER OF DONE ASSIGNMENTS FROM TASKS
                    //     $sql_tasks="SELECT * FROM tasks WHERE `File_Status`='approved' AND `Campus`='$my_campus'";
                    //     $result_tasks=mysqli_query($db,$sql_tasks);
                    //     $ttl_submitted_ass = mysqli_num_rows($result_tasks);
                        
                    //     // COUNT THE NUMBER OF PENDING ASSIGNMENTS FROM TASKS
                    //     $sql_tasks1="SELECT * FROM tasks WHERE `File_Status`='pending' AND `Campus`='$my_campus'";
                    //     $result_tasks1=mysqli_query($db,$sql_tasks1);
                    //     $pending_ass = mysqli_num_rows($result_tasks1);
                        
                    //     // COUNT THE NUMBER OF RETURNED ASSIGNMENTS FROM TASKS
                    //     $sql_tasks2="SELECT * FROM tasks WHERE `File_Status`='returned' AND `Campus`='$my_campus'";
                    //     $result_tasks2=mysqli_query($db,$sql_tasks2);
                    //     $returned_ass = mysqli_num_rows($result_tasks2);
                        
                    //     // COUNT THE NUMBER OF UNSUBMITTED ASSIGNMENTS FROM TASKS
                    //     $sql_tasks3="SELECT * FROM tasks WHERE `File_Status`='unsubmitted' AND `Campus`='$my_campus'";
                    //     $result_tasks3=mysqli_query($db,$sql_tasks3);
                    //     $unsubmitted_ass = mysqli_num_rows($result_tasks3);
                        
                    //     // COUNT THE NUMBER OF ALL ACTIVE OFFICES FROM TASKS
                    //     $sql_miss="SELECT * FROM usertype WHERE Status='Active' AND `Campus`='$my_campus'";
                    //     $result_miss=mysqli_query($db,$sql_miss);
                    //     $ttl_usertype = mysqli_num_rows($result_miss);

                        
                    //     //SELECT ALL REQUIREMENTS BY CAMPUS
                    //     $sql_all_req = "SELECT * FROM requirements WHERE Campus='$my_campus'";
                    //     $result_all_req=mysqli_query($db,$sql_all_req);
                    //     $ttl_all_req = mysqli_num_rows($result_all_req);
                        
                    //     // CALCULATE THE MISSING ASSIGNMENTS
                    //     $m = $ttl_usertype * $ttl_all_req;
                    //     $ttl_work = $ttl_submitted_ass + $pending_ass + $returned_ass;
                    //     $missing_ass = $m - $ttl_work;
                    // ?>
                <?php 
                    // DISPLAY NUMBER OF DONE AND MISSING ASSIGNMENTS
                    // echo '<span style="width: 150px; margin-top: -10px; color: black; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Done : '.$ttl_submitted_ass.'</span>
                    // <span style="width: 150px; margin-top: -10px; color: black; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Pending : '.$pending_ass.'</span>
                    // <span style="width: 150px; margin-top: -10px; color: black; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Returned : '.$returned_ass.'</span>
                    // <span style="width: 150px; margin-top: -10px; color: black; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Unsubmitted : '.$unsubmitted_ass.'</span>
                    // <span style="width: 150px; margin-top: -10px; color: black; background-color: #EBECF0; padding: 5px; border-radius: 5px;">Missing : '.$missing_ass.'</span>
                    // <br><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>
                <br><br><br>
                <p style="font-size: 17px; margin-top: 20px; float: left;">Year</p>
                    <form id="my-form" action='monitoring_overall.php?' method="post">
                        <select onchange="sort_file_status()" name="client_id" ID="mySelect" class="field input" style="width: 150px; float: left; border-color: white; border-radius: 5px; background-color: white; border: 2px solid #e8e8e8; height: 40px; font-size: 15px; font-weight: ; margin-top: 13px; margin-left: 10px; padding:5px;" required>
                        <?php
                            $sql3 = "SELECT * FROM `Year`";
                            if($result3 = $db->query($sql3)){
                                while($row3 = $result3->fetch_array()){
                                    $year_from_db = $row3['Year'];
                                    
                                    if($year_from_db == $selected_year){
                                        echo '<option value="'.$year_from_db.'" selected>
                                        '.$year_from_db.'
                                    </option>';
                                    }
                                    else{
                                        echo '<option value="'.$year_from_db.'">'.$year_from_db.'</option>';
                                    }
                                    
                                }
                            }
                        ?>
                        </select>
                    </form>
                    <br><br>  
                    <?php
                    // Include config file
                    require_once "config.php";
                    
                    // GET ALL USERTYPE or OFFICES
                    $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="tbl_exporttable_to_xls" style="font-size: 15px;">';
                                echo '<thead style="background-color: #e0e0e0;">';
                                    echo "<tr>";
                                        echo "<th>Department/Office (active)</th>";
                                        
                                        // GET ALL TITLE of ASSIGNMENT PER CAMPUS
                                        $sql_req = "SELECT * FROM requirements WHERE Campus='$my_campus'";
                                        $result_req = $db->query($sql_req);
                                        while($row_req = $result_req->fetch_array()){
                                            echo "<th>".$row_req['Title']."</th>";
                                        }
                                        
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = $result->fetch_array()){
                                    echo "<tr>";
                                        echo "<td style='font-weight: normal;'>" . $row['Department/Office'] . "</td>";
                                        $dept_off = $row['Department/Office'];
                                            
                                            //SELECT AGAIN THE REQUIREMENTS 
                                            $sql_req2 = "SELECT * FROM requirements WHERE Campus='$my_campus' AND Year='$selected_year'";
                                            $result_req2 = $db->query($sql_req2);
                                            while($row_req2 = $result_req2->fetch_array()){
                                                $id_req = $row_req2['ID'];
                                                // CHECK IF THE OFFICE SUBMIT THE REQUIREMENTS
                                                 $sql2 = "SELECT * FROM tasks WHERE `Department/Office`='$dept_off' AND `Campus`='$my_campus' AND `Assignment_Code`='$id_req' ";
                                                   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: normal;'>Done</td>";
                                                             }
                                                             else if($stat2 == "pending"){
                                                                 echo "<td style='color: orange; font-family: Arial; font-weight: normal;'>Pending</td>";
                                                             }
                                                             else if($stat2 == "returned"){
                                                                 echo "<td style='color: #1AA7EC; font-family: Arial; font-weight: normal;'>Returned</td>";
                                                             }
                                                        }
                                                       }
                                                       else{
                                                        echo "<td style='color: #d9534f; font-family: Arial; font-weight: normal;'>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.";
                    }
                    
                    // 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("tbl_exporttable_to_xls");
        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('tbl_exporttable_to_xls');
            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 || ('General Monitoring.' + (type || 'xlsx')));
        }
    </script>
    
        <!-- Used to sort table using selection box -->
    <script>
        function sort_file_status(){
            document.getElementById("my-form").submit();
        }
    </script>
</body>
</html>