<?php 
  session_start();
  if(empty($_SESSION['branch']) || $_SESSION['branch'] == ''){
    header("Location: index.php");
    die();
  }
  require_once 'system/conn.php';
?>
<!doctype html>
<html lang="en" class="semi-dark">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="assets/css/pace.min.css" rel="stylesheet" />
    <script src="assets/js/pace.min.js"></script>
    <link href="assets/plugins/perfect-scrollbar/css/perfect-scrollbar.css" rel="stylesheet" />
    <link href="assets/plugins/simplebar/css/simplebar.css" rel="stylesheet" />
    <link href="assets/plugins/metismenu/css/metisMenu.min.css" rel="stylesheet" />
    <link href="assets/plugins/datatable/css/dataTables.bootstrap5.min.css" rel="stylesheet" />
    <link href="assets/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/bootstrap-extended.css" rel="stylesheet">
    <link href="assets/css/style.css" rel="stylesheet">
    <link href="assets/css/icons.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.7.2/font/bootstrap-icons.css">
    <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@400;500&display=swap" rel="stylesheet">
    <link href="assets/css/dark-theme.css" rel="stylesheet" />
    <link href="assets/css/semi-dark.css" rel="stylesheet" />
    <link href="assets/css/header-colors.css" rel="stylesheet" />
    <title>All Advance Records</title>
  </head>
  <body>
    <div class="wrapper">
    <?php include 'system/header.php'?>
      <div class="page-content-wrapper">
         <div class="page-content">
          <div class="page-breadcrumb d-none d-sm-flex align-items-center mb-3">
            <div class="breadcrumb-title pe-3">All Advance Records</div>
            <div class="ms-auto">
              <a href="excel-advance.php"><button type="button" class="btn btn-primary px-5">Excel Download</button></a>
            </div>
          </div>
          <hr/>
          
          <div class="card">
            <div class="card-body">
              <div class="table-responsive">
                <table id="example2" class="table table-striped table-bordered">
                  <thead>
                    <tr>
                        <th>SR No</th>
                        <th>Emp Id</th>
                        <th>Employee Name</th>
                        <th>Date</th>
                        <th>Advance Taken</th>
                        <th>Amount Deduct</th>
                        <th>Amount Left</th>
                    </tr>
                  </thead>
                  <tbody>
                    <?php
                        $cnt = 1;
                        $branch_id = $_SESSION['branch'];

                        // OPTIMIZED QUERY:
                        // 1. Selects ALL records (No Date Filter)
                        // 2. Joins emp_details to get name in one go
                        // 3. Orders by Date DESC (Latest first)
                        $sql = "SELECT a.*, e.e_name 
                                FROM advance a
                                LEFT JOIN emp_details e ON a.e_id = e.e_id AND a.branch = e.branch
                                WHERE a.branch = :branch AND a.status = 1
                                ORDER BY a.date DESC, a.adv_id DESC";
                        
                        $stmt = $conn->prepare($sql);
                        $stmt->execute([':branch' => $branch_id]);
                        
                        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                    ?>
                    <tr>
                        <td><?php echo $cnt++;?></td>
                        <td><?php echo $row['e_id'];?></td>
                        <td><?php echo $row['e_name'];?></td> <td><?php echo date('d-M-Y', strtotime($row['date'])); ?></td>
                        <td>
                            <?php 
                              // Existing Logic: If manual balance is 0, it's a new advance taken
                              if($row['manual_balance'] == 0){
                                 echo $row['amount']; 
                              } else {
                                 echo '0';
                              }
                            ?>
                        </td>
                        <td><?php echo $row['manual_balance']; ?></td>
                        <td><?php echo $row['remaining_amount']; ?></td>
                    </tr>
                    <?php } ?>
                  </tbody>
                  <tfoot>
                    <tr>
                        <th>SR No</th>
                        <th>Emp Id</th>
                        <th>Employee Name</th>
                        <th>Date</th>
                        <th>Advance Taken</th>
                        <th>Amount Deduct</th>
                        <th>Amount Left</th>
                    </tr>
                  </tfoot>
                </table>
              </div>
            </div>
          </div>

         </div>
         </div>
    </div>
<?php include 'system/footer.php'?>