<?php

include "../php/database.php";

$start_date = date('2024-09-01');
$end_date = date('2024-10-01');

$startDate = new \DateTime($start_date);
$endDate = new \DateTime($end_date);

$interval = \DateInterval::createFromDateString('1 day');
$period = new \DatePeriod($startDate, $interval, $endDate);

$sql    = "SELECT * FROM users WHERE role = 'Utente' AND enable = 1 AND id = 29";
$result_users = mysqli_query($con, $sql);
while ($users = mysqli_fetch_assoc($result_users)) {

    $total_worked_minutes = 0;
    $total_theorical_minutes    = 0;
    $total_overtired_minutes = 0;
    $total_justification_minutes_1 = 0;
    $total_justification_minutes_2 = 0;
    $total_justification_minutes_3 = 0;
    $total_justification_minutes_4 = 0;
    $total_justification_minutes_5 = 0;
    $total_justification_minutes_6 = 0;
    $total_justification_minutes_7 = 0;
    $total_justification_minutes_8 = 0;
    $total_justification_minutes_9 = 0;
    $total_justification_minutes_10 = 0;
    $total_justification_minutes_11 = 0;
    $total_justification_minutes_12 = 0;

    foreach ($period as $dates) {

        $date = $dates->format('Y-m-d');

        $entry_time_1 = "";
        $entry_time_2 = "";
        $entry_time_3 = "";
        $entry_time_4 = "";
        $exit_time_1 = "";
        $exit_time_2 = "";
        $exit_time_3 = "";
        $exit_time_4 = "";

        $theorical_minutes    = 0;
        $worked_minutes = 0;

        $justification_id_1 = 0;
        $justification_id_2 = 0;
        $justification_id_3 = 0;
        $justification_id_4 = 0;
        $justification_id_5 = 0;
        $justification_id_6 = 0;
        $justification_minutes_1 = 0;
        $justification_minutes_2 = 0;
        $justification_minutes_3 = 0;
        $justification_minutes_4 = 0;
        $justification_minutes_5 = 0;
        $justification_minutes_6 = 0;

        $day_type = "";

        $entry_time = "";
        $exit_time = "";

        $dt1 = strtotime($date);
        $dt2 = date("l", $dt1);
        $dt3 = strtolower($dt2);
        if ($dt3 == "saturday") {
            $day_type = "SA";
            $theorical_minutes = 0;
        } else if ($dt3 == "sunday") {
            $day_type = "DO";
            $theorical_minutes = 0;
        } else {
            $day_type = "GL";
            $theorical_minutes = 480;
            $total_theorical_minutes += 480;
        }

        $sql = "SELECT * FROM stamps WHERE date = '$date' AND user_id = " . $users["id"] . " ORDER BY time ASC";
        $result_stamps = mysqli_query($con, $sql);
        while ($stamp_row = mysqli_fetch_assoc($result_stamps)) {

            if ($stamp_row["type"] == "Entrata") {
                $hour = date("H", strtotime($stamp_row["time"]));
                $minutes = date("i", strtotime($stamp_row["time"]));

                if ($minutes == 00) {
                    $entry_time = $hour . ":00";
                } else if ($minutes > 00 && $minutes <= 15) {
                    $entry_time = $hour . ":15";
                } else if ($minutes > 15 && $minutes <= 30) {
                    $entry_time = $hour . ":30";
                } else if ($minutes > 30 && $minutes <= 45) {
                    $entry_time = $hour . ":45";
                } else if ($minutes > 45 && $minutes < 60) {
                    $entry_time = ($hour + 1) . ":00";
                }

                if ($entry_time_1 == "") {
                    $entry_time_1 = $stamp_row["time"];
                } else if ($entry_time_2 == "") {
                    $entry_time_2 = $stamp_row["time"];
                } else if ($entry_time_3 == "") {
                    $entry_time_3 = $stamp_row["time"];
                } else if ($entry_time_4 == "") {
                    $entry_time_4 = $stamp_row["time"];
                }
            } else if ($stamp_row["type"] == "Uscita") {
                $hour = date("H", strtotime($stamp_row["time"]));
                $minutes = date("i", strtotime($stamp_row["time"]));

                if ($minutes >= 00 && $minutes < 15) {
                    $exit_time =  $hour . ":00";
                } else if ($minutes >= 15 && $minutes < 30) {
                    $exit_time =  $hour . ":15";
                } else if ($minutes >= 30 && $minutes < 45) {
                    $exit_time =  $hour . ":30";
                } else if ($minutes >= 45 && $minutes < 60) {
                    $exit_time =  $hour . ":45";
                }

                if ($exit_time_1 == "") {
                    $exit_time_1 = $stamp_row["time"];
                } else if ($exit_time_2 == "") {
                    $exit_time_2 = $stamp_row["time"];
                } else if ($exit_time_3 == "") {
                    $exit_time_3 = $stamp_row["time"];
                } else if ($exit_time_4 == "") {
                    $exit_time_4 = $stamp_row["time"];
                }

                $exit = date_create($exit_time);
                $entry = date_create($entry_time);
                $interval = date_diff($exit, $entry);
                $work = $interval->days * 24 * 60;
                $work += $interval->h * 60;
                $work += $interval->i;

                $worked_minutes += $work;
            }
        }

        if ($day_type == "GL") {
            $sql = "SELECT * FROM permissions WHERE '$date' BETWEEN start_date AND end_date AND user_id = " . $users["id"] . " AND status_id = 1";
            $result_permissions = mysqli_query($con, $sql);
            while ($permission_row = mysqli_fetch_assoc($result_permissions)) {
                $justification_min = 0;

                if ($permission_row["full_day"] == 1) {
                    $justification_min = 480;
                } else {
                    if ($permission_row["start_date"] == $date && $permission_row["end_date"] != $date) {
                        //Se la data inizio permesso è uguale alla data del ciclo e la fine è dopo faccio la differenza tra ora inizio e 23:59:59
                        $timeObject1 = date_create('23:59:59');
                        $timeObject2 = date_create($permission_row["start_date"]);
                        $interval = date_diff($timeObject1, $timeObject2);
                        $permission_time = $interval->days * 24 * 60;
                        $permission_time += $interval->h * 60;
                        $permission_time += $interval->i;

                        $justification_min = $permission_time;
                    } else if ($permission_row["end_date"] == $date && $permission_row["start_date"] != $date) {
                        //Se la data fine permesso è uguale alla data del ciclo e l'inizio è prima faccio la differenza tra ora fine e 00:00:00
                        $timeObject1 = date_create($permission_row["end_date"]);
                        $timeObject2 = date_create('00:00:00');
                        $interval = date_diff($timeObject1, $timeObject2);
                        $permission_time = $interval->days * 24 * 60;
                        $permission_time += $interval->h * 60;
                        $permission_time += $interval->i;

                        $justification_min = $permission_time;
                    } else if ($permission_row["end_date"] == $date && $permission_row["start_date"] == $date) {
                        //Se la data inizio e fine permesso sono la data del ciclo faccio la differenza tra inizio e fine permesso
                        $timeObject1 = date_create($permission_row["end_date"]);
                        $timeObject2 = date_create($permission_row["start_date"]);
                        $interval = date_diff($timeObject1, $timeObject2);
                        $permission_time = $interval->days * 24 * 60;
                        $permission_time += $interval->h * 60;
                        $permission_time += $interval->i;

                        $justification_min = $permission_time;
                    } else {
                        //Se la data del ciclo è compresa tra la data inizio e fine permesso
                        $justification_min = 480;
                    }
                }

                if ($justification_id_1 == 0) {
                    $justification_id_1 = $permission_row["justification_id"];
                    $justification_minutes_1 = $justification_min;
                } else if ($justification_id_2 == 0) {
                    $justification_id_2 = $permission_row["justification_id"];
                    $justification_minutes_2 = $justification_min;
                } else if ($justification_id_3 == 0) {
                    $justification_id_3 = $permission_row["justification_id"];
                    $justification_minutes_3 = $justification_min;
                } else if ($justification_id_4 == 0) {
                    $justification_id_4 = $permission_row["justification_id"];
                    $justification_minutes_4 = $justification_min;
                } else if ($justification_id_5 == 0) {
                    $justification_id_5 = $permission_row["justification_id"];
                    $justification_minutes_5 = $justification_min;
                } else if ($justification_id_6 == 0) {
                    $justification_id_6 = $permission_row["justification_id"];
                    $justification_minutes_6 = $justification_min;
                }

                if ($permission_row["justification_id"] == 1) {
                    $total_justification_minutes_1 += $justification_min;
                } else if ($permission_row["justification_id"] == 2) {
                    $total_justification_minutes_2 += $justification_min;
                } else if ($permission_row["justification_id"] == 3) {
                    $total_justification_minutes_3 += $justification_min;
                } else if ($permission_row["justification_id"] == 4) {
                    $total_justification_minutes_4 += $justification_min;
                } else if ($permission_row["justification_id"] == 5) {
                    $total_justification_minutes_5 += $justification_min;
                } else if ($permission_row["justification_id"] == 6) {
                    $total_justification_minutes_6 += $justification_min;
                } else if ($permission_row["justification_id"] == 7) {
                    $total_justification_minutes_7 += $justification_min;
                } else if ($permission_row["justification_id"] == 8) {
                    $total_justification_minutes_8 += $justification_min;
                } else if ($permission_row["justification_id"] == 9) {
                    $total_justification_minutes_9 += $justification_min;
                } else if ($permission_row["justification_id"] == 10) {
                    $total_justification_minutes_10 += $justification_min;
                } else if ($permission_row["justification_id"] == 11) {
                    $total_justification_minutes_11 += $justification_min;
                } else if ($permission_row["justification_id"] == 12) {
                    $total_justification_minutes_12 += $justification_min;
                }
            }
        }

        //Inserisco riga della giornata su DB
        $sql_insert = "INSERT INTO attendance_rows VALUES(NULL, 1, " . $users["id"] . ", '" . date("Y-m-d", strtotime($date)) . "', '$entry_time_1','$exit_time_1','$entry_time_2','$exit_time_2','$entry_time_3','$exit_time_3','$entry_time_4','$exit_time_4',$theorical_minutes,$worked_minutes,$justification_id_1,$justification_minutes_1,$justification_id_2,$justification_minutes_2,$justification_id_3,$justification_minutes_3,$justification_id_4,$justification_minutes_4,$justification_id_5,$justification_minutes_5,$justification_id_6,$justification_minutes_6,'$day_type')";
        $con->query($sql_insert);

        $total_worked_minutes += $worked_minutes;

        if ($worked_minutes > $theorical_minutes) {
            $total_overtired_minutes += $worked_minutes - $theorical_minutes;
        }

        echo $date . " --> Teorici: " . $theorical_minutes . "; Lavoro: " . $worked_minutes . "; Straordinari: " . $total_overtired_minutes;
        echo "<br>";
    }

    $progressive = 1;

    if ($total_worked_minutes > 0) {
        //Inserisco riga totale ore lavorate
        $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, 0, $total_worked_minutes,1,0,0)";
        $con->query($sql_insert);
        $progressive++;
    }

    if ($total_theorical_minutes > 0) {
        //Inserisco riga totale ore teoriche
        $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, 0, $total_theorical_minutes,0,1,0)";
        $con->query($sql_insert);
        $progressive++;
    }

    if ($total_overtired_minutes > 0) {
        //Inserisco riga totale ore straordinario
        $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, 0, $total_overtired_minutes,0,0,1)";
        $con->query($sql_insert);
        $progressive++;
    }

    $sql_justifications = "SELECT * FROM justifications";
    $result_justifications = mysqli_query($con, $sql_justifications);
    while ($row_justification = mysqli_fetch_assoc($result_justifications)) {
        if (($row_justification["id"] == 1) && ($total_justification_minutes_1 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_1,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 2) && ($total_justification_minutes_2 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_2,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 3) && ($total_justification_minutes_3 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_3,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 4) && ($total_justification_minutes_4 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_4,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 5) && ($total_justification_minutes_5 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_5,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 6) && ($total_justification_minutes_6 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_6,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 7) && ($total_justification_minutes_7 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_7,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 8) && ($total_justification_minutes_8 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_8,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 9) && ($total_justification_minutes_9 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_9,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 10) && ($total_justification_minutes_10 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_10,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 11) && ($total_justification_minutes_11 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_11,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        } else if (($row_justification["id"] == 12) && ($total_justification_minutes_12 > 0)) {
            //Inserisco riga giustificativo
            $sql_insert = "INSERT INTO attendance_users VALUES(NULL, 1, " . $users["id"] . ", $progressive, " . $row_justification["id"] . ", $total_justification_minutes_12,0,0,0)";
            $con->query($sql_insert);
            $progressive++;
        }
    }
}



$con->close();