<?php
defined('BASEPATH') or exit('No direct script access allowed');

$laporan = array();
$totalPajak = 0;
$totalTransaksi = 0;
$totalBayar = 0;
$totalDiskon = 0;
$totalPiutang = 0;

if (!empty($posts)) {
    foreach ($posts as $row) {
        $dataBayar = bayar($row['id_invoice']);
        $jumlahBayar = (is_array($dataBayar) && isset($dataBayar['Totalbayar']))
            ? round((float) $dataBayar['Totalbayar'])
            : 0;

        $jumlahTransaksi = round(
            (float) sumOrder($row['id_invoice']) - (float) totalDiskon($row['id_invoice'])
        );
        $diskonTransaksi = round((float) $row['potongan_harga']);

        $dataPiutang = sumPiutang($row['id_invoice']);
        $piutangTercatat = (is_array($dataPiutang) && isset($dataPiutang[0]['piutang']))
            ? round((float) $dataPiutang[0]['piutang'])
            : round((float) $dataPiutang);

        if ((float) $row['pajak'] > 0) {
            $jumlahPajak = round(($jumlahTransaksi * (float) $row['pajak']) / 100);
            $jumlahPiutang = round(
                $jumlahTransaksi + $jumlahPajak - $jumlahBayar - $diskonTransaksi
            );
        } else {
            $jumlahPajak = 0;
            $jumlahPiutang = round(
                $jumlahTransaksi - $piutangTercatat - $diskonTransaksi
            );
        }

        if ($row['status'] === 'batal') {
            $jumlahPajak = 0;
            $jumlahTransaksi = 0;
            $jumlahBayar = 0;
            $diskonTransaksi = 0;
            $jumlahPiutang = 0;
        }

        $namaPelanggan = ((int) $row['tampil'] === 0) ? $row['nama'] : $row['perusahaan'];
        $rincianBarang = array();
        $detail = detail_order($row['id_invoice'], 'semua');

        if (!empty($detail)) {
            foreach ($detail as $item) {
                $subtotalKotor = (float) $item->jumlah * (float) $item->harga;
                $diskonItem = ((float) $item->diskon > 0)
                    ? ($subtotalKotor * (float) $item->diskon) / 100
                    : 0;
                $dataBahan = getDetailBahan($item->id_bahan);

                $rincianBarang[] = array(
                    'nama_barang' => (is_object($dataBahan) && isset($dataBahan->title))
                        ? $dataBahan->title
                        : '',
                    'harga' => round((float) $item->harga),
                    'qty' => (float) $item->jumlah,
                    'total' => round($subtotalKotor - $diskonItem)
                );
            }
        }

        $laporan[] = array(
            'no_transaksi' => $row['id_transaksi'],
            'tanggal_order' => $row['tgl_trx'],
            'kasir' => $row['kasir'],
            'pelanggan' => $namaPelanggan,
            'rincian' => $rincianBarang,
            'pajak' => $jumlahPajak,
            'total_transaksi' => $jumlahTransaksi,
            'total_bayar' => $jumlahBayar,
            'diskon' => $diskonTransaksi,
            'piutang' => $jumlahPiutang
        );

        $totalPajak += $jumlahPajak;
        $totalTransaksi += $jumlahTransaksi;
        $totalBayar += $jumlahBayar;
        $totalDiskon += $diskonTransaksi;
        $totalPiutang += $jumlahPiutang;
    }
}

$formatTanggal = function ($tanggal) {
    return (!empty($tanggal) && $tanggal !== '0000-00-00' && strtotime($tanggal))
        ? dtimes($tanggal, false, false)
        : '-';
};
$formatQty = function ($qty) {
    return ((float) $qty == floor((float) $qty))
        ? (string) (int) $qty
        : rtrim(rtrim(number_format((float) $qty, 2, ',', '.'), '0'), ',');
};

$periodeDari = (!empty($dari) && strtotime($dari)) ? date('d/m/Y', strtotime($dari)) : '-';
$periodeSampai = (!empty($sampai) && strtotime($sampai)) ? date('d/m/Y', strtotime($sampai)) : '-';
$periode = 'Periode: '.$periodeDari.' s.d. '.$periodeSampai;

