<?php
ob_start();
$conn = new mysqli("localhost", "root", "", "klinik");
if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}

date_default_timezone_set("Asia/Pontianak");
$bulanIni = (int)date('m');
$tahunIni = (int)date('Y');
$jenisLaporan = 'obat_masuk';


$konten = ob_get_contents(); 

$cek = $conn->query("SELECT 1 FROM arsip WHERE jns_laporan='$jenisLaporan' AND bulan=$bulanIni AND thn=$tahunIni");
if ($cek && $cek->num_rows === 0) {
    $stmt = $conn->prepare("INSERT INTO arsip (jns_laporan, bulan, thn, dt_laporan) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("siis", $jenisLaporan, $bulanIni, $tahunIni, $konten);
    $stmt->execute();
    $stmt->close();
}


$jenis = 'laporan_obat';

$stmt = $conn->prepare("SELECT id FROM konfigurasi_laporan WHERE jenis_laporan=? LIMIT 1");
$stmt->bind_param("s", $jenis);
$stmt->execute();
$res = $stmt->get_result();
if ($res->num_rows === 0) {
    $today = date('Y-m-d');
    $sql_ins = "INSERT INTO konfigurasi_laporan (jenis_laporan, kop1, kop2, kop3, ttd_tanggal, ttd_diketahui, ttd_jabatan_diketahui, ttd_dibuat, ttd_jabatan_dibuat)
                VALUES (?, 'LAPORAN OBAT MASUK & KELUAR KLINIK NBC', 'PT. SINDO LUMBER - PT. INDEXIM UTAMA', 'PERIODE TAHUN 2025', ?, 'Manajer Klinik', 'Manager Camp', 'Petugas Gudang', 'Staf Gudang')";
    $stmt_ins = $conn->prepare($sql_ins);
    $stmt_ins->bind_param("ss", $jenis, $today);
    $stmt_ins->execute();
    $stmt_ins->close();
}
$stmt->close();

$stmt_cfg = $conn->prepare("SELECT * FROM konfigurasi_laporan WHERE jenis_laporan=? LIMIT 1");
$stmt_cfg->bind_param("s", $jenis);
$stmt_cfg->execute();
$config = $stmt_cfg->get_result()->fetch_assoc();
$stmt_cfg->close();


if (!isset($config['kop1']) || $config['kop1']==='') $config['kop1'] = 'LAPORAN OBAT MASUK & KELUAR KLINIK NBC';
if (!isset($config['kop2']) || $config['kop2']==='') $config['kop2'] = 'PT. SINDO LUMBER - PT. INDEXIM UTAMA';
if (!isset($config['kop3']) || $config['kop3']==='') $config['kop3'] = 'PERIODE TAHUN 2025';
if (!isset($config['ttd_tanggal']) || !$config['ttd_tanggal']) $config['ttd_tanggal'] = date('Y-m-d');
if (!isset($config['ttd_diketahui']) || $config['ttd_diketahui']==='') $config['ttd_diketahui'] = 'Manajer Klinik';
if (!isset($config['ttd_jabatan_diketahui']) || $config['ttd_jabatan_diketahui']==='') $config['ttd_jabatan_diketahui'] = 'Manager Camp';
if (!isset($config['ttd_dibuat']) || $config['ttd_dibuat']==='') $config['ttd_dibuat'] = 'Petugas Gudang';
if (!isset($config['ttd_jabatan_dibuat']) || $config['ttd_jabatan_dibuat']==='') $config['ttd_jabatan_dibuat'] = 'Staf Gudang';

function formatTanggalIndo($tanggal) {
    if (!$tanggal || $tanggal === '0000-00-00') return 'Ngurit Base Camp, dd/mm/yyyy';
    $bulan = ['Januari','Februari','Maret','April','Mei','Juni','Juli','Agustus','September','Oktober','November','Desember'];
    $ts = strtotime($tanggal);
    if ($ts === false) return 'Ngurit Base Camp, '.htmlspecialchars($tanggal);
    $tgl = date('d', $ts);
    $bln = $bulan[(int)date('n', $ts)-1];
    $thn = date('Y', $ts);
    return "Ngurit Base Camp, $tgl $bln $thn";
}


function parseTanggalToYMD($raw){
    $raw = trim($raw);
  
    $raw = preg_replace('~^Ngurit Base Camp,~i','',$raw);
    $raw = trim($raw);

    if (preg_match('~^\d{4}-\d{2}-\d{2}$~',$raw)) return $raw;

    $bulan_map = [
        'januari'=>1,'februari'=>2,'maret'=>3,'april'=>4,'mei'=>5,'juni'=>6,
        'juli'=>7,'agustus'=>8,'september'=>9,'oktober'=>10,'november'=>11,'desember'=>12
    ];
    $lower = mb_strtolower($raw,'UTF-8');
    foreach($bulan_map as $nama=>$angka){
        if(strpos($lower,$nama)!==false){
            if(preg_match('~(\d{1,2}).*'.preg_quote($nama,'~').'.*(\d{4})~i',$lower,$m)){
                $d = str_pad($m[1],2,'0',STR_PAD_LEFT);
                $mnum = str_pad($angka,2,'0',STR_PAD_LEFT);
                $y = $m[2];
                return "$y-$mnum-$d";
            }
        }
    }

    $ts = strtotime($raw);
    if($ts!==false) return date('Y-m-d',$ts);

    return date('Y-m-d');
}

$ttd_tanggal_indonesia = formatTanggalIndo($config['ttd_tanggal']);


if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['type'], $_POST['key'])) {
    $allowed_map = [
        'kop1' => 'kop1',
        'kop2' => 'kop2',
        'kop3' => 'kop3',
        'ttd_tanggal' => 'ttd_tanggal',
        'ttd_diketahui' => 'ttd_diketahui',
        'ttd_jabatan_diketahui' => 'ttd_jabatan_diketahui',
        'ttd_dibuat' => 'ttd_dibuat',
        'ttd_jabatan_dibuat' => 'ttd_jabatan_dibuat'
    ];

    $key   = $_POST['key'];
    $value = isset($_POST['value']) ? trim($_POST['value']) : '';

    if (isset($allowed_map[$key])) {
        $col = $allowed_map[$key];
        if ($col === 'ttd_tanggal') {
            $value = parseTanggalToYMD($value);
        }
        $stmt_upd = $conn->prepare("UPDATE konfigurasi_laporan SET $col=? WHERE jenis_laporan=?");
        $stmt_upd->bind_param("ss", $value, $jenis);
        $stmt_upd->execute();
        $stmt_upd->close();
        echo "Konfigurasi tersimpan.";
    } else {
        echo "Kolom tidak diizinkan.";
    }
    exit;
}


