<?php
require "../../config/koneksi.php";
require "../../vendor/autoload.php";
require("../../config/function.php");
cek_session_admin();
$file_mimes = array('application/vnd.ms-excel', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
if (isset($_FILES['file']['name'])) {
    $ext = ['xls', 'xlsx'];
    $arr_file = explode('.', $_FILES['file']['name']);
    $extension = end($arr_file);
    if (in_array($extension, $ext)) {
        if ('xls' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $reader->load($_FILES['file']['tmp_name']);

        $sheetData = $spreadsheet->getActiveSheet()->toArray();
        $sukses = $gagal = 0;
        $exec = mysqli_query($koneksi, "TRUNCATE siswa");
		$exec = mysqli_query($koneksi, "TRUNCATE kelas");
		$exec = mysqli_query($koneksi, "TRUNCATE level");
		$exec = mysqli_query($koneksi, "TRUNCATE pk");
		$exec = mysqli_query($koneksi, "TRUNCATE ruang");
		$exec = mysqli_query($koneksi, "TRUNCATE sesi");
		$exec = mysqli_query($koneksi, "TRUNCATE server");
		  function acakangkahuruf($panjang)
  {
    $karakter= '0123456789';
    $string = '';
    for ($i = 0; $i < $panjang; $i++) {
    $pos = rand(0, strlen($karakter)-1);
    $string .= $karakter{$pos};
    }
    return $string;
  }
        for ($i = 5; $i < count($sheetData); $i++) {
            $id_siswa = $sheetData[$i]['0'];
            $nis = $sheetData[$i]['1'];
            $nisn = $sheetData[$i]['2'];
            $nama = $sheetData[$i]['3'];
            $nama = addslashes($nama);
            $level = $sheetData[$i]['4'];
			$kelas = $sheetData[$i]['5'];
            $pk = $sheetData[$i]['6'];
            if ($pk == "") {
                $pk = "semua";
            }
			 if ($id_siswa <= 9) {
                $nom = "0".$id_siswa;
            }else{
				 $nom = $id_siswa;
			}
			
            $ruang = $sheetData[$i]['7'];
            $sesi = $sheetData[$i]['8'];
            $server = $sheetData[$i]['9'];
            $agama = $sheetData[$i]['10'];
			$jk = $sheetData[$i]['11'];
            $nowa = $sheetData[$i]['12'];
			$foto = $sheetData[$i]['13'];
			 $nopes =  "US-".date('Y')." ".date('md')." ".$nom;
            $username =  "US".acakangkahuruf(7);
			if($setting['jenjang']=='SD' OR $setting['jenjang']=='PAKET-A'){
            $password =  "D-01".acakangkahuruf(4);
			}elseif($setting['jenjang']=='SMP' OR $setting['jenjang']=='PAKET-B'){
		    $password =  "P-01".acakangkahuruf(4);
			}elseif($setting['jenjang']=='SMA' OR $setting['jenjang']=='PAKET-C'){
		    $password =  "A-01".acakangkahuruf(4);
			}elseif($setting['jenjang']=='SMK'){
		    $password =  "K-01".acakangkahuruf(4);
			}
			if($level=="1" OR $level=="2"){
				$fase="A";
			}elseif($level=="3" OR $level=="4"){
				$fase="B";
			}elseif($level=="5" OR $level=="6"){
				$fase="C";
			}elseif($level=="7" OR $level=="8" OR $level=="9"){
				$fase="D";
			}elseif($level=="10"){
				$fase="E";
			}elseif($level=="11" OR $level=="12"){
				$fase="F";
			}
            if ($setting['jenjang'] == 'SMA' OR $setting['jenjang'] == 'SMK' OR $setting['jenjang']=='PAKET-C') {
                $qpk = mysqli_query($koneksi, "SELECT id_pk FROM pk WHERE id_pk='$pk'");
                $cekpk = mysqli_num_rows($qpk);
                if (!$cekpk <> 0) {
                    $exec = mysqli_query($koneksi, "INSERT INTO pk (id_pk,program_keahlian)VALUES('$pk','$pk')");
                }
            }
			 $qkelas = mysqli_query($koneksi, "SELECT id_kelas FROM kelas WHERE id_kelas='$kelas'");
            $cekkelas = mysqli_num_rows($qkelas);
            if (!$cekkelas <> 0) {
                $exec = mysqli_query($koneksi, "INSERT INTO kelas (id_kelas,level,nama,idpk)VALUES('$kelas','$level','$kelas','$pk')");
            }
            $qlevel = mysqli_query($koneksi, "SELECT kode_level FROM level WHERE kode_level='$level'");
            $ceklevel = mysqli_num_rows($qlevel);
            if (!$ceklevel <> 0) {
                $exec = mysqli_query($koneksi, "INSERT INTO level (kode_level,keterangan)VALUES('$level','$level')");
            }
            $qruang = mysqli_query($koneksi, "SELECT kode_ruang FROM ruang WHERE kode_ruang='$ruang'");
            $cekruang = mysqli_num_rows($qruang);
            if (!$cekruang <> 0) {
                $exec = mysqli_query($koneksi, "INSERT INTO ruang (kode_ruang,keterangan)VALUES('$ruang','$ruang')");
            }
            $qsesi = mysqli_query($koneksi, "SELECT kode_sesi FROM sesi WHERE kode_sesi='$sesi'");
            $ceksesi = mysqli_num_rows($qsesi);
            if (!$ceksesi <> 0) {
                $exec = mysqli_query($koneksi, "INSERT INTO sesi (kode_sesi,nama_sesi)VALUES('$sesi','$sesi')");
            }
            $qserver = mysqli_query($koneksi, "SELECT kode_server FROM server WHERE kode_server='$server'");
            $cekserver = mysqli_num_rows($qserver);
            if (!$cekserver <> 0) {
                $exec = mysqli_query($koneksi, "INSERT INTO server (kode_server,nama_server,status)VALUES('$server','$server','aktif')");
            }
            if ($nama <> '') {
                $exec = mysqli_query($koneksi, "INSERT INTO siswa (nis,nisn,level,id_kelas,fase,idpk,nama,jenis_kelamin,agama,no_peserta,ruang,sesi,server,username,password,hp,foto) 
				VALUES ('$nis','$nisn','$level','$kelas','$fase','$pk','$nama','$jk','$agama','$nopes','$ruang','$sesi','$server','$username','$password','$nowa','$foto')");
               
				($exec) ? $sukses++ : $gagal++;
				
            }
        }
        echo "Berhasil: $sukses | Gagal: $gagal ";
    } else {
        echo "Pilih file yang bertipe xlsx or xls";
    }
}
