<?php
/**
 * Export Konsumsi Excel
 * Koperasi Management System
 */

require_once __DIR__ . '/../config/database.php';
$db = getDB();

$search = $_GET['search'] ?? '';
$selectedMonth = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('n');
$selectedYear = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');

$where = "WHERE MONTH(t.tanggal) = :month AND YEAR(t.tanggal) = :year";
$queryParams = [
    ':month' => $selectedMonth,
    ':year' => $selectedYear
];

if ($search) {
    $where .= " AND (a.nama LIKE :search OR a.no_anggota LIKE :search OR t.nama_barang LIKE :search)";
    $queryParams[':search'] = "%$search%";
}

$stmt = $db->prepare("
    SELECT t.*, a.nama, a.no_anggota 
    FROM konsumsi_transaksi t 
    JOIN anggota a ON t.anggota_id = a.id 
    $where
    ORDER BY t.tanggal ASC, t.id ASC
");
$stmt->execute($queryParams);
$data = $stmt->fetchAll();

$filename = "Rekap_Konsumsi_" . date('Ymd_His') . ".xlsx";
$tmpfile = tempnam(sys_get_temp_dir(), 'xlsx_');
$zip = new ZipArchive();
if ($zip->open($tmpfile, ZipArchive::CREATE | ZipArchive::OVERWRITE) === false) {
    die('Gagal membuat file Excel');
}

$sheetData = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<cols>
<col min="1" max="1" width="5" customWidth="1"/>
<col min="2" max="2" width="15" customWidth="1"/>
<col min="3" max="3" width="25" customWidth="1"/>
<col min="4" max="4" width="15" customWidth="1"/>
<col min="5" max="5" width="30" customWidth="1"/>
<col min="6" max="6" width="20" customWidth="1"/>
<col min="7" max="7" width="10" customWidth="1"/>
<col min="8" max="8" width="20" customWidth="1"/>
<col min="9" max="9" width="20" customWidth="1"/>
<col min="10" max="10" width="30" customWidth="1"/>
</cols>
<sheetData>';

function tglIndoExcel($tanggal){
    $bulan = array (
        1 =>   'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni', 'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
    );
    $pecahkan = explode('-', $tanggal);
    return $pecahkan[2] . ' ' . $bulan[ (int)$pecahkan[1] ] . ' ' . $pecahkan[0];
}

$sheetData .= '<row r="1"><c r="A1" t="str"><v>KOPERASI MANAGEMENT SYSTEM</v></c></row>';
$sheetData .= '<row r="2"><c r="A2" t="str"><v>LAPORAN REKAP KONSUMSI</v></c></row>';
$sheetData .= '<row r="3"><c r="A3" t="str"><v>Periode: ' . tglIndoExcel(date('Y-m-d', strtotime($selectedYear . '-' . $selectedMonth . '-01'))) . '</v></c></row>';
$sheetData .= '<row r="4"><c r="A4" t="str"><v>Tanggal Cetak: ' . tglIndoExcel(date('Y-m-d')) . '</v></c></row>';
$sheetData .= '<row r="5"><c r="A5" t="str"><v></v></c></row>';

$headers = ['No', 'Tanggal', 'Nama Anggota', 'No Anggota', 'Nama Barang', 'Harga Beli', 'PPN (%)', 'Harga Jual', 'Jumlah Belanja', 'Keterangan'];
$sheetData .= '<row r="6">';
foreach ($headers as $index => $header) {
    $col = chr(65 + $index);
    $sheetData .= '<c r="' . $col . '6" t="str"><v>' . $header . '</v></c>';
}
$sheetData .= '</row>';

$rowNum = 7;
$totalHargaBeli = 0;
$totalPPN = 0;
$totalHargaJual = 0;
$totalJumlahBelanja = 0;

foreach ($data as $i => $r) {
    $sheetData .= '<row r="' . $rowNum . '">';
    
    $cols = [
        $i + 1,
        date('d/m/Y', strtotime($r['tanggal'])),
        $r['nama'],
        $r['no_anggota'],
        $r['nama_barang'],
        $r['harga_beli'],
        $r['ppn_persen'],
        $r['harga_jual'],
        $r['jumlah_belanja'],
        $r['keterangan']
    ];
    
    foreach ($cols as $index => $val) {
        $col = chr(65 + $index);
        $valStr = is_numeric($val) ? number_format($val, 0, ',', '.') : $val;
        $sheetData .= '<c r="' . $col . $rowNum . '" t="str"><v>' . htmlspecialchars((string)$valStr) . '</v></c>';
    }
    
    $sheetData .= '</row>';
    $rowNum++;
    
    $totalHargaBeli += $r['harga_beli'];
    $totalPPN += ($r['harga_beli'] * ($r['ppn_persen'] / 100));
    $totalHargaJual += $r['harga_jual'];
    $totalJumlahBelanja += $r['jumlah_belanja'];
}

$sheetData .= '<row r="' . $rowNum . '"><c r="A' . $rowNum . '" t="str"><v></v></c></row>';
$rowNum++;

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="A' . $rowNum . '" t="str"><v></v></c>';
$sheetData .= '<c r="B' . $rowNum . '" t="str"><v>TOTAL</v></c>';
$sheetData .= '<c r="F' . $rowNum . '" t="str"><v>' . number_format($totalHargaBeli, 0, ',', '.') . '</v></c>';
$sheetData .= '<c r="G' . $rowNum . '" t="str"><v>' . number_format($totalPPN, 0, ',', '.') . '</v></c>';
$sheetData .= '<c r="H' . $rowNum . '" t="str"><v>' . number_format($totalHargaJual, 0, ',', '.') . '</v></c>';
$sheetData .= '<c r="I' . $rowNum . '" t="str"><v>' . number_format($totalJumlahBelanja, 0, ',', '.') . '</v></c>';
$sheetData .= '</row>';
$rowNum += 2;

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="I' . $rowNum . '" t="str"><v>Bogor, ' . tglIndoExcel(date('Y-m-d')) . '</v></c>';
$sheetData .= '</row>';
$rowNum++;

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="I' . $rowNum . '" t="str"><v>Bendahara</v></c>';
$sheetData .= '</row>';
$rowNum += 4;

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="I' . $rowNum . '" t="str"><v>(________________________)</v></c>';
$sheetData .= '</row>';

$sheetData .= '</sheetData></worksheet>';

$contentTypes = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
</Types>';
$zip->addFromString('[Content_Types].xml', $contentTypes);

$rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>';
$zip->addFromString('_rels/.rels', $rels);

$workbookRels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
</Relationships>';
$zip->addFromString('xl/_rels/workbook.xml.rels', $workbookRels);

$workbook = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets><sheet name="Rekap Konsumsi" sheetId="1" r:id="rId1"/></sheets>
</workbook>';
$zip->addFromString('xl/workbook.xml', $workbook);

$zip->addFromString('xl/worksheets/sheet1.xml', $sheetData);
$zip->close();

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . filesize($tmpfile));
readfile($tmpfile);
unlink($tmpfile);
?>