<?php

namespace Admin\AdminBundle\Repository;

use Admin\AdminBundle\Entity\User;
use Doctrine\ORM\EntityRepository;

class InterventionRepository extends EntityRepository
{
    /**
     * @param User $user
     * @return array
     */
    public function findBySupervisor(User $user)
    {
        $qb = $this->createQueryBuilder('i');

        $qb->join('i.user', 'u', 'WITH', 'u = i.user');
        $qb->join('u.managerEmployee', 'me');
        $qb->join('me.manager', 'm', 'WITH', 'm = me.manager');
        $qb->where('m.supervisor = :supervisor');
        $qb->setParameter('supervisor', $user);

        $qb->orWhere('me.manager = :manager');
        $qb->setParameter('manager', $user->getManagerEmployee()->getManager());

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

    public function interventionTraining($actuallyDate, $date30)
    {

        $qb = $this->createQueryBuilder('i');
        $qb->select(  'c.name as companyName', 'c.id as companyId', 'i.name as interventionName', 'u.lastname', 'cet2.name as trainingName', 'i.id as interventionId', 'ce.id as companyEmployeeId', 'c.isFrench as cfrench', 'm.name as managerName', 'i.startDate as iStart', 'i.endDate as iEnd', 'cet2.id as trainingId');
//        $qb->select(  'c.name as companyName', 'c.id as companyId', 'i.name as interventionName', 'u.lastname', 'cet2.name as trainingName', 'i.id as interventionId', 'ce.id as companyEmployeeId');
        $qb->innerJoin('i.interventionCompanyEmployees', 'ice')
            ->innerJoin('ice.companyEmployee', 'ce')
            ->innerJoin('ce.company', 'c')
            ->innerJoin('ce.companyEmployeeTrainings', 'cet')
            ->innerJoin('cet.training', 'cet2')
            ->innerJoin('ce.user', 'u')
            ->innerJoin('cet.trainingCategory', 'tec')
            ->leftJoin('u.managerEmployee', 'um')
            ->leftJoin('um.manager', 'm')

            ->where('cet.endDate > :endDate')
            ->setParameter('endDate', $actuallyDate)
            ->orWhere('cet.endDate > :endDate')
            ->setParameter('endDate', $date30)

            ->andWhere('i.startDate > :startDate')
            ->setParameter('startDate', $actuallyDate)

            ->andWhere('tec.name = :category')
            ->setParameter('category', 'Obligatoire');

        return $qb->getQuery()->getResult();


    }
}