<?php
include "../../inc/config.php";

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


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




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','J'))) { 
                return true; 
            } 
        } 
        return false; 
    } 
}

  $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' => ""
    ];


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

/*echo "<pre>";
    print_r($temp_data_akm);
print_r($temp_data);

exit();*/
$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_krs.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);
$baseRow = 14;

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

$jml_sks = 0;
$r = 0;
foreach($temp_data->data  as $dataRow) {

	$row = $baseRow + $r;

	$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,2);
	$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C'.$row.':I'.$row);
	$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);
	$temp_row = 4+$row;
	$jml_sks +=floatval($dataRow->sks_mata_kuliah);
  $r++;

}

 $end_row = $row+1;

	$objPHPExcel->getActiveSheet()->setCellValue('J'.$end_row, $jml_sks);

 $styleArray = array(
      'borders' => array(
          'bottom' => array(
              'style' => PHPExcel_Style_Border::BORDER_DOUBLE
          )
      )
  );

 		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A'.$end_row.':I'.$end_row);
	$objPHPExcel->getActiveSheet()
    ->getStyle('A'.$end_row)
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	$objPHPExcel->getActiveSheet()->setCellValue('A'.$end_row, 'JUMLAH SKS');
   $objPHPExcel->getActiveSheet()->getStyle('A'.$end_row.':J'.$end_row)->applyFromArray($styleArray);


$objPHPExcel->getActiveSheet()->setCellValue('H'.$temp_row, date('d F Y'));
//$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);

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

// 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_KRS_'.$nama_mahasiswa.'.xlsx"');
header('Cache-Control: max-age=0');


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

