<?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']);
		$enclist = ['UTF-8', 'Windows-1252', 'ISO-8859-1', 'ISO-8859-15', 'ASCII'];
		$encodage = mb_detect_encoding($content, $enclist, true);
		if ($encodage === false) {
			echo '<p>'.PBFILE1.' <a href="../database.php">'.BACK.'</a>.</p>';
		}
		// Si l'encodage est gérable
		else {
			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 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);
?>
