<?php
session_start();
$time_start = microtime(true); 
include "../../inc/config.php";
require('../../lib/simplexlsx.class.php');
session_check();

$error = array();

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',
                    'B1',
                    'C1',
                    'D1',
                    'E1',
                    'F1',
                    'G1',
                    'K1',
                    'L1',
                    'M1',
                    'N1',
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                      'H1',
                      'I1',
                      'J1',
                      'O1',
                      'P1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );

//column width
$col_width = array(
  1 => 15,
  2 => 20,
  3 => 10,
  4 => 15,
  5 => 15,
  6 => 18,
  7 => 13,
  8 => 25,
  9 => 15,
  10 => 15,
  11 => 10,
  12 => 20,
  13 => 11,
  14 => 13,
  15 => 20,
  16 => 15
  );
       $writer->setColWidth($col_width);
$header = array(
  'Kode MK'=>'string',
  'Nama MK'=>'string',
  'Jenis MK'=>'string',
  'SKS Tatap Muka'=>'string',
  'SKS Praktek' => 'string',
  'SKS Praktek Lapangan' => 'string',
  'SKS Simulasi' => 'string',
  'Metode Pembelajaran' => 'string',
  'Tgl Mulai Efektif' => 'string',
  'Tgl Akhir Efektif' => 'string',
  'Semester' => 'string',
  'Tahun Kurikulum' => 'string',
  'Wajib' => 'string',
  'Kode Prodi' => 'string',
  'Kelompok Matakuliah' => 'string',
  'Id Matkul' => 'string'
);
$data_rec = array();

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

        foreach ($data_json as $key) {
          
                        $data_rec[] = array(
                                      $key->kode_mk,
                                      $key->nama_mk,
                                      $key->jns_mk,
                                      $key->sks_tm,
                                      (string)$key->sks_prak,
                                      (string)$key->sks_prak_lap,
                                      (string)$key->sks_sim,
                                      $key->metode_pelaksanaan_kuliah,
                                      $key->tgl_mulai_efektif,
                                      $key->tgl_akhir_efektif,
                                      $key->semester,
                                      $key->tahun,
                                      $key->wajib,
                                      $key->kode_jurusan,
                                      $key->kelompok_mk,
                                      $key->id_matkul
                        );
        }



