<?php
ob_start();

require 'vendor/autoload.php';
include 'koneksi.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// tanggal
$dari   = $_GET['dari'] ?? date('Y-m-d');
$sampai = $_GET['sampai'] ?? date('Y-m-d');

$excel = new Spreadsheet();
$sheet = $excel->getActiveSheet();

// Judul
$sheet->setCellValue('A1', 'LAPORAN KETERLAMBATAN');
$sheet->setCellValue('A2', "Tanggal: $dari s/d $sampai");

// Header
$sheet->setCellValue('A4', 'Nama');
$sheet->setCellValue('B4', 'Kelas');
$sheet->setCellValue('C4', 'Tanggal');
$sheet->setCellValue('D4', 'Jam');
$sheet->setCellValue('E4', 'Menit');
$sheet->setCellValue('F4', 'Alasan');

// Data
$row = 5;

$data = $koneksi->query("
SELECT k.*, s.tingkat, s.jurusan, s.rombel
FROM keterlambatan k
LEFT JOIN data_siswa s ON k.siswa_id = s.id
WHERE k.tanggal BETWEEN '$dari' AND '$sampai'
");

while($d = $data->fetch_assoc()){
    $kelas = $d['tingkat'].' '.$d['jurusan'].' '.$d['rombel'];

    $sheet->setCellValue("A$row", $d['nama_siswa']);
    $sheet->setCellValue("B$row", $kelas);
    $sheet->setCellValue("C$row", $d['tanggal']);
    $sheet->setCellValue("D$row", $d['jam_datang']);
    $sheet->setCellValue("E$row", $d['menit_terlambat']);
    $sheet->setCellValue("F$row", $d['alasan']);
    $row++;
}

// Auto width
foreach(range('A','F') as $col){
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

ob_end_clean();

$filename = "keterlambatan.xlsx";

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");
header('Cache-Control: max-age=0');

$writer = new Xlsx($excel);
$writer->save('php://output');
exit;