<?php

use Doctrine\ORM\EntityRepository;

class ClassCancelRepository extends EntityRepository
{

    public function addCancelClass($email, $body)
    {
        $entityManager = $this->getEntityManager();
        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        $teacher = addslashes(strip_tags(trim($body['teacher'])));
        $reason = addslashes(strip_tags(trim($body['reason'])));
        $room_number = addslashes(strip_tags(trim($body['room_number'])));
        $section = addslashes(strip_tags(trim($body['section'])));
        $period = addslashes(strip_tags(trim($body['period_number'])));
        $start_time = addslashes(strip_tags(trim($body['start_time'])));
        $end_time = addslashes(strip_tags(trim($body['end_time'])));
        $date1 = date('d-m-Y');
        if (sizeof($this->checkCancelClass($body)) == 0) {
            $routine = new ClassCancel();
            $routine->setSession($session);
            $routine->setCourse($course);
            $routine->setStream($stream);
            $routine->setSubCode($sub_code);
            $routine->setTeacher($teacher);
            $routine->setReason($reason);
            $routine->setDay($day);
            $routine->setSection($section);
            $routine->setSemester($semester);
            $routine->setRoomNumber($room_number);
            $routine->setPeriodNumber($period);
            $routine->setStartTime($start_time);
            $routine->setEndTime($end_time);
            $routine->setDate1($date1);
            $entityManager->persist($routine);
            $entityManager->flush();
            return 'Class Cancelled Successfully';
        } else {
            return 'Already Exists';
        }

    }

