<?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>";

    }

}

?>