<?php


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

$limit = 20;

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
if ($page < 1) $page = 1;

$offset = ($page - 1) * $limit;


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';    
  }
    $selectedSource = isset($_GET['source']) ? $_GET['source'] : '';
    $whereStr = '';
    
    if (!empty($selectedSource)) {
        $selectedSource = htmlspecialchars($selectedSource, ENT_QUOTES);
        $whereStr = " AND utm_source = '" . $selectedSource . "'";
    }


$sql = "SELECT project_name,COUNT(id) as total,max(id) as id 
        FROM requests 
        WHERE delete_status=0 $whereStr 
        GROUP BY project_name 
        ORDER BY id DESC 
        LIMIT $limit OFFSET $offset";

    $result = mysqli_query($conn,$sql);
    
    $countSql = "
SELECT COUNT(DISTINCT project_name) AS total
FROM requests
WHERE delete_status=0 $whereStr
";


$countResult = mysqli_query($conn, $countSql);
$countRow = mysqli_fetch_assoc($countResult);

$totalPages = ceil($countRow['total'] / $limit);


    
    
	
}

?>


<!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="row g-3 align-items-center">

    <!-- Search Input -->
    <div class="col-auto">
      <div class="card p-2" style="width: 100%; max-width: 300px;">
        <input type="text" class="form-control" name="search_keyword" required placeholder="Search Your Projects..." id="myInput" onkeyup="myFunction();">
      </div>
    </div>

     
    <div class="col-auto">
      <select id="sourceSelect" class="form-select" style="min-width: 200px;">
        <option value="">Select Source</option>
        <option value="xd" <?= $selectedSource == 'xd' ? 'selected' : '' ?>>XD</option>
        <option value="xdd" <?= $selectedSource == 'xdd' ? 'selected' : '' ?>>XDD</option>
        <option value="xdp" <?= $selectedSource == 'xdp' ? 'selected' : '' ?>>XDP</option>
        <option value="xddaw" <?= $selectedSource == 'xddaw' ? 'selected' : '' ?>>XDDAW</option>
        <option value="xdaw" <?= $selectedSource == 'xdaw' ? 'selected' : '' ?>>XDAW</option>
        <option value="xdpl" <?= $selectedSource == 'xdpl' ? 'selected' : '' ?>>XDPL</option>
        <option value="xddpl" <?= $selectedSource == 'xddpl' ? 'selected' : '' ?>>XDDPL</option>
        <option value="BTS DMXD" <?= $selectedSource == 'BTS DMXD' ? 'selected' : '' ?>>BTS DMXD</option>
        <option value="BTS XD" <?= $selectedSource == 'BTS XD' ? 'selected' : '' ?>>BTS XD</option>
        <option value="BTS XDD" <?= $selectedSource == 'BTS XDD' ? 'selected' : '' ?>>BTS XDD</option>
        <option value="Digital Marketing XD" <?= $selectedSource == 'Digital Marketing XD' ? 'selected' : '' ?>>Digital Marketing XD</option>
      </select>
    </div>
<!--<a href="export_excel.php<?= !empty($selectedSource) ? '?source=' . $selectedSource : '' ?>" class="btn btn-success mb-3">-->
<!--  📥 Download Excel-->
<!--</a>-->

</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>Yesterday</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
	$yesterday = date('Y-m-d', strtotime('-1 day'));
	$sql = "SELECT count(id) as total FROM requests WHERE project_name = '".$row['project_name']."' AND request_date = '$yesterday' AND delete_status = 0";
	$yesterdayResults = mysqli_query($conn, $sql);
	$yesterdayCount = mysqli_fetch_assoc($yesterdayResults);
	echo $yesterdayCount['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>
               <?php if ($totalPages > 1) { ?>
<nav class="mt-3">
<ul class="pagination justify-content-end">

<li class="page-item <?= ($page <= 1) ? 'disabled' : '' ?>">
<a class="page-link" href="?page=<?= $page-1 ?>&source=<?= $selectedSource ?>">Prev</a>
</li>

<?php
$start = max(1, $page - 2);
$end   = min($totalPages, $page + 2);

for ($i = $start; $i <= $end; $i++) {
?>
<li class="page-item <?= ($page == $i) ? 'active' : '' ?>">
<a class="page-link" href="?page=<?= $i ?>&source=<?= $selectedSource ?>">
<?= $i ?>
</a>
</li>
<?php } ?>

<li class="page-item <?= ($page >= $totalPages) ? 'disabled' : '' ?>">
<a class="page-link" href="?page=<?= $page+1 ?>&source=<?= $selectedSource ?>">Next</a>
</li>

</ul>
</nav>
<?php } ?>


                </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>
  
    <script>
      document.getElementById('sourceSelect').addEventListener('change', function () {
        const source = this.value;
    
        // Create a new URL object from current location
        const url = new URL(window.location.href);
    
        if (source) {
          url.searchParams.set('source', source); // Add or update source param
        } else {
          url.searchParams.delete('source'); // Remove param if none selected
        }
    
        window.location.href = url.toString(); // Redirect with new URL
      });
    
      // Optional: Pre-select the dropdown if source param already exists
      window.addEventListener('DOMContentLoaded', function () {
        const urlParams = new URLSearchParams(window.location.search);
        const currentSource = urlParams.get('source');
        if (currentSource) {
          document.getElementById('sourceSelect').value = currentSource;
        }
      });
    </script>

</html>

