<?php

namespace App\Repository\Poll;

use Doctrine\ORM\EntityRepository;

/**
 * PollRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PollRepository extends EntityRepository
{
    public function findByLecturerPage($lecturerId, $page): array
    {
        $results = $this->getEntityManager()
                ->createQuery(
                    "SELECT p, pg, ar, af
                                    FROM App:Poll\Poll p                       
                                    JOIN p.pollGroup pg       
                                    JOIN p.answerRows ar            
                                    JOIN p.answerFields af            
                         WHERE pg.page = $page and ar.lecturer = $lecturerId
                                "
                )
                ->getResult();


        $return = [];
        foreach ($results as $result) {
            $return[$result->getId()] = $result->getAnswerRows();
        }

        return $return;
    }

    public function findAllArrayByPoll($lecturer = 0)
    {

        if (empty($lecturer)) {
            $sql = "SELECT p, pg, ar, l
                                    FROM App:Poll\Poll p                       
                                    JOIN p.pollGroup pg       
                                    JOIN p.answerRows ar            
                                    JOIN ar.lecturer l  
                                    JOIN l.department d
                                    JOIN d.academicUnit au                        
                                ";
        } else {
            $sql = "SELECT p, pg, ar, l
                                    FROM App:Poll\Poll p                       
                                    JOIN p.pollGroup pg       
                                    JOIN p.answerRows ar            
                                    JOIN ar.lecturer l  
                                    JOIN l.department d
                                    JOIN d.academicUnit au    
                                WHERE l.id = '$lecturer'    
                                ";
        }

        $results = $this->getEntityManager()
                ->createQuery(
                    $sql
                )
                ->getResult();

        $return = [];
        foreach ($results as $result) {
            $return[$result->getId()] = $result->getAnswerRows();
        }

        return $return;
    }

    public function findTmmvByLecturerPage($lecturerId, $page): array
    {

        $results = $this->getEntityManager()
            ->createQuery("
                SELECT 
                    p, pg,af, tmmv, tmmvLect
                FROM App:Poll\Poll p                       
                JOIN p.pollGroup pg
                JOIN p.answerFields af
                JOIN af.tmmvs tmmv                 
                JOIN tmmv.TmmvLecturers tmmvLect  
                JOIN tmmvLect.lecturer l                 
                WHERE 
                    pg.page = $page 
                    AND l.id = $lecturerId
                    AND tmmvLect.hoursFact > 0
                ORDER BY tmmvLect.poll ASC
            ")
            ->getResult();

        $return = [];
        foreach ($results as $poll) {
            $pollId = $poll->getId();
            $answerFields = $poll->getAnswerFields();
            foreach ($answerFields as $answerField) {
                $tmmvs = $answerField->getTmmvs();
                foreach ($tmmvs as $tmmv) {
                    $return[$pollId][] = $tmmv->getTmmvLecturers();
                }
            }
        }

//        return [];
        return $return;
    }

    public function findMeovByLecturerPage($lecturerId, $page): array
    {

        $results = $this->getEntityManager()
            ->createQuery("
                SELECT 
                    p, pg,af, meov, meovLect
                FROM App:Poll\Poll p                       
                JOIN p.pollGroup pg
                JOIN p.answerFields af
                JOIN af.meovs meov                 
                JOIN meov.MeovLecturers meovLect  
                JOIN meovLect.lecturer l                 
                WHERE 
                    pg.page = $page 
                    AND l.id = $lecturerId
                    AND meovLect.hoursFact > 0
                ORDER BY meovLect.poll ASC
            ")
            ->getResult();

        $return = [];
        foreach ($results as $poll) {
            $pollId = $poll->getId();
            $answerFields = $poll->getAnswerFields();
            foreach ($answerFields as $answerField) {
                $meovs = $answerField->getMeovs();
                foreach ($meovs as $meov) {
                    $return[$pollId][] = $meov->getMeovLecturers();
                }
            }
        }

        return $return;
    }

    public function findKtvByLecturerPage($lecturerId, $page): array
    {

        $results = $this->getEntityManager()
            ->createQuery("
                SELECT 
                    p, pg,af, ktv, ktvLect
                FROM App:Poll\Poll p                       
                JOIN p.pollGroup pg
                JOIN p.answerFields af
                JOIN af.ktvs ktv                 
                JOIN ktv.KtvLecturers ktvLect  
                JOIN ktvLect.lecturer l                 
                WHERE 
                    pg.page = $page 
                    AND l.id = $lecturerId
                    AND ktvLect.hoursFact > 0
                ORDER BY ktvLect.poll ASC
            ")
            ->getResult();

        $return = [];
        foreach ($results as $poll) {
            $pollId = $poll->getId();
            $answerFields = $poll->getAnswerFields();
            foreach ($answerFields as $answerField) {
                $tmmvs = $answerField->getKtvs();
                foreach ($tmmvs as $tmmv) {
                    $return[$pollId][] = $tmmv->getKtvLecturers();
                }
            }
        }

//        return [];
        return $return;
    }
}