<?php
session_start();
$time_start = microtime(true); 
include "../../inc/config.php";
session_check();
require('../../lib/simplexlsx.class.php');
switch ($_GET["act"]) {
  case 'down_template':
    require_once '../Writer.php';
    //jalur pendaftaran
    $datas_keluar = $db->fetch_custom("select * from jenis_keluar");
    foreach ($datas_keluar as $keluar) {
      $data_keluar[] = $keluar->id_jns_keluar." : ".$keluar->ket_keluar;
    }
    $writer = new XLSXWriter();
    $style =
        array (
            array(
              'border' => array(
                'style' => 'thin',
                'color' => '000000'
                ),
            'allfilleddata' => true
            ),
            array(
                'fill' => array(
                    'color' => 'ff0000'
                    ),
                'cells' => array(
                    'A1',
                    'C1',
                    'D1',
                    'E1',
                     'H1',
                     'J1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                      'B1',
                      'F1',
                      'G1',
                      'I1',
                     
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

    //column width
    $col_width = array(
      1 => 19,
      2 => 30,
      3 => 15,
      4 => 27,
      5 => 17,
      6 => 25,
      7 => 20,
      8 => 19,
      9 => 21,
      10 => 14
      );
    $writer->setColWidth($col_width);

    $header = array(
      'NIM'=>'string',
      'Nama'=>'string',
      'Jenis Keluar'=>'string',
      'Tanggal keluar / Tanggal lulus'=>'string',
      'Semester Keluar' => 'string',
      'Nomor SK'=>'string',
      'Tanggal SK' => 'string',
      'IP Kumulatif'=>'string',
      'Keterangan' => 'string',
      'Kode Prodi'=>'string'
    );

    $data_rec = array(); 
            $key = json_decode(file_get_contents('sample.json'));
                    $data_rec[] = array(
                          $key->nim,
                          $key->nama,
                          $key->id_jenis_keluar,
                          $key->tanggal_keluar,
                          $key->semester,
                          $key->sk_yudisium,
                          $key->tgl_sk_yudisium,
                          $key->ipk,
                          $key->keterangan_kelulusan,
                          $key->kode_jurusan
                    );
            $filename = 'template_kelulusan.xlsx';

$comments = array(
'A1' => 'NIM / NIPD Mahasiswa

info : wajib disi',
'B1' => 'Nama Mahasiswa
Info : Boleh kosong',
'C1' => 'Jenis keluar
'.implode("\n", $data_keluar).'

Info : Wajib Diisi',
'D1' => 'format tahun-bulan-tanggal
Info : Wajib Diisi',
'E1' => 'Peride keluar
tahun+semester
1 : ganjil, 2 genap, 
20221 berarti keluar 2022 ganjil
Info : Wajib Diisi',
'H1' => '
Info : Wajib Diisi',
'J1' => '
Info : Wajib Diisi'
);
    $writer->setComment($comments);
    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,'Template kelulusan', $header, $style);
    $writer->writeToStdOut();
    exit(0);
    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 ($status=="" && $jurusan=="") {
      if ($_SESSION['level']==1) {
         $db->fetch_custom("TRUNCATE `kelulusan`");
       } else {
        if ($_SESSION['jurusan']=='0') {
            $data_mhs_pt = $db->fetch_custom("TRUNCATE `kelulusan`");
        } else {
            $db->fetch_custom("delete from kelulusan where kode_jurusan='".$_SESSION['jurusan']."'");
        }
       }
    } else {

      $ids = $db->fetch_custom("select id from kelulusan where id!='' $jurusan $status");
      foreach ($ids as $id) {
        $id_delete[] = $id->id;
      }
        $data_id = implode(",", $id_delete);
       $data_mhs_pt = $db->fetch_custom("delete from kelulusan where id!='' $jurusan $status and id in ($data_id)");
    }


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

            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/kelulusan/".$_FILES['semester']['name']);
              $semester = array("semester"=>$_FILES["semester"]["name"]);
            }


$error_count = 0;
$error = array();
$sukses = 0;
$values = "";
$array_insert = array();

  $Reader = new SimpleXLSX("../../../upload/kelulusan/".$_FILES['semester']['name']);
  foreach( $Reader->rows() as $key => $val ) {
      if ($key>0) {
        
      if ($val[0]!='') {
          
    $check = $db->check_exist('kelulusan',array('nim'=>$db->trimmer($val[0])));
    if ($check==true) {
      $error_count++;
      $error[] = $val[0]." Sudah Ada";
    } else {
        $sukses++;
        $array_insert[] = array(
        'nim' => $db->trimmer($val[0]),
        'nama' => $db->trimmer($val[1]),
        'id_jenis_keluar' => $db->trimmer($val[2]),
        'tanggal_keluar' => $db->trimmer($val[3]),
        'semester' => $db->trimmer($val[4]),
        'sk_yudisium' => $db->trimmer($val[5]),
        'tgl_sk_yudisium' => $db->trimmer($val[6]),
        'ipk' => $db->trimmer($val[7]),
        'keterangan_kelulusan' => $db->trimmer($val[8]),
        'kode_jurusan' => $db->trimmer($val[9])
        );
              }

      }
      
    }
   
}


if (!empty($array_insert)) {
  $db->insertMulti('kelulusan',$array_insert);
}

    unlink("../../../upload/kelulusan/".$_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 kelulusan baru 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 "in":
  
  
  
  $data = array(
    "nim"=>$_POST["nim"],
    "nama"=>$_POST["nama"],
    "tanggal_keluar" => $_POST["tanggal_keluar"],
    "kode_jurusan" => $_POST["jurusan"],
    "id_jenis_keluar"=>strtoupper($_POST["id_jenis_keluar"]),
    "sk_yudisium"=>$_POST["sk_yudisium"],
    "tgl_sk_yudisium"=>$_POST["tgl_sk_yudisium"],
    "ipk"=>$_POST["ipk"],
    "semester"=>$_POST["semester"],
    'keterangan_kelulusan' => $_POST['keterangan_kelulusan']
    );
  
  
  
   
    $in = $db->insert("kelulusan",$data);
    
    if ($in=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  case "delete":
    
    
    
    $db->delete("kelulusan","id",$_GET["id"]);
    break;

        case 'del_massal':
          $data_ids = $_REQUEST['data_ids'];
       if(trim($data_ids)!="") {
        $delete =  $db->fetch_custom("delete from kelulusan where id in ($data_ids)");
    }
    break;

  case "up":
  $data = array(
    "nim"=>$_POST["nim"],
    "nama"=>$_POST["nama"],
    "tanggal_keluar" => $_POST["tanggal_keluar"],
    "kode_jurusan" => $_POST["jurusan"],
    "id_jenis_keluar"=>strtoupper($_POST["id_jenis_keluar"]),
    "sk_yudisium"=>$_POST["sk_yudisium"],
    "tgl_sk_yudisium"=>$_POST["tgl_sk_yudisium"],
    "ipk"=>$_POST["ipk"],
    "semester"=>$_POST["semester"],
    'keterangan_kelulusan' => $_POST['keterangan_kelulusan']
    );
  

    
    $up = $db->update("kelulusan",$data,"id",$_POST["id"]);
    if ($up=true) {
      echo "good";
    } else {
      return false; 
    }
    break;
  default:
    # code...
    break;
}

?>