<?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['importAttendanceSubmit'])) {

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

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


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

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


        $columnNames = array('employee_id' => 0, 'attendance_status' => 1, 'punch_in' => 2, 'punch_out' => 3, 'which_month' => 4);

        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='../importAttendance';</script>";
                    exit();
                }
            }
        } else {
            echo "<script>alert('Please Maintain all Column Names like:- `employee_id`, `attendance_status`, `punch_in`, `punch_out`, `which_month` ');</script>";
            echo "<script>location.href='../importAttendance';</script>";
            exit();
        }


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

            $employee_id = trim($inner[$columnNames['employee_id']]);
            $attendance_status = trim($inner[$columnNames['attendance_status']]);
            $punch_in = trim($inner[$columnNames['punch_in']]);
            $punch_out = trim($inner[$columnNames['punch_out']]);
            $which_month = trim($inner[$columnNames['which_month']]);
            $attendance_change = 0;
            
            if(!empty($punch_in)){
                $punch_in = date("h:i A", strtotime($punch_in));
            }
            if(!empty($punch_out)){
                $punch_out = date("h:i A", strtotime($punch_out));
            }
            if(!empty($which_month)){
                $which_month = date("M d Y", strtotime($which_month));
            }

            // echo $employee_id . " " . $attendance_status . " " . $punch_in . " " . $punch_out . " " . $which_month . "<br>";

            // ##################################################################
            $jwDate = mysqli_fetch_assoc(mysqli_query($db, "SELECT joining_date, working_status_date, employee_time FROM users WHERE employee_id='$employee_id' "));
            if (!empty($jwDate['joining_date']) && (strtotime($jwDate['joining_date']) > strtotime($which_month))) {
                continue;
            }
            if (!empty($jwDate['working_status_date']) && (strtotime($jwDate['working_status_date']) < strtotime($which_month))) {
                continue;
            }
            // ##################################################################
            
            $aCount = mysqli_num_rows(mysqli_query($db, "SELECT * FROM `attendance` WHERE `employee_id`='$employee_id' AND str_to_date(`which_month`, '%M %d %Y')=str_to_date('$which_month', '%M %d %Y') "));
            
            $q = false;
            if ($aCount == 0) {
                
                $policy_applicable_from = date("Y-m", strtotime($which_month));
                $work_policy = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM work_policy WHERE employee_id='$employee_id' AND applicable_from<='$policy_applicable_from' AND (CASE WHEN COALESCE(applicable_to, '')='' THEN DATE_FORMAT(CURRENT_DATE, '%Y-%m') ELSE applicable_to END)>=DATE_FORMAT(CURRENT_DATE, '%Y-%m') "));
                
                if(empty($work_policy)){
                    $check_punch_in = '10:00 AM';
                    $check_punch_out = '6:15 PM';
                    $check_hours = '08:50';
                }else{
                    $check_punch_in = date("h:i A", strtotime($work_policy['relaxation_from']));
                    $check_punch_out = date("h:i A", strtotime($work_policy['relaxation_to']));
                    $check_hours = date("H:i", (strtotime($check_punch_out) - strtotime($check_punch_in)));
                }

                // Convert working hours to seconds for proper comparison
                $punch_in_time  = strtotime($punch_in);
                $punch_out_time = strtotime($punch_out);
                $check_in_time  = strtotime($check_punch_in);
                $check_out_time = strtotime($check_punch_out);
                $worked_seconds = $punch_out_time - $punch_in_time;

                // Convert HH:MM string to seconds
                list($h, $m) = explode(':', $check_hours);
                $check_hours_seconds = ($h * 3600) + ($m * 60);
                

                // $leaveDate = date("d M Y", strtotime($which_month));
                $working_days = json_decode($work_policy['working_days'], true);
                $dayOfWeek = date('l', strtotime($which_month));
                if (is_array($working_days) && !empty($working_days)) {
                    if (!in_array($dayOfWeek, $working_days)) {
                        if($punch_in == '' && $punch_out == ''){
                            $attendance_status = 'Week Off';
                        } else if($punch_in == '' || $punch_out == '' || ($punch_in_time > $check_in_time) || ($punch_out_time < $check_out_time)) {
                            // $attendance_status = 'Worked on Week Off HD';
                        } else {
                            // $attendance_status = 'Worked on Week Off';
                        }
                    }else{
                        if ($punch_in == '' && $punch_out == '') {
                            $attendance_status = 'Absent';
                        }else if ($punch_in == '' || $punch_out == '') {
                            $attendance_status = 'Half Day';
                        } else if (($punch_in_time > $check_in_time) || ($punch_out_time < $check_out_time)) {
                            $attendance_status = 'Half Day';
                        } else if ($worked_seconds < $check_hours_seconds) {
                            $attendance_status = 'Half Day';
                        } else {
                            $attendance_status = 'Present';
                        }
                    }
                }else{
                    if($dayOfWeek=='Sunday'){
                        if($punch_in == '' && $punch_out == ''){
                            $attendance_status = 'Week Off';
                        } else if($punch_in == '' || $punch_out == '' || (($punch_in_time > $check_in_time) || ($punch_out_time < $check_out_time))) {
                            // $attendance_status = 'Worked on Week Off HD';
                        } else {
                            // $attendance_status = 'Worked on Week Off';
                        }
                    } else{
                        if ($punch_in == '' && $punch_out == '') {
                            $attendance_status = 'Absent';
                        }else if ($punch_in == '' || $punch_out == '') {
                            $attendance_status = 'Half Day';
                        } else if (($punch_in_time > $check_in_time) || ($punch_out_time < $check_out_time)) {
                            $attendance_status = 'Half Day';
                        } else if ($worked_seconds < $check_hours_seconds) {
                            $attendance_status = 'Half Day';
                        } else {
                            $attendance_status = 'Present';
                        }
                    }
                }


                $requestLeave = mysqli_query($db, "SELECT * from request_leave where employee_id='$employee_id' and leave_status='1' and leave_type='Work From Home' and created_at>now() - interval 30 day ");
                if (mysqli_num_rows($requestLeave) > 0) {

                    while ($reqLeave = mysqli_fetch_assoc($requestLeave)) {

                        if (strpos($reqLeave['leave_date'], 'to') == false) {

                            if (strtotime($reqLeave['leave_date']) == strtotime($which_month)) {
                                $gid = mysqli_fetch_assoc(mysqli_query($db, "select id from attendance order by id desc limit 1"));
                                $gid = $gid['id']++;
                                $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values ('$gid','$employee_id', '$attendance_status', '$punch_in', '$punch_out', '$which_month')  ");
                                $attendance_change = 1;
                                $attendance_status = 'Work From Home';
                                break;
                            }
                        } else {
                            $exDate = explode("to", $reqLeave['leave_date']);

                            if ((strtotime(trim($exDate[0])) <= strtotime($which_month)) && (strtotime(trim($exDate[1])) >= strtotime($which_month))) {
                                $gid = mysqli_fetch_assoc(mysqli_query($db, "select id from attendance order by id desc limit 1"));
                                $gid = $gid['id']++;
                                $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values ('$gid','$employee_id', '$attendance_status', '$punch_in', '$punch_out', '$which_month')  ");
                                $attendance_change = 1;
                                $attendance_status = 'Work From Home';
                                break;
                            }
                        }
                    }
                }

                $holidays = mysqli_num_rows(mysqli_query($db, "SELECT * from holidays where date='$which_month' "));
                if ($holidays == 1) {
                    $attendance_status = 'Holiday';
                }

                $q = mysqli_query($db, "INSERT INTO `attendance` (`employee_id`, `attendance_status`, `punch_in`, `punch_out`, `which_month`, `attendance_change`) VALUES ('$employee_id', '$attendance_status', '$punch_in', '$punch_out', '$which_month', $attendance_change) ");
            } else {
                $empExist .= $employee_id . ", ";
                $q = false;
            }

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

        if ($dataLength == $insertCount) {
            echo "<script>alert('All Data imported Successfully !');</script>";
        } else if (!empty(trim($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='../importAttendance';</script>";
        exit();
    } else {
        echo "<script>alert('Invalid File Formate, Please upload only .xlsx, .xls');</script>";
        echo "<script>location.href='../importAttendance';</script>";
        exit();
    }
}
