<?php
session_start();
include('includes/config.php');
if (strlen($_SESSION['alogin']) == 0) {
  header('location:index.php');
  exit;
}

// Filter inputs
$bookname = $_GET['bookname'] ?? '';
$student = $_GET['student'] ?? '';
$status = $_GET['status'] ?? '';
$from = $_GET['from'] ?? '';
$to = $_GET['to'] ?? '';
?>

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8" />
  <title>Admin Book Report</title>
  <link href="assets/css/bootstrap.css" rel="stylesheet" />
  <link href="assets/css/font-awesome.css" rel="stylesheet" />
  <link href="assets/js/dataTables/dataTables.bootstrap.css" rel="stylesheet" />
  <link href="assets/css/style.css" rel="stylesheet" />
  <link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.dataTables.min.css" rel="stylesheet" />
</head>

<body>
  <?php include('includes/header.php'); ?>
  <div class="content-wrapper">
    <div class="container">
      <div class="row pad-botm">
        <div class="col-md-12">
          <h4 class="header-line">All Issued Book Reports (Admin)</h4>
        </div>
      </div>

      <!-- Filters -->
      <form method="get" class="row mb-3">
        <div class="col-md-3">
          <input type="text" name="bookname" class="form-control" placeholder="Book Name"
            value="<?= htmlentities($bookname) ?>">
        </div>
        <div class="col-md-3">
          <input type="text" name="student" class="form-control" placeholder="Student Name"
            value="<?= htmlentities($student) ?>">
        </div>
        <div class="col-md-2">
          <select name="status" class="form-control">
            <option value="">All Status</option>
            <option value="returned" <?= $status == 'returned' ? 'selected' : '' ?>>Returned</option>
            <option value="not_returned" <?= $status == 'not_returned' ? 'selected' : '' ?>>Not Returned</option>
          </select>
        </div>
        <div class="col-md-2">
          <input type="date" name="from" class="form-control" value="<?= htmlentities($from) ?>">
        </div>
        <div class="col-md-2">
          <input type="date" name="to" class="form-control" value="<?= htmlentities($to) ?>">
        </div>
        <div class="col-md-2 mt-2">
          <button class="btn btn-primary" type="submit">Filter</button>
        </div>
      </form>

      <!-- Report Table -->
      <div class="panel panel-default">
        <div class="panel-heading">Issued Book Records</div>
        <div class="panel-body">
          <div class="table-responsive">
            <table class="table table-bordered" id="reportTable">
              <thead>
                <tr>
                  <th>#</th>
                  <th>Student</th>
                  <th>Book</th>
                  <th>ISBN</th>
                  <th>Issued Date</th>
                  <th>Return Date</th>
                  <th>Fine</th>
                  <th>Status</th>
                </tr>
              </thead>
              <tbody>
                <?php
                $sql = "SELECT 
                        s.FullName, 
                        b.BookName, b.ISBNNumber, 
                        i.IssuesDate, i.ReturnDate, i.fine 
                      FROM tblissuedbookdetails i
                      JOIN tblstudents s ON s.StudentId = i.StudentId
                      JOIN tblbooks b ON b.id = i.BookId
                      WHERE 1";

                // Add filters
                if (!empty($bookname)) {
                  $sql .= " AND b.BookName LIKE :bookname";
                }
                if (!empty($student)) {
                  $sql .= " AND s.FullName LIKE :student";
                }
                if (!empty($status)) {
                  if ($status == "returned")
                    $sql .= " AND i.ReturnDate IS NOT NULL";
                  else if ($status == "not_returned")
                    $sql .= " AND i.ReturnDate IS NULL";
                }
                if (!empty($from)) {
                  $sql .= " AND i.IssuesDate >= :from";
                }
                if (!empty($to)) {
                  $sql .= " AND i.IssuesDate <= :to";
                }

                $sql .= " ORDER BY i.id DESC";
                $query = $dbh->prepare($sql);

                // Bind parameters
                if (!empty($bookname)) {
                  $bn = "%$bookname%";
                  $query->bindParam(':bookname', $bn, PDO::PARAM_STR);
                }
                if (!empty($student)) {
                  $sn = "%$student%";
                  $query->bindParam(':student', $sn, PDO::PARAM_STR);
                }
                if (!empty($from)) {
                  $query->bindParam(':from', $from, PDO::PARAM_STR);
                }
                if (!empty($to)) {
                  $query->bindParam(':to', $to, PDO::PARAM_STR);
                }

                $query->execute();
                $results = $query->fetchAll(PDO::FETCH_OBJ);
                $cnt = 1;

                foreach ($results as $row) {
                  echo "<tr>
                        <td>{$cnt}</td>
                        <td>" . htmlentities($row->FullName) . "</td>
                        <td>" . htmlentities($row->BookName) . "</td>
                        <td>" . htmlentities($row->ISBNNumber) . "</td>
                        <td>" . htmlentities($row->IssuesDate) . "</td>
                        <td>" . ($row->ReturnDate ? htmlentities($row->ReturnDate) : "<span style='color:red;'>Not Returned</span>") . "</td>
                        <td>" . ($row->fine ?? '-') . "</td>
                        <td>" . ($row->ReturnDate ? '<span style="color:green;">Returned</span>' : '<span style="color:orange;">Issued</span>') . "</td>
                      </tr>";
                  $cnt++;
                }
                ?>
              </tbody>
            </table>
          </div>
        </div>
      </div>

    </div>
  </div>

  <?php include('includes/footer.php'); ?>

  <!-- Scripts -->
  <script src="assets/js/jquery-1.10.2.js"></script>
  <script src="assets/js/bootstrap.js"></script>
  <script src="assets/js/dataTables/jquery.dataTables.js"></script>
  <script src="assets/js/dataTables/dataTables.bootstrap.js"></script>

  <!-- DataTables Buttons -->
  <script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.print.min.js"></script>
  <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>

  <script>
    $(document).ready(function () {
      $('#reportTable').DataTable({
        dom: 'Bfrtip',
        buttons: [
          { extend: 'excel', className: 'btn btn-success' },
          { extend: 'print', className: 'btn btn-info' }
        ]
      });
    });
  </script>

</body>

</html>