$data_jenis_mk = $db->fetch_custom("select * from jenis_mk");
foreach ($data_jenis_mk as $hasil) {
  $jenis_mk[] = $hasil->id_jns_mk." : ".$hasil->nm_jns_mk;
}
$data_kelompok_mk = $db->fetch_custom("select * from kelompok_mk");
foreach ($data_kelompok_mk as $hasil) {
  $kelompok_mk[] = $hasil->id_kel_mk." : ".$hasil->nm_kel_mk;
}
$comments = array(
'A1' => 'Kode Mata Kuliah

info : wajib disi',
'B1' => 'Nama Mata Kuliah
info : wajib disi',
'B1' => 'Nama Mata Kuliah
info : wajib disi',
'C1' => 'Jenis Mata Kuliah. Isi sesuai id dibawah
'.implode("\n", $jenis_mk).'

Info : Wajib Diisi
',
'D1' => 'sks_tm : Jumlah Sks Tatap Muka
Isi 0 : jika tidak ada
info : wajib disi',
'E1' => 'Jumlah Sks Praktek.
Isi 0 : jika tidak ada
info : wajib disi',
'F1' => 'Jumlah SKS Prak Lapaangan.
Isi 0 : jika tidak ada
info : wajib disi',
'G1' => 'Jumlah SKS Simulasi.
Isi 0 : jika tidak ada
info : wajib disi',
'H1' => 'Metode Pembelajaran
info : Boleh kosong jika belum ada',
'I1' => 'ket : tanggal Mulai efektif matakuliah
format : tahun-bulan-tgl
contoh : 2017-07-01
info : Boleh kosong jika belum ada',
'J1' => 'ket : tanggal Akhir efektif matakuliah
format : tahun-bulan-tgl
contoh : 2017-07-01
info : Boleh kosong jika belum ada',
'K1' => 'Semester Matakuliah 1 sampai 8
info : Wajib diisi',
'L1' => 'Mulai Berlaku Kurikulum
format : tahunsemester
contoh : 20201',
'M1' => 'Apakah Matkul Wajib
1 : YA
0 : TIDAK',
'N1' => 'Kode Prodi/Jurusan, 
Lihat di menu master data -> jurusan
Wajib diisi',
'O1' => 'Kelompok Matakuliah. Isi sesuai id dibawah
'.implode("\n", $kelompok_mk).'

Info : Wajib Diisi
',
'P1' => 'Hanya untuk update Matakuliah, Bisa didapat dari download matkul di menu tool -> download data
Info : Boleh Kosong');

        $writer->setComment($comments);
        $filename = 'template_baru_matakuliah.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,'Matkul', $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'";
    }

     $data_mhs_pt = $db->fetch_custom("delete from mat_kurikulum where id!='' $status and id_kurikulum='".$_POST['id']."'");
 
    break;

    case 'del_massal':
    $data_ids = $_REQUEST['data_ids'];
    $data_id_array = explode(",", $data_ids);
    if(!empty($data_id_array)) {
        foreach($data_id_array as $id) {
          $db->delete("mat_kurikulum","id",$id);
         }
    }
    break;
  case 'import':
     if (!is_dir("../../../upload/matakuliah")) {
              mkdir("../../../upload/matakuliah");
            }


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

            }

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

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

      if ($val[0]!='') {
          $check = $db->check_exist('mat_kurikulum',array(
            'kode_mk' =>$db->trimmer($val[0]),
            'id_kurikulum'=>$_POST['id_kur'],
            'kode_jurusan' => $db->trimmer($val[11])
          ));
          if ($check==true) {
            $error_count++;
            $error[] = $val[0]." Sudah Ada";
          } else {
            $sukses++;
            $values[] = array(
              'id_kurikulum' => $_POST['id_kur'],
              'tahun' => $_POST['tahun'],
              'kode_mk' => $db->trimmer($val[0]),
              'nama_mk' => $db->trimmer($val[1]),
              'jns_mk' => $db->trimmer($val[2]),
              'sks_tm' => $db->trimmer($val[3]),
              'sks_prak' => $db->trimmer($val[4]),
              'sks_prak_lap' => $db->trimmer($val[5]),
              'sks_sim' => $db->trimmer($val[6]),
              'metode_pelaksanaan_kuliah' => $db->trimmer($val[7]),
              'tgl_mulai_efektif' => $db->trimmer($val[8]),
              'tgl_akhir_efektif' => $db->trimmer($val[9]),
              'semester' => $db->trimmer($val[10]),
              'kode_jurusan' => $db->trimmer($val[11])
            );
        }

      }

    }

      }

if (!empty($values)) {
  $db->insert_massal('mat_kurikulum',$values);
}

unlink("../../../upload/matakuliah/".$_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 Matakuliah 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 'delete_all':
    $db->fetch_custom("delete from mat_kurikulum where kode_jurusan='".$_POST['id']."' and tahun='".$_POST['tahun']."'");
    break;

  case "in":
  
  
  
  $data = array(
    "id_kurikulum"=>$_POST["id_kurikulum"],
    "kode_mk"=>$_POST["kode_mk"],
    "tahun"=>$_POST["tahun"],
    "nama_mk"=>$_POST["nama_mk"],
    "jns_mk"=>$_POST["jns_mk"],
    "sks_tm"=>$_POST["sks_tm"],
    "sks_prak"=>$_POST["sks_prak"],
    "sks_prak_lap"=>$_POST["sks_prak_lap"],
    "sks_sim"=>$_POST["sks_sim"],
    'metode_pelaksanaan_kuliah' => $_POST["metode_pelaksanaan_kuliah"],
    'tgl_mulai_efektif' => $_POST["tgl_mulai_efektif"],
    'tgl_akhir_efektif' => $_POST["tgl_akhir_efektif"],
    "semester"=>$_POST["semester"],
    "kode_jurusan"=>$_POST["kode_jurusan"]
  );
  
  
  
   
    $in = $db->insert("mat_kurikulum",$data);
    
    if ($in=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  case "delete":
    
    
    
    $db->delete("mat_kurikulum","id",$_GET["id"]);
    break;

  default:
    # code...
    break;
}

?>