<?php
session_start();
$time_start = microtime(true); 
include "../../inc/config.php";
session_check();
 
 /** PHPExcel_IOFactory */

require('../../lib/simplexlsx.class.php');

switch ($_GET["act"]) {
case 'download':
  require_once '../Writer.php';
      $writer = new XLSXWriter();
$style =
        array (
                      array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'C1',
                    'E1',
                    'F1',
                    'G1',
                    'H1',
                    'I1',
                    'J1',
                    'K1',
                    'L1',
                    'M1',
                    'N1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                      'B1',
                      'D1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

//column width
$col_width = array(
  1 => 12,
  2 => 28,
  3 => 16,
  4 => 19,
  5 => 9,
  6 => 9,
  7 => 17,
  8 => 11,
  9 => 7,
  10 => 7,
  11 => 7,
  12 => 7,
  13 => 19,
  14 => 16
  );
$writer->setColWidth($col_width);

$header = array(
  'NIM'=>'string',
  'Nama'=>'string',
  'Kode Matakuliah'=>'string',
  'Nama Matakuliah'=>'string',
  'Semester'=>'string',
  'Kelas' => 'string',
  'Aktivitas Partisipatif'=>'string',
  'Hasil proyek' => 'string',
  'QUIZ' => 'string',
  'TUGAS' => 'string',
  'UTS' => 'string',
  'UAS' => 'string',
  'Kode Prodi Mahasiswa'=>'string',
  'Kode Prodi Kelas'=>'string'
);
$data_rec = array();

        $data_json = json_decode(file_get_contents('data.json'));

        foreach ($data_json as $key) {
          
                      $data_rec[] = array(
                          $key->nim,
                          $key->nama,
                          $key->kode_mk,
                          $key->nama_mk,
                          $key->semester,
                          $key->nama_kelas,
                          $key->partisipatif,
                          $key->proyek,
                          $key->quiz,
                          $key->tugas,
                          $key->uts,
                          $key->uas,
                          $key->kode_jurusan,
                          $key->kode_jurusan_kelas,
                        );
        }

$comments = array(
'A1' => 'NIM/NIPD Mahasiswa
Info : Wajib diisi',
'B1' => 'Nama Mahasiswa
Info : Boleh Kosong',
'C1' => 'NIM/NIPD Mahasiswa
Info : Wajib diisi',
'D1' => 'Nama Matakuliah
Info : Boleh Kosong',
'E1' => 'Semester Pelaporan Nilai
format : tahun+semester (1 : Ganjil, 2 : Genap, 3 : Pendek)
contoh : 20231 (berarti 2023 ganjil)
info : Wajib disi',
'F1' => 'Nama Kelas Kuliah
Ket : Nama kelas tidak boleh melebihi 5 karakter
Info : Wajib Diisi',
'G1' => 'Komponen Nilai Aktivitas Partisipatif
info : wajib disi',
'H1' => 'Komponen Nilai Hasil Proyek
info : wajib disi',
'I1' => 'Komponen Nilai Kuis
info : wajib disi',
'J1' => 'Komponen Nilai TUGAS
info : wajib disi',
'K1' => 'Komponen Nilai UTS
info : wajib disi',
'L1' => 'Komponen Nilai UAS
info : wajib disi',
'M1' => 'Kode Prodi/Jurusan Mahasiswa 
Lihat di menu master data -> jurusan
Wajib diisi',
'M1' => 'Kode Prodi/Jurusan Kelas Kuliah 
Lihat di menu master data -> jurusan
Wajib diisi'
);
        $writer->setComment($comments);
        $filename = 'template_nilai_evaluasi_mahasiswa.xlsx';

        header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');

        $writer->writeSheet($data_rec,'Evaluasi', $header, $style);
        $writer->writeToStdOut();
        exit(0);
      break;
   case 'delete_error':
    $db->fetch_custom("delete from nilai_evaluasi_mahasiswa where status_error=2 and kode_jurusan='".$_POST['id']."'");
    break;
    case 'delete_all':
  
    $status = $_POST['status'];
    if ($status=='all') {
        $status = '';
    } elseif ($status=='0') {
        $status = "and status_error ='0'";
    } elseif ($status=='1') {
        $status = "and status_error ='1'";
    } elseif ($status=='2') {
        $status = "and status_error ='2'";
    }

    if ($_POST['jurusan']=='all') {
        $jurusan = "";
    } else {
        $jurusan = "and kode_jurusan='".$_POST['jurusan']."'";
    }

        if ($_POST['sem']=='all') {
        $semester = "";
    } else {
        $semester = "and semester='".$_POST['sem']."'";
    }

      if ($status=="" && $jurusan=="" && $semester=="") {
      
      if ($_SESSION['level']==1) {
         $db->fetch_custom("TRUNCATE `nilai_evaluasi_mahasiswa`");
       } else {
          $db->fetch_custom("delete from nilai_evaluasi_mahasiswa where kode_jurusan='".$_SESSION['jurusan']."'");
       }
    } else {
      $ids = $db->fetch_custom("select id from nilai_evaluasi_mahasiswa where id!='' $semester $jurusan $status");
      foreach ($ids as $id) {
        $id_delete[] = $id->id;
      }
        $data_id = implode(",", $id_delete);
       $data_mhs_pt = $db->fetch_custom("delete from nilai_evaluasi_mahasiswa where id!='' $semester $jurusan $status and id in ($data_id)");
    }


    
    break;

     case 'import':
  if (!is_dir("../../../upload/nilai")) {
              mkdir("../../../upload/nilai");
            }


   if (!preg_match("/.(xls|xlsx)$/i", $_FILES["semester"]["name"]) ) {

              echo "pastikan file yang anda pilih xls|xlsx";
              exit();

            } else {
              move_uploaded_file($_FILES["semester"]["tmp_name"], "../../../upload/nilai/".$_FILES['semester']['name']);
              $semester = array("semester"=>$_FILES["semester"]["name"]);

            }


$error_count = 0;
$error = array();
$sukses = 0;
$values = "";
$array_insert = array();
  //$Reader = new SpreadsheetReader("../../../upload/nilai/".$_FILES['semester']['name']);
  $Reader = new SimpleXLSX("../../../upload/nilai/".$_FILES['semester']['name']);


foreach( $Reader->rows() as $key => $val ) {
      if ($key>0) {
  //foreach ($Reader as $key => $val)
      if ($val[0]!='') {
        $check = $db->check_exist('nilai_evaluasi_mahasiswa',array(
          'nim'=> $db->trimmer($val[0]),
          'kode_mk' => $db->trimmer($val[2]),
          'semester'=> $db->trimmer($val[4]),
          'nama_kelas'=> $db->trimmer($val[5]),
          'kode_jurusan'=> $db->trimmer($val[12]),
          'kode_jurusan_kelas'=> $db->trimmer($val[13])
        ));
    if ($check==true) {
      $error_count++;
      $error[] = $val[0]." ".$val[2]." Sudah Ada";
     
    } else {
      $sukses++;
      $array_insert[] = array(
        'nim' => $db->trimmer($val[0]),
        'nama' => $db->trimmer($val[1]),
        'kode_mk' => $db->trimmer($val[2]),
        'nama_mk' => $db->trimmer($val[3]),
        'semester' => $db->trimmer($val[4]),
        'nama_kelas' => $db->trimmer($val[5]),
        'partisipatif' => $db->trimmer($val[6]),
        'proyek' => $db->trimmer($val[7]),
        'quiz' => $db->trimmer($val[8]),
        'tugas' => $db->trimmer($val[9]),
        'uts' => $db->trimmer($val[10]),
        'uas' => $db->trimmer($val[11]),
        'kode_jurusan' => $db->trimmer($val[12]),
        'kode_jurusan_kelas' => $db->trimmer($val[13])
      );
        }
}
      
   }
}

if (!empty($array_insert)) {
  $db->insertMulti('nilai_evaluasi_mahasiswa',$array_insert);
  echo $db->getErrorMessage();
}
    unlink("../../../upload/nilai/".$_FILES['semester']['name']);
        $msg = '';
$time_end = microtime(true);
$execution_time = ($time_end - $time_start);
if (($sukses>0) || ($error_count>0)) {
  $msg =  "<div class=\"alert alert-warning alert-dismissible\" role=\"alert\" >
  <button type=\"button\" class=\"close\" data-dismiss=\"alert\" aria-hidden=\"true\">×</button>
      <font color=\"#3c763d\">".$sukses." Data Nilai  berhasil di import</font><br />
      <font color=\"#ce4844\" >".$error_count." data tidak bisa ditambahkan </font>";
      if (!$error_count==0) {
        $msg .= "<a data-toggle=\"collapse\" href=\"#collapseExample\" aria-expanded=\"false\" aria-controls=\"collapseExample\">Detail error</a>";
      }
      //echo "<br />Total: ".$i." baris data";
      $msg .= "<div class=\"collapse\" id=\"collapseExample\">";
          $i=1;
          foreach ($error as $pesan) {
              $msg .= "<div class=\"bs-callout bs-callout-danger\">".$i.". ".$pesan."</div><br />";
            $i++;
            }
      $msg .= "</div>
    </div>";
      $msg .= "<p>Total Waktu Import : ". waktu_import($execution_time);
}
  echo $msg;
    break;
  case "delete":
    
    
    
    $db->delete("nilai_evaluasi_mahasiswa","id",$_GET["id"]);
    break;
  case "up":
   $data = array(
    "nim"=>$_POST["nim"],
    "nama"=>$_POST["nama"],
    "kode_mk"=>$_POST["kode_mk"],
    "nama_mk"=>$_POST["nama_mk"],
    "nama_kelas"=>$_POST["nama_kelas"],
    "semester"=>$_POST["semester"],
    "kode_jurusan"=>$_POST["kode_jurusan"],
    "kode_jurusan_kelas"=>$_POST["kode_jurusan_kelas"],
    "partisipatif"=>$_POST["partisipatif"],
    "proyek"=>$_POST["proyek"],
    "tugas"=>$_POST["tugas"],
    "quiz"=>$_POST["quiz"],
    "uts"=>$_POST["uts"],
    "uas"=>$_POST["uas"]
  );
   
    
    $up = $db->update("nilai_evaluasi_mahasiswa",$data,"id",$_POST["id"]);
    if ($up=true) {
      echo "good";
    } else {
      return false; 
    }
    break;
    case 'del_massal':
    $data_ids = $_REQUEST['data_ids'];
       if(trim($data_ids)!="") {
        $delete =  $db->fetch_custom("delete from nilai_evaluasi_mahasiswa where id in ($data_ids)");
    }
    break;

  default:
    # code...
    break;
}

?>