<?php

namespace App\Repository;

use App\Entity\Lecturer;
use Doctrine\ORM\EntityRepository;

/**
 * ActivitiesPlanRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ActivitiesPlanRepository extends EntityRepository
{
    public function findAll(): array
    {
        return $this->getEntityManager()
            ->createQuery(
                "SELECT la, l, d, ltl, au
                    FROM App:LecturerAgreement la 
                    INNER JOIN la.lecturer l
                    INNER JOIN l.department d                                                                      
                    LEFT JOIN la.activitiesPlans ltl
                    LEFT JOIN ltl.academicUnit au
                    -- where l.id = 2481
                ORDER BY l.name ASC"
            )
            ->getResult();
    }

    public function findLecturerActivities(Lecturer $lecturer)
    {
        return $this->getEntityManager()
            ->createQuery(
                "SELECT ap, la, l, au
                    FROM App:ActivitiesPlan ap
                    JOIN ap.lecturerAgreement la
                    JOIN la.lecturer l
                    JOIN ap.academicUnit au
                WHERE la.lecturer = :lecturer    
                ORDER BY l.name"
            )
            ->setParameter(':lecturer', $lecturer)
            ->getResult();
    }

    public function findLecturerActivitiesSum(Lecturer $lecturer)
    {
            return $this->getEntityManager()
                ->createQuery(
                    "SELECT 
                        SUM(ap.tmmvHours) as tmmvHours, 
                        SUM(ap.meovHours) as meovHours, 
                        SUM(ap.ktvHours) as ktvHours
                    FROM App:ActivitiesPlan ap
                    JOIN ap.lecturerAgreement la
                    JOIN la.lecturer l
                    JOIN ap.academicUnit au
                    WHERE la.lecturer = :lecturer    
                    GROUP BY l.id
                    ORDER BY l.name"
                )
                ->setParameter(':lecturer', $lecturer)
                ->getOneOrNullResult();
    }

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