<?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']);
			$enclist = array(
			    'UTF-8', 'ASCII',
			    'ISO-8859-1', 'ISO-8859-15',
			    'Windows-1252'
			);
			$encodage = mb_detect_encoding($content,$enclist,TRUE);
			$encoding = (mb_detect_encoding($content, ['UTF-8'], true) !== 'UTF-8') ? 1 : 0;
			if ($encodage === false) {
				echo '<p>'.PBFILE1.' <a href="../../freq.php?page=retards">'.BACK.'</a>.</p>';
			}
			// Si l'encodage est gérable
			else {
				// Conversion en UTF-8 si nécessaire
				if ($encodage !== 'UTF-8') {
				    $content = mb_convert_encoding($content, 'UTF-8', $encodage);
				}

				// 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);
?>
