<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="w3.css">

<title>Bulletin</title>
<style type="text/css">
<!--

@media print  { .noprint  { display: none; }
.page-break  { display:block; page-break-after:always; }
@page  
{ 
   size: 210mm 297mm; 
  /* Chrome sets own margins, we change these printer settings */
  margin: 2mm 2mm 2mm 2mm;
        }

}
body,td,th {
font-family: "Times New Roman", Times, serif;
size:12px;
}
.rotate div {
        writing-mode: vertical-rl;
        height: 250px;
        font-size: 13px;
        overflow: hidden;
        word-wrap: break-word;
        line-height: 0.99;
        text-align: left;
        left: 50%;
        transform: translateX(-90%);
        position: relative; 
		}
	

-->
</style>
</head>
<body bgcolor="#FFFFFF">

<form method="post" action="_relever.php" >

<?php
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="BASIC_Data.xls"');
header('Cache-Control: max-age=0');

echo "<div class='noprint'>";
include 'header.php';
echo "</div>";
$cycle=$_GET['cycle'];
include '_serveur.php';?>
<input name="cycle" type="hidden" value="<?php $cycle=$_REQUEST['cycle']; echo $cycle; ?>">
<?php
$sql = 'SELECT * from classes where cycleID="'.$cycle.'" order by id ASC';
   if(!(mysqli_query($link, $sql))){
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
$req=  mysqli_query($link, $sql);
echo '<div class="noprint">';
echo '<label class="w3-text-teal" >Classe:</label><select name="cla" class="w3-select w3-border w3-light-grey">';

 while($data = mysqli_fetch_array($req)) {
$classe=$data['name'];
$classeid=$data['id'];
$classe=strtoupper($classe);
 echo' <option value="'.$classeid.";".$classe.'">'.$classe.'</option>';
        };

echo '</select>';
$sql = 'SELECT Distinct semesters.id,semesters.name from semesters  inner join classesSemesters on semesters.id=classesSemesters.semesterID where classID=1 or classID=2 or classID=3 order by id ASC';
   if(!(mysqli_query($link, $sql))){
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
$req=  mysqli_query($link, $sql);
echo '<label class="w3-text-teal" >Contrôle:</label><select name="ctr" class="w3-select w3-border w3-light-grey">';

 while($data = mysqli_fetch_array($req)) {
$semester=$data['1'];
$semesterid=$data['0'];
 echo' <option value="'.$semesterid.";".$semester.'">'.$semester.'</option>';
        };

echo '</select>';

 
 
 
 
 echo'<input name="chercher" type="submit" class="style19" value="Envoyer" bgcolor="#CC99FF" /></form>';
echo'<button type="button" class="noprint" onclick="window.print()" /><img src="imprimante.png" height="32" width="40" /></div>';

if (isset ($_POST['chercher'])){
$cl1=$_POST['cla'];
$se1=$_POST['ctr'];
$cls1=explode(";",$cl1);
 $classeid1=$cls1[0];
 $classe1=$cls1[1];
$sem1=explode(";",$se1);
 $semesterid1=$sem1[0];
 $ctrl1=$sem1[1];

$sql = 'SELECT * from gradesSubjects inner join subSubjects on gradesSubjects.subSubjectID=subSubjects.id
							   inner join subjects on subSubjects.subjectID=subjects.id
							   where gradesSubjects.classID="'.$classeid1.'" order by subjects.ordering,gradesSubjects.ordering  ' ;
   if(!(mysqli_query($link, $sql))){
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
                             }
$req=  mysqli_query($link, $sql);

$i=0;
	echo " <table align='center' id='tbl_exporttable_to_xls' border='1' cellspacing='0' bordercolor='#666666' width='98%'>";
echo "<tr><td colspan='45' align='center'><span class='joe'>Classe :".$classe1. "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Ctrl: ".$ctrl1.
"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date: ".date ('d/m/y')."</span></td></tr>";

  while($data = mysqli_fetch_array($req)){
	  $coefficient1[$i]=$data[2];
	  $sousmatid1[$i]=$data[4];
	  $matsous[$i]=$data[5];
	  $sousmat1[$i]=$data[6];
	  $matid1[$i]=$data[7];
	  $mat1[$i]=$data[8];
//	echo  $matid1[$i]." ".$mat1[$i]."sousmatid1: ".$sousmatid1[$i]." ".$sousmat1[$i]." ".$coefficient1[$i]."<br>";
  $i=$i+1;
   }
   
      $matid2 = array_unique($matid1, SORT_REGULAR);

   $mat2=array_unique($mat1, SORT_REGULAR);
   
$sql = 'SELECT * from  classes  left join students on  students.classID=classes.id
                                left  join grades on students.id=grades.studentID and students.classID=grades.classID 
                                left join gradesSubjects on grades.subSubjectID=gradesSubjects.subSubjectID and grades.classID=gradesSubjects.classID
							     left join subSubjects on grades.subSubjectID=subSubjects.id
							    left join subjects on subSubjects.subjectID=subjects.id
							    left join classesSemesters on classesSemesters.classId=classes.id and grades.semesterID=classesSemesters.semesterID
							    left join semesters on semesters.id=classesSemesters.semesterID 
                                where classes.id="'.$classeid1.'" and semesters.id="'.$semesterid1.'" order by students.firstName,students.lastName,subjects.id,subSubjects.id,semesters.id , subjects.ordering' ;
   if(!(mysqli_query($link, $sql))){
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
$req=  mysqli_query($link, $sql);
$i=0;
  while($data = mysqli_fetch_array($req)){
		 $studentid[$i]=$data[3]; 
		 $prenom[$i]=$data[5];
	  	 $famille[$i]=$data[6];
		$grade[$i]=$data[13];
	  $discipcoef[$i]=$data[16];	
	  $discipid[$i]=$data[18];
	  $discip[$i]=$data[20];
	  $matid[$i]=$data[21];
	  $mat[$i]=$data[22];
	  $orderm[$i]=$data[23];
	  $ctrlid[$i]=$data[28];
	  $ctrl[$i]=$data[29];
//	echo $studentid[$i]." ".$prenom[$i]." ".$famille[$i]." ".$cl[$i]." ".$ctrl[$i]." ".$mat[$i]	." ".$discip[$i]."discpid:".$discipid[$i]." ".$grade[$i]." ". $discipcoef[$i]."<br>";
//echo $studentid[$i]." ". $matid[$i]. " " .$discipid[$i]. " ". $discipcoef[$i]."<br>";
  $cl[$i]=$data[1];
  $i=$i+1;
	  
    }

 
   
      echo "<tr><td colspan='2' width='20%'>";
		for($j=0;$j<=sizeof($mat1);$j++)  { $c[$mat1[$j]]=0;}
  
	for($j=0;$j<=sizeof($mat1);$j++)
  {
	 if($matid1[$j]==$matid1[$j-1]) $c[$mat1[$j]]=$c[$mat1[$j]]+1;
  }
      for($j=0;$j<sizeof($mat1);$j++)
   {
	   $c1[$mat1[$j]]=$c[$mat1[$j]]+3;
	   $siz[$mat1[$j]]=$c[$mat1[$j]]*4;
	  if($mat1[$j]<>$mat1[$j-1]) echo "<td width='".$siz[$mat1[$j]]."' colspan='".$c1[$mat1[$j]]."'>".$mat1[$j]."</td>";
   }
     echo "<td>Total</td> <td> Coefficient</td></tr>";
	   echo "<tr><td colspan='2' width='20%'>";
	    $sumcoef=0;
   for($j=0;$j<=sizeof($coefficient1);$j++)
   { 
            $sumcoef=$coefficient1[$j]+$sumcoef;
	        if(($mat1[$j]<>$mat1[$j+1])&&($j<>0)){
                 
	 	          echo "<td >".$coefficient1[$j]."</td>";  
	 			  echo "<td bgcolor='#D9FBFF'>".$sumcoef."</td>";  
 	 	                  echo "<td bgcolor='#D9FBFF'>20</td>"; 
						  $sumcoef=0;
			}
			else
			{
	              echo "<td>".$coefficient1[$j]."</td>";  
			}
   }
  

   echo "</tr>";
   echo "<tr><td colspan='2' width='20%'>Prénom et Nom ";
  for($j=0;$j<=sizeof($sousmat1);$j++)
   {
     if(($mat1[$j]<>$mat1[$j+1])&&($j<>0)){
		 	 			 echo "<th height='250px'><span class='rotate'><div>".$sousmat1[$j]."</div></span></th>";
			  echo "<th height='250px' bgcolor='#D9FBFF'><span class='rotate'><div>Total ".$mat1[$j]."</div></span></th>";  
 	 	                  echo "<th height='250px' bgcolor='#D9FBFF'><span class='rotate'><div>Moyenne ".$mat1[$j]."</div></span></th>"; 
	                    }
	   else
	   		    {	
			 echo "<th height='250px'><span class='rotate'><div>".$sousmat1[$j]."</div></span></th>";
			            }  
	 	
   }
 echo "</tr>";
$col=0;
$sum=$coeft=0;
$sumd=$coefd=0;
$k=0;$n=1;
 for($i=0;$i<=sizeof($studentid);$i++)
 {
	 
	 if($studentid[$i]<>$studentid[$i-1]){ 
	   while($j<sizeof($sousmatid1) &&$j<>0) { echo "<td></td>"; $j++; }
	   echo "<td bgcolor='#D6F1FE'>".$sum."</td><td bgcolor='#D6F1FE'>".$coeft."</td>";
	 echo "</tr>";
	 if ($prenom[$i]!="") {echo "<tr><td colspan='2'>".$prenom[$i]."&nbsp;&nbsp;".$famille[$i]."</td>";$j=0;$k=0;$sum=$coeft=0;} else {exit(0);}
	 }
         $trig=0;
	   while(($j<=sizeof($sousmatid1))&&($trig==0)){
		 
		  if($discipid[$i]==$sousmatid1[$j]) { 
		  
			
		       echo "<td>".$grade[$i]."</td>"; $sum=$sum+$grade[$i];
			   $coeft=$coeft+ $discipcoef[$i];$trig=1;
			   
			
			   } 
			   else
			       {echo "<td></td>"; 
				 
			   }
				// $c[$mat1[$k]] 
     
	          $j++;
	

			   }
      
  }
}
?>
</body>
</html>