$sql_nama = "SELECT DISTINCT nama FROM (SELECT nama FROM obat_masuk UNION SELECT nama FROM obat_keluar) AS combined ORDER BY nama";
$today = date('Y-m-d');


if (isset($_GET['export']) && $_GET['export'] === 'excel') {
 
    require_once __DIR__ . '/vendor/autoload.php';

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


    $sheet->getColumnDimension('A')->setWidth(5);   
    $sheet->getColumnDimension('B')->setWidth(30);  
    $sheet->getColumnDimension('C')->setWidth(15);  
    $sheet->getColumnDimension('D')->setWidth(15);  
    $sheet->getColumnDimension('E')->setWidth(15); 
    $sheet->getColumnDimension('F')->setWidth(20);  
    $sheet->getColumnDimension('G')->setWidth(25);  

    $sheet->setCellValue('A1', $config['kop1']);
    $sheet->setCellValue('A2', $config['kop2']);
    $sheet->setCellValue('A3', $config['kop3']);
    $sheet->mergeCells('A1:G1');
    $sheet->mergeCells('A2:G2');
    $sheet->mergeCells('A3:G3');
    $sheet->getStyle('A1:A3')->getFont()->setBold(true)->setSize(14);
    $sheet->getStyle('A1:A3')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $headers = ['No','Nama Obat','Jumlah Masuk','Jumlah Keluar','Sisa','Tanggal Kadaluarsa','Keterangan'];
    $sheet->fromArray($headers, NULL, 'A5');
    $sheet->getStyle('A5:G5')->getFont()->setBold(true)->getColor()->setARGB('FFFFFFFF');
    $sheet->getStyle('A5:G5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('FF388E3C');
    $sheet->getStyle('A5:G5')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

   
    $rowNum = 6; $no=1;
    $res_data = $conn->query($sql_nama);
    while ($r = $res_data->fetch_assoc()) {
        $nama = $conn->real_escape_string($r['nama']);

        $qMas = $conn->query("SELECT SUM(jumlah) AS total FROM obat_masuk WHERE nama='$nama'");
        $masuk = (int)($qMas && ($rowMas=$qMas->fetch_assoc()) ? $rowMas['total'] : 0);

        $qKel = $conn->query("SELECT SUM(jumlah) AS total FROM obat_keluar WHERE nama='$nama'");
        $keluar = (int)($qKel && ($rowKel=$qKel->fetch_assoc()) ? $rowKel['total'] : 0);

        $sisa = max(0, $masuk - $keluar);

        $qStat = $conn->query("SELECT SUM(CASE WHEN (tanggal < '$today' OR expired='Expired') THEN jumlah ELSE 0 END) AS masuk_expired, MIN(tanggal) AS tanggal_terlama FROM obat_masuk WHERE nama='$nama'");
        $stat = $qStat ? $qStat->fetch_assoc() : null;

        $masuk_expired = (int)($stat['masuk_expired'] ?? 0);
        $kadaluarsa    = $stat['tanggal_terlama'];
        $tgl_format    = $kadaluarsa ? date('d-m-Y', strtotime($kadaluarsa)) : '-';

        if ($sisa == 0) $keterangan = 'Habis';
        else if ($masuk_expired >= $sisa) $keterangan = 'Expired';
        else if ($masuk_expired > 0) $keterangan = 'Ada (Sebagian Expired)';
        else $keterangan = 'Ada';

        $sheet->fromArray([$no++, $r['nama'], $masuk, $keluar, $sisa, $tgl_format, $keterangan], NULL, "A{$rowNum}");
        $rowNum++;
    }

  
    $sheet->getStyle('A5:G'.($rowNum-1))
          ->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

    $rowNum += 2;
    $sheet->setCellValue("A{$rowNum}", "Diketahui oleh,");
    $sheet->mergeCells("A{$rowNum}:C{$rowNum}");
    $sheet->setCellValue("E{$rowNum}", formatTanggalIndo($config['ttd_tanggal']));
    $sheet->mergeCells("E{$rowNum}:G{$rowNum}");
    $sheet->getStyle("A{$rowNum}:G{$rowNum}")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $rowNum += 4; 
    $sheet->setCellValue("A{$rowNum}", $config['ttd_diketahui']);
    $sheet->mergeCells("A{$rowNum}:C{$rowNum}");
    $sheet->setCellValue("E{$rowNum}", $config['ttd_dibuat']);
    $sheet->mergeCells("E{$rowNum}:G{$rowNum}");

    $rowNum++;
    $sheet->setCellValue("A{$rowNum}", $config['ttd_jabatan_diketahui']);
    $sheet->mergeCells("A{$rowNum}:C{$rowNum}");
    $sheet->setCellValue("E{$rowNum}", $config['ttd_jabatan_dibuat']);
    $sheet->mergeCells("E{$rowNum}:G{$rowNum}");

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

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}


$result = $conn->query($sql_nama);
?>
<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <title>Laporan Obat</title>
  <style>
    body { font-family: 'Times New Roman'; padding: 30px; }
    .kop-header { display: flex; justify-content: space-between; align-items: center; }
    .kop-teks { flex: 1; text-align: center; }
    .kop-teks h2, .kop-teks h3, .kop-teks h4 { margin: 3px 0; }
    .logo { width: 80px; }
    .garis-bawah { border-bottom: 2px solid black; margin-top: 10px; }

    .print-btn {
      display: inline-block; margin: 10px 5px 20px 0; background: #0066cc; color: white;
      padding: 8px 16px; text-decoration: none; border-radius: 5px;
    }
    .print-btn:hover { background: #003366; }

    table { width: 100%; border-collapse: collapse; margin-top: 20px; }
    th, td { border: 1px solid black; padding: 8px; text-align: center; }
    th { background-color: #388e3c; color: #fff; -webkit-print-color-adjust: exact; print-color-adjust: exact; }

    [contenteditable]:focus { outline: 2px solid #00f; background: #e0f0ff; }

   @media print {
  .print-btn { display: none !important; }
  .print-center { text-align: center !important; }
}
  </style>
</head>
<body>


<div style="text-align: center; margin-top: 10px;">
  <a href="#" class="print-btn" onclick="window.print()">🖨️ Cetak</a>
  <a href="<?= htmlspecialchars($_SERVER['PHP_SELF']) ?>?export=excel" class="print-btn" style="background:#28a745;">📥 Ekspor</a>
  <a href="home.php" class="print-btn" style="background: #701881ff;">🏠 Kembali</a>
</div>
<br></br>


<div class="kop-surat">
  <div class="kop-header">
    <img src="logokiri.png" class="logo" alt="Logo Kiri">
    <div class="kop-teks">
      <h2 contenteditable="true" data-type="kop" data-key="kop1"><?= htmlspecialchars($config['kop1']) ?></h2>
      <h3 contenteditable="true" data-type="kop" data-key="kop2"><?= htmlspecialchars($config['kop2']) ?></h3>
      <h4 contenteditable="true" data-type="kop" data-key="kop3"><?= htmlspecialchars($config['kop3']) ?></h4>
    </div>
    <img src="logokanan.png" class="logo" alt="Logo Kanan">
  </div>
  <div class="garis-bawah"></div>
</div>



<table>
  <thead>
    <tr>
      <th>No</th>
      <th>Nama Obat</th>
      <th>Jumlah Masuk</th>
      <th>Jumlah Keluar</th>
      <th>Sisa</th>
      <th>Tanggal Kadaluarsa</th>
      <th>Keterangan</th>
    </tr>
  </thead>
  <tbody>
    <?php
    $no = 1;
    while ($row = $result->fetch_assoc()):
      $nama = $conn->real_escape_string($row['nama']);


      $masukRow  = $conn->query("SELECT SUM(jumlah) AS total FROM obat_masuk WHERE nama='$nama'")->fetch_assoc();
      $keluarRow = $conn->query("SELECT SUM(jumlah) AS total FROM obat_keluar WHERE nama='$nama'")->fetch_assoc();
      $masuk  = (int)($masukRow['total']  ?? 0);
      $keluar = (int)($keluarRow['total'] ?? 0);
      $sisa   = max(0, $masuk - $keluar);

  
      $stat = $conn->query("
          SELECT
              SUM(CASE WHEN (tanggal < '$today' OR expired='Expired') THEN jumlah ELSE 0 END) AS masuk_expired,
              MIN(tanggal) AS tanggal_terlama
          FROM obat_masuk
          WHERE nama='$nama'
      ")->fetch_assoc();

      $masuk_expired = (int)($stat['masuk_expired'] ?? 0);
      $kadaluarsa    = $stat['tanggal_terlama'];
      $tgl_format    = $kadaluarsa ? date('d-m-Y', strtotime($kadaluarsa)) : '-';

      if ($sisa == 0) {
          $keterangan = 'Habis';
      } else {
          if ($masuk_expired >= $sisa) {
              $keterangan = 'Expired';
          } elseif ($masuk_expired > 0) {
              $keterangan = 'Ada (Sebagian Expired)';
          } else {
              $keterangan = 'Ada';
          }
      }
    ?>
    <tr>
      <td><?= $no++ ?></td>
      <td><?= htmlspecialchars($nama) ?></td>
      <td><?= $masuk ?></td>
      <td><?= $keluar ?></td>
      <td><?= $sisa ?></td>
      <td><?= $tgl_format ?></td>
      <td><?= $keterangan ?></td>
    </tr>
    <?php endwhile; ?>
  </tbody>
</table>

<table style="width:100%; margin-top:40px; border:none;">
  <tr>
    <td style="text-align:center; width:50%; border:none;">
      <p style="margin:0; line-height:1;"><b>Diketahui oleh,</b></p>
      <div style="margin-top: 40px; line-height:1;">
        <p style="margin:0;" contenteditable="true" data-type="ttd" data-key="ttd_diketahui"><u><?= htmlspecialchars($config['ttd_diketahui']) ?></u></p>
        <p style="margin:0;" contenteditable="true" data-type="ttd" data-key="ttd_jabatan_diketahui"><?= htmlspecialchars($config['ttd_jabatan_diketahui']) ?></p>
      </div>
    </td>
    <td style="text-align:center; width:50%; border:none;">
      <p style="margin:0; line-height:1;" contenteditable="true" data-type="ttd" data-key="ttd_tanggal"><b><?= htmlspecialchars($ttd_tanggal_indonesia) ?></b></p>
      <p style="margin:0; line-height:1;"><b>Dibuat oleh,</b></p>
      <div style="margin-top: 40px; line-height:1;">
        <p style="margin:0;" contenteditable="true" data-type="ttd" data-key="ttd_dibuat"><u><?= htmlspecialchars($config['ttd_dibuat']) ?></u></p>
        <p style="margin:0;" contenteditable="true" data-type="ttd" data-key="ttd_jabatan_dibuat"><?= htmlspecialchars($config['ttd_jabatan_dibuat']) ?></p>
      </div>
    </td>
  </tr>
</table>

<script>

(function(){
  const els = document.querySelectorAll('[contenteditable="true"]');
  els.forEach(el => {
    el.addEventListener('blur', () => {
      const type = el.dataset.type;
      const key = el.dataset.key;
      const value = el.innerText.trim();
      fetch(location.href, {
        method: 'POST',
        headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
        body: new URLSearchParams({ type, key, value })
      })
      .then(r=>r.text())
      .then(txt=>console.log('Konfig simpan:',txt))
      .catch(err=>console.error(err));
    });
  });
})();
</script>

</body>
</html>
