query($sql_delete);
$sql_delete = "DELETE FROM attendance_rows WHERE attendance_id = $id";
$con->query($sql_delete);
$sql_delete = "DELETE FROM attendance_justifications_temp WHERE attendance_id = $id";
$con->query($sql_delete);
//Prendo i dati del cartellino
$sql_attendance = "SELECT * FROM attendance WHERE id = $id";
$result_attendance = mysqli_query($con, $sql_attendance);
$attendance = mysqli_fetch_assoc($result_attendance);
//Imposto le date
$start_date_db = '' . $attendance["year"] . '-' . $attendance["month"] . '-01';
$end_date_db = '' . $attendance["year"] . '-' . ($attendance["month"] + 1) . '-01';
$start_date = date($start_date_db);
$end_date = date($end_date_db);
echo "Data inizio calcolo: " . $start_date . "
";
echo "Data fine calcolo: " . $end_date . "
";
$night_start = "22:00:00";
$night_start_time = date("H:i:s", strtotime($night_start));
$night_end = "05:59:59";
$night_end_time = date("H:i:s", strtotime($night_end));
$day_start = "06:00:00";
$day_start_time = date("H:i:s", strtotime($day_start));
$day_end = "21:59:59";
$day_end_time = date("H:i:s", strtotime($day_end));
echo "Ora inizio orario notturno: " . $night_start_time . "
";
echo "Ora fine orario notturno: " . $night_end_time . "
";
$day_minutes = 480;
$startDate = new \DateTime($start_date);
$endDate = new \DateTime($end_date);
$interval = \DateInterval::createFromDateString('1 day');
$period = new \DatePeriod($startDate, $interval, $endDate);
//Ciclo gli utenti attivi
$sql = "SELECT * FROM users WHERE role = 'Utente' AND id = 29 AND enable = 1";
$result_users = mysqli_query($con, $sql);
while ($users = mysqli_fetch_assoc($result_users)) {
echo "Utente: " . $users["name"] . " " . $users["surname"] . "
";
//Dichiaro/pulisco le variabili
$total_theorical_minutes = 0;
$total_day_worked_minutes = 0;
$total_night_worked_minutes = 0;
$total_holiday_worked_minutes = 0;
$total_holiday_night_worked_minutes = 0;
$total_day_overtime_minutes = 0;
$total_night_overtime_minutes = 0;
$total_holiday_overtime_minutes = 0;
$total_holiday_night_overtime_minutes = 0;
$user_id = $users["id"];
//Ciclo i giorni del mese
foreach ($period as $dates) {
//Dichiaro/pulisco le variabili
$date = $dates->format('Y-m-d');
echo "Giorno: " . $date . "
";
$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;
$day_worked_minutes = 0;
$night_worked_minutes = 0;
$holiday_worked_minutes = 0;
$holiday_night_worked_minutes = 0;
$day_overtime_minutes = 0;
$night_overtime_minutes = 0;
$holiday_overtime_minutes = 0;
$holiday_night_overtime_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 = "";
$index = 0;
$last_stamp_type = "";
$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;
}
echo "Tipo giorno: " . $day_type . "
";
//Ciclo le timbrature dell'utente nel giorno
$sql = "SELECT * FROM stamps WHERE date = '$date' AND user_id = " . $user_id . " ORDER BY time ASC";
$result_stamps = mysqli_query($con, $sql);
while ($stamp_row = mysqli_fetch_assoc($result_stamps)) {
//Imposto le variabili
$stamp_type = $stamp_row["type"];
$stamp_time = $stamp_row["time"];
$last_stamp_type = $stamp_type;
if ($stamp_type == "Entrata") {
//Modifico l'orario con la gestione a 15 minuti
$entry_time = FormatEntryTimeBy15Minutes($stamp_time);
echo " | " . $stamp_type . " - Ora: " . $entry_time . "";
} else {
//Modifico l'orario con la gestione a 15 minuti
$exit_time = FormatExitTimeBy15Minutes($stamp_time);
echo " & " . $stamp_type . " - Ora: " . $exit_time . "";
}
if (($index == 0) && ($stamp_type == "Uscita")) {
//Se la prima timbratura del giorno è un'uscita
//Imposto le variabili
$entry_time_1 = "00:00:00";
$exit_time_1 = $exit_time;
$index++;
//Controllo fasce orarie
if ($exit_time <= $night_end_time) {
//Se l'orario di uscita è minore o uguale della fine dell'orario notturno
$minutes = CalcMinutesBetweenTimes("00:00:00", $exit_time);
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
} else {
//Se l'orario di uscita è maggiore della fine dell'orario notturno
$minutes = CalcMinutesBetweenTimes("00:00:00", $night_end_time);
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
$minutes = CalcMinutesBetweenTimes($day_start_time, $exit_time);
if ($day_type == "GL") {
$day_worked_minutes += $minutes;
echo " - Lavoro diurno: " . $minutes;
} else {
$holiday_worked_minutes += $minutes;
echo " - Lavoro festivo diurno: " . $minutes;
}
}
} else if ($stamp_type == "Entrata") {
//Se la timbratura è entrata
//Imposto le variabili
if ($entry_time_1 == "") {
$entry_time_1 = $stamp_time;
} else if ($entry_time_2 == "") {
$entry_time_2 = $stamp_time;
} else if ($entry_time_3 == "") {
$entry_time_3 = $stamp_time;
} else if ($entry_time_4 == "") {
$entry_time_4 = $stamp_time;
}
$index++;
} else if ($stamp_type == "Uscita") {
//Se la timbratura è uscita
//Imposto le variabili
if ($exit_time_1 == "") {
$exit_time_1 = $stamp_time;
} else if ($exit_time_2 == "") {
$exit_time_2 = $stamp_time;
} else if ($exit_time_3 == "") {
$exit_time_3 = $stamp_time;
} else if ($exit_time_4 == "") {
$exit_time_4 = $stamp_time;
}
$index++;
//Controllo fasce orarie
if (($entry_time >= "00:00:00") && ($entry_time <= $night_end_time)) {
//Se l'orario di entrata è mattino notturno
if ($exit_time <= $night_end_time) {
//Se l'orario di entrata è mattino notturno e l'orario di uscita è mattino notturno
$minutes = CalcMinutesBetweenTimes($entry_time, $exit_time);
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
} else {
//Se l'orario di entrata è mattino notturno e l'orario di uscita è diurno
$minutes = CalcMinutesBetweenTimes($entry_time, $night_end_time);
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
$minutes = CalcMinutesBetweenTimes($day_start_time, $exit_time);
if ($day_type == "GL") {
$day_worked_minutes += $minutes;
echo " - Lavoro diurno: " . $minutes;
} else {
$holiday_worked_minutes += $minutes;
echo " - Lavoro festivo diurno: " . $minutes;
}
}
} else if (($entry_time >= $day_start_time) && ($entry_time < $night_start_time)) {
//Se l'orario di entrata è diurno
if ($exit_time < $night_start_time) {
//Se l'orario di entrata è diurno e l'orario di uscita è diurno
$minutes = CalcMinutesBetweenTimes($entry_time, $exit_time);
if ($day_type == "GL") {
$day_worked_minutes += $minutes;
echo " - Lavoro diurno: " . $minutes;
} else {
$holiday_worked_minutes += $minutes;
echo " - Lavoro festivo diurno: " . $minutes;
}
} else {
//Se l'orario di entrata è diurno e l'orario di uscita è serale notturno
$minutes = CalcMinutesBetweenTimes($entry_time, $day_end_time);
if ($day_type == "GL") {
$day_worked_minutes += $minutes;
echo " - Lavoro diurno: " . $minutes;
} else {
$holiday_worked_minutes += $minutes;
echo " - Lavoro festivo diurno: " . $minutes;
}
$minutes = CalcMinutesBetweenTimes($night_start_time, $exit_time);
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
}
}
}
}
if ($last_stamp_type == "Entrata") {
//Se l'ultima timbratura della giornata è entrata
if ($entry_time < $night_start_time) {
//Se l'ultima timbratura della giornata è entrata e l'orario è diurno
$minutes = CalcMinutesBetweenTimes($entry_time, $day_end_time);
if ($day_type == "GL") {
$day_worked_minutes += $minutes;
echo " - Lavoro diurno: " . $minutes;
} else {
$holiday_worked_minutes += $minutes;
echo " - Lavoro festivo diurno: " . $minutes;
}
$minutes = CalcMinutesBetweenTimes($night_start_time, "23:59:59");
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
} else {
//Se l'ultima timbratura della giornata è entrata e l'orario è notturno
$minutes = CalcMinutesBetweenTimes($night_start_time, "23:59:59");
if ($day_type == "GL") {
$night_worked_minutes += $minutes;
echo " - Lavoro notturno: " . $minutes;
} else {
$holiday_night_worked_minutes += $minutes;
echo " - Lavoro festivo notturno: " . $minutes;
}
}
}
echo "
Minuti lavorati nel giorno: " . ($day_worked_minutes + $holiday_worked_minutes + $night_worked_minutes + $holiday_night_worked_minutes) . "
";
//CALCOLO STRAORDINARI
//Se è un giorno lavorativo
if ($day_type == "GL") {
//Se la somma dei minuti lavorati è maggiore dei minuti previsti per il giorno
if (($day_worked_minutes + $night_worked_minutes) > $day_minutes) {
//Se i minuti lavorati di giorno sono maggiori dei minuti lavorati di notte
if ($day_worked_minutes > $night_worked_minutes) {
$day_overtime_minutes = ($day_worked_minutes + $night_worked_minutes) - $day_minutes;
$night_overtime_minutes = 0;
} else {
//Se i minuti lavorati di notte sono maggiori dei minuti lavorati di giorno
$night_overtime_minutes = ($day_worked_minutes + $night_worked_minutes) - $day_minutes;
$day_overtime_minutes = 0;
}
}
} else {
//Se è un giorno festivo
//Se la somma dei minuti lavorati è maggiore dei minuti previsti per il giorno
if (($day_worked_minutes + $night_worked_minutes) > $day_minutes) {
//Se i minuti lavorati di giorno sono maggiori dei minuti lavorati di notte
if ($day_worked_minutes > $night_worked_minutes) {
$holiday_overtime_minutes = ($day_worked_minutes + $night_worked_minutes) - $day_minutes;
$holiday_night_overtime_minutes = 0;
} else {
//Se i minuti lavorati di notte sono maggiori dei minuti lavorati di giorno
$holiday_night_overtime_minutes = ($day_worked_minutes + $night_worked_minutes) - $day_minutes;
$holiday_overtime_minutes = 0;
}
}
}
//Inserisco nella tabella temporanea i giustificativi
if ($day_overtime_minutes > 0) {
$sql_insert = "INSERT INTO attendance_justifications_temp VALUES(NULL, $id,$user_id,24, $day_overtime_minutes,'$date')";
$con->query($sql_insert);
}
if ($night_overtime_minutes > 0) {
$sql_insert = "INSERT INTO attendance_justifications_temp VALUES(NULL, $id,$user_id,26, $night_overtime_minutes,'$date')";
$con->query($sql_insert);
}
if ($holiday_overtime_minutes > 0) {
$sql_insert = "INSERT INTO attendance_justifications_temp VALUES(NULL, $id,$user_id,25, $holiday_overtime_minutes,'$date')";
$con->query($sql_insert);
}
if ($holiday_night_overtime_minutes > 0) {
$sql_insert = "INSERT INTO attendance_justifications_temp VALUES(NULL, $id,$user_id,28, $holiday_night_overtime_minutes,'$date')";
$con->query($sql_insert);
}
echo "STRAORDINARI - Diurno: " . $day_overtime_minutes . " | Notturno: " . $night_overtime_minutes . " | Festivo diurno: " . $holiday_overtime_minutes . " | Festivo notturno: " . $holiday_night_overtime_minutes;
//Controllo i permessi
if ($day_type == "GL") {
//Se è un giorno lavorativo ciclo i permessi del giorno
$sql = "SELECT * FROM permissions WHERE '$date' BETWEEN start_date AND end_date AND user_id = " . $user_id . " AND status_id = 2";
$result_permissions = mysqli_query($con, $sql);
while ($permission_row = mysqli_fetch_assoc($result_permissions)) {
//Dichiaro le variabili
$permission_justification_id = $permission_row["justification_id"];
$permission_full_day = $permission_row["full_day"];
$permission_start_date = $permission_row["start_date"];
$permission_start_time = $permission_row["start_time"];
$permission_end_date = $permission_row["end_date"];
$permission_end_time = $permission_row["end_time"];
$justification_min = 0;
$minutes = 0;
if ($permission_full_day == 1) {
//Se il permesso ha giornata intera
//Conto 8 ore
$justification_min = 480;
} else {
//Se non è giornata intera
if ($permission_start_date == $date && $permission_end_date != $date) {
//Se la data inizio permesso è uguale alla data del ciclo e la fine è dopo
//Calcolo la differenza tra ora inizio e 23:59:59
$minutes = CalcMinutesBetweenTimes($permission_start_time, "23:59:59");
$justification_min = $minutes;
} else if ($permission_end_date == $date && $permission_start_date != $date) {
//Se la data fine permesso è uguale alla data del ciclo e l'inizio è prima
//Calcolo la differenza tra ora fine e 00:00:00
$minutes = CalcMinutesBetweenTimes("00:00:00", $permission_end_time);
$justification_min = $minutes;
} else if ($permission_end_date == $date && $permission_start_date == $date) {
//Se la data inizio e fine permesso sono la data del ciclo
//Calcolo la differenza tra inizio e fine permesso
$minutes = CalcMinutesBetweenTimes($permission_start_time, $permission_end_time);
$justification_min = $minutes;
} else {
//Se la data del ciclo è compresa tra la data inizio e fine permesso
//Conto 8 ore
$justification_min = 480;
}
}
//Inserisco nella tabella temporanea il permesso con i minuti per ogni giorno
$sql_insert = "INSERT INTO attendance_justifications_temp VALUES(NULL, $id,$user_id,$permission_justification_id, $justification_min,'$date')";
$con->query($sql_insert);
}
}
//Calcolo totale minuti di permessi nella giornata
$sql = "SELECT SUM(minutes) AS justification_minutes, justification_id FROM attendance_justifications_temp WHERE date = '$date' AND user_id = $user_id AND attendance_id = $id GROUP BY justification_id";
$result_justifications_temp = mysqli_query($con, $sql);
while ($justifications_temp = mysqli_fetch_assoc($result_justifications_temp)) {
if ($justification_id_1 == 0) {
$justification_id_1 = $justifications_temp["justification_id"];
$justification_minutes_1 = $justifications_temp["justification_minutes"];
} else if ($justification_id_2 == 0) {
$justification_id_2 = $justifications_temp["justification_id"];
$justification_minutes_2 = $justifications_temp["justification_minutes"];
} else if ($justification_id_3 == 0) {
$justification_id_3 = $justifications_temp["justification_id"];
$justification_minutes_3 = $justifications_temp["justification_minutes"];
} else if ($justification_id_4 == 0) {
$justification_id_4 = $justifications_temp["justification_id"];
$justification_minutes_4 = $justifications_temp["justification_minutes"];
} else if ($justification_id_5 == 0) {
$justification_id_5 = $justifications_temp["justification_id"];
$justification_minutes_5 = $justifications_temp["justification_minutes"];
} else if ($justification_id_6 == 0) {
$justification_id_6 = $justifications_temp["justification_id"];
$justification_minutes_6 = $justifications_temp["justification_minutes"];
}
}
$total_minutes = $day_worked_minutes + $holiday_worked_minutes + $night_worked_minutes + $holiday_night_worked_minutes;
if ($total_minutes > $day_minutes) {
$worked_minutes = $day_minutes;
} else {
$worked_minutes = $total_minutes;
}
//Inserisco riga della giornata su DB
$sql_insert = "INSERT INTO attendance_rows VALUES(NULL, $id, $user_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);
//Incremento i giustificativi totali
$total_day_worked_minutes += $day_worked_minutes;
$total_night_worked_minutes += $night_worked_minutes;
$total_holiday_worked_minutes += $holiday_worked_minutes;
$total_holiday_night_worked_minutes += $holiday_night_worked_minutes;
$total_day_overtime_minutes += $day_overtime_minutes;
$total_night_overtime_minutes += $night_overtime_minutes;
$total_holiday_overtime_minutes += $holiday_overtime_minutes;
$total_holiday_night_overtime_minutes += $holiday_night_overtime_minutes;
echo "
";
}
$progressive = 1;
if ($total_day_worked_minutes > 0) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, 20, $total_day_worked_minutes)";
$con->query($sql_insert);
$progressive++;
}
if ($total_night_worked_minutes > 0) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, 15, $total_night_worked_minutes)";
$con->query($sql_insert);
$progressive++;
}
if ($total_holiday_worked_minutes > 0) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, 13, $total_holiday_worked_minutes)";
$con->query($sql_insert);
$progressive++;
}
if ($total_holiday_night_worked_minutes > 0) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, 14, $total_holiday_night_worked_minutes)";
$con->query($sql_insert);
$progressive++;
}
if ($total_theorical_minutes > 0) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, 27, $total_theorical_minutes)";
$con->query($sql_insert);
$progressive++;
}
$sql = "SELECT SUM(minutes) AS justification_minutes, justification_id FROM attendance_justifications_temp WHERE user_id = $user_id AND attendance_id = $id GROUP BY justification_id";
$result_justifications_temp = mysqli_query($con, $sql);
while ($justifications_temp = mysqli_fetch_assoc($result_justifications_temp)) {
//Inserisco riga totale ore diurne lavorate
$sql_insert = "INSERT INTO attendance_users VALUES(NULL, $id, $user_id, $progressive, " . $justifications_temp["justification_id"] . ", " . $justifications_temp["justification_minutes"] . ")";
$con->query($sql_insert);
$progressive++;
}
}
$sql_delete = "DELETE FROM attendance_justifications_temp WHERE attendance_id = $id";
$con->query($sql_delete);
echo "ok";
$con->close();