<?php
require('../config.php');
require '../../../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);


if (isset($_POST['importEmployeeSubmit'])) {

    // valid extensions
    $extensions = array('xlsx', 'xls');

    $inputFileName = $_FILES['importEmployeeFile']['name'];
    $extension = pathinfo($inputFileName, PATHINFO_EXTENSION);
    if (in_array($extension, $extensions)) {

        $inputFileTempName = $_FILES['importEmployeeFile']['tmp_name'];

        /** Load $inputFileName to a Spreadsheet Object  **/
        $spreadsheet = IOFactory::load($inputFileTempName);
        $data = $spreadsheet->getActiveSheet()->toArray();


        $columnNames = array('employee_id' => 0, 'name' => 1, 'email' => 2, 'phone' => 3, 'gender'=> 4, 'designation' => 5, 'department' => 6, 'leader' => 7, 'role' => 8, 'dob' => 9, 'joining_date' => 10, 'probation_date'=>11, 'con_after_prob'=>12, 'notice_period'=>13, 'working_status' => 14, 'working_status_date' => 15, `pan_no` => 16, 'account_no' => 17, 'ifsc_code' => 18, 'esi_status' => 19, 'esi_no' => 20, 'uan_no_status' => 21, 'uan_no' => 22, 'opting_epf_status' => 23, 'epf_applicable_status' => 24);

        if (sizeof($columnNames) == sizeof($data[0])) {

            foreach ($data[0] as $key => $column) {
                if (array_key_exists(trim($column), $columnNames)) {
                    $columnNames[$column] = $key;
                } else {
                    echo "<script>alert('`$column` is not a valid column name !');</script>";
                    echo "<script>location.href='../importEmployees';</script>";
                    exit();
                }
            }
        } else {
            echo "<script>alert('Please Maintain all Column Names like:- `employee_id`, `name`, `email`, `phone`, `gender`, `designation`, `department`, `leader`, `role`, `dob`, `joining_date`, `probation_date`, `con_after_prob`, `notice_period`, `working_status`, `working_status_date`, `pan_no`, `account_no`, `ifsc_code`, `esi_status`, `esi_no`, `uan_no_status`, `uan_no`, `opting_epf_status`, `epf_applicable_status` ');</script>";
            echo "<script>location.href='../importEmployees';</script>";
            exit();
        }

        array_splice($data, 0, 1);
        $dataLength = sizeof($data);
        $insertCount = 0;
        $empExist = '';
        foreach ($data as $inner) {

            $employee_id = trim($inner[$columnNames['employee_id']]);
            $name = trim($inner[$columnNames['name']]);
            $email = trim($inner[$columnNames['email']]);
            $phone = trim($inner[$columnNames['phone']]);
            $gender = trim($inner[$columnNames['gender']]);
            $designation = trim($inner[$columnNames['designation']]);
            $department = trim($inner[$columnNames['department']]);
            $leader = trim($inner[$columnNames['leader']]);
            $role = trim($inner[$columnNames['role']]);
            $dob = date("Y-m-d",strtotime(trim($inner[$columnNames['dob']])));
            $joining_date = date("Y-m-d",strtotime(trim($inner[$columnNames['joining_date']])));
            $probation_date = date("Y-m-d",strtotime(trim($inner[$columnNames['probation_date']])));
            $con_after_prob = trim($inner[$columnNames['con_after_prob']]);
            $notice_period = trim($inner[$columnNames['notice_period']]);
            $working_status = trim($inner[$columnNames['working_status']]);
            $working_status_date = date("Y-m-d",strtotime(trim($inner[$columnNames['working_status_date']])));
            $pan_no = trim($inner[$columnNames['pan_no']]);
            $account_no = trim($inner[$columnNames['account_no']]);
            $ifsc_code = trim($inner[$columnNames['ifsc_code']]);
            $esi_status = trim($inner[$columnNames['esi_status']]);
            $esi_no = trim($inner[$columnNames['esi_no']]);
            $uan_no_status = trim($inner[$columnNames['uan_no_status']]);
            $uan_no = trim($inner[$columnNames['uan_no']]);
            $opting_epf_status = trim($inner[$columnNames['opting_epf_status']]);
            $epf_applicable_status = trim($inner[$columnNames['epf_applicable_status']]);

            // echo $employee_id . " " . $name . " " . $email . " " . $phone . " " . $designation . " " . $department . " " . $leader . " " . $role . " " . $joining_date . "<br>";

            $eCount = mysqli_num_rows(mysqli_query($db, "SELECT * FROM `users` WHERE `employee_id`='$employee_id' "));
            if ($eCount == 0) {

                $d_exist = mysqli_query($db,"SELECT * FROM departments WHERE department_name='$department' ");
                if(mysqli_num_rows($d_exist)==0){
                    // insert
                    mysqli_query($db,"INSERT INTO departments (department_name) VALUES('$department')");
                    $department = mysqli_insert_id($db);
                }else{
                    $department = mysqli_fetch_assoc($d_exist)['id'];
                }


                $d_exist = mysqli_query($db,"SELECT * FROM designations WHERE designation_name='$designation' ");
                if(mysqli_num_rows($d_exist)==0){
                    // insert
                    mysqli_query($db,"INSERT INTO designations (designation_name) VALUES('$designation')");
                    $designation = mysqli_insert_id($db);
                }else{
                    $designation = mysqli_fetch_assoc($d_exist)['id'];
                }

                $q = mysqli_query($db, "INSERT INTO `users` (`employee_id`, `name`, `email`, `phone`, `gender`, `designation`, `department`, `leader`, `role`, `dob`, `joining_date`, `probation_date`, `con_after_prob`, `notice_period`, `working_status`, `working_status_date`, `pan_no`, `account_no`, `ifsc_code`, `esi_status`, `esi_no`, `uan_no_status`, `uan_no`, `opting_epf_status`, `epf_applicable_status`) VALUES ('$employee_id', '$name', '$email', '$phone', '$gender', '$designation', '$department', '$leader', '$role', '$dob', '$joining_date', '$probation_date', '$con_after_prob', '$notice_period', '$working_status', '$working_status_date', '$pan_no', '$account_no', '$ifsc_code', '$esi_status', '$esi_no', '$uan_no_status', '$uan_no', '$opting_epf_status', '$epf_applicable_status' ) ");
            } else {
                $empExist .= $employee_id . ", ";
                $q = false;
            }

            if ($q) {
                $insertCount++;
            }
        }

        if ($dataLength == $insertCount) {
            echo "<script>alert('All Data imported Successfully !');</script>";
        } else if (!empty($empExist)) {
            echo "<script>alert('" . $empExist . " These Employee id data already exist in database and other data imported Successfully !');</script>";
        } else {
            echo "<script>alert('" . ($dataLength - $insertCount) . " rows not imported !');</script>";
        }

        echo "<script>location.href='../importEmployees';</script>";
        exit();
    } else {
        echo "<script>alert('Invalid File Formate, Please upload only .xlsx, .xls');</script>";
        echo "<script>location.href='../importEmployees';</script>";
        exit();
    }
}
