<?php

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

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


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;

// Cara penggunaan function ubahTgl
$clssekolah = new sekolah;

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


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

//for ($i = 'A'; $i !=  $sheet1->getHighestColumn(); $i++) {
//    $sheet1->getColumnDimension($i)->setAutoSize(TRUE);
//}
$sheet1->getColumnDimension('A')->setWidth(5);
$sheet1->getColumnDimension('B')->setWidth(35);
$sheet1->getColumnDimension('C')->setWidth(20);
$sheet1->getColumnDimension('D')->setWidth(10);
$sheet1->getColumnDimension('E')->setWidth(10);
$sheet1->getColumnDimension('F')->setWidth(15);
$sheet1->getColumnDimension('G')->setWidth(15);
$sheet1->getColumnDimension('H')->setWidth(15);
$sheet1->getColumnDimension('I')->setWidth(10);

$sheet1->setCellValue('A1', 'DATA WALI KELAS & PEMBAYARAN SISWA [TP. ' . $tahun . ']');
$sheet1->setCellValue('A2', $clssekolah->dataSekolah('sekolah'));
$sheet1->mergeCells('A1:I1');
$sheet1->mergeCells('A2:I2');


$sheet1->setCellValue('I4', 'Tgl Cetak : ' . $tglsekarang);
$sheet1->getStyle('I4')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('I4')->getFont()->setSize(8);
// Buat header tabel nya pada baris ke 3
$sheet1->setCellValue('A5', 'No');
$sheet1->setCellValue('B5', 'Nama Wali Kelas');
$sheet1->setCellValue('C5', 'NIP');
$sheet1->setCellValue('D5', 'Kelas');
$sheet1->setCellValue('E5', 'Jml  Siswa');
$sheet1->setCellValue('F5', 'Tanggungan');
$sheet1->setCellValue('G5', 'Terbayar');
$sheet1->setCellValue('H5', 'Kurang');
$sheet1->setCellValue('I5', '%');

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

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


// Buat query untuk menampilkan semua data siswa

$clsCrud = new crudData();
$show = $clsCrud->showTable("tb_walikelas where tp='$tahun' order by jenjang asc, paralel asc");
$count = $show->rowCount();
$no = 0; // Untuk penomoran tabel, di awal set dengan 1
$row = 5;
$row_end = $row + $count;
$row_jumlah = $row + $count + 1;
while ($data = $show->fetch(PDO::FETCH_OBJ)) {

    $nip = $data->nip;
    $clsCrud = new crudData;
    $clsformat = new format;
    $link = new linkpage;
    $classTrans = new transaksi();
    $clslistTanggungan = new listTanggungan;

    $tanggungan = $clslistTanggungan->tanggunganSiswaIf($tb_siswa, "  jenjang='$data->jenjang' and paralel='$data->paralel'");
    $terbayar = $classTrans->sumTerbayarKelas($tb_transaksi, $data->jenjang, $data->paralel);
    $kurang = @($tanggungan) - @($terbayar);
    $persen = $clsformat->persen($terbayar, $tanggungan);

    $no++;
    $row++;
    $sheet1->setCellValue('A' . $row, $no);
    $sheet1->setCellValue('B' . $row, $data->nama);
    $sheet1->setCellValue('C' . $row, " " . $data->nip);
    $sheet1->setCellValue('D' . $row, $data->jenjang . "-" . $data->paralel);
    $sheet1->setCellValue('E' . $row, $clsCrud->countData($tb_siswa, " where jenjang='$data->jenjang' and paralel='$data->paralel'"));
    $sheet1->setCellValue('F' . $row, $tanggungan);
    $sheet1->setCellValue('G' . $row, $terbayar);
    $sheet1->setCellValue('H' . $row, $kurang);
    $sheet1->setCellValue('I' . $row, $persen);

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

    $sheet1->getStyle('A1:I' . $row_end)->getFont()->setName('Arial');
    $sheet1->getStyle('A6:I' . $row_end)->getFont()->setSize(10);

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


$sheet1->setCellValue('A' . $row_jumlah, 'JUMLAH');
$sheet1->mergeCells('A' . $row_jumlah . ':E' . $row_jumlah);

$sheet1->setCellValue('F' . $row_jumlah, "=SUM(F6:F" . $row_end . ")");
$sheet1->setCellValue('G' . $row_jumlah, "=SUM(G6:G" . $row_end . ")");
$sheet1->setCellValue('H' . $row_jumlah, "=SUM(H6:H" . $row_end . ")");

$sheet1->getStyle('A' . $row . ':A' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('B' . $row . ':B' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('C' . $row . ':C' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('D' . $row . ':D' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('E' . $row . ':E' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('F' . $row . ':F' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('G' . $row . ':G' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('H' . $row . ':H' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('I' . $row . ':I' . $row_jumlah)->applyFromArray($style_row);

$sheet1->getStyle('E' . $row . ':E' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('F' . $row . ':F' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('G' . $row . ':G' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('H' . $row . ':H' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('I' . $row . ':I' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

$sheet1->getStyle('A' . $row_jumlah . ':I' . $row_jumlah)->getFont()->setName('Arial');
$sheet1->getStyle('A' . $row_jumlah . ':I' . $row_jumlah)->getFont()->setSize(10);

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

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