<?php

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

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

$clssekolah = new sekolah;
$classtr = new transaksi;
$classLaporan = new laporan();
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['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']) && $_GET['penerima_lap'] != 'Semua') {
    $penerima_lap = $_GET['penerima_lap'];
    $penerima_filter = "and penerima='$penerima_lap'";
} else {
    $penerima_lap = "Semua";
    $penerima_filter = "";
};
if (isset($_GET['jenjang_lap'])  && $_GET['jenjang_lap'] != 'Semua') {
    $jenjang_lap = $_GET['jenjang_lap'];
    $jenjang_filter = "and jenjang='$jenjang_lap'";
} else {
    $jenjang_lap = "Semua";
    $jenjang_filter = "";
};
if (isset($_GET['paralel_lap'])  && $_GET['paralel_lap'] != 'Semua') {
    $paralel_lap = $_GET['paralel_lap'];
    $paralel_filter = "and paralel='$paralel_lap'";
} else {
    $paralel_lap = "Semua";
    $paralel_filter = "";
};
if (isset($_GET['metode_lap'])  && $_GET['metode_lap'] != 'Semua') {
    $metode_lap = $_GET['metode_lap'];
    $metode_filter = "and metode='$metode_lap'";
} else {
    $metode_lap = "Semua";
    $metode_filter = "";
};

//$showList = $crud->showTable($tb_transaksi . " WHERE tanggal>='$tgl1' and tanggal<='$tgl2' $penerima_filter $metode_filter $jenjang_filter $paralel_filter");

$val_penerima = $classtr->namaPenerimaByID($penerima_lap);

$format = new format();
// 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
    ]
];


//SHEET - 2
$spreadsheet->createSheet();
$sheet2 = $spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$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);

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


$sheet2->setCellValue('A1', 'RINCIAN TRANSAKSI');
$sheet2->setCellValue('A2', strtoupper($clssekolah->dataSekolah('sekolah')));
$sheet2->mergeCells('A1:K1');
$sheet2->mergeCells('A2:K2');
$sheet2->getStyle('A1:K1')->applyFromArray($style_judul);
$sheet2->getStyle('A2:K2')->applyFromArray($style_judul);


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


$spreadsheet->getActiveSheet(1)->getStyle('A9:K9')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet2->getStyle('A9:K9')->applyFromArray($style_row);


// Bua2 header tabel nya pada baris ke 3
$sheet2->setCellValue('A9', '#');
$sheet2->setCellValue('B9', 'Tanggal');
$sheet2->setCellValue('C9', 'Nama');
$sheet2->setCellValue('D9', 'NIS');
$sheet2->setCellValue('E9', 'Kelas');
$sheet2->setCellValue('F9', 'Kode : Jenis Pembayaran');
$sheet2->setCellValue('G9', 'Periode');
$sheet2->setCellValue('H9', ' Jumlah ');
$sheet2->setCellValue('I9', ' Metode ');
$sheet2->setCellValue('J9', ' Penerima ');
$sheet2->setCellValue('K9', ' Catatan ');

$sheet2->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 9);

$sheet2->getColumnDimension('A')->setWidth(10);
$sheet2->getColumnDimension('B')->setWidth(18);
$sheet2->getColumnDimension('C')->setWidth(20);
$sheet2->getColumnDimension('D')->setWidth(10);
$sheet2->getColumnDimension('E')->setWidth(10);
$sheet2->getColumnDimension('F')->setWidth(30);
$sheet2->getColumnDimension('G')->setWidth(15);
$sheet2->getColumnDimension('H')->setWidth(10);
$sheet2->getColumnDimension('I')->setWidth(7);
$sheet2->getColumnDimension('J')->setWidth(20);
$sheet2->getColumnDimension('K')->setWidth(10);


//$showList = $classLaporan->TransaksiDetail($tb_transaksi, $tgl_1, $tgl_2, $penerima_lap, $metode_lap, $jenjang_lap, $paralel_lap);

$tgl1 = $format->dateToEng($tgl_1);
$tgl2 = $format->dateToEng($tgl_2);

