<?php
namespace App\Controllers;

use App\Models\Hargamodel;
use App\Models\Mejamodel;
use CodeIgniter\HTTP\RequestInterface;
use CodeIgniter\HTTP\ResponseInterface;
use Psr\Log\LoggerInterface;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

use CodeIgniter\API\ResponseTrait;
use App\Models\Ordermodel;
use App\Models\Orderdetailmodel;
use App\Models\Pajakmodel;
use App\Models\Transaksimodel;

use Dompdf\Dompdf;
use Dompdf\Options;

class Laporan extends BaseController
{
    use ResponseTrait;

    private $user;

    public function initController(RequestInterface $request, ResponseInterface $response, LoggerInterface $logger) 
    {
        parent::initController($request, $response, $logger);
        $this->user = service("user_data");
    }

    private function check_access(){
        $user = service("user_data");
        if($user->data->group != 'admin'){
            return false;
        }else{
            return true;
        }
    }

    private function saveToString(Xlsx $writer): string
    {
        ob_start();
        $writer->save('php://output');
        return ob_get_clean();
    }

    public function transaksi_harian()
    {
        $data['user'] = $this->user->data;
        return view('admin/laporan/transaksi/harian',$data);
    }

    public function transaksi_harian_proses(){
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // ===== DINE-IN =====
        $trx = $db->table('vtransaksi')
            ->select('id_transaksi, kode_order, tanggal_order, nama_meja, total_jual, diskon_total, metode, pajak_nominal, grand_total')
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->orderBy('tanggal_order', 'ASC')
            ->get()->getResultArray();

        $sumTotal = $sumDiskon = $sumPajak = $sumGrand = 0;
        $sumTotalD = $sumDiskonD = $sumPajakD = $sumGrandD = 0;
        foreach ($trx as $t) {
            $sumTotal   += (float) $t['total_jual'];
            $sumDiskon  += (float) $t['diskon_total'];
            $sumPajak   += (float) $t['pajak_nominal'];
            $sumGrand   += (float) $t['grand_total'];
            $sumTotalD  += (float) $t['total_jual'];
            $sumDiskonD += (float) $t['diskon_total'];
            $sumPajakD  += (float) $t['pajak_nominal'];
            $sumGrandD  += (float) $t['grand_total'];
        }

        // ===== TAKEAWAY =====
        $trxT = $db->table('vtransaksitakeaway')
            ->select('id_takeaway, kode_takeaway, tanggal, nama, total_jual, diskon_total, metode, pajak_nominal, grand_total')
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status','closed')
            ->orderBy('tanggal', 'ASC')
            ->get()->getResultArray();

        $sumTotalT = $sumDiskonT = $sumPajakT = $sumGrandT = 0;
        foreach ($trxT as $t) {
            $sumTotal   += (float) $t['total_jual'];
            $sumDiskon  += (float) $t['diskon_total'];
            $sumPajak   += (float) $t['pajak_nominal'];
            $sumGrand   += (float) $t['grand_total'];
            $sumTotalT  += (float) $t['total_jual'];
            $sumDiskonT += (float) $t['diskon_total'];
            $sumPajakT  += (float) $t['pajak_nominal'];
            $sumGrandT  += (float) $t['grand_total'];
        }

        // ===== RINGKASAN / STAT =====
        $countD = count($trx);
        $countT = count($trxT);
        $countAll = $countD + $countT;

        $penjualanKotorAll = $sumTotalD + $sumTotalT;            // sebelum diskon & pajak
        $totalDiskonAll    = $sumDiskon;                          // dine-in + takeaway
        $totalPajakAll     = $sumPajak;                           // dine-in + takeaway
        $omzetAll          = $sumGrand;                           // grand total gabungan

        $avgTicketD = $countD ? ($sumGrandD / $countD) : 0;
        $avgTicketT = $countT ? ($sumGrandT / $countT) : 0;
        $avgTicketAll = $countAll ? ($omzetAll / $countAll) : 0;

        $data = [
            'title'     => 'Laporan Detail Transaksi Harian',
            'rangeText' => date('d/m/Y', strtotime($startDate)) . " - " . date('d/m/Y', strtotime($endDate)),
            'printedAt' => date('d/m/Y H:i'),

            // detail
            'trx'        => $trx,
            'trxT'       => $trxT,

            // total gabungan
            'sumTotal'   => $sumTotal,
            'sumDiskon'  => $sumDiskon,
            'sumPajak'   => $sumPajak,
            'sumGrand'   => $sumGrand,

            // total dine-in
            'sumTotalD'  => $sumTotalD,
            'sumDiskonD' => $sumDiskonD,
            'sumPajakD'  => $sumPajakD,
            'sumGrandD'  => $sumGrandD,

            // total takeaway
            'sumTotalT'  => $sumTotalT,
            'sumDiskonT' => $sumDiskonT,
            'sumPajakT'  => $sumPajakT,
            'sumGrandT'  => $sumGrandT,

            // ringkasan
            'countD'     => $countD,
            'countT'     => $countT,
            'countAll'   => $countAll,
            'penjualanKotorAll' => $penjualanKotorAll,
            'totalDiskonAll'    => $totalDiskonAll,
            'totalPajakAll'     => $totalPajakAll,
            'omzetAll'          => $omzetAll,
            'avgTicketD'        => $avgTicketD,
            'avgTicketT'        => $avgTicketT,
            'avgTicketAll'      => $avgTicketAll,
        ];

        $html = view('print/laporan/transaksi/harian', $data);

        $options = new Options();
        $options->set('isRemoteEnabled', true);

        $dompdf = new Dompdf($options);
        $dompdf->loadHtml($html, 'UTF-8');
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        return $this->response
            ->setContentType('application/pdf')
            ->setBody($dompdf->output());
    }

