<?php
session_start();
if (!isset($_SESSION['admin'])) {
  header("Location: login_admin.php");
  exit;
}

<?php
$host = "localhost";
$user = "odmr9632_userdb";
$pass = "@Sukabumi2018";
$db   = "odmr9632_order_db";

$conn = new mysqli($host, $user, $pass, $db);
if ($conn->connect_error) {
  die("Koneksi gagal: " . $conn->connect_error);
}
?>
if ($conn->connect_error) {
  die("Koneksi gagal: " . $conn->connect_error);
}

$tgl1 = $_GET['tgl1'] ?? '';
$tgl2 = $_GET['tgl2'] ?? '';
$produk_filter = $_GET['produk'] ?? [];

$where = "WHERE 1=1";
if ($tgl1 && $tgl2) $where .= " AND tanggal BETWEEN '$tgl1' AND '$tgl2'";

$produk_checked = [];
$produk_all = [];
$produk_query = $conn->query("SELECT DISTINCT nama_produk FROM orders");
while ($p = $produk_query->fetch_assoc()) {
  $produk_all[] = $p['nama_produk'];
  if (in_array($p['nama_produk'], $produk_filter)) {
    $produk_checked[] = "'" . $conn->real_escape_string($p['nama_produk']) . "'";
  }
}

if ($produk_checked) {
  $where .= " AND nama_produk IN (" . implode(",", $produk_checked) . ")";
}

$produk_hpp = [];
$hpp_q1 = $conn->query("SELECT nama_produk, hpp FROM produk_fisik WHERE hpp IS NOT NULL");
if ($hpp_q1) {
  while ($row = $hpp_q1->fetch_assoc()) {
    $produk_hpp[$row['nama_produk']] = (int)$row['hpp'];
  }
}
$hpp_q2 = $conn->query("SELECT nama_produk, hpp FROM produk_digital WHERE hpp IS NOT NULL");
if ($hpp_q2) {
  while ($row = $hpp_q2->fetch_assoc()) {
    $produk_hpp[$row['nama_produk']] = (int)$row['hpp'];
  }
}


$total_omset = 0;
$hpp_total = 0;
$total_biaya_iklan = 0;
$total_ppn = 0;
$denda_return = 0;
$unprofit = 0;

$produk_pendapatan = [];
$orders = $conn->query("SELECT * FROM orders $where");
while ($row = $orders->fetch_assoc()) {
  $produk = $row['nama_produk'];
  $qty = $row['qty'] ?? 1;
  $harga_jual = (int)$row['harga_jual'];
  $hpp = $produk_hpp[$produk] ?? 0;

  $total_omset += $harga_jual;
  $hpp_total += ($hpp * $qty);
  $total_biaya_iklan += (int)($row['biaya_iklan'] ?? 0);
  $total_ppn += (int)($row['ppn'] ?? 0);

  if (!isset($produk_pendapatan[$produk])) {
    $produk_pendapatan[$produk] = 0;
  }
  $produk_pendapatan[$produk] += $harga_jual;

  if ($row['status_pengiriman'] === 'Return') {
    $denda_return += (int)$row['ongkir'] / 2;
    $unprofit += ($harga_jual - $hpp);
  }
}

$laba_kotor = $total_omset - $hpp_total;
$total_operasional = $total_biaya_iklan + $total_ppn + $denda_return + $unprofit;
$laba_bersih = $laba_kotor - $total_operasional;

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=laporan_laba_rugi.xls");

echo "<table border='1'>";
echo "<tr><th colspan='2'>Laporan Laba Rugi</th></tr>";
echo "<tr><th colspan='2'>Periode: $tgl1 s/d $tgl2</th></tr>";
echo "<tr><th colspan='2'>Pendapatan Usaha</th></tr>";
foreach ($produk_pendapatan as $nama => $jumlah) {
  echo "<tr><td>$nama</td><td>Rp" . number_format($jumlah) . "</td></tr>";
}
echo "<tr><td><strong>Total Pendapatan Usaha</strong></td><td><strong>Rp" . number_format($total_omset) . "</strong></td></tr>";
echo "<tr><td>HPP</td><td>Rp" . number_format($hpp_total) . "</td></tr>";
echo "<tr><td>Laba Kotor</td><td>Rp" . number_format($laba_kotor) . "</td></tr>";
echo "<tr><td>Biaya Iklan & PPN</td><td>Rp" . number_format($total_biaya_iklan + $total_ppn) . "</td></tr>";
echo "<tr><td>Unprofit</td><td>Rp" . number_format($unprofit) . "</td></tr>";
echo "<tr><td>Denda Return</td><td>Rp" . number_format($denda_return) . "</td></tr>";
echo "<tr><td>Total Beban Operasional</td><td>Rp" . number_format($total_operasional) . "</td></tr>";
echo "<tr><td><strong>Laba Bersih</strong></td><td><strong>Rp" . number_format($laba_bersih) . "</strong></td></tr>";
echo "</table>";
exit;
