<?php

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

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

$tahun = $_GET['tahun'];
$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);
$tb_siswa = "tb_siswa" . $tahun_tabel;
$payment_list = "payment_list" . $tahun_tabel;



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

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$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
    ]
];


// Buat header tabel nya pada baris ke 3
$sheet->setCellValue('A1', 'NO');
$sheet->setCellValue('B1', 'TANGGAL');
$sheet->setCellValue('C1', 'NIS');
$sheet->setCellValue('D1', 'NAMA');
$sheet->setCellValue('E1', 'JENJANG');
$sheet->setCellValue('F1', 'PARALEL');
$sheet->setCellValue('G1', 'HP');
$sheet->setCellValue('H1', 'EMAIL');
$sheet->setCellValue('I1', 'JUMLAH');
$sheet->setCellValue('J1', 'STATUS');
$sheet->setCellValue('K1', 'ID BILLING');
$sheet->setCellValue('L1', 'CREATED AT');
// Apply style header yang telah kita buat tadi ke masing-masing kolom header
$sheet->getStyle('A1:L1')->applyFromArray($style_col);


$spreadsheet->getActiveSheet()->getStyle('A1:L1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');

// Set height baris ke 1, 2 dan 3
$sheet->getRowDimension('1')->setRowHeight(20);
$sheet->getRowDimension('2')->setRowHeight(20);
$sheet->getRowDimension('3')->setRowHeight(20);

// Buat query untuk menampilkan semua data siswa
$crud = new crudData();
$sql = $crud->showTable($payment_list);

$no = 1; // Untuk penomoran tabel, di awal set dengan 1
$row = 2; // Set baris pertama untuk isi tabel adalah baris ke 4
while ($data = $sql->fetch(PDO::FETCH_OBJ)) {
    $sheet->setCellValue('A' . $row, $no);
    $sheet->setCellValue('B' . $row, $data->tanggal);
    $sheet->setCellValue('C' . $row, $data->nis);
    $sheet->setCellValue('D' . $row, $data->nama);
    $sheet->setCellValue('E' . $row, $data->jenjang);
    $sheet->setCellValue('F' . $row, $data->paralel);
    $sheet->setCellValue('G' . $row, $data->hp);
    $sheet->setCellValue('H' . $row, $data->email);
    $sheet->setCellValue('I' . $row, $data->jumlah);
    $sheet->setCellValue('J' . $row, $data->status);
    $sheet->setCellValue('K' . $row, $data->payment_id);
    $sheet->setCellValue('L' . $row, $data->created_at);


    // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
    $sheet->getStyle('A' . $row . ':L' . $row)->applyFromArray($style_row);


    $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle('B' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet->getStyle('C' . $row . ':F' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet->getStyle('AI' . $row . ':L' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

    $sheet->getRowDimension($row)->setRowHeight(20); // Set height tiap row


    $sheet->getStyle('I' . $row . ':I' . $row)
        ->getNumberFormat()
        ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);


    $no++; // Tambah 1 setiap kali looping
    $row++; // Tambah 1 setiap kali looping

    $sheet->getStyle('A1:L' . $row)->getFont()->setName('Arial');
}

// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(15);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(60);
$sheet->getColumnDimension('E')->setWidth(10);
$sheet->getColumnDimension('F')->setWidth(20);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(40);
$sheet->getColumnDimension('I')->setWidth(15);
$sheet->getColumnDimension('J')->setWidth(15);
$sheet->getColumnDimension('K')->setWidth(35);
$sheet->getColumnDimension('L')->setWidth(35);



// Set orientasi kertas jadi LANDSCAPE
$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);

// Set judul file excel nya
$sheet->setTitle("Data Payment Gateway");

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

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