<?php
require("../../config/excel_reader.php");
require("../../config/database.php");
require("../../config/function.php");
require("../../config/functions.crud.php");
require "../../vendor/autoload.php";



session_start();
if (!isset($_SESSION['id_user'])) {
    die('Anda tidak diijinkan mengakses langsung');
}
if ($pg == 'ubah') {
    $data = [
        'nama_siswa' => $_POST['nama'],
        'alamat' => $_POST['alamat']
    ];
    $npsn = $_POST['npsn'];
    $exec = update($koneksi, 'siswa', $data, ['npsn' => $npsn]);
    echo $exec;
}
if ($pg == 'tambah') {
    $data = [
        'npsn'          => $_POST['npsn'],
        'nama_siswa'   => $_POST['nama'],
        'alamat'         => $_POST['alamat'],
        'status'         => 1
    ];
    $exec = insert($koneksi, 'siswa', $data);
    echo $exec;
}
if ($pg == 'hapus') {
    $id = $_POST['id'];
    delete($koneksi, 'mapel', ['kode_mapel' => $id]);
}

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

if ($pg == 'importmaster') {
    $file_mimes = array('application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    if (isset($_FILES['file']['name'])) {

        $arr_file = explode('.', $_FILES['file']['name']);
        $extension = end($arr_file);

        if ('csv' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

        $spreadsheet = $reader->load($_FILES['file']['tmp_name']);
        $sheetNames = $spreadsheet->getSheetNames();
        foreach ($sheetNames as $sheetIndex => $sheetName) {
            if ($sheetName == 'siswa') {
                mysqli_query($koneksi, 'Truncate siswa');
                $sheetData = $spreadsheet->setActiveSheetIndexByName('siswa')->toArray();
                $sukses = $gagal = 0;
                for ($i = 1; $i < count($sheetData); $i++) {
                    $nis = $sheetData[$i]['1'];
                    $nisn = $sheetData[$i]['2'];
                    $nama = $sheetData[$i]['3'];
                    $tempat = $sheetData[$i]['4'];
                    $tgllahir = $sheetData[$i]['5'];
                    $kelas = $sheetData[$i]['6'];
                    $jurusan = $sheetData[$i]['7'];
                    $keterangan = $sheetData[$i]['8'];
                    $nohp = $sheetData[$i]['9'];
                    $password = $sheetData[$i]['10'];
                    $datax = [
                        'nis' => $nis,
                        'nisn' => $nisn,
                        'nama' => $nama,
                        'tempat' => $tempat,
                        'tgl_lahir' => $tgllahir,
                        'kelas' => $kelas,
                        'jurusan' => $jurusan,
                        'keterangan' => $keterangan,
                        'nohp' => $nohp,
                        'status' => 1,
                        'password' => $password
                    ];
                    $exec = insert($koneksi, 'siswa', $datax);
                    echo mysqli_error($koneksi);
                    ($exec) ? $sukses++ : $gagal++;
                }
            }
            if ($sheetName == 'mapel') {
                mysqli_query($koneksi, 'Truncate mapel');
                $sheetData = $spreadsheet->setActiveSheetIndexByName('mapel')->toArray();
                $sukses = $gagal = 0;
                for ($i = 1; $i < count($sheetData); $i++) {
                    $urut = $sheetData[$i]['0'];
                    $kode = $sheetData[$i]['1'];
                    $nama = $sheetData[$i]['2'];
                    $kelompok = $sheetData[$i]['3'];
                    $jurusan = $sheetData[$i]['4'];

                    $datax = [
                        'no_urut' => $urut,
                        'kode_mapel' => $kode,
                        'nama_mapel' => $nama,
                        'kelompok' => $kelompok,
                        'jurusan' => $jurusan,

                    ];
                    $exec = insert($koneksi, 'mapel', $datax);
                    echo mysqli_error($koneksi);
                    ($exec) ? $sukses++ : $gagal++;
                }
            }
            if ($sheetName == 'nilai') {
                mysqli_query($koneksi, 'Truncate nilai');
                $sheetData = $spreadsheet->setActiveSheetIndexByName('nilai')->toArray();
                $sukses = $gagal = 0;
                for ($a = 3; $a < 30; $a++) {
                    $kode = $sheetData['0'][$a];
                    if ($kode == "") {

                        break;
                    }
                    for ($i = 1; $i < count($sheetData); $i++) {
                        $nis = $sheetData[$i]['1'];
                        $kode = $kode;
                        $nilai = $sheetData[$i][$a];
                        $datax = [
                            'nis' => $nis,
                            'kode_mapel' => $kode,
                            'nilai' => $nilai,
                        ];
                        $exec = insert($koneksi, 'nilai', $datax);
                        echo mysqli_error($koneksi);
                        ($exec) ? $sukses++ : $gagal++;
                    }
                }
            }
        }
    }
}
if ($pg == 'importxlsx') {

    $file_mimes = array('application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    if (isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {

        $arr_file = explode('.', $_FILES['file']['name']);
        $extension = end($arr_file);

        if ('csv' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }

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

        $sheetData = $spreadsheet->getActiveSheet()->toArray();

        $sukses = $gagal = 0;
        mysqli_query($koneksi, "truncate nilai");
        for ($a = 3; $a < 30; $a++) {
            $kode = $sheetData['0'][$a];
            if ($kode == "") {

                break;
            }
            for ($i = 1; $i < count($sheetData); $i++) {
                $nis = $sheetData[$i]['1'];
                $kode = $kode;
                $nilai = $sheetData[$i][$a];
                $datax = [
                    'nis' => $nis,
                    'kode_mapel' => $kode,
                    'nilai' => $nilai,
                ];
                $exec = insert($koneksi, 'nilai', $datax);
                echo mysqli_error($koneksi);
                ($exec) ? $sukses++ : $gagal++;
            }
        }
        echo "jumlah kode mapel " . $a;


        // }
    } else {
        echo "xxxx";
    }
}

if ($pg == 'import') {
    if (isset($_FILES['file']['name'])) {
        $file = $_FILES['file']['name'];
        $temp = $_FILES['file']['tmp_name'];
        $ext = explode('.', $file);
        $ext = end($ext);
        if ($ext <> 'xls') {
            echo "harap pilih file excel .xls";
        } else {
            $data = new Spreadsheet_Excel_Reader($temp);
            $hasildata = $data->rowcount($sheet_index = 0);
            $sukses = $gagal = 0;

            mysqli_query($koneksi, "truncate mapel");
            for ($i = 2; $i <= $hasildata; $i++) {
                $no_urut = $data->val($i, 1);
                $kode_mapel = $data->val($i, 2);
                $nama_mapel = addslashes($data->val($i, 3));
                $kelompok = addslashes($data->val($i, 4));

                if ($kode_mapel <> "") {
                    $datax = [
                        'no_urut' => $no_urut,
                        'kode_mapel' => $kode_mapel,
                        'nama_mapel' => $nama_mapel,
                        'kelompok'  => $kelompok,

                    ];
                    $exec = insert($koneksi, 'mapel', $datax);
                    echo mysqli_error($koneksi);
                    ($exec) ? $sukses++ : $gagal++;
                }
            }
            $total = $hasildata - 1;
            echo "Berhasil: $sukses | Gagal: $gagal | Dari: $total";
        }
    } else {
        echo "gagal";
    }
}
