<?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 FinalEgzamHistoryRepository 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:FinalEgzamHistory t"
                        )
                        ->execute();
    }
}