<?php

use Doctrine\ORM\EntityRepository;

class FeedbacksRepository extends EntityRepository
{

    public function addFeedbacks($body)
    {
        $entityManager = $this->getEntityManager();

        $category_id = addslashes(strip_tags(trim($body['id'])));
        $type = addslashes(strip_tags(trim($body['type'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();
        $response = $body['answers'];
        $comment = addslashes(strip_tags(trim($body['comment_text'])));
        $time_stamp = date('d-m-Y H:i:s');

        if ($session_id !== false) {

            if ($this->checkFeedbacks($body)) {
                $sm = new Feedbacks();
                $sm->setCategoryId($category_id);
                $sm->setEmail($email);
                $sm->setSessionId($session_id);
                $sm->setResponses($response);
                $sm->setComment($comment);
                $sm->setTimeStamp($time_stamp);
                $sm->setType($type);
                $entityManager->persist($sm);
                $entityManager->flush();
                return 'Successfully Submitted';
            } else {
                return 'Feedback already submitted';
            }

        } else {
            return 'No Session is activated. Please contact your administrator';
        }

    }

    private function checkFeedbacks($body)
    {
        $entityManager = $this->getEntityManager();
        $category_id = addslashes(strip_tags(trim($body['id'])));
        $type = addslashes(strip_tags(trim($body['type'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();

        $dql = "SELECT r FROM Feedbacks r WHERE r.category_id=:category_id AND r.type=:ty AND r.email=:email AND r.session_id=:session_id";
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('category_id', $category_id)
            ->setParameter('email', $email)
            ->setParameter('ty', $type)
            ->setParameter('session_id', $session_id);
        $results = $query->getResult();

        if (sizeof($results) == 0) {
            return true;
        } else {
            return false;
        }

    }

    public function getFeedbackPercentage($email)
    {
        $total = 3;

        $entityManager = $this->getEntityManager();
        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();

        $dql = "SELECT r From Feedbacks r where r.session_id=:id AND r.email=:email";
        $query = $this->getEntityManager()
            ->createQuery($dql)
            ->setParameter('id', $session_id)
            ->setParameter('email', $email);
        $result = $query->getResult();

        $count = sizeof($result);

        $percentage = ($count / $total) * 100;
        return (int) $percentage;
    }

    public function getFeedbacks()
    {
        $dql = "SELECT r.id,r.question_id,r.email,r.response,s.title_en,s.title_as,s.question_options,t.by_whom,t.on_whom,t.name_en,t.name_as,t.on_teacher,u.session_name From Feedbacks r,FeedbackQuestion s,FeedbackCategory t,FeedbackSession u where r.question_id=s.id AND s.category_id=t.id AND r.session_id=u.id";
        $query = $this->getEntityManager()->createQuery($dql);
        return $query->getResult();
    }

    public function getFeedbackSingle($id)
    {
        $dql = "SELECT r.id,r.question_id,r.email,r.response,s.title_en,s.title_as,s.question_options,t.by_whom,t.on_whom,t.name_en,t.name_as,t.on_teacher,u.session_name From Feedbacks r,FeedbackQuestion s,FeedbackCategory t,FeedbackSession u where r.question_id=s.id AND s.category_id=t.id AND r.session_id=u.id AND r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    private function getFeedbackForEdit($id)
    {
        $dql = "SELECT r from Feedbacks r where r.id=:id";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('id', $id);
        return $query->getResult();
    }

    public function updateFeedbackSession($id, $body)
    {
        $entityManager = $this->getEntityManager();

        $question_id = addslashes(strip_tags(trim($body['question_id'])));
        $email = addslashes(strip_tags(trim($body['email'])));
        $session_id = addslashes(strip_tags(trim($body['session_id'])));
        $response = addslashes(strip_tags(trim($body['response'])));

        $result = $this->getFeedbackForEdit($id);
        if (sizeof($result) == 1) {

            $result[0]->setQuestionId($question_id);
            $result[0]->setEmail($email);
            $result[0]->setSessionId($session_id);
            $result[0]->setResponse($response);

            $entityManager->persist($result[0]);
            $entityManager->flush();
            return "success";
        } else {
            return "Not Found.";
        }

    }

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

    public function getFeedbackCount()
    {
        $entityManager = $this->getEntityManager();

        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();

        $dql = "SELECT count(r.id) as count FROM Feedbacks r WHERE r.session_id=:session_id AND r.type=0";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $result = $query->getResult();

        $b['student_count'] = $result[0]['count'];

        $dql1 = "SELECT count(r.id) as count FROM Feedbacks r WHERE r.session_id=:session_id AND r.type=1";
        $query1 = $this->getEntityManager()->createQuery($dql1)->setParameter('session_id', $session_id);
        $result1 = $query1->getResult();

        $b['teacher_count'] = $result1[0]['count'];

        return $b;

    }

    public function getFeedbackStudents()
    {
        $entityManager = $this->getEntityManager();

        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();

        $dql = "SELECT MIN(s.name) as name,MIN(s.roll_no) as roll_no,MIN(s.course) as course,MIN(s.stream) as stream,MIN(s.course_type) as course_type,MIN(s.semester) as semester,MIN(s.email) as email,MIN(r.email) as id_email, MAX(r.time_stamp) as time_stamp,count(r.id) as count_feedback FROM Feedbacks r, Student s WHERE r.session_id=:session_id AND r.type=0 AND r.email=s.email GROUP BY r.email";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $result = $query->getResult();
        return $result;

    }

    public function getFeedbackTeachers()
    {
        $entityManager = $this->getEntityManager();

        $session_id = $entityManager->getRepository('FeedbackSession')->getActiveFeedbackSession();

        $dql = "SELECT s.name,s.email,MIN(r.email) as id_email, MAX(r.time_stamp) as time_stamp,count(r.id) as count_feedback, d.dept_name FROM Feedbacks r, Employee s, Department d WHERE r.session_id=:session_id AND s.dept_code=d.dept_code AND r.type=1 AND r.email=s.email GROUP BY r.email";
        $query = $this->getEntityManager()->createQuery($dql)->setParameter('session_id', $session_id);
        $result = $query->getResult();
        return $result;

    }

    public function getFeedbackAnalysisOfStudent($session_id)
    {
        $entityManager = $this->getEntityManager();

        $sql = "SELECT r.id,r.title_en,r.category_id,JSON_EXTRACT(r.question_options,'$[*].value') as question_options,s.name_en,s.on_teacher FROM feedback_question r, feedback_category s WHERE r.category_id=s.id";
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();

        $index = 0;
        foreach ($result as $r) {

            $qo = json_decode($r['question_options'], true);
            $result[$index]['question_options'] = $qo;

            $result[$index]['data'] = $this->getFeedbackData($session_id, $r['id'], $qo, $r['on_teacher']);

            $index++;
        }

        return $result;

    }

    private function getFeedbackData($session_id, $question_id, $options, $on_teacher)
    {
        $entityManager = $this->getEntityManager();

        $sql = "SELECT r.responses FROM feedbacks r WHERE r.session_id=:session_id AND json_contains(r.responses->'$[*].question_id',json_array(:question_id))";
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->bindParam(':session_id', $session_id, PDO::PARAM_INT);
        $stmt->bindParam(':question_id', $question_id, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetchAll();

        $f_data = [];
        $f_teachers = [];

        foreach ($result as $r) {
            $res = json_decode($r['responses'], true);
            foreach ($res as $re) {
                if ($re['question_id'] == $question_id) {
                    array_push($f_data, $re);
                }
                if ($re['question_id'] == $question_id && $re['teacher_id'] != 0) {
                    if (!in_array($re['teacher_id'], $f_teachers)) {
                        array_push($f_teachers, $re['teacher_id']);
                    }

                }
            }
        }

        // now we will calculate nos against value or label

        $op_value = [];

        $final_array = [];

        if ($on_teacher == 0) {
            foreach ($options as $op) {
                $count = 0;
                foreach ($f_data as $fd) {
                    if ($op == $fd['value'] && $fd['teacher_id'] == 0) {
                        $count++;
                    }
                }

                $d = [];
                $d['value'] = $op;
                $d['count'] = $count;
                array_push($op_value, $d);
            }
            $f_a['type'] = 0;
            $f_a['teacher'] = 0;
            $f_a['teacher_name'] = '';
            $f_a['data'] = $op_value;
            array_push($final_array,$f_a);
        } else {
            foreach ($f_teachers as $ft) {
                $op_value = [];

                foreach ($options as $op) {
                    $count = 0;
                    foreach ($f_data as $fd) {
                        if ($op == $fd['value'] && $fd['teacher_id'] == $ft) {
                            $count++;
                        }
                    }

                    $d = [];
                    $d['value'] = $op;
                    $d['count'] = $count;
                    array_push($op_value, $d);
                }
                $f_a = [];
                $f_a['type'] = 1;
                $f_a['teacher'] = $ft;
                $f_a['teacher_name'] = $entityManager->getRepository('Employee')->getNameById($ft);
                $f_a['data'] = $op_value;
                array_push($final_array,$f_a);

            }
        }

        return $final_array;

    }
}
