<?php

namespace App\Repository;

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

/**
 * FinalEgzamRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class FinalEgzamRepository extends EntityRepository
{
    public function getLecturerFinalEgzamHoursGroupByLecturer($filter = null)
    {

        $query = $this->createQueryBuilder('fe')
                ->join('fe.lecturer', 'l')
                ->groupBy('fe.lecturer')
                ->select('SUM('
                . 'COALESCE(fe.prepareTasks,0) + '
                . 'COALESCE(fe.finalCommisionHead,0) + '
                . 'COALESCE(fe.finalCommision,0) '
                . ') as sumEgzam, '
                . 'l.id as lecturerId');

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

        $return = [];
        foreach ($results as $result) {
            $return[$result['lecturerId']] = $result;
        }

        return $return;
    }

    public function getLecturerFinalEgzamHoursActivityPlan(Lecturer $lecturer)
    {

        $query = $this->createQueryBuilder('fe')
                ->join('fe.studiesProgramPlan', 'spp')
                ->join('spp.studiesProgram', 'sp')
                ->join('spp.studiesForm', 'sf')
                ->join('fe.lecturer', 'l')
                ->where('fe.lecturer = :lecturer')
                ->setParameter(':lecturer', $lecturer)
                ->select(
                    'sp.shortName as studiesProgram,'
                    . 'sf.shortName as studiesForm, '
                    . 'spp.semester as semester, '
                    . 'COALESCE(fe.prepareTasks,0) as prepareTasks, '
                    . 'COALESCE(fe.finalCommisionHead ,0) as finalCommisionHead,'
                    . 'COALESCE(fe.finalCommision,0) as finalCommision '
                );

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

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