<?php
session_start();
$time_start = microtime(true); 
include "../../inc/config.php";
session_check();
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',
                    'D1',
                    'E1',
                    'F1',
                    'G1',
                    'H1',
                    'I1',
                    'J1',
                    'K1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
            ),
                        array(
                'fill' => array(
                    'color' => '00ff00'
                    ),
                'cells' => array(
                    'B1'
                    ),
                'border' => array(
                    'style' => 'thin',
                    'color' => '000000'
                    ),
                'verticalAlign' => 'center',
                'horizontalAlign' => 'center',
                )
            );



//column width
$col_width = array(
  1 => 19,
  2 => 27,
  3 => 15,
  4 => 15,
  5 => 15,
  6 => 15,
  7 => 15,
  8 => 15,
  9 => 20,
  10 => 20,
  11 => 20
  );
$writer->setColWidth($col_width);
$header = array(
  'NIM'=>'string',
  'NAMA'=>'string',
  'Semester'=>'string',
  'SKS Semester'=>'string',
  'IP Semester'=>'string',
  'SKS Kumulatif'=>'string',
  'IP Kumulatif'=>'string',
  'Status'=>'string',
  'Kode Prodi'=>'string',
  'Biaya Kuliah'=>'string',
  'Jenis Pembiayaan' => 'string'
);
$data_rec = array();

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

        foreach ($data_json as $key) {
                      $data_rec[] = array(
                          $key->nim,
                          $key->nama,
                          $key->semester,
                          $key->sks_smt,
                          $key->ips,
                          $key->sks_total,
                          $key->ipk,
                          $key->status_kuliah,
                          $key->kode_jurusan,
                          $key->biaya_smt,
                          $key->id_pembiayaan,
                        );
        }

