<?php 
ob_start();
session_start();
if(!isset($_SESSION['login'])){
  header('Location: ../../auth/login.php?pesan=belum_login');
}
else if($_SESSION['role'] != 'Admin'){
  header('Location: ../../auth/login.php?pesan=tolak_akses');
}
$judul = 'Rekap Bulanan Excel';
include_once('../../config.php');

require('../../assets/vendor/autoload.php');

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

$tahun_bulan = $_POST['filter_tahun'] .'-'. $_POST['filter_bulan'];
$result = mysqli_query($connection, "
  SELECT presensi.*, pegawai.nama, pegawai.lokasi_presensi, pegawai.nip 
  FROM presensi 
  JOIN pegawai ON pegawai.id = presensi.id_pegawai 
  WHERE DATE_FORMAT(tanggal_masuk, '%Y-%m') = '$tahun_bulan' 
  ORDER BY tanggal_masuk DESC
");

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

$sheet->setCellValue('A1', 'REKAP PRESENSI BULANAN');
$sheet->setCellValue('A2', 'Bulan');
$sheet->setCellValue('A3', 'Tahun');
$sheet->setCellValue('C2', $_POST['filter_bulan']);
$sheet->setCellValue('C3', $_POST['filter_tahun']);
$sheet->setCellValue('A5', 'NO');
$sheet->setCellValue('B5', 'NAMA');
$sheet->setCellValue('C5', 'NIP');
$sheet->setCellValue('D5', 'TANGGAL MASUK');
$sheet->setCellValue('E5', 'JAM MASUK');
$sheet->setCellValue('F5', 'FOTO MASUK');
$sheet->setCellValue('G5', 'TANGGAL KELUAR');
$sheet->setCellValue('H5', 'JAM KELUAR');
$sheet->setCellValue('I5', 'FOTO KELUAR');
$sheet->setCellValue('J5', 'TOTAL JAM KERJA');
$sheet->setCellValue('K5', 'TOTAL JAM TERLAMBAT');

$sheet->mergeCells('A1:K1');
$sheet->mergeCells('A2:B2');
$sheet->mergeCells('A3:B3');

$no = 1;
$row = 6;

while($data = mysqli_fetch_array($result)){
  // Hitung total jam kerja
  $jam_tanggal_masuk = date('Y-m-d H:i:s', strtotime($data['tanggal_masuk'].' '.$data['jam_masuk']));
  $jam_tanggal_keluar = date('Y-m-d H:i:s', strtotime($data['tanggal_masuk'].' '.$data['jam_keluar']));

  $timestamp_masuk = strtotime($jam_tanggal_masuk);
  $timestamp_keluar = strtotime($jam_tanggal_keluar);

  $selisih = $timestamp_keluar - $timestamp_masuk;
  $total_jam = floor($selisih / 3600);
  $selisih -= $total_jam * 3600;
  $selisih_menit = floor($selisih / 60);

  // Hitung keterlambatan
  $lokasi_presensi = $data['lokasi_presensi'];
  $lokasi = mysqli_query($connection, "SELECT * FROM lokasi_presensi WHERE nama_lokasi = '$lokasi_presensi'");
  while($lokasi_result = mysqli_fetch_array($lokasi)){
    $jam_masuk_kantor = date('H:i:s', strtotime($lokasi_result['jam_masuk']));
  }

  $jam_masuk = date('H:i:s', strtotime($data['jam_masuk']));
  $timestamp_jam_masuk_pegawai = strtotime($jam_masuk);
  $timestamp_jam_masuk_kantor = strtotime($jam_masuk_kantor);

  $terlambat = $timestamp_jam_masuk_pegawai - $timestamp_jam_masuk_kantor;
  $total_jam_terlambat = floor($terlambat / 3600);
  $terlambat -= $total_jam_terlambat * 3600;
  $selisih_menit_terlambat = floor($terlambat / 60);

  // Isi data teks
  $sheet->setCellValue('A'.$row, $no);
  $sheet->setCellValue('B'.$row, $data['nama']);
  $sheet->setCellValue('C'.$row, $data['nip']);
  $sheet->setCellValue('D'.$row, $data['tanggal_masuk']);
  $sheet->setCellValue('E'.$row, $data['jam_masuk']);
  $sheet->setCellValue('G'.$row, $data['tanggal_keluar']);
  $sheet->setCellValue('H'.$row, $data['jam_keluar']);

  // Total jam kerja
  if($data['tanggal_keluar'] == '0000-00-00'){
    $sheet->setCellValue('J'.$row, '0 jam 0 menit');
  } else {
    $sheet->setCellValue('J'.$row, $total_jam .' jam '. $selisih_menit . ' menit');
  }

  // Keterlambatan
  if($total_jam_terlambat < 0){
    $sheet->setCellValue('K'.$row, 'On Time');
  } else {
    $sheet->setCellValue('K'.$row, $total_jam_terlambat .' jam '. $selisih_menit_terlambat . ' menit');
  }

  // Tambahkan foto masuk
  if(!empty($data['foto_masuk']) && file_exists('../../pegawai/presensi/foto/'.$data['foto_masuk'])){
    $drawingMasuk = new Drawing();
    $drawingMasuk->setPath('../../pegawai/presensi/foto/'.$data['foto_masuk']);
    $drawingMasuk->setCoordinates('F'.$row);
    $drawingMasuk->setHeight(60);
    $drawingMasuk->setWorksheet($sheet);
  } else {
    $sheet->setCellValue('F'.$row, 'Tidak ada');
  }

  // Tambahkan foto keluar
  if(!empty($data['foto_keluar']) && file_exists('../../pegawai/presensi/foto/'.$data['foto_keluar'])){
    $drawingKeluar = new Drawing();
    $drawingKeluar->setPath('../../pegawai/presensi/foto/'.$data['foto_keluar']);
    $drawingKeluar->setCoordinates('I'.$row);
    $drawingKeluar->setHeight(60);
    $drawingKeluar->setWorksheet($sheet);
  } else {
    $sheet->setCellValue('I'.$row, 'Tidak ada');
  }

  // Tinggi baris agar muat gambar
  $sheet->getRowDimension($row)->setRowHeight(50);

  $no++;
  $row++;
}

// Atur lebar kolom agar rapi
foreach (range('A', 'K') as $col) {
  $sheet->getColumnDimension($col)->setAutoSize(true);
}

// Output Excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Laporan Presensi Bulanan.xlsx"');
header('Cache-Control: max-age=0');

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