<?php

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

// Load file autoload.php
require '../../../../asset/bootstrap/asset/phpspreadsheet/autoload.php';
require('../../../../asset/mod/autoLoad.php');
//$clssekolah = new sekolah;

$tahun = $_GET['tahun'];

$tahun1 = substr($tahun, 0, 4);
$tahun2 = substr($tahun, 5, 4);

$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);

$tb_komponen = "simakom_komponen" . $tahun_tabel;
$tb_kegiatan = "simakom_kegiatan" . $tahun_tabel;
$tb_akun = "simakom_akun" . $tahun_tabel;
$tb_detail = "simakom_detail" . $tahun_tabel;
$tb_revisi = "simakom_revisi" . $tahun_tabel;
$tb_transaksi = "simakom_transaksi" . $tahun_tabel;
$tb_jurnal = "simakom_jurnal" . $tahun_tabel;

// 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(10);
$sheet1->getColumnDimension('C')->setWidth(10);
$sheet1->getColumnDimension('D')->setWidth(50);
$sheet1->getColumnDimension('E')->setWidth(15);


$sheet1->setCellValue('A1', 'DAFTAR KEGIATAN');
//$sheet1->setCellValue('A2', $clssekolah->dataSekolah('sekolah'));
$sheet1->mergeCells('A1:E1');
$sheet1->mergeCells('A2:E2');

// Buat header tabel nya pada baris ke 3
$sheet1->setCellValue('A4', 'No');
$sheet1->setCellValue('B4', 'Kategori');
$sheet1->setCellValue('C4', 'Kegiatan');
$sheet1->setCellValue('D4', 'Uraian');
$sheet1->setCellValue('E4', 'Kode');

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

$spreadsheet->getActiveSheet(1)->getStyle('A4:E4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet1->getStyle('A4:E4')->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();
$sql_keg = $crud->showTable($tb_kegiatan);
$no = 1;
$row = 5;
while ($datakeg = $sql_keg->fetch(PDO::FETCH_OBJ)) {


    $sheet1->setCellValue('A' . $row, $no);
    $sheet1->setCellValue('B' . $row, $datakeg->kategori);
    $sheet1->setCellValue('C' . $row, $datakeg->kegiatan);
    $sheet1->setCellValue('D' . $row, $datakeg->uraian);
    $sheet1->setCellValue('E' . $row,  $datakeg->kode_id);
    $row++;
    $no++;

    $sheet1->getStyle('A4:E' . $row)->applyFromArray($style_row);
    $sheet1->getStyle('A4:E' . $row + 1)->getFont()->setSize(10);
}
// Set orientasi kertas jadi LANDSCAPE
$sheet1->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);

////$spreadsheet->getActiveSheet(1)->setTitle("Rekapitulasi");
$spreadsheet->getActiveSheet()->setTitle("KEGIATAN");


//SHEET - 2 // AKUN
$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', 'AKUN');

$spreadsheet->getActiveSheet(1)->getStyle('A4:G4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet2->getStyle('A4:G4')->applyFromArray($style_row);
$sheet2->setCellValue('A4', 'No');
$sheet2->setCellValue('B4', 'Kategori');
$sheet2->setCellValue('C4', 'Kegiatan');
$sheet2->setCellValue('D4', 'komponen');
$sheet2->setCellValue('E4', 'akun');
$sheet2->setCellValue('F4', 'Uraian');
$sheet2->setCellValue('G4', 'Kode');

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

$sheet2->getColumnDimension('A')->setWidth(3);
$sheet2->getColumnDimension('B')->setWidth(10);
$sheet2->getColumnDimension('C')->setWidth(10);
$sheet2->getColumnDimension('D')->setWidth(50);
$sheet2->getColumnDimension('E')->setWidth(15);

$sql_komp = $crud->showTable($tb_akun);
$no = 1;
$row = 5;
while ($datakomp = $sql_komp->fetch(PDO::FETCH_OBJ)) {


    $sheet2->setCellValue('A' . $row, $no);
    $sheet2->setCellValue('B' . $row, $datakomp->kategori);
    $sheet2->setCellValue('C' . $row, $datakomp->kegiatan);
    $sheet2->setCellValue('D' . $row, $datakomp->komponen);
    $sheet2->setCellValue('E' . $row, $datakomp->akun);
    $sheet2->setCellValue('F' . $row, $datakomp->uraian);
    $sheet2->setCellValue('G' . $row, $datakomp->kode_id);
    $row++;
    $no++;

    $sheet2->getStyle('A4:G' . $row)->applyFromArray($style_row);
    $sheet2->getStyle('A4:G' . $row + 1)->getFont()->setSize(10);
}
$spreadsheet->getActiveSheet()->setTitle("AKUN");


//SHEET - 3 //KOMPONEN
$spreadsheet->createSheet();
$sheet3 = $spreadsheet->setActiveSheetIndex(2);
$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);

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


