<?php
/*
|--------------------------------------------------------------------------
| IMPORT SISWA
| SIA PAY - Administrasi Pembayaran Madrasah
| Compatible PHP 7.4+
|--------------------------------------------------------------------------
*/

$title = 'Import Data Siswa';

require_once __DIR__ . '/../includes/header.php';


/*
|--------------------------------------------------------------------------
| VARIABEL
|--------------------------------------------------------------------------
*/

$success  = 0;
$duplicate = 0;
$invalid  = 0;
$errors   = array();


/*
|--------------------------------------------------------------------------
| FUNGSI KONVERSI KOLOM EXCEL
|--------------------------------------------------------------------------
*/

if (!function_exists('excel_column_number')) {

    function excel_column_number($column)
    {
        $column = strtoupper($column);
        $number = 0;

        for ($i = 0; $i < strlen($column); $i++) {

            $number =
                ($number * 26)
                + (ord($column[$i]) - 64);
        }

        return $number - 1;
    }
}


/*
|--------------------------------------------------------------------------
| MEMBACA XLSX
|--------------------------------------------------------------------------
*/

if (!function_exists('read_xlsx_file')) {

    function read_xlsx_file($file)
    {
        if (!class_exists('ZipArchive')) {

            throw new Exception(
                'Extension ZIP belum aktif pada PHP. ' .
                'Silakan aktifkan extension=zip pada php.ini kemudian restart Apache.'
            );
        }


        $zip = new ZipArchive();


        if (
            $zip->open($file) !== true
        ) {

            throw new Exception(
                'File Excel tidak dapat dibuka.'
            );
        }


        /*
        |--------------------------------------------------------------------------
        | SHARED STRINGS
        |--------------------------------------------------------------------------
        */

        $sharedStrings = array();


        $sharedXml =
            $zip->getFromName(
                'xl/sharedStrings.xml'
            );


        if ($sharedXml !== false) {

            libxml_use_internal_errors(true);


            $xmlShared =
                simplexml_load_string(
                    $sharedXml
                );


            if ($xmlShared !== false) {

                /*
                |--------------------------------------------------------------------------
                | Gunakan local-name()
                | Tidak menggunakan prefix x:
                |--------------------------------------------------------------------------
                */

                $items =
                    $xmlShared->xpath(
                        '//*[local-name()="si"]'
                    );


                if ($items) {

                    foreach (
                        $items as $item
                    ) {

                        $texts =
                            $item->xpath(
                                './/*[local-name()="t"]'
                            );


                        $value = '';


                        if ($texts) {

                            foreach (
                                $texts as $text
                            ) {

                                $value .=
                                    (string)$text;
                            }
                        }


                        $sharedStrings[] =
                            $value;
                    }
                }
            }


            libxml_clear_errors();
        }


        /*
        |--------------------------------------------------------------------------
        | SHEET 1
        |--------------------------------------------------------------------------
        */

        $sheetXml =
            $zip->getFromName(
                'xl/worksheets/sheet1.xml'
            );


        if ($sheetXml === false) {

            $zip->close();


            throw new Exception(
                'Sheet pertama pada file Excel tidak ditemukan.'
            );
        }


        libxml_use_internal_errors(true);


        $xml =
            simplexml_load_string(
                $sheetXml
            );


        if ($xml === false) {

            $zip->close();

            libxml_clear_errors();


            throw new Exception(
                'Isi file Excel tidak dapat dibaca.'
            );
        }


        /*
        |--------------------------------------------------------------------------
        | AMBIL BARIS
        |--------------------------------------------------------------------------
        */

        $rowsXml =
            $xml->xpath(
                '//*[local-name()="sheetData"]/*[local-name()="row"]'
            );


        $rows = array();


        if ($rowsXml) {

            foreach (
                $rowsXml as $rowXml
            ) {

                $cells = array();


                /*
                |--------------------------------------------------------------------------
                | AMBIL CELL
                |--------------------------------------------------------------------------
                */

                $cellNodes =
                    $rowXml->xpath(
                        './*[local-name()="c"]'
                    );


                if (!$cellNodes) {
                    continue;
                }


                foreach (
                    $cellNodes as $cell
                ) {

                    /*
                    |--------------------------------------------------------------------------
                    | REFERENSI CELL
                    |--------------------------------------------------------------------------
                    */

                    $reference = '';


                    if (
                        isset($cell['r'])
                    ) {

                        $reference =
                            (string)$cell['r'];
                    }


                    preg_match(
                        '/^([A-Z]+)/i',
                        $reference,
                        $match
                    );


                    $column =
                        isset($match[1])
                        ? strtoupper($match[1])
                        : 'A';


                    $index =
                        excel_column_number(
                            $column
                        );


                    /*
                    |--------------------------------------------------------------------------
                    | TIPE CELL
                    |--------------------------------------------------------------------------
                    */

                    $type = '';


                    if (
                        isset($cell['t'])
                    ) {

                        $type =
                            (string)$cell['t'];
                    }


                    $value = '';


                    /*
                    |--------------------------------------------------------------------------
                    | VALUE NORMAL
                    |--------------------------------------------------------------------------
                    */

                    $valueNodes =
                        $cell->xpath(
                            './*[local-name()="v"]'
                        );


                    if ($valueNodes) {

                        $value =
                            (string)$valueNodes[0];
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | SHARED STRING
                    |--------------------------------------------------------------------------
                    */

                    if (
                        $type === 's'
                    ) {

                        $stringIndex =
                            (int)$value;


                        if (
                            isset(
                                $sharedStrings[
                                    $stringIndex
                                ]
                            )
                        ) {

                            $value =
                                $sharedStrings[
                                    $stringIndex
                                ];
                        }
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | INLINE STRING
                    |--------------------------------------------------------------------------
                    */

                    elseif (
                        $type === 'inlineStr'
                    ) {

                        $inlineTexts =
                            $cell->xpath(
                                './/*[local-name()="is"]//*[local-name()="t"]'
                            );


                        $value = '';


                        if ($inlineTexts) {

                            foreach (
                                $inlineTexts as $text
                            ) {

                                $value .=
                                    (string)$text;
                            }
                        }
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | STRING
                    |--------------------------------------------------------------------------
                    */

                    elseif (
                        $type === 'str'
                    ) {

                        $texts =
                            $cell->xpath(
                                './/*[local-name()="t"]'
                            );


                        if ($texts) {

                            $value = '';


                            foreach (
                                $texts as $text
                            ) {

                                $value .=
                                    (string)$text;
                            }
                        }
                    }


                    $cells[$index] =
                        trim(
                            (string)$value
                        );
                }


                /*
                |--------------------------------------------------------------------------
                | SUSUN KOLOM
                |--------------------------------------------------------------------------
                */

                if (!empty($cells)) {

                    ksort($cells);


                    /*
                    |--------------------------------------------------------------------------
                    | Penting:
                    | array_values bisa menghilangkan posisi kolom kosong.
                    | Karena template kita menggunakan kolom berurutan,
                    | ini aman.
                    |--------------------------------------------------------------------------
                    */

                    $row =
                        array_values($cells);


                    while (
                        count($row) < 7
                    ) {

                        $row[] = '';
                    }


                    $rows[] = $row;
                }
            }
        }


        libxml_clear_errors();

        $zip->close();


        if (empty($rows)) {

            throw new Exception(
                'Tidak ditemukan data pada file Excel.'
            );
        }


        return $rows;
    }
}


/*
|--------------------------------------------------------------------------
| DOWNLOAD TEMPLATE XLSX
|--------------------------------------------------------------------------
|
| File template dibuat sebagai file statis:
|
| /admin/template_siswa.xlsx
|
|--------------------------------------------------------------------------
*/

if (
    isset($_GET['template'])
) {

    $template =
        __DIR__ .
        '/template_siswa.xlsx';


    if (
        !file_exists($template)
    ) {

        echo '<div class="alert danger">';
        echo 'File <strong>template_siswa.xlsx</strong> belum ditemukan di folder admin.';
        echo '</div>';

    } else {

        /*
        |--------------------------------------------------------------------------
        | Bersihkan output buffer
        |--------------------------------------------------------------------------
        */

        while (
            ob_get_level() > 0
        ) {

            ob_end_clean();
        }


        header(
            'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        );

        header(
            'Content-Disposition: attachment; filename="template_siswa.xlsx"'
        );

        header(
            'Content-Length: ' .
            filesize($template)
        );

        header(
            'Cache-Control: no-cache, no-store, must-revalidate'
        );

        header(
            'Pragma: no-cache'
        );

        header(
            'Expires: 0'
        );


        readfile($template);

        exit;
    }
}


/*
|--------------------------------------------------------------------------
| PROSES IMPORT
|--------------------------------------------------------------------------
*/

if (
    $_SERVER['REQUEST_METHOD'] === 'POST'
) {

    if (
        !isset($_FILES['excel'])
    ) {

        $errors[] =
            'Silakan pilih file Excel terlebih dahulu.';

    } else {

        $file =
            $_FILES['excel'];


        /*
        |--------------------------------------------------------------------------
        | ERROR UPLOAD
        |--------------------------------------------------------------------------
        */

        if (
            $file['error'] !== UPLOAD_ERR_OK
        ) {

            $errors[] =
                'File gagal diupload. Kode error: ' .
                (int)$file['error'];

        } else {

            /*
            |--------------------------------------------------------------------------
            | EXTENSION
            |--------------------------------------------------------------------------
            */

            $extension =
                strtolower(
                    pathinfo(
                        $file['name'],
                        PATHINFO_EXTENSION
                    )
                );


            if (
                $extension !== 'xlsx'
            ) {

                $errors[] =
                    'File harus berformat .xlsx';

            } else {

                try {

                    /*
                    |--------------------------------------------------------------------------
                    | BACA EXCEL
                    |--------------------------------------------------------------------------
                    */

                    $rows =
                        read_xlsx_file(
                            $file['tmp_name']
                        );


                    if (
                        count($rows) < 2
                    ) {

                        throw new Exception(
                            'File Excel belum memiliki data siswa.'
                        );
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | TRANSAKSI
                    |--------------------------------------------------------------------------
                    */

                    $pdo->beginTransaction();


                    /*
                    |--------------------------------------------------------------------------
                    | CEK NIS
                    |--------------------------------------------------------------------------
                    */

                    $check =
                        $pdo->prepare(
                            "
                            SELECT id
                            FROM siswa
                            WHERE nis = ?
                            LIMIT 1
                            "
                        );


                    /*
                    |--------------------------------------------------------------------------
                    | INSERT
                    |--------------------------------------------------------------------------
                    */

                    $insert =
                        $pdo->prepare(
                            "
                            INSERT INTO siswa
                            (
                                nama,
                                nis,
                                kelas,
                                wa_ortu,
                                portal_password,
                                status
                            )
                            VALUES
                            (
                                ?,
                                ?,
                                ?,
                                ?,
                                ?,
                                ?
                            )
                            "
                        );


                    /*
                    |--------------------------------------------------------------------------
                    | DATA
                    |--------------------------------------------------------------------------
                    |
                    | Baris pertama dianggap header.
                    |--------------------------------------------------------------------------
                    */

                    for (
                        $i = 1;
                        $i < count($rows);
                        $i++
                    ) {

                        $row =
                            $rows[$i];


                        /*
                        |--------------------------------------------------------------------------
                        | Pastikan 7 kolom
                        |--------------------------------------------------------------------------
                        */

                        while (
                            count($row) < 7
                        ) {

                            $row[] = '';
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | DATA
                        |--------------------------------------------------------------------------
                        */

                        $nama =
                            trim(
                                (string)$row[1]
                            );


                        $nis =
                            trim(
                                (string)$row[2]
                            );


                        $kelas =
                            strtoupper(
                                trim(
                                    (string)$row[3]
                                )
                            );


                        $wa =
                            trim(
                                (string)$row[4]
                            );


                        $password =
                            trim(
                                (string)$row[5]
                            );


                        $status =
                            strtolower(
                                trim(
                                    (string)$row[6]
                                )
                            );


                        /*
                        |--------------------------------------------------------------------------
                        | BARIS KOSONG
                        |--------------------------------------------------------------------------
                        */

                        if (
                            $nama === '' &&
                            $nis === ''
                        ) {

                            continue;
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | VALIDASI
                        |--------------------------------------------------------------------------
                        */

                        if (
                            $nama === '' ||
                            $nis === ''
                        ) {

                            $invalid++;

                            continue;
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | VALIDASI KELAS
                        |--------------------------------------------------------------------------
                        */

                        if (
                            !in_array(
                                $kelas,
                                array(
                                    'VII',
                                    'VIII',
                                    'IX'
                                ),
                                true
                            )
                        ) {

                            $invalid++;

                            continue;
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | STATUS
                        |--------------------------------------------------------------------------
                        */

                        if (
                            $status !== 'nonaktif'
                        ) {

                            $status =
                                'aktif';
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | PASSWORD DEFAULT
                        |--------------------------------------------------------------------------
                        */

                        if (
                            $password === ''
                        ) {

                            $password =
                                '123456';
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | CEK DUPLIKAT NIS
                        |--------------------------------------------------------------------------
                        */

                        $check->execute(
                            array(
                                $nis
                            )
                        );


                        if (
                            $check->fetch(
                                PDO::FETCH_ASSOC
                            )
                        ) {

                            $duplicate++;

                            continue;
                        }


                        /*
                        |--------------------------------------------------------------------------
                        | INSERT
                        |--------------------------------------------------------------------------
                        */

                        $insert->execute(
                            array(
                                $nama,
                                $nis,
                                $kelas,
                                $wa,
                                student_portal_password(
                                    $password
                                ),
                                $status
                            )
                        );


                        $success++;
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | COMMIT
                    |--------------------------------------------------------------------------
                    */

                    $pdo->commit();


                } catch (
                    Exception $e
                ) {

                    if (
                        $pdo->inTransaction()
                    ) {

                        $pdo->rollBack();
                    }


                    $errors[] =
                        $e->getMessage();
                }
            }
        }
    }
}

?>


<style>

.import-page {
    max-width: 1050px;
    margin: 0 auto;
}

.import-header {
    background: linear-gradient(
        135deg,
        #173b57,
        #246b8f
    );

    color: #fff;

    border-radius: 18px;

    padding: 26px;

    margin-bottom: 20px;

    box-shadow:
        0 8px 25px rgba(
            0,
            0,
            0,
            .08
        );
}

.import-header h1 {
    margin: 0 0 7px;
}

.import-header p {
    margin: 0;

    opacity: .9;
}


.import-grid {
    display: grid;

    grid-template-columns:
        repeat(3, 1fr);

    gap: 14px;

    margin-bottom: 20px;
}


.import-step {
    border: 1px solid #e1e6eb;

    background: #fff;

    border-radius: 14px;

    padding: 17px;

    box-shadow:
        0 4px 15px rgba(
            0,
            0,
            0,
            .04
        );
}


.import-step .number {
    width: 32px;

    height: 32px;

    display: inline-flex;

    align-items: center;

    justify-content: center;

    border-radius: 50%;

    background: #eef6fb;

    margin-bottom: 8px;

    font-weight: bold;
}


.import-step strong {
    display: block;

    margin-bottom: 5px;
}


.upload-card {
    background: #fff;

    border: 1px solid #e1e6eb;

    border-radius: 18px;

    padding: 25px;

    box-shadow:
        0 5px 20px rgba(
            0,
            0,
            0,
            .05
        );
}


.upload-area {
    border: 2px dashed #ccd6dd;

    border-radius: 15px;

    padding: 35px 20px;

    text-align: center;

    background: #fafcfd;

    margin: 18px 0;
}


.upload-icon {
    font-size: 40px;

    margin-bottom: 8px;
}


.upload-area input[type=file] {
    margin-top: 15px;

    max-width: 100%;
}


.import-info {
    background: #eef7ff;

    border: 1px solid #cfe7fa;

    color: #17527a;

    border-radius: 12px;

    padding: 14px;

    font-size: 13px;

    margin-bottom: 18px;
}


.import-warning {
    background: #fff8e6;

    border: 1px solid #f0df9c;

    color: #705b17;

    border-radius: 12px;

    padding: 14px;

    font-size: 13px;

    margin-bottom: 18px;
}


.result-box {
    background: #f7faf8;

    border: 1px solid #d9eadf;

    border-radius: 14px;

    padding: 18px;

    margin-bottom: 20px;
}


.result-box strong {
    font-size: 16px;
}


.example-table {
    overflow-x: auto;
}


@media(max-width:700px) {

    .import-grid {
        grid-template-columns: 1fr;
    }

    .upload-card {
        padding: 17px;
    }

}

</style>


<div class="import-page">


    <!-- =========================================================
         HEADER
    ========================================================== -->

    <div class="import-header">

        <h1>
            📥 Import Data Siswa
        </h1>

        <p>
            Tambahkan banyak siswa sekaligus
            menggunakan template Excel.
        </p>

    </div>


    <!-- =========================================================
         HASIL IMPORT
    ========================================================== -->

    <?php if (
        $success > 0 ||
        $duplicate > 0 ||
        $invalid > 0
    ): ?>

        <div class="result-box">

            <strong>
                ✅ Hasil Import
            </strong>

            <div style="margin-top:10px;">

                Berhasil ditambahkan:
                <strong>
                    <?= (int)$success ?>
                </strong>
                siswa

                &nbsp; | &nbsp;

                NIS sudah terdaftar:
                <strong>
                    <?= (int)$duplicate ?>
                </strong>

                &nbsp; | &nbsp;

                Data tidak valid:
                <strong>
                    <?= (int)$invalid ?>
                </strong>

            </div>

        </div>

    <?php endif; ?>


    <!-- =========================================================
         ERROR
    ========================================================== -->

    <?php if (
        !empty($errors)
    ): ?>

        <div class="alert danger">

            <strong>
                ❌ Import gagal
            </strong>

            <ul>

                <?php foreach (
                    $errors as $error
                ): ?>

                    <li>
                        <?= e($error) ?>
                    </li>

                <?php endforeach; ?>

            </ul>

        </div>

    <?php endif; ?>


    <!-- =========================================================
         LANGKAH
    ========================================================== -->

    <div class="import-grid">


        <div class="import-step">

            <div class="number">
                1
            </div>

            <strong>
                Download Template
            </strong>

            <small>
                Download template Excel
                resmi aplikasi.
            </small>

        </div>


        <div class="import-step">

            <div class="number">
                2
            </div>

            <strong>
                Isi Data Siswa
            </strong>

            <small>
                Masukkan nama, NIS,
                kelas dan WA orang tua.
            </small>

        </div>


        <div class="import-step">

            <div class="number">
                3
            </div>

            <strong>
                Upload Excel
            </strong>

            <small>
                Pilih file kemudian
                klik Import Siswa.
            </small>

        </div>


    </div>


    <!-- =========================================================
         UPLOAD CARD
    ========================================================== -->

    <div class="upload-card">


        <div class="import-info">

            <strong>
                📋 Struktur Template Excel
            </strong>

            <br><br>

            <strong>
                No
            </strong>
            — nomor urut

            <br>

            <strong>
                Nama
            </strong>
            — nama lengkap siswa

            <br>

            <strong>
                NIS
            </strong>
            — nomor induk siswa

            <br>

            <strong>
                Kelas
            </strong>
            — VII / VIII / IX

            <br>

            <strong>
                WA Orang Tua
            </strong>
            — nomor WhatsApp orang tua

            <br>

            <strong>
                Password Portal
            </strong>
            — password login orang tua/siswa

            <br>

            <strong>
                Status
            </strong>
            — aktif / nonaktif

        </div>


        <div class="import-warning">

            ⚠️ <strong>Perhatian</strong>

            <br><br>

            1. Jangan mengubah nama kolom
            pada baris pertama.

            <br>

            2. NIS yang sudah terdaftar
            akan dilewati.

            <br>

            3. Jika password dikosongkan,
            otomatis menjadi:

            <strong>
                123456
            </strong>

            <br>

            4. Kelas yang diperbolehkan:

            <strong>
                VII, VIII, IX
            </strong>

            <br>

            5. Sebaiknya nomor WA ditulis
            dengan format:

            <strong>
                08xxxxxxxxxx
            </strong>

        </div>


        <!-- =====================================================
             UPLOAD
        ====================================================== -->

        <div class="upload-area">

            <div class="upload-icon">
                📊
            </div>

            <strong>
                Pilih File Excel
            </strong>

            <br>

            <small>
                File harus berformat
                <strong>.xlsx</strong>
            </small>


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

                <input
                    type="file"
                    name="excel"
                    accept=".xlsx"
                    required
                >

                <br><br>

                <button
                    type="submit"
                    class="btn primary"
                    onclick="
                        this.disabled=true;
                        this.innerHTML='⏳ Memproses...';
                        this.form.submit();
                    "
                >
                    📥 Import Siswa
                </button>


                <a
                    href="siswa.php"
                    class="btn"
                >
                    Batal
                </a>

            </form>

        </div>


        <!-- =====================================================
             TEMPLATE
        ====================================================== -->

        <div style="margin-bottom:20px;">

            <a
                href="import_siswa.php?template=1"
                class="btn primary"
            >
                📄 Download Template Excel
            </a>

        </div>


        <hr style="margin:25px 0;">


        <!-- =====================================================
             CONTOH
        ====================================================== -->

        <h3>
            Contoh Pengisian
        </h3>


        <div class="example-table table-wrap">

            <table>

                <thead>

                    <tr>

                        <th>
                            No
                        </th>

                        <th>
                            Nama
                        </th>

                        <th>
                            NIS
                        </th>

                        <th>
                            Kelas
                        </th>

                        <th>
                            WA Orang Tua
                        </th>

                        <th>
                            Password
                        </th>

                        <th>
                            Status
                        </th>

                    </tr>

                </thead>

                <tbody>

                    <tr>

                        <td>
                            1
                        </td>

                        <td>
                            Ahmad Fauzan
                        </td>

                        <td>
                            10001
                        </td>

                        <td>
                            VII
                        </td>

                        <td>
                            081234567890
                        </td>

                        <td>
                            123456
                        </td>

                        <td>
                            aktif
                        </td>

                    </tr>


                    <tr>

                        <td>
                            2
                        </td>

                        <td>
                            Siti Aisyah
                        </td>

                        <td>
                            10002
                        </td>

                        <td>
                            VII
                        </td>

                        <td>
                            081234567891
                        </td>

                        <td>
                            123456
                        </td>

                        <td>
                            aktif
                        </td>

                    </tr>


                    <tr>

                        <td>
                            3
                        </td>

                        <td>
                            Budi Santoso
                        </td>

                        <td>
                            20001
                        </td>

                        <td>
                            VIII
                        </td>

                        <td>
                            081234567892
                        </td>

                        <td>
                            123456
                        </td>

                        <td>
                            aktif
                        </td>

                    </tr>

                </tbody>

            </table>

        </div>


    </div>

</div>


<?php
/* Pastikan siswa hasil import juga memiliki snapshot tahun aktif. */
try { sync_all_students_current_year(); } catch (Exception $e) {}
require_once __DIR__ . '/../includes/footer.php';
?>