<!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-break-inside: avoid; }
    @page {
        size: 210mm 297mm;
        margin: 7mm 7mm 7mm 7mm;
    }
}
body, td, th {
    font-family: "Times New Roman", Times, serif;
    font-size: 12px;
}
-->
</style>
</head>
<body bgcolor="#FFFFFF">

<form method="post" action="">
<?php
error_reporting(E_ALL & ~E_WARNING & ~E_DEPRECATED);
ini_set('log_errors', '0');

/*
 * STRUCTURE DE LA BASE DE DONNEES (confirmee) :
 * semesters         : id, name
 * classesSemesters  : classID, semesterID, ...
 * gradesSubjects    : classID, subSubjectID, coeficient (1 f!), ordering
 * grades            : id, studentID, classID, semesterID, subSubjectID, grade
 * subSubjects       : id, name, subjectID
 * subjects          : id, name, ordering
 * students          : id, firstName, lastName, classID, userID
 * users             : id, firstName
 * absenceclasse     : ?, studentID, ?, ?, nbAbsences, semesterID
 * observationsele   : studentID, obser
 */

function mention($totlagen, $moycoef) {
    if ($moycoef == 0) return " ";
    $moy = round($totlagen * 20 / $moycoef, 1);
    if      ($moy >= 18) return "EXC.";
    elseif  ($moy >= 16) return "T.B.";
    elseif  ($moy >= 14) return "B.";
    elseif  ($moy >= 12) return "A.B.";
    elseif  ($moy >= 10) return "P.";
    elseif  ($moy >  0)  return "F.";
    else                 return " ";
}

echo "<div class='noprint'>";
include 'header.php';
echo "</div>";

$cycle = $_GET['cycle'] ?? '';
include '_serveur.php';
?>
<input name="cycle" type="hidden" value="<?php echo htmlspecialchars($cycle); ?>">

<?php
/* ---- Liste des classes ---- */
$sql = 'SELECT * FROM classes WHERE cycleID="' . mysqli_real_escape_string($link, $cycle) . '" ORDER BY id ASC';
$req = mysqli_query($link, $sql);

echo '<div class="noprint">';
echo '<label class="w3-text-teal">Classe :</label> ';
echo '<select name="cla" class="w3-select w3-border w3-light-grey">';
while ($data = mysqli_fetch_assoc($req)) {
    echo '<option value="' . $data['id'] . '">' . strtoupper($data['name']) . '</option>';
}
echo '</select> ';
echo '<input name="chercher" type="submit" value="Envoyer" class="style19" />';
echo '</form>';
echo '<button type="button" onclick="window.print()"><img src="imprimante.png" height="32" width="40" /></button>';
echo '</div>';

if (!isset($_POST['chercher'])) exit;

$classeid1 = (int)$_POST['cla'];

/* ---- Semesters de la classe (dynamique) ---- */
$sql_sem = 'SELECT s.id, s.name
            FROM semesters s
            INNER JOIN classesSemesters cs ON cs.semesterID = s.id
            WHERE cs.classID = ' . $classeid1 . '
            ORDER BY s.id ASC';
$res_sem = mysqli_query($link, $sql_sem);

$ctrls = [];
if ($res_sem) {
    while ($r = mysqli_fetch_assoc($res_sem)) {
        $ctrls[(int)$r['id']] = $r['name'];
    }
}

if (empty($ctrls)) {
    echo "<p style='color:red'>Aucun semestre trouve pour cette classe.</p>";
    exit;
}

$ctrl_ids = array_keys($ctrls);
$nb_ctrls = count($ctrl_ids);

/* ---- Largeur egale pour toutes les colonnes notes + MG ---- */
$col_width = round(52 / ($nb_ctrls + 1), 1);

/* ---- Suppression anciennes moyennes ---- */
mysqli_query($link, 'DELETE FROM moyens WHERE classID=' . $classeid1);

/* ---- Sous-matieres et coefficients ---- */
$sql = 'SELECT
            gs.coeficient   AS coef,
            gs.subSubjectID AS subSubjectID,
            ss.subjectID    AS subjectID,
            ss.name         AS sousmat_name,
            su.id           AS matID,
            su.name         AS mat_name
        FROM gradesSubjects gs
        INNER JOIN subSubjects ss ON gs.subSubjectID = ss.id
        INNER JOIN subjects    su ON ss.subjectID    = su.id
        WHERE gs.classID = ' . $classeid1 . '
        ORDER BY su.ordering ASC, gs.ordering ASC';