$excel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$excel->getProperties()
    ->setCreator(info()['title'])
    ->setLastModifiedBy(info()['title'])
    ->setTitle('Rincian Penjualan')
    ->setSubject('Laporan rincian penjualan')
    ->setDescription('Laporan rincian penjualan sesuai filter pada halaman rincian penjualan.');
$excel->getDefaultStyle()->getFont()->setName('Arial')->setSize(9);

$sheet = $excel->getActiveSheet();
$sheet->setTitle('Rincian Penjualan');
$sheet->setShowGridlines(false);

$setText = function ($cell, $value) use ($sheet) {
    $sheet->setCellValueExplicit(
        $cell,
        (string) $value,
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
    );
};
$setMoneyText = function ($cell, $value) use ($setText) {
    $setText($cell, rp(round((float) $value)));
};

$warnaHeader = 'FF5A5B68';
$warnaBorderHeader = 'FF707382';
$warnaBorder = 'FFDDE4EE';
$warnaBaris = 'FFFBFCFE';
$warnaTotal = 'FFF8FAFC';
$warnaJudul = 'FFF5F8FB';

$styleHeader = array(
    'font' => array('bold' => true, 'color' => array('argb' => 'FFFFFFFF'), 'size' => 8),
    'alignment' => array(
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
        'wrapText' => true
    ),
    'fill' => array(
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => array('argb' => $warnaHeader)
    ),
    'borders' => array(
        'allBorders' => array(
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('argb' => $warnaBorderHeader)
        )
    )
);
$styleIsi = array(
    'alignment' => array(
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
        'wrapText' => true
    ),
    'borders' => array(
        'allBorders' => array(
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('argb' => $warnaBorder)
        )
    )
);
$styleTotal = array(
    'font' => array('bold' => true, 'color' => array('argb' => 'FF1D2939')),
    'fill' => array(
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => array('argb' => $warnaTotal)
    ),
    'borders' => array(
        'allBorders' => array(
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => array('argb' => $warnaBorder)
        )
    )
);

/* Judul dan periode */
$sheet->mergeCells('A1:O1');
$sheet->mergeCells('A2:O2');
$sheet->mergeCells('A3:O3');
$sheet->setCellValue('A1', strtoupper(info()['title']));
$sheet->setCellValue('A2', 'RINCIAN PENJUALAN');
$sheet->setCellValue('A3', $periode);
$sheet->getStyle('A1:O3')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB($warnaJudul);
$sheet->getStyle('A1:O3')->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(10)->getColor()->setARGB('FF1769E8');
$sheet->getStyle('A2')->getFont()->setBold(true)->setSize(16)->getColor()->setARGB('FF1D2939');
$sheet->getStyle('A3')->getFont()->setSize(8)->getColor()->setARGB('FF667085');
$sheet->getRowDimension(1)->setRowHeight(18);
$sheet->getRowDimension(2)->setRowHeight(25);
$sheet->getRowDimension(3)->setRowHeight(17);
$sheet->getRowDimension(4)->setRowHeight(7);

/* Kartu ringkasan: masing-masing memakai tiga kolom */
$cards = array(
    array('range' => 'A5:C7', 'labelRange' => 'A5:C5', 'valueRange' => 'A6:C7', 'label' => 'Total Transaksi', 'value' => $totalTransaksi, 'fill' => 'FFF7F6FF', 'accent' => 'FF6657E8'),
    array('range' => 'D5:F7', 'labelRange' => 'D5:F5', 'valueRange' => 'D6:F7', 'label' => 'Total Bayar', 'value' => $totalBayar, 'fill' => 'FFF3FAFF', 'accent' => 'FF299DEA'),
    array('range' => 'G5:I7', 'labelRange' => 'G5:I5', 'valueRange' => 'G6:I7', 'label' => 'Total Piutang', 'value' => $totalPiutang, 'fill' => 'FFFAF6FF', 'accent' => 'FF9146E8'),
    array('range' => 'J5:L7', 'labelRange' => 'J5:L5', 'valueRange' => 'J6:L7', 'label' => 'Total Pajak', 'value' => $totalPajak, 'fill' => 'FFF3FFFE', 'accent' => 'FF20BFB8'),
    array('range' => 'M5:O7', 'labelRange' => 'M5:O5', 'valueRange' => 'M6:O7', 'label' => 'Total Diskon', 'value' => $totalDiskon, 'fill' => 'FFFFF8F3', 'accent' => 'FFFF7F32')
);
foreach ($cards as $card) {
    $sheet->mergeCells($card['labelRange']);
    $sheet->mergeCells($card['valueRange']);
    $labelCell = explode(':', $card['labelRange'])[0];
    $valueCell = explode(':', $card['valueRange'])[0];
    $sheet->setCellValue($labelCell, $card['label']);
    $setMoneyText($valueCell, $card['value']);
    $sheet->getStyle($card['range'])->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($card['fill']);
    $sheet->getStyle($card['range'])->getBorders()->getOutline()
        ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN)
        ->getColor()->setARGB($warnaBorder);
    $sheet->getStyle($card['range'])->getBorders()->getLeft()
        ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM)
        ->getColor()->setARGB($card['accent']);
    $sheet->getStyle($labelCell)->getFont()->setBold(true)->setSize(8)->getColor()->setARGB('FF667085');
    $sheet->getStyle($valueCell)->getFont()->setBold(true)->setSize(12)->getColor()->setARGB('FF1D2939');
    $sheet->getStyle($card['range'])->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}
