<?php

namespace App\Repository;

use Doctrine\ORM\EntityRepository;

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

    public function getAllByHash()
    {
        $results = $this->getEntityManager()
                ->createQuery(
                    "SELECT spp 
                                    FROM App:StudiesProgramPlan spp
                                    JOIN spp.studiesProgram sp
                                ORDER BY spp.studiesProgram ASC"
                )
                ->getResult();
        $return = [];
        foreach ($results as $result) {
            $studiesProgram = $result->getStudiesProgram()->getShortName();
            $studiesForm = $result->getStudiesForm()->getShortName();
            $semester = $result->getSemester();
            $module = $result->getStudiesModule()->getShortName();
            $return[md5($studiesProgram . "_" . $studiesForm . "_" . $semester . "_" . $module)] = $result;
        }

        return $return;
    }

    public function getAllByProgramFormCourse()
    {
        $results = $this->getEntityManager()
                ->createQuery(
                    "SELECT spp, sp
                                    FROM App:StudiesProgramPlan spp
                                    JOIN spp.studiesProgram sp
                                ORDER BY spp.studiesProgram ASC"
                )
                ->getResult();
        $return = [];
        foreach ($results as $result) {
            $studiesProgram = $result->getStudiesProgram()->getShortName();
            $studiesForm = $result->getStudiesForm()->getShortName();
            $semester = $result->getSemester();

            $course = round($semester / 2);

            $return[$studiesProgram . "_" . $studiesForm . "_" . $course][$result->getId()] = $result;
        }

        return $return;
    }

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

    public function findAllReport($filter = null)
    {

        $query = $this->createQueryBuilder('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('spp.finalEgzams', 'fe')
            ->leftJoin('spp.finalProjects', 'fp')
            ->select('spp, sp, d, smt, sf, sm, tl, au, fe, fp')
            ->addOrderBy('spp.id', 'ASC')
        ;

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

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

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