src/Controller/ClassRoomController.php line 117

Open in your IDE?
  1. <?php
  2. namespace App\Controller;
  3. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  4. use Symfony\Component\HttpFoundation\Response;
  5. use Symfony\Component\Routing\Annotation\Route;
  6. use Symfony\Component\HttpFoundation\Request;
  7. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
  8. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
  9. use Sensio\Bundle\FrameworkExtraBundle\Configuration\ParamConverter;
  10. use Knp\Snappy\Pdf;
  11. use Knp\Bundle\SnappyBundle\Snappy\Response\PdfResponse;
  12. use Symfony\Component\HttpFoundation\Session\SessionInterface;
  13. use App\Repository\AttributionRepository;
  14. use Doctrine\ORM\EntityManagerInterface;
  15. use App\Repository\ClassRoomRepository;
  16. use App\Repository\SchoolYearRepository;
  17. use App\Repository\QuaterRepository;
  18. use App\Repository\SequenceRepository;
  19. use App\Repository\EvaluationRepository;
  20. use App\Repository\StudentRepository;
  21. use App\Repository\MainTeacherRepository;
  22. use App\Repository\MarkRepository;
  23. use App\Entity\ClassRoom;
  24. use App\Entity\SchoolYear;
  25. use App\Form\ClassRoomType;
  26. use App\Entity\Sequence;
  27. use App\Entity\Quater;
  28. use App\Repository\SubscriptionRepository;
  29. use App\Repository\InstallmentRepository;
  30. use App\Service\SchoolYearService;
  31. /**
  32.  * ClassRoom controller.
  33.  *
  34.  * @Route("prof/rooms")
  35.  */
  36. class ClassRoomController extends AbstractController
  37. {
  38.     private $em;
  39.     private $repo;
  40.     private $scRepo;
  41.     private $stdRepo;
  42.     private $subRepo;
  43.     private $seqRepo;
  44.     private $evalRepo;
  45.     private $qtRepo;
  46.     private $markRepo;
  47.     private  $snappy;
  48.     private $session;
  49.     private Pdf $pdf;
  50.     private SchoolYearService $schoolYearService;
  51.     private MainTeacherRepository $mainTeacherRepo;
  52.     private AttributionRepository $attRepo;
  53.     private InstallmentRepository $instRepo;
  54.     public function __construct(Pdf $pdf,InstallmentRepository $instRepoAttributionRepository $attRepoMainTeacherRepository $mainTeacherRepoSchoolYearService $schoolYearService,MarkRepository $markRepoQuaterRepository $qtRepoStudentRepository $stdRepoEvaluationRepository $evalRepoSchoolYearRepository $scRepoSequenceRepository $seqRepoClassRoomRepository $repo,  SubscriptionRepository $subRepo,  EntityManagerInterface $emPdf $snappy,  SessionInterface $session)
  55.     {
  56.         $this->em $em;
  57.         $this->pdf $pdf;
  58.         $this->repo $repo;
  59.         $this->scRepo $scRepo;
  60.         $this->attRepo $attRepo;
  61.         $this->seqRepo $seqRepo;
  62.         $this->evalRepo $evalRepo;
  63.         $this->mainTeacherRepo $mainTeacherRepo;
  64.         $this->stdRepo $stdRepo;
  65.         $this->instRepo $instRepo;
  66.         $this->qtRepo $qtRepo;
  67.         $this->subRepo $subRepo;
  68.         $this->markRepo $markRepo;
  69.         $this->snappy $snappy;
  70.         $this->session $session;        
  71.         $this->schoolYearService $schoolYearService;
  72.     }
  73.     /**
  74.      * Lists all ClassRoomme entities.
  75.      *
  76.      * @Route("/", name="admin_classrooms")
  77.      * @Method("GET")
  78.      * @Template()
  79.      */
  80.     public function indexAction()
  81.     {
  82.         $classrooms $this->repo->findAll();
  83.         $year $this->schoolYearService->sessionYearById();
  84.         $seq $this->seqRepo->findOneBy(array("activated" => true));
  85.         $mainTeachers =  $this->mainTeacherRepo->findBy(array("schoolYear" => $year));
  86.         $mainTeachersMap = array();
  87.         foreach($mainTeachers as $mt){
  88.             $mainTeachersMap[$mt->getClassRoom()->getId()] = $mt->getTeacher();
  89.         }
  90.         return $this->render('classroom/index.html.twig', array(
  91.             'mainTeachers' => $mainTeachersMap,
  92.             'classrooms' => $classrooms,
  93.             'year' => $year,
  94.             'seq' => $seq->getId(),
  95.         ));
  96.     }
  97.     /** 
  98.      * Finds and displays a ClassRoomme entity.
  99.      *
  100.      * @Route("/{id}/show", name="admin_classrooms_show", requirements={"id"="\d+"})
  101.      * @Method("GET")
  102.      * @Template()
  103.      */
  104.     public function showAction(ClassRoom $classroomStudentRepository $stdRepo)
  105.     {
  106.         // Année scolaire et seuquence en cours
  107.         $year $this->schoolYearService->sessionYearById();
  108.         $seq $this->seqRepo->findOneBy(array("activated" => true));
  109.         // Elèves inscrits
  110.         $studentEnrolled $stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  111.         $fileExists = [];
  112.         foreach ($studentEnrolled as $std) {
  113.             $filename "assets/images/student/" $std->getMatricule() . ".jpg";
  114.             $fileExists[$std->getId()] = file_exists($filename);
  115.         }
  116.         
  117.           // Attributions de cours durant l'annee
  118.         $attributions $this->attRepo->findByYearAndByRoom($year,$classroom);
  119.         $attributionsMapCourses null;
  120.         foreach($attributions as $att){
  121.               $attributionsMapCourses[$att->getCourse()->getId()] = $att;
  122.         }
  123.         // Liste des resulats au examens officiels
  124.         $officialExamResults $this->subRepo->countByMention($year$classroom);
  125.         $mentionCategories = [];
  126.         $mentionCountCategories = [];
  127.         foreach ($officialExamResults as $exam) {
  128.             switch ($exam["officialExamResult"]) {
  129.                 case  "0":
  130.                     $mentionCategories[] = "ECHEC";
  131.                     break;
  132.                 case  "1p":
  133.                     $mentionCategories[] = "PASSABLE";
  134.                     break;
  135.                 case  "1a":
  136.                     $mentionCategories[] = "ASSEZ-BIEN";
  137.                     break;
  138.                 case  "1b":
  139.                     $mentionCategories[] = "BIEN";
  140.                     break;
  141.                 case  "1t":
  142.                     $mentionCategories[] = "TRES-BIEN";
  143.                     break;
  144.                 case  "1e":
  145.                     $mentionCategories[] = "EXCELLENT";
  146.                     break;
  147.                 case  "A":
  148.                     $mentionCategories[] = "5 POINTS";
  149.                     break;
  150.                 case  "B":
  151.                     $mentionCategories[] = "4 POINTS";
  152.                     break;
  153.                 case  "C":
  154.                     $mentionCategories[] = "3 POINTS";
  155.                     break;
  156.                 case  "D":
  157.                     $mentionCategories[] = "2 POINTS";
  158.                     break;
  159.                 case  "E":
  160.                     $mentionCategories[] = "1 POINT";
  161.                     break;
  162.             }
  163.             $mentionCountCategories[] = $exam["count"];
  164.         }
  165.         // Extraction de donnees pour les graphes
  166.         $seqs $this->seqRepo->findSequenceThisYear($year);
  167.         $evalSeqs = [];
  168.         foreach ($seqs as $seq) {
  169.             $evalSeqs[$seq->getId()] = $this->evalRepo->findBy(array("classRoom" => $classroom"sequence" => $seq));
  170.         }
  171.         $courses = [];
  172.         $averageSeqs = [];
  173.         // Traitements de donnees pour les graphes de notes sequentielles
  174.         foreach ($evalSeqs[$seq->getId()] as $eval) {
  175.             $courses[] = $eval->getCourse()->getWording();
  176.         }
  177.         foreach ($seqs as $seq) {
  178.             $average = [];
  179.             foreach ($evalSeqs[$seq->getId()]  as $eval) {
  180.                 $average[] = $eval->getMoyenne();
  181.             }
  182.             $averageSeqs[$seq->getId()] = $average;
  183.         }
  184.         // Recherche de l'enseignant titulaire
  185.         $mainTeacher null;
  186.         foreach($classroom->getMainTeachers() as $mainT){
  187.                 if($mainT->getSchoolYear()->getId() == $year->getId()){
  188.                     $mainTeacher $mainT->getTeacher();
  189.                 }
  190.         }
  191.     
  192.         $results['mainteacher'] = $mainTeacher;
  193.         $results['classroom'] = $classroom;
  194.         $results['attributions'] = $attributionsMapCourses;
  195.         $results['modules'] = $classroom->getModules();
  196.         $results['studentEnrolled'] = $studentEnrolled;
  197.         $results['cours'] = json_encode($courses);
  198.         $results['fileExists'] = $fileExists;
  199.         $results['sessions'] = json_encode($seqs);
  200.         $results['mentionCategories'] = json_encode($mentionCategories);
  201.         $results['mentionCountCategories'] = json_encode($mentionCountCategories);
  202.         foreach ($seqs as $seq) {
  203.             $results[strtolower($seq->getWording())] = json_encode($averageSeqs[$seq->getId()]);
  204.         }
  205.         return $this->render('classroom/show.html.twig'$results);
  206.     }
  207.     /** 
  208.      * Finds and displays a ClassRoomme entity.
  209.      *
  210.      * @Route("/{id}/stat", name="admin_classrooms_stat", requirements={"id"="\d+"})
  211.      * @Method("GET")
  212.      * @Template()
  213.      */
  214.     public function statAction(ClassRoom $classroom)
  215.     {
  216.         return $this->render('classroom/show.html.twig', array());
  217.     }
  218.     /**
  219.      * Finds and displays a ClassRoom entity.
  220.      *
  221.      * @Route("/{id}/reportCardsYear", name="admin_classrooms_reportcards_year", requirements={"id"="\d+"})
  222.      * @Method("GET")
  223.      * @Template()
  224.      */
  225.     public function reportCardsYearAction(ClassRoom $classroom)
  226.     {
  227.         set_time_limit(600);
  228.         $connection $this->em->getConnection();
  229.         $year $this->schoolYearService->sessionYearById();
  230.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  231.         $statement $connection->prepare(
  232.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ1 AS
  233.             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
  234.             FROM  mark  m   JOIN  student    std     ON  m.student_id        =   std.id
  235.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  236.             JOIN  class_room room    ON   eval.class_room_id     =   room.id
  237.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  238.             JOIN  attribution att    ON  att.course_id      =   crs.id
  239.             JOIN  user  teach ON  att.teacher_id  =   teach.id
  240.             JOIN  module     modu    ON  modu.id       =   crs.module_id
  241.             JOIN  sequence   seq     ON  seq.id     =   eval.sequence_id
  242.             JOIN  quater   quat     ON  seq.quater_id     =   quat.id
  243.             JOIN  school_year   year     ON  quat.school_year_id     =   year.id
  244.             WHERE    room.id = ? AND eval.sequence_id =1
  245.           "
  246.         );
  247.         $statement->bindValue(1$classroom->getId());
  248.         $statement->execute();
  249.         $statement $connection->prepare(
  250.             "CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ2 AS
  251.             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
  252.             FROM  mark  m   
  253.             JOIN  student    std     ON  m.student_id        =   std.id
  254.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  255.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  256.             WHERE  eval.class_room_id = ? AND eval.sequence_id = 2
  257.             ORDER BY matricule,eval; "
  258.         );
  259.         $statement->bindValue(1$classroom->getId());
  260.         $statement->execute();
  261.         $statement $connection->prepare(
  262.             "CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ3 AS
  263.             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
  264.             FROM  mark  m   
  265.             JOIN  student    std     ON  m.student_id        =   std.id
  266.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  267.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  268.             WHERE  eval.class_room_id =? AND eval.sequence_id = 3
  269.             ORDER BY matricule,eval; "
  270.         );
  271.         $statement->bindValue(1$classroom->getId());
  272.         $statement->execute();
  273.         $statement $connection->prepare(
  274.             "CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ4 AS
  275.             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
  276.             FROM  mark  m   
  277.             JOIN  student    std     ON  m.student_id        =   std.id
  278.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  279.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  280.             WHERE  eval.class_room_id = ? AND eval.sequence_id = 4
  281.             ORDER BY matricule,eval; "
  282.         );
  283.         $statement->bindValue(1$classroom->getId());
  284.         $statement->execute();
  285.         $statement $connection->prepare(
  286.             "CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ5 AS
  287.             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
  288.             FROM  mark  m   
  289.             JOIN  student    std     ON  m.student_id        =   std.id
  290.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  291.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  292.             WHERE  eval.class_room_id = ? AND eval.sequence_id = 5
  293.             ORDER BY matricule,eval; "
  294.         );
  295.         $statement->bindValue(1$classroom->getId());
  296.         $statement->execute();
  297.         $statement $connection->prepare(
  298.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_DATA_SEQ6 AS
  299.             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
  300.             FROM  mark  m   JOIN  student    std     ON  m.student_id        =   std.id
  301.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  302.             JOIN  class_room room    ON   eval.class_room_id     =   room.id
  303.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  304.             WHERE    room.id = ? AND eval.sequence_id = 6
  305.             ORDER BY std.matricule"
  306.         );
  307.         $statement->bindValue(1$classroom->getId());
  308.         $statement->execute();
  309.         $dataYear $this->em->getConnection()->executeQuery("select *  from V_STUDENT_MARK_DATA_SEQ1 
  310.             INNER JOIN  V_STUDENT_MARK_DATA_SEQ2 ON  V_STUDENT_MARK_DATA_SEQ1.matricule = V_STUDENT_MARK_DATA_SEQ2.matricule 
  311.             INNER JOIN  V_STUDENT_MARK_DATA_SEQ3 ON  V_STUDENT_MARK_DATA_SEQ2.matricule = V_STUDENT_MARK_DATA_SEQ3.matricule 
  312.             INNER JOIN  V_STUDENT_MARK_DATA_SEQ4 ON  V_STUDENT_MARK_DATA_SEQ3.matricule = V_STUDENT_MARK_DATA_SEQ4.matricule 
  313.             INNER JOIN  V_STUDENT_MARK_DATA_SEQ5 ON  V_STUDENT_MARK_DATA_SEQ4.matricule = V_STUDENT_MARK_DATA_SEQ5.matricule 
  314.             INNER JOIN  V_STUDENT_MARK_DATA_SEQ6 ON  V_STUDENT_MARK_DATA_SEQ5.matricule = V_STUDENT_MARK_DATA_SEQ6.matricule 
  315.             ")->fetchAll();
  316.         $this->snappy->setTimeout(600);
  317.         $html $this->renderView('classroom/reportcard/annual.html.twig', array(
  318.             'year' => $year,
  319.             'data' => $dataYear,
  320.             'room' => $classroom,
  321.             'year' => $year,
  322.             'students' => $studentEnrolled,
  323.         ));
  324.         return new Response(
  325.             $this->snappy->getOutputFromHtml($html),
  326.             200,
  327.             array(
  328.                 'Content-Type' => 'application/pdf',
  329.                 'Content-Disposition' => 'attachment; filename="BUL_ANN_' $classroom->getName() . '.pdf"',
  330.             )
  331.         );
  332.     }
  333.     public function viewSeq(int $i){
  334.         $year $this->schoolYearService->sessionYearById();
  335.         $connection $this->em->getConnection();
  336.         $statement $connection->prepare(
  337.             " CREATE OR REPLACE VIEW V_STUDENT_MARK_SEQ" $i " AS
  338.             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
  339.             FROM  mark  m   JOIN  student    std     ON  m.student_id        =   std.id
  340.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  341.             JOIN  class_room room    ON  eval.class_room_id     =   room.id
  342.             JOIN  course      crs    ON  eval.course_id      =   crs.id
  343.             JOIN  attribution att    ON  att.course_id      =   crs.id  
  344.             JOIN  user      teach    ON  att.teacher_id  =   teach.id
  345.             JOIN  module     modu    ON  modu.id       =   crs.module_id
  346.             JOIN  sequence    seq    ON  seq.id     =   eval.sequence_id
  347.             JOIN  quater     quat    ON  seq.quater_id     =   quat.id
  348.             JOIN  school_year   year ON  quat.school_year_id     =   year.id
  349.             WHERE att.year_id =? AND  room.id = ? AND eval.sequence_id =?  
  350.             ORDER BY room.id,modu.id ,  std;    "
  351.         );
  352.         $statement->bindValue(1$year->getId());
  353.         $statement->bindValue(2$classroom->getId());
  354.         $statement->bindValue(3$seq->getId());
  355.         $statement->execute();
  356.     }
  357.     /**
  358.      * Finds and displays a ClassRoom entity.
  359.      *
  360.      * @Route("/{id}/reportCardsApcYearapc", name="admin_class_reportcards_apc_year", requirements={"id"="\d+"})
  361.      * @Method("GET")
  362.      * @Template()
  363.      */
  364.     public function reportCards2YearAction(ClassRoom $classroom)
  365.     {
  366.         set_time_limit(600);
  367.         $connection $this->em->getConnection();
  368.         $year $this->schoolYearService->sessionYearById();
  369.         $sequences  $this->seqRepo->findSequenceThisYear($year);
  370.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  371.         $i 1;
  372.         foreach ($sequences as $seq) {
  373.             /*******************************************************************************************************************/
  374.             /***************CREATION DE la VIEW DES NOTES  SEQUENTIELLES, TRIMESTRIELLES ET ANNUELLES DE LA CLASSE**************/
  375.             /*******************************************************************************************************************/
  376.             // CAS DES NOTES SEQUENTIELLES
  377.               // $this->viewSeq($i, $classroom, $seq);
  378.             $this->getViewSeqData$classroom$seq$i);
  379.             
  380.            
  381.             $i++;
  382.         }
  383.         // CAS DES NOTES TRIMESTRIELLES
  384.         $statement $connection->prepare(
  385.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_1 AS
  386.             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
  387.             FROM V_STUDENT_MARK_SEQ1 seq1
  388.             JOIN  V_STUDENT_MARK_SEQ2 seq2  
  389.             ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs)
  390.             ORDER BY seq1.std"
  391.         );
  392.         $statement->execute();
  393.         $statement $connection->prepare(
  394.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_2 AS
  395.             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
  396.             FROM V_STUDENT_MARK_SEQ3 seq1
  397.             JOIN  V_STUDENT_MARK_SEQ4 seq2  
  398.             ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs)
  399.             ORDER BY seq1.std"
  400.         );
  401.         $statement->execute();
  402.         $statement $connection->prepare(
  403.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_3 AS
  404.             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
  405.             FROM V_STUDENT_MARK_SEQ5 seq1
  406.             JOIN  V_STUDENT_MARK_SEQ6 seq2  
  407.             ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs)
  408.             ORDER BY seq1.std"
  409.         );
  410.         $statement->execute();
  411.         // CAS DES NOTES ANNUELLES
  412.         $statement $connection->prepare(
  413.             "CREATE OR REPLACE VIEW ANNUAL_DATA AS
  414.             SELECT DISTINCT   student.id as idStd ,  student.matricule as matricule ,  student.image_name as profileImagePath, 
  415.             student.lastname as lastname, student.firstname as firstname, student.birthday as birthday,
  416.             student.gender as gender,student.birthplace as birthplace , 
  417.             class_room.name as room_name,
  418.             course.wording as course, course.coefficient as coef, 
  419.             module.name as module,
  420.             user.full_name as teacher,
  421.             quat1.std,quat1.modu,
  422.             quat1.value as value1, quat1.weight as weight1,  
  423.             quat2.value as value2,  quat2.weight as weight2,  
  424.             quat3.value as value3,quat3.weight as weight3,
  425.             greatest(quat1.weight , quat2.weight, quat3.weight ) as weight,
  426.             ( quat1.value*quat1.weight+ quat2.value*quat2.weight + quat3.value*quat3.weight) /(quat1.weight+quat2.weight+quat3.weight) as value
  427.             FROM student  
  428.             JOIN V_STUDENT_MARK_QUATER_1  quat1 ON  student.id = quat1.std
  429.             JOIN  class_room ON class_room.id = quat1.room
  430.             JOIN  course    ON course.id = quat1.crs
  431.             JOIN  module    ON course.module_id = quat1.modu
  432.             JOIN user ON user.full_name = quat1.teacher
  433.             JOIN   V_STUDENT_MARK_QUATER_2   quat2  ON  quat1.std = quat2.std AND quat1.crs = quat2.crs
  434.             JOIN 
  435.             V_STUDENT_MARK_QUATER_3   quat3  ON  quat1.std = quat3.std AND quat1.crs = quat3.crs
  436.             ORDER BY  quat1.std, quat1.modu
  437.         
  438.             "
  439.         );
  440.         $statement->execute();
  441.         $dataYear $connection->executeQuery("SELECT *  FROM ANNUAL_DATA ")->fetchAll();
  442.         // For calculating ranks
  443.         $statement $connection->prepare(
  444.             "  CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
  445.             SELECT DISTINCT idStd , CAST( SUM(value*weight*coef) / sum(weight*coef) AS decimal(4,2)) as moyenne, sum(weight*coef) as totalCoef
  446.             FROM ANNUAL_DATA 
  447.             GROUP BY idStd
  448.             ORDER BY SUM(value*weight*coef) DESC"
  449.         );
  450.         $statement->execute();
  451.         $annualAvg $connection->executeQuery("SELECT *  FROM V_STUDENT_RANKS ")->fetchAll();
  452.         $annualAvgArray = [];
  453.         $sumAvg 0;
  454.         $rank 0;
  455.         $rankArray = [];
  456.         foreach ($annualAvg as $avg) {
  457.             $annualAvgArray[$avg['idStd']] = $avg['moyenne'];
  458.             $rankArray[$avg['idStd']] = ++$rank;
  459.             $sumAvg += $avg['moyenne'];
  460.         }
  461.         $this->snappy->setTimeout(600);
  462.         $html $this->renderView('classroom/reportcardYear.html.twig', array(
  463.             'year' => $year,
  464.             'data' => $dataYear,
  465.             'room' => $classroom,
  466.             'students' => $studentEnrolled,
  467.             'ranks' => $rankArray,
  468.             'means' => $annualAvgArray,
  469.             'genMean' => $sumAvg sizeof($annualAvgArray),
  470.         ));
  471.         //return new Response($html);
  472.         return new Response(
  473.             $this->snappy->getOutputFromHtml($html),
  474.             200,
  475.             array(
  476.                 'Content-Type' => 'application/pdf',
  477.                 'Content-Disposition' => 'attachment; filename="BUL_ANN_' $classroom->getName() . '.pdf"',
  478.             )
  479.         );
  480.     }
  481.     /**
  482.      * Finds and displays a Evaluation entity.
  483.      *
  484.      * @Route("/{room}/{seq}/pdf", name="admin_classrooms_recapitulatif", requirements={"room"="\d+","seq"="\d+"})
  485.      * @Method("GET")
  486.      * @Template()
  487.      * @return Response
  488.      */
  489.     public function recapitulatifAction(ClassRoom $roomSequence $seq)
  490.     {
  491.         // Année scolaire en cours
  492.         $year $this->schoolYearService->sessionYearById();
  493.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($room$year);
  494.         $html $this->renderView('classroom/templating/recapitulatifseqvierge.html.twig', array(
  495.             'room' => $room,
  496.             'seq' => $seq,
  497.             'students' => $studentEnrolled,
  498.             'year' => $year,
  499.         ));
  500.         $options = [
  501.             'orientation' => 'Landscape'// Changer ici entre 'Portrait' ou 'Landscape'
  502.             'page-size' => 'A4',          // Format de page
  503.         ];
  504.         return new Response(
  505.             $this->pdf->getOutputFromHtml($html,  $options),
  506.             200,
  507.             array(
  508.                 'Content-Type'          => 'application/pdf',
  509.                 'Content-Disposition'   => 'inline; filename="fiche_recep_' $room->getName() . '.pdf"'
  510.             )
  511.         );
  512.     }
  513.     /**
  514.      * Finds and displays a ClassRoom entity.
  515.      *
  516.      * @Route("/{id}/recapitulatifseq", name="admin_classrooms_recapitulatif_seq", requirements={"id"="\d+"})
  517.      * @Method("GET")
  518.      * @Template()
  519.      */
  520.     public function recapSeqAction(ClassRoom $roomRequest $request)
  521.     {
  522.         // set_time_limit(600);
  523.         $checkedValues $request->request->get('selected_courses');
  524.         $em $this->getDoctrine()->getManager();
  525.         $year $this->schoolYearService->sessionYearById();
  526.         $seq $this->seqRepo->findOneBy(array("activated" => true));
  527.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($room$year);
  528.         $datas $this->markRepo->findMarksBySequenceAndClassOrderByStd($seq$room);
  529.         $html $this->renderView('classroom/recapitulatifseq.html.twig', array(
  530.             'room' => $room,
  531.             'datas' => $datas,
  532.             'year' => $year,
  533.             'seq' => $seq,
  534.             'students' => $studentEnrolled,
  535.             'checkedValues' => $checkedValues
  536.         ));
  537.         return new Response($html);
  538.     }
  539.     /**
  540.      * @Route("/create",name= "admin_classrooms_new", methods={"GET","POST"})
  541.      */
  542.     public function create(Request $request): Response
  543.     {
  544.         if (!$this->getUser()) {
  545.             $this->addFlash('warning''You need login first!');
  546.             return $this->redirectToRoute('app_login');
  547.         }
  548.         $schoolyear = new ClassRoom();
  549.         $form $this->createForm(ClassRoomType::class, $schoolyear);
  550.         $form->handleRequest($request);
  551.         if ($form->isSubmitted() && $form->isValid()) {
  552.             $this->em->persist($schoolyear);
  553.             $this->em->flush();
  554.             $this->addFlash('success''ClassRoom succesfully created');
  555.             return $this->redirectToRoute('admin_classrooms');
  556.         }
  557.         return $this->render(
  558.             'classroom/new.html.twig',
  559.             ['form' => $form->createView()]
  560.         );
  561.     }
  562.     /**
  563.      * Rapport séquentiel d'enregistrement des notes.
  564.      *
  565.      * @Route("/{id}/evalrepport", name="admin_current_fulfilled_eval_show", requirements={"id"="\d+"})
  566.      * @Method("GET")
  567.      * @Template()
  568.      */
  569.     public function currentFullfilledEvalAction(ClassRoom $classroom)
  570.     {
  571.         $em $this->getDoctrine()->getManager();
  572.         $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));
  573.         // Liste des séquences de l'année scolaire en cours
  574.         $sequences $em->getRepository('AppBundle:Sequence')->findSequencesBySchoolYear($year);
  575.         // Liste des matières
  576.         $courses $em->getRepository('AppBundle:Course')->findProgrammedCoursesInClass($classroom);
  577.         // Elèves inscrits
  578.         foreach ($sequences as $seq) {
  579.             // Lecture de chaque tableau de chaque ligne
  580.             foreach ($courses as $course) {
  581.                 // Liste des évaluations
  582.                 $evaluation $em->getRepository('AppBundle:Evaluation')->findOneBy(array(
  583.                     "classRoom" => $classroom,
  584.                     "sequence" => $seq"course" => $course
  585.                 ));
  586.                 if ($evaluation != null) {
  587.                     $evaluations[$seq->getId()][$course->getId()] = 1;
  588.                 } else {
  589.                     $evaluations[$seq->getId()][$course->getId()] = 0;
  590.                 }
  591.             }
  592.         }
  593.         return $this->render('classroom/eval_repport.html.twig', array(
  594.             'evaluations' => $evaluations,
  595.             'courses' => $courses,
  596.             'room' => $classroom,
  597.             'sequences' => $sequences,
  598.         ));
  599.     }
  600.     /**
  601.      * Displays a form to edit an existing ClassRoomme entity.
  602.      *
  603.      * @Route("/{id}/edit", name="admin_classrooms_edit", requirements={"id"="\d+"}, methods={"GET","PUT"})
  604.      * @Template()
  605.      */
  606.     public function edit(Request $requestClassRoom $room): Response
  607.     {
  608.         $form $this->createForm(ClassRoomType::class, $room, [
  609.             'method' => 'PUT'
  610.                 ]);
  611.         
  612.         $form->handleRequest($request);
  613.         if ($form->isSubmitted() && $form->isValid()) {
  614.             $this->em->flush();
  615.             $this->addFlash('success''ClassRoom succesfully updated');
  616.             return $this->redirectToRoute('admin_classrooms');
  617.         }
  618.         return $this->render('classroom/edit.html.twig', [
  619.             'room' => $room,
  620.             'form' => $form->createView()
  621.         ]);
  622.     }
  623.     /**
  624.      * Deletes a ClassRoom entity.
  625.      *
  626.      * @Route("/{id}/delete", name="admin_classrooms_delete", requirements={"id"="\d+"}, methods={"DELETE"})
  627.      
  628.      */
  629.     public function delete(ClassRoom $qRequest $request): Response
  630.     {
  631.         //  dd($q);
  632.         // if($this->isCsrfTokenValid('classrooms_deletion'.$schoolyear->getId(), $request->request->get('crsf_token') )){
  633.         $this->em->remove($q);
  634.         $this->em->flush();
  635.         $this->addFlash('info''ClassRoom succesfully deleted');
  636.         //    }
  637.         return $this->redirectToRoute('admin_classrooms');
  638.     }
  639.     /**
  640.      * Finds and displays a ClassRoom entity.
  641.      *
  642.      * @Route("/{id}/fichesimple", name="admin_classrooms_fichesimple", requirements={"id"="\d+"})
  643.      * @Method("GET")
  644.      * @Template()
  645.      */
  646.     public function fichesiplmeAction(ClassRoom $classroom)
  647.     {
  648.         // Année scolaire en cours
  649.         $year $this->schoolYearService->sessionYearById();
  650.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  651.         $html $this->renderView('classroom/templating/fiche_repport_notes.html.twig', array(
  652.             'year' => $year,
  653.             'room' => $classroom,
  654.             'students' => $studentEnrolled,
  655.         ));
  656.         return new Response(
  657.             $this->pdf->getOutputFromHtml($html),
  658.             200,
  659.             array(
  660.                 'Content-Type'          => 'application/pdf',
  661.                 'Content-Disposition'   => 'inline; filename="fiche_pv_' $classroom->getName() . '.pdf"'
  662.             )
  663.         );
  664.     }
  665.      /**
  666.      * Finds and displays a ClassRoom entity.
  667.      *
  668.      * @Route("/{id}/disciplinary_record", name="admin_classrooms_disciplinary_record", requirements={"id"="\d+"})
  669.      * @Method("GET")
  670.      * @Template()
  671.      */
  672.     public function ficheDisciplineAction(ClassRoom $classroom)
  673.     {
  674.         // Année scolaire en cours
  675.         $year $this->schoolYearService->sessionYearById();
  676.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  677.         $html $this->renderView('classroom/templating/fiche_repport_disc.html.twig', array(
  678.             'year' => $year,
  679.             'room' => $classroom,
  680.             'students' => $studentEnrolled,
  681.         ));
  682.         return new Response(
  683.             $this->pdf->getOutputFromHtml($html),
  684.             200,
  685.             array(
  686.                 'Content-Type'          => 'application/pdf',
  687.                 'Content-Disposition'   => 'inline; filename="fich_disc_' $classroom->getName() . '.pdf"'
  688.             )
  689.         );
  690.     }
  691.     /**
  692.      * LISTE DES ELEVES DE LA CLASSE DANS UNE FICHE DE PRESENTATION.
  693.      *
  694.      * @Route("/{id}/presentation", name="admin_classrooms_presentation", requirements={"id"="\d+"})
  695.      * @Method("GET")
  696.      * @Template()
  697.      */
  698.     public function presentationAction(ClassRoom $classroom)
  699.     {
  700.         // Année scolaire en cours
  701.         $year $this->schoolYearService->sessionYearById();
  702.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  703.         $html $this->renderView('classroom/templating/student_list.html.twig', array(
  704.             'year' => $year,
  705.             'room' => $classroom,
  706.             'students' => $studentEnrolled,
  707.         ));
  708.         return new Response(
  709.             $this->pdf->getOutputFromHtml($html),
  710.             200,
  711.             array(
  712.                 'Content-Type'          => 'application/pdf',
  713.                 'Content-Disposition'   => 'inline; filename="std_list_' $classroom->getName() . '.pdf"'
  714.             )
  715.         );
  716.     }
  717.     /**
  718.      * MOYENNE GENERALE DE LA CLASSE A UNE SEQUENCE
  719.      * @Route("/{id_room}/{id_seq}/sqavg", name="admin_classrooms_avg_seq", requirements={"id_room"="\d+", "id_seq"="\d+"})
  720.      * @ParamConverter("room", options={"mapping": {"id_room" : "id"}})
  721.      * @ParamConverter("seq", options={"mapping": {"id_seq"   : "id"}})
  722.      * @Method("GET")
  723.      * @Template()
  724.      */
  725.     public function generalSeqAverage(ClassRoom $roomSequence $seq)
  726.     {
  727.         $dql =     "SELECT SUM(evaluation.moyenne * course.coefficient)/SUM(course.coefficient)  FROM App\Entity\Evaluation evaluation , App\Entity\Course  course
  728.         WHERE evaluation.course=         course.id AND evaluation.sequence=?2 AND evaluation.classRoom=?1 ";
  729.         $avg_seq1 $this->em->createQuery($dql)
  730.             ->setParameter(1$room->getId())
  731.             ->setParameter(2$seq->getId())
  732.             ->getSingleScalarResult();
  733.         return round($avg_seq12);
  734.     }
  735.     /**
  736.      * MOYENNE GENERALE DE LA CLASSE A UN TRIMESTRE
  737.      * @Route("/{id_room}/{id_quat}/qtavg", name="admin_classrooms_avg_quat", requirements={"id_room"="\d+", "id_quat"="\d+"})
  738.      * @ParamConverter("room", options={"mapping": {"id_room" : "id"}})
  739.      * @ParamConverter("quater", options={"mapping": {"id_quat"   : "id"}})
  740.      * @Method("GET")
  741.      * @Template()
  742.      */
  743.     public function generalQuatAverage(ClassRoom $roomQuater $quater)
  744.     {
  745.         $dql =     "SELECT SUM(evaluation.moyenne * course.coefficient)/SUM(course.coefficient)  FROM App\Entity\Evaluation evaluation , App\Entity\Course  course
  746.         WHERE evaluation.course=         course.id AND evaluation.sequence=?2 AND evaluation.classRoom=?1 ";
  747.         $avg_seq 0;
  748.         foreach ($quater->getSequences() as $seq) {
  749.             $avg_seq += $this->em->createQuery($dql)
  750.                 ->setParameter(1$room->getId())
  751.                 ->setParameter(2$seq->getId())
  752.                 ->getSingleScalarResult();
  753.         }
  754.         return round($avg_seq 22);
  755.     }
  756.     /**
  757.      * Finds and displays a Evaluation entity.
  758.      *
  759.      * @Route("/{room}/pdf", name="admin_classrooms_blanc_ann", requirements={"room"="\d+"})
  760.      * @Method("GET")
  761.      * @Template()
  762.      * @return Response
  763.      */
  764.     public function annualSummaryAction(ClassRoom $room)
  765.     {
  766.         $year $this->schoolYearService->sessionYearById();
  767.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($room$year);
  768.         $html $this->renderView('classroom/templating/blankAnnualForm.html.twig', array(
  769.             'room' => $room,
  770.             'students' => $studentEnrolled,
  771.             'year' => $year,
  772.         ));
  773.         return new Response(
  774.             $this->pdf->getOutputFromHtml($html, array(
  775.                 'default-header' => false
  776.             )),
  777.             200,
  778.             array(
  779.                 'Content-Type' => 'application/pdf',
  780.                 'Content-Disposition' => 'attachment; filename="recap_empty_' $room->getName() . '.pdf"',
  781.             )
  782.         );
  783.     }
  784.     /**
  785.      * Finds and displays a ClassRoom entity.
  786.      *
  787.      * @Route("/{id}/reportCardSeq", name="admin_classrooms_reportcards_seq", requirements={"id"="\d+"})
  788.      * @Method("GET")
  789.      * @Template()
  790.      */
  791.     public function reportCardSeqAction(ClassRoom $classroom)
  792.     {
  793.         set_time_limit(600);
  794.         $totalNtCoef 0;
  795.         $totalCoef 0;
  796.         $em $this->getDoctrine()->getManager();
  797.         $year =$this->schoolYearService->sessionYearById();
  798.         $sequence $this->seqRepo->findOneBy(array("activated" => true));
  799.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  800.         // Existance des photos d'eleves
  801.         $fileExists = [];
  802.         foreach ($studentEnrolled as $std) {
  803.             $filename "assets/images/student/" $std->getMatricule() . ".jpg";
  804.             $fileExists[$std->getId()] = file_exists($filename);
  805.         }
  806.         $evaluations $this->evalRepo->findSequantialExamsOfRoom($classroom->getId(), $sequence->getId());
  807.         foreach ($evaluations as $ev) {
  808.             $totalNtCoef += $ev->getMoyenne() * $ev->getCourse()->getCoefficient();
  809.             $totalCoef += $ev->getCourse()->getCoefficient();
  810.         }
  811.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  812.         $this->getViewSeqData($classroom$sequence0);
  813.         $connection $this->em->getConnection();
  814.         $datas $connection->executeQuery("SELECT *  FROM V_STUDENT_MARK_SEQ0  ")->fetchAll();
  815.          // For calculating ranks
  816.         $statement $connection->prepare(
  817.             "  CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
  818.                 SELECT DISTINCT std , CAST( SUM(value*weight*coef) / sum(weight*coef) AS decimal(4,2)) as moyenne, sum(weight*coef) as totalCoef
  819.                 FROM V_STUDENT_MARK_SEQ0 
  820.                 GROUP BY std
  821.                 ORDER BY SUM(value*weight*coef) DESC"
  822.         );
  823.         $statement->execute();
  824.         $seqAvg $connection->executeQuery("SELECT *  FROM V_STUDENT_RANKS ")->fetchAll();
  825.         $seqAvgArray = [];
  826.         $sumAvg 0;
  827.         $rank 0;
  828.         $rankArray = [];
  829.        
  830.         foreach ($seqAvg as $avg) {
  831.             $seqAvgArray[$avg['std']] = $avg['moyenne'];
  832.             $rankArray[$avg['std']] = ++$rank;
  833.             $sumAvg += $avg['moyenne'];
  834.         }
  835.         // CAS DES ABSCENCES SEQUENTIELLES
  836.         $statement $connection->prepare(
  837.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_SEQ AS
  838.             SELECT DISTINCT   seq.std as std , seq.total_hours  as abscences
  839.             FROM V_STUDENT_ABSCENCE_SEQ0 seq
  840.             ORDER BY std "
  841.         );
  842.         $statement->execute();
  843.          // Traitement des abscences
  844.          $absences $connection->executeQuery("SELECT *  FROM V_STUDENT_ABSCENCE_SEQ ")->fetchAll();
  845.          $absencesArray = [];
  846.          foreach ($absences as $abs) {
  847.              $absencesArray[$abs['std']] = $abs['abscences'];
  848.          }
  849.         $html $this->renderView('classroom/reportcard/sequential.html.twig', array(
  850.             'year' => $year,
  851.             'datas' => $datas,
  852.             'students' => $studentEnrolled,
  853.             'sequence' => $sequence,
  854.             'quater' => $sequence->getQuater(),
  855.             'room' => $classroom,
  856.             'students' => $studentEnrolled,
  857.             'ranks' => $rankArray,
  858.             'means' => $seqAvgArray,
  859.             'abscences' => $absencesArray,
  860.             'genMean' => $sumAvg sizeof($seqAvgArray),
  861.             'fileExists'=> $fileExists
  862.         ));
  863.         return new Response(
  864.             $this->pdf->getOutputFromHtml($html),
  865.             200,
  866.             array(
  867.                 'Content-Type'          => 'application/pdf',
  868.                 'Content-Disposition'   => 'inline; filename="bull_seq_' $classroom->getName() . '.pdf"'
  869.             )
  870.         );
  871.         
  872.     }
  873.     public function getViewSeqData(ClassRoom $room,Sequence $seqint $i){
  874.         $connection $this->em->getConnection();
  875.         $year $this->schoolYearService->sessionYearById();
  876.          // CAS DES NOTES SEQUENTIELLES
  877.         $statement $connection->prepare(
  878.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_SEQ" $i " AS
  879.             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
  880.             FROM  mark  m   JOIN  student    std     ON  m.student_id        =   std.id
  881.             JOIN  evaluation eval    ON  m.evaluation_id     =   eval.id
  882.             JOIN  class_room room    ON   eval.class_room_id     =   room.id
  883.             JOIN  course     crs     ON  eval.course_id      =   crs.id
  884.             JOIN  attribution att    ON  att.course_id      =   crs.id  and att.year_id = ?
  885.             JOIN  user  teach        ON  att.teacher_id  =   teach.id
  886.             JOIN  module     modu    ON  modu.id       =   crs.module_id
  887.             JOIN  sequence   seq     ON  seq.id     =   eval.sequence_id
  888.             LEFT JOIN  abscence_sheet   sheet     ON  seq.id     =   sheet.sequence_id AND sheet.class_room_id = room.id
  889.             LEFT JOIN  abscence   abs     ON  sheet.id     =   abs.abscence_sheet_id
  890.             JOIN  quater   quat      ON  seq.quater_id     =   quat.id
  891.             WHERE    room.id = ? AND eval.sequence_id =? 
  892.             ORDER BY room.id,modu.id ,  std; "
  893.         );
  894.         $statement->bindValue(1$year->getId());
  895.         $statement->bindValue(2$room->getId());
  896.         $statement->bindValue(3$seq->getId());
  897.         $statement->execute();
  898.         $statement $connection->prepare(
  899.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_SEQ" $i " AS
  900.             SELECT DISTINCT  room.id as room, abs.student_id as std, sum( abs.weight) as total_hours
  901.             FROM   class_room room  
  902.             LEFT JOIN  abscence_sheet   sheet     ON  sheet.class_room_id = room.id AND sheet.sequence_id = ?
  903.             LEFT JOIN  abscence   abs     ON  sheet.id     =   abs.abscence_sheet_id
  904.             WHERE  room.id = ? 
  905.             GROUP BY  std
  906.             ORDER BY room.id, std; "
  907.         );
  908.         $statement->bindValue(1$seq->getId());
  909.         $statement->bindValue(2$room->getId());
  910.         $statement->execute();
  911.        
  912.     }
  913.     /**
  914.      * Finds and displays a ClassRoom entity.
  915.      *
  916.      * @Route("/{id}/reportCardsTrim", name="admin_classrooms_reportcards_trim", requirements={"id"="\d+"})
  917.      * @Method("GET")
  918.      * @Template()
  919.      */
  920.     public function reportCardsTrimAction(ClassRoom $roomRequest $request)
  921.     {
  922.         $connection $this->em->getConnection();
  923.         $year $this->schoolYearService->sessionYearById();
  924.         $quater $this->qtRepo->findOneBy(array("activated" => true));
  925.         $sequences $this->seqRepo->findBy(array("quater" => $quater));
  926.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($room$year);
  927.          // Existance des photos d'eleves
  928.          $fileExists = [];
  929.          foreach ($studentEnrolled as $std) {
  930.              $filename "assets/images/student/" $std->getMatricule() . ".jpg";
  931.              $fileExists[$std->getId()] = file_exists($filename);
  932.          }
  933.         /*******************************************************************************************************************/
  934.         /***************CREATION DE la VIEW DES NOTES  SEQUENTIELLES, TRIMESTRIELLES  DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
  935.         /*******************************************************************************************************************/
  936.         $i 1;
  937.         foreach ($sequences as $seq) {
  938.             // CAS DES NOTES et ABSCENCES SEQUENTIELLES
  939.             $this->getViewSeqData($room$seq$i);
  940.             $i++;
  941.         }
  942.         // CAS DES NOTES TRIMESTRIELLES
  943.         $statement $connection->prepare(
  944.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER AS
  945.             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
  946.             FROM V_STUDENT_MARK_SEQ1 seq1
  947.             JOIN  V_STUDENT_MARK_SEQ2 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  948.             ORDER BY std , module"
  949.         );
  950.         $statement->execute();
  951.         // CAS DES ABSCENCES TRIMESTRIELLES
  952.         $statement $connection->prepare(
  953.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER AS
  954.             SELECT DISTINCT   seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
  955.             FROM V_STUDENT_ABSCENCE_SEQ1 seq1
  956.              JOIN  V_STUDENT_ABSCENCE_SEQ2 seq2  ON  (seq1.std    =   seq2.std  )
  957.             ORDER BY std "
  958.         );
  959.         $statement->execute();
  960.         $dataQuater $connection->executeQuery("SELECT *  FROM V_STUDENT_MARK_QUATER marks  ")->fetchAll();
  961.         // For calculating ranks
  962.         $statement $connection->prepare(
  963.             "  CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
  964.         SELECT DISTINCT std , CAST( SUM(value*weight*coef) / sum(weight*coef) AS decimal(4,2)) as moyenne, sum(weight*coef) as totalCoef
  965.         FROM V_STUDENT_MARK_QUATER 
  966.         GROUP BY std
  967.         ORDER BY SUM(value*weight*coef) DESC"
  968.         );
  969.         $statement->execute();
  970.         $quaterAvg $connection->executeQuery("SELECT *  FROM V_STUDENT_RANKS ")->fetchAll();
  971.         $quaterAvgArray = [];
  972.         $sumAvg 0;
  973.         $rank 0;
  974.         $rankArray = [];
  975.        
  976.         foreach ($quaterAvg as $avg) {
  977.             $quaterAvgArray[$avg['std']] = $avg['moyenne'];
  978.             $rankArray[$avg['std']] = ++$rank;
  979.             $sumAvg += $avg['moyenne'];
  980.         }
  981.         
  982.         // Traitement des abscences
  983.         $absences $connection->executeQuery("SELECT *  FROM V_STUDENT_ABSCENCE_QUATER ")->fetchAll();
  984.         $absencesArray = [];
  985.         foreach ($absences as $abs) {
  986.             $absencesArray[$abs['std']] = $abs['abscences'];
  987.         }
  988.         $pdf->setTimeout(600);
  989.         $html $this->renderView('classroom/reportcard/quaterly.html.twig', array(
  990.             'year' => $year,
  991.             'data' => $dataQuater,
  992.             'ranks' => $rankArray,
  993.             'means' => $quaterAvgArray,
  994.             'abscences' => $absencesArray,
  995.             'genMean' => $sumAvg sizeof($quaterAvgArray),
  996.             'room' => $room,
  997.             'quater' => $quater,
  998.             'sequences' => $sequences,
  999.             'students' => $studentEnrolled,
  1000.             'fileExists'=> $fileExists
  1001.         ));
  1002.         return new Response(
  1003.             $this->pdf->getOutputFromHtml($html),
  1004.             200,
  1005.             array(
  1006.                 'Content-Type'          => 'application/pdf',
  1007.                 'Content-Disposition'   => 'inline; filename="' $room->getName() . '.pdf"'
  1008.             )
  1009.         );
  1010.         // return new Response($html);
  1011.     }
  1012.     /**
  1013.      * Finds and displays a ClassRoom entity.
  1014.      *
  1015.      * @Route("/{id}/annualavglist", name="admin_avg_list", requirements={"id"="\d+"})
  1016.      * @Method("GET")
  1017.      * @Template()
  1018.      */
  1019.     public function annualAvgList(ClassRoom $classroomRequest $request)
  1020.     {
  1021.         $connection $this->em->getConnection();
  1022.         $year $this->schoolYearService->sessionYearById();
  1023.         $quater $this->qtRepo->findOneBy(array("activated" => true));
  1024.         $sequences  $this->seqRepo->findSequenceThisYear($year);
  1025.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  1026.          /*******************************************************************************************************************/
  1027.         /***************CREATION DE la VIEW DES NOTES  SEQUENTIELLES, TRIMESTRIELLES  DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
  1028.         /*******************************************************************************************************************/
  1029.         $i 1;
  1030.         foreach ($sequences as $seq) {
  1031.             // CAS DES NOTES et ABSCENCES SEQUENTIELLES
  1032.             $this->getViewSeqData($classroom$seq$i);
  1033.             $i++;
  1034.         }
  1035.          // CAS DES NOTES TRIMESTRIELLES1
  1036.          $statement $connection->prepare(
  1037.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_1 AS
  1038.             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
  1039.             FROM V_STUDENT_MARK_SEQ1 seq1
  1040.             JOIN  V_STUDENT_MARK_SEQ2 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1041.             ORDER BY std , modu"
  1042.         );
  1043.         $statement->execute();
  1044.        
  1045.          // CAS DES NOTES TRIMESTRIELLES2
  1046.          $statement $connection->prepare(
  1047.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_2 AS
  1048.             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
  1049.             FROM V_STUDENT_MARK_SEQ3 seq1
  1050.             JOIN  V_STUDENT_MARK_SEQ4 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1051.             ORDER BY std , modu"
  1052.         );
  1053.         $statement->execute();
  1054.           // CAS DES NOTES TRIMESTRIELLES3
  1055.           $statement $connection->prepare(
  1056.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_3 AS
  1057.             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
  1058.             FROM V_STUDENT_MARK_SEQ5 seq1
  1059.             JOIN  V_STUDENT_MARK_SEQ6 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1060.             ORDER BY std , modu"
  1061.         );
  1062.         $statement->execute();
  1063.         $statement $connection->prepare(
  1064.             "CREATE OR REPLACE VIEW ANNUAL_DATA AS
  1065.             SELECT DISTINCT   student.id as idStd ,  student.matricule as matricule 
  1066.             student.lastname as lastname, student.firstname as firstname
  1067.             course.wording as course, course.coefficient as coef, 
  1068.             module.name as module,
  1069.             user.full_name as teacher,
  1070.             quat1.std, quat1.modu,
  1071.             quat1.value as value1,  quat1.weight as weight1,
  1072.             quat2.value as value2,  quat2.weight as weight2,
  1073.             quat3.value as value3,  quat3.weight as weight3,
  1074.             greatest(quat1.weight , quat2.weight, quat3.weight ) as weight,
  1075.             ( quat1.value*quat1.weight+ quat2.value*quat2.weight + quat3.value*quat3.weight) /(quat1.weight+quat2.weight+quat3.weight) as value
  1076.             FROM student  
  1077.             JOIN   V_STUDENT_MARK_QUATER_1      quat1   ON  student.id = quat1.std 
  1078.             JOIN   V_STUDENT_MARK_QUATER_2      quat2  ON  student.id = quat2.std AND quat1.crs = quat2.crs
  1079.             JOIN   V_STUDENT_MARK_QUATER_3      quat3   ON  student.id = quat3.std AND quat2.crs = quat3.crs
  1080.             JOIN  class_room ON class_room.id = quat1.room
  1081.             JOIN  course     ON course.id = quat1.crs
  1082.             JOIN  module     ON course.module_id = quat1.modu
  1083.             JOIN  user       ON user.full_name = quat1.teacher
  1084.             ORDER BY  quat1.std, quat1.modu
  1085.             "
  1086.         );
  1087.         $statement->execute();
  1088.         $dataYear $connection->executeQuery("SELECT *  FROM ANNUAL_DATA ")->fetchAll();
  1089.         // For calculating ranks
  1090.         $statement $connection->prepare(
  1091.             "  CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
  1092.             SELECT DISTINCT idStd , CAST( SUM(value*weight*coef) / sum(weight*coef) AS decimal(4,2)) as moyenne, sum(weight*coef) as totalCoef
  1093.             FROM ANNUAL_DATA 
  1094.             GROUP BY idStd
  1095.             ORDER BY SUM(value*weight*coef) DESC"
  1096.         );
  1097.         $statement->execute();
  1098.         $annualAvg $connection->executeQuery("SELECT *  FROM V_STUDENT_RANKS ")->fetchAll();
  1099.         $annualAvgArray = [];
  1100.         $sumAvg 0;
  1101.         $rank 0;
  1102.         $rankArray = [];
  1103.         foreach ($annualAvg as $avg) {
  1104.             $annualAvgArray[$avg['idStd']] = $avg['moyenne'];
  1105.             $rankArray[$avg['idStd']] = ++$rank;
  1106.             $sumAvg += $avg['moyenne'];
  1107.         }
  1108.         $html $this->renderView('classroom/avglist.html.twig', array(
  1109.            
  1110.             'year' => $year,
  1111.             'room' => $classroom,
  1112.             'students' => $studentEnrolled,
  1113.             'ranks' => $rankArray,
  1114.             'means' => $annualAvgArray,
  1115.             'genMean' => $sumAvg sizeof($annualAvgArray),
  1116.         ));
  1117.         return new Response(
  1118.             $this->snappy->getOutputFromHtml($html,  [
  1119.                 'page-size' => 'A4',
  1120.                
  1121.             ]),
  1122.             200,
  1123.             array(
  1124.                 'Content-Type' => 'application/pdf',
  1125.                 'Content-Disposition' => 'attachment; filename="BUL_ANN_' $classroom->getName() . '.pdf"',
  1126.             )
  1127.         );
  1128.     }
  1129.     /**
  1130.      * Finds and displays a ClassRoom entity.
  1131.      *
  1132.      * @Route("/{id}/reportCards3ApcYearApc", name="admin_class_reportcards_3_apc_year", requirements={"id"="\d+"})
  1133.      * @Method("GET")
  1134.      * @Template()
  1135.      */
  1136.     public function reportCards3YearAction(ClassRoom $classroomRequest $request)
  1137.     {
  1138.         $headerFontSize $request->request->get('header_font_size');
  1139.         $lineHeight $request->request->get('line_height');
  1140.         $copyright =  $request->request->get('copyright')=="on";
  1141.         $reverse =  $request->request->get('reverse')=="on";
  1142.         
  1143.         $connection $this->em->getConnection();
  1144.         $year $this->schoolYearService->sessionYearById();
  1145.         $quater $this->qtRepo->findOneBy(array("activated" => true));
  1146.         $sequences  $this->seqRepo->findSequenceThisYear($year);
  1147.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYearInClass($classroom$year);
  1148.          // Existance des photos d'eleves
  1149.          $fileExists = [];
  1150.          foreach ($studentEnrolled as $std) {
  1151.              $filename "assets/images/student/" $std->getMatricule() . ".jpg";
  1152.              $fileExists[$std->getId()] = file_exists($filename);
  1153.          }
  1154.         /*******************************************************************************************************************/
  1155.         /***************CREATION DE la VIEW DES NOTES  SEQUENTIELLES, TRIMESTRIELLES  DE LA CLASSE, AINSI QUE DE LA VIEW DES ABSCENCES**************/
  1156.         /*******************************************************************************************************************/
  1157.         $i 1;
  1158.         foreach ($sequences as $seq) {
  1159.             // CAS DES NOTES et ABSCENCES SEQUENTIELLES
  1160.             $this->getViewSeqData($classroom$seq$i);
  1161.             $i++;
  1162.         }
  1163.         // CAS DES NOTES TRIMESTRIELLES1
  1164.         $statement $connection->prepare(
  1165.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_1 AS
  1166.             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
  1167.             FROM V_STUDENT_MARK_SEQ1 seq1
  1168.             JOIN  V_STUDENT_MARK_SEQ2 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1169.             ORDER BY std , modu"
  1170.         );
  1171.         $statement->execute();
  1172.         // CAS DES ABSCENCES TRIMESTRIELLES1
  1173.         $statement $connection->prepare(
  1174.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_1 AS
  1175.             SELECT DISTINCT   seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
  1176.             FROM V_STUDENT_ABSCENCE_SEQ1 seq1
  1177.              JOIN  V_STUDENT_ABSCENCE_SEQ2 seq2  ON  (seq1.std    =   seq2.std  )
  1178.             ORDER BY std "
  1179.         );
  1180.         $statement->execute();
  1181.          // CAS DES NOTES TRIMESTRIELLES2
  1182.          $statement $connection->prepare(
  1183.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_2 AS
  1184.             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
  1185.             FROM V_STUDENT_MARK_SEQ3 seq1
  1186.             JOIN  V_STUDENT_MARK_SEQ4 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1187.             ORDER BY std , modu"
  1188.         );
  1189.         $statement->execute();
  1190.         // CAS DES ABSCENCES TRIMESTRIELLES2
  1191.         $statement $connection->prepare(
  1192.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_2 AS
  1193.             SELECT DISTINCT   seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
  1194.             FROM V_STUDENT_ABSCENCE_SEQ3 seq1
  1195.              JOIN  V_STUDENT_ABSCENCE_SEQ4 seq2  ON  (seq1.std    =   seq2.std  )
  1196.             ORDER BY std "
  1197.         );
  1198.         $statement->execute();
  1199.          // CAS DES NOTES TRIMESTRIELLES3
  1200.          $statement $connection->prepare(
  1201.             "  CREATE OR REPLACE VIEW V_STUDENT_MARK_QUATER_3 AS
  1202.             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
  1203.             FROM V_STUDENT_MARK_SEQ5 seq1
  1204.             JOIN  V_STUDENT_MARK_SEQ6 seq2  ON  (seq1.std    =   seq2.std  AND seq1.crs = seq2.crs )
  1205.             ORDER BY std , modu"
  1206.         );
  1207.         $statement->execute();
  1208.         // CAS DES ABSCENCES TRIMESTRIELLES3
  1209.         $statement $connection->prepare(
  1210.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_QUATER_3 AS
  1211.             SELECT DISTINCT   seq1.std as std , seq1.total_hours + seq2.total_hours as abscences
  1212.             FROM V_STUDENT_ABSCENCE_SEQ5 seq1
  1213.              JOIN  V_STUDENT_ABSCENCE_SEQ6 seq2  ON  (seq1.std    =   seq2.std  )
  1214.             ORDER BY std "
  1215.         );
  1216.         $statement->execute();
  1217.         set_time_limit(600);
  1218.         $statement $connection->prepare(
  1219.             "CREATE OR REPLACE VIEW ANNUAL_DATA AS
  1220.             SELECT DISTINCT   student.id as idStd ,  student.matricule as matricule ,  student.image_name as profileImagePath, 
  1221.             student.lastname as lastname, student.firstname as firstname, student.birthday as birthday,
  1222.             student.gender as gender,student.birthplace as birthplace , 
  1223.             class_room.name as room_name,
  1224.             course.wording as course, course.coefficient as coef, 
  1225.             module.name as module,
  1226.             user.full_name as teacher,
  1227.             quat1.std, quat1.modu,
  1228.             quat1.value as value1,  quat1.weight as weight1,
  1229.             quat2.value as value2,  quat2.weight as weight2,
  1230.             quat3.value as value3,  quat3.weight as weight3,
  1231.             greatest(quat1.weight , quat2.weight, quat3.weight ) as weight,
  1232.             ( quat1.value*quat1.weight+ quat2.value*quat2.weight + quat3.value*quat3.weight) /(quat1.weight+quat2.weight+quat3.weight) as value
  1233.             FROM student  
  1234.             JOIN   V_STUDENT_MARK_QUATER_1      quat1   ON  student.id = quat1.std 
  1235.             JOIN   V_STUDENT_MARK_QUATER_2      quat2  ON  student.id = quat2.std AND quat1.crs = quat2.crs
  1236.             JOIN   V_STUDENT_MARK_QUATER_3      quat3   ON  student.id = quat3.std AND quat2.crs = quat3.crs
  1237.             JOIN  class_room ON class_room.id = quat1.room
  1238.             JOIN  course     ON course.id = quat1.crs
  1239.             JOIN  module     ON course.module_id = quat1.modu
  1240.             JOIN  user       ON user.full_name = quat1.teacher
  1241.             ORDER BY  quat1.std, quat1.modu
  1242.             "
  1243.         );
  1244.         $statement->execute();
  1245.         $dataYear $connection->executeQuery("SELECT *  FROM ANNUAL_DATA ")->fetchAll();
  1246.         // For calculating ranks
  1247.         $statement $connection->prepare(
  1248.             "  CREATE OR REPLACE VIEW V_STUDENT_RANKS AS
  1249.             SELECT DISTINCT idStd , CAST( SUM(value*weight*coef) / sum(weight*coef) AS decimal(4,2)) as moyenne, sum(weight*coef) as totalCoef
  1250.             FROM ANNUAL_DATA 
  1251.             GROUP BY idStd
  1252.             ORDER BY SUM(value*weight*coef) DESC"
  1253.         );
  1254.         $statement->execute();
  1255.         $annualAvg $connection->executeQuery("SELECT *  FROM V_STUDENT_RANKS ")->fetchAll();
  1256.         $annualAvgArray = [];
  1257.         $sumAvg 0;
  1258.         $rank 0;
  1259.         $rankArray = [];
  1260.         foreach ($annualAvg as $avg) {
  1261.             $annualAvgArray[$avg['idStd']] = $avg['moyenne'];
  1262.             $rankArray[$avg['idStd']] = ++$rank;
  1263.             $sumAvg += $avg['moyenne'];
  1264.         }
  1265.          // CAS DES ABSCENCES ANNUELLES
  1266.          $statement $connection->prepare(
  1267.             "  CREATE OR REPLACE VIEW V_STUDENT_ABSCENCE_ANNUAL AS
  1268.             SELECT DISTINCT   q1.std as std , q1.abscences + q2.abscences + q3.abscences as abscences
  1269.              FROM  V_STUDENT_ABSCENCE_QUATER_1 q1
  1270.              JOIN  V_STUDENT_ABSCENCE_QUATER_2 q2  ON  (q1.std    =   q2.std  )
  1271.              JOIN  V_STUDENT_ABSCENCE_QUATER_3 q3  ON  (q1.std    =   q3.std  )
  1272.             ORDER BY std "
  1273.         );
  1274.         $statement->execute();
  1275.          // Traitement des abscences
  1276.          $absences $connection->executeQuery("SELECT *  FROM V_STUDENT_ABSCENCE_ANNUAL ")->fetchAll();
  1277.          $absencesArray = [];
  1278.          foreach ($absences as $abs) {
  1279.              $absencesArray[$abs['std']] = $abs['abscences'];
  1280.          }
  1281.         $html $this->renderView('classroom/reportcard/annual.html.twig', array(
  1282.             "headerFontSize" => $headerFontSize,
  1283.             "lineHeight" => $lineHeight,
  1284.             "copyright" => $copyright,
  1285.             "reverse" => $reverse,
  1286.             'year' => $year,
  1287.             'data' => $dataYear,
  1288.             'room' => $classroom,
  1289.             'students' => $studentEnrolled,
  1290.             'abscences' => $absencesArray,
  1291.             'ranks' => $rankArray,
  1292.             'means' => $annualAvgArray,
  1293.             'genMean' => $sumAvg sizeof($annualAvgArray),
  1294.             'fileExists'=> $fileExists
  1295.         ));
  1296.         return new Response(
  1297.             $this->snappy->getOutputFromHtml($html,  [
  1298.                 'page-size' => 'A4',
  1299.                
  1300.             ]),
  1301.             200,
  1302.             array(
  1303.                 'Content-Type' => 'application/pdf',
  1304.                 'Content-Disposition' => 'attachment; filename="BUL_ANN_' $classroom->getName() . '.pdf"',
  1305.             )
  1306.         );
  1307.     }
  1308.     /**
  1309.      * Finds and displays a ClassRoom entity.
  1310.      *
  1311.      * @Route("/{id}/recapitulatiftrim", name="admin_classrooms_recapitulatif_trim", requirements={"id"="\d+"})
  1312.      * @Method("GET")
  1313.      * @Template()
  1314.      */
  1315.     public function recapTrimAction(ClassRoom $room)
  1316.     {
  1317.         set_time_limit(600);
  1318.         $em $this->getDoctrine()->getManager();
  1319.         $year $this->schoolYearService->sessionYearById();
  1320.         $quater $this->qtRepo->findOneBy(array("activated" => true));
  1321.         $sequences $this->seqRepo->findBy(array("quater" => $quater));
  1322.         $studentEnrolled $this->stdRepo->findEnrolledStudentsThisYear($room$year->getId());
  1323.         $data1 $this->markRepo->findMarksBySequenceAndClassOrderByStd($sequences[0], $room);
  1324.         $data1 $this->doubleEntry($data1$room);
  1325.         $data2 $this->markRepo->findMarksBySequenceAndClassOrderByStd($sequences[1], $room);
  1326.         $data2 $this->doubleEntry($data2$room);
  1327.         $datas null;
  1328.         foreach ($data1 as $students) {
  1329.             foreach ($students as $m) {
  1330.                 $n = new Mark();
  1331.                 $n->setStudent($m->getStudent());
  1332.                 $n->setRank($m->getEvaluation()->getCourse()->getCoefficient());
  1333.                 $n->setAppreciation($m->getEvaluation()->getCourse()->getWording());
  1334.                 $n->setValue($this->average($data1[$m->getStudent()->getMatricule()][$m->getEvaluation()->getCourse()->getId()], $data2[$m->getStudent()->getMatricule()][$m->getEvaluation()->getCourse()->getId()]));
  1335.                 $w $data1[$m->getStudent()->getMatricule()][$m->getEvaluation()->getCourse()->getId()]->getWeight() + $data2[$m->getStudent()->getMatricule()][$m->getEvaluation()->getCourse()->getId()]->getWeight();
  1336.                 if ($w != 0)
  1337.                     $n->setWeight($w);
  1338.                 else
  1339.                     $n->setWeight(0);
  1340.                 $datas[$m->getStudent()->getMatricule()][$m->getEvaluation()->getCourse()->getWording()] = $n;
  1341.             }
  1342.         }
  1343.         $this->get('knp_snappy.pdf')->setTimeout(600);
  1344.         //     if($classroom->getApc()){
  1345.         $html $this->renderView('classroom/recapitulatiftrimWithMoy.html.twig', array(
  1346.             'year' => $year,
  1347.             'datas' => $datas,
  1348.             'room' => $room,
  1349.             'quater' => $quater,
  1350.             'students' => $studentEnrolled
  1351.         ));
  1352.         return new Response($html);
  1353.     }
  1354.     public function officialExam()
  1355.     {
  1356.         // Retrieve student categories from the corresponding repository
  1357.         $categoriesStudent $this->getDoctrine()->getRepository(CategStudent::class)->findAll();
  1358.         // Initialize arrays for student categories, mentions, and counters
  1359.         $studentCategories = [];
  1360.         $mentionCategories = [];
  1361.         $studentCountCategories = [];
  1362.         $mentionCountCategories = [];
  1363.         // Fill the arrays with data from student categories
  1364.         foreach ($categoriesStudent as $category) {
  1365.             $studentCategories[] = $category->getName();
  1366.             $mentionCategories[] = $category->getMention();
  1367.             $studentCountCategories[] = $category->getCountStudent();
  1368.             $mentionCountCategories[] = $category->getCountMention();
  1369.         }
  1370.         // Render the Twig template and pass the data in JSON format
  1371.         return $this->render('admin/class_room/show.html.twig', [
  1372.             'studentCategories' => json_encode($studentCategories),
  1373.             'mentionCategories' => json_encode($mentionCategories),
  1374.             'studentCountCategories' => json_encode($studentCountCategories),
  1375.             'mentionCountCategories' => json_encode($mentionCountCategories),
  1376.         ]);
  1377.     }
  1378.         /**
  1379.      * @Route("/classroom/insolvents", name="admin_classroom_insolvents")
  1380.      */
  1381.     public function listInsolventStudents(): Response
  1382.     {
  1383.         $year $this->schoolYearService->sessionYearById();
  1384.         $paymentPlan =  $year->getPaymentPlan();
  1385.         // List of student subscriptions for the class
  1386.         $subscriptions $this->subRepo->findBy(array("schoolYear" =>  $year), array("classRoom"=>"ASC"));
  1387.         $insolventSub = [];
  1388.      
  1389.         foreach($subscriptions as $sub){
  1390.             if($year->paymentThresholdAmount($sub->getClassRoom()) > $sub->getStudent()->getPaymentsSum($year) ){
  1391.                 $insolventSub[]  = $sub;
  1392.             }
  1393.         }
  1394.          $html $this->render('school_year/templating/insolvent_students_list.html.twig', [
  1395.             
  1396.             'students' => $insolventSub,
  1397.             'year' => $year,
  1398.         ]);
  1399.         return new Response(
  1400.             $this->pdf->getOutputFromHtml($html),
  1401.             200,
  1402.             array(
  1403.                 'Content-Type'          => 'application/pdf',
  1404.                 'Content-Disposition'   => 'inline; filename="insolvent_student_' $year->getCode() . '.pdf"'
  1405.             )
  1406.         );
  1407.     }
  1408.     /**
  1409.      * @Route("/classroom/{id}", name="class_room_stats")
  1410.      */
  1411.     public function showClassRoomStats(ClassRoomRepository $classRoomRepositoryClassRoom $room): Response
  1412.     {
  1413.         $classRoom $classRoomRepository->find($id);
  1414.         $successfulCount $classRoomRepository->countSuccessfulStudentsForClass($classRoom);
  1415.         $unsuccessfulCount $classRoomRepository->countUnsuccessfulStudentsForClass($classRoom);
  1416.         $mentionStatistics $classRoomRepository->getMentionStatisticsForClass($classRoom);
  1417.         return $this->render('class_room/stats.html.twig', [
  1418.             'classRoom' => $classRoom,
  1419.             'successfulCount' => $successfulCount,
  1420.             'unsuccessfulCount' => $unsuccessfulCount,
  1421.             'mentionStatistics' => $mentionStatistics,
  1422.         ]);
  1423.     }
  1424.       /**
  1425.      * @Route("/classroom/{id}/insolvent", name="admin_classroom_insolvent")
  1426.      */
  1427.     public function listInsolventStudentsByRoom(ClassRoom $room): Response
  1428.     {
  1429.         $year $this->schoolYearService->sessionYearById();
  1430.         $paymentPlan =  $year->getPaymentPlan();
  1431.         // List of student subscriptions for the class
  1432.         $subscriptions $this->subRepo->findBy(array("schoolYear" =>  $year"classRoom" => $room));
  1433.         $students = [];
  1434.         $dueAmounts = [];
  1435.         foreach($subscriptions as $sub){
  1436.             if($year->paymentThresholdAmount($room) > $sub->getStudent()->getPaymentsSum($year) ){
  1437.                 $students[] = $sub->getStudent() ;
  1438.                 $dueAmounts[$sub->getStudent()->getId()] = $year->paymentThresholdAmount($room)-$sub->getStudent()->getPaymentsSum($year);
  1439.             }
  1440.         }
  1441.         $html $this->render('classroom/templating/insolvent_student_list.html.twig', [
  1442.             'room' => $room,
  1443.             'students' => $students,
  1444.             'year' => $year,
  1445.             'amounts' =>  $dueAmounts 
  1446.         ]);
  1447.         return new Response(
  1448.             $this->pdf->getOutputFromHtml($html),
  1449.             200,
  1450.             array(
  1451.                 'Content-Type'          => 'application/pdf',
  1452.                 'Content-Disposition'   => 'inline; filename="insolvent_student_' $room->getName() . '.pdf"'
  1453.             )
  1454.         );
  1455.     }
  1456.      /**
  1457.      * @Route("/insolventspercentage", name="admin_classroom_insolvents_percentage")
  1458.      */
  1459.     public function insolventStudentsRate(): Response
  1460.     {
  1461.         $year $this->schoolYearService->sessionYearById();
  1462.         $paymentPlan =  $year->getPaymentPlan();
  1463.         $rooms $this->repo->findAll();
  1464.         $rates = [];
  1465.         
  1466.         foreach($rooms as $room){
  1467.             $subscriptions $this->subRepo->findBy(array("schoolYear" =>  $year"classRoom" =>  $room));
  1468.             $installments $this->instRepo->findBy(array("classRoom" =>  $room"paymentPlan" =>  $paymentPlan));
  1469.             $sum 0;
  1470.             foreach($installments as $installment){
  1471.                 $sum += $installment->getAmount();
  1472.             }
  1473.             $ratesByRoom = [];
  1474.             foreach($subscriptions as $sub){
  1475.                  $ratesByRoom[]  = 100*$sub->getStudent()->getPaymentsSum($year) / $sum;
  1476.             }
  1477.               // Calculer la somme des valeurs entières
  1478.             $sum array_sum($ratesByRoom);
  1479.             // Calculer la moyenne
  1480.             $avg count($ratesByRoom) > $sum count($ratesByRoom) : 0;
  1481.             $rates[$room->getName()] = $avg ;
  1482.         }
  1483.         $html $this->render('school_year/templating/recovery_rates_by_room.html.twig', [
  1484.             
  1485.             'rates' => $rates,
  1486.             'year' => $year,
  1487.         ]);
  1488.         return new Response(
  1489.             $this->pdf->getOutputFromHtml($html),
  1490.             200,
  1491.             array(
  1492.                 'Content-Type'          => 'application/pdf',
  1493.                 'Content-Disposition'   => 'inline; filename="insolvent_student_' $year->getCode() . '.pdf"'
  1494.             )
  1495.         );
  1496.     }
  1497.    
  1498.  
  1499. }