$spreadsheet->getActiveSheet(1)->getStyle('A4:F4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet3->getStyle('A4:F4')->applyFromArray($style_row);
$sheet3->setCellValue('A4', 'No');
$sheet3->setCellValue('B4', 'Kategori');
$sheet3->setCellValue('C4', 'Kegiatan');
$sheet3->setCellValue('D4', 'komponen');
$sheet3->setCellValue('E4', 'Uraian');
$sheet3->setCellValue('F4', 'Kode');

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

$sheet3->getColumnDimension('A')->setWidth(3);
$sheet3->getColumnDimension('B')->setWidth(10);
$sheet3->getColumnDimension('C')->setWidth(10);
$sheet3->getColumnDimension('D')->setWidth(10);
$sheet3->getColumnDimension('E')->setWidth(50);
$sheet3->getColumnDimension('F')->setWidth(15);

$sql_komp = $crud->showTable($tb_komponen);
$no = 1;
$row = 5;
while ($datakomp = $sql_komp->fetch(PDO::FETCH_OBJ)) {


    $sheet3->setCellValue('A' . $row, $no);
    $sheet3->setCellValue('B' . $row, $datakomp->kategori);
    $sheet3->setCellValue('C' . $row, $datakomp->kegiatan);
    $sheet3->setCellValue('D' . $row, $datakomp->komponen);
    $sheet3->setCellValue('E' . $row, $datakomp->uraian);
    $sheet3->setCellValue('F' . $row, $datakomp->kode_id);
    $row++;
    $no++;

    $sheet3->getStyle('A4:F' . $row)->applyFromArray($style_row);
    $sheet3->getStyle('A4:F' . $row + 1)->getFont()->setSize(10);
}
$spreadsheet->getActiveSheet()->setTitle("KOMPONEN");


//SHEET - 4 //DETAIL
$spreadsheet->createSheet();
$sheet4 = $spreadsheet->setActiveSheetIndex(3);
$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);

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

