export-excel.php

<?php
include 'security-check.php';
include 'db_connect.php';

if (!isset($_SESSION['staff_user'])) die("Unauthorized!");

$is_admin    = ($_SESSION['role'] == 'Admin');
$user_hostel = $_SESSION['hostel_type'] ?? NULL;
$user_block  = $_SESSION['block_name']  ?? NULL;

// ── Filters ─────────────────────────────────────────────────
$pass_type     = isset($_GET['pass_type'])    ? mysqli_real_escape_string($conn, $_GET['pass_type'])    : '';
$status_filter = isset($_GET['status'])       ? mysqli_real_escape_string($conn, $_GET['status'])       : '';
$hostel_filter = isset($_GET['hostel'])       ? mysqli_real_escape_string($conn, $_GET['hostel'])       : '';
$block_filter  = isset($_GET['block'])        ? mysqli_real_escape_string($conn, $_GET['block'])        : '';
$start_date    = isset($_GET['start_date'])   ? mysqli_real_escape_string($conn, $_GET['start_date'])   : date('Y-m-01');
$end_date      = isset($_GET['end_date'])     ? mysqli_real_escape_string($conn, $_GET['end_date'])     : date('Y-m-t');

// ── Build Query ──────────────────────────────────────────────
$query = "SELECT * FROM extended_logs WHERE out_date BETWEEN '$start_date' AND '$end_date'";
if (!$is_admin && $user_hostel && $user_block) {
    $query .= " AND ((hostel_type='$user_hostel' AND block_name='$user_block') OR (pass_type='FV' AND hostel_type='$user_hostel'))";
} elseif ($is_admin) {
    if ($hostel_filter) $query .= " AND hostel_type='$hostel_filter'";
    if ($block_filter)  $query .= " AND block_name='$block_filter'";
}
if ($pass_type)     $query .= " AND pass_type='$pass_type'";
if ($status_filter) $query .= " AND status='$status_filter'";
$query .= " ORDER BY out_date ASC, out_time ASC";

$result        = mysqli_query($conn, $query);
$total_records = mysqli_num_rows($result);

// ── FV check ────────────────────────────────────────────────
$is_fv_only = ($pass_type === 'FV'); // Only pure FV filter hides student cols

// ── Filename ─────────────────────────────────────────────────
$pass_names = ['EX'=>'Extended','DP'=>'DayPass','FV'=>'FacultyVisit'];
$filename   = "Movement_Report";
if ($hostel_filter) $filename .= "_" . str_replace(' ','',$hostel_filter);
if ($block_filter)  $filename .= "_" . str_replace(' ','',$block_filter);
if ($pass_type)     $filename .= "_" . ($pass_names[$pass_type] ?? $pass_type);
if ($status_filter) $filename .= "_" . $status_filter;
$filename .= "_" . date('d-M-Y', strtotime($start_date)) . "_to_" . date('d-M-Y', strtotime($end_date)) . ".xls";

// ── Headers ──────────────────────────────────────────────────
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");

// ── Report Title ─────────────────────────────────────────────
echo "YOUR UNIVERSITY - HOSTEL MOVEMENT REPORT\n";
echo "Generated On: " . date('d-M-Y h:i A') . "\n";
echo "Period: " . date('d-M-Y', strtotime($start_date)) . " to " . date('d-M-Y', strtotime($end_date)) . "\n";
$filter_info = [];
if ($hostel_filter) $filter_info[] = "Hostel: $hostel_filter";
if ($block_filter)  $filter_info[] = "Block: $block_filter";
if ($pass_type) {
    $pn = ['EX'=>'Extended','DP'=>'Day Pass','FV'=>'Faculty Visit'];
    $filter_info[] = "Pass Type: " . ($pn[$pass_type] ?? $pass_type);
}
if ($status_filter) $filter_info[] = "Status: $status_filter";
if (!empty($filter_info)) echo "Filters: " . implode(", ", $filter_info) . "\n";
echo "Total Records: $total_records\n\n";

// ── Column Headers ───────────────────────────────────────────
echo "SL\t";
echo "Serial No\t";
echo "Name\t";
if (!$is_fv_only) {
    echo "Roll No\t";
    echo "Class\t";
    echo "Year\t";
}
echo "Hostel\t";
echo "Block\t";
echo "Phone\t";
if (!$is_fv_only) {
    echo "Parent Name\t";
    echo "Parent Phone\t";
    echo "District\t";
}
echo ($is_fv_only ? "Visit Date\t" : "Out Date\t");
echo ($is_fv_only ? "Entry Time\t" : "Exit Time\t");
echo ($is_fv_only ? "Expected Out\t" : "Return Time\t");
if (!$is_fv_only) {
    echo "Return Date\t";
    echo "Expected Return\t";
}
echo "Reason\t";
echo "Teacher\t";
echo "Supervisor\t";
echo "Pass Type\t";
echo "Status\n";

// ── Data Rows ────────────────────────────────────────────────
$sl = 1;
if ($total_records > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        $is_fv = ($row['pass_type'] === 'FV');
        $pass_full = ['EX'=>'Extended','DP'=>'Day Pass','FV'=>'Faculty Visit'];

        echo $sl++ . "\t";
        echo ($row['serial_no']    ?: '-') . "\t";
        echo ($row['student_name'] ?: '-') . "\t";
        if (!$is_fv_only) {
            echo ($row['roll_no']      ?: '-') . "\t";
            echo ($row['class_name']   ?: '-') . "\t";
            echo ($row['academic_year']?: '-') . "\t";
        }
        echo ($row['hostel_type']  ?: '-') . "\t";
        echo ($row['block_name']   ?: '-') . "\t";
        echo ($row['phone_no']     ?: '-') . "\t";
        if (!$is_fv_only) {
            echo ($row['parent_name']  ?: '-') . "\t";
            echo ($row['parent_phone'] ?: '-') . "\t";
            echo ($row['district']     ?: '-') . "\t";
        }
        echo date('d-M-Y', strtotime($row['out_date'])) . "\t";
        echo date('h:i A', strtotime($row['out_time'])) . "\t";
        echo ($row['in_time'] ? date('h:i A', strtotime($row['in_time'])) : 'Pending') . "\t";
        if (!$is_fv_only) {
            echo ($row['in_date'] ? date('d-M-Y', strtotime($row['in_date'])) : '-') . "\t";
            echo (($row['pass_type']=='EX' && $row['expected_return_date']) ? date('d-M-Y', strtotime($row['expected_return_date'])) : '-') . "\t";
        }
        echo ($row['reason']          ?: '-') . "\t";
        echo ($row['teacher_name']    ?: '-') . "\t";
        echo ($row['supervisor_name'] ?: '-') . "\t";
        echo ($pass_full[$row['pass_type']] ?? $row['pass_type']) . "\t";
        echo ($row['status'] ?: '-') . "\n";
    }
} else {
    echo "\nNo records found.\n";
}

echo "\nGenerated by: " . ($_SESSION['staff_user'] ?? 'System') . "\n";
echo "Role: " . ($_SESSION['role'] ?? 'N/A') . "\n";
mysqli_close($conn);
?>