<?php

use Doctrine\ORM\EntityRepository;

class SectionRepository extends EntityRepository
{

    public function addSection($body)
    {
        $entityManager = $this->getEntityManager();
        $section = addslashes(strip_tags(trim($body['section'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $student_data = $body['student_data'];

        $new_student_data = [];

        foreach ($student_data as $sd) {
            if ($sd != null) {
                array_push($new_student_data, $sd);
            }
        }

        if (sizeof($this->checkSection($section, $semester, $sub_code, $course)) == 0) {
            $Section = new Section();
            $Section->setSection($section);
            $Section->setSemester($semester);
            $Section->setSubCode($sub_code);
            $Section->setCourse($course);
            $Section->setSession($session);
            $Section->setStudentData($new_student_data);
            $entityManager->persist($Section);
            $entityManager->flush();
            if (null != $Section->getId()) {
                return "Section Registered Successfully.";
            } else {
                return "Failed";
            }

        } else {
            return "Section already exist.";
        }

    }

    public function updateSection($id, $body)
    {
        $entityManager = $this->getEntityManager();
        $section = addslashes(strip_tags(trim($body['section'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $course = addslashes(strip_tags(trim($body['course'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $student_data = $body['student_data'];

        $new_student_data = [];

        foreach ($student_data as $sd) {
            if ($sd != null) {
                array_push($new_student_data, $sd);
            }
        }

        if (sizeof($result = $this->getSectionEdit($id)) == 1) {
            $result[0]->setSection($section);
            $result[0]->setSemester($semester);
            $result[0]->setSubCode($sub_code);
            $result[0]->setCourse($course);
            $result[0]->setSession($session);
            $result[0]->setStudentData($new_student_data);
            $entityManager->persist($result[0]);
            $entityManager->flush();
            if (null != $result[0]->getId()) {
                return "Section Updated Successfully.";
            } else {
                return "Failed";
            }

        } else {
            return "Section not found";
        }

    }

    public function getSemSection($id, $sub_code)
    {   
        $sql = "SELECT section from section  where json_contains(student_data->'$[*].id',json_array(:id)) AND sub_code=:sub_code";
        $em = $this->getEntityManager();
        $stmt = $em->getConnection()->prepare($sql);
        $stmt->bindParam(':id', $id, PDO::PARAM_STR);
        $stmt->bindParam(':sub_code', $sub_code, PDO::PARAM_STR);
        $stmt->execute();
        $r = $stmt->fetchAll();
        if (sizeof($r) > 0) {
            return $r[0]['section'];
        } else {
            return null;
        }

    }

    public function getSection()
    {
        $entityManager = $this->getEntityManager();
        $dql = "SELECT r.id,r.section,r.semester,r.sub_code,r.course,r.session,r.student_data From Section r";
        $query = $entityManager->createQuery($dql);
        return $query->getResult();
    }

    public function getSectionSingle($id)
    {
        $entityManager = $this->getEntityManager();
        $dql = "SELECT r.id,r.section,r.semester,r.sub_code,r.course,r.session,r.student_data From Section r where r.id=:id";
        $query = $entityManager->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function getSectionSingleData($course, $semester, $sub_code, $section)
    {
        $entityManager = $this->getEntityManager();
        $dql = "SELECT r.student_data From Section r where r.course=:course AND r.semester=:semester AND r.sub_code=:sub_code AND r.section=:section";
        $query = $entityManager->createQuery($dql)
            ->setParameter('course', $course)
            ->setParameter('semester', $semester)
            ->setParameter('section', $section)
            ->setParameter('sub_code', $sub_code);
        $result = $query->getResult();
        return $result[0]['student_data'];
    }

    public function getSectionEdit($id)
    {
        $entityManager = $this->getEntityManager();
        $dql = "SELECT r From Section r where r.id=:id";
        $query = $entityManager->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function checkSection($section_name, $semester, $sub_code, $course)
    {
        $dql = 'SELECT r FROM Section r WHERE r.section=:section_name AND r.semester=:semester AND r.sub_code=:sub_code AND r.course=:course';
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('course', $course)->setParameter('sub_code', $sub_code)->setParameter('semester', $semester)->setParameter('section_name', $section_name);
        return $query->getResult();
    }

    public function deleteSection($id)
    {
        $dql = 'DELETE  FROM Section r WHERE r.id=:id';
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        $query->getResult();
        return true;
    }

}
