<?php
include "../../inc/config.php";
/** Error reporting */
error_reporting(E_ALL);



/** PHPExcel */
require_once '../../lib/PHPExcel.php';
/** PHPExcel_IOFactory */
require_once '../../lib/PHPExcel/IOFactory.php';

$url = $db->get_service_url('rest');
$token = $db->get_token();



  $id_reg = $_GET['id_reg'];
  $id = explode("|", $id_reg);



    $filter_krs = "id_registrasi_mahasiswa='".$id[0]."' and id_semester='".$id[1]."' and id_prodi='".$id[2]."'";
    
    $temp_data_akm = [
      'act' => 'GetDetailNilaiPerkuliahanKelas',
        'token' => $token,
        'filter' => $filter_krs,
        'order' => "nama_mata_kuliah asc",
        'limit' => "",
        'offset' => ""
    ];

    //print_r($temp_data_akm);
    $temp_data = json_decode($db->run($temp_data_akm,$url)); 
$nama_mahasiswa = str_replace(" ", "_", $temp_data->data[0]->nama_mahasiswa);

class CustomFilter implements \PHPExcel_Reader_IReadFilter 
{ 
    public function readCell($column, $row, $worksheetName = '') 
    { 
        // Read rows 1 to 7 and columns A to E only 
        if ($row >= 1 && $row <=40) { 
            if (\in_array($column,range('A','N'))) { 
                return true; 
            } 
        } 
        return false; 
    } 
}

$cm = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
\PHPExcel_Settings::setCacheStorageMethod($cm);


$readFilter = new \CustomFilter(); 

$objReader = \PHPExcel_IOFactory::createReader('Excel2007'); 

$objReader->setReadFilter($readFilter);  
$objPHPExcel = $objReader->load("down_khs.xlsx");

//SET SHEET KRS
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('E7', $temp_data->data[0]->nama_semester);
$objPHPExcel->getActiveSheet()->setCellValue('E8', $temp_data->data[0]->nama_program_studi);
$objPHPExcel->getActiveSheet()->setCellValue('E9', $temp_data->data[0]->nim);
$objPHPExcel->getActiveSheet()->setCellValue('E10', $temp_data->data[0]->nama_mahasiswa);

$objPHPExcel->getActiveSheet()
    ->getStyle('E7:E10')
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);

$baseRow = 14;
$ipk = "";


$jml_sks = 0;
$jml_kali = 0;
$nilai_angka = 0;
$nilai_indeks = 0;

$r = 0;
 //$objPHPExcel->getActiveSheet()->insertNewRowBefore(14,10);
 foreach($temp_data->data as $dataRow) {

	$row = $baseRow + $r;

  $nilai_angka =  $dataRow->sks_mata_kuliah;
  $nilai_indeks = $dataRow->nilai_indeks;

  $nilai_kalis = ( $nilai_angka * $nilai_indeks );
  $nilai_kali = number_format($nilai_kalis, 2, ',', ' ');
  $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
	//$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,2);
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C'.$row.':I'.$row);
	$objPHPExcel->getActiveSheet()
    ->getStyle('B'.$row)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
  $objPHPExcel->getActiveSheet()
    ->getStyle('C'.$row)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
   
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1)
						->setCellValue('B'.$row, $dataRow->kode_mata_kuliah)
						->setCellValue('C'.$row, $dataRow->nama_mata_kuliah)
						->setCellValue('J'.$row, $dataRow->sks_mata_kuliah)
            ->setCellValue('K'.$row, $dataRow->nilai_angka)
            ->setCellValue('L'.$row, $dataRow->nilai_huruf)
            ->setCellValue('M'.$row, number_format($dataRow->nilai_indeks, 2, ',', ' '))
            ->setCellValue('N'.$row, $nilai_kali);
	$temp_row = $row;
	$jml_sks +=floatval($dataRow->sks_mata_kuliah);
  $jml_kali +=$nilai_kalis;
  $r++;
}

 $temp_row = $row+2;
 $end_row = $row+1;
 $end_row_ips = $row+2;
 $end_row_ipk = $row+3;
 $end_row_note = $row+5;

 
 // $objPHPExcel->getActiveSheet()->insertNewRowBefore($signature,10);

