<style>
    .fontInput {
        font-size: 11px;
        font-family: arial;
    }
</style>
<script>
    function formatNumber(num) {
        return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1.')
    }
</script>
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">

    <?php require('./layout/headerpage.php'); ?>

    <div class="container col-12 mb-4 pb-5">
        <div class="row">
            <div class="col-12">
                <div class="col-12">
                    <div class="card  mx-3">
                        <div class="timeline-header bg-primary py-2 pl-2">
                            <b class="text-light judul"><span class="bi bi-upload"></span> Upload Siswa & Tanggungan</h4>
                                <?php //$pandu->linkPandu('view/seting/siswa/pandu_rekamsiswa'); 
                                ?>
                        </div>
                        <div class="col-12">
                            <div class="col-md-6">
                                <div class="card-body mt-4 px-1 mx-2">
                                    <div class="row">
                                        <div class="col-12 col-sm-6 col-md-12">
                                            <div class="info-box mb-3">
                                                <div class="info-box-content">
                                                    <div class="relative mb-0 pb-4">
                                                        <input name="judul" value="UPLOAD SISWA (XLSX)" style="background-color: gainsboro;" class="form-control-sm col-sm-3 fontInput small bg-default mt-3 text-center" disabled>
                                                    </div>

                                                    <div class="col-sm-12 invoice-col pt-0 text-dark">

                                                        <form action="" method="post" name="frmExcelImport" id="frmExcelImport" enctype="multipart/form-data">

                                                            <?php $csrf->token(); ?>
                                                            <input type="text" class="form-control form-control-sm" value="<?php echo $tahun; ?>" name="restore" hidden />

                                                            <div class="form-group">
                                                                <label for="exampleInputFile">File Data Siswa & Tanggungan (Format File : <i class="text-danger">FILE.xlsx</i>)</label>
                                                                <div class="input-group">
                                                                    <div class="custom-file">
                                                                        <input type="file" class="custom-file-input" id="exampleInputFile" name="file" accept=".xlsx,.csv">
                                                                        <label class="custom-file-label" for="exampleInputFile">Pilih File Data Siswa</label>
                                                                    </div>
                                                                </div>
                                                            </div>
                                                            <div class='row mt-4'>

                                                                <div class="col-12 py-0 mb-4">
                                                                    <div>
                                                                        <p class="text-primary"><b><i> <?php
                                                                                                        echo $link->exportXlsx('seting/siswa/siswa_export', $tahun);
                                                                                                        ?></i></b></p>

                                                                    </div>
                                                                    <div class="row float-right">
                                                                        <div class="col-auto">

                                                                            <a <?php echo $link->page('seting/siswa/siswa_read'); ?> class="btn btn-primary btn-xs mx-1 px-2 mt-1"><i class="bi-arrow-left-circle"></i> Cancel</a>
                                                                            <button type="submit" class="btn btn-primary btn-xs mx-1 px-2 mt-1" name="import" onclick="return confirm('Apakah Anda yakin akan upload data siswa, Yakin Data sudah benar?')"><i class="bi-upload"></i> UPLOAD </button>
                                                                        </div>

                                                                    </div>
                                                                </div>
                                                            </div>
                                                        </form>
                                                    </div>
                                                </div>

                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>
        </div>

    </div>
</div>
<script>
    $(function() {
        bsCustomFileInput.init();
    });
</script>


<?php

error_reporting(0);
spl_autoload_register(function ($mod) {
    require('../../asset/mod/' . $mod . '.php');
});

// Load file autoload.php
require '../../asset/bootstrap/asset/phpspreadsheet/autoload.php';
$crud = new crudData;
$siswa = new siswa;
//$tahun = $_GET['tahun'];
$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);
$tb_siswa = "tb_siswa" . $tahun_tabel;
$tb_siswa_min = "tb_siswa_min" . $tahun_tabel;

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