$req = mysqli_query($link, $sql);
if (!$req) { echo "ERREUR matieres: " . mysqli_error($link); exit; }

$coef_arr = $sousmatid_arr = $matsous_arr = $sousmat_arr = $matid_arr = $mat_arr = [];
$i = 0;
while ($data = mysqli_fetch_assoc($req)) {
    $coef_arr[$i]      = $data['coef'];
    $sousmatid_arr[$i] = $data['subSubjectID'];
    $matsous_arr[$i]   = $data['subjectID'];
    $sousmat_arr[$i]   = $data['sousmat_name'];
    $matid_arr[$i]     = $data['matID'];
    $mat_arr[$i]       = $data['mat_name'];
    $i++;
}
$mat2   = array_unique($mat_arr,   SORT_REGULAR);
$matid2 = array_unique($matid_arr, SORT_REGULAR);

/* ---- Toutes les notes de la classe ----
   NOTE: On recupere les etudiants SEPAREMENT des notes pour eviter
   les problemes de LEFT JOIN qui duplique ou manque des eleves sans notes.
*/

/* Liste des eleves de la classe */
$sql_stu = 'SELECT
                st.id        AS studentid,
                st.firstName AS prenom,
                st.lastName  AS famille,
                us.firstName AS pere,
                cl.name      AS cl
            FROM students st
            INNER JOIN classes cl ON cl.id = st.classID
            LEFT JOIN  users   us ON us.id = st.userID
            WHERE st.classID = ' . $classeid1 . '
            ORDER BY st.firstName, st.lastName';
$res_stu = mysqli_query($link, $sql_stu);
if (!$res_stu) { echo "ERREUR eleves: " . mysqli_error($link); exit; }

$students = [];
while ($row = mysqli_fetch_assoc($res_stu)) {
    $students[] = $row;
}

if (empty($students)) {
    echo "<p style='color:red'>Classe vide ou sans eleves.</p>"; exit;
}

/* Toutes les notes de la classe indexees par [studentID][subSubjectID][semesterID] */
$sql_gr = 'SELECT
               gr.studentID    AS studentid,
               gr.subSubjectID AS discipid,
               gr.semesterID   AS ctrlid,
               gr.grade        AS grade,
               gs.coeficient   AS discipcoef
           FROM grades gr
           LEFT JOIN gradesSubjects gs
               ON gs.subSubjectID = gr.subSubjectID AND gs.classID = gr.classID
           WHERE gr.classID = ' . $classeid1 . '
           ORDER BY gr.studentID, gr.subSubjectID, gr.semesterID';
$res_gr = mysqli_query($link, $sql_gr);
if (!$res_gr) { echo "ERREUR notes: " . mysqli_error($link); exit; }

/* Index des notes : $grades_index[studentID][subSubjectID][semesterID] = ['grade'=>..,'coef'=>..] */
$grades_index = [];
while ($row = mysqli_fetch_assoc($res_gr)) {
    $sid  = $row['studentid'];
    $ssid = $row['discipid'];
    $cid  = (int)$row['ctrlid'];
    $grades_index[$sid][$ssid][$cid] = [
        'grade' => $row['grade'],
        'coef'  => (float)$row['discipcoef'],
    ];
}

/* ============================================================
   AFFICHAGE DES BULLETINS — un par eleve
   ============================================================ */
