<?php
// Load file koneksi.php
include "../koneksi.php";

// Load plugin PHPExcel nya
require_once '../library/PHPExcel.php';

// Panggil class PHPExcel nya
$excel = new PHPExcel();
// Settingan awal fil excel
$excel->getProperties()->setCreator('absensi')
->setLastModifiedBy('absensi')
->setTitle("Data Laporan")
->setSubject("Laporan")
->setDescription("Semua Data Laporan")
->setKeywords("Data Laporan");

// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$style_col = array(
  'font' => array('bold' => true), // Set font nya jadi bold
  'alignment' => array(
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
  ),
  'borders' => array(
    'top' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
    'right' => array('style'  => PHPExcel_Style_Border::BORDER_THIN),  // Set border right dengan garis tipis
    'bottom' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
    'left' => array('style'  => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
  )
);

// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = array(
  'alignment' => array(
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
  ),
  'borders' => array(
    'top' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border top dengan garis tipis
    'right' => array('style'  => PHPExcel_Style_Border::BORDER_THIN),  // Set border right dengan garis tipis
    'bottom' => array('style'  => PHPExcel_Style_Border::BORDER_THIN), // Set border bottom dengan garis tipis
    'left' => array('style'  => PHPExcel_Style_Border::BORDER_THIN) // Set border left dengan garis tipis
  )
);



// $tgl_dari = $_GET['tanggal_dari'];
// $tgl_sampai = $_GET['tanggal_sampai'];

$km = $_GET['km'];              
$kelas = mysqli_query($koneksi, "select * from kelas, kelas_mapel, jurusan, ta, mapel, guru where km_guru=guru_id and km_mapel=mapel_id and km_kelas=kelas_id and km_id='$km' and kelas_ta=ta_id and kelas_jurusan=jurusan_id");
$k = mysqli_fetch_assoc($kelas);
$id_kelas = $k['kelas_id'];
$id_ta = $k['kelas_ta'];
$id_km = $k['km_id'];



$excel->setActiveSheetIndex(0)->setCellValue('A1', "absensi"); // Set kolom A1 dengan tulisan "DATA SISWA"
$excel->getActiveSheet()->mergeCells('A1:N1'); // Set Merge Cell pada kolom A1 sampai F1
$excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(TRUE); // Set bold kolom A1
$excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15); // Set font size 15 untuk kolom A1

$excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom A1


$excel->setActiveSheetIndex(0)->setCellValue('B3', "Mata Pelajaran");
$excel->setActiveSheetIndex(0)->setCellValue('C3', $k['mapel_nama']);

$excel->setActiveSheetIndex(0)->setCellValue('B4', "Nama Kelas");
$excel->setActiveSheetIndex(0)->setCellValue('C4', $k['kelas_nama']);

$excel->setActiveSheetIndex(0)->setCellValue('B5', "Jurusan");
$excel->setActiveSheetIndex(0)->setCellValue('C5', $k['jurusan_nama']);

$excel->setActiveSheetIndex(0)->setCellValue('B6', "Tahun Ajaran");
$excel->setActiveSheetIndex(0)->setCellValue('C6', $k['ta_nama']);

$excel->setActiveSheetIndex(0)->setCellValue('B7', "Guru Pengajar");
$excel->setActiveSheetIndex(0)->setCellValue('C7', $k['guru_nama']);

$excel->getActiveSheet()->mergeCells('A8:A9');
$excel->getActiveSheet()->mergeCells('B8:B9');


$excel->setActiveSheetIndex(0)->setCellValue('A8', "NO");
$excel->setActiveSheetIndex(0)->setCellValue('B8', "NAMA");


$jumlah_input = mysqli_query($koneksi,"select * from absen where absen_km='$id_km' group by absen_tanggal");
$ji = mysqli_num_rows($jumlah_input); 
if($ji > 0){

  $huruf = "C";
  for ($i=0; $i < $ji-1; $i++) { 
    $huruf++;
  }



  $id_km = $k['km_id'];
  $huruf1 = "C";
  $tgl = mysqli_query($koneksi,"select * from absen where absen_km='$id_km' group by absen_tanggal order by date(absen_tanggal) asc");                
  while($t = mysqli_fetch_array($tgl)){


    $excel->setActiveSheetIndex(0)->setCellValue($huruf1++.'9', date('d/m/Y', strtotime($t['absen_tanggal'])));  

  }

  $excel->getActiveSheet()->mergeCells('C8:'.$huruf.'8');  
  $excel->setActiveSheetIndex(0)->setCellValue('C8', "TANGGAL PELAJARAN"); 
  $excel->setActiveSheetIndex(0)->setCellValue($huruf1++.'9', "Plus"); 
  $huruf2 = $huruf1; 
  $excel->setActiveSheetIndex(0)->setCellValue($huruf1++.'9', "Minus");  

}