//jalur pendaftaran
$status_mhs = $db->fetch_custom("select * from status_mahasiswa_akm");
foreach ($status_mhs as $status) {
  $data_status[] = $status->id_status." : ".$status->nama_status;
}
//pembiayaan
$pembiayaan_mhs = $db->fetch_custom("select * from pembiayaan");
foreach ($pembiayaan_mhs as $pembiayaan_mhs) {
  $data_pembiayaan[] = $pembiayaan_mhs->id_pembiayaan." : ".$pembiayaan_mhs->nm_pembiayaan;
}
$comments = array(
'A1' => 'Nomor Induk Mahasiswa

Wajib Diisi',
'B1' => 'Nama Mahasiswa

Boleh Kosong',
'C1' => 'Periode Semester Pelaporan
format : tahun+semester (1 : Ganjil, 2 : Genap, 3 : Pendek)
contoh : 20231 (berarti 2023 ganjil)

Wajib disi',
'D1' => 'Jumlah SKS Semester

WAJIB DIISI',
'E1' => 'IP Semester

WAJIB DIISI',
'F1' => 'Jumlah SKS Kumulatif

WAJIB DIISI',
'G1' => 'IP Kumulatif

WAJIB DIISI',
'H1' => 'Status Mahasiswa
'.implode("\n", $data_status).'

WAJIB DIISI',
'I1' => 'Kode Prodi/Jurusan, 
Lihat di menu master data -> jurusan

Wajib diisi',
'J1' => 'Biaya kuliah Semester, isi tanpa koma atau titik. 
Contoh : 2500000

Wajib diisi',
'K1' => 'Jenis Pembiayaan
'.implode("\n", $data_pembiayaan).'

Wajib diisi',
);
        $writer->setComment($comments);
        $filename = 'template_akm.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,'AKM', $header, $style);
        $writer->writeToStdOut();
        exit(0);
      break;
    case 'delete_error':
    $db->fetch_custom("delete from nilai_akm 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['valid']=='all') {
        $valid = "";
    } else {
        $valid = "and valid='".$_POST['valid']."'";
    }

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

      if ($status=="" && $jurusan=="" && $semester=="" && $valid=="") {
      if ($_SESSION['level']==1) {
         $db->fetch_custom("TRUNCATE `nilai_akm`");
       } else {
          $db->fetch_custom("delete from nilai_akm where kode_jurusan='".$_SESSION['jurusan']."'");
       }

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

    break;


  case 'import':

 // $reader = ReaderFactory::create(Type::XLSX); // for XLSX files
  //$reader = ReaderFactory::create(Type::CSV); // for CSV files
  //$reader = ReaderFactory::create(Type::ODS); // for ODS files



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


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


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

  $Reader = new SimpleXLSX("../../../upload/akm/".$_FILES['semester']['name']);
  foreach( $Reader->rows() as $key => $val ) {
      if ($key>0) {
         if ($val[0]!='') {
        $check = $db->check_exist('nilai_akm',
          array(
            'nim'=> $db->trimmer($val[0]),
            'semester'=> $db->trimmer($val[2]),
            'kode_jurusan' => $db->trimmer($val[8])
          ));
        if ($check==true) {
          $error_count++;
          $error[] = $val[0]." Sudah Ada";
        } else {
          $sukses++;
          $sks_smt = ($db->trimmer($val[3])=="") ? "0":$db->trimmer($val[3]);
          $ips = ($db->trimmer($val[4])=="") ? "0":$db->trimmer($val[4]);
          $ips = str_replace(",", ".", $ips);
          $sks_total = ($db->trimmer($val[5])=="") ? "0":$db->trimmer($val[5]);
          $ipk = ($db->trimmer($val[6])=="") ? "0":$db->trimmer($val[6]);
          $ipk = str_replace(",", ".", $ipk);
          $insert_akm[] = array(
            'nim' => $db->trimmer($val[0]),
            'nama' => $db->trimmer($val[1]),
            'semester' => $db->trimmer($val[2]),
            'sks_smt' => $sks_smt,
            'ips' => $ips,
            'sks_total' => $sks_total,
            'ipk' => $ipk,
            'status_kuliah' => $db->trimmer($val[7]),
            'kode_jurusan' => $db->trimmer($val[8]),
            'biaya_smt' => $db->trimmer($val[9]),
            'id_pembiayaan' => $db->trimmer($val[10])
          );

        }
      }

    }

}

if (!empty($insert_akm)) {
  $db->insertMulti('nilai_akm',$insert_akm);
  echo $db->getErrorMessage();
}
//unlink("../../../upload/akm/".$_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 Akm 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 'del_massal':
        $data_ids = $_REQUEST['data_ids'];
       if(trim($data_ids)!="") {
        $delete =  $db->fetch_custom("delete from nilai_akm where id in ($data_ids)");
    }
    break;
  case "in":

  $data = array(
    "semester"=>$_POST["semester"],
    "nim"=>$_POST["nim"],
    "nama"=>$_POST["nama"],
    "ips"=>$_POST["ips"],
    "ipk"=>$_POST["ipk"],
    "sks_smt"=>$_POST["sks_smt"],
    "sks_total"=>$_POST["sks_total"],
    "kode_jurusan"=>$_POST["kode_jurusan"],
    "status_kuliah"=>$_POST["status"],
    "biaya_smt" => $_POST["biaya_smt"],
    "id_pembiayaan" => $_POST["id_pembiayaan"]
    
  );



   $in = $db->insert("nilai_akm",$data);

    if ($in=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  case "delete":



    $db->delete("nilai_akm","id",$_GET["id"]);
    break;
  case "up":
 


   $data = array(
    "semester"=>$_POST["semester"],
    "nim"=>$_POST["nim"],
    "nama"=>$_POST["nama"],
    "ips"=>$_POST["ips"],
    "ipk"=>$_POST["ipk"],
    "sks_smt"=>$_POST["sks_smt"],
    "sks_total"=>$_POST["sks_total"],
    "kode_jurusan"=>$_POST["kode_jurusan"],
    "status_kuliah"=>$_POST["status"],
    "biaya_smt" => $_POST["biaya_smt"],
    "id_pembiayaan" => $_POST["id_pembiayaan"]
  );
   
  $rule_akm = $db->fetch_single_row('rule_akm','id',1);

  $up = $db->update("nilai_akm",$data,"id",$_POST["id"]);

  $sks = $rule_akm->sks;
  if ($rule_akm->kecuali!="") { 
      $not_in = explode(",", $rule_akm->kecuali);
  } else {
    $not_in = array();
  }

  $error_count = 0;
  $sukses_count = 0;

      $valid = $db->fetch_custom_single("select * from nilai_akm where id=?",array('id' => $_POST['id']));
        //print_r($valid);
        //semester > 30
        if ((!in_array($valid->kode_jurusan, $not_in) && $valid->sks_smt>24) || $valid->ips>4 || $valid->ipk>4 || ($valid->ipk==0 and $valid->ips>0) || (($valid->sks_smt==0 or $valid->sks_total==0) and $valid->ips>0)) {

          if (!in_array($valid->kode_jurusan, $not_in) && $valid->sks_smt>24) {
            $ket_valids_smt = "SKS per semester > 24 sks";
        $update_data_valid_30[] = "WHEN $valid->id THEN '2'";
        $update_data_sks_sem_30[]= "WHEN $valid->id THEN '$ket_valids_smt'";
        $update_data_ket_valid_ips_30[]= "WHEN $valid->id THEN ''";
        $update_data_ket_valid_ipk_30[]= "WHEN $valid->id THEN ''";
        $update_data_ket_valid_ips_ipk_30[]= "WHEN $valid->id THEN ''";
        $update_data_ket_krs_ada_30[]= "WHEN $valid->id THEN ''";
        $data_id_error_30[] = $valid->id;
        $error_count++;
          }

          if ($valid->ips>4) {
              //check if ips is valid
          $ket_valid_ips = "Nilai IPS > 4.00";
          $update_data_valid_ips_fours[] = "WHEN $valid->id THEN '2'";
      $update_data_ket_ips_fours[]= "WHEN $valid->id THEN '$ket_valid_ips'";
      $data_id_error_ipss[] = $valid->id;
      $error_count++;

      }

      if ($valid->ipk>4) {
              //check if ipk is valid
          $ket_valid_ipk = "Nilai IPK > 4.00";
          $update_data_valid_ipk_fours[] = "WHEN $valid->id THEN '2'";
      $update_data_sks_sem_four_ipks[]= "WHEN $valid->id THEN '$ket_valid_ipk'";
      $data_id_error_ipks[] = $valid->id;
      $error_count++;
          }

          if ($valid->ipk==0 and $valid->ips>0) {
              //check if ipk=0 but ips > 0
          $ket_valid_ips_ipk = "NIlai IPK = 0 , tapi IPS > 0";
          $update_data_valid_ipk_ipss[] = "WHEN $valid->id THEN '2'";
          $update_data_valid_ket_ipk_ipss[] = "WHEN $valid->id THEN '$ket_valid_ips_ipk'";
      $data_id_error_ipk_ipss[] = $valid->id;
      $error_count++;
          }

          if (($valid->sks_smt==0 or $valid->sks_total==0) and $valid->ips>0) {
             //check if SKS semester = 0 , tapi KRS atau ips > 0
          $ket_krsnya = "SKS semester = 0 , tapi KRS atau ips > 0";
            $update_data_valid_krss[] = "WHEN $valid->id THEN '2'";
          $update_data_valid_ket_krss[] = "WHEN $valid->id THEN '$ket_krsnya'";
      $data_id_error_skss[] = $valid->id;
      $error_count++;

      }

        } else {
          //otherwise set valid to 1
            $update_data_valid_good[] = "WHEN $valid->id THEN '1'";
      $data_id_sukses[] = $valid->id;
            $sukses_count++;
        }
        

        $all_id[] = $valid->id;
      
    
        if (!empty($data_id_error_30)) {
          $update_data_valid = implode(" ", $update_data_valid_30);
        $update_data_sks_sem = implode(" ", $update_data_sks_sem_30);
        $update_data_ket_valid_ips = implode(" ", $update_data_ket_valid_ips_30);
        $update_data_ket_valid_ipk = implode(" ", $update_data_ket_valid_ipk_30);
        $update_data_ket_valid_ips_ipk = implode(" ", $update_data_ket_valid_ips_ipk_30);
        $update_data_ket_krs_ada = implode(" ", $update_data_ket_krs_ada_30);
        $data_id_error = implode(",", $data_id_error_30);

          $bulk_update_semester_30 = "UPDATE nilai_akm 
      SET valid = (CASE id 
        $update_data_valid
        END),ket_sks_sem = (CASE id 
        $update_data_sks_sem
        END),ket_valid_ips = (CASE id 
        $update_data_ket_valid_ips
        END),ket_valid_ipk = (CASE id 
        $update_data_ket_valid_ipk
        END),ket_valid_ips_ipk = (CASE id 
        $update_data_ket_valid_ips_ipk
        END),ket_krs_ada = (CASE id 
        $update_data_ket_krs_ada
        END)
      WHERE id IN($data_id_error);";

        
      $db->fetch_custom($bulk_update_semester_30);
        }

        if (!empty($data_id_error_ipss)) {

            $update_data_valid_ips_four = implode(" ", $update_data_valid_ips_fours); 
          $update_data_ket_ips_four = implode(" ", $update_data_ket_ips_fours);
          $data_id_error_ips = implode(",", $data_id_error_ipss);
        $bulk_update_semester_ips = "UPDATE nilai_akm 
        SET valid = (CASE id 
          $update_data_valid_ips_four
          END),ket_valid_ips = (CASE id 
          $update_data_ket_ips_four
          END)
        WHERE id IN($data_id_error_ips);
        ";
        $db->fetch_custom($bulk_update_semester_ips);
        }

        if (!empty($data_id_error_ipks)) {

          $update_data_valid_ipk_four = implode(" ", $update_data_valid_ipk_fours); 
          $update_data_sks_sem_four_ipk = implode(" ", $update_data_sks_sem_four_ipks);
          $data_id_error_ipk = implode(",", $data_id_error_ipks);
        $bulk_update_semester_ipk = "UPDATE nilai_akm 
        SET valid = (CASE id 
          $update_data_valid_ipk_four
          END),ket_valid_ipk = (CASE id 
          $update_data_sks_sem_four_ipk
          END)
        WHERE id IN($data_id_error_ipk);
        ";
        $db->fetch_custom($bulk_update_semester_ipk);
        }

        if (!empty($data_id_error_ipk_ipss)) {
        $update_data_valid_ipk_ips = implode(" ", $update_data_valid_ipk_ipss); 
          $update_data_valid_ket_ipk_ips = implode("", $update_data_valid_ket_ipk_ipss);
          $data_id_error_ipk_ips = implode(",", $data_id_error_ipk_ipss);
        $bulk_update_semester_ipk_ips = "UPDATE nilai_akm 
        SET valid = (CASE id 
          $update_data_valid_ipk_ips
          END),ket_valid_ips_ipk = (CASE id 
          $update_data_valid_ket_ipk_ips
          END)
        WHERE id IN($data_id_error_ipk_ips);
        ";
        $db->fetch_custom($bulk_update_semester_ipk_ips);
        }

        if (!empty($data_id_error_skss)) {

        $update_data_valid_krs = implode(" ", $update_data_valid_krss); 
          $update_data_valid_ket_krs = implode(" ", $update_data_valid_ket_krss);
          $data_id_error_sks = implode(",", $data_id_error_skss);
        $bulk_update_semester_krs = "UPDATE nilai_akm 
        SET valid = (CASE id 
          $update_data_valid_krs
          END),ket_krs_ada = (CASE id 
          $update_data_valid_ket_krs
          END)
        WHERE id IN($data_id_error_sks);
        ";
        $db->fetch_custom($bulk_update_semester_krs);
        }


        if (!empty($data_id_sukses)) {

          $update_data_valid_goodd = implode(" ", $update_data_valid_good); 
        $data_id_sukse = implode(",", $data_id_sukses);
        $bulk_update_sukses = "UPDATE nilai_akm 
        SET valid = (CASE id 
          $update_data_valid_goodd
          END)
        WHERE id IN($data_id_sukse);
        ";
        //echo $bulk_update_sukses;
        $db->fetch_custom($bulk_update_sukses);
        }
    
     
    if ($up=true) {
      echo "good";
    } else {
      return false;
    }
    break;
  default:
    # code...
    break;
}

?>
