<?php
session_start();
require_once(dirname(__DIR__, 2) . '/config.php');
require_once(BASE_PATH . '/db/idl_connect.php');

echo '<meta charset="utf-8" />';
if(isset($_SESSION['user_droits'])) {
	$droits = substr($_SESSION['user_droits'],3,1);
	
	if ($droits!=2&&$droits!=5&&$droits!=6)
	{
		echo '<p>'.NOMODULE.' <a href="./index.php">'.ACCUEIL.'</a>.</p>';
	}
	else {
		$mimes = array('text/csv',
		'text/plain',
		'application/csv',
		'text/comma-separated-values', 
		'application/excel',
		'application/vnd.ms-excel',
		'application/vnd.msexcel',
		'text/anytext',
		'application/octet-stream',
		'application/txt',);	
		if(@in_array($_FILES['file']['type'],$mimes)){
			// Recherche de l'encodage
			$content = file_get_contents($_FILES['file']['tmp_name']);
			$encodage = mb_detect_encoding($content, ['UTF-8','ISO-8859-1','ISO-8859-15','Windows-1252'], true);
			if ($encodage === false || $encodage === 'ASCII') {
			    $encodage = 'Windows-1252';
			}
			if ($encodage !== 'UTF-8') {
			    $content = iconv($encodage, 'UTF-8//IGNORE', $content);
			}

			// Suppression d'une éventuelle BOM UTF-8
			$content = preg_replace('/^\xEF\xBB\xBF/', '', $content);

			// Création d’un fichier temporaire avec le contenu converti
			$tmp_utf8_file = tempnam(sys_get_temp_dir(), 'utf8_');
			file_put_contents($tmp_utf8_file, $content);
			// Suppression des retards
			mysqli_query($bdd,"DELETE FROM ".$prefix."_retards");
			// Intégration dans la table ".$prefix."_elevesmaj
			echo '<p>'.FREQ_GEST7.'</p>';
			if(isset($_SESSION['nblines'])) {
				unset($_SESSION['nblines']);
			}
			$_SESSION['nblines']=0;
			if (($handle = fopen($tmp_utf8_file, "r")) !== false) {
				while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
					if(isset($data[8])&&$data[1]!="") {
						if($data[8]=="") {
							$data[8]=$data[7];
							$data[10]=$data[9];
							$data[6]=$data[5];
						}
						$date = mysqli_real_escape_string($bdd,$data[8]);
						if(is_numeric(substr($date,0,2))) {
							if ($encoding == 1) {
						        $nom_prenom = mb_convert_encoding(mysqli_real_escape_string($bdd, $data[1]), "UTF-8", $encodage);
						    } else {
						        $nom_prenom = mysqli_real_escape_string($bdd, $data[1]);
						    }
							$eleve = mysqli_query($bdd, "
						        SELECT eleves_id FROM ".$prefix."_eleves 
						        WHERE CONCAT(nom, ' ', prenom) = '".$nom_prenom."'
						    ");

						    $eleve_nb = mysqli_num_rows($eleve);
							if($eleve_nb==1) {
								if($encoding==1) {
									$titre = mb_convert_encoding(mysqli_real_escape_string($bdd,$data[6]),"UTF-8",$encodage);
								}
								else {
									$titre = mysqli_real_escape_string($bdd,$data[6]);
								}
								if(isset($data[10])) {
									$alerte = mysqli_real_escape_string($bdd,$data[10]);
								}
								else { $alerte = "0"; }
								$eleve_info = mysqli_fetch_assoc($eleve);
								mysqli_query($bdd,"INSERT INTO ".$prefix."_retards (retards_id, eleves_id, titre, retour_date, niv_alerte) VALUES ('', '".$eleve_info['eleves_id']."', '".$titre."', '".substr($date,6,4)."/".substr($date,3,2)."/".substr($date,0,2)."', '".$alerte."')");
							}			
							else {
								$bcdi_wrong_case=1;
								include('freq_retards_bcdi_wrong.php');
							}			
						}
					}
					else if(isset($data[12])&&$data[1]==""&&$data[2]!="") {
						if($data[12]=="") {
							$data[12]=$data[11];
							$data[9]=$data[8];
						}
						$date = mysqli_real_escape_string($bdd,$data[12]);
						if(is_numeric(substr($date,0,2))) {
							if ($encoding == 1) {
						        $nom_prenom = mb_convert_encoding(mysqli_real_escape_string($bdd, $data[2]), "UTF-8", $encodage);
						    } else {
						        $nom_prenom = mysqli_real_escape_string($bdd, $data[2]);
						    }
							$eleve = mysqli_query($bdd, "
						        SELECT eleves_id FROM ".$prefix."_eleves 
						        WHERE CONCAT(nom, ' ', prenom) = '".$nom_prenom."'
						    ");
							$eleve_nb = mysqli_num_rows($eleve);
							if($eleve_nb==1) {
								if($encoding==1) {
									$titre = mb_convert_encoding(mysqli_real_escape_string($bdd,$data[9]),"UTF-8",$encodage);
								}
								else {
									$titre = mysqli_real_escape_string($bdd,$data[9]);
								}
								if(isset($data[14])) {
									$alerte = mysqli_real_escape_string($bdd,$data[14]);
								}
								else { $alerte = "0"; }
								$eleve_info = mysqli_fetch_assoc($eleve);
								mysqli_query($bdd,"INSERT INTO ".$prefix."_retards (retards_id, eleves_id, titre, retour_date, niv_alerte) VALUES ('', '".$eleve_info['eleves_id']."', '".$titre."', '".substr($date,6,4)."/".substr($date,3,2)."/".substr($date,0,2)."', '".$alerte."')");
							}
							else {
								$bcdi_wrong_case=2;
								include('freq_retards_bcdi_wrong.php');
							}				
						}
					}
					else if (count($data) >= 4) {
					    // Recherche de la date et de l'alerte
					    $date_index = null;
					    $alerte = "0";
					    
					    // Parcours inversé pour trouver la date
					    for ($i = count($data) - 1; $i >= 0; $i--) {
					        if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $data[$i])) {
					            $date_index = $i;
					            break;
					        }
					    }
					    
					    if ($date_index !== null) {
					        $date = mysqli_real_escape_string($bdd, $data[$date_index]);
					        // L'alerte est après la date si elle est numérique
					        if (isset($data[$date_index + 1]) && is_numeric($data[$date_index + 1])) {
					            $alerte = mysqli_real_escape_string($bdd, $data[$date_index + 1]);
					        }
					    }
					    
					    if ($date_index !== null && is_numeric(substr($date, 0, 2)) && $data[0] != "EMPRUNTEUR") {
					        if ($encoding == 1) {
						        $nom_prenom = mb_convert_encoding(mysqli_real_escape_string($bdd, $data[0]), "UTF-8", $encodage);
						    } else {
						        $nom_prenom = mysqli_real_escape_string($bdd, $data[0]);
						    }
							$eleve = mysqli_query($bdd, "
						        SELECT eleves_id FROM ".$prefix."_eleves 
						        WHERE CONCAT(nom, ' ', prenom) = '".$nom_prenom."'
						    ");
				            $eleve_nb = mysqli_num_rows($eleve);
				            
				            if ($eleve_nb == 1) {
				                // Traitement du titre
				                if ($encoding == 1) {
				                    $titre = mb_convert_encoding(mysqli_real_escape_string($bdd, $data[2]), "UTF-8", $encodage);
				                } else {
				                    $titre = mysqli_real_escape_string($bdd, $data[2]);
				                }
				                
				                // On récupère l'id de l'élève
				                $eleve_info = mysqli_fetch_assoc($eleve);
				                
				                // Insertion dans la base de données
				                mysqli_query($bdd, "INSERT INTO " . $prefix . "_retards (retards_id, eleves_id, titre, retour_date, niv_alerte) VALUES ('', '" . $eleve_info['eleves_id'] . "', '" . $titre . "', '" . substr($date, 6, 4) . "/" . substr($date, 3, 2) . "/" . substr($date, 0, 2) . "', '" . $alerte . "')");
				            } else {
				                $bcdi_wrong_case = 3;
				                include('freq_retards_bcdi_wrong.php');
				            }
					    }
					}						
				}
				fclose($handle);
				unlink($tmp_utf8_file);
			}
			echo '<script language="Javascript">
			<!--
			document.location.replace("../../freq.php?page=retards");
			// -->
			</script>';
		}
		else {
			die(NOCSV);
		}
	}
}
else {
	include('include/noaccess.php');
	echo '<a href="../../index.php">'.ACCUEIL.'</a>.</p>';
}
mysqli_close($bdd);
?>
