<?php
session_start();
$time_start = microtime(true); 
include "../../inc/config.php";
session_check();
require('../../lib/simplexlsx.class.php');
switch ($_GET["act"]) {
   case 'download_sample':
      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',
                    'D1',
                    'E1',
                    'F1',
                    'I1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '91d24a'
                    ),
                'cells' => array(
                    'B1',
                    'G1',
                    'H1',
                    'J1',
                    'K1'
                    ),
                #2f74b5
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                ),

              array(
                'fill' => array(
                    'color' => '2f74b5'
                    ),
                'cells' => array(
                    'AW1',
                    'AX1',
                    'AY1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center'
                )
            );

//column width
$col_width = array(
  1 => 20,
  2 => 28,
  3 => 15,
  4 => 16,
  5 => 31,
  6 => 9,
  7 => 21,
  8 => 11,
  9 => 13,
  10 => 41,
  11 => 41
  );

$writer->setColWidth($col_width);

$header = array(
            'NIM' =>'string',
             'NAMA' =>'string',
             'Jenis Prestasi' =>'string',
             'Tingkat Prestasi' =>'string',
             'Nama Prestasi' =>'string',
             'Tahun' =>'string',
             'Penyelenggara' =>'string',
             'Peringkat' =>'string',
             'Kode Prodi' => 'string',
             'ID Aktivitas' => 'string',
             'ID Prestasi Neo' => 'string'
);

$writer->setColWidth($col_width);

$no=1;
$index = 2;

$data_rec = array();

$key_json = json_decode(file_get_contents('sample.json'));
foreach ($key_json as $key) {
  $data_rec[] = array(
    $key->nipd,
    $key->nama,
    $key->id_jns_prestasi,
    $key->id_tkt_prestasi,
    $key->nm_prestasi,
    $key->thn_prestasi,
    $key->penyelenggara,
    $key->peringkat,
    $key->kode_jurusan,
    $key->id_aktivitas,
    $key->id_prestasi
);
}



        $filename = 'sample_prestasi_template.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');



//jalur pendaftaran
$data_jenis_prestasi = $db->fetch_custom("select * from jenis_prestasi");
foreach ($data_jenis_prestasi as $jns_prestasi) {
  $jenis_prestasi[] = $jns_prestasi->id_jns_prestasi." : ".$jns_prestasi->nm_jns_prestasi;
}
//jenis pendaftaran
$data_tingkat_prestasi = $db->fetch_custom("select * from tingkat_prestasi");
foreach ($data_tingkat_prestasi as $level_prestasi) {
  $tingkat_prestasi[] = $level_prestasi->id_tkt_prestasi." : ".$level_prestasi->nm_tkt_prestasi;
}

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

info : wajib disi',
'B1' => 'Nama Mahasiswa

Info : Boleh dikosongkan',
'C1' => 'Jenis Prestasi
Ket : Jenis Prestasi
id Nama Jenis
'.implode("\n", $jenis_prestasi).'

Info : Wajib Diisi, isi sesuai id diatas',
'D1' => 'Ket:  Tingkat Prestasi
id nama tingkat
'.implode("\n", $tingkat_prestasi).'
Info : Wajib Diisi',
'E1' => '
Nama prestasi Maximal 160 Karakter
Info : Wajib Diisi',
'F1' => '
Isi dengan 4 digit angka tahun
Info : Wajib Diisi',
'G1' => '
Info : Boleh Kosong',
'H1' => 'Ket:  Peringkat
Isi dengan angka, contoh 1,2,3

Info : Boleh Kosong',
'I1' => '
Info : Wajib Diisi
',
'J1' => 'Id Aktivitas,
anda bisa download dulu data aktivitas di menu tool, download aktivitas, 
pilih jenis yang pertukaran pelajar, 
anda bisa menggunakan isian Id Aktivitas (yang di buat diimporter) atau ID Aktivitas NEO (berbentuk md4 dari hasil download, lihat kolom id)',
'K1' => 'Silakan isi jika and ingin update Prestasi. Data ID Bisa didapat dari download Prestasi di Menu Tool -> Data Downloader -> Download Prestasi
Info : Boleh Kosong'
);

$writer->setComment($comments);
        $writer->writeSheet($data_rec,'Sample Template', $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 `prestasi_mahasiswa`");
       } else {
          $db->fetch_custom("delete from prestasi_mahasiswa where kode_jurusan='".$_SESSION['jurusan']."'");
       }
    } else {
      $ids = $db->fetch_custom("select id from prestasi_mahasiswa 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 prestasi_mahasiswa where id!='' $semester $jurusan $status and id in ($data_id)");
    }

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

    if (!is_dir("../../../upload/prestasi")) {
              mkdir("../../../upload/prestasi");
            }


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

            }


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

  $Reader = new SimpleXLSX("../../../upload/prestasi/".$_FILES['semester']['name']);
  foreach( $Reader->rows() as $key => $val ) {
      if ($key>0) {
           if ($val[0]!="") {

        $check = $db->check_exist('prestasi_mahasiswa',array(
          'nipd'=> $db->trimmer($val[0]),
          'kode_jurusan' => $db->trimmer($val[8]),
          'penyelenggara' => $db->trimmer($val[6]),
          'peringkat' => $db->trimmer($val[7]),
          'thn_prestasi' => $db->trimmer($val[5])
        ));
        if ($check==true) {
          $error_count++;
          $error[] = $val[0]." Sudah Ada";
        } else {
          $sukses++;
            $array_insert[] = array(
              'nipd' => $db->trimmer($val[0]),
              'nama' => $db->trimmer($val[1]),
              'id_jns_prestasi' => $db->trimmer($val[2]),
              'id_tkt_prestasi' => $db->trimmer($val[3]),
              'nm_prestasi' => $db->trimmer($val[4]),
              'thn_prestasi' => $db->trimmer($val[5]),
              'penyelenggara' => $db->trimmer($val[6]),
              'peringkat' => $db->trimmer($val[7]),
              'kode_jurusan' => $db->trimmer($val[8]),
              'id_aktivitas' => $db->trimmer($val[9]),
              'id_prestasi' => $db->trimmer($val[10]),
            );
        }
      }

    }

}

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




    unlink("../../../upload/prestasi/".$_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 Prestasi 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("nipd"=>$_POST["nipd"],"nama"=>$_POST["nama"],"id_jns_prestasi"=>$_POST["id_jns_prestasi"],"id_tkt_prestasi"=>$_POST["id_tkt_prestasi"],"nm_prestasi"=>$_POST["nm_prestasi"],"thn_prestasi"=>$_POST["thn_prestasi"],"penyelenggara"=>$_POST["penyelenggara"],"peringkat"=>$_POST["peringkat"],"kode_jurusan"=>$_POST["kode_jurusan"],);
  
  
  
   
    $in = $db->insert("prestasi_mahasiswa",$data);
    
    if ($in=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  case "delete":
    
    
    
    $db->delete("prestasi_mahasiswa","id",$_GET["id"]);
    break;
  case "up":
   $data = array("nipd"=>$_POST["nipd"],"nama"=>$_POST["nama"],"id_jns_prestasi"=>$_POST["id_jns_prestasi"],"id_tkt_prestasi"=>$_POST["id_tkt_prestasi"],"nm_prestasi"=>$_POST["nm_prestasi"],"thn_prestasi"=>$_POST["thn_prestasi"],"penyelenggara"=>$_POST["penyelenggara"],"peringkat"=>$_POST["peringkat"],"kode_jurusan"=>$_POST["kode_jurusan"],'id_aktivitas' => $_POST["id_aktivitas"]);
   
   
   

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

?>