<?php

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

/**
 * AcademicGroupPlanRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class AcademicGroupPlanRepository extends EntityRepository
{
    public function findAll(): array
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "SELECT agp, spp, sp 
                                    FROM App:AcademicGroupPlan agp
                                    JOIN agp.studiesProgramPlan spp
                                    JOIN spp.studiesProgram sp
                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
                        )
                        ->getResult();
    }

    public function findAllReport($filter = null)
    {
//        return $this->getEntityManager()
//                        ->createQuery(
//                                "SELECT agp, spp, sp, lp
//                                    FROM App:AcademicGroupPlan agp
//                                    JOIN agp.lecturerPlans lp
//                                    JOIN agp.studiesProgramPlan spp
//                                    JOIN spp.studiesProgram sp
//                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
//                        )
//                        ->getResult();

        $query = $this->createQueryBuilder('agp')
                ->join('agp.academicGroup', 'ag')
                ->join('agp.studiesProgramPlan', 'spp')
                ->join('spp.studiesProgram', 'sp')
                ->join('sp.department', 'd')
                ->leftJoin('spp.studiesModuleType', 'smt')
                ->leftJoin('spp.studiesForm', 'sf')
                ->leftJoin('spp.studiesModule', 'sm')
                ->leftJoin('spp.teachingLanguage', 'tl')
                ->leftJoin('d.academicUnit', 'au')
                ->leftJoin('agp.lecturerPlans', 'lp')
                ->leftJoin('lp.academicGroupPlans', 'agp2')
                ->leftJoin('lp.lecturer', 'l')
                ->leftJoin('lp.lecturerPosition', 'lpos')
                ->leftJoin('l.department', 'ld')
                ->select('agp, lp, agp2, ag, spp, smt, sf, sm, l, tl, lpos, sp, d, au, ld')
                ->addOrderBy('agp.academicGroup', 'ASC')
                ->addOrderBy('agp.studiesProgramPlan', 'ASC')

//                ->setMaxResults(400)
        ;

        if (!empty($filter['department_id'])) {
            $query
                    ->andWhere('d.id = :did')
                    ->setParameter(':did', $filter['department_id']);
        }

        if (!empty($filter['academic_unit_id'])) {
            $query
                    ->andWhere('au.id = :aid')
                    ->setParameter(':aid', $filter['academic_unit_id']);
        }

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

    public function findAllBySppAgId()
    {
        $results = $this->getEntityManager()
                ->createQuery(
                    "SELECT agp 
                                    FROM App:AcademicGroupPlan agp
                                    JOIN agp.studiesProgramPlan spp
                                    JOIN spp.studiesProgram sp
                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
                )
                ->getResult();

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

        return $return;
    }

    public function getAllPlansById()
    {
        $results = $this->getEntityManager()
                ->createQuery(
                    "SELECT agp 
                                    FROM App:AcademicGroupPlan agp
                                    JOIN agp.studiesProgramPlan spp
                                    JOIN spp.studiesProgram sp
                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
                )
                ->getResult();

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

        return $return;
    }

    public function getAllPlansByIdQueryBuilder($filter = [])
    {

        $query = $this->createQueryBuilder('agp')
                ->join('agp.studiesProgramPlan', 'spp')
                ->join('spp.studiesProgram', 'sp')
                ->leftJoin('spp.studiesModule', 'sm')
                ->leftJoin('spp.studiesModuleType', 'smt')
                ->leftJoin('spp.studiesForm', 'sf')
                ->leftJoin('spp.teachingLanguage', 'tl')
                ->join('sp.department', 'd')
                ->join('d.academicUnit', 'au')
                ->select('agp, spp, sp, sm, smt, sf, tl, d, au')
                ->addOrderBy('agp.academicGroup', 'ASC')
                ->addOrderBy('agp.studiesProgramPlan', 'ASC');

        if (!empty($filter->get('academicUnit'))) {
            $query->andWhere('au.id = :auid');
            $query->setParameter(':auid', $filter->get('academicUnit'));
        }

        if (!empty($filter->get('department'))) {
            $query->andWhere('d.id = :did');
            $query->setParameter(':did', $filter->get('department'));
        }

        if (!empty($filter->get('studiesProgram'))) {
            $query->andWhere('sp.id = :spid');
            $query->setParameter(':spid', $filter->get('studiesProgram'));
        }

        if (!empty($filter->get('academicGroup'))) {
            $query->andWhere('agp.academicGroup = :agid');
            $query->setParameter(':agid', $filter->get('academicGroup'));
        }

        if (!empty($filter->get('semester'))) {
            $query->andWhere('spp.semester = :agsem');
            $query->setParameter(':agsem', $filter->get('semester'));
        }

        if (!empty($filter->get('studiesModule'))) {
            $query->andWhere('spp.studiesModule = :sppsm');
            $query->setParameter(':sppsm', $filter->get('studiesModule'));
        }

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

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

        return $return;
    }

    public function getOtherGroupsPlansById($academicGroupsIds)
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "SELECT agp 
                                    FROM App:AcademicGroupPlan agp
                                    WHERE agp.academicGroup not in ('" . implode("','", $academicGroupsIds) . "')
                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
                        )
                        ->getResult();

//        return $this->getEntityManager()
//                        ->createQuery(
//                                "SELECT agp
//                                    FROM App:AcademicGroupPlan agp
//                                    JOIN agp.studiesProgramPlan spp
//                                    JOIN spp.studiesProgram sp
//                                    WHERE agp.academicGroup not in ('" . implode("','", $academicGroupsIds) . "')
//                                ORDER BY agp.academicGroup, agp.studiesProgramPlan"
//                        )
//                        ->getResult();
    }

    public function getPlansList()
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "SELECT 
                                        agp.id, 
                                        sum(lp.theoryHours) as theoryHours, 
                                        sum(lp.studentCount) as studentCount, 
                                        sum(lp.practicGroup) as practicGroup, 
                                        sum(lp.practicGroup1) as practicGroup1, 
                                        sum(lp.practicGroup2) as practicGroup2, 
                                        sum(lp.consultationHours) as consultationHours, 
                                        sum(lp.egzamHours) as egzamHours 
                                FROM App:AcademicGroupPlan agp INDEX BY agp.id
                                LEFT JOIN agp.lecturerPlans lp
                                GROUP BY agp
                                "
                        )
                        ->getResult();
    }

    public function findEgzamType()
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "SELECT 
                                    agp, spp, sp, smt, sm, ag, sf
                                    FROM App:AcademicGroupPlan agp
                                    INNER JOIN agp.studiesProgramPlan spp
                                    INNER JOIN spp.studiesProgram sp
                                    LEFT JOIN spp.studiesModule sm
                                    LEFT JOIN spp.studiesForm sf
                                    INNER JOIN spp.studiesModuleType smt
                                    INNER JOIN agp.academicGroup ag
                                WHERE smt.shortName = :shortName1 OR  smt.shortName = :shortName2
                                "
                        )
                        ->setParameter(":shortName1", "SK-BD")
                        ->setParameter(":shortName2", "SK-KE")
                        ->getResult();
    }

    public function findEgzamTypeSpp()
    {
        return $this
            ->getEntityManager()
            ->createQuery(
                "SELECT 
                    spp, sp, smt, sm, sf, fp, fe
                    FROM App:StudiesProgramPlan spp
                    INNER JOIN spp.studiesProgram sp
                    INNER JOIN sp.department d
                    LEFT JOIN spp.studiesModule sm
                    LEFT JOIN spp.studiesForm sf
                    LEFT JOIN spp.finalProjects fp
                    LEFT JOIN spp.finalEgzams fe
                    INNER JOIN spp.studiesModuleType smt
                WHERE smt.shortName = :shortName1 OR smt.shortName = :shortName2"
            )
            ->setParameter(":shortName1", "SK-BD")
            ->setParameter(":shortName2", "SK-KE")
            ->getResult()
        ;
    }

    public function findDepartmentLecturerModules()
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "SELECT agp, lp
                                    FROM App:AcademicGroupPlan agp
                                    INNER JOIN agp.lecturerPlans lp
                                ORDER BY agp.academicGroup, agp.studiesProgramPlan
                                "
                        )
                        ->getResult();
    }

    public function deleteAll()
    {
        return $this->getEntityManager()
                        ->createQuery(
                            "DELETE App:AcademicGroupPlan t"
                        )
                        ->execute();
    }
}