<?php
    // session_start();
    include "../../db.php";
    require '../../auth.php';
    restrictAccess(['master_admin']);
    date_default_timezone_set('Asia/Kolkata');
    $currentTime = date('M j, Y g:i a');

    if ( !isset($_SESSION['uid']) && !isset($_SESSION['uref']) && !isset($_SESSION['utype']) ) {
        header("Location: logout.php");
        exit();
    }

    if ( $_SESSION['utype'] != 'master_admin' && $_SESSION['uref'] != 'fssdf4543frts' ) {
        header("Location: logout.php");
        exit();
    }
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <?php include "include/meta.php"; ?>
    <?php include "include/css.php"; ?>
    <?php include "include/php-functions.php"; ?>
</head>

<body>
    <!--  Header -->
    <?php 
    include "include/header.php"; 
    ?>
    <!-- #header -->

    <section id="inner-body">
        <div class="body-content">
            <div class="container-fluid">
                <div class="card mt-3">
                    <h5 class="card-header text-dark text-center font-weight-bold">Data Upload</h5>
                    <div class="card-body">
                        <a href="upload/excelUpload-Sample.xlsx" download class="badge badge-secondary text-center"
                            style="background: green;">Click to Download Sample File</a>
                        <form class="user" method="post" action="" enctype="multipart/form-data">
                            <div class="row justify-content-center ">
                                <div class="col-md-5 ">
                                    <div class=" mb-1 mb-sm-0">
                                        <b>Member/Agent </b>
                                        <select name="MemberId" id="MemberId" class="form-control mb-3">
                                            <option value="">Select Member/Agent</option>
                                            <?php 
                                                $sql = "SELECT `uid`,`uname` FROM users WHERE udisplay != 'disable' AND uStatus = '1' AND utype='Associate' AND `uadmin` = '$_SESSION[uref]' ORDER BY `uname` ASC";
                                                $run = mysqli_query($con, $sql);
                                                while ( $rows = mysqli_fetch_assoc($run) ) {
                                                    echo "<option value='$rows[uid]'>$rows[uname]</option>";
                                                }
                                            ?>
                                        </select>
                                    </div>
                                    <div class="mb-1 mb-sm-0">
                                        <b>Department <span style="color: red;">*</span> </b>
                                        <select name="DepartmentId" id="DepartmentId" class="form-control mb-3"
                                            required>
                                            <option value="">Select Department</option>
                                            <?php 
                                                $sql = "SELECT * FROM clg_course_cat WHERE recycle ='1' ORDER BY sl_no ASC";
                                                $run = mysqli_query($con, $sql);
                                                while ( $rows = mysqli_fetch_assoc($run) ) {
                                                    echo "<option value='$rows[sl_no]'>$rows[cat_name]</option>";
                                                }
                                            ?>
                                        </select>
                                    </div>
                                    <div class="mb-1 mb-sm-0">
                                        <b>Status <span style="color: red;">*</span> </b>
                                        <select name="statusId" id="statusId" class="form-control mb-3" required>
                                            <?php 
                                                $sql = "SELECT * FROM status WHERE s_by = '$_SESSION[uref]' ORDER BY s_pos ASC";
                                                $run = mysqli_query($con, $sql);
                                                while ( $rows = mysqli_fetch_assoc($run) ) {
                                                    echo "<option value='$rows[s_id]'>$rows[s_name]</option>";
                                                }
                                            ?>
                                        </select>
                                    </div>

                                    <div class="mb-1 mb-sm-0">
                                        <b>Upload Excel <span style="color: red;">*</span> </b>
                                        <input type="file"
                                            style="background-color:green; border-radius:5px; border: 1px solid Green; padding:6px 5px; color:white;"
                                            name="excel" required value="">
                                    </div>
                                    <div class="mb-1 mt-5 mb-sm-0" align="center">
                                        <button type="submit" class="btn btn-success btn-user m-2"
                                            style="background-color:green; padding:6px 25px;" name="import"> Import
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>

        <?php include "include/footer.php"; ?>
    </section>

    <!-- JavaScript Libraries -->

    <?php include "include/js.php"; ?>


</body>

</html>

<?php
if(isset($_POST["import"])) {
    require 'xcelr/excelReader/excel_reader2.php';
    require 'xcelr/excelReader/SpreadsheetReader.php';

    $st_by = $_POST["MemberId"];
    $st_category = $_POST["DepartmentId"];
    $st_status = $_POST["statusId"];
    $st_admin = $_SESSION['uref'];

    $fileName = $_FILES["excel"]["name"];
    $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
    $newFileName = date("Y.m.d") . " - " . date("h.i.sa") . "." . $fileExtension;
    $targetDirectory = "uploads/" . $newFileName;

    move_uploaded_file($_FILES['excel']['tmp_name'], $targetDirectory);

    error_reporting(0);
    ini_set('display_errors', 1);

    $reader = new SpreadsheetReader($targetDirectory);
    $allDataStatus = true;

    $currentTime = date('Y-m-d H:i:s'); // Define current timestamp

    foreach ($reader as $key => $row) {
        if ($key > 0) { // Skip header row

            // Ensure indexes exist to avoid "Undefined index" errors
            $st_name = isset($row[1]) ? trim($row[1]) : ''; 
            $st_ph = isset($row[2]) ? trim($row[2]) : ''; 
            $st_wp_no = isset($row[3]) ? trim($row[3]) : ''; 
            $st_email = isset($row[4]) ? trim($row[4]) : ''; 
            $st_address = isset($row[5]) ? trim($row[5]) : ''; 
            $st_city = isset($row[6]) ? trim($row[6]) : ''; 
            $st_dist = isset($row[7]) ? trim($row[7]) : ''; 
            $st_about = isset($row[8]) ? trim($row[8]) : ''; 
            $st_about2 = isset($row[9]) ? trim($row[9]) : '';
            $st_about3 = isset($row[10]) ? trim($row[10]) : '';
            $st_about4 = isset($row[11]) ? trim($row[11]) : '';
            $st_about5 = isset($row[12]) ? trim($row[12]) : '';
            $st_about6 = isset($row[13]) ? trim($row[13]) : '';
            $st_about7 = isset($row[14]) ? trim($row[14]) : '';
            $st_about8 = isset($row[15]) ? trim($row[15]) : '';

            // Validate required fields (NAME and PHONE NO)
            if (!empty($st_name) && !empty($st_ph)) {
                $excel_sql = "INSERT INTO `student_details` 
                (`st_name`, `st_ph`, `st_wp_no`, `st_by`, `st_admin`, `st_status`, `st_category`, 
                 `st_email`, `st_city`, `st_dist`, `st_address`, 
                 `st_about`, `st_about2`, `st_about3`, `st_about4`, `st_about5`, `st_about6`, `st_about7`, `st_about8`, `uploadDT`) 
                VALUES
                ('$st_name', '$st_ph', '$st_wp_no', '$st_by', '$st_admin', '$st_status', '$st_category',
                 '$st_email', '$st_city', '$st_dist', '$st_address',
                 '$st_about', '$st_about2', '$st_about3', '$st_about4', '$st_about5', '$st_about6', '$st_about7', '$st_about8', '$currentTime')";

                if (!mysqli_query($con, $excel_sql)) {
                    $allDataStatus = false;
                }
            } else {
                $allDataStatus = false;
            }
        }
    }

    if ($allDataStatus == true) {
        echo "<script>
            alert('Successfully Imported');
            document.location.href = '';
        </script>";
    } else {
        echo "<script>
            alert('Some rows failed to import due to missing required fields.');
            document.location.href = '';
        </script>";
    }
}
?>