<?php
session_start();
require_once(dirname(__DIR__, 1) . '/config.php');
require_once(BASE_PATH . '/db/idl_connect.php');
// UPLOAD DU FICHIER CSV, vérification et insertion en BASE

echo '<meta charset="utf-8" />';
if(isset($_SESSION['user_droits'])) {
	if($_SESSION['user_droits'][5]!="2")
	{ echo '<p>'.NOMODULE.' <a href="../index.php">'.ACCUEIL.'</a>.</p>'; }
	function strToNoAccent($var) {
		$var = str_replace(
			array(
				'à', 'â', 'ä', 'á', 'ã', 'å',
				'î', 'ï', 'ì', 'í', 
				'ô', 'ö', 'ò', 'ó', 'õ', 'ø', 
				'ù', 'û', 'ü', 'ú', 
				'é', 'è', 'ê', 'ë', 
				'ç', 'ÿ', 'ñ',
				'À', 'Â', 'Ä', 'Á', 'Ã', 'Å',
				'Î', 'Ï', 'Ì', 'Í', 
				'Ô', 'Ö', 'Ò', 'Ó', 'Õ', 'Ø', 
				'Ù', 'Û', 'Ü', 'Ú', 
				'É', 'È', 'Ê', 'Ë', 
				'Ç', 'Ÿ', 'Ñ', '-', ' ', '\''
			),
			array(
				'a', 'a', 'a', 'a', 'a', 'a', 
				'i', 'i', 'i', 'i', 
				'o', 'o', 'o', 'o', 'o', 'o', 
				'u', 'u', 'u', 'u', 
				'e', 'e', 'e', 'e', 
				'c', 'y', 'n', 
				'a', 'a', 'a', 'a', 'a', 'a', 
				'i', 'i', 'i', 'i', 
				'o', 'o', 'o', 'o', 'o', 'o', 
				'u', 'u', 'u', 'u', 
				'e', 'e', 'e', 'e', 
				'c', 'y', 'n', '','', ''
			),$var);
		return $var;
	}
	function rand_nb($length = 8) {
	    $characters = '123456789';
	    $charactersLength = strlen($characters);
	    $randomString = '';
	    for ($i = 0; $i < $length; $i++) {
	        $randomString .= $characters[rand(0, $charactersLength - 1)];
	    }
	    return $randomString;
	}
	if (isset($_FILES['file_csv']) && $_FILES['file_csv']['error'] === UPLOAD_ERR_OK) {
	    $mimes = [
	        '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'
	    ];
	    $method = 'csv';
	    $_FILES['file'] = $_FILES['file_csv'];
	} else if (isset($_FILES['file_xml']) && $_FILES['file_xml']['error'] === UPLOAD_ERR_OK) {
	    $mimes = array('application/xml', 'text/xml');
	    $method = 'xml';
	    $_FILES['file'] = $_FILES['file_xml'];
	    $xmlEleves = simplexml_load_file($_FILES['file_xml']['tmp_name']);
	    $xmlResponsables = null;
		if (isset($_FILES['file_xml2']) && $_FILES['file_xml2']['error'] === UPLOAD_ERR_OK) {
		    $xmlResponsables = simplexml_load_file($_FILES['file_xml2']['tmp_name']);
		}
	}
	else {
	    die(ERROR3);
	}

	// Vérification du type MIME réel
	$finfo = finfo_open(FILEINFO_MIME_TYPE);
	$mime = finfo_file($finfo, $_FILES['file']['tmp_name']);
	finfo_close($finfo);

	if (!in_array($mime, $mimes)) {
	    die('<p>Type de fichier non autorisé (détecté : ' . htmlspecialchars($mime) . ')</p>');
	}

	// Intégration dans la table ".$prefix."_eleves
	echo '<p>'.ELEVINSTALL.'</p>';
	$datas_col=0;
	if (($handle = fopen($_FILES['file']['tmp_name'], "r")) !== FALSE) {
		// 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);
		}
		if($method=='csv') {
			include('proc/install_eleves_csv.php');
		}
		else if($method=='xml') {
			include('proc/install_eleves_xml.php');
		}
	
		// Vérification de la qualité de la table de MAJ
		$elevescheck = mysqli_query($bdd,"SELECT * FROM ".$prefix."_elevesmaj WHERE nom = '' OR nom = 'nom' OR nom = 'Nom' OR nom = 'Nom de famille' OR nom = 'nom de famille' OR prenom = '' OR division = '' OR division LIKE '%  %' OR sexe = '' OR (sexe <> 'M' AND sexe <> 'F') OR naissance ='0000-00-00' OR naissance IS NULL");
		$nb = mysqli_num_rows($elevescheck);
		if((@$datas_col==4&&@$cdata8==0)||(@$datas_col==3&&@$cdata7==0)||(@$datas_col==2&&@$cdata6==0)||(@$datas_col==1&&@$cdata5==0)||(@$datas_col==0&&@$cdata5==1)) {
			echo '<p>'.PBFILE2;
			mysqli_query($bdd,"DELETE FROM ".$prefix."_elevesmaj");
			echo '</p><p><a href="../database.php">'.BACK.'</a>.</p>';
		}
		else if($nb>0) {
			$virgule = mysqli_fetch_assoc($elevescheck);
			if(strpos($virgule['nom'], ',')!==false) {
				echo '<p>'.PBFILE3;
			}
			else {
				echo '<p>'.PBFILE4.'<br>';
				foreach($elevescheck as $problemes) {
					if($problemes['nom']=='Nom'||$problemes['nom']=='nom'||$problemes['nom']=='Nom de famille'||$problemes['nom']=='nom de famille') {
						echo PBFILE5;
					}
					else {
						echo PBFILE6.' '.$problemes['prenom'].' '.$problemes['nom'].' :';
						if($problemes['division']=='') { echo ' la division'; }
						if(preg_match('#  #', $problemes['division'])) { echo ' '.PBFILE7; }
						if($problemes['sexe']==''||($problemes['sexe'] != 'M' AND $problemes['sexe'] != 'F')) { echo ' '.PBFILE8; }
						if($problemes['naissance']=='0000-00-00'||$problemes['naissance']=='') { echo ' '.PBFILE9; }													
						echo '.<br>';
					}
				}
			}
			mysqli_query($bdd,"DELETE FROM ".$prefix."_elevesmaj");
			echo '</p><p><a href="../database.php">'.BACK.'</a>.</p>';
		}				
		else {
			$elevesmaj = mysqli_query($bdd,"SELECT * FROM ".$prefix."_elevesmaj");
			while($elevesmaj_info=mysqli_fetch_assoc($elevesmaj)) {
				$majnom = mysqli_real_escape_string($bdd,$elevesmaj_info['nom']);
				$majprenom = mysqli_real_escape_string($bdd,$elevesmaj_info['prenom']);
				$majdiv = mysqli_real_escape_string($bdd,$elevesmaj_info['division']);
				$majnaissance = mysqli_real_escape_string($bdd,$elevesmaj_info['naissance']);
				$majsexe = mysqli_real_escape_string($bdd,$elevesmaj_info['sexe']);
				$majcode = mysqli_real_escape_string($bdd,$elevesmaj_info['code']);
				$majmdp = mysqli_real_escape_string($bdd,$elevesmaj_info['mdp']);
				$majmail = mysqli_real_escape_string($bdd,$elevesmaj_info['mail']);
				$majid_perso = mysqli_real_escape_string($bdd,$elevesmaj_info['id_perso']);
				$majsel = mysqli_real_escape_string($bdd,$elevesmaj_info['sel']);
				$resp_nom = mysqli_real_escape_string($bdd,$elevesmaj_info['resp_nom']);
				$add1 = mysqli_real_escape_string($bdd,$elevesmaj_info['add1']);
				$add2 = mysqli_real_escape_string($bdd,$elevesmaj_info['add2']);
				$cpostal = mysqli_real_escape_string($bdd,$elevesmaj_info['cpostal']);
				$ville = mysqli_real_escape_string($bdd,$elevesmaj_info['ville']);
				$elevesnew_insert = "INSERT INTO ".$prefix."_eleves (eleves_id, nom, prenom, division, naissance, sexe, code, mdp, mail, auth_mail, id_perso, sel, lang, resp_nom, add1, add2, cpostal, ville, categ) VALUES('', '".$majnom."', '".$majprenom."', '".$majdiv."', '".$majnaissance."', '".$majsexe."', '".$majcode."', '".$majmdp."', '".$majmail."', '0', '".$majid_perso."', '".$majsel."', 'fr', '$resp_nom', '$add1', '$add2', '$cpostal', '$ville', '1')";
				$elevesnew = mysqli_query($bdd,$elevesnew_insert);
			}
			mysqli_query($bdd,"DELETE FROM ".$prefix."_elevesmaj");
			
			// Mise à jour ou création des effectifs dans _etab_effectifs
			$anneesco = $_POST['annee_effectifs'];
			$divisions = mysqli_query($bdd,"SELECT division FROM ".$prefix."_eleves WHERE categ = '1' GROUP BY division ORDER BY division ASC");
			if(mysqli_num_rows($divisions)>0) {
				$etab = mysqli_query($bdd,"SELECT etab_id FROM ".$prefix."_etab");
				if(mysqli_num_rows($etab)>0) {
					$etab_info = mysqli_fetch_assoc($etab);
					$etab_id = $etab_info['etab_id'];
				}
				else { $etab_id = ''; }
				foreach($divisions as $div) {
					$nbeleves = mysqli_num_rows(mysqli_query($bdd,"SELECT * FROM ".$prefix."_eleves WHERE division = '".$div['division']."'"));
					if(mysqli_num_rows(mysqli_query($bdd,"SELECT * FROM ".$prefix."_etab_effectifs WHERE anneesco = '".$anneesco."' AND division = '".$div['division']."'"))>0) {
						mysqli_query($bdd,"UPDATE ".$prefix."_etab_effectifs SET effectif = '".$nbeleves."' WHERE anneesco = '".$anneesco."' AND division = '".$div['division']."'");
					}
					else {
						mysqli_query($bdd,"INSERT INTO ".$prefix."_etab_effectifs (etab_id, anneesco, division, effectif) VALUES ('".$etab_id."', '".$anneesco."', '".$div['division']."', '".$nbeleves."')");
					}
				}
			}
			// Identifiants persos par défaut si importés
			if(isset($_POST['input_identifiant'])) {
				mysqli_query($bdd,"UPDATE ".$prefix."_parametres SET valeur = 'perso' WHERE param_nom = 'freq_resmodauth'");
			}
			else {						
				$auth_info = mysqli_fetch_assoc(mysqli_query($bdd,"SELECT valeur FROM ".$prefix."_parametres WHERE param_nom = 'freq_resmodauth'"));
				if($auth_info['valeur']=="prenomnom"||$auth_info['valeur']=="pnom") {
					$eleves = mysqli_query($bdd,"SELECT eleves_id, nom, prenom FROM ".$prefix."_eleves");
					foreach($eleves as $eleve) {
						if($auth_info['valeur']=="prenomnom") {
							$ident = substr(strToNoAccent($eleve['prenom']),0,20).'.'.substr(strToNoAccent($eleve['nom']),0,20);
						}
						else if($auth_info['valeur']=="pnom") {
							$ident = substr(strToNoAccent($eleve['prenom']),0,1).substr(strToNoAccent($eleve['nom']),0,20);
						}
						$checke = mysqli_query($bdd,"SELECT id_constr FROM ".$prefix."_eleves WHERE id_constr = '".mysqli_real_escape_string($bdd,strtolower($ident))."' AND eleves_id <> '".$eleve['eleves_id']."'");
						$checku = mysqli_query($bdd,"SELECT login FROM ".$prefix."_user WHERE login = '".mysqli_real_escape_string($bdd,strtolower($ident))."'");
						if(mysqli_num_rows($checke)>0||mysqli_num_rows($checku)>0) {
							$ident = $ident.rand_nb(2);
						}
						mysqli_query($bdd,"UPDATE ".$prefix."_eleves SET id_constr = '".strtolower($ident)."' WHERE eleves_id = '".$eleve['eleves_id']."'");
					}
				}
			}
			// on crée les identifiants id_constr si besoin quand ils sont vides :
			$reserv_could=mysqli_fetch_assoc(mysqli_query($bdd,"SELECT valeur FROM ".$prefix."_parametres WHERE param_nom = 'freq_reseleve'"));
			$auth_info = mysqli_fetch_assoc(mysqli_query($bdd,"SELECT valeur FROM ".$prefix."_parametres WHERE param_nom = 'freq_resmodauth'"));
			if($reserv_could['valeur']==2&&($auth_info['valeur']=="prenomnom"||$auth_info['valeur']=="pnom")) {						
				$eleves = mysqli_query($bdd,"SELECT eleves_id, nom, prenom FROM ".$prefix."_eleves WHERE id_constr = ''");
				foreach($eleves as $eleve) {
					if($auth_info['valeur']=="prenomnom") {
						$ident = substr(strToNoAccent($eleve['prenom']),0,20).'.'.substr(strToNoAccent($eleve['nom']),0,20);
					}
					else if($auth_info['valeur']=="pnom") {
						$ident = substr(strToNoAccent($eleve['prenom']),0,1).substr(strToNoAccent($eleve['nom']),0,20);
					}
					$checke = mysqli_query($bdd,"SELECT id_constr FROM ".$prefix."_eleves WHERE id_constr = '".mysqli_real_escape_string($bdd,strtolower($ident))."' AND eleves_id <> '".$eleve['eleves_id']."'");
					$checku = mysqli_query($bdd,"SELECT login FROM ".$prefix."_user WHERE login = '".mysqli_real_escape_string($bdd,strtolower($ident))."'");
					if(mysqli_num_rows($checke)>0||mysqli_num_rows($checku)>0||$ident=='reserver') {
						$ident = $ident.rand_nb(2);
					}
					mysqli_query($bdd,"UPDATE ".$prefix."_eleves SET id_constr = '".strtolower($ident)."' WHERE eleves_id = '".$eleve['eleves_id']."'");
				}
			}
			$_SESSION['import']="new";
			echo '<script language="Javascript">
			<!--
			document.location.replace("../database.php?page=consulter");
			// -->
			</script>';
		}
	}
}
else {
	include('include/noaccess.php');
	echo '<a href="../index.php">'.ACCUEIL.'</a>.</p>';
}
mysqli_close($bdd);
?>