$sheet->getRowDimension(5)->setRowHeight(17);
$sheet->getRowDimension(6)->setRowHeight(15);
$sheet->getRowDimension(7)->setRowHeight(15);
$sheet->getRowDimension(8)->setRowHeight(8);

/* Header tabel bertingkat, sama dengan PDF */
$mainHeaders = array(
    'A9:A10' => 'No',
    'B9:B10' => 'No Trx',
    'C9:C10' => 'Tanggal Order',
    'D9:D10' => 'Kasir',
    'E9:E10' => 'Pelanggan',
    'F9:J9' => 'Rincian Penjualan Barang',
    'K9:K10' => 'Pajak',
    'L9:L10' => 'Total Transaksi',
    'M9:M10' => 'Total Bayar',
    'N9:N10' => 'Diskon',
    'O9:O10' => 'Piutang'
);
foreach ($mainHeaders as $range => $label) {
    $sheet->mergeCells($range);
    $sheet->setCellValue(explode(':', $range)[0], $label);
}
$sheet->mergeCells('F10:G10');
$sheet->setCellValue('F10', 'Nama Barang');
$sheet->setCellValue('H10', 'Harga');
$sheet->setCellValue('I10', 'Qty');
$sheet->setCellValue('J10', 'Total');
$sheet->getStyle('A9:O10')->applyFromArray($styleHeader);
$sheet->getRowDimension(9)->setRowHeight(26);
$sheet->getRowDimension(10)->setRowHeight(22);

/* Isi tabel */
$baris = 11;
foreach ($laporan as $index => $row) {
    $items = !empty($row['rincian'])
        ? $row['rincian']
        : array(array('nama_barang' => 'Tidak ada item', 'harga' => 0, 'qty' => 0, 'total' => 0));
    $awal = $baris;
    $akhir = $baris + count($items) - 1;

    foreach (array('A', 'B', 'C', 'D', 'E', 'K', 'L', 'M', 'N', 'O') as $kolom) {
        if ($akhir > $awal) {
            $sheet->mergeCells($kolom.$awal.':'.$kolom.$akhir);
        }
    }

    $sheet->setCellValue('A'.$awal, $index + 1);
    $setText('B'.$awal, $row['no_transaksi']);
    $setText('C'.$awal, $formatTanggal($row['tanggal_order']));
    $setText('D'.$awal, $row['kasir']);
    $setText('E'.$awal, $row['pelanggan']);
    $setMoneyText('K'.$awal, $row['pajak']);
    $setMoneyText('L'.$awal, $row['total_transaksi']);
    $setMoneyText('M'.$awal, $row['total_bayar']);
    $setMoneyText('N'.$awal, $row['diskon']);
    $setMoneyText('O'.$awal, $row['piutang']);

    foreach ($items as $itemIndex => $item) {
        $itemRow = $awal + $itemIndex;
        $sheet->mergeCells('F'.$itemRow.':G'.$itemRow);
        $setText('F'.$itemRow, $item['nama_barang']);
        $setMoneyText('H'.$itemRow, $item['harga']);
        $setText('I'.$itemRow, $formatQty($item['qty']));
        $setMoneyText('J'.$itemRow, $item['total']);
        $sheet->getRowDimension($itemRow)->setRowHeight(21);
    }

    $sheet->getStyle('A'.$awal.':O'.$akhir)->applyFromArray($styleIsi);
    if (($index + 1) % 2 === 0) {
        $sheet->getStyle('A'.$awal.':O'.$akhir)->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setARGB($warnaBaris);
    }

    $sheet->getStyle('A'.$awal.':E'.$akhir)->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle('E'.$awal.':E'.$akhir)->getFont()->getColor()->setARGB('FF31B84D');
    $sheet->getStyle('F'.$awal.':G'.$akhir)->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet->getStyle('H'.$awal.':O'.$akhir)->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle('I'.$awal.':I'.$akhir)->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $baris = $akhir + 1;
}