$objPHPExcel->getActiveSheet()->removeRow($end_row,1);
 //$objPHPExcel->getActiveSheet()->insertNewRowBefore($end_row_ips,2);

	$objPHPExcel->getActiveSheet()->setCellValue('J'.$end_row, $jml_sks);
  $objPHPExcel->getActiveSheet()->setCellValue('J'.$end_row_ips, '=ROUND('.'N'.$end_row.'/'.'J'.$end_row.',2)');  
  $objPHPExcel->getActiveSheet()->setCellValue('N'.$end_row, $jml_kali);
 $styleArray = array(
      'borders' => array(
          'bottom' => array(
              'style' => PHPExcel_Style_Border::BORDER_DOUBLE
          )
      )
  );

 $styleArray1 = array(
      'borders' => array(
          'allborders' => array(
              'style' => PHPExcel_Style_Border::BORDER_THIN
          )
      )
  );

  $styleArray2 = array(
      'borders' => array(
          'right' => array(
              'style' => PHPExcel_Style_Border::BORDER_DOUBLE
          )
      )
  );

 $objPHPExcel->getActiveSheet()->getStyle('A'.$end_row.':N'.$end_row_ipk)->applyFromArray($styleArray1);
 $objPHPExcel->getActiveSheet()->getStyle('N'.$end_row.':N'.$end_row_ipk)->applyFromArray($styleArray2);
 	
    $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$end_row_ips.':I'.$end_row_ips);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$end_row_ipk.':I'.$end_row_ipk);
	$objPHPExcel->getActiveSheet()
    ->getStyle('A'.$end_row.':'.'N'.$end_row_ipk)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $objPHPExcel->getActiveSheet()
    ->getStyle('B'.$end_row_note)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

	$objPHPExcel->getActiveSheet()->setCellValue('A'.$end_row, 'JUMLAH');
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$end_row_ips, 'IP Semester (IPS)');
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$end_row_ipk, 'IP Kumulatif (IPK)');

      $temp_data_akm = [
        'act' => 'GetDetailPerkuliahanMahasiswa',
          'token' => $token,
          'filter' => $filter_krs,
          'limit' => 1,
          'offset' => 0
      ];

      $temp_akm = json_decode($db->run($temp_data_akm,$url));
       
      $ipk = "";
      $beban = "";
          if ($temp_akm->data) {
    
            $ipk = $temp_akm->data[0]->ipk;
             $objPHPExcel->getActiveSheet()->setCellValue('J'.$end_row_ipk, $ipk);
              if ($ipk<=1.49) {
                $beban = 12;
              } elseif ($ipk>1.49&&$ipk<=1.99) {
                $beban = 15;
              } elseif ($ipk>1.99&&$ipk<=2.49) {
                $beban = 18;
              } elseif ($ipk>2.49&&$ipk<=2.99) {
                $beban = 21;
              } else {
                $beban = 24;
              }
               $objPHPExcel->getActiveSheet()->setCellValue('H'.$end_row_note, $beban);
          }
            $objPHPExcel->getActiveSheet()
    ->getStyle('H'.$end_row_note)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);


 //$objPHPExcel->getActiveSheet()->setCellValue('J'.$end_row_ipk, 2);
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$end_row_note, 'Maks. Beban sks semester berikutnya :');
   $objPHPExcel->getActiveSheet()->getStyle('A'.$end_row_ipk.':N'.$end_row_ipk)->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->getStyle("A$end_row:N$end_row_note")->getFont()->setBold(true);


  $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$end_row.':I'.$end_row);
//$objPHPExcel->getActiveSheet()->setCellValue('H'.$temp_row, date('d F Y'));



// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Data KHS');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
//$sheet->getStyle($column.$style)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);


// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Data_KHS_'.$nama_mahasiswa.'.xlsx"');
header('Cache-Control: max-age=0');


$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
return $objWriter->save('php://output');
exit;
?>

