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\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;
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->annualAvgArray = [];
$this->sumAvg = 0;
$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 ClassRoomme entities.
*
* @Route("/", name="admin_classrooms")
* @Method("GET")
* @Template()
*/
public function indexAction()
{
$classrooms = $this->repo->findAll();
$year = $this->schoolYearService->sessionYearById();
$seq = $this->seqRepo->findOneBy(array("activated" => true));
$mainTeachers = $this->mainTeacherRepo->findBy(array("schoolYear" => $year));
$mainTeachersMap = array();
foreach($mainTeachers as $mt){
$mainTeachersMap[$mt->getClassRoom()->getId()] = $mt->getTeacher();
}
return $this->render('classroom/index.html.twig', array(
'mainTeachers' => $mainTeachersMap,
'classrooms' => $classrooms,
'year' => $year,
'seq' => $seq->getId(),
));
}
// Existance de fichiers image des eleves
private function fileExists(ClassRoom $classroom, SchoolYear $year): array
{
$imageExists = [];
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
// Répertoire absolu vers les images
$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) {
$filename = $baseDir . $matricule . '.' . $ext;
if (file_exists($filename)) {
$found = true;
break;
}
}
$imageExists[$std->getId()] = $found;
}
return $imageExists;
}
/**
* Finds and displays a ClassRoomme entity.
*
* @Route("/{id}/show", name="admin_classrooms_show", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function showAction(ClassRoom $classroom, StudentRepository $stdRepo)
{
// Année scolaire et seuquence en cours
$year = $this->schoolYearService->sessionYearById();
$seq = $this->seqRepo->findOneBy(array("activated" => true));
// Elèves inscrits
// Attributions de cours durant l'annee
$attributions = $this->attRepo->findByYearAndByRoom($year,$classroom);
$attributionsMapCourses = null;
foreach($attributions as $att){
$attributionsMapCourses[$att->getCourse()->getId()] = $att;
}
// Liste des resulats au examens officiels
$officialExamResults = $this->subRepo->countByMention($year, $classroom);
$mentionCategories = [];
$mentionCountCategories = [];
foreach ($officialExamResults as $exam) {
switch ($exam["officialExamResult"]) {
case "0":
$mentionCategories[] = "ECHEC";
break;
case "1p":
$mentionCategories[] = "PASSABLE";
break;
case "1a":
$mentionCategories[] = "ASSEZ-BIEN";
break;
case "1b":
$mentionCategories[] = "BIEN";
break;
case "1t":
$mentionCategories[] = "TRES-BIEN";
break;
case "1e":
$mentionCategories[] = "EXCELLENT";
break;
case "A":
$mentionCategories[] = "5 POINTS";
break;
case "B":
$mentionCategories[] = "4 POINTS";
break;
case "C":
$mentionCategories[] = "3 POINTS";
break;
case "D":
$mentionCategories[] = "2 POINTS";
break;
case "E":
$mentionCategories[] = "1 POINT";
break;
}
$mentionCountCategories[] = $exam["count"];
}
// Extraction de donnees pour les graphes
$seqs = $this->seqRepo->findSequenceThisYear($year);
$evalSeqs = [];
foreach ($seqs as $seq) {
$evalSeqs[$seq->getId()] = $this->evalRepo->findBy(array("classRoom" => $classroom, "sequence" => $seq));
}
$courses = [];
$averageSeqs = [];
// Traitements de donnees pour les graphes de notes sequentielles
foreach ($evalSeqs[$seq->getId()] as $eval) {
$courses[] = $eval->getCourse()->getWording();
}
foreach ($seqs as $seq) {
$average = [];
foreach ($evalSeqs[$seq->getId()] as $eval) {
$average[] = $eval->getMoyenne();
}
$averageSeqs[$seq->getId()] = $average;
}
// Recherche de l'enseignant titulaire
$mainTeacher = null;
foreach($classroom->getMainTeachers() as $mainT){
if($mainT->getSchoolYear()->getId() == $year->getId()){
$mainTeacher = $mainT->getTeacher();
}
}
$results['mainteacher'] = $mainTeacher;
$results['classroom'] = $classroom;
$results['attributions'] = $attributionsMapCourses;
$results['modules'] = $classroom->getModules();
$results['studentEnrolled'] = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
;
$results['cours'] = json_encode($courses);
$results['fileExists'] = $this->fileExists($classroom, $year);
$results['sessions'] = json_encode($seqs);
$results['mentionCategories'] = json_encode($mentionCategories);
$results['mentionCountCategories'] = json_encode($mentionCountCategories);
foreach ($seqs as $seq) {
$results[strtolower($seq->getWording())] = json_encode($averageSeqs[$seq->getId()]);
}
return $this->render('classroom/show.html.twig', $results);
}
/**
* Finds and displays a ClassRoomme entity.
*
* @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', array());
}
/**
* Finds and displays a ClassRoom entity.
*
* @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);
$statement = $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
"
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$statement = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ2 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 = 2
ORDER BY matricule,eval; "
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$statement = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ3 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 = 3
ORDER BY matricule,eval; "
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$statement = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ4 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 = 4
ORDER BY matricule,eval; "
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$statement = $connection->prepare(
"CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ5 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 = 5
ORDER BY matricule,eval; "
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$statement = $connection->prepare(
" CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ6 AS
SELECT DISTINCT eval.id as eval,crs.id as crs, 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 class_room room ON eval.class_room_id = room.id
JOIN course crs ON eval.course_id = crs.id
WHERE room.id = ? AND eval.sequence_id = 6
ORDER BY std.matricule"
);
$statement->bindValue(1, $classroom->getId());
$statement->execute();
$dataYear = $this->em->getConnection()->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
")->fetchAll();
$this->snappy->setTimeout(600);
$html = $this->renderView('classroom/reportcard/annual.html.twig', array(
'year' => $year,
'data' => $dataYear,
'room' => $classroom,
'year' => $year,
'students' => $studentEnrolled,
));
return new Response(
$this->snappy->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
)
);
}
public function viewSeq(int $i){
$year = $this->schoolYearService->sessionYearById();
$connection = $this->em->getConnection();
$statement = $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; "
);
$statement->bindValue(1, $year->getId());
$statement->bindValue(2, $classroom->getId());
$statement->bindValue(3, $seq->getId());
$statement->execute();
}
/**
* Finds and displays a ClassRoom entity.
*
* @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) {
/*******************************************************************************************************************/
/***************CREATION DE la VIEW DES NOTES SEQUENTIELLES, TRIMESTRIELLES ET ANNUELLES DE LA CLASSE**************/
/*******************************************************************************************************************/
// CAS DES NOTES SEQUENTIELLES
// $this->viewSeq($i, $classroom, $seq);
$this->getViewSeqData( $classroom, $seq);
}
// CAS DES NOTES TRIMESTRIELLES
$statement = $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"
);
$statement->execute();
$statement = $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"
);
$statement->execute();
$statement = $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"
);
$statement->execute();
// CAS DES NOTES ANNUELLES
$statement = $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
JOIN
LEFT V_STUDENT_MARK_QUATER_3 quat3 ON quat1.std = quat3.std AND quat1.crs = quat3.crs
ORDER BY quat1.std, quat1.modu
"
);
$statement->execute();
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA ")->fetchAll();
// For calculating ranks
$statement = $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"
);
$statement->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$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', array(
'year' => $year,
'data' => $dataYear,
'room' => $classroom,
'students' => $studentEnrolled,
'ranks' => $rankArray,
'means' => $this->annualAvgArray,
'genMean' => $this->sumAvg / sizeof($this->annualAvgArray),
));
//return new Response($html);
return new Response(
$this->snappy->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
)
);
}
/**
* Finds and displays a Evaluation entity.
*
* @Route("/{room}/{seq}/pdf", name="admin_classrooms_recapitulatif", requirements={"room"="\d+","seq"="\d+"})
* @Method("GET")
* @Template()
* @return Response
*/
public function recapitulatifAction(ClassRoom $room, Sequence $seq)
{
// Année scolaire en cours
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$html = $this->renderView('classroom/templating/recapitulatifseqvierge.html.twig', array(
'room' => $room,
'seq' => $seq,
'students' => $studentEnrolled,
'year' => $year,
));
$options = [
'orientation' => 'Landscape', // Changer ici entre 'Portrait' ou 'Landscape'
'page-size' => 'A4', // Format de page
];
return new Response(
$this->pdf->getOutputFromHtml($html, $options),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fiche_recep_' . $room->getName() . '.pdf"'
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @Route("/{id}/recapitulatifseq", name="admin_classrooms_recapitulatif_seq", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function recapSeqAction(ClassRoom $room, Request $request)
{
// set_time_limit(600);
$checkedValues = $request->request->get('selected_courses');
$em = $this->getDoctrine()->getManager();
$year = $this->schoolYearService->sessionYearById();
$seq = $this->seqRepo->findOneBy(array("activated" => true));
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$datas = $this->markRepo->findMarksBySequenceAndClassOrderByStd($seq, $room);
$html = $this->renderView('classroom/recapitulatifseq.html.twig', array(
'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 succesfully created');
return $this->redirectToRoute('admin_classrooms');
}
return $this->render(
'classroom/new.html.twig',
['form' => $form->createView()]
);
}
/**
* Rapport séquentiel d'enregistrement des notes.
*
* @Route("/{id}/evalrepport", name="admin_current_fulfilled_eval_show", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function currentFullfilledEvalAction(ClassRoom $classroom)
{
$em = $this->getDoctrine()->getManager();
$year = ($this->session->has('session_school_year') && ($this->session->get('session_school_year')!= null)) ? $this->session->get('session_school_year') : $this->scRepo->findOneBy(array("activated" => true));
// Liste des séquences de l'année scolaire en cours
$sequences = $em->getRepository('AppBundle:Sequence')->findSequencesBySchoolYear($year);
// Liste des matières
$courses = $em->getRepository('AppBundle:Course')->findProgrammedCoursesInClass($classroom);
// Elèves inscrits
foreach ($sequences as $seq) {
// Lecture de chaque tableau de chaque ligne
foreach ($courses as $course) {
// Liste des évaluations
$evaluation = $em->getRepository('AppBundle:Evaluation')->findOneBy(array(
"classRoom" => $classroom,
"sequence" => $seq, "course" => $course
));
if ($evaluation != null) {
$evaluations[$seq->getId()][$course->getId()] = 1;
} else {
$evaluations[$seq->getId()][$course->getId()] = 0;
}
}
}
return $this->render('classroom/eval_repport.html.twig', array(
'evaluations' => $evaluations,
'courses' => $courses,
'room' => $classroom,
'sequences' => $sequences,
));
}
/**
* Displays a form to edit an existing ClassRoomme entity.
*
* @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 succesfully updated');
return $this->redirectToRoute('admin_classrooms');
}
return $this->render('classroom/edit.html.twig', [
'room' => $room,
'form' => $form->createView()
]);
}
/**
* Deletes a ClassRoom entity.
*
* @Route("/{id}/delete", name="admin_classrooms_delete", requirements={"id"="\d+"}, methods={"DELETE"})
*/
public function delete(ClassRoom $q, Request $request): Response
{
// if($this->isCsrfTokenValid('classrooms_deletion'.$schoolyear->getId(), $request->request->get('crsf_token') )){
$this->em->remove($q);
$this->em->flush();
$this->addFlash('info', 'ClassRoom succesfully deleted');
// }
return $this->redirectToRoute('admin_classrooms');
}
/**
* Finds and displays a ClassRoom entity.
*
* @Route("/{id}/fichesimple", name="admin_classrooms_fichesimple", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function fichesiplmeAction(ClassRoom $classroom)
{
// Année scolaire en cours
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/fiche_repport_notes.html.twig', array(
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fiche_pv_' . $classroom->getName() . '.pdf"'
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @Route("/{id}/disciplinary_record", name="admin_classrooms_disciplinary_record", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function ficheDisciplineAction(ClassRoom $classroom)
{
// Année scolaire en cours
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/fiche_repport_disc.html.twig', array(
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="fich_disc_' . $classroom->getName() . '.pdf"'
)
);
}
/**
* LISTE DES ELEVES DE LA CLASSE DANS UNE FICHE DE PRESENTATION.
*
* @Route("/{id}/presentation", name="admin_classrooms_presentation", requirements={"id"="\d+"})
* @Method("GET")
* @Template()
*/
public function presentationAction(ClassRoom $classroom)
{
// Année scolaire en cours
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$html = $this->renderView('classroom/templating/student_list.html.twig', array(
'year' => $year,
'room' => $classroom,
'students' => $studentEnrolled,
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="std_list_' . $classroom->getName() . '.pdf"'
)
);
}
/**
* MOYENNE GENERALE DE LA CLASSE A UNE SEQUENCE
* @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_seq1 = $this->em->createQuery($dql)
->setParameter(1, $room->getId())
->setParameter(2, $seq->getId())
->getSingleScalarResult();
return round($avg_seq1, 2);
}
/**
* MOYENNE GENERALE DE LA CLASSE A UN TRIMESTRE
* @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);
}
/**
* Finds and displays a Evaluation entity.
*
* @Route("/{room}/pdf", name="admin_classrooms_blanc_ann", requirements={"room"="\d+"})
* @Method("GET")
* @Template()
* @return Response
*/
public function annualSummaryAction(ClassRoom $room)
{
$year = $this->schoolYearService->sessionYearById();
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$html = $this->renderView('classroom/templating/blankAnnualForm.html.twig', array(
'room' => $room,
'students' => $studentEnrolled,
'year' => $year,
));
return new Response(
$this->pdf->getOutputFromHtml($html, array(
'default-header' => false
)),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="recap_empty_' . $room->getName() . '.pdf"',
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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;
$em = $this->getDoctrine()->getManager();
$year =$this->schoolYearService->sessionYearById();
$sequence = $this->seqRepo->findOneBy(array("activated" => true));
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
// Existance des photos d'eleves
$evaluations = $this->evalRepo->findSequantialExamsOfRoom($classroom->getId(), $sequence->getId());
foreach ($evaluations as $ev) {
$totalNtCoef += $ev->getMoyenne() * $ev->getCourse()->getCoefficient();
$totalCoef += $ev->getCourse()->getCoefficient();
}
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
$this->getViewSeqData($classroom, $sequence);
$connection = $this->em->getConnection();
$datas = $connection->executeQuery("SELECT * FROM V_STUDENT_MARK_SEQ0 ")->fetchAll();
// For calculating ranks
$statement = $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"
);
$statement->execute();
$seqAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$seqAvgArray = [];
$sumAvg = 0;
$rank = 0;
$rankArray = [];
foreach ($seqAvg as $avg) {
$seqAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
}
// CAS DES ABSCENCES SEQUENTIELLES
$statement = $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 "
);
$statement->execute();
// Traitement des abscences
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_SEQ ")->fetchAll();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$html = $this->renderView('classroom/reportcard/sequential.html.twig', array(
'year' => $year,
'datas' => $datas,
'students' => $studentEnrolled,
'sequence' => $sequence,
'quater' => $sequence->getQuater(),
'room' => $classroom,
'students' => $studentEnrolled,
'means' => $seqAvgArray,
'abscences' => $absencesArray,
'genMean' => $sumAvg / sizeof($seqAvgArray),
'ranks' => $rankArray,
'fileExists'=> $this->fileExists($classroom, $year)
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="bull_seq_' . $classroom->getName() . '.pdf"'
)
);
}
public function buildAbsViewSeq(ClassRoom $room,Sequence $seq){
$connection = $this->em->getConnection();
$statement = $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; "
);
$statement->bindValue(1, $seq->getId());
$statement->bindValue(2, $room->getId());
$statement->execute();
}
public function getAbsSeqFromView(ClassRoom $room,Sequence $seq){
$connection = $this->em->getConnection();
return $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_SEQ".$room->getId()."_".$seq->getId()." ")->fetchAllAssociative();
}
// Vue des abscences d'eleves d'une classe durant un trimestre
public function buildAbsQuaterView(ClassRoom $room,Quater $quater){
$sequences = $this->seqRepo->findBy(array("quater" => $quater));
foreach ($sequences as $seq) {
$this->buildAbsViewSeq($room, $seq);
}
$year = $this->schoolYearService->sessionYearById();
// CAS DES ABSCENCES TRIMESTRIELLES
$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;
";
$connection = $this->em->getConnection();
$connection->executeQuery($query);
}
public function getAbsQuaterFromView(ClassRoom $room, Quater $quater){
$this->buildAbsQuaterView($room, $quater);
$query = "select * from V_STUDENT_ABSCENCE_QUATER".$room->getId()."_".$quater->getId()." ;";
$connection = $this->em->getConnection();
$absences = $connection->executeQuery( $query )->fetchAll();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['total_hours'];
}
return $absencesArray;
}
public function getViewSeqData(ClassRoom $room,Sequence $seq){
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
// CAS DES NOTES SEQUENTIELLES
$statement = $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; "
);
$statement->bindValue(1, $year->getId());
$statement->bindValue(2, $room->getId());
$statement->bindValue(3, $seq->getId());
$statement->execute();
$statement = $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; "
);
$statement->bindValue(1, $seq->getId());
$statement->bindValue(2, $room->getId());
$statement->execute();
}
public function getViewSeqMark2024(ClassRoom $room,Sequence $seq){
$connection = $this->em->getConnection();
$year = $this->schoolYearService->sessionYearById();
// CAS DES NOTES SEQUENTIELLES
$statement = $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; "
);
$statement->bindValue(1, $room->getId());
$statement->bindValue(2, $seq->getId());
$statement->execute();
}
/**
* Finds and displays a ClassRoom entity.
*
* @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(array("activated" => true));
$sequences = $this->seqRepo->findBy(array("quater" => $quater));
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
// Existance des photos d'eleves
$this->fileExists($classroom, $year);
/*******************************************************************************************************************/
/***************CREATION DE la VIEW DES NOTES SEQUENTIELLES, TRIMESTRIELLES DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
/*******************************************************************************************************************/
foreach ($sequences as $seq) {
// CAS DES NOTES et ABSCENCES SEQUENTIELLES
$this->getViewSeqData($room, $seq);
}
// CAS DES NOTES TRIMESTRIELLES
$statement = $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"
);
$statement->execute();
// CAS DES ABSCENCES TRIMESTRIELLES
$statement = $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_SEQ1 seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ2 seq2 ON (seq1.std = seq2.std )
ORDER BY std "
);
$statement->execute();
$dataQuater = $connection->executeQuery("SELECT * FROM V_STUDENT_MARK_QUATER marks ")->fetchAll();
// For calculating ranks
$statement = $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"
);
$statement->execute();
$quaterAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$quaterAvgArray = [];
$sumAvg = 0;
$rank = 0;
$rankArray = [];
foreach ($quaterAvg as $avg) {
$quaterAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
}
// Traitement des abscences
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_QUATER ")->fetchAll();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$this->pdf->setTimeout(600);
$html = $this->renderView('classroom/reportcard/quaterly_2024.html.twig', array(
'year' => $year,
'data' => $dataQuater,
'ranks' => $rankArray,
'means' => $quaterAvgArray,
'abscences' => $absencesArray,
'genMean' => $sumAvg / sizeof($quaterAvgArray),
'room' => $room,
'quater' => $quater,
'sequences' => $sequences,
'students' => $studentEnrolled,
'fileExists'=> $fileExists
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="' . $room->getName() . '.pdf"'
)
);
// return new Response($html);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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');
}
$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();
$quater = $this->qtRepo->findOneBy(array("activated" => true));
$students = $this->stdRepo->findEnrolledStudentsThisYearInClass($room, $year);
$mainTeacher = $this->mainTeacherRepo->findOneBy(array("classRoom" => $room, "schoolYear" => $year))-> getTeacher();
// Retrieve of marks
$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; ";
$params = [
'quater_id' => $quater->getId(),
'room_id' => $room->getId(), // Remplace :room_id
];
$result = $connection->executeQuery($query, $params);
$dataMarks = $result->fetchAllAssociative();
$sequences = $this->seqRepo->findBy(array("quater" => $quater));
// Calculation of rank and general average
foreach ($sequences as $seq) {
$this->getViewSeqMark2024($room,$seq);
}
// CAS DES NOTES TRIMESTRIELLES
$query =
" 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 ";
$connection->executeQuery($query);
$statement = $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"
);
$statement->execute();
$quaterAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$quaterAvgArray = [];
$sumAvg = 0;
$rank = 0;
$minAgv = 20;
$maxAvg = 0;
$rankArray = [];
foreach ($quaterAvg as $avg) {
$quaterAvgArray[$avg['std']] = $avg['moyenne'];
$rankArray[$avg['std']] = ++$rank;
$sumAvg += $avg['moyenne'];
if($minAgv > $avg['moyenne']){
$minAgv = $avg['moyenne'];
}
if($maxAvg < $avg['moyenne']){
$maxAvg = $avg['moyenne'];
}
}
$html = $this->renderView('classroom/reportcard/quaterly_2024.html.twig', array(
'genMean' => $sumAvg / sizeof($quaterAvgArray),
'ranks' => $rankArray,
'year' => $year,
'minAvg' => $minAgv,
'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), // Existance des images d'eleves
"headerFontSize" => 0.75*$headerFontSize,
"lineHeight" => 1.5*$lineHeight,
"copyright" => $copyright,
"reverse" => $reverse
));
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="bull_' . $quater->getId() . '.pdf"'
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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();
$quater = $this->qtRepo->findOneBy(array("activated" => true));
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
/*******************************************************************************************************************/
/***************CREATION DE la VIEW DES NOTES SEQUENTIELLES, TRIMESTRIELLES DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
/*******************************************************************************************************************/
foreach ($sequences as $seq) {
// CAS DES NOTES et ABSCENCES SEQUENTIELLES
$this->getViewSeqData($classroom, $seq);
$i++;
}
// CAS DES NOTES TRIMESTRIELLES1
$statement = $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".$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 , modu"
);
$statement->execute();
// CAS DES NOTES TRIMESTRIELLES2
$statement = $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".$room->getId()."_".$sequences[2]->getId()." seq1
JOIN V_STUDENT_MARK_SEQ".$room->getId()."_".$sequences[3]->getId()." seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs )
ORDER BY std , modu"
);
$statement->execute();
// CAS DES NOTES TRIMESTRIELLES3
$statement = $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".$room->getId()."_".$sequences[4]->getId()." seq1
JOIN V_STUDENT_MARK_SEQ".$room->getId()."_".$sequences[5]->getId()." seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs )
ORDER BY std , modu"
);
$statement->execute();
$statement = $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
"
);
$statement->execute();
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA ")->fetchAll();
// For calculating ranks
$statement = $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"
);
$statement->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$rank = 0;
$rankArray = [];
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['idStd']] = $avg['moyenne'];
$rankArray[$avg['idStd']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
$html = $this->renderView('classroom/avglist.html.twig', array(
'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,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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();
$quater = $this->qtRepo->findOneBy(array("activated" => true));
$sequences = $this->seqRepo->findSequenceThisYear($year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom, $year);
// Existance des photos d'eleves
$this->fileExists($classroom, $year);
/*******************************************************************************************************************/
/***************CREATION DE la VIEW DES NOTES SEQUENTIELLES, TRIMESTRIELLES DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
/*******************************************************************************************************************/
foreach ($sequences as $seq) {
// CAS DES NOTES et ABSCENCES SEQUENTIELLES
$this->getViewSeqData($classroom, $seq);
}
// CAS DES NOTES TRIMESTRIELLES1
$statement = $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_SEQ1 seq1
LEFT JOIN V_STUDENT_MARK_SEQ2 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs )
ORDER BY std , modu"
);
$statement->execute();
// CAS DES ABSCENCES TRIMESTRIELLES1
$statement = $connection->prepare(
" CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_1 AS
SELECT DISTINCT seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ1 seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ2 seq2 ON (seq1.std = seq2.std )
ORDER BY std "
);
$statement->execute();
// CAS DES NOTES TRIMESTRIELLES2
$statement = $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_SEQ3 seq1
LEFT JOIN V_STUDENT_MARK_SEQ4 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs )
ORDER BY std , modu"
);
$statement->execute();
// CAS DES ABSCENCES TRIMESTRIELLES2
$statement = $connection->prepare(
" CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_2 AS
SELECT DISTINCT seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ3 seq1
JOIN V_STUDENT_ABSCENCE_SEQ4 seq2 ON (seq1.std = seq2.std )
ORDER BY std "
);
$statement->execute();
// CAS DES NOTES TRIMESTRIELLES3
$statement = $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_SEQ5 seq1
JOIN V_STUDENT_MARK_SEQ6 seq2 ON (seq1.std = seq2.std AND seq1.crs = seq2.crs )
ORDER BY std , modu"
);
$statement->execute();
// CAS DES ABSCENCES TRIMESTRIELLES3
$statement = $connection->prepare(
" CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_3 AS
SELECT DISTINCT seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
FROM V_STUDENT_ABSCENCE_SEQ5 seq1
LEFT JOIN V_STUDENT_ABSCENCE_SEQ6 seq2 ON (seq1.std = seq2.std )
ORDER BY std "
);
$statement->execute();
set_time_limit(600);
$statement = $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
"
);
$statement->execute();
$dataYear = $connection->executeQuery("SELECT * FROM ANNUAL_DATA ")->fetchAll();
// For calculating ranks
$statement = $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"
);
$statement->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS ")->fetchAll();
$rank = 0;
$rankArray = [];
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['idStd']] = $avg['moyenne'];
$rankArray[$avg['idStd']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
// CAS DES ABSCENCES ANNUELLES
$statement = $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 "
);
$statement->execute();
// Traitement des abscences
$absences = $connection->executeQuery("SELECT * FROM V_STUDENT_ABSCENCE_ANNUAL ")->fetchAll();
$absencesArray = [];
foreach ($absences as $abs) {
$absencesArray[$abs['std']] = $abs['abscences'];
}
$html = $this->renderView('classroom/reportcard/annual.html.twig', array(
"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),
'fileExists'=> $this->imagesExist
));
return new Response(
$this->snappy->getOutputFromHtml($html, [
'page-size' => 'A4',
]),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $classroom->getName() . '.pdf"',
)
);
}
function ranking(ClassRoom $room, SchoolYear $year){
$connection = $this->em->getConnection();
$statement = $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"
);
$statement->execute();
$annualAvg = $connection->executeQuery("SELECT * FROM V_STUDENT_RANKS".$room->getId()."_".$year->getId()." WHERE totalCoef > 0 ;" )->fetchAll();
$this->sumAvg = 0;
$rank = 0;
foreach ($annualAvg as $avg) {
$this->annualAvgArray[$avg['std']] = $avg['moyenne'];
$this->annualRanks[$avg['std']] = ++$rank;
$this->sumAvg += $avg['moyenne'];
}
$array = array_filter($this->annualRanks, function ($v, $k) {
return $k !== '';
}, ARRAY_FILTER_USE_BOTH);
return $array;
}
/**
* Finds and displays a ClassRoom entity.
*
* @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');
$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(array("classRoom" => $room, "schoolYear" => $year))-> getTeacher();
// Performance annuelles
$this->getViewYearMark2024($room, $year);
// Existance des photos d'eleves
$this->fileExists($room, $year);
// Traitement des abscences
$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,
"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,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'attachment; filename="BUL_ANN_' . $room->getName() .'_'.$year->getId().'.pdf"',
)
);
}
public function getStudentQuaterMark(Array $std,Course $crs){
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){
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){
$em = $this->getDoctrine()->getManager();
$year = $this->schoolYearService->sessionYearById();
$connection = $this->em->getConnection();
$sequences = $this->seqRepo->findBy(array("quater" => $quater));
foreach ($sequences as $seq) {
$this->getViewSeqMark2024($room,$seq );
}
$query =
" 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 ";
$connection->executeQuery($query);
$query = "SELECT * FROM V_STUDENT_MARK_QUATER".$room->getId()."_".$quater->getId();
$this->quaterData = $connection->fetchAllAssociative($query);
}
// Vues des performances d'eleves d'une classe en une annee scolaire
public function getViewYearMark2024(ClassRoom $room, SchoolYear $year){
$em = $this->getDoctrine()->getManager();
$connection = $this->em->getConnection();
$quaters = $year->getQuaters();
foreach ($quaters as $quater) {
$this->getViewQuaterMark2024($room,$quater );
}
$query = "
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
";
$connection->executeQuery($query);
$query = "SELECT * FROM V_STUDENT_MARK_YEAR".$room->getId()."_".$year->getId();
$this->annualMark = $connection->fetchAllAssociative($query);
}
// Vues des abscences d'eleves d'une classe en une annee scolaire
public function buildAbsYearView(ClassRoom $room, SchoolYear $year){
$connection = $this->em->getConnection();
$quaters = $year->getQuaters();
// Construction de la vue des abscences annuelles de la classe pour l'annee
foreach($quaters as $quater){
$this->buildAbsQuaterView($room, $quater);
}
$query =
"CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_YEAR" . $room->getId() . "_" . $year->getId() . " 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" . $room->getId() . "_" . $quaters[0]->getId() . " qt1
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[1]->getId() . " qt2 ON qt1.std = qt2.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[2]->getId() . " 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" . $room->getId() . "_" . $quaters[1]->getId() . " qt2
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[0]->getId() . " qt1 ON qt1.std = qt2.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[2]->getId() . " 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" . $room->getId() . "_" . $quaters[2]->getId() . " qt3
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[0]->getId() . " qt1 ON qt1.std = qt3.std
LEFT JOIN V_STUDENT_ABSCENCE_QUATER" . $room->getId() . "_" . $quaters[1]->getId() . " qt2 ON qt2.std = qt3.std
ORDER BY std;";
$connection->executeQuery($query);
}
public function getAbsYearFromView(ClassRoom $room, SchoolYear $year){
$connection = $this->em->getConnection();
$this->buildAbsYearView($room, $year);
$query = "SELECT * FROM V_STUDENT_ABSCENCE_YEAR".$room->getId()."_".$year->getId();
return $connection->fetchAllAssociative($query);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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);
$em = $this->getDoctrine()->getManager();
$year = $this->schoolYearService->sessionYearById();
$connection = $this->em->getConnection();
$quater = $this->qtRepo->findOneBy(array("activated" => true));
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYear($room, $year->getId());
$this->getViewQuaterMark2024($room, $quater);
$result = [];
foreach ($studentEnrolled as $std) { // Parcourir les étudiants inscrits
$result[$std["id"]] = []; // Initialiser un sous-tableau pour chaque étudiant
foreach ($room->getModules() as $module) { // Parcourir les modules de la salle
foreach ($module->getCourses() as $crs) {
if(in_array($crs->getId(), $checkedValues))
{
// Collecter les note de chaque combinaison étudiant/module
$result[$std["id"]][$crs->getId()] = $this->getStudentQuaterMark($std, $crs); // Remplacer 'null' par la donnée que vous voulez associer
}
}
}
}
$mainTeacher = $this->mainTeacherRepo->findOneBy(array("classRoom" => $room, "schoolYear" => $year))-> getTeacher();
$options = [
'orientation' => 'Landscape', // Spécifie l'orientation paysage
'page-size' => 'A4', // Taille de page A4
'margin-top' => '10mm', // Marges pour personnaliser l'apparence
'margin-bottom' => '10mm',
'margin-left' => '10mm',
'margin-right' => '10mm'
];
$html = $this->renderView('classroom/recapitulatiftrimWithMoy.html.twig', array(
'year' => $year,
'datas' => $result,
'room' => $room,
'quater' => $quater,
'checkedValues'=>$checkedValues,
'students' => $studentEnrolled,
'mainTeacher' => $mainTeacher
));
return new Response(
$this->pdf->getOutputFromHtml($html, $options),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="recap_trim' . $quater->getId().'_'.$room->getName() . '.pdf"'
)
);
}
/**
* Finds and displays a ClassRoom entity.
*
* @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');
$em = $this->getDoctrine()->getManager();
$year = $this->schoolYearService->sessionYearById();
$this->getViewYearMark2024($room, $year);
$studentEnrolled = $this->stdRepo->findEnrolledStudentsThisYear($room, $year->getId());
$result = [];
$courses = [];
// Préparation des données
foreach ($studentEnrolled as $std) {
$result[$std["id"]]['student'] = $std["lastname"]." ". $std["firstname"];
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);
}
}
}
}
// Générer le fichier Excel
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// En-têtes de colonnes (lignes 1)
$sheet->setCellValue('A1', 'Élève');
$colIndex = 2;
foreach ($courses as $courseId => $courseName) {
$sheet->setCellValueByColumnAndRow($colIndex, 1, $courseName);
$colIndex++;
}
$sheet->setCellValueByColumnAndRow($colIndex, 1, "MOYENNE");
// Remplir les données
$rowIndex = 2;
foreach ($result as $studentData) {
$sheet->setCellValueByColumnAndRow(1, $rowIndex, $studentData['student']);
$colIndex = 2;
$totalMackCoef =0;
$totalCoef =0;
foreach ($courses as $courseId => $courseName) {
$mark = $studentData[$courseId]["value"]*$studentData[$courseId]["weight"];
$coef = $studentData[$courseId]["coef"];
$totalMackCoef += $mark*$coef;
$totalCoef += $coef;
$sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $mark);
$colIndex++;
}
$moy = $totalCoef>0 ? $totalMackCoef/$totalCoef : "#";
$sheet->setCellValueByColumnAndRow($colIndex, $rowIndex, $moy);
$rowIndex++;
}
// Retourner le fichier Excel en téléchargement
$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;
}
public function officialExam()
{
// Retrieve student categories from the corresponding repository
$categoriesStudent = $this->getDoctrine()->getRepository(CategStudent::class)->findAll();
// Initialize arrays for student categories, mentions, and counters
$studentCategories = [];
$mentionCategories = [];
$studentCountCategories = [];
$mentionCountCategories = [];
// Fill the arrays with data from student categories
foreach ($categoriesStudent as $category) {
$studentCategories[] = $category->getName();
$mentionCategories[] = $category->getMention();
$studentCountCategories[] = $category->getCountStudent();
$mentionCountCategories[] = $category->getCountMention();
}
// Render the Twig template and pass the data in JSON format
return $this->render('admin/class_room/show.html.twig', [
'studentCategories' => json_encode($studentCategories),
'mentionCategories' => json_encode($mentionCategories),
'studentCountCategories' => json_encode($studentCountCategories),
'mentionCountCategories' => json_encode($mentionCountCategories),
]);
}
/**
* @Route("/classroom/insolvents", name="admin_classroom_insolvents")
*/
public function listInsolventStudents(): Response
{
$year = $this->schoolYearService->sessionYearById();
$paymentPlan = $year->getPaymentPlan();
// List of student subscriptions for the class
$subscriptions = $this->subRepo->findBy(array("schoolYear" => $year), array("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,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="insolvent_student_' . $year->getCode() . '.pdf"'
)
);
}
/**
* @Route("/classroom/{id}", name="class_room_stats")
*/
public function showClassRoomStats(ClassRoomRepository $classRoomRepository, ClassRoom $room): Response
{
$classRoom = $classRoomRepository->find($id);
$successfulCount = $classRoomRepository->countSuccessfulStudentsForClass($classRoom);
$unsuccessfulCount = $classRoomRepository->countUnsuccessfulStudentsForClass($classRoom);
$mentionStatistics = $classRoomRepository->getMentionStatisticsForClass($classRoom);
return $this->render('class_room/stats.html.twig', [
'classRoom' => $classRoom,
'successfulCount' => $successfulCount,
'unsuccessfulCount' => $unsuccessfulCount,
'mentionStatistics' => $mentionStatistics,
]);
}
/**
* @Route("/classroom/{id}/insolvent", name="admin_classroom_insolvent")
*/
public function listInsolventStudentsByRoom(ClassRoom $room): Response
{
$year = $this->schoolYearService->sessionYearById();
$paymentPlan = $year->getPaymentPlan();
// List of student subscriptions for the class
$subscriptions = $this->subRepo->findBy(array("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,
array(
'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(array("schoolYear" => $year, "classRoom" => $room));
$installments = $this->instRepo->findBy(array("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;
}
// Calculer la somme des valeurs entières
$sum = array_sum($ratesByRoom);
// Calculer la moyenne
$avg = count($ratesByRoom) > 0 ? $sum / count($ratesByRoom) : 0;
$rates[$room->getName()] = $avg ;
}
$html = $this->render('school_year/templating/recovery_rates_by_room.html.twig', [
'rates' => $rates,
'year' => $year,
]);
return new Response(
$this->pdf->getOutputFromHtml($html),
200,
array(
'Content-Type' => 'application/pdf',
'Content-Disposition' => 'inline; filename="insolvent_student_' . $year->getCode() . '.pdf"'
)
);
}
}

src/Controller/ClassRoomController.php line 1225