if (empty($laporan)) {
    $sheet->mergeCells('A11:O11');
    $sheet->setCellValue('A11', 'Data belum ada untuk filter yang dipilih.');
    $sheet->getStyle('A11:O11')->applyFromArray($styleIsi);
    $sheet->getStyle('A11')->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getRowDimension(11)->setRowHeight(28);
    $baris = 12;
}

/* Total keseluruhan */
$barisTotal = $baris;
$sheet->mergeCells('A'.$barisTotal.':J'.$barisTotal);
$sheet->setCellValue('A'.$barisTotal, 'Total keseluruhan');
$setMoneyText('K'.$barisTotal, $totalPajak);
$setMoneyText('L'.$barisTotal, $totalTransaksi);
$setMoneyText('M'.$barisTotal, $totalBayar);
$setMoneyText('N'.$barisTotal, $totalDiskon);
$setMoneyText('O'.$barisTotal, $totalPiutang);
$sheet->getStyle('A'.$barisTotal.':O'.$barisTotal)->applyFromArray($styleTotal);
$sheet->getStyle('A'.$barisTotal)->getFont()->setBold(false)->setSize(8)->getColor()->setARGB('FF667085');
$sheet->getStyle('K'.$barisTotal.':O'.$barisTotal)->getAlignment()
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet->getRowDimension($barisTotal)->setRowHeight(20);

$barisJumlah = $barisTotal + 2;
$sheet->mergeCells('A'.$barisJumlah.':C'.$barisJumlah);
$sheet->setCellValue('A'.$barisJumlah, 'Menampilkan: '.count($laporan));
$sheet->getStyle('A'.$barisJumlah)->getFont()->setSize(9)->getColor()->setARGB('FF344054');

/* Lebar kolom mengikuti proporsi PDF */
$lebarKolom = array(
    'A' => 5, 'B' => 14, 'C' => 18, 'D' => 12, 'E' => 15,
    'F' => 13, 'G' => 20, 'H' => 13, 'I' => 8, 'J' => 14,
    'K' => 10, 'L' => 16, 'M' => 14, 'N' => 10, 'O' => 16
);
foreach ($lebarKolom as $kolom => $lebar) {
    $sheet->getColumnDimension($kolom)->setWidth($lebar);
}

$sheet->freezePane('A11');
$sheet->getSheetView()->setZoomScale(80);
$sheet->getPageSetup()
    ->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE)
    ->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4)
    ->setFitToWidth(1)
    ->setFitToHeight(0);
$sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(9, 10);
$sheet->getPageMargins()->setTop(0.35)->setRight(0.25)->setLeft(0.25)->setBottom(0.4);
$sheet->getHeaderFooter()->setOddFooter('&LRincian Penjualan - '.$periode.'&RHalaman &P dari &N');
$sheet->getPageSetup()->setPrintArea('A1:O'.$barisJumlah);

$namaDari = (!empty($dari) && strtotime($dari)) ? date('Ymd', strtotime($dari)) : date('Ymd');
$namaSampai = (!empty($sampai) && strtotime($sampai)) ? date('Ymd', strtotime($sampai)) : date('Ymd');
$namaFile = 'Rincian_Penjualan_'.$namaDari.'_'.$namaSampai.'.xlsx';

while (ob_get_level() > 0) {
    ob_end_clean();
}

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

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