<?php
if($eleves_nb>0) {
	echo '<h3>'.FDIR_STATS_TIT27.DOTS.'</h3>';
	//élèves sortis
	$eleves_select = "SELECT freq.freq_id, COUNT(eleves.eleves_id), freq.eleves_id, eleves.division, eleves.nom, eleves.prenom FROM ".$prefix."_freq freq 
	INNER JOIN ".$prefix."_eleves eleves
	ON freq.eleves_id = eleves.eleves_id
	WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND cas = 'sor' GROUP BY eleves.eleves_id ORDER BY COUNT(eleves.eleves_id) DESC";
	$eleves = mysqli_query($bdd,$eleves_select);
	$eleves_nb = mysqli_num_rows($eleves);
	if($eleves_nb>0) {
		$sorties_nb = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND cas = 'sor'"));
		echo '<p>'.$sorties_nb.' '.FREQJ_ORDER81;
		if($sorties_nb>1) { echo PLURIEL; }
		echo ', '.FOR2.' '.$eleves_nb.' '.ELEVS3;
		if($eleves_nb>1) { echo PLURIEL; }
		echo '.</p>';
		echo '<div id="pager3" class="pager3">
			<form>
				<i class="fas fa-fast-backward first"></i>
				<i class="fas fa-backward prev"></i>
				<!-- the "pagedisplay" can be any element, including an input -->
				<input type="text" class="pagedisplay inptxt"/>
				<i class="fas fa-forward next"></i>
				<i class="fas fa-fast-forward last"></i>
				<div class="box"><select class="pagesize">
					<option selected="selected"  value="15">15'.PERPAGE.'</option>
					<option value="30">30'.PERPAGE.'</option>
					<option  value="45">45'.PERPAGE.'</option>
					<option  value="60">60'.PERPAGE.'</option>
				</select></div>
			</form><br>
		</div>';
		$bctab3 = '<table><thead><tr><td colspan="4" style="text-align:center;">'.FDIR_STATS_TIT27.'<br>'.FROM2.' '.$affday1.' '.TO2.' '.$affday2.'</td></tr><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF5.'</th></tr></thead><tbody>';
		echo '<table cellspacing="1" class="tablesorter table3">
		<thead><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF5.'</th></tr></thead><tbody>';
		foreach($eleves as $eleves_info) {
			$eleves_freq = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND eleves_id = '".$eleves_info['eleves_id']."'"));
			echo '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
			$bctab3 .= '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
		}
		echo '</tbody></table>';
		$bctab3 .= '</tbody></table>';
		$_SESSION['bctabs'].= '<table></table>'.$bctab3;
	}
	else { echo FDIR_STATS_INF6; }
	echo '</div>';

	echo '<div id="artfreqc"><h3>'.FDIR_STATS_TIT28.DOTS.'</h3>';
	if($excluopt_info['valeur']==1) { 
		echo FDIR_STATS_INF7;
	}
	//élèves exclus
	if($excluopt_info['valeur']==1) { 
		$eleves_select = "SELECT exc.exclusion_id, COUNT(eleves.eleves_id), exc.eleves_id, eleves.division, eleves.nom, eleves.prenom FROM ".$prefix."_exclusions exc
		INNER JOIN ".$prefix."_eleves eleves
		ON exc.eleves_id = eleves.eleves_id
		WHERE date_exclusion >= '".$day1."' AND date_exclusion <= '".$day2."' GROUP BY eleves.eleves_id ORDER BY COUNT(eleves.eleves_id) DESC";
		$eleves = mysqli_query($bdd,$eleves_select);
		$eleves_nb = mysqli_num_rows($eleves);
	}
	else {
		$eleves_select = "SELECT freq.freq_id, COUNT(eleves.eleves_id), freq.eleves_id, eleves.division, eleves.nom, eleves.prenom FROM ".$prefix."_freq freq 
		INNER JOIN ".$prefix."_eleves eleves
		ON freq.eleves_id = eleves.eleves_id
		WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND cas = 'exc' GROUP BY eleves.eleves_id ORDER BY COUNT(eleves.eleves_id) DESC";
		$eleves = mysqli_query($bdd,$eleves_select);
		$eleves_nb = mysqli_num_rows($eleves);
	}
	if($eleves_nb>0) {
		if($excluopt_info['valeur']==1) { 
			$exclus_nb = mysqli_num_rows(mysqli_query($bdd,"SELECT exclusion_id, eleves_id FROM ".$prefix."_exclusions WHERE date_exclusion >= '".$day1."' AND date_exclusion <= '".$day2."'"));
		}
		else {
			$exclus_nb = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND cas = 'exc'"));
		}
		echo '<p>'.$exclus_nb.' '.FREQJ_ORDER91;
		if($exclus_nb>1) { echo PLURIEL; }
		echo ', '.FOR2.' '.$eleves_nb.' '.ELEVS3;
		if($eleves_nb>1) { echo PLURIEL; }
		echo '.</p>';
		echo '<div id="pager2" class="pager2">
			<form>
				<i class="fas fa-fast-backward first"></i>
				<i class="fas fa-backward prev"></i>
				<!-- the "pagedisplay" can be any element, including an input -->
				<input type="text" class="pagedisplay inptxt"/>
				<i class="fas fa-forward next"></i>
				<i class="fas fa-fast-forward last"></i>
				<div class="box"><select class="pagesize">
					<option selected="selected"  value="15">15'.PERPAGE.'</option>
					<option value="30">30'.PERPAGE.'</option>
					<option  value="45">45'.PERPAGE.'</option>
					<option  value="60">60'.PERPAGE.'</option>
				</select></div>
			</form><br>
		</div>';
		$bctab2 = '<table><thead><tr><td colspan="4" style="text-align:center;">'.FDIR_STATS_TIT28.'<br>'.FROM2.' '.$affday1.' '.TO2.' '.$affday2.'</td></tr><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF8.'</th></tr></thead><tbody>';
		echo '<table  cellspacing="1" class="tablesorter table2"><thead><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF8.'</th></tr></thead><tbody>';
		if($excluopt_info['valeur']==1) { 
			foreach($eleves as $eleves_info) {
				$eleves_freq = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' AND eleves_id = '".$eleves_info['eleves_id']."'"));
				$info_limits = mysqli_query($bdd,"SELECT * FROM ".$prefix."_exclusions exc 
					INNER JOIN ".$prefix."_eleves eleves
					ON exc.eleves_id = eleves.eleves_id
					INNER JOIN ".$prefix."_exclucauses cau
					ON exc.exclucause_id = cau.exclucause_id
					WHERE date_exclusion >= '".$day1."' AND date_exclusion <= '".$day2."' AND eleves.eleves_id = '".$eleves_info['eleves_id']."'  ORDER BY date_exclusion DESC");
				echo '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center" title="';
				foreach($info_limits as $info_limit) {
					echo $info_limit['date_exclusion'].' : '.$info_limit['exclucause_txt'].'
';					
				}
				echo '">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
				$bctab2 .= '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
			}
		}
		else {
			foreach($eleves as $eleves_info) {
				$eleves_freq = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' ".$filtre_activs." AND eleves_id = '".$eleves_info['eleves_id']."'"));
				echo '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
				$bctab2 .= '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
			}
		}
		echo '</tbody></table>';
		$bctab2.= '</tbody></table>';
		$_SESSION['bctabs'].= '<table></table>'.$bctab2;
	}
	else { echo FDIR_STATS_INF9; }

	if($excluopt_info['valeur']==1) { 
		echo '<div id="artfreql"><h3>'.FSTATS_EXC6.DOTS.'</h3>'; 
		echo FDIR_STATS_INF7;
		//élèves avec limitations
		$eleves_select = "SELECT limits.limit_id, COUNT(eleves.eleves_id), limits.eleves_id, eleves.division, eleves.nom, eleves.prenom FROM ".$prefix."_limits limits 
		INNER JOIN ".$prefix."_eleves eleves
		ON limits.eleves_id = eleves.eleves_id
		WHERE date_debut >= '".$day1."' AND date_debut <= '".$day2."' GROUP BY eleves.eleves_id ORDER BY COUNT(eleves.eleves_id) DESC";
		$eleves = mysqli_query($bdd,$eleves_select);
		$eleves_nb = mysqli_num_rows($eleves);
		if($eleves_nb>0) {
			$exclus_nb = mysqli_num_rows(mysqli_query($bdd,"SELECT limit_id, eleves_id FROM ".$prefix."_limits WHERE date_debut >= '".$day1."' AND date_debut <= '".$day2."'"));
			echo '<p>'.$exclus_nb.' '.FREQ_LIMIT;
			if($exclus_nb>1) { echo PLURIEL; }
			echo ', '.FOR2.' '.$eleves_nb.' '.ELEVS3;
			if($eleves_nb>1) { echo PLURIEL; }
			echo '.</p>';
			echo '<div id="pager5" class="pager5">
				<form>
					<i class="fas fa-fast-backward first"></i>
					<i class="fas fa-backward prev"></i>
					<!-- the "pagedisplay" can be any element, including an input -->
					<input type="text" class="pagedisplay inptxt"/>
					<i class="fas fa-forward next"></i>
					<i class="fas fa-fast-forward last"></i>
					<div class="box"><select class="pagesize">
						<option selected="selected"  value="15">15'.PERPAGE.'</option>
						<option value="30">30'.PERPAGE.'</option>
						<option  value="45">45'.PERPAGE.'</option>
						<option  value="60">60'.PERPAGE.'</option>
					</select></div>
				</form><br>
			</div>';
			$bctab4 = '<table><thead><tr><td colspan="4" style="text-align:center;">'.FSTATS_EXC6.'<br>'.FROM2.' '.$affday1.' '.TO2.' '.$affday2.'</td></tr><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF10.'</th></tr></thead><tbody>';
			echo '<table  cellspacing="1" class="tablesorter table2"><thead><tr><th>'.DIV.'</th><th>'.NAMECH2.'</th><th>'.FDIR_STATS_INF4.'</th><th>'.FDIR_STATS_INF10.'</th></tr></thead><tbody>';
			foreach($eleves as $eleves_info) {
				$eleves_freq = mysqli_num_rows(mysqli_query($bdd,"SELECT freq_id, eleves_id FROM ".$prefix."_freq WHERE date_freq >= '".$day1."' AND date_freq <= '".$day2."' AND eleves_id = '".$eleves_info['eleves_id']."'"));
				$info_limits = mysqli_query($bdd,"SELECT * FROM ".$prefix."_limits limits 
					INNER JOIN ".$prefix."_eleves eleves
					ON limits.eleves_id = eleves.eleves_id
					INNER JOIN ".$prefix."_limitstxt lt
					ON limits.limittxt_id = lt.limittxt_id
					LEFT JOIN ".$prefix."_espaces esp
					ON esp.espace_id = lt.espace_id
					WHERE date_debut >= '".$day1."' AND date_debut <= '".$day2."' AND eleves.eleves_id = '".$eleves_info['eleves_id']."'  ORDER BY date_debut DESC");
				echo '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center" title="';
				foreach($info_limits as $info_limit) {
					echo $info_limit['date_debut'].' : '.$info_limit['limits_txt'].' - ';
					if($info_limit['espace_id']==0) { echo $esp0['valeur'].'
'; }
					else { echo $info_limit['espace_nom'].'
'; }
				}
				echo '">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
				$bctab4 .= '<tr><td class="center">'.$eleves_info['division'].'</td><td>'.$eleves_info['nom'].' '.$eleves_info['prenom'].'</td><td class="center">'.$eleves_freq.'</td><td class="center">'.$eleves_info['COUNT(eleves.eleves_id)'].'</td></tr>';
			}
			echo '</tbody></table>';
			$bctab4.= '</tbody></table>';
			$_SESSION['bctabs'].= '<table></table>'.$bctab4;
		}
		else { echo FDIR_STATS_INF11; }
	}
}

if($excluopt_info['valeur']==1) { 
	echo ' <h3>'.FDIR_STATS_TIT29.'</h3>'; 
	echo FDIR_STATS_INF71;
	include('db/proc/freq_stats_exclusions.php');
}
?>