<?php

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

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

set_time_limit(0); //Unlimited max execution time
$clssekolah = new sekolah;
$classtr = new transaksi;
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;
$tb_detailakun = "detail_akun" . $tahun_tabel;



if (isset($_GET['tgl_1'])) {
    $tgl_1 = $_GET['tgl_1'];
} else {
    $tgl_1 = $tglsekarang;
};
if (isset($_GET['tgl_2'])) {
    $tgl_2 = $_GET['tgl_2'];
} else {
    $tgl_2 = $tglsekarang;
};
if (isset($_GET['penerima_lap'])) {
    $penerima_lap = $_GET['penerima_lap'];
} else {
    $penerima_lap = "Semua";
};
if (isset($_GET['jenjang_lap'])) {
    $jenjang_lap = $_GET['jenjang_lap'];
} else {
    $jenjang_lap = "Semua";
};
if (isset($_GET['paralel_lap'])) {
    $paralel_lap = $_GET['paralel_lap'];
} else {
    $paralel_lap = "Semua";
};
if (isset($_GET['kategori'])) {
    $kategori_lap = $_GET['kategori'];
} else {
    $kategori_lap = "Semua";
};
if (isset($_GET['metode_lap'])) {
    $metode_lap = $_GET['metode_lap'];
} else {
    $metode_lap = "Semua";
};

$val_penerima = $classtr->namaPenerimaByID($penerima_lap);
//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
//$tanggal1 = ubahformatTgl($tgl_1);
//$tanggal2 = ubahformatTgl($tgl_2);;
$format = new format();

$tgl1 = $format->dateToEng($tgl_1);
$tgl2 = $format->dateToEng($tgl_2);
// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet1 = $spreadsheet->setActiveSheetIndex(0);

$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 = [
    '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
    ]
];


//$sheet1->getStyle('A1')->getFont()->setBold(true);
//$sheet1->getStyle('A1')->getFont()->setSize(15);
//$sheet1->getStyle('A1:F1000')->getFont()->setName('Arial');

//for ($i = 'A'; $i !=  $sheet1->getHighestColumn(); $i++) {
//    $sheet1->getColumnDimension($i)->setAutoSize(TRUE);
//}
$sheet1->getColumnDimension('A')->setWidth(3);
$sheet1->getColumnDimension('B')->setWidth(40);
$sheet1->getColumnDimension('C')->setWidth(10);
$sheet1->getColumnDimension('D')->setWidth(10);
$sheet1->getColumnDimension('E')->setWidth(10);
$sheet1->getColumnDimension('F')->setWidth(15);

$sheet1->setCellValue('A1', 'LAPORAN TRANSAKSI');
$sheet1->setCellValue('A2', $clssekolah->dataSekolah('sekolah'));
$sheet1->mergeCells('A1:F1');
$sheet1->mergeCells('A2:F2');

$sheet1->setCellValue('A4', 'Periode');
$sheet1->setCellValue('A5', 'Penerima' . $paralel_lap);
$sheet1->setCellValue('A6', 'Metode');
$sheet1->setCellValue('A7', 'Kelas');
$sheet1->setCellValue('C4', ': ' . $tgl_1 . " s.d " . $tgl_2);
$sheet1->setCellValue('C5', ': ' . $val_penerima);
$sheet1->setCellValue('C6', ': ' . $metode_lap);
$sheet1->setCellValue('C7', ': ' . $jenjang_lap . "-" . $paralel_lap);

// Buat header tabel nya pada baris ke 3
$sheet1->setCellValue('A9', '#');
$sheet1->setCellValue('B9', 'Kode : Jenis Pembayaran');
$sheet1->setCellValue('C9', ' Jenjang ');
$sheet1->setCellValue('D9', ' Bulanan ');
$sheet1->setCellValue('E9', ' Record ');
$sheet1->setCellValue('F9', '   Jumlah   ');

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

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


// Buat query untuk menampilkan semua data siswa
$crud = new crudData();
$classLaporan = new laporan();

$showSiswa = $crud->showTable($tb_siswa);

$no = 1; // Untuk penomoran tabel, di awal set dengan 1
$row = 10;

//$data_sekolah = new crudData;
//$sekolah = $data_sekolah->editData('sekolah', 'no', 1);
//$dataSekolah = $sekolah->fetch(PDO::FETCH_OBJ);
//$jenjang = strtolower($dataSekolah->jenjang);

//$clssekolah = new sekolah;
$jenjang = strtolower($clssekolah->dataSekolah('jenjang'));

