<?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';
set_time_limit(0); //Unlimited max execution time
//$tahun = $_GET['tahun'];
//$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);
//$tb_transaksi = "db_transaksi" . $tahun_tabel;

$tb_transaksi =  $_GET['tahun'];

// 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],
    'font-family' => ['Arial'],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]
    ]
];

// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = [
    'font' => ['bold' => false],
    'font-family' => ['Arial'],
    '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', 'KELAMIN');
$sheet->setCellValue('F1', 'JENJANG');
$sheet->setCellValue('G1', 'PARALEL');
$sheet->setCellValue('H1', 'KATEGORI');
$sheet->setCellValue('I1', 'JENIS');
$sheet->setCellValue('J1', 'TAHAP');
$sheet->setCellValue('K1', 'JUMLAH');
$sheet->setCellValue('L1', 'RANDOM');
$sheet->setCellValue('M1', 'PENERIMA');
$sheet->setCellValue('N1', 'SETOR');
$sheet->setCellValue('O1', 'ID_SETOR');
$sheet->setCellValue('P1', 'METODE');
$sheet->setCellValue('Q1', 'CATATAN');
$sheet->setCellValue('R1', 'TIME');
$sheet->setCellValue('S1', 'IP');
$sheet->setCellValue('T1', 'BROWSER');
$sheet->setCellValue('U1', 'ID_TR');
$sheet->setCellValue('V1', 'ID');
// Apply style header yang telah kita buat tadi ke masing-masing kolom header
$sheet->getStyle('A1:V1')->applyFromArray($style_col);
$spreadsheet->getActiveSheet()->getStyle('A1:V1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');


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

$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, $format->entityEncode($data->nama));
    $sheet->setCellValue('E' . $row, $data->kelamin);
    $sheet->setCellValue('F' . $row, $data->jenjang);
    $sheet->setCellValue('G' . $row, $data->paralel);
    $sheet->setCellValue('H' . $row, $data->kategori);
    $sheet->setCellValue('I' . $row, $data->jenis);
    $sheet->setCellValue('J' . $row, $data->tahap);
    $sheet->setCellValue('K' . $row, $data->jumlah);
    $sheet->setCellValue('L' . $row, $data->random);
    $sheet->setCellValue('M' . $row, $data->penerima);
    $sheet->setCellValue('N' . $row, $data->setor);
    $sheet->setCellValue('O' . $row, $data->id_setor);
    $sheet->setCellValue('P' . $row, $data->metode);
    $sheet->setCellValue('Q' . $row, $data->catatan);
    $sheet->setCellValue('R' . $row, $data->time);
    $sheet->setCellValue('S' . $row, $data->ip);
    $sheet->setCellValue('T' . $row, $data->browser);
    $sheet->setCellValue('U' . $row, $data->id_tr);
    $sheet->setCellValue('V' . $row, $data->id);



    // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
    $sheet->getStyle('A' . $row . ':V' . $row)->applyFromArray($style_row);
    // $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

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

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


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

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

// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5);
$sheet->getColumnDimension('B')->setWidth(20);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(60);
$sheet->getColumnDimension('E')->setWidth(10);
$sheet->getColumnDimension('F')->setWidth(10);
$sheet->getColumnDimension('G')->setWidth(20);
$sheet->getColumnDimension('H')->setWidth(20);
$sheet->getColumnDimension('I')->setWidth(10);
$sheet->getColumnDimension('J')->setWidth(10);
$sheet->getColumnDimension('K')->setWidth(15);
$sheet->getColumnDimension('L')->setWidth(35);
$sheet->getColumnDimension('M')->setWidth(50);
$sheet->getColumnDimension('N')->setWidth(10);
$sheet->getColumnDimension('O')->setWidth(15);
$sheet->getColumnDimension('P')->setWidth(15);
$sheet->getColumnDimension('Q')->setWidth(25);
$sheet->getColumnDimension('R')->setWidth(25);
$sheet->getColumnDimension('S')->setWidth(30);
$sheet->getColumnDimension('T')->setWidth(70);
$sheet->getColumnDimension('U')->setWidth(30);
$sheet->getColumnDimension('V')->setWidth(30);



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

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

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

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