<?php

namespace App\Controllers;
//require APPPATH . 'ThirdParty/dompdf/autoload.inc.php';
require APPPATH . 'ThirdParty/mpdf/mpdf.php';
//require APPPATH.'ThirdParty/dompdf/src/Adapter/CPDF.php';

//require APPPATH . 'ThirdParty/dompdf/autoload.inc.php';

use Endroid\QrCode\Color\Color;
use Endroid\QrCode\Encoding\Encoding;
use Endroid\QrCode\ErrorCorrectionLevel\ErrorCorrectionLevelLow;
use Endroid\QrCode\QrCode;
use Endroid\QrCode\Label\Label;
//use Endroid\QrCode\Logo\Logo;
use Endroid\QrCode\RoundBlockSizeMode\RoundBlockSizeModeMargin;
use Endroid\QrCode\Writer\PngWriter;
use Mpdf;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
//use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;


class PdfMain extends BaseController
{
    function index()
    {
        //return view('/PdfMain/pdf_view');
        echo 'Cetak';
    }

    function etiketcetak($koded, $bus = 0, $aindex = 0)
    {
        ini_set("memory_limit", "64M");
        $request = \Config\Services::request();
        //$session = \Config\Services::session();

        //error_reporting(E_ALL & ~E_NOTICE & ~E_WARNING);

        if ($bus == 0) {
            $q = "select a.balik,a.etiket,a.armada_id,a.berangkat_tgl,a.berangkat_jam,a.tempatkumpul,b.nik,b.nama,b.wilayah_id,b.kat,c.perusahaan,c.nobus,c.nama as namabus,c.nopol,c.jenis,d.nama as tujuan from tbl_mudik a 
        left join tbl_warga b on a.warga_id=b.warga_id
        left join tbl_armada c on a.armada_id=c.armada_id
        left join mst_wilayah d on b.wilayah_id=d.wilayah_id
        where b.statusdata='1' and b.koded =:koded: and b.status in (2,3,4) ";
        } else {
            $q = "select a.balik,a.etiket,a.armada_id,a.berangkat_tgl,a.berangkat_jam,a.tempatkumpul,b.nik,b.nama,b.wilayah_id,b.kat,c.perusahaan,c.nobus,c.nama as namabus,c.jenis,c.nopol,d.nama as tujuan from tbl_mudik a 
            left join tbl_warga b on a.warga_id=b.warga_id
            left join tbl_armada c on a.armada_id=c.armada_id
            left join mst_wilayah d on b.wilayah_id=d.wilayah_id
            where a.armada_id =:bus: and b.statusdata=1 and b.status in (2,3,4) ";
        }

        $query = $this->db->query($q, ['koded' => $koded, 'bus' => $bus]);

        $result = $query->getResultArray();
        $jmlrecord = $query->getNumRows();

        $resultc = array_chunk($result, 100, true);

        if ($result) $perusahaan = $result[0]['perusahaan'];
        //$kat = intval($result[0]['kat']);
        else $perusahaan = '';
        //$kat = 0;

        if ($perusahaan == 'GUBERNUR') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_banhub.jpg" width="120px"></td>';
            $html .= '<td><H2>BADAN PENGHUBUNG PEMERINTAH PROVINSI JAWA TENGAH</h2></td></tr></table><hr/>';
        } else if ($perusahaan == 'BANK JATENG') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_bankjateng.jpg" width="150px"></td>';
            $html .= '<td></tr></table><hr/>';
        } else if ($perusahaan == 'Bus Bantuan Bupati/Walikota') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_pjt.png" width="120px"></td>';
            $html .= '<td></tr></table><hr/>';
        } else if ($perusahaan == 'KAB/KOTA') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_pjt.png" width="120px"></td>';
            $html .= '<td></tr></table><hr/>';
        } else if ($perusahaan == 'JASA RAHARJA') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_jasaraharja.png" width="120px"></td>';
            $html .= '<td></tr></table><hr/>';
        } else if ($perusahaan == 'SEMEN GRESIK') {
            $html = '<table><tr><td><img src="assets/images/pemprovsmr/etiket_semengresik.png" width="120px"></td>';
            $html .= '<td></tr></table><hr/>';
        }

        $html .= '<H3 style="text-align:center">E-TIKET PEDAMATENG</h3>';

        $html .= '<table class="table"  width="100%">';
        $i = 1;
        //$html .= '<tr><th>NO</th><th align="center" colspan="2">DATA PEMUDIK</th><th>e-TIKET</th></tr>';

        foreach ($resultc[$aindex] as $k => $v) {
            $this->createqr($v['etiket']);

            if ($v['jenis'] == 1) {
                $nob = 'NOMOR BUS<br/>NAMA BUS';
            } else {
                $nob = 'NAMA KA';
            }

            if ($v['balik'] == '0')
                $deskripsi1 = '<i>NIK<BR/>NAMA<BR/>TUJUAN<br/>' . $nob . '<BR/>TGL BERANGKAT<BR/>JAM DAFTAR ULANG<BR/>TEMPAT KUMPUL<br/>KODE e-TIKET</i>';
            else {
                if ($v['jenis'] == 1)
                    $deskripsi1 = '<i>NIK<BR/>NAMA<BR/>TITIK KUMPUL<br/>' . $nob . '<BR/>TGL BERANGKAT<BR/>JAM DAFTAR ULANG<BR/>TUJUAN<br/>KODE e-TIKET</i>';
                else
                    $deskripsi1 = '<i>NIK<BR/>NAMA<BR/>TITIK KUMPUL<br/>' . $nob . '<BR/>TGL BERANGKAT<BR/>KODE BOOKING<BR/>TUJUAN</i>';
            }
            if ($v['jenis'] == 1)
                $deskripsi2 = $v['nik'] . '<br/><b>' . $v['nama'] . '</b><br/>' . $v['tujuan'] . '<br/>' . $v['nobus'] . '<br/>' . $v['namabus'] . '<br/>' . $v['berangkat_tgl'] . '<br/>' . $v['berangkat_jam'] . '<br/>' . $v['tempatkumpul'] . '<br/>' . $v['etiket'];
            else
                $deskripsi2 = $v['nik'] . '<br/><b>' . $v['nama'] . '</b><br/>' . $v['tujuan'] . '<br/>' . $v['namabus'] . '<br/>' . $v['berangkat_tgl'] . '<br/>' . 'KODE BOOKING AKAN DIBERIKAN PADA TGL 18-APR-2024 MELALUI WA TERDAFTAR' . '<br/>' . $v['tempatkumpul'];

            $etiket = '<img src="assets/etiket/' . $v['etiket'] . '.png" width="100px">';

            if (intval($v['armada_id']) <= 0 || intval($v['wilayah_id']) <= 0)
                $etiket = '<img src="assets/etiket/' . $v['etiket'] . '.png" width="100px"><br><span style="font-weight:bold;color:red">BELUM BERLAKU</span>';

            if ($v['jenis'] == '1') {
                if ($i % 2 == 0)
                    $html .= '<tr><td align="center" width="5%">' . ($k + 1) . '</td><td width="20%" align="right">' . $deskripsi1 . '</td><td width="25%">' . $deskripsi2 . '</td><td width="50%" align="center">' . $etiket . '<br/><br/><br/></td></tr>';
                else
                    $html .= '<tr><td align="center" width="5%">' . ($k + 1) . '</td><td width="20%" align="right">' . $etiket . '</td><td width="25%">' . $deskripsi1 . '</td><td width="50%" align="left">' . $deskripsi2 . '<br/><br/><br/></td></tr>';
                $i++;
            } else {

                $html .= '<tr><td align="center" width="2%">' . ($k + 1) . '</td><td width="20%" align="right">' . $deskripsi1 . '</td><td width="78%">' . $deskripsi2 . '</td><td width="3%" align="center">' . '' . '<br/><br/><br/></td></tr>';

                $i++;
            }
        }

        $html .= '</table><br/>';


        //        ob_start(); //Start output buffer
        //        recursive($result, $level = 0);
        //        $output = ob_get_contents(); //Grab output
        //        ob_end_clean(); //Discard output buffer

        $filename = date('y-m-d-H-i-s') . '-qadr-labs-report';

        //$mpdf = new Mpdf();
        $mpdf = new \Mpdf\Mpdf();
        //$mpdf = new Mpdf(['mode' => 'utf-8', 'format' => 'A4','default_font_size' => 16]);
        $mpdf->AddPage('P', '', '', '', '', 10, 10, 10, 10, 10, 10);
        $stylesheet = file_get_contents('assets/admin/dist/css/print3.css');
        $mpdf->WriteHTML($stylesheet, 1);

        //$html = '<H1>E-TIKET PEDAMATENG</h1>';
        $mpdf->WriteHTML($html, 2);
        $this->response->setHeader('Content-Type', 'application/x-pdf');
        $mpdf->Output(); // opens in browser
    }

    function warga_cetak($balik = 0)
    {
        $request = \Config\Services::request();
        $session = \Config\Services::session();
        //$session->set('qwarga', array($q,$subtitle));
        $d = $session->get('qwarga');

        $query = $this->db->query($d[0]);
        $result = $query->getResultArray();
        $rekap = array();
        foreach ($result as $k => $v) {
            $rekap[$v['wilayaht']][] = '1';
        }
        $rekapf = array();
        foreach ($rekap as $k => $v) {
            $rekapf[$k] = sizeof($rekap[$k]);
        }

        if ($balik == 0) {
            $html = '<h2>DAFTAR CALON PEMUDIK PEDAMATENG<br/>';
            $html .= 'Untuk pemudik dengan status : ' . $d[1] . '</h2>';
        } else {
            $html = '<h2>DAFTAR CALON ARUS BALIK PEDAMATENG<br/>';
            $html .= 'Untuk arus balik dengan status : ' . $d[1] . '</h2>';
        }

        $i = 1;
        $html .= '<table class="tableeparkir" width="100%">';
        if ($balik ==  0)
            $html .= '<tr><th>NO</th><th>TUJUAN MUDIK</th><th>JUMLAH PEMUDIK</th></tr>';
        else
            $html .= '<tr><th>NO</th><th>ARUS BALIK DARI</th><th>JUMLAH ARUS BALIK</th></tr>';

        foreach ($rekapf as $k => $v) {
            if ($k !== '') {
                $html .= '<tr><th>' . $i . '</th><td>' . $k . '</td><th>' . $v . '</th></tr>';
                $i++;
            }
        }
        $html .= '</table><br/>';

        $html .= '<table class="tableeparkir" width="100%">';

        $i = 1;
        $html .= '<tr><th>NO</th><th>GRUP</th><th>NIK</th><th>NAMA</th><th>NO TELP.</th><th>TUJUAN</th><tr>';
        foreach ($result as $k => $v) {
            $html .= '<tr><td align="center">' . $i . '</td><td>' . $v['koded'] . '</td><td>' . $v['nik'] . '</td><td>' . $v['nama'] . '</td><td>' . $v['nowa'] . '</td><td>' . $v['wilayaht'] . '</td></tr>';
            $i++;
        }
        $html .= '</table>';

        $mpdf = new \Mpdf\Mpdf();
        //$mpdf = new Mpdf(['mode' => 'utf-8', 'format' => 'A4','default_font_size' => 16]);
        $mpdf->AddPage('P', '', '', '', '', 10, 10, 10, 10, 10, 10);
        $stylesheet = file_get_contents('assets/admin/dist/css/print3.css');
        $mpdf->WriteHTML($stylesheet, 1);

        //$html = '<H1>E-TIKET PEDAMATENG</h1>';
        $mpdf->WriteHTML($html, 2);
        $this->response->setHeader('Content-Type', 'application/pdf');
        $mpdf->Output(); // opens in browser

    }

    function wargabus_cetak($balik = 0)
    {
        $request = \Config\Services::request();
        $session = \Config\Services::session();
        //$session->set('qwarga', array($q,$subtitle));
        $d = $session->get('qwarga');

        $query = $this->db->query($d[0]);
        $result = $query->getResultArray();
        $rekap = array();
        foreach ($result as $k => $v) {
            $rekap[$v['wilayaht']][] = '1';
        }
        $rekapf = array();
        foreach ($rekap as $k => $v) {
            $rekapf[$k] = sizeof($rekap[$k]);
        }

        if ($balik == 0) {
            $html = '<h2>DAFTAR CALON PEMUDIK PEDAMATENG<br/>';
            $html .= 'Untuk pemudik dengan status : ' . $d[1] . '</h2>';
        } else {
            $html = '<h2>DAFTAR CALON ARUS BALIK PEDAMATENG<br/>';
            $html .= 'Untuk arus balik dengan status : ' . $d[1] . '</h2>';
        }

        $i = 1;
        $html .= '<table class="tableeparkir" width="100%">';
        if ($balik ==  0)
            $html .= '<tr><th>NO</th><th>TUJUAN MUDIK</th><th>JUMLAH PEMUDIK</th></tr>';
        else
            $html .= '<tr><th>NO</th><th>ARUS BALIK DARI</th><th>JUMLAH ARUS BALIK</th></tr>';

        foreach ($rekapf as $k => $v) {
            if ($k !== '') {
                $html .= '<tr><th>' . $i . '</th><td>' . $k . '</td><th>' . $v . '</th></tr>';
                $i++;
            }
        }
        $html .= '</table><br/>';

        $html .= '<table class="tableeparkir" width="100%">';

        $i = 1;
        $html .= '<tr><th>NO</th><th>GRUP</th><th>NAMA</th><th>TUJUAN</th><tr>';
        //$html .= '<tr><th>NO</th><th>GRUP</th><th>NIK</th><th>NAMA</th><th>NO TELP.</th><th>TUJUAN</th><tr>';
        foreach ($result as $k => $v) {
            $html .= '<tr><td align="center">' . $i . '</td><td>' . $v['koded'] . '</td><td>' . $v['nama'] . '</td><td>' . $v['wilayaht'] . '</td></tr>';
            //$html .= '<tr><td align="center">'.$i.'</td><td>'.$v['koded'].'</td><td>'.$v['nik'].'</td><td>'.$v['nama'].'</td><td>'.$v['nowa'].'</td><td>'.$v['wilayaht'].'</td></tr>';
            $i++;
        }
        $html .= '</table>';

        $mpdf = new \Mpdf\Mpdf();
        //$mpdf = new Mpdf(['mode' => 'utf-8', 'format' => 'A4','default_font_size' => 16]);
        $mpdf->AddPage('P', '', '', '', '', 10, 10, 10, 10, 10, 10);
        $stylesheet = file_get_contents('assets/admin/dist/css/print3.css');
        $mpdf->WriteHTML($stylesheet, 1);

        //$html = '<H1>E-TIKET PEDAMATENG</h1>';
        $mpdf->WriteHTML($html, 2);
        $this->response->setHeader('Content-Type', 'application/pdf');
        $mpdf->Output(); // opens in browser

    }


    function paguyuban_cetak($balik = 0)
    {
        $request = \Config\Services::request();
        $session = \Config\Services::session();
        //$session->set('qwarga', array($q,$subtitle));
        $d = $session->get('qwarga');

        $query = $this->db->query($d[0]);
        $result = $query->getResultArray();
        $rekap = array();
        foreach ($result as $k => $v) {
            $rekap[$v['wilayaht']][] = '1';
        }
        $rekapf = array();
        foreach ($rekap as $k => $v) {
            $rekapf[$k] = sizeof($rekap[$k]);
        }

        if ($balik == 0) {
            $html = '<h2>DAFTAR CALON PEMUDIK PEDAMATENG (PAGUYUBAN)<br/>';
            $html .= 'Untuk pemudik dengan status : ' . $d[1] . '</h2>';
        } else {
            $html = '<h2>DAFTAR CALON ARUS BALIK PEDAMATENG<br/>';
            $html .= 'Untuk arus balik dengan status : ' . $d[1] . '</h2>';
        }

        $i = 1;
        $html .= '<table class="tableeparkir" width="100%">';
        if ($balik ==  0)
            $html .= '<tr><th>NO</th><th>TUJUAN MUDIK</th><th>JUMLAH PEMUDIK</th></tr>';
        else
            $html .= '<tr><th>NO</th><th>ARUS BALIK DARI</th><th>JUMLAH ARUS BALIK</th></tr>';

        foreach ($rekapf as $k => $v) {
            if ($k !== '') {
                $html .= '<tr><th>' . $i . '</th><td>' . $k . '</td><th>' . $v . '</th></tr>';
                $i++;
            }
        }
        $html .= '</table><br/>';

        $html .= '<table class="tableeparkir" width="100%">';

        $i = 1;
        $html .= '<tr><th>NO</th><th>GRUP</th><th>NIK</th><th>NAMA</th><th>NO TELP.</th><th>TUJUAN</th><tr>';
        foreach ($result as $k => $v) {
            $html .= '<tr><td align="center">' . $i . '</td><td>' . $v['koded'] . '</td><td>' . $v['nik'] . '</td><td>' . $v['nama'] . '</td><td>' . $v['nowa'] . '</td><td>' . $v['wilayaht'] . '</td></tr>';
            $i++;
        }
        $html .= '</table>';

        $mpdf = new \Mpdf\Mpdf();
        //$mpdf = new Mpdf(['mode' => 'utf-8', 'format' => 'A4','default_font_size' => 16]);
        $mpdf->AddPage('P', '', '', '', '', 10, 10, 10, 10, 10, 10);
        $stylesheet = file_get_contents('assets/admin/dist/css/print3.css');
        $mpdf->WriteHTML($stylesheet, 1);

        //$html = '<H1>E-TIKET PEDAMATENG</h1>';
        $mpdf->WriteHTML($html, 2);
        $this->response->setHeader('Content-Type', 'application/pdf');
        $mpdf->Output(); // opens in browser

    }




    function armada_cetak()
    {
        $request = \Config\Services::request();
        $session = \Config\Services::session();

        $query = $this->db->query("select a.*,b.nama as tujuan from tbl_armada a left join mst_wilayah b on a.wilayah_id=b.wilayah_id ");
        $result = $query->getResultArray();

        $rekap = array();
        $rekap2 = array();
        foreach ($result as $k => $v) {
            $rekap[$v['tujuan']][] = '1';
            $rekap2[$v['perusahaan']][] = '1';
        }
        $rekapf = array();
        $rekapf2 = array();

        foreach ($rekap as $k => $v) {
            $rekapf[$k] = sizeof($rekap[$k]);
        }
        foreach ($rekap2 as $k => $v) {
            //            $rekapf[$k] = sizeof($rekap2[$k]);
            $rekapf2[$k] = sizeof($rekap2[$k]);
        }

        $html = '<h2>DAFTAR PENYEDIA ARMADA</h2>';
        $i = 1;

        $html .= '<table class="tableeparkir" width="100%">';
        $html .= '<tr><th colspan="3">REKAP JUMLAH ARMADA DAN TUJUAN</th></tr>';
        $html .= '<tr><th align="center">NO</th><th>TUJUAN</th><th>JUMLAH ARMADA</th></tr>';
        foreach ($rekapf as $k => $v) {
            if ($k == '') $k = '<i><b>* Belum di alokasikan</b></i>';
            $html .= '<tr><td align="center">' . $i . '</td><td>' . $k . '</td><td align="center">' . $v . '</td></tr>';
        }
        $html .= '</table><br/>';


        $html .= '<table class="tableeparkir" width="100%">';
        $html .= '<tr><th colspan="3">REKAP JUMLAH ARMADA DAN PENYEDIA</th></tr>';
        $html .= '<tr><th align="center">NO</th><th>PENYEDIA</th><th>JUMLAH ARMADA</th></tr>';
        foreach ($rekapf2 as $k => $v) {
            $html .= '<tr><td align="center">' . $i . '</td><td>' . $k . '</td><td align="center">' . $v . '</td></tr>';
        }
        $html .= '</table><br/>';



        $i = 1;
        $html .= '<table class="tableeparkir" width="100%">';
        $html .= '<tr><th>NO</th><th>PENYEDIA</th><th>NAMA ARMADA</th><th>NO BUS / NO POL</th><th>KAP</th><th>TUJUAN</th></tr>';
        foreach ($result as $k => $v) {
            $html .= '<tr><td>' . $i . '</td><td>' . $v['perusahaan'] . '</td><td>' . $v['nama'] . '</td><td><b>' . $v['nobus'] . '</b> / ' . $v['nopol'] . '</td><td align="center">' . $v['kapasitas'] . '</td><td>' . $v['tujuan'] . '</td></tr>';
            $i++;
        }


        $html .= '</table>';


        $mpdf = new \Mpdf\Mpdf();
        //$mpdf = new Mpdf(['mode' => 'utf-8', 'format' => 'A4','default_font_size' => 16]);
        $mpdf->AddPage('P', '', '', '', '', 10, 10, 10, 10, 10, 10);
        $stylesheet = file_get_contents('assets/admin/dist/css/print3.css');
        $mpdf->WriteHTML($stylesheet, 1);

        //$html = '<H1>E-TIKET PEDAMATENG</h1>';
        $mpdf->WriteHTML($html, 2);
        $this->response->setHeader('Content-Type', 'application/pdf');
        $mpdf->Output(); // opens in browser


    }


    public function createqr($etiket)
    {
        if ($etiket == null) return;
        $writer = new PngWriter();

        $qrCode = QrCode::create($etiket)
            ->setEncoding(new Encoding('UTF-8'))
            ->setErrorCorrectionLevel(new ErrorCorrectionLevelLow())
            ->setSize(300)
            ->setMargin(10)
            ->setRoundBlockSizeMode(new RoundBlockSizeModeMargin())
            ->setForegroundColor(new Color(0, 0, 0))
            ->setBackgroundColor(new Color(255, 255, 255));
        $logo = null;
        // Create generic label
        $label = Label::create(strtoupper("PEDAMATENG - BANHUB PEMPROV JATENG"))->setTextColor(new Color(255, 20, 0));

        $result = $writer->write($qrCode, $logo, $label);

        $dataUri = $result->getDataUri();

        // Save it to a file
        $result->saveToFile(FCPATH . 'assets/etiket/' . $etiket . '.png');
        //echo json_encode($data);
        return;
    }

    function tambahPrefix($nomor)
    {
        if (substr($nomor, 0, 1) === '0') {
            return '+62' . substr($nomor, 1);
        }
        return $nomor;
    }


    function warga_blasting()
    {
        $request = \Config\Services::request();
        $session = \Config\Services::session();
        //$session->set('qwarga', array($q,$subtitle));
        $d = $session->get('qwarga');

        $query = $this->db->query($d[0]);
        $result = $query->getResultArray();

        $data = [];

        foreach ($result as $k => $v) {
            if (strlen($v['nowa']) > 0) {
                $data[] = array(
                    'nama' => $v['nama'],
                    'koded' => $v['koded'],
                    'tujuan' => $v['wilayaht'],
                    'nowa' => $this->tambahPrefix($v['nowa'])
                );
            }
        }

        foreach ($data as $k => $v) {
            $trombongan = [];
            $q = $this->db->query("select nama from tbl_warga where koded = :koded:", ['koded' => $v['koded']]);
            $result = $q->getResultArray();
            foreach ($result as $kk => $vv) $trombongan[] = $vv['nama'];
            $data[$k]['rombongan'] .= implode(',', $trombongan);
        }

        // echo '<pre>';
        // print_r($data);
        // echo '</pre>';

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //Specify the properties for this document
        $spreadsheet->getProperties()
            ->setTitle('WA BLASTING - PEDAMATENG')
            ->setSubject('BADAN PENGHUBUNG PROV SEMARANG')
            ->setDescription('Data WA Blasting')
            ->setCreator('pedamateng')
            ->setLastModifiedBy('pedamateng');

        //Adding data to the excel sheet
        $sheet->getColumnDimension('A')->setWidth(5);
        $sheet->getColumnDimension('B')->setWidth(30);
        $sheet->getColumnDimension('C')->setWidth(80);
        $sheet->getColumnDimension('D')->setWidth(50);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getStyle('A:E')->getAlignment()->setWrapText(true); 
        $sheet->getStyle('A:E')->getAlignment()->setVertical("TOP");

        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'NO')
            ->setCellValue('B1', 'NAMA PENDAFTAR')
            ->setCellValue('C1', 'NAMA YNG LOLOS VERIFIKASI')
            ->setCellValue('D1', 'TUJUAN')
            ->setCellValue('E1', 'NO TELP.')
        ;

        $sheet->getStyle('A')->getFont()->setSize(14);
        $sheet->getStyle('B')->getFont()->setSize(14);
        $sheet->getStyle('C')->getFont()->setSize(14);
        $sheet->getStyle('D')->getFont()->setSize(14);


        $i = 2;
        foreach ($data as $k => $v) {
            $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue('A' . $i, $i - 1)
                ->setCellValue('B' . $i, $v['nama'])
                ->setCellValue('C' . $i, $v['rombongan'])
                ->setCellValue('D' . $i, $v['tujuan'])
                ->setCellValue('E' . $i, $v['nowa'] . " ")
            ;
            $i++;

        }


        $writer = IOFactory::createWriter($spreadsheet, "Xlsx"); //Xls is also possible
        // $writer->save(FCPATH . "/assets/wablast/bda.xlsx");
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="spreadsheet.xlsx"');
        header('Cache-Control: max-age=0');
        
        // Write file to the output stream
        $writer->save('php://output');

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

}