    public function transaksi_harian_proses_excel(){
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // ===== DINE-IN =====
        $trx = $db->table('vtransaksi')
            ->select('id_transaksi, kode_order, tanggal_order, nama_meja, total_jual, diskon_total, metode, pajak_nominal, grand_total')
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->orderBy('tanggal_order', 'ASC')
            ->get()->getResultArray();

        $sumTotal = $sumDiskon = $sumPajak = $sumGrand = 0;
        $sumTotalD = $sumDiskonD = $sumPajakD = $sumGrandD = 0;
        foreach ($trx as $t) {
            $sumTotal   += (float) $t['total_jual'];
            $sumDiskon  += (float) $t['diskon_total'];
            $sumPajak   += (float) $t['pajak_nominal'];
            $sumGrand   += (float) $t['grand_total'];
            $sumTotalD  += (float) $t['total_jual'];
            $sumDiskonD += (float) $t['diskon_total'];
            $sumPajakD  += (float) $t['pajak_nominal'];
            $sumGrandD  += (float) $t['grand_total'];
        }

        // ===== TAKEAWAY =====
        $trxT = $db->table('vtransaksitakeaway')
            ->select('id_takeaway, kode_takeaway, tanggal, nama, total_jual, diskon_total, metode, pajak_nominal, grand_total')
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status','closed')
            ->orderBy('tanggal', 'ASC')
            ->get()->getResultArray();

        $sumTotalT = $sumDiskonT = $sumPajakT = $sumGrandT = 0;
        foreach ($trxT as $t) {
            $sumTotal   += (float) $t['total_jual'];
            $sumDiskon  += (float) $t['diskon_total'];
            $sumPajak   += (float) $t['pajak_nominal'];
            $sumGrand   += (float) $t['grand_total'];
            $sumTotalT  += (float) $t['total_jual'];
            $sumDiskonT += (float) $t['diskon_total'];
            $sumPajakT  += (float) $t['pajak_nominal'];
            $sumGrandT  += (float) $t['grand_total'];
        }

        // ===== RINGKASAN / STAT =====
        $countD = count($trx);
        $countT = count($trxT);
        $countAll = $countD + $countT;

        $penjualanKotorAll = $sumTotalD + $sumTotalT;            // sebelum diskon & pajak
        $totalDiskonAll    = $sumDiskon;                          // dine-in + takeaway
        $totalPajakAll     = $sumPajak;                           // dine-in + takeaway
        $omzetAll          = $sumGrand;                           // grand total gabungan

        $avgTicketD = $countD ? ($sumGrandD / $countD) : 0;
        $avgTicketT = $countT ? ($sumGrandT / $countT) : 0;
        $avgTicketAll = $countAll ? ($omzetAll / $countAll) : 0;

        // ===================== EXCEL =====================
        $spreadsheet = new Spreadsheet();

        // ---------- Sheet 1: Ringkasan ----------
        $sheet1 = $spreadsheet->getActiveSheet();
        $sheet1->setTitle('Ringkasan');

        $rangeText = date('d/m/Y', strtotime($startDate)) . " - " . date('d/m/Y', strtotime($endDate));
        $printedAt = date('d/m/Y H:i');

        $sheet1->setCellValue('A1', 'Laporan Detail Transaksi Harian');
        $sheet1->mergeCells('A1:D1');
        $sheet1->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet1->getStyle('A1')->getFont()->setBold(true)->setSize(14);

        $sheet1->setCellValue('A2', 'Periode');
        $sheet1->setCellValue('B2', $rangeText);
        $sheet1->setCellValue('A3', 'Dicetak');
        $sheet1->setCellValue('B3', $printedAt);

        $sheet1->setCellValue('A5', 'Metrix');
        $sheet1->setCellValue('B5', 'Nilai');
        $sheet1->getStyle('A5:B5')->getFont()->setBold(true);

        $rows = [
            ['Jumlah Transaksi Dine-in', $countD],
            ['Jumlah Transaksi Takeaway', $countT],
            ['Jumlah Transaksi (All)', $countAll],
            ['Penjualan Kotor (All)', $penjualanKotorAll],
            ['Total Diskon (All)', $totalDiskonAll],
            ['Total Pajak (All)', $totalPajakAll],
            ['Omzet / Grand Total (All)', $omzetAll],
            ['Avg Ticket Dine-in', $avgTicketD],
            ['Avg Ticket Takeaway', $avgTicketT],
            ['Avg Ticket (All)', $avgTicketAll],
            ['Penjualan Kotor Dine-in', $sumTotalD],
            ['Penjualan Kotor Takeaway', $sumTotalT],
            ['Omzet Dine-in', $sumGrandD],
            ['Omzet Takeaway', $sumGrandT],
        ];

        $r = 6;
        foreach ($rows as $row) {
            $sheet1->setCellValue('A'.$r, $row[0]);
            $sheet1->setCellValue('B'.$r, $row[1]);
            $r++;
        }
        // Format angka (kolom B dari baris 9 ke bawah berisi uang)
        $sheet1->getStyle('B9:B'.($r-1))
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        foreach (['A','B','C','D'] as $col) {
            $sheet1->getColumnDimension($col)->setAutoSize(true);
        }

        // ---------- Sheet 2: DineIn ----------
        $sheet2 = $spreadsheet->createSheet();
        $sheet2->setTitle('DineIn');

        $headersD = [
            'A1' => 'ID Transaksi',
            'B1' => 'Kode Order',
            'C1' => 'Tanggal Order',
            'D1' => 'Meja',
            'E1' => 'Total Jual',
            'F1' => 'Diskon',
            'G1' => 'Metode',
            'H1' => 'Pajak',
            'I1' => 'Grand Total',
        ];
        foreach ($headersD as $cell => $text) {
            $sheet2->setCellValue($cell, $text);
        }

        // style header
        $sheet2->getStyle('A1:I1')->getFont()->setBold(true);
        $sheet2->getStyle('A1:I1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet2->freezePane('A2');

        $row = 2;
        foreach ($trx as $t) {
            $sheet2->setCellValue('A'.$row, $t['id_transaksi']);
            $sheet2->setCellValue('B'.$row, $t['kode_order']);
            $sheet2->setCellValue('C'.$row, $t['tanggal_order']); // bisa diformat tanggal jika mau
            $sheet2->setCellValue('D'.$row, $t['nama_meja']);
            $sheet2->setCellValue('E'.$row, (float)$t['total_jual']);
            $sheet2->setCellValue('F'.$row, (float)$t['diskon_total']);
            $sheet2->setCellValue('G'.$row, $t['metode']);
            $sheet2->setCellValue('H'.$row, (float)$t['pajak_nominal']);
            $sheet2->setCellValue('I'.$row, (float)$t['grand_total']);
            $row++;
        }

        // total di bawah
        $sheet2->setCellValue('D'.$row, 'TOTAL');
        $sheet2->getStyle('D'.$row)->getFont()->setBold(true);
        $sheet2->setCellValue('E'.$row, $sumTotalD);
        $sheet2->setCellValue('F'.$row, $sumDiskonD);
        $sheet2->setCellValue('H'.$row, $sumPajakD);
        $sheet2->setCellValue('I'.$row, $sumGrandD);
        $sheet2->getStyle('E2:I'.$row)
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        foreach (range('A','I') as $col) {
            $sheet2->getColumnDimension($col)->setAutoSize(true);
        }

        // border tipis
        $sheet2->getStyle('A1:I'.($row))->getBorders()->getAllBorders()
               ->setBorderStyle(Border::BORDER_THIN);

        // ---------- Sheet 3: Takeaway ----------
        $sheet3 = $spreadsheet->createSheet();
        $sheet3->setTitle('Takeaway');

        $headersT = [
            'A1' => 'ID Takeaway',
            'B1' => 'Kode',
            'C1' => 'Tanggal',
            'D1' => 'Nama',
            'E1' => 'Total Jual',
            'F1' => 'Diskon',
            'G1' => 'Metode',
            'H1' => 'Pajak',
            'I1' => 'Grand Total',
        ];
        foreach ($headersT as $cell => $text) {
            $sheet3->setCellValue($cell, $text);
        }

        $sheet3->getStyle('A1:I1')->getFont()->setBold(true);
        $sheet3->getStyle('A1:I1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet3->freezePane('A2');

        $row = 2;
        foreach ($trxT as $t) {
            $sheet3->setCellValue('A'.$row, $t['id_takeaway']);
            $sheet3->setCellValue('B'.$row, $t['kode_takeaway']);
            $sheet3->setCellValue('C'.$row, $t['tanggal']);
            $sheet3->setCellValue('D'.$row, $t['nama']);
            $sheet3->setCellValue('E'.$row, (float)$t['total_jual']);
            $sheet3->setCellValue('F'.$row, (float)$t['diskon_total']);
            $sheet3->setCellValue('G'.$row, $t['metode']);
            $sheet3->setCellValue('H'.$row, (float)$t['pajak_nominal']);
            $sheet3->setCellValue('I'.$row, (float)$t['grand_total']);
            $row++;
        }

        $sheet3->setCellValue('D'.$row, 'TOTAL');
        $sheet3->getStyle('D'.$row)->getFont()->setBold(true);
        $sheet3->setCellValue('E'.$row, $sumTotalT);
        $sheet3->setCellValue('F'.$row, $sumDiskonT);
        $sheet3->setCellValue('H'.$row, $sumPajakT);
        $sheet3->setCellValue('I'.$row, $sumGrandT);
        $sheet3->getStyle('E2:I'.$row)
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        foreach (range('A','I') as $col) {
            $sheet3->getColumnDimension($col)->setAutoSize(true);
        }

        $sheet3->getStyle('A1:I'.($row))->getBorders()->getAllBorders()
               ->setBorderStyle(Border::BORDER_THIN);

        // ---------- Output ----------
        $filename = 'Laporan_Transaksi_Harian_' . date('Ymd_His') . '.xlsx';
        $writer = new Xlsx($spreadsheet);

        // headers untuk XLSX
        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="'.$filename.'"')
            ->setHeader('Cache-Control', 'max-age=0')
            ->setBody($this->saveToString($writer));
    }

    public function metode_penerimaan()
    {
        $data['user'] = $this->user->data;
        return view('admin/laporan/transaksi/metode_penerimaan',$data);
    }

    public function metode_penerimaan_proses()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();
        $methods = ['cash','qris','debit','lainnya'];

        // ===== Ambil SUM per metode untuk DINE-IN =====
        $rowsD = $db->table('vtransaksi')
            ->select('LOWER(metode) AS metode, COUNT(*) as trx_count, COALESCE(SUM(grand_total),0) as nominal', false)
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->groupBy('metode')
            ->get()->getResultArray();

        // ===== Ambil SUM per metode untuk TAKEAWAY =====
        $rowsT = $db->table('vtransaksitakeaway')
            ->select('LOWER(metode) AS metode, COUNT(*) as trx_count, COALESCE(SUM(grand_total),0) as nominal', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status', 'closed')
            ->groupBy('metode')
            ->get()->getResultArray();

        // ===== Normalisasi & gabungkan ke struktur ringkasan =====
        $summary = [];
        foreach ($methods as $m) {
            $summary[$m] = [
                'd_nominal' => 0.0, 'd_count' => 0,
                't_nominal' => 0.0, 't_count' => 0,
            ];
        }

        foreach ($rowsD as $r) {
            $k = in_array($r['metode'], $methods) ? $r['metode'] : 'lainnya';
            $summary[$k]['d_nominal'] += (float)$r['nominal'];
            $summary[$k]['d_count']   += (int)$r['trx_count'];
        }

        foreach ($rowsT as $r) {
            $k = in_array($r['metode'], $methods) ? $r['metode'] : 'lainnya';
            $summary[$k]['t_nominal'] += (float)$r['nominal'];
            $summary[$k]['t_count']   += (int)$r['trx_count'];
        }

        // ===== Hitung total & persentase =====
        $grandAll = 0.0;
        foreach ($methods as $m) {
            $summary[$m]['total_nominal'] = $summary[$m]['d_nominal'] + $summary[$m]['t_nominal'];
            $summary[$m]['total_count']   = $summary[$m]['d_count']   + $summary[$m]['t_count'];
            $grandAll += $summary[$m]['total_nominal'];
        }

        // Persentase kontribusi
        foreach ($methods as $m) {
            $summary[$m]['percent'] = $grandAll > 0 ? ($summary[$m]['total_nominal'] / $grandAll * 100.0) : 0;
        }

        // Total baris bawah
        $totals = [
            'd_nominal' => array_sum(array_column($summary, 'd_nominal')),
            't_nominal' => array_sum(array_column($summary, 't_nominal')),
            'total_nominal' => $grandAll,
            'd_count' => array_sum(array_column($summary, 'd_count')),
            't_count' => array_sum(array_column($summary, 't_count')),
            'total_count' => array_sum(array_column($summary, 'total_count')),
        ];

        $data = [
            'title'     => 'Ringkasan Penerimaan per Metode',
            'rangeText' => date('d/m/Y', strtotime($startDate)) . " - " . date('d/m/Y', strtotime($endDate)),
            'printedAt' => date('d/m/Y H:i'),
            'summary'   => $summary,
            'totals'    => $totals,
            'methods'   => $methods,
        ];

        $html = view('print/laporan/transaksi/metode_penerimaan', $data);

        $options = new \Dompdf\Options();
        $options->set('isRemoteEnabled', true);
        $dompdf = new \Dompdf\Dompdf($options);
        $dompdf->loadHtml($html, 'UTF-8');
        $dompdf->setPaper('A4', 'landscape'); // landscape biar lega
        $dompdf->render();

        return $this->response->setContentType('application/pdf')->setBody($dompdf->output());
    }

    public function metode_penerimaan_proses_excel()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();
        $methods = ['cash','qris','debit','lainnya'];

        // ===== Ambil SUM per metode untuk DINE-IN =====
        $rowsD = $db->table('vtransaksi')
            ->select('LOWER(metode) AS metode, COUNT(*) as trx_count, COALESCE(SUM(grand_total),0) as nominal', false)
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->groupBy('metode')
            ->get()->getResultArray();

        // ===== Ambil SUM per metode untuk TAKEAWAY =====
        $rowsT = $db->table('vtransaksitakeaway')
            ->select('LOWER(metode) AS metode, COUNT(*) as trx_count, COALESCE(SUM(grand_total),0) as nominal', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status', 'closed')
            ->groupBy('metode')
            ->get()->getResultArray();

        // ===== Normalisasi & gabungkan =====
        $summary = [];
        foreach ($methods as $m) {
            $summary[$m] = ['d_nominal'=>0.0,'d_count'=>0,'t_nominal'=>0.0,'t_count'=>0];
        }
        foreach ($rowsD as $r) {
            $k = in_array($r['metode'], $methods) ? $r['metode'] : 'lainnya';
            $summary[$k]['d_nominal'] += (float)$r['nominal'];
            $summary[$k]['d_count']   += (int)$r['trx_count'];
        }
        foreach ($rowsT as $r) {
            $k = in_array($r['metode'], $methods) ? $r['metode'] : 'lainnya';
            $summary[$k]['t_nominal'] += (float)$r['nominal'];
            $summary[$k]['t_count']   += (int)$r['trx_count'];
        }

        // ===== Total & persen =====
        $grandAll = 0.0;
        foreach ($methods as $m) {
            $summary[$m]['total_nominal'] = $summary[$m]['d_nominal'] + $summary[$m]['t_nominal'];
            $summary[$m]['total_count']   = $summary[$m]['d_count']   + $summary[$m]['t_count'];
            $grandAll += $summary[$m]['total_nominal'];
        }
        foreach ($methods as $m) {
            $summary[$m]['percent'] = $grandAll > 0 ? ($summary[$m]['total_nominal'] / $grandAll * 100.0) : 0;
        }

        $totals = [
            'd_nominal'     => array_sum(array_column($summary, 'd_nominal')),
            't_nominal'     => array_sum(array_column($summary, 't_nominal')),
            'total_nominal' => $grandAll,
            'd_count'       => array_sum(array_column($summary, 'd_count')),
            't_count'       => array_sum(array_column($summary, 't_count')),
            'total_count'   => array_sum(array_column($summary, 'total_count')),
        ];

        // ================= Excel output =================
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Metode');

        $rangeText = date('d/m/Y', strtotime($startDate)) . " - " . date('d/m/Y', strtotime($endDate));
        $printedAt = date('d/m/Y H:i');

        // Header judul
        $sheet->setCellValue('A1', 'Ringkasan Penerimaan per Metode');
        $sheet->mergeCells('A1:H1');
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue('A2', 'Periode')->setCellValue('B2', $rangeText);
        $sheet->setCellValue('A3', 'Dicetak')->setCellValue('B3', $printedAt);

        // Header tabel
        $headers = [
            'A5' => 'Metode',
            'B5' => 'Dine-in Trx',
            'C5' => 'Dine-in Nominal',
            'D5' => 'Takeaway Trx',
            'E5' => 'Takeaway Nominal',
            'F5' => 'Total Trx',
            'G5' => 'Total Nominal',
            'H5' => 'Kontribusi',
        ];
        foreach ($headers as $cell => $text) $sheet->setCellValue($cell, $text);
        $sheet->getStyle('A5:H5')->getFont()->setBold(true);
        $sheet->getStyle('A5:H5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->freezePane('A6');
        $sheet->setAutoFilter('A5:H5');

        // Isi baris
        $row = 6;
        foreach ($methods as $m) {
            $d_count = $summary[$m]['d_count'];
            $d_nom   = $summary[$m]['d_nominal'];
            $t_count = $summary[$m]['t_count'];
            $t_nom   = $summary[$m]['t_nominal'];
            $tot_c   = $summary[$m]['total_count'];
            $tot_n   = $summary[$m]['total_nominal'];
            $pct     = ($summary[$m]['percent'] / 100.0); // 0..1

            $sheet->setCellValue('A'.$row, strtoupper($m));
            $sheet->setCellValue('B'.$row, $d_count);
            $sheet->setCellValue('C'.$row, $d_nom);
            $sheet->setCellValue('D'.$row, $t_count);
            $sheet->setCellValue('E'.$row, $t_nom);
            $sheet->setCellValue('F'.$row, $tot_c);
            $sheet->setCellValue('G'.$row, $tot_n);
            $sheet->setCellValue('H'.$row, $pct);

            $row++;
        }

        // Total baris bawah
        $sheet->setCellValue('A'.$row, 'TOTAL');
        $sheet->getStyle('A'.$row)->getFont()->setBold(true);
        $sheet->setCellValue('B'.$row, $totals['d_count']);
        $sheet->setCellValue('C'.$row, $totals['d_nominal']);
        $sheet->setCellValue('D'.$row, $totals['t_count']);
        $sheet->setCellValue('E'.$row, $totals['t_nominal']);
        $sheet->setCellValue('F'.$row, $totals['total_count']);
        $sheet->setCellValue('G'.$row, $totals['total_nominal']);
        $sheet->setCellValue('H'.$row, ($totals['total_nominal'] > 0 ? 1 : 0));

        // Format angka & persen
        $sheet->getStyle('C6:C'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('E6:E'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('G6:G'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('H6:H'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);

        // Border & autosize
        $sheet->getStyle('A5:H'.$row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        foreach (range('A','H') as $col) $sheet->getColumnDimension($col)->setAutoSize(true);

        // Output
        $filename = 'Metode_Penerimaan_' . date('Ymd_His') . '.xlsx';
        $writer = new Xlsx($spreadsheet);
        ob_start();
        $writer->save('php://output');
        $binary = ob_get_clean();

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="'.$filename.'"')
            ->setHeader('Cache-Control', 'max-age=0')
            ->setBody($binary);
    }

    public function belanja_harian()
    {
        $data['user'] = $this->user->data;
        return view('admin/laporan/transaksi/belanja_harian',$data);
    }

    public function belanja_harian_proses()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // Detail belanja/pengeluaran
        $rows = $db->table('vbelanja')
            ->select('tanggal, keterangan, nominal, nama')
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->orderBy('tanggal', 'ASC')
            ->get()->getResultArray();

        // Total keseluruhan
        $totalBelanja = 0;
        foreach ($rows as $r) { $totalBelanja += (float)$r['nominal']; }

        // Ringkasan per operator
        $ops = $db->table('vbelanja')
            ->select('nama, COUNT(*) as jml, COALESCE(SUM(nominal),0) as total', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->groupBy('nama')
            ->orderBy('nama','ASC')
            ->get()->getResultArray();

        $totalTransaksi = array_sum(array_column($ops, 'jml'));

        $data = [
            'title'         => 'Daftar Belanja / Pengeluaran Harian',
            'rangeText'     => date('d/m/Y', strtotime($startDate)).' - '.date('d/m/Y', strtotime($endDate)),
            'printedAt'     => date('d/m/Y H:i'),
            'rows'          => $rows,
            'totalBelanja'  => $totalBelanja,
            'ops'           => $ops,
            'totalTransaksi'=> $totalTransaksi,
        ];

        $html = view('print/laporan/transaksi/belanja_harian', $data);

        $options = new \Dompdf\Options();
        $options->set('isRemoteEnabled', true);
        $dompdf = new \Dompdf\Dompdf($options);
        $dompdf->loadHtml($html, 'UTF-8');
        $dompdf->setPaper('A4', 'portrait'); // portrait cukup; ganti 'landscape' kalau mau lebih lega
        $dompdf->render();

        return $this->response->setContentType('application/pdf')->setBody($dompdf->output());
    }

    public function belanja_harian_proses_excel()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // Detail belanja/pengeluaran
        $rows = $db->table('vbelanja')
            ->select('tanggal, keterangan, nominal, nama')
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->orderBy('tanggal', 'ASC')
            ->get()->getResultArray();

        // Total keseluruhan
        $totalBelanja = 0;
        foreach ($rows as $r) { $totalBelanja += (float)$r['nominal']; }

        // Ringkasan per operator
        $ops = $db->table('vbelanja')
            ->select('nama, COUNT(*) as jml, COALESCE(SUM(nominal),0) as total', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->groupBy('nama')
            ->orderBy('nama','ASC')
            ->get()->getResultArray();

        $totalTransaksi = array_sum(array_column($ops, 'jml'));

        // ===== Excel =====
        $rangeText = date('d/m/Y', strtotime($startDate)).' - '.date('d/m/Y', strtotime($endDate));
        $printedAt = date('d/m/Y H:i');

        $ss = new Spreadsheet();

        // Sheet 1: Detail
        $s1 = $ss->getActiveSheet();
        $s1->setTitle('Detail Belanja');

        $s1->setCellValue('A1', 'Daftar Belanja / Pengeluaran Harian');
        $s1->mergeCells('A1:E1');
        $s1->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $s1->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $s1->setCellValue('A2', 'Periode')->setCellValue('B2', $rangeText);
        $s1->setCellValue('A3', 'Dicetak')->setCellValue('B3', $printedAt);

        $headers = ['Tanggal','Keterangan','Nominal','Operator'];
        $s1->fromArray($headers, null, 'A5');
        $s1->getStyle('A5:D5')->getFont()->setBold(true);
        $s1->getStyle('A5:D5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $s1->freezePane('A6');
        $s1->setAutoFilter('A5:D5');

        $row = 6;
        foreach ($rows as $r) {
            // tanggal biarkan string; kalau mau format Excel date, bisa convert DateTime + set format
            $s1->setCellValue('A'.$row, $r['tanggal']);
            $s1->setCellValue('B'.$row, $r['keterangan']);
            $s1->setCellValue('C'.$row, (float)$r['nominal']);
            $s1->setCellValue('D'.$row, $r['nama']);
            $row++;
        }

        // Total di bawah
        $s1->setCellValue('B'.$row, 'TOTAL');
        $s1->getStyle('B'.$row)->getFont()->setBold(true);
        $s1->setCellValue('C'.$row, $totalBelanja);

        // Format angka
        $s1->getStyle('C6:C'.$row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

        // Border & autosize
        $s1->getStyle('A5:D'.$row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        foreach (range('A','D') as $c) $s1->getColumnDimension($c)->setAutoSize(true);

        // Sheet 2: Ringkasan per Operator
        $s2 = $ss->createSheet();
        $s2->setTitle('Ringkasan Operator');

        $s2->setCellValue('A1', 'Ringkasan Pengeluaran per Operator');
        $s2->mergeCells('A1:E1');
        $s2->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $s2->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $s2->setCellValue('A2', 'Periode')->setCellValue('B2', $rangeText);
        $s2->setCellValue('A3', 'Dicetak')->setCellValue('B3', $printedAt);

        $s2->fromArray(['Operator','Jumlah Transaksi','Total Nominal','Kontribusi'], null, 'A5');
        $s2->getStyle('A5:D5')->getFont()->setBold(true);
        $s2->getStyle('A5:D5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $s2->freezePane('A6');
        $s2->setAutoFilter('A5:D5');

        // total nominal untuk persen
        $grand = array_sum(array_map(fn($x)=> (float)$x['total'], $ops)) ?: 0;

        $r2 = 6;
        foreach ($ops as $o) {
            $pct = $grand > 0 ? ((float)$o['total'] / $grand) : 0; // 0..1
            $s2->setCellValue('A'.$r2, $o['nama']);
            $s2->setCellValue('B'.$r2, (int)$o['jml']);
            $s2->setCellValue('C'.$r2, (float)$o['total']);
            $s2->setCellValue('D'.$r2, $pct);
            $r2++;
        }

        // Baris total
        $s2->setCellValue('A'.$r2, 'TOTAL');
        $s2->getStyle('A'.$r2)->getFont()->setBold(true);
        $s2->setCellValue('B'.$r2, $totalTransaksi);
        $s2->setCellValue('C'.$r2, $grand);
        $s2->setCellValue('D'.$r2, ($grand > 0 ? 1 : 0));

        $s2->getStyle('C6:C'.$r2)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $s2->getStyle('D6:D'.$r2)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
        $s2->getStyle('A5:D'.$r2)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        foreach (range('A','D') as $c) $s2->getColumnDimension($c)->setAutoSize(true);

        // Output
        $filename = 'Belanja_Harian_' . date('Ymd_His') . '.xlsx';
        $writer = new Xlsx($ss);
        ob_start();
        $writer->save('php://output');
        $binary = ob_get_clean();

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="'.$filename.'"')
            ->setHeader('Cache-Control', 'max-age=0')
            ->setBody($binary);
    }

    public function laba_rugi()
    {
        $data['user'] = $this->user->data;
        return view('admin/laporan/keuangan/laba_rugi',$data);
    }

    public function laba_rugi_proses()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // ===== Agregasi Dine-In (pakai total_modal) =====
        $aggD = $db->table('vtransaksi')
            ->select('
                COALESCE(SUM(total_jual),0)       AS total,
                COALESCE(SUM(diskon_total),0)     AS diskon,
                COALESCE(SUM(pajak_nominal),0)    AS pajak,
                COALESCE(SUM(grand_total),0)      AS grand,
                COALESCE(SUM(total_modal),0)      AS modal
            ', false)
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->get()->getRowArray();

        // ===== Agregasi Takeaway (pakai total_modal) =====
        $aggT = $db->table('vtransaksitakeaway')
            ->select('
                COALESCE(SUM(total_jual),0)       AS total,
                COALESCE(SUM(diskon_total),0)     AS diskon,
                COALESCE(SUM(pajak_nominal),0)    AS pajak,
                COALESCE(SUM(grand_total),0)      AS grand,
                COALESCE(SUM(total_modal),0)      AS modal
            ', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status', 'closed')
            ->get()->getRowArray();

        // ===== Komponen Laba Rugi =====
        $penjualanKotor = (float)$aggD['total'] + (float)$aggT['total'];     // sebelum pajak & diskon
        $totalDiskon    = (float)$aggD['diskon'] + (float)$aggT['diskon'];
        $totalPajak     = (float)$aggD['pajak']  + (float)$aggT['pajak'];
        $omzetGrand     = (float)$aggD['grand']  + (float)$aggT['grand'];    // setelah diskon + pajak

        // ✅ HPP/COGS dari total_modal (tanpa join detail)
        $cogs = (float)$aggD['modal'] + (float)$aggT['modal'];

        $biayaRow = $db->table('vbelanja')
            ->select('COALESCE(SUM(nominal),0) AS total', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->get()->getRowArray();
        $biayaOperasional = (float)($biayaRow['total'] ?? 0);

        $labaKotor  = $penjualanKotor - $cogs;
        $labaBersih = $labaKotor - $biayaOperasional;

        $grossMarginPct = $penjualanKotor > 0 ? ($labaKotor / $penjualanKotor * 100) : 0;
        $netMarginPct   = $penjualanKotor > 0 ? ($labaBersih / $penjualanKotor * 100) : 0;

        $data = [
            'title'            => 'Laba Rugi',
            'rangeText'        => date('d/m/Y', strtotime($startDate)).' - '.date('d/m/Y', strtotime($endDate)),
            'printedAt'        => date('d/m/Y H:i'),

            'penjualanKotor'   => $penjualanKotor,
            'totalDiskon'      => $totalDiskon,
            'totalPajak'       => $totalPajak,
            'omzetGrand'       => $omzetGrand,

            'cogs'             => $cogs,                 // dari total_modal
            'labaKotor'        => $labaKotor,
            'biayaOperasional' => $biayaOperasional,
            'labaBersih'       => $labaBersih,

            'grossMarginPct'   => $grossMarginPct,
            'netMarginPct'     => $netMarginPct,
        ];

        $html = view('print/laporan/keuangan/laba_rugi', $data);

        $options = new \Dompdf\Options();
        $options->set('isRemoteEnabled', true);
        $dompdf = new \Dompdf\Dompdf($options);
        $dompdf->loadHtml($html, 'UTF-8');
        $dompdf->setPaper('A4', 'portrait');
        $dompdf->render();

        return $this->response->setContentType('application/pdf')->setBody($dompdf->output());
    }

    public function laba_rugi_proses_excel()
    {
        $startDate = $this->request->getGet('start');
        $endDate   = $this->request->getGet('end');

        if (!$startDate || !$endDate) {
            $today = date('Y-m-d');
            $startDate = $startDate ?: $today;
            $endDate   = $endDate   ?: $today;
        }

        $start = $startDate . " 00:00:00";
        $end   = date('Y-m-d 00:00:00', strtotime($endDate . ' +1 day'));

        $db = db_connect();

        // ===== Agregasi Dine-In =====
        $aggD = $db->table('vtransaksi')
            ->select('
                COALESCE(SUM(total_jual),0)    AS total,
                COALESCE(SUM(diskon_total),0)  AS diskon,
                COALESCE(SUM(pajak_nominal),0) AS pajak,
                COALESCE(SUM(grand_total),0)   AS grand,
                COALESCE(SUM(total_modal),0)   AS modal
            ', false)
            ->where('tanggal_order >=', $start)
            ->where('tanggal_order <',  $end)
            ->where('status', 'closed')
            ->get()->getRowArray();

        // ===== Agregasi Takeaway =====
        $aggT = $db->table('vtransaksitakeaway')
            ->select('
                COALESCE(SUM(total_jual),0)    AS total,
                COALESCE(SUM(diskon_total),0)  AS diskon,
                COALESCE(SUM(pajak_nominal),0) AS pajak,
                COALESCE(SUM(grand_total),0)   AS grand,
                COALESCE(SUM(total_modal),0)   AS modal
            ', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->where('status', 'closed')
            ->get()->getRowArray();

        // ===== Komponen Laba Rugi =====
        $penjualanKotor = (float)$aggD['total'] + (float)$aggT['total'];
        $totalDiskon    = (float)$aggD['diskon'] + (float)$aggT['diskon'];
        $totalPajak     = (float)$aggD['pajak']  + (float)$aggT['pajak'];
        $omzetGrand     = (float)$aggD['grand']  + (float)$aggT['grand'];
        $cogs           = (float)$aggD['modal']  + (float)$aggT['modal'];

        $biayaRow = $db->table('vbelanja')
            ->select('COALESCE(SUM(nominal),0) AS total', false)
            ->where('tanggal >=', $start)
            ->where('tanggal <',  $end)
            ->get()->getRowArray();
        $biayaOperasional = (float)($biayaRow['total'] ?? 0);

        $labaKotor  = $penjualanKotor - $cogs;
        $labaBersih = $labaKotor - $biayaOperasional;

        $grossMarginPct = $penjualanKotor > 0 ? ($labaKotor / $penjualanKotor * 100) : 0;
        $netMarginPct   = $penjualanKotor > 0 ? ($labaBersih / $penjualanKotor * 100) : 0;

        // ===== Excel =====
        $rangeText = date('d/m/Y', strtotime($startDate)) . ' - ' . date('d/m/Y', strtotime($endDate));
        $printedAt = date('d/m/Y H:i');

        $ss = new Spreadsheet();
        $sh = $ss->getActiveSheet();
        $sh->setTitle('Laba Rugi');

        // Judul
        $sh->setCellValue('A1', 'Laporan Laba Rugi');
        $sh->mergeCells('A1:D1');
        $sh->getStyle('A1')->getFont()->setBold(true)->setSize(14);
        $sh->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sh->setCellValue('A2', 'Periode')->setCellValue('B2', $rangeText);
        $sh->setCellValue('A3', 'Dicetak')->setCellValue('B3', $printedAt);

        // Tabel utama
        $rows = [
            ['Penjualan Kotor',        $penjualanKotor],
            ['Total Diskon',           $totalDiskon],
            ['Total Pajak',            $totalPajak],
            ['Omzet (Grand Total)',    $omzetGrand],
            ['HPP / COGS',             $cogs],
            ['Laba Kotor',             $labaKotor],
            ['Biaya Operasional',      $biayaOperasional],
            ['Laba Bersih',            $labaBersih],
            ['Gross Margin %',         $grossMarginPct/100], // pakai format persen (0..1)
            ['Net Margin %',           $netMarginPct/100],
        ];

        // Header mini
        $sh->setCellValue('A5', 'Komponen');
        $sh->setCellValue('B5', 'Nilai');
        $sh->getStyle('A5:B5')->getFont()->setBold(true);
        $sh->getStyle('A5:B5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sh->freezePane('A6');

        // Isi
        $r = 6;
        foreach ($rows as $row) {
            $sh->setCellValue('A'.$r, $row[0]);
            $sh->setCellValue('B'.$r, $row[1]);
            $r++;
        }

        // Format angka & persen
        $sh->getStyle('B6:B13')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        // Baris persen (Gross & Net) ada di dua baris terakhir
        $sh->getStyle('B'.($r-2).':B'.($r-1))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);

        // Border & autosize
        $sh->getStyle('A5:B'.($r-1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        foreach (range('A','D') as $c) $sh->getColumnDimension($c)->setAutoSize(true);

        // Output
        $filename = 'Laba_Rugi_' . date('Ymd_His') . '.xlsx';
        $writer = new Xlsx($ss);
        ob_start();
        $writer->save('php://output');
        $binary = ob_get_clean();

        return $this->response
            ->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            ->setHeader('Content-Disposition', 'attachment; filename="'.$filename.'"')
            ->setHeader('Cache-Control', 'max-age=0')
            ->setBody($binary);
    }
}
?>