<?php


session_start();
include ('connect.php');
date_default_timezone_set("Asia/Calcutta"); 

if(empty($_SESSION['user_login'])){

	header("Location: login.php");
}else{
 
  if(date("m") <=  "03"){ 
    $startDate = (date('Y')-1).'-04-01';  
    $endDate =  date('Y').'-03-31';
    
  }else{
    $startDate = date('Y').'-04-01';  
    $endDate =  (date('Y')+1).'-03-31';    
  }

	$sql = "SELECT project_name,COUNT(id) as total,max(id) as id FROM requests WHERE delete_status=0 GROUP by project_name ORDER BY `id` DESC";    
    $result = mysqli_query($conn,$sql);
	
}
?>


<!DOCTYPE html>

<html
  lang="en"
  class="light-style layout-menu-fixed"
  dir="ltr"
  data-theme="theme-default"
  data-assets-path="assets/"
  data-template="vertical-menu-template-free"
>
  <head>
    <meta charset="utf-8" />
    <meta
      name="viewport"
      content="width=device-width, initial-scale=1.0, user-scalable=no, minimum-scale=1.0, maximum-scale=1.0"
    />

    <title>Tables - Basic Tables</title>

    <meta name="description" content="" />

    <!-- Favicon -->
    <link rel="icon" type="image/x-icon" href="assets/img/favicon/favicon.ico" />

    <!-- Fonts -->
    <link rel="preconnect" href="https://fonts.googleapis.com" />
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin />
    <link
      href="https://fonts.googleapis.com/css2?family=Public+Sans:ital,wght@0,300;0,400;0,500;0,600;0,700;1,300;1,400;1,500;1,600;1,700&display=swap"
      rel="stylesheet"
    />

   
    <link rel="stylesheet" href="assets/vendor/fonts/boxicons.css" />

    <!-- Core CSS -->
    <link rel="stylesheet" href="assets/vendor/css/core.css" class="template-customizer-core-css" />
    <link rel="stylesheet" href="assets/vendor/css/theme-default.css" class="template-customizer-theme-css" />
    <link rel="stylesheet" href="assets/css/demo.css" />

    <!-- Vendors CSS -->
    <link rel="stylesheet" href="assets/vendor/libs/perfect-scrollbar/perfect-scrollbar.css" />

    <script src="assets/vendor/js/helpers.js"></script>

    <script src="assets/js/config.js"></script>
  </head>

  <body>
    <!-- Layout wrapper -->
    <div class="layout-wrapper layout-content-navbar">
      
      <div class="layout-container">
        

        <!-- Layout container -->
        <div class="layout-page">
          <!-- Navbar -->

         <?php include('navbar.php'); ?>

          <!-- / Navbar -->

          <!-- Content wrapper -->
          <div class="content-wrapper">
            <!-- Content -->

            <div class="container-xxl flex-grow-1 container-p-y">

            <div class="card" style="width: 25%;">
            <input type="text" class="form-control" name="search_keyword" required placeholder="Search Your Projects..." id="myInput" onkeyup="myFunction();" style="padding: 10px 10px;">
                                
            </div>
            <br>

              
              <!-- Basic Bootstrap Table -->
              <div class="card">
               <!--  <h5 class="card-header">Table Basic</h5> -->
                <div class="table-responsive text-nowrap">
                  <table class="table" id="myTable">
                    <thead>                   
                      <tr>
                        <th>Project</th>
                        <th>Today</th>
                        <th>Week</th>
                        <th>Month</th>
                        <th>Year</th>
                        <th>All</th>
                        <th><a href="spam.php?project_name=all"> Spam </a></th>
                        <th>Spam (%)</th>
                      </tr>
                    
                    </thead>
                    <tbody class="table-border-bottom-0">

                    	<?php 
                        while($row=mysqli_fetch_array($result))
                        {?>

	                      <tr>
	                        <td><strong><?php echo $row['project_name'];?></strong></td>
	                        <td>
	                        	<?php

	                        	$sql = "SELECT count(id) as total FROM requests where project_name ='".$row['project_name']."' and request_date = '".date('Y-m-d')."' and delete_status=0"; 	                        	
                                $todayResults = mysqli_query($conn,$sql);
                                $todayCount = mysqli_fetch_assoc($todayResults);
                                echo $todayCount['total'];

	                        	?>
	                        	
	                        </td>
	                        <td>
	                           <?php

	                        	$sql = "SELECT count(id) as total FROM requests where project_name ='".$row['project_name']."' and WEEK(NOW()) = WEEK(request_date) and delete_status=0"; 	                        	
                                $weekResults = mysqli_query($conn,$sql);
                                $weekCount = mysqli_fetch_assoc($weekResults);
                                echo $weekCount['total'];

	                        	?>
	                        </td>

	                        <td>
	                           <?php

	                        	$sql = "SELECT count(id) as total FROM requests where project_name ='".$row['project_name']."' and MONTH(NOW()) = MONTH(request_date) and YEAR(NOW()) = YEAR(request_date) and delete_status=0"; 	                        	
                                $monthResults = mysqli_query($conn,$sql);
                                $monthCount = mysqli_fetch_assoc($monthResults);
                                echo $monthCount['total'];

	                        	?>
	                        </td>

	                        <td>
	                           <?php

	                        	$sql = "SELECT count(id) as total FROM requests where project_name ='".$row['project_name']."' and request_date >='".$startDate."' and request_date <='".$endDate."' and delete_status=0"; 	                        	
                                $yearResults = mysqli_query($conn,$sql);
                                $yearCount = mysqli_fetch_assoc($yearResults);
                                echo $yearCount['total'];

                                
	                        	?>
	                        </td>

	                        <td>
	                           <?php                     	
                             
                                echo '<a href="all.php?project_name='.$row['project_name'].'">'.$row['total'].'</a>';
	                        	?>
	                        </td>


	                        <td>
	                           <?php

	                        	$sql = "SELECT count(id) as total FROM requests where project_name ='".$row['project_name']."' and span_status = '1' and delete_status=0"; 	                        	
                                $spantotalResults = mysqli_query($conn,$sql);
                                $spantotalCount = mysqli_fetch_assoc($spantotalResults);

                                echo '<a href="spam.php?project_name='.$row['project_name'].'">'.$spantotalCount['total'].'</a>';

	                        	?>
	                        </td>

                          <td>
                          <?php echo round(($spantotalCount['total']/$row['total']*100),2)."%"; ?>
                          </td>

	                        
	                      </tr>

                       <?php } ?>
                      
                     
                    </tbody>
                  </table>
                </div>
              </div>
          

            </div>
            <!-- / Content -->


            <div class="content-backdrop fade"></div>
          </div>
          <!-- Content wrapper -->
        </div>
        <!-- / Layout page -->
      </div>

      
    </div>
    

    <script src="assets/vendor/libs/jquery/jquery.js"></script>
    <script src="assets/vendor/libs/popper/popper.js"></script>
    <script src="assets/vendor/js/bootstrap.js"></script>
    <script src="assets/vendor/libs/perfect-scrollbar/perfect-scrollbar.js"></script>

    <script src="assets/vendor/js/menu.js"></script>
    <!-- endbuild -->

    <!-- Vendors JS -->

    <!-- Main JS -->
    <script src="assets/js/main.js"></script>

    <!-- Page JS -->

    <!-- Place this tag in your head or just before your close body tag. -->
    <script async defer src="https://buttons.github.io/buttons.js"></script>
  </body>

  <script>
     function myFunction() {
               //alert("hello");
              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 = 0; i < tr.length; i++) {
              td = tr[i].getElementsByTagName("td")[3];
              if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                tr[i].style.display = "";
                } else {
                tr[i].style.display = "none";
                td = tr[i].getElementsByTagName("td")[4];
              if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                tr[i].style.display = "";
                } else {
                tr[i].style.display = "none";
                td = tr[i].getElementsByTagName("td")[2];

               if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                tr[i].style.display = "";
                } else {
                tr[i].style.display = "none";
                td = tr[i].getElementsByTagName("td")[5];
                  if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                tr[i].style.display = "";
                } else {
                tr[i].style.display = "none";
                td = tr[i].getElementsByTagName("td")[0];
                   if (td) {
                txtValue = td.textContent || td.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                tr[i].style.display = "";
                } else {
                tr[i].style.display = "none";
               

                }
              }

                }
              }
                }
              }




                }
              }
                }
              }       
              }
           }
  </script>
</html>