$spreadsheet->getActiveSheet(1)->getStyle('A4:Y4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet4->getStyle('A4:Y4')->applyFromArray($style_row);
$sheet4->setCellValue('A4', 'No');
$sheet4->setCellValue('B4', 'Kategori');
$sheet4->setCellValue('C4', 'Kegiatan');
$sheet4->setCellValue('D4', 'komponen');
$sheet4->setCellValue('E4', 'akun');
$sheet4->setCellValue('F4', 'detail');
$sheet4->setCellValue('G4', 'detail_id');
$sheet4->setCellValue('H4', 'Uraian');
$sheet4->setCellValue('I4', 'volume');
$sheet4->setCellValue('J4', 'satuan');
$sheet4->setCellValue('K4', 'unit');
$sheet4->setCellValue('L4', 'total');
$sheet4->setCellValue('M4', 'komponen_1');
$sheet4->setCellValue('N4', 'komponen_2');
$sheet4->setCellValue('O4', 'komponen_3');
$sheet4->setCellValue('P4', 'komponen_4');
$sheet4->setCellValue('Q4', 'unit_1');
$sheet4->setCellValue('R4', 'unit_2');
$sheet4->setCellValue('S4', 'unit_3');
$sheet4->setCellValue('T4', 'unit_4');
$sheet4->setCellValue('U4', 'pembiayaan_id');
$sheet4->setCellValue('V4', 'pembiayaan_kegiatan');
$sheet4->setCellValue('W4', 'pembiayaan_komponen');
$sheet4->setCellValue('X4', 'pembiayaan_akun');
$sheet4->setCellValue('Y4', 'kode_id');

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

$sheet4->getColumnDimension('A')->setWidth(3);
$sheet4->getColumnDimension('B')->setWidth(10);
$sheet4->getColumnDimension('C')->setWidth(10);
$sheet4->getColumnDimension('D')->setWidth(10);
$sheet4->getColumnDimension('E')->setWidth(15);
$sheet4->getColumnDimension('F')->setWidth(15);
$sheet4->getColumnDimension('G')->setWidth(15);
$sheet4->getColumnDimension('H')->setWidth(50);
$sheet4->getColumnDimension('I')->setWidth(15);
$sheet4->getColumnDimension('J')->setWidth(15);
$sheet4->getColumnDimension('K')->setWidth(15);
$sheet4->getColumnDimension('L')->setWidth(15);
$sheet4->getColumnDimension('M')->setWidth(15);
$sheet4->getColumnDimension('N')->setWidth(15);
$sheet4->getColumnDimension('O')->setWidth(15);
$sheet4->getColumnDimension('P')->setWidth(15);
$sheet4->getColumnDimension('Q')->setWidth(15);
$sheet4->getColumnDimension('R')->setWidth(15);
$sheet4->getColumnDimension('S')->setWidth(15);
$sheet4->getColumnDimension('T')->setWidth(15);
$sheet4->getColumnDimension('U')->setWidth(20);
$sheet4->getColumnDimension('V')->setWidth(20);
$sheet4->getColumnDimension('W')->setWidth(20);
$sheet4->getColumnDimension('X')->setWidth(20);
$sheet4->getColumnDimension('Y')->setWidth(20);

$sql_komp = $crud->showTable($tb_detail);
$no = 1;
$row = 5;
while ($datakomp = $sql_komp->fetch(PDO::FETCH_OBJ)) {
    $sheet4->setCellValue('A' . $row, $no);
    $sheet4->setCellValue('B' . $row, $datakomp->kategori);
    $sheet4->setCellValue('C' . $row, $datakomp->kegiatan);
    $sheet4->setCellValue('D' . $row, $datakomp->komponen);
    $sheet4->setCellValue('E' . $row, $datakomp->akun);
    $sheet4->setCellValue('F' . $row, $datakomp->detail);
    $sheet4->setCellValue('G' . $row, $datakomp->detail_id);
    $sheet4->setCellValue('H' . $row, $datakomp->uraian);
    $sheet4->setCellValue('I' . $row, $datakomp->volume);
    $sheet4->setCellValue('J' . $row, $datakomp->satuan);
    $sheet4->setCellValue('K' . $row, $datakomp->unit);
    $sheet4->setCellValue('L' . $row, $datakomp->total);
    $sheet4->setCellValue('M' . $row, $datakomp->komponen_1);
    $sheet4->setCellValue('N' . $row, $datakomp->komponen_2);
    $sheet4->setCellValue('O' . $row, $datakomp->komponen_3);
    $sheet4->setCellValue('P' . $row, $datakomp->komponen_4);
    $sheet4->setCellValue('Q' . $row, $datakomp->unit_1);
    $sheet4->setCellValue('R' . $row, $datakomp->unit_2);
    $sheet4->setCellValue('S' . $row, $datakomp->unit_3);
    $sheet4->setCellValue('T' . $row, $datakomp->unit_4);
    $sheet4->setCellValue('U' . $row, $datakomp->pembiayaan_id);
    $sheet4->setCellValue('V' . $row, $datakomp->pembiayaan_kegiatan);
    $sheet4->setCellValue('W' . $row, $datakomp->pembiayaan_komponen);
    $sheet4->setCellValue('X' . $row, $datakomp->pembiayaan_akun);
    $sheet4->setCellValue('Y' . $row, $datakomp->kode_id);

    $row++;
    $no++;

    $sheet4->getStyle('A4:Y' . $row)->applyFromArray($style_row);
    $sheet4->getStyle('A4:Y' . $row + 1)->getFont()->setSize(10);
}
$spreadsheet->getActiveSheet()->setTitle("DETAIL");

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

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