<?php

spl_autoload_register(function ($mod) {
    require('../../../../../asset/mod/' . $mod . '.php');
});

// Load file autoload.php
require '../../../../../asset/bootstrap/asset/phpspreadsheet/autoload.php';

$link = new linkpage;
date_default_timezone_set('Asia/Jakarta');
$tanggal = mktime(date("m"), date("d"), date("Y"));
$tglsekarang = date("d-m-Y", $tanggal);
$tahunnow = date("Y", $tanggal);

if (isset($_GET['tahun'])) {
    $tahun = $_GET['tahun'];
} else {
    $tahun = $tahunnow;
};
$tahun1 = substr($tahun, 0, 4);
$tahun2 = substr($tahun, 5, 4);

$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);

$tb_siswa = "tb_siswa" . $tahun_tabel;
$tb_transaksi = "db_transaksi" . $tahun_tabel;


if (isset($_GET['jenjang'])) {
    $jenjang_lap = $_GET['jenjang'];
} else {
    $jenjang_lap = "Semua";
};
if (isset($_GET['paralel'])) {
    $paralel_lap = $_GET['paralel'];
} else {
    $paralel_lap = "Semua";
};
if (isset($_GET['kategori'])) {
    $kategori_lap = $_GET['kategori'];
} else {
    $kategori_lap = "Semua";
};
if (isset($_GET['jenis'])) {
    $jenis_lap = $_GET['jenis'];
} else {
    $jenis_lap = "Semua";
};
if (isset($_GET['status'])) {
    $status_lap = $_GET['status'];
} else {
    $status_lap = "Semua";
};

$dt_jenis = new namaBayar();
$showJenis = $dt_jenis->shownamaBayar($tahun, $jenis_lap, $jenjang_lap);
$count_nb = $showJenis->rowCount();
$datajenis = $showJenis->fetch(PDO::FETCH_OBJ);
if ($count_nb > 0) {
    $namaBayar = $datajenis->nama;
} else {
    $namaBayar = "Semua";
};


//Query Tanggungan
$classTanggungan = new listTanggungan();
//$val_sumtang = $classTanggungan->sumTanggunganByFilter($tb_siswa, $jenjang_lap, $paralel_lap, $jenis_lap);
$val_sumtang = $classTanggungan->sumTanggunganByFilterRekap($tb_siswa, $jenjang_lap, $paralel_lap, $status_lap, $jenis_lap);

if ($val_sumtang == "" || $val_sumtang  == 0) {

    $sumtanggungan = 0;
    $v_sumtanggungan = 0;
} else {

    $sumtanggungan = $val_sumtang;
    $v_sumtanggungan = $val_sumtang;
};
//Terbayar & Kurang
$classTrans = new transaksi();
//$Terbayar = $classTrans->sumTerbayar($tb_transaksi, $jenjang_lap, $paralel_lap, $jenis_lap);
$Terbayar = $classTrans->sumTerbayarRekap($tb_transaksi, $tb_siswa, $jenjang_lap, $paralel_lap, $status_lap, $jenis_lap);
$sumterbayar = @($Terbayar);
$kurang = $v_sumtanggungan - $Terbayar;
$sumkurang = @(($kurang));

//Indo to Egg
function ubahformatTgl($tanggal)
{
    $pisah = explode('-', $tanggal);
    $urutan = array($pisah[2], $pisah[1], $pisah[0]);
    $satukan = implode('-', $urutan);
    return $satukan;
}
// Cara penggunaan function ubahTgl
$clssekolah = new sekolah;

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();


$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
$spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setRight(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);
// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$style_judul = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'font-family' => ['Arial'], // Set font nya jadi bold
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],

];
$style_col = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'font-family' => ['Arial'], // Set font nya jadi bold
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];

// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = [
    'font-family' => ['Arial'], // Set font nya jadi bold
    'alignment' => [
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];


// Buat query untuk menampilkan semua data siswa
$classLaporan = new laporan();
$format = new format();
$showList = $classLaporan->showRekap($tb_siswa, $jenjang_lap, $paralel_lap, $kategori_lap, $status_lap, 'jenjang asc, paralel asc, nama asc');
$no = 0;