$crud = new crudData();
$showList = $crud->showTable($tb_transaksi . " WHERE tanggal>='$tgl1' and tanggal<='$tgl2' $penerima_filter $metode_filter $jenjang_filter $paralel_filter");


$count_trn = $showList->rowCount();
$no = 0;
$row = 9;
$row_end = $count_trn + $row;
while ($data = $showList->fetch(PDO::FETCH_OBJ)) {


    if ($data->jumlah == "" || $data->jumlah == 0) {
        // $jumlah = number_format(0, 2, ",", ".");
        $jumlah = 0;
    } else {
        //$jumlah = number_format($data->jumlah, 2, ",", ".");
        $jumlah = $data->jumlah;
    };
    if ($data->setor == "Ya") {
        $setor = "Disetor";
    } else {
        $setor = "--";
    };
    $nis = $data->nis;
    $tahap = $data->tahap;
    //nama Bayar
    $classNB = new namaBayar();
    $ceknamaBayar = $classNB->ceknamaBayar($tahun, $data->jenis, $data->jenjang);

    //startAt
    $classTr = new transaksi();
    $startAt = $classTr->startAt($tb_siswa, $nis);
    $cekbulan = $classTr->cekPeriode($startAt, $tahap, $tahun1, $tahun2);

    //nama Bayar
    $classNB = new namaBayar();
    $ceknamaBayar = $classNB->ceknamaBayar($tahun, $data->jenis, $data->jenjang);

    $showJenis = $classNB->shownamaBayar($tahun, $data->jenis, $data->jenjang);
    $datajenis = $showJenis->fetch(PDO::FETCH_OBJ);
    $field_tang = 'tang_' . $datajenis->kode;
    $setbulanan = $datajenis->bulanan; //Cek Apakah Bulanan

    if ($setbulanan == "Tidak") {
        $periode = "Tahap " . $tahap;
    } else {
        $periode = $cekbulan;
    };

    $penerima = $classTr->namaPenerimaByID($data->penerima);

    // $link = new linkpage;
    $no++;
    $row++;
    $sheet2->setCellValue('A' . $row, $no);
    $sheet2->setCellValue('B' . $row, $data->tanggal);
    $sheet2->setCellValue('C' . $row, $format->entityEncode($data->nama));
    $sheet2->setCellValue('D' . $row, $nis);
    $sheet2->setCellValue('E' . $row, $data->jenjang . '-' . $data->paralel);
    $sheet2->setCellValue('F' . $row, $data->jenis . ' : ' . $ceknamaBayar);
    $sheet2->setCellValue('G' . $row, $periode);
    $sheet2->setCellValue('H' . $row, $jumlah);
    $sheet2->setCellValue('I' . $row, $data->metode);
    $sheet2->setCellValue('J' . $row, $penerima);
    $sheet2->setCellValue('K' . $row, $data->catatan);

    $sheet2->getStyle('A' . $row . ':A' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('B' . $row . ':B' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('C' . $row . ':C' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('D' . $row . ':D' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('E' . $row . ':E' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('F' . $row . ':F' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('G' . $row . ':G' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('H' . $row . ':H' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('I' . $row . ':I' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('J' . $row . ':J' . $row_end)->applyFromArray($style_row);
    $sheet2->getStyle('K' . $row . ':K' . $row_end)->applyFromArray($style_row);

    $sheet2->getStyle('A' . $row . ':A' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet2->getStyle('B' . $row . ':B' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet2->getStyle('C' . $row . ':C' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet2->getStyle('D' . $row . ':D' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet2->getStyle('E' . $row . ':E' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet2->getStyle('F' . $row . ':F' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet2->getStyle('G' . $row . ':G' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet2->getStyle('H' . $row . ':H' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet2->getStyle('I' . $row . ':I' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet2->getStyle('J' . $row . ':J' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet2->getStyle('K' . $row . ':K' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);

    //$sheet2->getStyle('A1:K' . $row_end)->getFont()->setName('Arial');
    // $sheet2->getStyle('A10:K' . $row_end)->getFont()->setSize(10);
}



//$sheet2->getRowDimension($row_jns)->setRowHeight(20);

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

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