    public function getRoutine()
    {
        $dql = "SELECT r.id,r.session,r.course,r.stream,r.sub_code,r.teacher,r.day,r.semester,r.room_number,r.period_number,r.start_time,r.end_time,s.name as teacher_name FROM Routine r, Employee s WHERE r.teacher=s.email";
        $query = $this->getEntityManager()->createQuery($dql);
        return $query->getResult();
    }
    private function getRoutineForEdit($id)
    {
        $dql = "SELECT r FROM Routine r WHERE r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function getRoutineSingle($id)
    {
        $dql = "SELECT r.id,r.session,r.course,r.stream,r.sub_code,r.teacher,r.day,r.semester,r.room_number,r.period_number,r.start_time,r.end_time FROM Routine r WHERE r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();

    }

    public function countCancel($date, $teacher)
    {
        if ($teacher == '') {
            $dql = "SELECT count(r.id) as c FROM ClassCancel r WHERE r.date1=:date1";
            $query = $this->getEntityManager()->createQuery($dql)->setParameter('date1', $date);
            $r = $query->getResult();
            return $r[0]['c'];

        } else {
            $dql = "SELECT count(r.id) as c FROM ClassCancel r WHERE r.date1=:date1 AND r.teacher=:teacher";
            $query = $this->getEntityManager()->createQuery($dql)->setParameter('date1', $date)->setParameter('teacher', $teacher);
            $r = $query->getResult();
            return $r[0]['c'];

        }

    }
    public function getCancelledClass($body)
    {
        $start_date = addslashes(strip_tags(trim($body['start_date'])));
        $end_date = addslashes(strip_tags(trim($body['end_date'])));
        $teacher = addslashes(strip_tags(trim($body['teacher'])));
        $date1 = $start_date;

        if ($teacher == '') {
            $dql = 'SELECT r.id, r.teacher,r.course,r.stream,r.sub_code,r.date1,r.semester, r.reason,r.day, r.period_number, r.room_number, r.section, s.name, u.sub_name FROM ClassCancel r, Employee s,  SubjectMaster u WHERE r.teacher=s.email AND r.sub_code=u.sub_code AND r.date1=:date1';
            $query = $this->getEntityManager()->createQuery($dql)
                ->setParameter('date1', $date1);
            $result = $query->getResult();

            return $result;

        } else {
            $dql = 'SELECT r.id, r.teacher,r.course,r.stream,r.sub_code,r.reason,r.date1,r.semester, r.reason, r.day, r.period_number, r.room_number, r.section, s.name, u.sub_name FROM ClassCancel r, Employee s, SubjectMaster u WHERE r.teacher=s.email AND r.sub_code=u.sub_code AND r.date1=:date1 AND r.teacher=:teacher';
            $query = $this->getEntityManager()->createQuery($dql)
                ->setParameter('teacher', $teacher)
                ->setParameter('date1', $date1);
            $result = $query->getResult();

            return $result;

        }

    }

    public function UpdateRoutine($id, $body)
    {
        $entityManager = $this->getEntityManager();
        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        $teacher = addslashes(strip_tags(trim($body['teacher'])));
        $room_number = addslashes(strip_tags(trim($body['room_number'])));
        $period = addslashes(strip_tags(trim($body['period_number'])));
        $start_time = addslashes(strip_tags(trim($body['start_time'])));
        $end_time = addslashes(strip_tags(trim($body['end_time'])));

        $result = $this->getRoutineForEdit($id);

        if (sizeof($result) == 1) {
            $result[0]->setSession($session);
            $result[0]->setCourse($course);
            $result[0]->setStream($stream);
            $result[0]->setSubCode($sub_code);
            $result[0]->setTeacher($teacher);
            $result[0]->setDay($day);
            $result[0]->setSemester($semester);
            $result[0]->setRoomNumber($room_number);
            $result[0]->setPeriodNumber($period);
            $result[0]->setStartTime($start_time);
            $result[0]->setEndTime($end_time);
            $entityManager->persist($result[0]);
            $entityManager->flush();
            return "Routine Updated Successfully.";
        } else {
            return "Not Found.";
        }

    }

    public function checkCancelClass($body)
    {

        $course = addslashes(strip_tags(trim($body['course'])));
        $stream = addslashes(strip_tags(trim($body['stream'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        $teacher = addslashes(strip_tags(trim($body['teacher'])));
        //$assigned_by = addslashes(strip_tags(trim($body['assigned_by'])));
        $room_number = addslashes(strip_tags(trim($body['room_number'])));
        $period = addslashes(strip_tags(trim($body['period_number'])));
        $start_time = addslashes(strip_tags(trim($body['start_time'])));
        $end_time = addslashes(strip_tags(trim($body['end_time'])));
        $date1 = date('d-m-Y');

        $dql = 'SELECT r FROM ClassCancel r WHERE r.course=:course AND r.stream=:stream AND r.sub_code=:sub_code AND r.semester=:semester AND r.session=:session AND r.day=:day AND r.teacher=:teacher AND r.period_number=:period AND r.start_time=:start_time AND r.end_time=:end_time AND r.date1=:date1';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('course', $course)
            ->setParameter('stream', $stream)
            ->setParameter('sub_code', $sub_code)
            ->setParameter('semester', $semester)
            ->setParameter('session', $session)
            ->setParameter('day', $day)
            ->setParameter('teacher', $teacher)
        //->setParameter('assigned_by', $assigned_by)
            ->setParameter('start_time', $start_time)
            ->setParameter('end_time', $end_time)
            ->setParameter('date1', $date1)
            ->setParameter('period', $period);
        return $query->getResult();
    }

    public function checkCancelClassForStatus($body)
    {

        $course = addslashes(strip_tags(trim($body['course'])));
//        $stream = addslashes(strip_tags(trim($body['stream'])));
        $sub_code = addslashes(strip_tags(trim($body['sub_code'])));
        $semester = addslashes(strip_tags(trim($body['semester'])));
        $section = addslashes(strip_tags(trim($body['section'])));
        $session = addslashes(strip_tags(trim($body['session'])));
        $day = addslashes(strip_tags(trim($body['day'])));
        //$assigned_by = addslashes(strip_tags(trim($body['teacher'])));
        $room_number = addslashes(strip_tags(trim($body['room_number'])));
        $period = addslashes(strip_tags(trim($body['period_number'])));
        $start_time = addslashes(strip_tags(trim($body['start_time'])));
        $end_time = addslashes(strip_tags(trim($body['end_time'])));
        $date1 = date('d-m-Y');

        $dql = 'SELECT r FROM ClassCancel r WHERE r.course=:course  AND r.sub_code=:sub_code AND r.semester=:semester AND r.session=:session AND r.day=:day AND r.period_number=:period AND r.start_time=:start_time AND r.end_time=:end_time AND r.section=:section AND r.date1=:date1';
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('course', $course)
            ->setParameter('sub_code', $sub_code)
            ->setParameter('semester', $semester)
            ->setParameter('session', $session)
            ->setParameter('section', $section)
            ->setParameter('day', $day)
        //->setParameter('assigned_by', $assigned_by)
            ->setParameter('start_time', $start_time)
            ->setParameter('end_time', $end_time)
            ->setParameter('date1', $date1)
            ->setParameter('period', $period);
        return sizeof($query->getResult());
    }

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

    public function serachCancelForEntry($date1, $body)
    {
        $date = date('d-m-Y', strtotime($date1));
        $sub_code = $body['sub_code'];
        $semester = $body['semester'];
        $section = $body['section'];
        $period_number = $body['period_number'];
        if ($section == null) {
            $section = '';
        }
        $dql = 'SELECT COUNT(r.id) as c FROM ClassCancel r WHERE r.date1=:date1 AND r.sub_code=:sub_code AND r.section=:section AND r.semester=:semester AND r.period_number=:period_number';
        $query = $this->getEntityManager()->createQuery($dql)
            ->setParameter('sub_code', $sub_code)
            ->setParameter('semester', $semester)
            ->setParameter('section', $section)
            ->setParameter('period_number', $period_number)
            ->setParameter('date1', $date);
        $result = $query->getResult();
        return $result[0]['c'];
    }
}