while ($data = $showList->fetch(PDO::FETCH_OBJ)) {

    $nis = $data->nis;
    $id = $data->id;
    $nama = $format->entityEncode($data->nama);
    $jenjang = $data->jenjang;
    $paralel = $data->paralel;
    $kelas = $jenjang . '-' . $paralel;

    $sheetindex = $no;
    $sheetname = "sheet" . $no + 1;

    $sheetname = $spreadsheet->setActiveSheetIndex($sheetindex);

    $sheetname->getStyle('A1')->getFont()->setBold(true);
    $sheetname->getStyle('A1')->getFont()->setSize(15);


    $sheetname->getColumnDimension('A')->setWidth(5);
    $sheetname->getColumnDimension('B')->setWidth(25);
    $sheetname->getColumnDimension('C')->setWidth(10);
    $sheetname->getColumnDimension('D')->setWidth(10);
    $sheetname->getColumnDimension('E')->setWidth(30);
    $sheetname->getColumnDimension('F')->setWidth(12);
    $sheetname->getColumnDimension('G')->setWidth(12);
    $sheetname->getColumnDimension('H')->setWidth(12);
    $sheetname->getColumnDimension('I')->setWidth(12);
    $sheetname->getColumnDimension('J')->setWidth(12);
    $sheetname->getColumnDimension('K')->setWidth(12);
    $sheetname->getColumnDimension('L')->setWidth(12);
    $sheetname->getColumnDimension('M')->setWidth(12);
    $sheetname->getColumnDimension('N')->setWidth(12);
    $sheetname->getColumnDimension('O')->setWidth(12);
    $sheetname->getColumnDimension('P')->setWidth(12);
    $sheetname->getColumnDimension('Q')->setWidth(12);
    $sheetname->getColumnDimension('R')->setWidth(12);
    $sheetname->getColumnDimension('S')->setWidth(12);
    $sheetname->getColumnDimension('T')->setWidth(12);


    $sheetname->setCellValue('A1', 'LAPORAN REKAPITULASI [MODEL-4]');
    $sheetname->setCellValue('A2', $clssekolah->datasekolah('sekolah'));
    $sheetname->mergeCells('A1:T1');
    $sheetname->mergeCells('A2:T2');


    // Buat header tabel nya pada baris ke 3
    $sheetname->setCellValue('A4', 'No');
    $sheetname->setCellValue('B4', 'Nama');
    $sheetname->setCellValue('C4', 'NIS');
    $sheetname->setCellValue('D4', 'Kelas');
    $sheetname->setCellValue('E4', 'Jenis Pembayaran');
    $sheetname->setCellValue('F4', 'Tanggungan');
    $sheetname->setCellValue('G4', 'Tahap 1');
    $sheetname->setCellValue('H4', 'Tahap 2');
    $sheetname->setCellValue('I4', 'Tahap 3');
    $sheetname->setCellValue('J4', 'Tahap 4');
    $sheetname->setCellValue('K4', 'Tahap 5');
    $sheetname->setCellValue('L4', 'Tahap 6');
    $sheetname->setCellValue('M4', 'Tahap 7');
    $sheetname->setCellValue('N4', 'Tahap 8');
    $sheetname->setCellValue('O4', 'Tahap 9');
    $sheetname->setCellValue('P4', 'Tahap 10');
    $sheetname->setCellValue('Q4', 'Tahap 11');
    $sheetname->setCellValue('R4', 'Tahap 12');
    $sheetname->setCellValue('S4', 'Jumlah');
    $sheetname->setCellValue('T4', 'Kurang');


    // Apply style header yang telah kita buat tadi ke masing-masing kolom header
    $sheetname->getStyle('A1:T1')->applyFromArray($style_judul);
    $sheetname->getStyle('A2:T2')->applyFromArray($style_judul);

    $spreadsheet->getActiveSheet(1)->getStyle('A4:T4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
    $sheetname->getStyle('A4:T4')->applyFromArray($style_row);
    // Set height baris ke 1, 2 dan 3
    $sheetname->getRowDimension('1')->setRowHeight(20);

    //Creat Sheet
    $spreadsheet->createSheet();
    $sheetname = $spreadsheet->setActiveSheetIndex($sheetindex);


    $row = 4;

    $dt_jenis = new namaBayar();
    $showJenis = $dt_jenis->NamaTransaksi('tb_nama_bayar', 'tp', $tahun, $jenjang, 'kode');
    $count = $showJenis->rowCount();

    $row_next = $row + $count;
    $row_end = $row_next;
    $row_jumlah =  $row_end + 1;

    //Jumlah
    $dt_jenis = new listTanggungan();
    $jumlah = $dt_jenis->tanggunganSiswa($tb_siswa, 'id', $id);
    //$total_tang = number_format($jumlah, 0, ".", ".");
    $total_tang = $jumlah;

    //Terbayar & Kurang Total
    $infoTerbayartotal = $classTrans->infoterbayartotal($tb_transaksi, $nis);
    //$terbayarTotal = @(number_format($infoTerbayartotal, 0, ".", "."));
    //$kurangTotal = @(number_format(($jumlah - $infoTerbayartotal), 0, ".", "."));

    $terbayarTotal = $infoTerbayartotal;
    $kurangTotal = $jumlah - $infoTerbayartotal;



    $sheetname->setCellValue('A' . $row, $no);
    $sheetname->setCellValue('B' . $row, $nama);
    $sheetname->setCellValue('C' . $row, $nis);
    $sheetname->setCellValue('D' . $row, $kelas);

    $row++;
    $no++;

    //RINCIAN 
    $dt_jenis = new namaBayar();
    $showJenis = $dt_jenis->NamaTransaksi('tb_nama_bayar', 'tp', $tahun, $jenjang, 'kode');
    $count_nb = $showJenis->rowCount();
    $no_jenis = 0;

    $row_tang = 5;
    while ($datajenis = $showJenis->fetch(PDO::FETCH_OBJ)) {
        $field_tang = 'tang_' . $datajenis->kode;
        $namaBayar =  $datajenis->nama;
        $kode =  $datajenis->kode;

        if ($data->{$field_tang} == "" || !is_numeric($data->{$field_tang})) {
            $val_tang = 0;
        } else {
            $val_tang = $data->{$field_tang};
        }; //Cek jika jumlah tanggungan kosong / bukan angka

        $setbulanan = $datajenis->bulanan; //Cek Apakah Bulanan

        //$rupiah_tang = @(number_format($val_tang, 0, ".", "."));
        $rupiah_tang = $val_tang;
        $jenis = $datajenis->kode;

        //Terbayar & Kurang
        $classTrans = new transaksi();
        $format = new format();
        $infoTerbayar = $classTrans->infoterbayar($tb_transaksi, $nis, $jenis);
        //$terbayar = @(number_format($infoTerbayar, 0, ".", "."));
        //$kurang = @(number_format(($val_tang - $infoTerbayar), 0, ".", "."));

        $terbayar = $infoTerbayar;
        $kurang = $val_tang - $infoTerbayar;


        if ($infoTerbayar == "" || $infoTerbayar  == 0) {
            $infoTerbayar = 0;
        } else {
            $infoTerbayar = $infoTerbayar;
        };
        if ($setbulanan == "Ya" || $setbulanan == "ya") {
            //$bulanan = @(number_format(($val_tang / (12 - ($startAt - 1))), 0, ".", "."));
            $bulanan = ($val_tang / (12 - ($startAt - 1)));
            $disabledQty = "";
        } else {
            $bulanan = "--";
            $disabledQty = "disabled";
        };

        if ($count_nb > 0) {
            $namaBayar = $datajenis->nama;
            $bulanan = $datajenis->bulanan;
            $filterJenis = "and jenis='$datajenis->kode'";
        } else {
            $namaBayar = "Semua";
            $bulanan = "Tidak";
            $filterJenis = "";
        };



        $sheetname->setCellValue('E' . $row_tang, $kode . " : " . $namaBayar);
        $sheetname->setCellValue('F' . $row_tang, $val_tang);
        $sheetname->setCellValue('G' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '1', $nis,  $filterJenis));
        $sheetname->setCellValue('H' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '2', $nis,  $filterJenis));
        $sheetname->setCellValue('I' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '3', $nis,  $filterJenis));
        $sheetname->setCellValue('J' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '4', $nis,  $filterJenis));
        $sheetname->setCellValue('K' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '5', $nis,  $filterJenis));
        $sheetname->setCellValue('L' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '6', $nis,  $filterJenis));
        $sheetname->setCellValue('M' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '7', $nis,  $filterJenis));
        $sheetname->setCellValue('N' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '8', $nis,  $filterJenis));
        $sheetname->setCellValue('O' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '9', $nis,  $filterJenis));
        $sheetname->setCellValue('P' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '10', $nis,  $filterJenis));
        $sheetname->setCellValue('Q' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '11', $nis,  $filterJenis));
        $sheetname->setCellValue('R' . $row_tang,  $classLaporan->sumTahapXlsx($tb_transaksi, '>=12', $nis,  $filterJenis));
        $sheetname->setCellValue('S' . $row_tang, $infoTerbayar);
        $sheetname->setCellValue('T' . $row_tang, ($val_tang - $infoTerbayar));

        $sheetname->getStyle('A' . $row . ':A' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheetname->getStyle('B' . $row . ':B' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheetname->getStyle('C' . $row . ':C' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheetname->getStyle('D' . $row . ':D' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        $sheetname->getStyle('E' . $row . ':E' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
        $sheetname->getStyle('F' . $row . ':F' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('G' . $row . ':G' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('H' . $row . ':H' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('I' . $row . ':I' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('J' . $row . ':J' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('K' . $row . ':K' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('L' . $row . ':L' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('M' . $row . ':M' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('N' . $row . ':N' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('O' . $row . ':O' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('P' . $row . ':P' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('Q' . $row . ':Q' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('R' . $row . ':R' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('S' . $row . ':S' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        $sheetname->getStyle('T' . $row . ':T' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

        $sheetname->getStyle('A1:T' . $row_end)->getFont()->setName('Arial');
        $sheetname->getStyle('A4:T' . $row_end)->getFont()->setSize(10);
        $sheetname->getStyle('A' . $row . ':A' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('B' . $row . ':B' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('C' . $row . ':C' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('D' . $row . ':D' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('E' . $row . ':E' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('F' . $row . ':F' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('G' . $row . ':G' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('H' . $row . ':H' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('I' . $row . ':I' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('J' . $row . ':J' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('K' . $row . ':K' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('L' . $row . ':L' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('M' . $row . ':M' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('N' . $row . ':N' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('O' . $row . ':O' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('P' . $row . ':P' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('Q' . $row . ':Q' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('R' . $row . ':R' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('S' . $row . ':S' . $row_end)->applyFromArray($style_row);
        $sheetname->getStyle('T' . $row . ':T' . $row_end)->applyFromArray($style_row);
        $row_tang++;
    };


    //Jumlah
    $dt_jenis = new listTanggungan();
    $jumlah_tang = $dt_jenis->tanggunganSiswa($tb_siswa, 'id', $id);
    //$total_tang = number_format($jumlah_tang, 0, ".", ".");
    $total_tang = $jumlah_tang;
    $infoTerbayartotal = $classTrans->infoterbayartotal($tb_transaksi, $nis);
    //$terbayarTotal = @(number_format($infoTerbayartotal, 0, ".", "."));
    $terbayarTotal = $infoTerbayartotal;
    $kurangTotal = @(($jumlah - $infoTerbayartotal));


    $sheetname->setCellValue('A' . $row_jumlah, 'JUMLAH');
    $sheetname->mergeCells('A' . $row_jumlah . ':E' . $row_jumlah);
    $sheetname->setCellValue('F' . $row_jumlah, $jumlah_tang);
    $sheetname->setCellValue('G' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '1', $nis));
    $sheetname->setCellValue('H' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '2', $nis));
    $sheetname->setCellValue('I' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '3', $nis));
    $sheetname->setCellValue('J' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '4', $nis));
    $sheetname->setCellValue('K' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '5', $nis));
    $sheetname->setCellValue('L' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '6', $nis));
    $sheetname->setCellValue('M' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '7', $nis));
    $sheetname->setCellValue('N' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '8', $nis));
    $sheetname->setCellValue('O' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '9', $nis));
    $sheetname->setCellValue('P' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '10', $nis));
    $sheetname->setCellValue('Q' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '11', $nis));
    $sheetname->setCellValue('R' . $row_jumlah, $classLaporan->totalTahapModel4Xlsx($tb_transaksi, '>=12', $nis));
    $sheetname->setCellValue('S' . $row_jumlah, $infoTerbayartotal);
    $sheetname->setCellValue('T' . $row_jumlah, $kurangTotal);

    $sheetname->getStyle('A' . $row . ':A' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('B' . $row . ':B' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('C' . $row . ':C' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('D' . $row . ':D' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('E' . $row . ':E' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('F' . $row . ':F' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('G' . $row . ':G' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('H' . $row . ':H' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('I' . $row . ':I' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('J' . $row . ':J' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('K' . $row . ':K' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('L' . $row . ':L' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('M' . $row . ':M' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('N' . $row . ':N' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('O' . $row . ':O' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('P' . $row . ':P' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('Q' . $row . ':Q' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('R' . $row . ':R' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('S' . $row . ':S' . $row_jumlah)->applyFromArray($style_row);
    $sheetname->getStyle('T' . $row . ':T' . $row_jumlah)->applyFromArray($style_row);

    $sheetname->getStyle('E' . $row . ':E' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheetname->getStyle('F' . $row . ':F' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('G' . $row . ':G' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('H' . $row . ':H' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('I' . $row . ':I' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('J' . $row . ':J' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('K' . $row . ':K' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('L' . $row . ':L' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('M' . $row . ':M' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('N' . $row . ':N' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('O' . $row . ':O' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('P' . $row . ':P' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('Q' . $row . ':Q' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('R' . $row . ':R' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('S' . $row . ':S' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('T' . $row . ':T' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheetname->getStyle('A' . $row_jumlah . ':T' . $row_jumlah)->getFont()->setName('Arial');
    $sheetname->getStyle('A' . $row_jumlah . ':T' . $row_jumlah)->getFont()->setSize(10);
    $sheetname->getStyle('T' . $row_jumlah)->getFont()->setName('Arial');

    $spreadsheet->getActiveSheet()->setTitle($nis);
}
// Proses file excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Laporan Rekapitulasi.xlsx"'); // Set nama file excel nya
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