if (isset($_POST["import"])) {

    $file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', '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();

        if (!empty($sheetData) && ($extension = 'xlsx' or $extention = 'csv')) {
            for ($i = 1; $i < count($sheetData); $i++) {

                $no = "";

                $nama = $siswa->ifsheetText($sheetData, $i, 1, "");
                $nis = $siswa->ifsheetText($sheetData, $i, 2, "");
                $kelamin = $siswa->ifsheetText($sheetData, $i, 3, "");
                $jenjang_sis = $siswa->ifsheetText($sheetData, $i, 4, "");
                $paralel = $siswa->ifsheetText($sheetData, $i, 5, "");
                $ortu = $siswa->ifsheetText($sheetData, $i, 6, "");
                $alamat = $siswa->ifsheetText($sheetData, $i, 7, "");

                $tang_A = $siswa->ifsheetNumber($sheetData, $i, 8, "");
                $tang_B = $siswa->ifsheetNumber($sheetData, $i, 9, "");
                $tang_C = $siswa->ifsheetNumber($sheetData, $i, 10, "");
                $tang_D = $siswa->ifsheetNumber($sheetData, $i, 11, "");
                $tang_E = $siswa->ifsheetNumber($sheetData, $i, 12, "");
                $tang_F = $siswa->ifsheetNumber($sheetData, $i, 13, "");
                $tang_G = $siswa->ifsheetNumber($sheetData, $i, 14, "");
                $tang_H = $siswa->ifsheetNumber($sheetData, $i, 15, "");
                $tang_I = $siswa->ifsheetNumber($sheetData, $i, 16, "");
                $tang_J = $siswa->ifsheetNumber($sheetData, $i, 17, "");
                $tang_K = $siswa->ifsheetNumber($sheetData, $i, 18, "");
                $tang_L = $siswa->ifsheetNumber($sheetData, $i, 19, "");
                $tang_M = $siswa->ifsheetNumber($sheetData, $i, 20, "");
                $tang_N = $siswa->ifsheetNumber($sheetData, $i, 21, "");
                $tang_O = $siswa->ifsheetNumber($sheetData, $i, 22, "");
                $tang_P = $siswa->ifsheetNumber($sheetData, $i, 23, "");
                $tang_Q = $siswa->ifsheetNumber($sheetData, $i, 24, "");
                $tang_R = $siswa->ifsheetNumber($sheetData, $i, 25, "");
                $tang_S = $siswa->ifsheetNumber($sheetData, $i, 26, "");
                $tang_T = $siswa->ifsheetNumber($sheetData, $i, 27, "");
                $tang_U = $siswa->ifsheetNumber($sheetData, $i, 28, "");
                $tang_V = $siswa->ifsheetNumber($sheetData, $i, 29, "");
                $tang_W = $siswa->ifsheetNumber($sheetData, $i, 30, "");
                $tang_X = $siswa->ifsheetNumber($sheetData, $i, 31, "");
                $tang_Y = $siswa->ifsheetNumber($sheetData, $i, 32, "");
                $tang_Z = $siswa->ifsheetNumber($sheetData, $i, 33, "");

                $status = $siswa->ifsheetText($sheetData, $i, 34, "Aktif");
                $email = $siswa->ifsheetText($sheetData, $i, 35, "");
                $hp = $siswa->ifsheetText($sheetData, $i, 36, "");
                $kategori = $siswa->ifsheetText($sheetData, $i, 37, "");



                if ($sheetData[$i][38] == "" || strlen($sheetData[$i][38]) > 2) {
                    $set_startAt = 1;
                } else {
                    $set_startAt = htmlspecialchars($sheetData[$i][38]);
                };
                $id = substr(md5($nis), 0, 10) . substr(md5($nama), 11, 10);

                $countdata = $crud->countData($tb_siswa, "where nis='$nis'");

                if ($nis == "" || $nama == "" || $jenjang_sis == "" || $paralel == "" || $countdata > 0)
                    continue;


                $db = database::getInstance();



                $query = "insert into " . $tb_siswa . " (nama,nis,kelamin,jenjang,paralel,ortu,alamat,tang_A,tang_B,tang_C,tang_D,tang_E,tang_F,tang_G,tang_H,tang_I,tang_J,tang_K,tang_L,tang_M,tang_N,tang_O,tang_P,tang_Q,tang_R,tang_S,tang_T,tang_U,tang_V,tang_W,tang_X,tang_Y,tang_Z,status,email,hp,kategori,startAt,id) 
                    values ('$nama','$nis','$kelamin','$jenjang_sis','$paralel','$ortu','$alamat','$tang_A','$tang_B','$tang_C','$tang_D','$tang_E','$tang_F','$tang_G','$tang_H','$tang_I','$tang_J','$tang_K','$tang_L','$tang_M','$tang_N','$tang_O','$tang_P','$tang_Q','$tang_R','$tang_S','$tang_T','$tang_U','$tang_V','$tang_W','$tang_X','$tang_Y','$tang_Z','$status','$email','$hp','$kategori','$set_startAt','$id')";
                $insert = $db->runQuery($query);

                $query2 = "insert into " . $tb_siswa_min . " (nama,nis,jenjang,paralel) 
                values ('$nama','$nis','$jenjang_sis','$paralel')";
                $insert2 = $db->runQuery($query2);

                if (!empty($insert)) {
                    $type = "success";
                    $message = "Excel Data Imported into the Database";
                } else {
                    $type = "error";
                    $message = "Problem in Importing Excel Data";
                }
                //  }
            }
        } else {
            $direct = new toast();
            echo  $direct->redirect('toast', 'unsupport', 'seting/siswa/siswa_read');
        }
    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
    }

    $direct = new toast();
    echo  $direct->redirect('toast', 'save', 'seting/siswa/siswa_read');
}
