<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 Data Nasabah </h4>
                                <?php //$pandu->linkPandu('view/seting/siswa/pandu_rekamsiswa'); 
                                ?>
                        </div>

                        <div class="row col-md-12">
                            <div class="col-md-8">
                                <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="PILIH FILE" 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">



                                                        <div class="row">

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

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

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

                                                                    <div class="form-group">
                                                                        <label for=""> <i class="text-primary">File Nasabah (Format 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 </label>
                                                                            </div>

                                                                        </div>
                                                                        <div class="row mt-4 pl-2">

                                                                            <?php echo $link->formNasabah('nasabah/nasabah_form', $tahun); ?>
                                                                        </div>
                                                                        <div class='row '>
                                                                            <div class="col-12 my-0 pl-3 py-0 my-3">
                                                                                <div class="row float-left">
                                                                                    <div class="col-auto">
                                                                                        <a <?php echo $link->page('nasabah/nasabah_read'); ?> class="btn btn-primary btn-xs float-right mx-2"><i class="bi-arrow-left-circle"></i> Cancel</a>
                                                                                        <button type="submit" class="btn btn-primary btn-xs float-right" name="importNasabah"><i class="bi-upload"></i> Upload</button>
                                                                                    </div>

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

                                                                    </div>


                                                                </form>
                                                            </div>

                                                        </div>




                                                    </div>
                                                </div>

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


                    </div>
                </div>

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

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

<?php

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

// Load file autoload.php
require '../../asset/bootstrap/asset/phpspreadsheet/autoload.php';

//$tahun = $_GET['tahun'];
$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);
$tabungan_siswa = "tabungan_siswa" . $tahun_tabel;

$clsCrud = new crudData;
$user = $clsCrud->getValue('user', " where username='$username'", 'id');

$clstime = new time;
$waktu = $clstime->getTime();

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

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

    $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 = htmlspecialchars($sheetData[$i][1]);
                //$nis = htmlspecialchars($sheetData[$i][2]);
                //$kelamin = htmlspecialchars($sheetData[$i][3]);
                //$saldo = htmlspecialchars($sheetData[$i][4]);

                $nama = $sheetData[$i][1];
                $nis = $sheetData[$i][2];
                $rekening = $sheetData[$i][3];
                $kelamin = $sheetData[$i][4];
                $saldo = $sheetData[$i][5];

                if (is_null($nis)) {
                    $nismd5 = 0;
                    $namamd5 = 0;
                } else {
                    $nismd5 = $nis;
                    $namamd5 = $nama;
                };

                // $id = substr(md5($nis), 0, 10) . substr(md5($nama), 11, 10);
                $id =  substr(md5($namamd5), 0, 7) . substr(md5($waktu), 0, 8) . substr(md5($nismd5), 0, 7);

                $cekDuplikat = new cekID;
                $countDuplikat = $cekDuplikat->count($tabungan_siswa, 'nis', $nis);

                if ($saldo == "" || $saldo < 0) {
                    $saldo = 0;
                } else {
                    $saldo = $saldo;
                };

                if (($nis == "" && $nama == "" && $kelamin == "" && $saldo == "") || $countDuplikat > 0)
                    continue;


                $db = database::getInstance();
                $query = "insert into " . $tabungan_siswa . " (nama,nis,rekening,kelamin,saldo,id,status,created_at,updated_at,created_by) 
                    values ('$nama','$nis','$rekening','$kelamin','$saldo','$id','Aktif','$waktu','$waktu','$user')";

                $insert = $db->runQuery($query);
                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', 'nasabah/nasabah_import');
        }
    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
    }

    $direct = new toast();
    echo  $direct->redirect('toast', 'save', 'nasabah/nasabah_read');
}
