<?php

namespace AppBundle\Entity\Education\Repository;

use AppBundle\Entity\Education\EducationStudent;
use Doctrine\ORM\EntityRepository;

/**
 * Description of EducationStudentRepository
 *
 */
class EducationStudentRepository extends EntityRepository
{

    public function findTeacherByEducationWithoutDateFilter($educationStudent, $education, $teacher)
    {

        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);

        $queryBuilder =
            $this->createQueryBuilder('a');
            $queryBuilder->innerJoin('a.educationCurriculum','eduC')
                ->addSelect('eduC')
                ->andWhere('eduC.education=:education')
                ->innerJoin('a.teachers','tea')
                ->addSelect('tea')
                ->andWhere('tea.id = :teacher')
                ->andWhere('a.id != :educationStudent')
                ->setParameter('education', $education)
                ->setParameter('educationStudent', $educationStudent)
                ->setParameter('teacher',  $teacher)
        ;

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();

        $filter->setParameter('disabledFilter', 0);

        return $results;
    }

    /**
     * @param EducationStudent $educationStudent
     * @return mixed
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findEducationStudentByYear($educationStudent, $year)
    {
        $queryBuilder = $this->createQueryBuilder('education_student');
        $queryBuilder->innerJoin('education_student.educationCurriculum','eduC')
            ->andWhere('eduC.education=:education')
            ->andWhere('education_student.access=:access')
            ->andWhere('education_student.startYear=:year')
            ->setParameter('education', $educationStudent->getEducationCurriculum()->getEducation())
            ->setParameter('access', $educationStudent->getAccess())
            ->setParameter('year', $year)
        ;

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();
        if(count($results) > 0) return $results[0];
        else return null;
    }

    /**
     * @param $startYear
     * @param $organizationId
     * @return array|int|string
     */
    public function getEducationStudentByStartYearAndORganization($startYear, $organizationId){
        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);
        $queryBuilder = $this->createQueryBuilder('education_student');
        $queryBuilder
            ->innerJoin('education_student.access','access')
            ->innerJoin('access.organization','organization')
            ->andWhere('organization.id=:organization')
            ->andWhere('education_student.startYear=:year')
            ->setParameter('organization', $organizationId)
            ->setParameter('year', $startYear)
        ;
        $filter->setParameter('disabledFilter', 0);
        return $queryBuilder->getQuery()->getResult();
    }

    /**
     * @param $pastYear
     * @param $currentYear
     * @param $eucationCurriculum
     * @param $organization
     * @param null $accessesToExclude
     * @return array|int|string
     */
    public function getEducationStudentByEducationCurriculumNextYear($pastYear,$currentYear,$eucationCurriculum,$organization, $accessesToExclude = null){
        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);

        $queryBuilderNotIn = $this->createQueryBuilder('es2');
        $queryBuilderNotIn
            ->select('esly.id')
            ->innerJoin('es2.access', 'acc2')
            ->innerJoin('es2.educationStudentLastYear', 'esly')
            ->andWhere('acc2.id=access.id')
            ->andWhere('es2.startYear=:currentYear');

        $queryBuilder = $this->createQueryBuilder('education_student');
        $queryBuilder
            ->innerJoin('education_student.access','access')
            ->innerJoin('access.organization','organization')
            ->innerJoin('education_student.educationCurriculum','educationCurriculum')
            ->andWhere('educationCurriculum.id=:educationCurriculum')
            ->andWhere('organization.id=:organization')
            ->andWhere(
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->andX(
                        $queryBuilder->expr()->eq('education_student.startYear', ':currentYear')
                    ),
                    $queryBuilder->expr()->andX(
                        $queryBuilder->expr()->eq('education_student.startYear', ':pastYear'),
                        $queryBuilder->expr()->eq('education_student.registeredFollowingYear', ':yes'),
                        $queryBuilder->expr()->notIn('education_student.id', $queryBuilderNotIn->getDQL())
                    )
                )
            )
            ->setParameter(':pastYear', $pastYear)
            ->setParameter(':currentYear', $currentYear)
            ->setParameter(':yes', 1)
            ->setParameter('organization', $organization)
            ->setParameter('educationCurriculum', $eucationCurriculum)
            ->groupBy('access.id')
        ;

        if(!empty($accessesToExclude)){
            $queryBuilder->andWhere($queryBuilder->expr()->notIn('access.id', $accessesToExclude));
        }

        $result =  $queryBuilder->getQuery()->getResult();
        $filter->setParameter('disabledFilter', 0);
        return $result;
    }

    /**
     * @param $currentYear
     * @param $eucationCurriculum
     * @param $organization
     * @param null $accessesToExclude
     * @return array|int|string
     */
    public function getEducationStudentByEducationCurriculumCurrentYear($currentYear,$eucationCurriculum,$organization, $accessesToExclude = null){
        $queryBuilder = $this->createQueryBuilder('education_student');
        $queryBuilder
            ->innerJoin('education_student.access','access')
            ->innerJoin('access.organizationFunction', 'organization_function')
            ->innerJoin('access.organization','organization')
            ->innerJoin('education_student.educationCurriculum','educationCurriculum')
            ->andWhere('educationCurriculum.id=:educationCurriculum')
            ->andWhere('organization.id=:organization')
            ->andWhere('education_student.startYear=:currentYear')
            ->setParameter(':currentYear', $currentYear)
            ->setParameter('organization', $organization)
            ->setParameter('educationCurriculum', $eucationCurriculum);

        if(!empty($accessesToExclude)){
            $queryBuilder->andWhere($queryBuilder->expr()->notIn('access.id', $accessesToExclude));
        }

        $queryBuilder->groupBy('access.id');

        return $queryBuilder->getQuery()->getResult();
    }
}

?>