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

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

$search = $_GET['search'] ?? '';
$where = $search ? "WHERE keterangan LIKE ?" : "";
$params = $search ? ["%$search%"] : [];

$allData = $db->query("SELECT * FROM provisi ORDER BY tanggal ASC, id ASC")->fetchAll();
$saldoMap = [];
$tempSaldo = 0;
foreach ($allData as $row) {
    if ($row['tipe'] === 'masuk') $tempSaldo += $row['jumlah'];
    else $tempSaldo -= $row['jumlah'];
    $saldoMap[$row['id']] = $tempSaldo;
}

$stmt = $db->prepare("SELECT * FROM provisi $where ORDER BY tanggal ASC, id ASC");
$stmt->execute($params);
$data = $stmt->fetchAll();

$filename = "Rekap_Provisi_" . 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="40" customWidth="1"/>
<col min="4" max="4" width="20" customWidth="1"/>
<col min="5" max="5" width="20" customWidth="1"/>
<col min="6" max="6" width="20" 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 MANAJEMEN PROVISI</v></c></row>';
$sheetData .= '<row r="3"><c r="A3" t="str"><v>Tanggal Cetak: ' . tglIndoExcel(date('Y-m-d')) . '</v></c></row>';
$sheetData .= '<row r="4"><c r="A4" t="str"><v></v></c></row>';

$headers = ['No', 'Tanggal', 'Keterangan', 'Masuk', 'Keluar', 'Saldo'];
$sheetData .= '<row r="5">';
foreach ($headers as $index => $header) {
    $col = chr(65 + $index);
    $sheetData .= '<c r="' . $col . '5" t="str"><v>' . $header . '</v></c>';
}
$sheetData .= '</row>';

$rowNum = 6;
$totalMasuk = 0;
$totalKeluar = 0;

foreach ($data as $i => $r) {
    $sheetData .= '<row r="' . $rowNum . '">';
    
    $cols = [
        $i + 1,
        date('d/m/Y', strtotime($r['tanggal'])),
        $r['keterangan'],
        ($r['tipe'] === 'masuk') ? $r['jumlah'] : '-',
        ($r['tipe'] === 'keluar') ? $r['jumlah'] : '-',
        $saldoMap[$r['id']]
    ];
    
    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++;
    
    if ($r['tipe'] === 'masuk') $totalMasuk += $r['jumlah'];
    else $totalKeluar += $r['jumlah'];
}

$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 Masuk</v></c>';
$sheetData .= '<c r="D' . $rowNum . '" t="str"><v>' . number_format($totalMasuk, 0, ',', '.') . '</v></c>';
$sheetData .= '</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 Keluar</v></c>';
$sheetData .= '<c r="E' . $rowNum . '" t="str"><v>' . number_format($totalKeluar, 0, ',', '.') . '</v></c>';
$sheetData .= '</row>';
$rowNum++;

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="A' . $rowNum . '" t="str"><v></v></c>';
$sheetData .= '<c r="B' . $rowNum . '" t="str"><v>Saldo Akhir</v></c>';
$sheetData .= '<c r="F' . $rowNum . '" t="str"><v>' . number_format($totalMasuk - $totalKeluar, 0, ',', '.') . '</v></c>';
$sheetData .= '</row>';
$rowNum += 2;

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

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

$sheetData .= '<row r="' . $rowNum . '">';
$sheetData .= '<c r="E' . $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 Provisi" 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);