$huruf++;
$excel->getActiveSheet()->mergeCells($huruf.'8:'.$huruf2.'8');
$excel->setActiveSheetIndex(0)->setCellValue($huruf.'8', "POIN");  


for ($i= "A"; $i <= $huruf2; $i++) { 
  // code...
  $excel->getActiveSheet()->getStyle($i.'8')->applyFromArray($style_col);
  $excel->getActiveSheet()->getStyle($i.'9')->applyFromArray($style_col);  
}


foreach(range('A',$huruf2) as $columnID) {
  $excel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}



$no=1;

$angka = "10";
$id = $id_kelas; 
$angka1 = 10;
$terbesar = 10;
// perulangan siswa
$diki = 1;
$data = mysqli_query($koneksi,"SELECT * FROM siswa, jurusan where siswa_jurusan=jurusan_id and siswa_id in (select ks_siswa from kelas_siswa where ks_kelas='$id') order by siswa_id desc");
while($d = mysqli_fetch_array($data)){
  // if($diki == 1){


  
  // $angka1 = $angka;  
  $total_plus = 0;
  $total_minus = 0;

  $angka = $terbesar;


   $excel->getActiveSheet()->getStyle("A".$angka)->applyFromArray($style_row); 
   $excel->getActiveSheet()->getStyle("B".$angka)->applyFromArray($style_row); 


  $excel->setActiveSheetIndex(0)->setCellValue('A'.$angka, $no++);
  $excel->setActiveSheetIndex(0)->setCellValue('B'.$angka, $d['siswa_nama']);

  $id_km = $k['km_id'];
  $id_siswa = $d['siswa_id'];
  $huruf = "C";

  // perulangan tanggal
  $tgl = mysqli_query($koneksi,"select * from absen where absen_km='$id_km' group by absen_tanggal order by date(absen_tanggal) asc");
  while($t = mysqli_fetch_array($tgl)){

   

    $warna = "";
    $d_sis = $d['siswa_id'];
    $d_tgl = $t['absen_tanggal'];
    $d_km = $id_km; 


    $angka1 = $angka;
    $pres = mysqli_query($koneksi,"select * from absen, prestasi where absen_prestasi=prestasi_id and absen_siswa='$d_sis' and date(absen_tanggal)='$d_tgl' and absen_km='$d_km'");
    $x_text = "";
    while($p = mysqli_fetch_array($pres)){
      $x_text = $p['prestasi_nama'];   
      $total_plus += $p['prestasi_point'];


      $excel->setActiveSheetIndex(0)->setCellValue($huruf.$angka1,  $x_text);
      $angka1++;
    }

    $pres = mysqli_query($koneksi,"select * from absen, pelanggaran where absen_pelanggaran=pelanggaran_id and absen_siswa='$d_sis' and date(absen_tanggal)='$d_tgl' and absen_km='$d_km'");
    $x_text = "";
    while($p = mysqli_fetch_array($pres)){
      $x_text = $p['pelanggaran_nama'];   
      $total_minus += $p['pelanggaran_point'];


      $excel->setActiveSheetIndex(0)->setCellValue($huruf.$angka1,  $x_text);



      if($x_text == "A"){
        $excel->getActiveSheet()->getStyle($huruf.$angka1)->applyFromArray(
          array(
            'fill' => array(
              'type' => PHPExcel_Style_Fill::FILL_SOLID,
              'color' => array('rgb' => 'FF0000')
            ),
            'font'  => array(
              'bold'  => true,
              'color' => array('rgb' => 'FFFFFF'),
            )
          )
        );
      }else if($x_text == "I"){
        $excel->getActiveSheet()->getStyle($huruf.$angka1)->applyFromArray(
          array(
            'fill' => array(
              'type' => PHPExcel_Style_Fill::FILL_SOLID,
              'color' => array('rgb' => 'd9ff00')
            ),
            'font'  => array(
              'bold'  => true,
              'color' => array('rgb' => '000000'),
            )
          )
        );
      }else if($x_text == "X"){
        $excel->getActiveSheet()->getStyle($huruf.$angka1)->applyFromArray(
          array(
            'fill' => array(
              'type' => PHPExcel_Style_Fill::FILL_SOLID,
              'color' => array('rgb' => '000000')
            ),
            'font'  => array(
              'bold'  => true,
              'color' => array('rgb' => 'FFFFFF'),
            )
          )
        );
      }



      $angka1++;
    }
    

    if($angka1 > $terbesar){
      // $terbesar++;
      $terbesar = $angka1;
    }

    
    // $excel->setActiveSheetIndex(0)->setCellValue($huruf.$angka,  $x_text);

    $excel->getActiveSheet()->getStyle($huruf.$angka)->applyFromArray($style_row);  
    $huruf++;
  }


  $huruf_a = $huruf++;
  $excel->setActiveSheetIndex(0)->setCellValue($huruf_a.$angka,  $total_plus);
  $excel->getActiveSheet()->getStyle($huruf_a.$angka)->applyFromArray($style_row);  
  
  $huruf_b = $huruf++;
  $excel->setActiveSheetIndex(0)->setCellValue($huruf_b.$angka,  $total_minus);
  $excel->getActiveSheet()->getStyle($huruf_b.$angka)->applyFromArray($style_row);  

  $ax = $terbesar-1;
   $excel->getActiveSheet()->mergeCells($huruf_a.$angka.':'.$huruf_a.$ax); // Set Merge Cell pada kolom A1 sampai F1
   $excel->getActiveSheet()->mergeCells($huruf_b.$angka.':'.$huruf_b.$ax); // Set Merge Cell pada kolom A1 sampai F1



 

  $angka++;
// }
// $diki++;
}


