<?php
require_once "../config.php";

$data = json_decode(file_get_contents("php://input"), true);

if (isset($data['selectedMonth'])) {

    $firstDate = date("Y-m-01", strtotime($data['selectedMonth']));
    $lastDate = date("Y-m-t", strtotime($data['selectedMonth']));
    $currMonth = date("Y-m");
    $getMonth = date("Y-m", strtotime($data['selectedMonth']));
    $final_data = array();


    $users = mysqli_query($db,"SELECT 
	employee_id, 
    (SELECT name FROM users WHERE employee_id=a.employee_id) as name,
    COUNT(*) AS days_count,
    SUM(CASE 
        WHEN attendance_status = 'Present' AND attendance_change=0 THEN 1 
        WHEN attendance_status = 'Work From Home' THEN 1 
        ELSE 0 
        END) AS present_count,
    SUM(CASE 
        WHEN attendance_status = 'Absent' AND attendance_change=0 THEN 1 
        WHEN attendance_status = 'Half Day' AND attendance_change=0 THEN 0.5
        ELSE 0 
        END) AS absent_count,
    SUM(CASE 
        WHEN attendance_status = 'Present' AND attendance_change=1 THEN 1 
        ELSE 0 
        END) AS present_not_count,
    
    TIME_FORMAT(
        SEC_TO_TIME(
            SUM(
                TIME_TO_SEC(
                    TIME_FORMAT(TIMEDIFF(
                        IF(str_to_date(punch_out, '%h:%i %p') < str_to_date(punch_in, '%h:%i %p'), 
                            DATE_ADD(str_to_date(punch_out, '%h:%i %p'), INTERVAL 12 HOUR), 
                            str_to_date(punch_out, '%h:%i %p')
                        ), 
                    str_to_date(punch_in, '%h:%i %p')
        		), '%H:%i')
            )
        )
    ),'%H:%i') AS hours_worked
FROM attendance a
WHERE str_to_date(which_month,'%M %d %Y') BETWEEN '$firstDate' AND '$lastDate'
GROUP BY employee_id  
HAVING hours_worked>'00:00'
ORDER BY present_count DESC, absent_count ASC, present_not_count ASC, hours_worked DESC LIMIT 20;");

    while($user = mysqli_fetch_assoc($users)){
        $events = array();
        $eid = $user['employee_id'];
        $total_hours = $user['hours_worked'];
        $name = $user['name'];


        // Show attendance ##########################
        $bgcolor = array('Present' => 'bg-success', 'Half Day' => 'bg-warning', 'Work From Home' => 'bg-primary', 'Absent' => 'bg-danger', 'Week Off' => 'bg-secondary', 'Holiday' => 'bg-secondary');
    
        $q = mysqli_query($db, "SELECT * FROM `attendance` WHERE `employee_id`='$eid' and (str_to_date(which_month,'%M %d %Y')>='$firstDate' and str_to_date(which_month,'%M %d %Y')<='$lastDate')  ORDER BY `which_month` ASC ");
    
        while ($row = mysqli_fetch_assoc($q)) {
            $title =  $row['attendance_status'];
            $start =  date("Y-m-d", strtotime($row['which_month']));
            if ($row['attendance_change'] == 1) {
                $className = 'attendance-change-bg';
            } else {
                $className = $bgcolor[$title];
            }
            $event = array(
                'id' => $row['id'],
                'title' => $title,
                'start' => $start,
                'className' => $className
            );
            array_push($events, $event);
        }
        // ##############################################

        array_push($final_data,array('name'=>$name, 'total_hours'=>$total_hours, 'events'=>$events));
        
    }


    
    // $lAD = mysqli_fetch_assoc(mysqli_query($db, "select max(str_to_date(which_month,'%M %d %Y')) as max_date from attendance "));

    echo json_encode($final_data);

}


