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

                          ),
                      'border' => array(
                          'style' => 'thin',
                          'color' => '000000'
                          ),
                      'verticalAlign' => 'center',
                      'horizontalAlign' => 'center',
                      )
                  );

      //column width
      $col_width = array(
        1 => 15,
        2 => 23,
        3 => 15,
        4 => 23,
        5 => 10,
        6 => 15,
        7 => 22,
        8 => 25,
        9 => 19,
        10 => 19,
        11 => 19,
        12 => 20,
        13 => 25,
        14 => 38
        );
      $writer->setColWidth($col_width);


      $header = array(
        'NIM'=>'string',
        'NAMA'=>'string',
        'Kode Mk Asal'=>'string',
        'Nama Mk Asal'=>'string',
        'SKS Asal'=>'string',
        'Nilai Huruf Asal'=>'string',
        'Kode Matakuliah Diakui'=>'string',
        'Nama Matakuliah Diakui'=>'string',
        'Nilai Huruf Diakui' => 'string',
        'Nilai Indeks Diakui' => 'string',
        'Kode Prodi' => 'string',
        'Semester' => 'string',
        'Kode Asal Perguruan Tinggi' => 'string',
        'ID Aktivitas' => 'string'
      );

      $no=1;
      $index = 2;

      $data_rec = array();
              $datas = json_decode(file_get_contents('sample.json'));
              foreach ($datas as $key) {
                   $data_rec[] = array(
                                    $key->nim,
                                    $key->nama,
                                    $key->kode_mk_asal,
                                    $key->nm_mk_asal,
                                    $key->sks_asal,
                                    $key->nilai_huruf_asal,
                                    $key->kode_mk,
                                    $key->nama_mk,
                                    $key->nilai_huruf_diakui,
                                    $key->nilai_angka_diakui,
                                    $key->kode_jurusan,
                                    $key->semester,
                                    $key->kode_pt,
                                    $key->id_aktivitas
                      );
              }


      $comments = array(
      'A1' => 'NIM / NIPD Mahasiswa
      info : wajib disi',
      'C1' => '
      info : wajib disi',
      'D1' => '
      info : wajib disi',
      'E1' => '
      info : wajib disi',
      'F1' => '
      info : wajib disi',
      'G1' => '
      info : wajib disi',
      'H1' => '
      info : wajib disi',
      'I1' => '
      info : wajib disi',
      'J1' => '
      info : wajib disi',
      'K1' => '
      info : wajib disi',
      'L1' => 'Peride Masuk Kuliah/Angkatan
      tahun+semester
      1 : ganjil, 2 genap, 
      20221 berarti angakatan 2022 ganjil
      Info : Wajib Diisi',
      'M1' => 'info :
      Kode Asal Perguruan Tinggi , Bisa lihat referensi kode pt di menu Master Data, Data Prodi Kampus. 

      Boleh kosog, baiknya Diisi',
      'N1' => 'Id Aktivitas,
      anda bisa download dulu data aktivitas di menu tool, download aktivitas, 
      pilih jenis yang pertukaran pelajar, 
      anda bisa menggunakan isian Id Aktivitas atau ID Aktivitas NEO'
      );
      $writer->setComment($comments);
      $filename = 'template_nilai_transfer.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,'Sample', $header, $style);
      $writer->writeToStdOut();
      exit(0);
      break;
     case 'delete_error':
    $db->fetch_custom("delete from nilai_transfer 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 ($status=="" && $jurusan=="") {
        if ($_SESSION['level']==1) {
         $db->fetch_custom("TRUNCATE `nilai_transfer`");
       } else {
          $db->fetch_custom("delete from nilai_transfer where kode_jurusan='".$_SESSION['jurusan']."'");
       } 
    } else {

      $ids = $db->fetch_custom("select id from nilai_transfer 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 nilai_transfer where id!='' $jurusan $status and id in ($data_id)");
    }

           
    break;

  case "in":
  $data = array("nim"=>$_POST["nim"],
      "kode_mk_asal"=>$_POST["kode_mk_asal"],
      "nm_mk_asal"=>$_POST["nm_mk_asal"],
      "sks_asal"=>$_POST["sks_asal"],
      "nilai_huruf_asal"=>$_POST["nilai_huruf_asal"],
      "kode_mk"=>$_POST["kode_mk"],
      "nama_mk"=>$_POST["nama_mk"],
      "nilai_huruf_diakui"=>$_POST["nilai_huruf_diakui"],
      "nilai_angka_diakui"=>$_POST["nilai_angka_diakui"],
      "kode_jurusan"=>$_POST["kode_jurusan"],
      "semester"=> $_POST["semester"],
      'kode_pt' => $_POST["kode_pt"],
      'id_aktivitas' => $_POST["id_aktivitas"]
);
  
   
    $in = $db->insert("nilai_transfer",$data);
    
    if ($in=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  case 'import':
  if (!is_dir("../../../upload/nilai_transfer")) {
              mkdir("../../../upload/nilai_transfer");
            }


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

            }



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

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

    if ($val[6]!='') {
        $check = $db->check_exist('nilai_transfer',array(
          'nim'=> $db->trimmer($val[0]),
          'kode_mk' => $db->trimmer($val[6])
        ));
          if ($check==true) {
            $error_count++;
            $error[] = $val[0]." ".$val[6]." Sudah Ada";
          } else {
            $sukses++;
            $array_insert[] = array(
              'nim' => $db->trimmer($val[0]),
              'nama' => $db->trimmer($val[1]),
              'kode_mk_asal' => $db->trimmer($val[2]),
              'nm_mk_asal' => $db->trimmer($val[3]),
              'sks_asal' => $db->trimmer($val[4]),
              'nilai_huruf_asal' => $db->trimmer($val[5]),
              'kode_mk' => $db->trimmer($val[6]),
              'nama_mk' => $db->trimmer($val[7]),
              'nilai_huruf_diakui' => $db->trimmer($val[8]),
              'nilai_angka_diakui' => $db->trimmer($val[9]),
              'kode_jurusan' => $db->trimmer($val[10]),
              'semester' => $db->trimmer($val[11]),
              'kode_pt' => $db->trimmer($val[12]),
              'id_aktivitas' => $db->trimmer($val[13]),
            );
        }
    }

}
      
    }
   
}