foreach ($students as $stu) {

    $sid_cur = $stu['studentid'];

    echo '<div class="page-break">';
    include 'titre.php';

    echo '<table width="98%" border="0" align="center" cellspacing="0">
            <tr class="carnet">
              <td width="80%" height="30">&nbsp;&nbsp;&nbsp;Nom de L\'élève :&nbsp;'
              . strtoupper($stu['prenom'])  . '&nbsp;&nbsp;'
              . strtoupper($stu['pere'])    . '&nbsp;&nbsp;'
              . strtoupper($stu['famille'])
              . '</td>
              <td width="20%" align="right">Classe :&nbsp;' . strtoupper($stu['cl']) . '</td>
            </tr>
          </table>';

    echo '<table width="98%" border="1" align="center" cellspacing="0">';
    echo '<tr class="carnet">
            <td width="40%" height="25">Matieres</td>
            <td width="8%" align="center"><b>Coef.</b></td>';
    foreach ($ctrls as $sid => $sname) {
        echo '<td width="' . $col_width . '%" align="center"><b>' . htmlspecialchars($sname) . '</b></td>';
    }
    echo '<td width="' . $col_width . '%" align="center"><b>M.G.</b></td>';
    echo '</tr>';

    $totlagen = array_fill_keys($ctrl_ids, 0);
    $moycoef  = array_fill_keys($ctrl_ids, 0);
    $coeftot  = 0;

    $sou = 0; /* index dans les tableaux sous-matieres */

    /* ---- Boucle matieres ---- */
    foreach ($mat2 as $s => $nom_mat) {

        if (empty($nom_mat)) { $sou++; continue; }

        $discpsum         = array_fill_keys($ctrl_ids, 0);
        $matcoef_par_ctrl = array_fill_keys($ctrl_ids, 0);
        $coefmat_current  = 0;

        echo '<tr>
                <td width="40%" height="25" align="center">
                  <label class="w3-text-blue"><b>' . htmlspecialchars($nom_mat) . '</b></label>
                </td>
                <td></td>';
        for ($c = 0; $c <= $nb_ctrls; $c++) echo '<td></td>';
        echo '</tr>';

        /* ---- Sous-matieres ---- */
        while (isset($matsous_arr[$sou]) && $matsous_arr[$sou] == $matid2[$s]) {

            $coef             = (int)$coef_arr[$sou];
            $subsubid         = $sousmatid_arr[$sou];
            $coefmat_current += $coef;
            $coeftot         += $coef;

            echo '<tr>
                    <td>' . htmlspecialchars($sousmat_arr[$sou]) . '</td>
                    <td align="center"><b>' . $coef . '</b></td>';

            $notes    = array_fill_keys($ctrl_ids, null);
            $sumg     = 0;
            $countgra = 0;

            /* Recuperer les notes depuis l'index (pas de boucle sequentielle fragile) */
            foreach ($ctrl_ids as $cid) {
                if (isset($grades_index[$sid_cur][$subsubid][$cid])) {
                    $entry = $grades_index[$sid_cur][$subsubid][$cid];
                    $g     = $entry['grade'];
                    $dc    = $entry['coef'];

                    if ($g == -1) {
                        $notes[$cid] = 'Abs.';
                    } else {
                        $notes[$cid]              = $g;
                        $matcoef_par_ctrl[$cid]  += $dc;
                        $discpsum[$cid]           += $g;
                        if ($g != 0) { $sumg += $g; $countgra++; }
                    }
                }
                /* Si pas de note pour ce ctrl : $notes[$cid] reste null → cellule vide */
            }

            foreach ($ctrl_ids as $cid) {
                $v = $notes[$cid];
                if ($v === null)       echo '<td></td>';
                elseif ($v === 'Abs.') echo '<td align="center">Abs.</td>';
                else                   echo '<td align="center">' . $v . '</td>';
            }

            if ($countgra == 0) $countgra = 1;
            $moyg = round($sumg / $countgra, 2);
            echo '<td align="center">' . ($moyg == 0 ? '' : $moyg) . '</td></tr>';

            $sou++;
        }

        /* ---- Total matiere ---- */
        echo '<tr><td><b>Total ' . htmlspecialchars($nom_mat) . '</b></td>';
        echo '<td align="center"><b>' . $coefmat_current . '</b></td>';
        $cd = 0; $ds = 0;
        foreach ($ctrl_ids as $cid) {
            if ($discpsum[$cid] != 0) {
                $cd++; $ds += $discpsum[$cid];
                echo '<td align="center"><b>' . $discpsum[$cid] . '</b></td>';
            } else {
                echo '<td></td>';
            }
        }
        if ($cd == 0) $cd = 1;
        $dt = round($ds / $cd, 2);
        echo '<td align="center"><b>' . ($dt == 0 ? '' : $dt) . '</b></td></tr>';

        foreach ($ctrl_ids as $cid) {
            $totlagen[$cid] += $discpsum[$cid];
            $moycoef[$cid]  += $matcoef_par_ctrl[$cid];
        }

    } /* fin foreach matieres */

    /* ---- Bilan general ---- */
    echo '<tr><td colspan="' . ($nb_ctrls + 3) . '" height="10px"></td></tr>';

    echo '<tr><td><b>Total General</b></td><td align="center"><b>' . $coeftot . '</b></td>';
    $cnt1 = 0; $som = 0;
    foreach ($ctrl_ids as $cid) {
        if ($totlagen[$cid] == 0) { echo '<td></td>'; }
        else { echo '<td align="center"><b>' . $totlagen[$cid] . '</b></td>'; $cnt1++; $som += $totlagen[$cid]; }
    }
    /* Total General MG last column:
       Formula: (total1 * ref_coef/coef1 + total2 * ref_coef/coef2 + ...) / nb_controls
       ref_coef = coefficient of the first semester (e.g. 600, 390, 400, etc.)
       Each semester total is normalized to the first semester's coef, then averaged. */
    $ref_coef = 0;
    foreach ($ctrl_ids as $cid) {
        if ($moycoef[$cid] > 0) { $ref_coef = $moycoef[$cid]; break; }
    }
    $tg_converted_sum = 0;
    $tg_converted_cnt = 0;
    foreach ($ctrl_ids as $cid) {
        if ($moycoef[$cid] > 0 && $totlagen[$cid] != 0) {
            $tg_converted_sum += $totlagen[$cid] * $ref_coef / $moycoef[$cid];
            $tg_converted_cnt++;
        }
    }
    $tg_mg_avg = ($tg_converted_cnt > 0) ? round($tg_converted_sum / $tg_converted_cnt, 2) : 0;
    echo '<td align="center"><b>' . ($tg_mg_avg == 0 ? '' : $tg_mg_avg) . '</b></td></tr>';

    echo '<tr><td><b>Moyenne Generale</b></td><td align="center"><b>20</b></td>';
    $sumMoy = 0; $nbMoy = 0;
    foreach ($ctrl_ids as $cid) {
        $mc = ($moycoef[$cid] == 0) ? 1 : $moycoef[$cid];
        if ($totlagen[$cid] == 0) { echo '<td></td>'; }
        else {
            $mv = round($totlagen[$cid] * 20 / $mc, 2);
            echo '<td align="center"><b>' . $mv . '</b></td>';
            $sumMoy += $mv; $nbMoy++;
        }
    }
    if ($nbMoy == 0) $nbMoy = 1;
    echo '<td align="center"><b>' . round($sumMoy / $nbMoy, 2) . '</b></td></tr>';

    echo '<tr><td><b>Mention</b></td><td></td>';
    foreach ($ctrl_ids as $cid) {
        $mc = ($moycoef[$cid] == 0) ? 1 : $moycoef[$cid];
        echo '<td align="center"><b>' . mention($totlagen[$cid], $mc) . '</b></td>';
    }
    echo '<td></td></tr>';

    /* ---- Absences ---- */
    $req_abs = mysqli_query($link, 'SELECT * FROM absenceclasse WHERE studentID=' . (int)$sid_cur);
    $abs = []; $studID = null;
    while ($d = mysqli_fetch_array($req_abs)) {
        $studID = $d[1];
        $abs[$studID][$d[5]] = $d[4];
    }
    echo '<tr><td><b>Absence</b></td><td></td>';
    foreach ($ctrl_ids as $cid) {
        $v = ($studID == $sid_cur && isset($abs[$studID][$cid])) ? $abs[$studID][$cid] : '';
        echo '<td align="center"><b>' . $v . '</b></td>';
    }
    echo '<td></td></tr>';

    echo '</table>';

    /* ---- Observations + Signatures ---- */
    $req_obs = mysqli_query($link, 'SELECT obser FROM observationsele WHERE studentID=' . (int)$sid_cur);
    $obser = '';
    while ($d = mysqli_fetch_array($req_obs)) { $obser = $d['obser']; }
    $obser = preg_replace('/\. /', '.<br>', $obser);

    echo '<div class="w3-panel w3-white w3-border w3-round-xlarge w3-border-black">
        <p class="m">Observations:</p> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>' . $obser . '
          </b></div>';
    echo '<div class="w3-cell-row">
  <div class="w3-container w3-round-xxlarge w3-border w3-cell w3-border-black">
    Direction<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span class="m"><b>Sr Carole Arrouk</b></span>
  </div>
  <div class="w3-container w3-round-xxlarge w3-cell"><p></p></div>
  <div class="w3-container w3-round-xxlarge w3-border w3-cell w3-border-black">
    <p>Parent</p>
  </div>
</div>';

    echo '</div>';

} /* fin foreach eleves */
?>
</body>
</html>