$classForm = new form();
$crud = new crudData;
$tingkat = $classForm->optSelect('tb_tingkat', "where {$jenjang}<>'' order by no asc");
while ($datatingkat = $tingkat->fetch(PDO::FETCH_OBJ)) {
    $jenjang_opt = $datatingkat->{$jenjang};
    $namaBayar = new namaBayar();
    //Jenis Bayar Kelas LULUS
    //$jmlJnsLULUS = $crud->countData('tb_nama_bayar', " where tp=$tahun && (kelas='Lulus' || kelas='lulus'");
    $jmlJnsLULUS =  $crud->countData('tb_nama_bayar', "where tp='$tahun' && kelas='Lulus'");

    //Jumlah Jenis Per Jenjang

    $maxJenjang = $namaBayar->maxJenjang();

    if ($jenjang == "paud") {
        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='" . $jenjang_opt . "'");
        $count_jjg = $showJenis->rowCount();

        if ($jenjang_opt == "TPA") {
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas=''");
            $count_Bef = $showJenis->rowCount();
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas=''");
            $count_Jns = $showJenis->rowCount();
        } elseif ($jenjang_opt == "KB") {
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA'");
            $count_Bef = $showJenis->rowCount();
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA' || kelas='KB'");
            $count_Jns = $showJenis->rowCount();
        } elseif ($jenjang_opt == "TK") {
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA' || kelas='KB'");
            $count_Bef = $showJenis->rowCount();
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA' || kelas='KB' || kelas='TK'");
            $count_Jns = $showJenis->rowCount();
        } else {
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA' || kelas='KB' || kelas='TK'");
            $count_Bef = $showJenis->rowCount();
            $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='TPA' || kelas='KB' || kelas='TK' ");
            $count_Jns = $showJenis->rowCount();
        };
    } elseif ($jenjang != 'paud' && $jenjang_opt == "Lulus") {
        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='" . $jenjang_opt . "'");
        $count_jjg = $showJenis->rowCount();

        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas<='" . $maxJenjang . "'");
        $count_Bef = $showJenis->rowCount();

        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas<='" . $maxJenjang . "'");
        $count_Jns = $showJenis->rowCount();
    } else {

        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas='" . $jenjang_opt . "'");
        $count_jjg = $showJenis->rowCount();

        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas<'" . $jenjang_opt . "'");
        $count_Bef = $showJenis->rowCount();

        $showJenis = $namaBayar->CountTransaksi('tb_nama_bayar', 'tp', $tahun, "kelas<='" . $jenjang_opt . "' ");
        $count_Jns = $showJenis->rowCount();
    };
    $showJenis = $namaBayar->CountTransaksiAll('tb_nama_bayar', 'tp', $tahun);
    $count_All = $showJenis->rowCount();
    //
    if ($jenjang == "sd") {
        $row_total = $count_All + 15;
    } else {
        $row_total = $count_All + 18;
    };
    //
    //
    if ($jenjang_opt == "TPA" || $jenjang_opt == "1" || $jenjang_opt == "7" || $jenjang_opt == "10") {
        $plusrow = 0;
    } elseif ($jenjang_opt == "KB" || $jenjang_opt == "2" || $jenjang_opt == "8" || $jenjang_opt == "11") {
        $plusrow = 1;
    } elseif ($jenjang_opt == "TK" || $jenjang_opt == "3" || $jenjang_opt == "9" || $jenjang_opt == "12") {
        $plusrow = 2;
    } elseif ($jenjang_opt == "4") {
        $plusrow = 3;
    } elseif ($jenjang_opt == "5") {
        $plusrow = 4;
    } elseif ($jenjang_opt == "6") {
        $plusrow = 5;
    } elseif ($jenjang_opt == "Lulus" && $jenjang == "sd") {
        $plusrow = 5;
    } elseif ($jenjang_opt == "Lulus" && $jenjang != "sd") {
        $plusrow = 2;
    } else {
        $plusrow = 0;
    };
    //

    if ($jenjang_opt == "Lulus" ||  $jenjang_opt == "lulus") {

        $row_jjg = $row +  $count_Bef + $plusrow - ($jmlJnsLULUS - 1);
        $row_jns = $row + $count_Bef + $plusrow - ($jmlJnsLULUS - 1) + 1;
        $row_subt = $row + $count_Jns + $plusrow + 2;
    } else {
        $row_jjg = $row +  $count_Bef + $plusrow;
        $row_jns = $row + $count_Bef + $plusrow + 1;
        $row_subt = $row + $count_Jns + $plusrow + 1;
    }


    $sheet1->setCellValue('A' . $row_jjg, $no);
    $sheet1->setCellValue('B' . $row_jjg, 'Jenjang Kelas ' . $jenjang_opt);

    $classLaporan = new laporan();
    $jenisBayar = $classLaporan->ShowjenisBayar($tahun, $jenjang_opt);
    while ($datajenis = $jenisBayar->fetch(PDO::FETCH_OBJ)) {
        $jenis = $datajenis->kode;
        $sheet1->setCellValue('A' . $row_jns, '');
        $sheet1->setCellValue('B' . $row_jns, $datajenis->kode . ' : ' . $datajenis->nama);
        $sheet1->setCellValue('C' . $row_jns, $datajenis->kelas);
        $sheet1->setCellValue('D' . $row_jns, $datajenis->bulanan);
        $sheet1->setCellValue('E' . $row_jns, $classLaporan->CountRecordJenis($tb_transaksi, $jenis, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_opt, $paralel_lap, $kategori_lap));
        $sheet1->setCellValue('F' . $row_jns, $classLaporan->TransaksisubTotalJenis($tb_transaksi, $jenis, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_opt, $kategori_lap));
        $row_jns++;
        // $classLaporan->CountRecordJenis($tb_transaksi, $jenis, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_opt, $paralel_lap, $kategori_lap)
        $sheet1->getStyle('A9:F' . $row_jns + 1)->applyFromArray($style_row);
        $sheet1->getStyle('A9:F' . $row_jns + 1)->getFont()->setSize(10);
    }

    //JUmlah Per Jenjang
    $sheet1->setCellValue('B' . $row_subt, 'Jumlah');
    $sheet1->setCellValue('E' . $row_subt, $classLaporan->CountRecordJenjang($tb_transaksi, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_opt, $kategori_lap));
    $sheet1->setCellValue('F' . $row_subt, $classLaporan->TransaksisubTotalJenjang($tb_transaksi, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_opt, $kategori_lap));
    //$row_subt++;

    // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
    $sheet1->getStyle('A' . $row . ':A' . $row_jns + 1)->applyFromArray($style_row);
    $sheet1->getStyle('B' . $row . ':B' . $row_jns + 1)->applyFromArray($style_row);
    $sheet1->getStyle('C' . $row . ':C' . $row_jns + 1)->applyFromArray($style_row);
    $sheet1->getStyle('D' . $row . ':D' . $row_jns + 1)->applyFromArray($style_row);
    $sheet1->getStyle('E' . $row . ':E' . $row_jns + 1)->applyFromArray($style_row);
    $sheet1->getStyle('F' . $row . ':F' . $row_jns + 1)->applyFromArray($style_row);

    $sheet1->getStyle('A' . $row . ':A' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('B' . $row . ':B' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet1->getStyle('C' . $row . ':C' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('D' . $row . ':D' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('E' . $row . ':E' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('F' . $row . ':F' . $row_jns + 1)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

    $sheet1->getRowDimension($row_jns)->setRowHeight(20);
    //
    $no++; // Tambah 1 setiap kali looping
    $row++; // Tambah 1 setiap kali looping
    $row_jjg++;
    $sheet1->getStyle('A' . $row . ':F' . $row_jns)->applyFromArray($style_row);
}
//
////JUmlah Total
$sheet1->setCellValue('B' . $row_total, 'TOTAL');
$sheet1->setCellValue('E' . $row_total, $classLaporan->CountRecordAll($tb_transaksi, $tgl1, $tgl2, $penerima_lap, $metode_lap, $jenjang_lap, $paralel_lap));
$sheet1->setCellValue('F' . $row_total, $classLaporan->TransaksiTotal($tb_transaksi, $tgl1, $tgl2, $penerima_lap, $metode_lap, $kategori_lap));
//$classLaporan->CountRecordAll($tb_transaksi, $tgl1, $tgl2, $penerima_lap, $metode_lap, $kategori_lap);
$sheet1->getStyle('A' . $row_total . ':F' . $row_total)->getFont()->setBold(true);
$sheet1->getStyle('A' . $row_total . ':F' . $row_total)->applyFromArray($style_row);
//
//// Set orientasi kertas jadi LANDSCAPE
$sheet1->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);

//Set judul file excel nya
$spreadsheet->getActiveSheet()->setTitle("Rekapitulasi");



//SHEET - 2
$spreadsheet->createSheet();
$sheet2 = $spreadsheet->setActiveSheetIndex(1);
$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);


// Proses file excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Rekap Laporan Transaksi.xlsx"'); // Set nama file excel nya
header('Cache-Control: max-age=0');

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