if (!empty($array_insert)) {
  $db->insertMulti('nilai_transfer',$array_insert);
  echo $db->getErrorMessage();
}
    unlink("../../../upload/nilai_transfer/".$_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_transfer","id",$_GET["id"]);
    break;

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

  case "up":
  $data = array("nim"=>$_POST["nim"],
      "kode_mk_asal"=>$_POST["kode_mk_asal"],
      "nm_mk_asal"=>$_POST["nm_mk_asal"],
      "sks_asal"=>$_POST["sks_asal"],
      "nilai_huruf_asal"=>$_POST["nilai_huruf_asal"],
      "kode_mk"=>$_POST["kode_mk"],
      "nama_mk"=>$_POST["nama_mk"],
      "nilai_huruf_diakui"=>$_POST["nilai_huruf_diakui"],
      "nilai_angka_diakui"=>$_POST["nilai_angka_diakui"],
      "kode_jurusan"=>$_POST["kode_jurusan"],
      "semester"=>$_POST["semester"]
      ,
      'id_aktivitas' => $_POST["id_aktivitas"]
);
  if (isset($_POST['kode_pt'])) {
    $data['kode_pt'] = $_POST["kode_pt"];
  }
  
    
    $up = $db->update("nilai_transfer",$data,"id",$_POST["id"]);
    if ($up=true) {
      echo "good";
    } else {
      return false; 
    }
    break;
  default:
    # code...
    break;
}

?>