<?php 
//cheque-sheet.php
session_start();
if(empty($_SESSION['branch']) || $_SESSION['branch'] == ''){
    header("Location: index.php");
    die();
} 
require_once 'system/conn.php';

// 1. Centralized Date Logic
$dateObj = new DateTime('first day of last month');
$month   = $dateObj->format('m');
$year    = $dateObj->format('Y');
$monthName = $dateObj->format('F');
$branch  = $_SESSION['branch'];
?>
<!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>Current Cheque Sheet (<?php echo $monthName;?>-<?php echo $year;?>)</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">
                Current Cheque Sheet (<?php echo $monthName;?>-<?php echo $year;?>)
            </div>
            <div class="ms-auto">
                <a href="excel-cheque-sheet.php" class="btn btn-primary px-5" style="margin-top: 5px;">Excel download</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>Employee Id</th>
                                        <th>Employee Name</th>
                                        <th>Designation</th>
                                        <th>Account Number</th>
                                        <th>Amount</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    // 2. Secure PDO Query (Performance Optimized)
                                    // Using ? placeholders prevents SQL Injection
                                    // Using fetch() instead of fetchAll() saves memory for large lists
                                    
                                    $sql = "SELECT `emp_id`, `name`, `designation`, `ac_no`, `net_pay` 
                                            FROM `emp_salary` 
                                            WHERE `branch`=? AND `month`=? AND `year`=? AND (`cheque`='yes' OR `ac_no`='' OR `ac_no`='NA') 
                                            
                                            UNION ALL
                                            
                                            SELECT '', 'z','','TOTAL', SUM(`net_pay`) 
                                            FROM `emp_salary` 
                                            WHERE `branch`=? AND `month`=? AND `year`=? AND (`cheque`='yes' OR `ac_no`='' OR `ac_no`='NA') 
                                            
                                            ORDER BY `name` ASC";
                                    
                                    $stmt = $conn->prepare($sql);
                                    // We pass the parameters twice because there are 2 SELECT statements
                                    $stmt->execute([$branch, $month, $year, $branch, $month, $year]);
                                    
                                    $cnt = 1;
                                    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
                                    ?>
                                    <tr>
                                        <td><?php echo $cnt;?></td>
                                        <td><?php echo $row['emp_id'];?></td>
                                        <td><?php echo $row['name'];?></td>
                                        <td><?php echo $row['designation']?></td>
                                        <td><?php echo $row['ac_no']?></td>
                                        <td><?php echo $row['net_pay']?></td>
                                    </tr>
                                    <?php 
                                        $cnt++; 
                                    } 
                                    ?>
                                </tbody>
                                <tfoot>
                                    <tr>
                                        <th>SR No</th>
                                        <th>Employee Id</th>
                                        <th>Employee Name</th>
                                        <th>Designation</th>
                                        <th>Account Number</th>
                                        <th>Amount</th>
                                    </tr>
                                </tfoot>
                            </table>
                        </div>
                    </div>
                </div>
          </div>
          </div>
<?php include 'system/footer.php'; ?>