$a = "A";

for ($i=$a; $i <= $huruf_b; $i++) { 

  for ($j=10; $j <= $ax; $j++) { 

    $excel->getActiveSheet()->getStyle($i.$j)->applyFromArray($style_row);

  }

}























    // $excel->getActiveSheet()->getStyle('A8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('B8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('C8')->applyFromArray($style_row);

    // $excel->getActiveSheet()->getStyle('D8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('E8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('F8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('G8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('H8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('I8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('J8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('K8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('L8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('M8')->applyFromArray($style_row);
    // $excel->getActiveSheet()->getStyle('N8')->applyFromArray($style_row);


    // $huruf = "F";
    // $pertanyaan = mysqli_query($koneksi,"select * from pertanyaan");
    // while($p = mysqli_fetch_array($pertanyaan)){
      //   $hurufx = $huruf++;
      //   $excel->setActiveSheetIndex(0)->setCellValueExplicit($hurufx.'3', $p['pertanyaan'], PHPExcel_Cell_DataType::TYPE_STRING);
      //   $excel->getActiveSheet()->getStyle($hurufx.'3')->applyFromArray($style_row);
      // }

      // auto width excel
      // foreach(range('A',$hurufx) as $columnID) {
        //     $excel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
        // }

        // $excel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);




        // $x_total_sub_total = 0;
        // $x_total_total = 0;
        // $x_total_modal = 0;
        // $x_total_laba = 0;
        // $sql = mysqli_query($koneksi,"SELECT * FROM invoice,kasir,transaksi,produk where transaksi_produk=produk_id and transaksi_invoice=invoice_id and kasir_id=invoice_kasir and date(invoice_tanggal) >= '$tgl_dari' and date(invoice_tanggal) <= '$tgl_sampai' order by invoice_id desc");

        // $no = 1; // Untuk penomoran tabel, di awal set dengan 1
        // $numrow = 7; // Set baris pertama untuk isi tabel adalah baris ke 4

        // while($data = mysqli_fetch_array($sql)){

          //  $sub_total = $data['transaksi_harga'] * $data['transaksi_jumlah'];
          //  $d_modal = $data['produk_harga_modal'] * $data['transaksi_jumlah'];
          //  $d_laba = $sub_total - $d_modal;

          //  $excel->setActiveSheetIndex(0)->setCellValue('A'.$numrow, $no);
          //  $excel->setActiveSheetIndex(0)->setCellValue('B'.$numrow, $data['invoice_nomor']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('C'.$numrow, date('d-m-Y', strtotime($data['invoice_tanggal'])));
          //  $excel->setActiveSheetIndex(0)->setCellValue('D'.$numrow, $data['invoice_pelanggan']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('E'.$numrow, $data['kasir_nama']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('F'.$numrow, $data['produk_nama']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('G'.$numrow, $data['transaksi_jumlah']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('H'.$numrow, $sub_total);
          //  $excel->setActiveSheetIndex(0)->setCellValue('I'.$numrow, $sub_total * $data['transaksi_diskon'] / 100);
          //  $excel->setActiveSheetIndex(0)->setCellValue('J'.$numrow, $data['transaksi_total']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('K'.$numrow, $d_modal);
          //  $excel->setActiveSheetIndex(0)->setCellValue('L'.$numrow, $d_laba);
          //  $excel->setActiveSheetIndex(0)->setCellValue('M'.$numrow, $data['invoice_bayar']);
          //  $excel->setActiveSheetIndex(0)->setCellValue('N'.$numrow, $data['invoice_kembalian']);

          //   // $excel->setActiveSheetIndex(0)->setCellValueExplicit('E'.$numrow, $data['mahasiswa_email'], PHPExcel_Cell_DataType::TYPE_STRING);

          //   // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
          //  $excel->getActiveSheet()->getStyle('A'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('B'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('C'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('D'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('E'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('F'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('G'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('H'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('I'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('J'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('K'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('L'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('M'.$numrow)->applyFromArray($style_row);
          //  $excel->getActiveSheet()->getStyle('N'.$numrow)->applyFromArray($style_row);


          // foreach(range('A','N') as $columnID) {
            //     $excel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
            // }

            //  $excel->getActiveSheet()->getStyle('G'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('H'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('I'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('J'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('K'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('L'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('M'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
            //  $excel->getActiveSheet()->getStyle('N'.$numrow)->getNumberFormat()->setFormatCode('#,##0');


            //   // $id_mahasiswa = $data['mahasiswa_id'];
            //   // $huruf = "F";
            //   // $polling = mysqli_query($koneksi,"SELECT * FROM polling,mahasiswa,pertanyaan,jawaban WHERE mahasiswa_id=polling_mahasiswa AND mahasiswa_id='$id_mahasiswa' AND polling_pertanyaan=pertanyaan_id AND polling_jawaban=jawaban_id");
            //   // while($p = mysqli_fetch_array($polling)){
              //   //   $hurufx = $huruf++;
              //   //   $excel->setActiveSheetIndex(0)->setCellValueExplicit($hurufx.$numrow, $p['jawaban'], PHPExcel_Cell_DataType::TYPE_STRING);
              //   //   $excel->getActiveSheet()->getStyle($hurufx.'3')->applyFromArray($style_row);
              //   //   $excel->getActiveSheet()->getStyle($hurufx.$numrow)->applyFromArray($style_row);
              //   // }




              //  $excel->getActiveSheet()->getRowDimension($numrow)->setRowHeight(20);

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

              //   $x_total_sub_total += $sub_total;
              //   $x_total_total += $data['transaksi_total'];
              //   $x_total_modal += $d_modal;
              //   $x_total_laba += $d_laba;

              // }

              //  $excel->getActiveSheet()->mergeCells('A'.$numrow.':G'.$numrow); // Set Merge Cell pada kolom A1 sampai F1
              //  $excel->setActiveSheetIndex(0)->setCellValue('A'.$numrow, "TOTAL");


              //  $excel->setActiveSheetIndex(0)->setCellValue('H'.$numrow, $x_total_sub_total);
              //  $excel->setActiveSheetIndex(0)->setCellValue('I'.$numrow, '');
              //  $excel->setActiveSheetIndex(0)->setCellValue('J'.$numrow, $x_total_total);
              //  $excel->setActiveSheetIndex(0)->setCellValue('K'.$numrow, $x_total_modal);
              //  $excel->setActiveSheetIndex(0)->setCellValue('L'.$numrow, $x_total_laba);
              //  $excel->setActiveSheetIndex(0)->setCellValue('M'.$numrow, '');
              //  $excel->setActiveSheetIndex(0)->setCellValue('N'.$numrow, '');




              // $excel->getActiveSheet()->getStyle('H'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('I'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('J'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('K'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('L'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('M'.$numrow)->getNumberFormat()->setFormatCode('#,##0');
              // $excel->getActiveSheet()->getStyle('N'.$numrow)->getNumberFormat()->setFormatCode('#,##0');

              // $excel->getActiveSheet()->getStyle('A'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('B'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('C'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('D'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('E'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('F'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('G'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('H'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('I'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('J'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('K'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('L'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('M'.$numrow)->applyFromArray($style_row);
              // $excel->getActiveSheet()->getStyle('N'.$numrow)->applyFromArray($style_row);


              // Set orientasi kertas jadi LANDSCAPE
$excel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

              // Set judul file excel nya
$excel->getActiveSheet(0)->setTitle("absensi");
$excel->setActiveSheetIndex(0);

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

              $write = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
              $write->save('php://output');
              ?>
