Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Annotation\Route;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\ParamConverter;
use Knp\Snappy\Pdf;
use Knp\Bundle\SnappyBundle\Snappy\Response\PdfResponse;
use Symfony\Component\HttpFoundation\Session\SessionInterface;
use App\Repository\AttributionRepository;
use Doctrine\ORM\EntityManagerInterface;
use App\Repository\ClassRoomRepository;
use App\Repository\SchoolYearRepository;
use App\Repository\QuaterRepository;
use App\Repository\SequenceRepository;
use App\Repository\EvaluationRepository;
use App\Repository\StudentRepository;
use App\Repository\MainTeacherRepository;
use App\Repository\MarkRepository;
use App\Entity\ClassRoom;
use App\Entity\Course;
use App\Repository\LevelRepository;
use App\Entity\SchoolYear;
use App\Form\ClassRoomType;
use App\Entity\Sequence;
use App\Entity\Quater;
use App\Repository\SubscriptionRepository;
use App\Repository\InstallmentRepository;
use App\Service\SchoolYearService;
/**
* ClassRoom controller.
*
* @Route("prof/rooms")
*/
class ClassRoomController extends AbstractController
{
private $em;
private $repo;
private $scRepo;
private $stdRepo;
private $subRepo;
private $seqRepo;
private $evalRepo;
private $qtRepo;
private $markRepo;
private $snappy;
private $session;
private $quaterData;
private $annualMark;
private $annualAbs;
private $annualRanks;
private $imagesExists;
private Pdf $pdf;
private SchoolYearService $schoolYearService;
private MainTeacherRepository $mainTeacherRepo;
private AttributionRepository $attRepo;
private InstallmentRepository $instRepo;
private $annualAvgArray = [];
private $sumAvg = 0;
public function __construct(
Pdf $pdf,
InstallmentRepository $instRepo,
AttributionRepository $attRepo,
MainTeacherRepository $mainTeacherRepo,
SchoolYearService $schoolYearService,
MarkRepository $markRepo,
QuaterRepository $qtRepo,
StudentRepository $stdRepo,
EvaluationRepository $evalRepo,
SchoolYearRepository $scRepo,
SequenceRepository $seqRepo,
ClassRoomRepository $repo,
SubscriptionRepository $subRepo,
EntityManagerInterface $em,
Pdf $snappy,
SessionInterface $session
) {
$this->annualMark = [];
$this->annualAbs = [];
$this->annualRanks = [];
$this->quaterData = [];
$this->em = $em;
$this->pdf = $pdf;
$this->repo = $repo;
$this->scRepo = $scRepo;
$this->attRepo = $attRepo;
$this->seqRepo = $seqRepo;
$this->evalRepo = $evalRepo;
$this->mainTeacherRepo = $mainTeacherRepo;
$this->stdRepo = $stdRepo;
$this->instRepo = $instRepo;
$this->qtRepo = $qtRepo;
$this->subRepo = $subRepo;
$this->markRepo = $markRepo;
$this->snappy = $snappy;
$this->session = $session;
$this->schoolYearService = $schoolYearService;
}
/**
* Lists all ClassRoom entities.
*
* @Route("/", name="admin_classrooms")
* @Method("GET")
* @Template()
*/
public function indexAction(LevelRepository $levelRepository)
{
$classrooms = $this->repo->findAll();
$year = $this->schoolYearService->sessionYearById();
$seq = $this->seqRepo->findOneBy(['activated' => true]);
$mainTeachers = $this->mainTeacherRepo->findBy(['schoolYear' => $year]);
$mainTeachersMap = [];
foreach ($mainTeachers as $mt) {
$mainTeachersMap[$mt->getClassRoom()->getId()] = $mt->getTeacher();
}
$levels = $levelRepository->findAll();
return $this->render('classroom/index.html.twig', [
'mainTeachers' => $mainTeachersMap,
'classrooms' => $classrooms,
'levels' => $levels,
'year' => $year,
'seq' => $seq ? $seq->getId() : 0,
]);
}
/**
* Existence de fichiers image des élèves
*/
private function fileExists(ClassRoom $classroom, SchoolYear $year): array
{
$imageExists = [];
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$baseDir = $this->getParameter('kernel.project_dir') . '/public/assets/images/student/';
foreach ($studentEnrolled as $std) {
$matricule = $std->getMatricule();
$found = false;
foreach (['jpg', 'jpeg', 'PNG'] as $ext) {
if (file_exists($baseDir . $matricule . '.' . $ext)) {
$found = true;
break;
}
}
$imageExists[$std->getId()] = $found;
}
return $imageExists;
}
/**
* Calcule le facteur de zoom CSS à appliquer sur chaque bulletin.
*/
private function computeScale(ClassRoom $room): float
{
$nbCourses = 0;
foreach ($room->getModules() as $module) {
$nbCourses += count($module->getCourses());
}
return match (true) {
$nbCourses <= 12 => 0.95,
$nbCourses <= 16 => 0.90,
$nbCourses <= 20 => 0.85,
default => 0.80,
};
}
/**
* Finds and displays a ClassRoom entity.
*
* @Route("/{id}/show", name="admin_classrooms_show", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function showAction(ClassRoom $classroom, StudentRepository $stdRepo): Response
{
$year = $this->schoolYearService->sessionYearById();
$attributions = $this->attRepo->findByYearAndByRoom($year, $classroom);
$attributionsMapCourses = [];
foreach ($attributions as $att) {
$attributionsMapCourses[$att->getCourse()->getId()] = $att;
}
$officialExamResults = $this->subRepo->countByMention($year, $classroom);
$mentionCategories = [];
$mentionCountCategories = [];
$mentionMap = [
'0' => 'ECHEC', '1p' => 'PASSABLE', '1a' => 'ASSEZ-BIEN',
'1b' => 'BIEN', '1t' => 'TRES-BIEN', '1e' => 'EXCELLENT',
'A' => '5 POINTS', 'B' => '4 POINTS', 'C' => '3 POINTS',
'D' => '2 POINTS', 'E' => '1 POINT',
];
foreach ($officialExamResults as $exam) {
$mentionCategories[] = $mentionMap[$exam['officialExamResult']] ?? $exam['officialExamResult'];
$mentionCountCategories[] = $exam['count'];
}
$seqs = $this->seqRepo->findSequenceThisYear($year);
$evalSeqs = [];
foreach ($seqs as $seq) {
$evalSeqs[$seq->getId()] = $this->evalRepo->findBy([
'classRoom' => $classroom,
'sequence' => $seq,
]);
}
$allCoursesMap = [];
foreach ($evalSeqs as $seqEvals) {
foreach ($seqEvals as $eval) {
$cid = $eval->getCourse()->getId();
if (!isset($allCoursesMap[$cid])) {
$allCoursesMap[$cid] = $eval->getCourse()->getWording();
}
}
}
asort($allCoursesMap);
$allCourseIds = array_keys($allCoursesMap);
$allCourseLabels = array_values($allCoursesMap);
$matrix = [];
foreach ($allCourseIds as $cid) {
foreach ($seqs as $seq) {
$matrix[$cid][$seq->getId()] = null;
}
}
foreach ($evalSeqs as $seqId => $seqEvals) {
foreach ($seqEvals as $eval) {
$cid = $eval->getCourse()->getId();
$matrix[$cid][$seqId] = [
'avg' => $eval->getMoyenne() > 0 ? round($eval->getMoyenne(), 2) : null,
'success' => $eval->getSuccessH() + $eval->getSuccessF(),
'failures' => $eval->getFailluresH() + $eval->getFailluresF(),
'absents' => $eval->getAbscent(),
];
}
}
$heatmapData = [];
foreach ($allCourseIds as $cid) {
$row = ['course' => $allCoursesMap[$cid]];
foreach ($seqs as $seq) {
$sid = $seq->getId();
$row['s'.$sid] = $matrix[$cid][$sid] ? $matrix[$cid][$sid]['avg'] : null;
}
$heatmapData[] = $row;
}
$seqLabels = array_map(fn($s) => $s->getWording(), $seqs);
$seqIds = array_map(fn($s) => $s->getId(), $seqs);
$generalAvgBySeq = [];
foreach ($seqs as $seq) {
$sumWeighted = 0.0;
$sumCoef = 0;
$hasData = false;
foreach ($evalSeqs[$seq->getId()] as $eval) {
if ($eval->getMoyenne() > 0) {
$coef = $eval->getCourse()->getCoefficient();
$sumWeighted += $eval->getMoyenne() * $coef;
$sumCoef += $coef;
$hasData = true;
}
}
$generalAvgBySeq[] = $hasData && $sumCoef > 0
? round($sumWeighted / $sumCoef, 2)
: null;
}
$activeSeq = $this->seqRepo->findOneBy(['activated' => true]);
$successFailData = ['labels' => [], 'success' => [], 'failures' => [], 'absents' => []];
if ($activeSeq && isset($evalSeqs[$activeSeq->getId()])) {
foreach ($evalSeqs[$activeSeq->getId()] as $eval) {
if ($eval->getMoyenne() > 0) {
$successFailData['labels'][] = $eval->getCourse()->getWording();
$successFailData['success'][] = $eval->getSuccessH() + $eval->getSuccessF();
$successFailData['failures'][] = $eval->getFailluresH() + $eval->getFailluresF();
$successFailData['absents'][] = $eval->getAbscent();
}
}
}
$quaterProfiles = [];
$trimNames = [];
$seqsByQuater = [];
foreach ($seqs as $seq) {
$qid = $seq->getQuater()->getId();
$seqsByQuater[$qid][] = $seq;
$trimNames[$qid] = $seq->getQuater()->getWording();
}
foreach ($seqsByQuater as $qid => $qSeqs) {
$profile = [];
foreach ($allCourseIds as $cid) {
$sum = 0.0; $n = 0;
foreach ($qSeqs as $seq) {
$cell = $matrix[$cid][$seq->getId()] ?? null;
if ($cell && $cell['avg'] !== null) {
$sum += $cell['avg'];
$n++;
}
}
$profile[] = $n > 0 ? round($sum / $n, 2) : null;
}
$quaterProfiles[] = [
'label' => $trimNames[$qid],
'data' => $profile,
];
}
return $this->render('classroom/show.html.twig', [
'mainteacher' => $this->_getMainTeacher($classroom, $year),
'classroom' => $classroom,
'attributions' => $attributionsMapCourses,
'modules' => $classroom->getModules(),
'studentEnrolled' => $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year),
'fileExists' => $this->fileExists($classroom, $year),
'mentionCategories' => json_encode($mentionCategories),
'mentionCountCategories' => json_encode($mentionCountCategories),
'courseLabels' => json_encode($allCourseLabels),
'seqLabels' => json_encode($seqLabels),
'seqIds' => json_encode($seqIds),
'heatmapData' => json_encode($heatmapData),
'generalAvgBySeq' => json_encode($generalAvgBySeq),
'successFailData' => json_encode($successFailData),
'quaterProfiles' => json_encode($quaterProfiles),
'activeSeqLabel' => $activeSeq ? $activeSeq->getWording() : '',
]);
}
private function _getMainTeacher(ClassRoom $classroom, $year): ?object
{
foreach ($classroom->getMainTeachers() as $mainT) {
if ($mainT->getSchoolYear()->getId() === $year->getId()) {
return $mainT->getTeacher();
}
}
return null;
}
/**
* @Route("/{id}/stat", name="admin_classrooms_stat", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function statAction(ClassRoom $classroom)
{
return $this->render('classroom/show.html.twig', []);
}
/**
* @Route("/{id}/reportCardsYear", name="admin_classrooms_reportcards_year", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCardsYearAction(ClassRoom $classroom)
{
set_time_limit(600);
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
// Création des vues séquentielles via prepare/bindValue/execute (API legacy DBAL 3.x compatible)
$seqDefs = [
1 => "eval.sequence_id = 1",
2 => "eval.sequence_id = 2",
3 => "eval.sequence_id = 3",
4 => "eval.sequence_id = 4",
5 => "eval.sequence_id = 5",
6 => "eval.sequence_id = 6",
];
// SEQ1 : vue complète avec toutes les infos élève
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ1 AS
SELECT DISTINCT eval.id as eval, crs.id as crs, room.id as room, year.id as year,
std.matricule as matricule, std.image_name as profileImagePath,
std.lastname as lastname, std.firstname as firstname, std.birthday as birthday,
std.gender as gender, std.birthplace as birthplace,
teach.full_name as teacher, modu.name as module, crs.wording as wording,
crs.coefficient as coefficient, m.value as valeur, m.weight as weight,
m.appreciation as appreciation
FROM mark m
JOIN student std ON m.student_id = std.id
JOIN evaluation eval ON m.evaluation_id = eval.id
JOIN class_room room ON eval.class_room_id = room.id
JOIN course crs ON eval.course_id = crs.id
JOIN attribution att ON att.course_id = crs.id
JOIN user teach ON att.teacher_id = teach.id
JOIN module modu ON modu.id = crs.module_id
JOIN sequence seq ON seq.id = eval.sequence_id
JOIN quater quat ON seq.quater_id = quat.id
JOIN school_year year ON quat.school_year_id = year.id
WHERE room.id = ? AND eval.sequence_id = 1"
);
$stmt->bindValue(1, $classroom->getId());
$stmt->execute();
// SEQ2-6 : vues allégées
foreach ([2, 3, 4, 5, 6] as $i) {
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ{$i} AS
SELECT DISTINCT crs.id as crs, eval.id as eval, std.matricule as matricule,
m.value as valeur, m.weight as weight, m.appreciation as appreciation
FROM mark m
JOIN student std ON m.student_id = std.id
JOIN evaluation eval ON m.evaluation_id = eval.id
JOIN course crs ON eval.course_id = crs.id
WHERE eval.class_room_id = ? AND eval.sequence_id = {$i}
ORDER BY matricule, eval"
);
$stmt->bindValue(1, $classroom->getId());
$stmt->execute();
}
// BUG FIX : fetchAll() déprécié → fetchAllAssociative()
$dataYear = $connection->executeQuery(
"SELECT * FROM V_STUDENT_MARK_DATA_SEQ1
INNER JOIN V_STUDENT_MARK_DATA_SEQ2 ON V_STUDENT_MARK_DATA_SEQ1.matricule = V_STUDENT_MARK_DATA_SEQ2.matricule
INNER JOIN V_STUDENT_MARK_DATA_SEQ3 ON V_STUDENT_MARK_DATA_SEQ2.matricule = V_STUDENT_MARK_DATA_SEQ3.matricule
INNER JOIN V_STUDENT_MARK_DATA_SEQ4 ON V_STUDENT_MARK_DATA_SEQ3.matricule = V_STUDENT_MARK_DATA_SEQ4.matricule
INNER JOIN V_STUDENT_MARK_DATA_SEQ5 ON V_STUDENT_MARK_DATA_SEQ4.matricule = V_STUDENT_MARK_DATA_SEQ5.matricule
INNER JOIN V_STUDENT_MARK_DATA_SEQ6 ON V_STUDENT_MARK_DATA_SEQ5.matricule = V_STUDENT_MARK_DATA_SEQ6.matricule"
)->fetchAllAssociative();
$this->snappy->setTimeout(600);
$html = $this->renderView('classroom/reportcard/annual.html.twig', [
'year' => $year,
'data' => $dataYear,
'room' => $classroom,
'students' => $studentEnrolled,
]);
return new Response($this->snappy->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
]);
}
/**
* BUG FIX : $classroom et $seq n'étaient pas des paramètres de la méthode.
* Renommée en viewSeqForRoom pour éviter la confusion avec getViewSeqData.
*/
public function viewSeqForRoom(ClassRoom $classroom, Sequence $seq, int $i): void
{
$year = $this->schoolYearService->sessionYearById();
$connection = $this->em->getConnection();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_SEQ{$i} AS
SELECT DISTINCT eval.id as eval, crs.id as crs, room.id as room, year.id as year,
std.id as std, teach.full_name as teacher, modu.id as module,
m.value as value, m.weight as weight
FROM mark m
JOIN student std ON m.student_id = std.id
JOIN evaluation eval ON m.evaluation_id = eval.id
JOIN class_room room ON eval.class_room_id = room.id
JOIN course crs ON eval.course_id = crs.id
JOIN attribution att ON att.course_id = crs.id
JOIN user teach ON att.teacher_id = teach.id
JOIN module modu ON modu.id = crs.module_id
JOIN sequence seq ON seq.id = eval.sequence_id
JOIN quater quat ON seq.quater_id = quat.id
JOIN school_year year ON quat.school_year_id = year.id
WHERE att.year_id = ? AND room.id = ? AND eval.sequence_id = ?
ORDER BY room.id, modu.id, std"
);
$stmt->bindValue(1, $year->getId());
$stmt->bindValue(2, $classroom->getId());
$stmt->bindValue(3, $seq->getId());
$stmt->execute();
}
/**
* @Route("/{id}/reportCardsApcYearapc", name="admin_class_reportcards_apc_year", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCards2YearAction(ClassRoom $classroom)
{
set_time_limit(600);
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
foreach ($sequences as $seq) {
$this->getViewSeqData($classroom, $seq);
}
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_1 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ1 seq1
LEFT JOIN V_STUDENT_MARK_SEQ2 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY seq1.std"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_2 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ3 seq1
LEFT JOIN V_STUDENT_MARK_SEQ4 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY seq1.std"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_3 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ5 seq1
LEFT JOIN V_STUDENT_MARK_SEQ6 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY seq1.std"
);
$stmt->execute();
// BUG FIX : "JOIN LEFT" → "LEFT JOIN"
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW ANNUAL_DATA AS
SELECT DISTINCT student.id as idStd, student.matricule as matricule,
student.image_name as profileImagePath,
student.lastname as lastname, student.firstname as firstname,
student.birthday as birthday, student.gender as gender,
student.birthplace as birthplace,
class_room.name as room_name,
course.wording as course, course.coefficient as coef,
module.name as module, user.full_name as teacher,
quat1.std, quat1.modu,
quat1.value as value1, quat1.weight as weight1,
quat2.value as value2, quat2.weight as weight2,
quat3.value as value3, quat3.weight as weight3,
greatest(quat1.weight, quat2.weight, quat3.weight) as weight,
(quat1.value*quat1.weight + quat2.value*quat2.weight + quat3.value*quat3.weight)
/ (quat1.weight+quat2.weight+quat3.weight) as value
FROM student
JOIN V_STUDENT_MARK_QUATER_1 quat1 ON student.id = quat1.std
JOIN class_room ON class_room.id = quat1.room
JOIN course ON course.id = quat1.crs
JOIN module ON course.module_id = quat1.modu
JOIN user ON user.full_name = quat1.teacher
LEFT JOIN V_STUDENT_MARK_QUATER_2 quat2 ON quat1.std = quat2.std AND quat1.crs = quat2.crs
LEFT JOIN V_STUDENT_MARK_QUATER_3 quat3 ON quat1.std = quat3.std AND quat1.crs = quat3.crs
ORDER BY quat1.std, quat1.modu"
);
$stmt->execute();
// BUG FIX : fetchAll() → fetchAllAssociative()
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA")->fetchAllAssociative();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT idStd,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM ANNUAL_DATA
GROUP BY idStd
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$rank = 0;
$rankArray = [];
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['idStd']] = $avg['moyenne'];
$rankArray[$avg['idStd']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
$this->snappy->setTimeout(600);
$html = $this->renderView('classroom/reportcardYear.html.twig', [
'year' => $year,
'data' => $dataYear,
'room' => $classroom,
'students' => $studentEnrolled,
'ranks' => $rankArray,
'means' => $this->annualAvgArray,
'genMean' => $this->sumAvg / sizeof($this->annualAvgArray),
]);
return new Response($this->snappy->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
]);
}
/**
* @Route("/{room}/{seq}/pdf", name="admin_classrooms_recapitulatif", requirements={"room"="\d+","seq"="\d+"})
* @Method("GET")
* @Template()
*/
public function recapitulatifAction(ClassRoom $room, Sequence $seq)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$html = $this->renderView('classroom/templating/recapitulatifseqvierge.html.twig', [
'room' => $room,
'seq' => $seq,
'students' => $studentEnrolled,
'year' => $year,
]);
return new Response(
$this->pdf->getOutputFromHtml($html, ['orientation' => 'Landscape', 'page-size' => 'A4']),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fiche_recep_' . $room->getName() . '.pdf"',
]
);
}
/**
* @Route("/{id}/recapitulatifseq", name="admin_classrooms_recapitulatif_seq", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function recapSeqAction(ClassRoom $room, Request $request)
{
$checkedValues = $request->request->get('selected_courses');
$year = $this->schoolYearService->sessionYearById();
$seq = $this->seqRepo->findOneBy(['activated' => true]);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$datas = $this->markRepo->findMarksBySequenceAndClassOrderByStd($seq, $room);
$html = $this->renderView('classroom/recapitulatifseq.html.twig', [
'room' => $room,
'datas' => $datas,
'year' => $year,
'seq' => $seq,
'students' => $studentEnrolled,
'checkedValues' => $checkedValues,
]);
return new Response($html);
}
/**
* @Route("/create", name="admin_classrooms_new", methods={"GET","POST"})
*/
public function create(Request $request): Response
{
if (!$this->getUser()) {
$this->addFlash('warning', 'You need login first!');
return $this->redirectToRoute('app_login');
}
$schoolyear = new ClassRoom();
$form = $this->createForm(ClassRoomType::class, $schoolyear);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$this->em->persist($schoolyear);
$this->em->flush();
$this->addFlash('success', 'ClassRoom successfully created');
return $this->redirectToRoute('admin_classrooms');
}
return $this->render('classroom/new.html.twig', ['form' => $form->createView()]);
}
/**
* @Route("/{id}/edit", name="admin_classrooms_edit", requirements={"id"="\d+"}, methods={"GET","PUT"})
* @Template()
*/
public function edit(Request $request, ClassRoom $room): Response
{
$form = $this->createForm(ClassRoomType::class, $room, ['method' => 'PUT']);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$this->em->flush();
$this->addFlash('success', 'ClassRoom successfully updated');
return $this->redirectToRoute('admin_classrooms');
}
return $this->render('classroom/edit.html.twig', [
'room' => $room,
'form' => $form->createView(),
]);
}
/**
* @Route("/{id}/delete", name="admin_classrooms_delete", requirements={"id"="\d+"}, methods={"DELETE"})
*/
public function delete(ClassRoom $q, Request $request): Response
{
$this->em->remove($q);
$this->em->flush();
$this->addFlash('info', 'ClassRoom successfully deleted');
return $this->redirectToRoute('admin_classrooms');
}
/**
* @Route("/{id}/fichesimple", name="admin_classrooms_fichesimple", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function fichesiplmeAction(ClassRoom $classroom)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/fiche_repport_notes.html.twig', [
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fiche_pv_' . $classroom->getName() . '.pdf"',
]);
}
/**
* @Route("/{id}/disciplinary_record", name="admin_classrooms_disciplinary_record", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function ficheDisciplineAction(ClassRoom $classroom)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/fiche_repport_disc.html.twig', [
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fich_disc_' . $classroom->getName() . '.pdf"',
]);
}
/**
* @Route("/{id}/presentation", name="admin_classrooms_presentation", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function presentationAction(ClassRoom $classroom)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/student_list.html.twig', [
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="std_list_' . $classroom->getName() . '.pdf"',
]);
}
/**
* @Route("/{id_room}/{id_seq}/sqavg", name="admin_classrooms_avg_seq", requirements={"id_room"="\d+", "id_seq"="\d+"})
* @ParamConverter("room", options={"mapping": {"id_room" : "id"}})
* @ParamConverter("seq", options={"mapping": {"id_seq" : "id"}})
* @Method("GET")
* @Template()
*/
public function generalSeqAverage(ClassRoom $room, Sequence $seq)
{
$dql = "SELECT SUM(evaluation.moyenne * course.coefficient)/SUM(course.coefficient)
FROM App\Entity\Evaluation evaluation, App\Entity\Course course
WHERE evaluation.course = course.id
AND evaluation.sequence = ?2
AND evaluation.classRoom = ?1";
$avg = $this->em->createQuery($dql)
->setParameter(1, $room->getId())
->setParameter(2, $seq->getId())
->getSingleScalarResult();
return round($avg, 2);
}
/**
* @Route("/{id_room}/{id_quat}/qtavg", name="admin_classrooms_avg_quat", requirements={"id_room"="\d+", "id_quat"="\d+"})
* @ParamConverter("room", options={"mapping": {"id_room" : "id"}})
* @ParamConverter("quater", options={"mapping": {"id_quat" : "id"}})
* @Method("GET")
* @Template()
*/
public function generalQuatAverage(ClassRoom $room, Quater $quater)
{
$dql = "SELECT SUM(evaluation.moyenne * course.coefficient)/SUM(course.coefficient)
FROM App\Entity\Evaluation evaluation, App\Entity\Course course
WHERE evaluation.course = course.id
AND evaluation.sequence = ?2
AND evaluation.classRoom = ?1";
$avg_seq = 0;
foreach ($quater->getSequences() as $seq) {
$avg_seq += $this->em->createQuery($dql)
->setParameter(1, $room->getId())
->setParameter(2, $seq->getId())
->getSingleScalarResult();
}
return round($avg_seq / 2, 2);
}
/**
* @Route("/{room}/pdf", name="admin_classrooms_blanc_ann", requirements={"room"="\d+"})
* @Method("GET")
* @Template()
*/
public function annualSummaryAction(ClassRoom $room)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$html = $this->renderView('classroom/templating/blankAnnualForm.html.twig', [
'room' => $room,
'students' => $studentEnrolled,
'year' => $year,
]);
return new Response(
$this->pdf->getOutputFromHtml($html, ['default-header' => false]),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="recap_empty_' . $room->getName() . '.pdf"',
]
);
}
/**
* @Route("/{id}/reportCardSeq", name="admin_classrooms_reportcards_seq", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCardSeqAction(ClassRoom $classroom)
{
set_time_limit(600);
$totalNtCoef = 0;
$totalCoef = 0;
$year = $this->schoolYearService->sessionYearById();
$sequence = $this->seqRepo->findOneBy(['activated' => true]);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$evaluations = $this->evalRepo->findSequantialExamsOfRoom($classroom->getId(), $sequence->getId());
foreach ($evaluations as $ev) {
$totalNtCoef += $ev->getMoyenne() * $ev->getCourse()->getCoefficient();
$totalCoef += $ev->getCourse()->getCoefficient();
}
$this->getViewSeqData($classroom, $sequence);
$connection = $this->em->getConnection();
// BUG FIX : fetchAll() → fetchAllAssociative()
$datas = $connection->executeQuery("SELECT * FROM V_STUDENT_MARK_SEQ0")->fetchAllAssociative();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT std,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM V_STUDENT_MARK_SEQ0
GROUP BY std
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$seqAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$seqAvgArray = [];
$sumAvg = 0;
$rank = 0;
$rankArray = [];
foreach ($seqAvg as $avg) {
$seqAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
}
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_SEQ AS
SELECT DISTINCT seq.std as std, seq.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ0 seq
ORDER BY std"
);
$stmt->execute();
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_SEQ")->fetchAllAssociative();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$html = $this->renderView('classroom/reportcard/sequential.html.twig', [
'year' => $year,
'datas' => $datas,
'students' => $studentEnrolled,
'sequence' => $sequence,
'quater' => $sequence->getQuater(),
'room' => $classroom,
'means' => $seqAvgArray,
'abscences' => $absencesArray,
'genMean' => $sumAvg / sizeof($seqAvgArray),
'ranks' => $rankArray,
'fileExists' => $this->fileExists($classroom, $year),
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="bull_seq_' . $classroom->getName() . '.pdf"',
]);
}
public function buildAbsViewSeq(ClassRoom $room, Sequence $seq): void
{
$connection = $this->em->getConnection();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $seq->getId() . " AS
SELECT DISTINCT abs.student_id as std, SUM(abs.weight) as total_hours
FROM class_room room
LEFT JOIN abscence_sheet sheet ON sheet.class_room_id = room.id AND sheet.sequence_id = ?
LEFT JOIN abscence abs ON sheet.id = abs.abscence_sheet_id
WHERE room.id = ?
GROUP BY std
ORDER BY std"
);
$stmt->bindValue(1, $seq->getId());
$stmt->bindValue(2, $room->getId());
$stmt->execute();
}
public function getAbsSeqFromView(ClassRoom $room, Sequence $seq): array
{
$connection = $this->em->getConnection();
return $connection->executeQuery(
"SELECT * FROM V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $seq->getId()
)->fetchAllAssociative();
}
public function buildAbsQuaterView(ClassRoom $room, Quater $quater): void
{
$sequences = $this->seqRepo->findBy(['quater' => $quater]);
foreach ($sequences as $seq) {
$this->buildAbsViewSeq($room, $seq);
}
$query = "CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quater->getId() . " AS
SELECT DISTINCT seq1.std AS std,
seq1.total_hours + IFNULL(seq2.total_hours, 0) AS total_hours
FROM V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2 ON seq1.std = seq2.std
UNION
SELECT DISTINCT seq2.std AS std,
IFNULL(seq1.total_hours, 0) + seq2.total_hours AS total_hours
FROM V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2
LEFT JOIN V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1 ON seq1.std = seq2.std
ORDER BY std";
$this->em->getConnection()->executeQuery($query);
}
public function getAbsQuaterFromView(ClassRoom $room, Quater $quater): array
{
$this->buildAbsQuaterView($room, $quater);
$query = "SELECT * FROM V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quater->getId();
$connection = $this->em->getConnection();
$absences = $connection->executeQuery($query)->fetchAllAssociative();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['total_hours'];
}
return $absencesArray;
}
public function getViewSeqData(ClassRoom $room, Sequence $seq): void
{
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $seq->getId() . " AS
SELECT DISTINCT eval.id as eval, crs.id as crs, crs.coefficient as coef,
room.id as room, std.id as std, teach.full_name as teacher,
modu.id as module, m.value as value, m.weight as weight
FROM mark m
JOIN student std ON m.student_id = std.id
JOIN evaluation eval ON m.evaluation_id = eval.id
JOIN class_room room ON eval.class_room_id = room.id
JOIN course crs ON eval.course_id = crs.id
JOIN attribution att ON att.course_id = crs.id AND att.year_id = ?
JOIN user teach ON att.teacher_id = teach.id
JOIN module modu ON modu.id = crs.module_id
JOIN sequence seq ON seq.id = eval.sequence_id
LEFT JOIN abscence_sheet sheet ON seq.id = sheet.sequence_id AND sheet.class_room_id = room.id
LEFT JOIN abscence abs ON sheet.id = abs.abscence_sheet_id
JOIN quater quat ON seq.quater_id = quat.id
WHERE room.id = ? AND eval.sequence_id = ?
ORDER BY room.id, modu.id, std"
);
$stmt->bindValue(1, $year->getId());
$stmt->bindValue(2, $room->getId());
$stmt->bindValue(3, $seq->getId());
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $seq->getId() . " AS
SELECT DISTINCT room.id as room, abs.student_id as std, SUM(abs.weight) as total_hours
FROM class_room room
LEFT JOIN abscence_sheet sheet ON sheet.class_room_id = room.id AND sheet.sequence_id = ?
LEFT JOIN abscence abs ON sheet.id = abs.abscence_sheet_id
WHERE room.id = ?
GROUP BY std
ORDER BY room.id, std"
);
$stmt->bindValue(1, $seq->getId());
$stmt->bindValue(2, $room->getId());
$stmt->execute();
}
public function getViewSeqMark2024(ClassRoom $room, Sequence $seq): void
{
$connection = $this->em->getConnection();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $seq->getId() . " AS
SELECT DISTINCT crs.id as crs, crs.coefficient as coef, std.id as std,
m.value as value, m.weight as weight, eval.mini as mini, eval.maxi as maxi
FROM mark m
JOIN student std ON m.student_id = std.id
JOIN evaluation eval ON m.evaluation_id = eval.id
JOIN class_room room ON eval.class_room_id = room.id
JOIN course crs ON eval.course_id = crs.id
JOIN sequence seq ON seq.id = eval.sequence_id
WHERE room.id = ? AND eval.sequence_id = ?
ORDER BY std, crs"
);
$stmt->bindValue(1, $room->getId());
$stmt->bindValue(2, $seq->getId());
$stmt->execute();
}
/**
* BUG FIX : $classroom → $room, $fileExists → $this->fileExists($room, $year)
*
* @Route("/{id}/reportCardsTrim", name="admin_classrooms_reportcards_trim", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCardsTrimAction(ClassRoom $room, Request $request)
{
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$quater = $this->qtRepo->findOneBy(['activated' => true]);
$sequences = $this->seqRepo->findBy(['quater' => $quater]);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
// BUG FIX : $classroom → $room
$fileExists = $this->fileExists($room, $year);
foreach ($sequences as $seq) {
$this->getViewSeqData($room, $seq);
}
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as module, seq1.room as room
FROM V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1
JOIN V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std, module"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER AS
SELECT DISTINCT seq1.std as std, seq1.total_hours + seq2.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2
ON (seq1.std = seq2.std)
ORDER BY std"
);
$stmt->execute();
// BUG FIX : fetchAll() → fetchAllAssociative()
$dataQuater = $connection->executeQuery("SELECT * FROM V_STUDENT_MARK_QUATER")->fetchAllAssociative();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT std,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM V_STUDENT_MARK_QUATER
GROUP BY std
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$quaterAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$quaterAvgArray = [];
$sumAvg = 0;
$rank = 0;
$rankArray = [];
foreach ($quaterAvg as $avg) {
$quaterAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
}
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_QUATER")->fetchAllAssociative();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$this->pdf->setTimeout(600);
$html = $this->renderView('classroom/reportcard/quaterly_2024.html.twig', [
'year' => $year,
'data' => $dataQuater,
'ranks' => $rankArray,
'means' => $quaterAvgArray,
'abscences' => $absencesArray,
'genMean' => $sumAvg / sizeof($quaterAvgArray),
'room' => $room,
'quater' => $quater,
'sequences' => $sequences,
'students' => $studentEnrolled,
// BUG FIX : variable correctement définie
'fileExists' => $fileExists,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="' . $room->getName() . '.pdf"',
]);
}
/**
* @Route("/{id}/reportCardsTrim2024", name="admin_classrooms_reportcards_trim_2024", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCardsTrim2024Action(ClassRoom $room, Request $request)
{
if (!$this->getUser()) {
$this->addFlash('warning', 'You need login first!');
return $this->redirectToRoute('app_login');
}
if (!$this->getUser()->isVerified()) {
$this->addFlash('warning', 'You need to have a verified account!');
return $this->redirectToRoute('app_login');
}
$copyright = $request->request->get('copyright') == "on";
$reverse = $request->request->get('reverse') == "on";
$scale = $request->request->get('scale')
? max(0.70, min(1.00, (float) $request->request->get('scale')))
: 1.0;
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$quater = $this->qtRepo->findOneBy(['activated' => true]);
$students = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$mainTeacher = $this->mainTeacherRepo->findOneBy(['classRoom' => $room, 'schoolYear' => $year])->getTeacher();
$query = "SELECT DISTINCT student.id as student_id, student.firstname as student_firstname,
student.lastname as student_last_name, student.birthday as student_birthday,
student.matricule as matricule, sequence.id as sequence, course.id as course_id,
course.wording, course.coefficient, mark.value, mark.weight, mark.rank2,
evaluation.mini as mini, evaluation.maxi as maxi, evaluation.competence,
attribution.teacher_id, school_year.id, user.full_name
FROM sequence
JOIN evaluation ON evaluation.sequence_id = sequence.id AND evaluation.class_room_id = :room_id
JOIN course ON evaluation.course_id = course.id
JOIN attribution ON attribution.course_id = course.id
JOIN user ON user.id = attribution.teacher_id
JOIN mark ON evaluation.id = mark.evaluation_id
JOIN student ON mark.student_id = student.id
JOIN quater ON sequence.quater_id = quater.id
JOIN school_year ON quater.school_year_id = school_year.id AND school_year.id = attribution.year_id
WHERE quater.id = :quater_id
ORDER BY student_id, course.id, sequence.id";
$dataMarks = $connection->executeQuery($query, [
'quater_id' => $quater->getId(),
'room_id' => $room->getId(),
])->fetchAllAssociative();
$sequences = $this->seqRepo->findBy(['quater' => $quater]);
foreach ($sequences as $seq) {
$this->getViewSeqMark2024($room, $seq);
}
$connection->executeQuery(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
(seq1.mini + seq2.mini)/2 as mini, (seq1.maxi + seq2.maxi)/2 as maxi
FROM V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1
LEFT JOIN V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std"
);
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT std,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM V_STUDENT_MARK_QUATER
GROUP BY std
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$quaterAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$quaterAvgArray = [];
$sumAvg = 0;
$rank = 0;
$minAvg = 20;
$maxAvg = 0;
$rankArray = [];
foreach ($quaterAvg as $avg) {
$quaterAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
if ($minAvg > $avg['moyenne']) $minAvg = $avg['moyenne'];
if ($maxAvg < $avg['moyenne']) $maxAvg = $avg['moyenne'];
}
$html = $this->renderView('classroom/reportcard/quaterly_2024.html.twig', [
'genMean' => $sumAvg / sizeof($quaterAvgArray),
'ranks' => $rankArray,
'year' => $year,
'minAvg' => $minAvg,
'maxAvg' => $maxAvg,
'quater' => $quater,
'mainTeacher' => $mainTeacher,
'dataMarks' => $dataMarks,
'dataAbs' => $this->getAbsQuaterFromView($room, $quater),
'students' => $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year),
'room' => $room,
'fileExists' => $this->fileExists($room, $year),
'copyright' => $copyright,
'reverse' => $reverse,
'scale' => $scale,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="bull_' . $quater->getId() . '.pdf"',
]);
}
/**
* BUG FIX : $room → $classroom, $i non initialisé → $i = 0, $sumAvg local
*
* @Route("/{id}/annualavglist", name="admin_avg_list", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function annualAvgList(ClassRoom $classroom, Request $request)
{
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
// BUG FIX : $i initialisé
$i = 0;
foreach ($sequences as $seq) {
$this->getViewSeqData($classroom, $seq);
$i++;
}
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_1 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[0]->getId() . " seq1
LEFT JOIN V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[1]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std, modu"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_2 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[2]->getId() . " seq1
JOIN V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[3]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std, modu"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_3 AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[4]->getId() . " seq1
JOIN V_STUDENT_MARK_SEQ" . $classroom->getId() . "_" . $sequences[5]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std, modu"
);
$stmt->execute();
// BUG FIX : virgule manquante dans le SQL original entre student.matricule et student.lastname
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW ANNUAL_DATA AS
SELECT DISTINCT student.id as idStd, student.matricule as matricule,
student.lastname as lastname, student.firstname as firstname,
course.wording as course, course.coefficient as coef,
module.name as module, user.full_name as teacher,
quat1.std, quat1.modu,
quat1.value as value1, quat1.weight as weight1,
quat2.value as value2, quat2.weight as weight2,
quat3.value as value3, quat3.weight as weight3,
greatest(quat1.weight, quat2.weight, quat3.weight) as weight,
(quat1.value*quat1.weight + quat2.value*quat2.weight + quat3.value*quat3.weight)
/ (quat1.weight+quat2.weight+quat3.weight) as value
FROM student
LEFT JOIN V_STUDENT_MARK_QUATER_1 quat1 ON student.id = quat1.std
LEFT JOIN V_STUDENT_MARK_QUATER_2 quat2 ON student.id = quat2.std AND quat1.crs = quat2.crs
LEFT JOIN V_STUDENT_MARK_QUATER_3 quat3 ON student.id = quat3.std AND quat2.crs = quat3.crs
JOIN class_room ON class_room.id = quat1.room
JOIN course ON course.id = quat1.crs
JOIN module ON course.module_id = quat1.modu
JOIN user ON user.full_name = quat1.teacher
ORDER BY quat1.std, quat1.modu"
);
$stmt->execute();
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA")->fetchAllAssociative();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT idStd,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM ANNUAL_DATA
GROUP BY idStd
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$rank = 0;
$rankArray = [];
// BUG FIX : $sumAvg local
$sumAvg = 0;
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['idStd']] = $avg['moyenne'];
$rankArray[$avg['idStd']] = ++$rank;
$sumAvg += $avg['moyenne'];
}
$html = $this->renderView('classroom/avglist.html.twig', [
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
'ranks' => $rankArray,
'means' => $this->annualAvgArray,
'genMean' => $sumAvg / sizeof($this->annualAvgArray),
]);
return new Response(
$this->snappy->getOutputFromHtml($html, ['page-size' => 'A4']),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
]
);
}
/**
* @Route("/{id}/reportCards3ApcYearApc", name="admin_class_reportcards_3_apc_year", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCards3YearAction(ClassRoom $classroom, Request $request)
{
$headerFontSize = $request->request->get('header_font_size');
$lineHeight = $request->request->get('line_height');
$copyright = $request->request->get('copyright') == "on";
$reverse = $request->request->get('reverse') == "on";
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
foreach ($sequences as $seq) {
$this->getViewSeqData($classroom, $seq);
}
$trimDefs = [
['V_STUDENT_MARK_QUATER_1', 0, 1, 'V_STUDENT_ABSCENCE_QUATER_1'],
['V_STUDENT_MARK_QUATER_2', 2, 3, 'V_STUDENT_ABSCENCE_QUATER_2'],
['V_STUDENT_MARK_QUATER_3', 4, 5, 'V_STUDENT_ABSCENCE_QUATER_3'],
];
foreach ($trimDefs as [$viewName, $idx1, $idx2, $absViewName]) {
$s1 = $sequences[$idx1];
$s2 = $sequences[$idx2];
$rid = $classroom->getId();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW {$viewName} AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(seq1.value*seq1.weight + seq2.value*seq2.weight)/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
seq1.teacher as teacher, seq1.module as modu, seq1.room as room
FROM V_STUDENT_MARK_SEQ{$rid}_{$s1->getId()} seq1
LEFT JOIN V_STUDENT_MARK_SEQ{$rid}_{$s2->getId()} seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std, modu"
);
$stmt->execute();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW {$absViewName} AS
SELECT DISTINCT seq1.std as std, seq1.total_hours + seq2.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ{$rid}_{$s1->getId()} seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ{$rid}_{$s2->getId()} seq2 ON (seq1.std = seq2.std)
ORDER BY std"
);
$stmt->execute();
}
set_time_limit(600);
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW ANNUAL_DATA AS
SELECT DISTINCT student.id as idStd, student.matricule as matricule,
student.image_name as profileImagePath,
student.lastname as lastname, student.firstname as firstname,
student.birthday as birthday, student.gender as gender,
student.birthplace as birthplace, class_room.name as room_name,
course.wording as course, course.coefficient as coef,
module.name as module, user.full_name as teacher,
quat1.std, quat1.modu,
quat1.value as value1, quat1.weight as weight1,
quat2.value as value2, quat2.weight as weight2,
quat3.value as value3, quat3.weight as weight3,
greatest(quat1.weight, quat2.weight, quat3.weight) as weight,
(quat1.value*quat1.weight + quat2.value*quat2.weight + quat3.value*quat3.weight)
/ (quat1.weight+quat2.weight+quat3.weight) as value
FROM student
LEFT JOIN V_STUDENT_MARK_QUATER_1 quat1 ON student.id = quat1.std
LEFT JOIN V_STUDENT_MARK_QUATER_2 quat2 ON student.id = quat2.std AND quat1.crs = quat2.crs
LEFT JOIN V_STUDENT_MARK_QUATER_3 quat3 ON student.id = quat3.std AND quat2.crs = quat3.crs
LEFT JOIN class_room ON class_room.id = quat1.room
JOIN course ON course.id = quat1.crs
JOIN module ON course.module_id = quat1.modu
JOIN user ON user.full_name = quat1.teacher
ORDER BY quat1.std, quat1.modu"
);
$stmt->execute();
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA")->fetchAllAssociative();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
SELECT DISTINCT idStd,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM ANNUAL_DATA
GROUP BY idStd
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS")->fetchAllAssociative();
$rank = 0;
$rankArray = [];
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['idStd']] = $avg['moyenne'];
$rankArray[$avg['idStd']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_ANNUAL AS
SELECT DISTINCT q1.std as std, q1.abscences + q2.abscences + q3.abscences as abscences
FROM V_STUDENT_ABSCENCE_QUATER_1 q1
LEFT JOIN V_STUDENT_ABSCENCE_QUATER_2 q2 ON (q1.std = q2.std)
LEFT JOIN V_STUDENT_ABSCENCE_QUATER_3 q3 ON (q1.std = q3.std)
ORDER BY std"
);
$stmt->execute();
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_ANNUAL")->fetchAllAssociative();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$html = $this->renderView('classroom/reportcard/annual.html.twig', [
'headerFontSize' => $headerFontSize,
'lineHeight' => $lineHeight,
'copyright' => $copyright,
'reverse' => $reverse,
'year' => $year,
'data' => $dataYear,
'room' => $classroom,
'students' => $studentEnrolled,
'abscences' => $absencesArray,
'ranks' => $rankArray,
'means' => $this->annualAvgArray,
'genMean' => $this->sumAvg / sizeof($this->annualAvgArray),
// BUG FIX : $this->imagesExist → $this->fileExists()
'fileExists' => $this->fileExists($classroom, $year),
]);
return new Response(
$this->snappy->getOutputFromHtml($html, ['page-size' => 'A4']),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
]
);
}
public function ranking(ClassRoom $room, SchoolYear $year): array
{
$connection = $this->em->getConnection();
$stmt = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_RANKS" . $room->getId() . "_" . $year->getId() . " AS
SELECT DISTINCT std,
CAST(SUM(value*weight*coef) / SUM(weight*coef) AS decimal(4,2)) as moyenne,
SUM(weight*coef) as totalCoef
FROM V_STUDENT_MARK_YEAR" . $room->getId() . "_" . $year->getId() . "
GROUP BY std
ORDER BY SUM(value*weight*coef) DESC"
);
$stmt->execute();
$annualAvg = $connection->executeQuery(
"SELECT * FROM V_STUDENT_RANKS" . $room->getId() . "_" . $year->getId() . " WHERE totalCoef > 0"
)->fetchAllAssociative();
$this->sumAvg = 0;
$rank = 0;
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['std']] = $avg['moyenne'];
$this->annualRanks[$avg['std']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
return array_filter($this->annualRanks, fn($v, $k) => $k !== '', ARRAY_FILTER_USE_BOTH);
}
/**
* @Route("/{id}/reportCards4ApcYearApc", name="admin_class_reportcards_year_2024", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function reportCards2024YearAction(ClassRoom $room, Request $request)
{
// Parametres de presentation du bulletin
$headerFontSize = $request->request->get('header_font_size');
$bodyFontSize = $request->request->get('body_font_size');
$lineHeight = $request->request->get('line_height');
$copyright = $request->request->get('copyright')=="on";
$reverse = $request->request->get('reverse')=="on";
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
$quaters = $year->getQuaters();
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$mainTeacher = $this->mainTeacherRepo->findOneBy(['classRoom' => $room, 'schoolYear' => $year])->getTeacher();
$this->getViewYearMark2024($room, $year);
$absencesArray = [];
$this->annualAbs = $this->getAbsYearFromView($room, $year);
foreach ($this->annualAbs as $abs) {
$absencesArray[$abs['std']] = $abs['total_hours'];
}
set_time_limit(600);
$this->getViewYearMark2024($room, $year);
// For calculating ranks
$html = $this->renderView('classroom/reportcard/annual_2024.html.twig', array(
"headerFontSize" => $headerFontSize,
"bodyFontSize" => $bodyFontSize,
"lineHeight" => $lineHeight,
"copyright" => $copyright,
"reverse" => $reverse,
'mainTeacher'=>$mainTeacher,
'year' => $year,
'data' => $this->annualMark,
'room' => $room,
'students' => $studentEnrolled,
'abscences' => $absencesArray,
'ranks' => $this->ranking($room, $year),
'means' => $this->annualAvgArray,
'genMean' => $this->sumAvg / sizeof($studentEnrolled),
'fileExists'=> $this->fileExists($room, $year)
));
return new Response(
$this->snappy->getOutputFromHtml($html, ['page-size' => 'A4']),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $room->getName() . '_' . $year->getId() . '.pdf"',
]
);
}
public function getStudentQuaterMark(array $std, Course $crs): ?array
{
foreach ($this->quaterData as $data) {
if ($std['id'] == $data['std'] && $crs->getId() == $data['crs']) {
return [
'seq1' => $data['value1'],
'weight1' => $data['weight1'],
'seq2' => $data['value2'],
'weight2' => $data['weight2'],
'coef' => $data['coef'],
];
}
}
return null;
}
public function getStudentAnnualMark(array $std, Course $crs): ?array
{
foreach ($this->annualMark as $data) {
if ($std['id'] == $data['std'] && $crs->getId() == $data['crs']) {
return [
'value' => $data['value'],
'weight' => $data['weight'],
'coef' => $data['coef'],
];
}
}
return null;
}
public function getViewQuaterMark2024(ClassRoom $room, Quater $quater): void
{
$connection = $this->em->getConnection();
$sequences = $this->seqRepo->findBy(['quater' => $quater]);
foreach ($sequences as $seq) {
$this->getViewSeqMark2024($room, $seq);
}
$connection->executeQuery(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER" . $room->getId() . "_" . $quater->getId() . " AS
SELECT DISTINCT seq1.std as std, seq1.crs as crs, seq1.coef as coef,
seq1.value as value1, seq1.weight as weight1,
seq2.value as value2, seq2.weight as weight2,
(COALESCE(seq1.value*seq1.weight,0) + COALESCE(seq2.value*seq2.weight,0))/(seq1.weight+seq2.weight) as value,
greatest(seq1.weight, seq2.weight) as weight,
(seq1.mini + seq2.mini)/2 as mini, (seq1.maxi + seq2.maxi)/2 as maxi
FROM V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[0]->getId() . " seq1
LEFT JOIN V_STUDENT_MARK_SEQ" . $room->getId() . "_" . $sequences[1]->getId() . " seq2
ON (seq1.std = seq2.std AND seq1.crs = seq2.crs)
ORDER BY std"
);
$this->quaterData = $connection->fetchAllAssociative(
"SELECT * FROM V_STUDENT_MARK_QUATER" . $room->getId() . "_" . $quater->getId()
);
}
public function getViewYearMark2024(ClassRoom $room, SchoolYear $year): void
{
$connection = $this->em->getConnection();
$quaters = $year->getQuaters();
foreach ($quaters as $quater) {
$this->getViewQuaterMark2024($room, $quater);
}
$connection->executeQuery("
CREATE OR REPLACE VIEW V_STUDENT_MARK_YEAR" . $room->getId() . "_" . $year->getId() . " AS
SELECT DISTINCT
`user`.full_name, course.wording,
qt1.std AS std, qt1.crs AS crs, qt1.coef AS coef,
qt1.value AS value1, qt1.weight AS weight1,
qt2.value AS value2, qt2.weight AS weight2,
qt3.value AS value3, qt3.weight AS weight3,
(
COALESCE(qt1.value,0)*COALESCE(qt1.weight,0) +
COALESCE(qt2.value,0)*COALESCE(qt2.weight,0) +
COALESCE(qt3.value,0)*COALESCE(qt3.weight,0)
) / NULLIF(COALESCE(qt1.weight,0)+COALESCE(qt2.weight,0)+COALESCE(qt3.weight,0), 0) AS `value`,
GREATEST(COALESCE(qt1.weight,0), COALESCE(qt2.weight,0), COALESCE(qt3.weight,0)) AS weight,
(COALESCE(qt1.mini,0)+COALESCE(qt2.mini,0)+COALESCE(qt3.mini,0))/3 AS mini,
(COALESCE(qt1.maxi,0)+COALESCE(qt2.maxi,0)+COALESCE(qt3.maxi,0))/3 AS maxi,
RANK() OVER (
PARTITION BY course.id
ORDER BY (
COALESCE(qt1.value,0)*COALESCE(qt1.weight,0) +
COALESCE(qt2.value,0)*COALESCE(qt2.weight,0) +
COALESCE(qt3.value,0)*COALESCE(qt3.weight,0)
) / NULLIF(COALESCE(qt1.weight,0)+COALESCE(qt2.weight,0)+COALESCE(qt3.weight,0), 0) DESC
) AS `rank`
FROM course
JOIN attribution att ON att.course_id = course.id AND att.year_id = " . $year->getId() . "
JOIN `user` ON `user`.id = att.teacher_id
JOIN V_STUDENT_MARK_QUATER" . $room->getId() . "_" . $quaters[0]->getId() . " qt1 ON course.id = qt1.crs
LEFT JOIN V_STUDENT_MARK_QUATER" . $room->getId() . "_" . $quaters[1]->getId() . " qt2 ON qt1.std = qt2.std AND qt1.crs = qt2.crs
LEFT JOIN V_STUDENT_MARK_QUATER" . $room->getId() . "_" . $quaters[2]->getId() . " qt3 ON qt1.std = qt3.std AND qt1.crs = qt3.crs
ORDER BY qt1.std
");
$this->annualMark = $connection->fetchAllAssociative(
"SELECT * FROM V_STUDENT_MARK_YEAR" . $room->getId() . "_" . $year->getId()
);
}
public function buildAbsYearView(ClassRoom $room, SchoolYear $year): void
{
$connection = $this->em->getConnection();
$quaters = $year->getQuaters();
foreach ($quaters as $quater) {
$this->buildAbsQuaterView($room, $quater);
}
$rid = $room->getId();
$yid = $year->getId();
$q0 = $quaters[0]->getId();
$q1 = $quaters[1]->getId();
$q2 = $quaters[2]->getId();
$connection->executeQuery("
CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_YEAR{$rid}_{$yid} AS
SELECT DISTINCT qt1.std AS std,
COALESCE(qt1.total_hours,0)+COALESCE(qt2.total_hours,0)+COALESCE(qt3.total_hours,0) AS total_hours
FROM V_STUDENT_ABSCENCE_QUATER{$rid}_{$q0} qt1
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q1} qt2 ON qt1.std = qt2.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q2} qt3 ON qt1.std = qt3.std
UNION
SELECT DISTINCT qt2.std AS std,
COALESCE(qt1.total_hours,0)+COALESCE(qt2.total_hours,0)+COALESCE(qt3.total_hours,0) AS total_hours
FROM V_STUDENT_ABSCENCE_QUATER{$rid}_{$q1} qt2
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q0} qt1 ON qt1.std = qt2.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q2} qt3 ON qt2.std = qt3.std
UNION
SELECT DISTINCT qt3.std AS std,
COALESCE(qt1.total_hours,0)+COALESCE(qt2.total_hours,0)+COALESCE(qt3.total_hours,0) AS total_hours
FROM V_STUDENT_ABSCENCE_QUATER{$rid}_{$q2} qt3
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q0} qt1 ON qt1.std = qt3.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER{$rid}_{$q1} qt2 ON qt2.std = qt3.std
ORDER BY std
");
}
public function getAbsYearFromView(ClassRoom $room, SchoolYear $year): array
{
$this->buildAbsYearView($room, $year);
return $this->em->getConnection()->fetchAllAssociative(
"SELECT * FROM V_STUDENT_ABSCENCE_YEAR" . $room->getId() . "_" . $year->getId()
);
}
/**
* @Route("/{id}/recapitulatiftrim", name="admin_classrooms_recapitulatif_trim", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function recapTrimAction(ClassRoom $room, Request $request)
{
$checkedValues = $request->request->get('selected_courses');
set_time_limit(600);
$year = $this->schoolYearService->sessionYearById();
$quater = $this->qtRepo->findOneBy(['activated' => true]);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYear($room, $year->getId());
$this->getViewQuaterMark2024($room, $quater);
$result = [];
foreach ($studentEnrolled as $std) {
$result[$std['id']] = [];
foreach ($room->getModules() as $module) {
foreach ($module->getCourses() as $crs) {
if (in_array($crs->getId(), $checkedValues)) {
$result[$std['id']][$crs->getId()] = $this->getStudentQuaterMark($std, $crs);
}
}
}
}
$mainTeacher = $this->mainTeacherRepo->findOneBy(['classRoom' => $room, 'schoolYear' => $year])->getTeacher();
$html = $this->renderView('classroom/recapitulatiftrimWithMoy.html.twig', [
'year' => $year,
'datas' => $result,
'room' => $room,
'quater' => $quater,
'checkedValues' => $checkedValues,
'students' => $studentEnrolled,
'mainTeacher' => $mainTeacher,
]);
return new Response(
$this->pdf->getOutputFromHtml($html, [
'orientation' => 'Landscape',
'page-size' => 'A4',
'margin-top' => '10mm',
'margin-bottom'=> '10mm',
'margin-left' => '10mm',
'margin-right' => '10mm',
]),
200,
[
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="recap_trim' . $quater->getId() . '_' . $room->getName() . '.pdf"',
]
);
}
/**
* @Route("/{id}/recapitulatifannexcel", name="admin_classrooms_recapitulatif_ann_excel", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function recapAnnExcelAction(ClassRoom $room, Request $request)
{
$checkedValues = $request->request->get('selected_courses');
$year = $this->schoolYearService->sessionYearById();
$this->getViewYearMark2024($room, $year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYear($room, $year->getId());
$result = [];
$courses = [];
foreach ($studentEnrolled as $std) {
$result[$std['id']]['student'] = $std['lastname'] . ' ' . $std['firstname'];
$result[$std['id']]['gender'] = $std['gender'];
$result[$std['id']]['birthday'] = $std['birthday'];
foreach ($room->getModules() as $module) {
foreach ($module->getCourses() as $crs) {
if (in_array($crs->getId(), $checkedValues)) {
$courses[$crs->getId()] = $crs->getWording();
$result[$std['id']][$crs->getId()] = $this->getStudentAnnualMark($std, $crs);
}
}
}
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Élève');
$sheet->setCellValue('B1', 'Sexe');
$sheet->setCellValue('C1', 'Age');
$colIndex = 4;
foreach ($courses as $courseId => $courseName) {
$sheet->setCellValueByColumnAndRow($colIndex, 1, $courseName);
$colIndex++;
}
$sheet->setCellValueByColumnAndRow($colIndex, 1, 'MOYENNE');
$rowIndex = 2;
foreach ($result as $studentData) {
$sheet->setCellValueByColumnAndRow(1, $rowIndex, $studentData['student']);
$genderLabel = ((int) $studentData['gender'] === 1) ? 'Femme' : 'Homme';
$sheet->setCellValueByColumnAndRow(2, $rowIndex, $genderLabel);
$sheet->setCellValueByColumnAndRow(3, $rowIndex, $studentData['birthday']);
$colIndex = 4;
$totalMarkCoef = 0;
$totalCoef = 0;
foreach ($courses as $courseId => $courseName) {
$data = $studentData[$courseId] ?? ['value' => 0, 'weight' => 0, 'coef' => 0];
$mark = $data['value'] * $data['weight'];
$coef = $data['coef'];
$totalMarkCoef += $mark * $coef;
$totalCoef += $coef;
$sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $mark);
$colIndex++;
}
$moy = $totalCoef > 0 ? $totalMarkCoef / $totalCoef : '#';
$sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $moy);
$rowIndex++;
}
$writer = new Xlsx($spreadsheet);
$filename = 'recapitulatif_' . $room->getName() . '_' . date('Ymd_His') . '.xlsx';
$response = new StreamedResponse(function () use ($writer) {
$writer->save('php://output');
});
$response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', 'attachment; filename="' . $filename . '"');
$response->headers->set('Cache-Control', 'max-age=0');
return $response;
}
/**
* @Route("/classroom/insolvents", name="admin_classroom_insolvents")
*/
public function listInsolventStudents(): Response
{
$year = $this->schoolYearService->sessionYearById();
$subscriptions = $this->subRepo->findBy(['schoolYear' => $year], ['classRoom' => 'ASC']);
$insolventSub = [];
foreach ($subscriptions as $sub) {
if ($year->paymentThresholdAmount($sub->getClassRoom()) > $sub->getStudent()->getPaymentsSum($year)) {
$insolventSub[] = $sub;
}
}
$html = $this->render('school_year/templating/insolvent_students_list.html.twig', [
'students' => $insolventSub,
'year' => $year,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="insolvent_student_' . $year->getCode() . '.pdf"',
]);
}
/**
* BUG FIX : $id non défini → utiliser $room directement
*
* @Route("/classroom/{id}", name="class_room_stats")
*/
public function showClassRoomStats(ClassRoomRepository $classRoomRepository, ClassRoom $room): Response
{
$successfulCount = $classRoomRepository->countSuccessfulStudentsForClass($room);
$unsuccessfulCount = $classRoomRepository->countUnsuccessfulStudentsForClass($room);
$mentionStatistics = $classRoomRepository->getMentionStatisticsForClass($room);
return $this->render('class_room/stats.html.twig', [
'classRoom' => $room,
'successfulCount' => $successfulCount,
'unsuccessfulCount' => $unsuccessfulCount,
'mentionStatistics' => $mentionStatistics,
]);
}
/**
* @Route("/classroom/{id}/insolvent", name="admin_classroom_insolvent")
*/
public function listInsolventStudentsByRoom(ClassRoom $room): Response
{
$year = $this->schoolYearService->sessionYearById();
$subscriptions = $this->subRepo->findBy(['schoolYear' => $year, 'classRoom' => $room]);
$students = [];
$dueAmounts = [];
foreach ($subscriptions as $sub) {
if ($year->paymentThresholdAmount($room) > $sub->getStudent()->getPaymentsSum($year)) {
$students[] = $sub->getStudent();
$dueAmounts[$sub->getStudent()->getId()] = $year->paymentThresholdAmount($room) - $sub->getStudent()->getPaymentsSum($year);
}
}
$html = $this->render('classroom/templating/insolvent_student_list.html.twig', [
'room' => $room,
'students' => $students,
'year' => $year,
'amounts' => $dueAmounts,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="insolvent_student_' . $room->getName() . '.pdf"',
]);
}
/**
* @Route("/insolventspercentage", name="admin_classroom_insolvents_percentage")
*/
public function insolventStudentsRate(): Response
{
$year = $this->schoolYearService->sessionYearById();
$paymentPlan = $year->getPaymentPlan();
$rooms = $this->repo->findAll();
$rates = [];
foreach ($rooms as $room) {
$subscriptions = $this->subRepo->findBy(['schoolYear' => $year, 'classRoom' => $room]);
$installments = $this->instRepo->findBy(['classRoom' => $room, 'paymentPlan' => $paymentPlan]);
$sum = 0;
foreach ($installments as $installment) {
$sum += $installment->getAmount();
}
$ratesByRoom = [];
foreach ($subscriptions as $sub) {
$ratesByRoom[] = 100 * $sub->getStudent()->getPaymentsSum($year) / $sum;
}
$rates[$room->getName()] = count($ratesByRoom) > 0 ? array_sum($ratesByRoom) / count($ratesByRoom) : 0;
}
$html = $this->render('school_year/templating/recovery_rates_by_room.html.twig', [
'rates' => $rates,
'year' => $year,
]);
return new Response($this->pdf->getOutputFromHtml($html), 200, [
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="insolvent_student_' . $year->getCode() . '.pdf"',
]);
}
}

src/Controller/ClassRoomController.php line 48