<?php

    include '../include/condb_help.php'; 

    sleep(1);

    date_default_timezone_set('Asia/Bangkok');
    $date = date("Ymd_His");

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

        $idnumber       = (isset($_POST['idnumber']) ? $_POST['idnumber'] : '');
        $head_class     = (isset($_POST['head_class']) ? $_POST['head_class'] : '');
        
        $prefix         = (isset($_POST['prefix']) ? $_POST['prefix'] : '');
        $name           = (isset($_POST['name']) ? $_POST['name'] : '');
        $lname          = (isset($_POST['lname']) ? $_POST['lname'] : '');
        $head_sex       = (isset($_POST['head_sex']) ? $_POST['head_sex'] : '');

        $query_check = "SELECT * FROM tbl_head WHERE head_idnumber = '$idnumber' AND head_class = '$head_class'";
        $result_check = $conn->query($query_check);
        $row_check = $result_check->fetch_assoc();

        $output = '';

        if(mysqli_num_rows($result_check) >= 1){

            $query_checkclass = "SELECT * FROM tbl_headclass WHERE head_idnumber = '$idnumber' AND head_class = '$head_class'";
            $result_checkclass = $conn->query($query_checkclass);

            if(mysqli_num_rows($result_checkclass) >= 1){}else{

                $sql = "INSERT INTO tbl_headclass (head_idnumber, head_class)
                VALUES ('$idnumber', '$head_class')";
                $result = mysqli_query($conn, $sql);

            }


            $output = '<label class="text-danger"> เลขประจำตัวนักเรียนนี้ ได้เพิ่มลงในระบบแล้ว </label>
            <br />
            <a href="report?idnumber='.$idnumber.'&head_class='.$head_class.'&status=modify" class="btn btn-dark btn-sm"> ดู/ลบ ข้อมูล </a>
            <a href="?idnumber='.$idnumber.'&head_class='.$head_class.'&status=edit&page=1" class="btn btn-warning btn-sm"> แก้ไข </a>
            <a href="help-students-insert-two?idnumber='.$idnumber.'&head_class='.$head_class.'&page=2" class="btn btn-success btn-sm"> ทำต่อไป </a>';

        }else{

            $file                       = (isset($_POST['file']) ? $_POST['file'] : '');
            $file2                      = (isset($_POST['file2']) ? $_POST['file2'] : '');

            $file                       = $_FILES["file"]["name"];
            $type                       = strrchr($file,".");

            if(($type==".jpg") || ($type==".jpeg") || ($type==".png") || ($type=="")){

            $numrand_img = (mt_rand());
            $upload = $_FILES['file']['name'];
            if($upload !=''){
                $path_img ="img/student/";
                $type_img = strrchr($_FILES['file']['name'],".");
                $new_img = $numrand_img.$date.$type_img;
                $path_copy_img = $path_img.$new_img;
                $path_link_img = "img/student/".$new_img;
                move_uploaded_file($_FILES['file']['tmp_name'],$path_copy_img);  

                @unlink("img/student/".$file2);
            }else{
                $new_img = $file2;
            }

            $sql = "INSERT INTO tbl_head (head_idnumber, head_pre, head_name, head_lname, head_sex, head_img, head_class)
            VALUES ('$idnumber', '$prefix', '$name', '$lname', '$head_sex', '$new_img', '$head_class')";
            $result = mysqli_query($conn, $sql);

            $query_check = "SELECT * FROM tbl_headclass WHERE head_idnumber = '$idnumber' AND head_class = '$head_class'";
            $result_check = $conn->query($query_check);

            if(mysqli_num_rows($result_check) >= 1){}else{

                $sql = "INSERT INTO tbl_headclass (head_idnumber, head_class)
                VALUES ('$idnumber', '$head_class')";
                $result = mysqli_query($conn, $sql);
                    
            }

            echo "<script type='text/javascript'>";
            echo "window.location='help-students-insert-two?idnumber=$idnumber&head_class=$head_class&page=2'";
            echo "</script>";

            }else{

                echo "<script type='text/javascript'>";
                echo "alert('ไฟล์ภาพไม่ถูกต้อง');";
                echo "window.location='javascript:history.back()';";
                echo "</script>";

            }

        }

        echo $output;

    }

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

        $head_id            = (isset($_POST['head_id']) ? $_POST['head_id'] : '');
        $idnumber           = (isset($_POST['idnumber']) ? $_POST['idnumber'] : '');
        $prefix             = (isset($_POST['prefix']) ? $_POST['prefix'] : '');
        $name               = (isset($_POST['name']) ? $_POST['name'] : '');
        $lname              = (isset($_POST['lname']) ? $_POST['lname'] : '');
        $head_sex           = (isset($_POST['head_sex']) ? $_POST['head_sex'] : '');
        $head_class         = (isset($_POST['head_class']) ? $_POST['head_class'] : '');

        $query_check = "SELECT * FROM tbl_headclass WHERE head_idnumber = '$idnumber' AND head_class = '$head_class'";
        $result_check = $conn->query($query_check);

        $output = '';

        if(mysqli_num_rows($result_check) >= 1){}else{

            $sql = "INSERT INTO tbl_headclass (head_idnumber, head_class)
            VALUES ('$idnumber', '$head_class')";
            $result = mysqli_query($conn, $sql);
                    
        }

        $file                       = (isset($_POST['file']) ? $_POST['file'] : '');
        $file2                      = (isset($_POST['file2']) ? $_POST['file2'] : '');

        $file                       = $_FILES["file"]["name"];
        $type                       = strrchr($file,".");

        if(($type==".jpg") || ($type==".jpeg") || ($type==".png") || ($type=="")){

        $numrand_img = (mt_rand());
        $upload = $_FILES['file']['name'];
        if($upload !=''){
            $path_img ="img/student/";
            $type_img = strrchr($_FILES['file']['name'],".");
            $new_img = $numrand_img.$date.$type_img;
            $path_copy_img = $path_img.$new_img;
            $path_link_img = "img/student/".$new_img;
            move_uploaded_file($_FILES['file']['tmp_name'],$path_copy_img);  

            @unlink("img/student/".$file2);
        }else{
            $new_img = $file2;
        }

        $sql = "UPDATE tbl_head SET head_pre = '$prefix', head_name = '$name', head_lname = '$lname'
        , head_sex = '$head_sex', head_img = '$new_img' WHERE head_idnumber = '$idnumber' AND head_class = '$head_class'";
        $result = mysqli_query($conn, $sql);

        echo "<script type='text/javascript'>";
        echo "window.location='help-students-insert-two?idnumber=$idnumber&head_class=$head_class&page=2'";
        echo "</script>";

        }else{

            echo "<script type='text/javascript'>";
            echo "alert('ไฟล์ภาพไม่ถูกต้อง');";
            echo "window.location='javascript:history.back()';";
            echo "</script>";

        }


    echo $output;

    }


    /* ----------------------------------------------------------- */

    require 'vendor/autoload.php';

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    if(isset($_POST['save_excel_data'])) {
        $fileName = $_FILES['import_file']['name'];
        $file_ext = pathinfo($fileName, PATHINFO_EXTENSION);

        $allowed_ext = ['xls', 'csv', 'xlsx'];

        if(in_array($file_ext, $allowed_ext))
        {
            $inputFileNamePath = $_FILES['import_file']['tmp_name'];
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);
            $sheet = $spreadsheet->getActiveSheet();

            $rowStart = 5; // เริ่มอ่านข้อมูลจากแถวที่ 5 (ข้ามหัวตาราง)
            $highestRow = $sheet->getHighestRow();
            $duplicate_ids = []; // เก็บรหัสที่ซ้ำ
            $updated_ids = [];
            $new_ids = [];
            $failed_ids = [];
            $success = false;
            $hasChanges = false; // ติดตามว่ามีการเปลี่ยนแปลงข้อมูลหรือไม่
            $idNumbers = []; // ใช้เก็บรหัสนักเรียนที่เจอในไฟล์ Excel

            // Loop ผ่านทุกแถวในไฟล์ Excel
            for($row = $rowStart; $row <= $highestRow; $row++)
            {
                $head_class = isset($_POST['head_class']) ? $_POST['head_class'] : '';

                $head_idnumber      = $sheet->getCell('A' . $row)->getValue();
                $head_pre           = $sheet->getCell('B' . $row)->getValue();
                $head_name          = $sheet->getCell('C' . $row)->getValue();
                $head_lname         = $sheet->getCell('D' . $row)->getValue();
                $head_sex           = $sheet->getCell('E' . $row)->getValue();
                
                // ตรวจสอบว่ามีรหัสนักเรียนซ้ำใน Excel หรือไม่
                if(in_array($head_idnumber, $idNumbers))
                {
                    // ถ้ามีรหัสซ้ำเก็บไว้ใน $duplicate_ids
                    $duplicate_ids[] = $head_idnumber;
                    continue; // ข้ามไปยังแถวถัดไป
                }

                // เพิ่มรหัสนักเรียนลงในรายการ
                $idNumbers[] = $head_idnumber;

                // ตรวจสอบข้อมูล
                if(empty($head_idnumber))
                {
                    $failed_ids[] = $row; // บันทึกแถวที่ไม่มีรหัสนักเรียน
                    continue;
                }

                /* ------------------------------ ส่วนที่ 1 ------------------------------ */

                // ตรวจสอบว่ามีข้อมูลนี้ในฐานข้อมูลหรือไม่
                $checkQuery = "SELECT * FROM tbl_head WHERE head_idnumber = '$head_idnumber'
                AND head_class = '$head_class'";
                $checkResult = mysqli_query($conn, $checkQuery);

                if($checkResult && mysqli_num_rows($checkResult) > 0)
                {

                    $sql = "UPDATE tbl_head SET head_pre = '$head_pre'
                    , head_name     = '$head_name'
                    , head_lname    = '$head_lname'
                    , head_sex      = '$head_sex' 
                    WHERE head_idnumber = '$head_idnumber' AND head_class = '$head_class'";
                    $result = mysqli_query($conn, $sql);
                    if ($result) {
                        $new_ids[] = $head_idnumber;
                        $success = true;
                        $hasChanges = true;
                    }

                }else{

                    $insertQuery = "INSERT IGNORE INTO tbl_head (head_class
                    , head_idnumber
                    , head_pre
                    , head_name
                    , head_lname
                    , head_sex)
                    VALUES ('$head_class'
                    , '$head_idnumber'
                    , '$head_pre'
                    , '$head_name'
                    , '$head_lname'
                    , '$head_sex')";
                    if (mysqli_query($conn, $insertQuery)) {
                        $new_ids[] = $head_idnumber;
                        $success = true;
                        $hasChanges = true;
                    }

                }


                /* -------------------------------------------------------- */

           

            }

            // แสดงผลลัพธ์หลังการประมวลผลข้อมูล
            if (!$hasChanges) {
                echo "
                <script>
                    setTimeout(function() {
                        swal({
                            title: 'ไม่มีการเปลี่ยนแปลง',
                            text: 'ข้อมูลที่อัปโหลดไม่มีการเปลี่ยนแปลงใดๆ',
                            type: 'info'
                        }, function() {
                            window.location = 'help-students-insert-one?head_class=$head_class';
                        });
                    }, 0);
                </script>";
            } else {
                // หากมีรหัสนักเรียนซ้ำ
                if (!empty($duplicate_ids)) {
                    $duplicates = implode(', ', $duplicate_ids);
                    echo "
                    <script>
                        setTimeout(function() {
                            swal({
                                title: 'รหัสนักเรียนที่ซ้ำ',
                                text: 'รหัสนักเรียนเหล่านี้ซ้ำ: $duplicates',
                                type: 'warning'
                            }, function() {
                                window.location = 'help-students-insert-one?head_class=$head_class';
                            });
                        }, 0);
                    </script>";
                }

                // หากมีการอัปเดตข้อมูล
                if (!empty($updated_ids)) {
                    $updates = implode(', ', $updated_ids);
                    echo "
                    <script>
                        setTimeout(function() {
                            swal({
                                title: 'ข้อมูลอัปเดต',
                                text: 'อัปเดตข้อมูลสำเร็จ',
                                type: 'success'
                            }, function() {
                                window.location = 'help-students-insert-one?head_class=$head_class';
                            });
                        }, 0);
                    </script>";
                }

                // หากมีการเพิ่มข้อมูลใหม่
                if (!empty($new_ids)) {
                    $news = implode(', ', $new_ids);
                    echo "
                    <script>
                        setTimeout(function() {
                            swal({
                                title: 'Success',
                                text: 'เพิ่มข้อมูลใหม่สำเร็จ',
                                type: 'success',
                                buttons: false,
                                timer: 2000
                            }, function() {
                                window.location = 'help-students';
                            });
                        }, 0);
                    </script>";
                }

                // หากพบแถวที่ไม่มีรหัสนักเรียน
                /*if (!empty($failed_ids)) {
                    $failedList = implode(', ', $failed_ids);
                    echo "
                    <script>
                        setTimeout(function() {
                            swal({
                                title: 'ERROR',
                                text: 'เพิ่มข้อมูลไม่สำเร็จสำหรับแถว: $failedList',
                                type: 'error'
                            }, function() {
                                window.location = 'help-students-insert-one?head_class=$head_class';
                            });
                        }, 0);
                    </script>";
                }*/
            }
        } else {
            echo "
            <script>
                setTimeout(function() {
                    swal({
                        title: 'ไม่สำเร็จ',
                        text: 'ไฟล์ไม่ถูกต้อง',
                        type: 'warning'
                    }, function() {
                        window.location = 'help-students-insert-one?head_class=$head_class';
                    });
                }, 0);
            </script>";
        }
    }

?>