ap2i/old/Entity/Booking/Repository/EventRepository.php

<?php

/**
 * Created by PhpStorm.
 * User: alex
 * Date: 18/02/16
 * Time: 12:00
 */

namespace AppBundle\Entity\Booking\Repository;

use Doctrine\ORM\EntityRepository;
use Recurr\DateExclusion;
use Recurr\Rule;
use Recurr\Transformer\ArrayTransformer;
use Recurr\Transformer\Constraint\BetweenConstraint;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Dunglas\ApiBundle\Api\ResourceInterface;
use Symfony\Component\HttpFoundation\Request;

class EventRepository extends EntityRepository {
        
    /**
     * Get Attendance records
     * 
     * @param ResourceInterface $resource
     * @param Request $request
     * @param int $firstResult
     * @param mixed int|string $itemsPerPage
     * @return Paginator
     */
    public function getAttendanceRecord(ResourceInterface $resource, Request $request, $firstResult = 1, $itemsPerPage = 20) {
        $queryBuilder = $this->createQueryBuilder('o')
                ->select(''
                        . 'NEW AppBundle\DTO\EventAttendanceRecord('
                        . 'o.id,'
                        . 'o.description,'
                        . 'i.id,'
                        . 'o.url,'
                        . 'o.technicalDescription,'
                        . 'o.visibility,'
                        . 'o.report,'
                        . 'o.datetimeMeeting,'
                        . 'o.locationMeeting,'
                        . 'o.comment,'
                        . '(SELECT group_concat(eu.id) FROM AppBundle:Event\EventUser as eu WHERE eu.event = o.id), '
                        . '(SELECT count(eupp.id) FROM AppBundle:Event\EventUser as eupp WHERE eupp.event = o.id AND eupp.participation = \'PARTICIPATE\'),' // plannedParticipation
                        . '(SELECT count(eupnp.id) FROM AppBundle:Event\EventUser as eupnp WHERE eupnp.event = o.id AND eupnp.participation = \'NOT_PARTICIPATE\'),' // plannedNotParticipation
                        . '(SELECT count(eunp.id) FROM AppBundle:Event\EventUser as eunp WHERE eunp.event = o.id AND eunp.participation = \'NOT_RESPONSE\'),' // withoutResponse
                        . '(SELECT count(eua.id) FROM AppBundle:Event\EventUser as eua WHERE eua.event = o.id AND eua.attendance = 1)' // attendance
                        . ')'
                )
                ->leftJoin('o.image', 'i')
        ;

        foreach ($resource->getFilters() as $filter) {
            $filter->apply($resource, $queryBuilder, $request);
        }

        // Item per page should be equal to 'ALL'
        if (is_int($itemsPerPage)) {
            $queryBuilder->setFirstResult($firstResult);
            $queryBuilder->setMaxResults($itemsPerPage);
        }

        $paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($queryBuilder);
        $paginator->setUseOutputWalkers(false);
        return $paginator;
    }

    public function getOrganizationEvent($organization){
        $datetime = new \DateTime();
        $today = $datetime->format('Y-m-d');

        $qb = $this->createQueryBuilder('a');

        $qb
            ->leftJoin('a.eventRecur', 'er')
            ->where('a.organization = :organization')
            ->andWhere('a.datetimeStart >= :today OR er.datetimeEnd >= :today')
            ->setParameter('today', $today)
            ->setParameter('organization', $organization);

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