<?php

namespace Ig\IgReservations\Domain\Repository;

use Ig\IgReservations\Domain\Model\Event;
use Ig\IgReservations\Domain\Model\Reminder;
use Ig\IgReservations\Domain\Model\Status;
use Ig\IgReservations\Service\UserService;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper;
use TYPO3\CMS\Extbase\Persistence\Generic\PersistenceManager;
use TYPO3\CMS\Extbase\Persistence\Generic\Query;
use TYPO3\CMS\Extbase\Utility\DebuggerUtility;

/**
 * The repository for Events
 */
class EventRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
    /**
     * ConnectionPool
     *
     * @var ConnectionPool
     */
    protected $connectionPool = null;

    /**
     * DataMapper
     *
     * @var DataMapper
     */
    protected $dataMapper = null;

    /**
     * @var \Ig\IgReservations\Domain\Service\UserService
     */
    protected UserService $userService;

    /**
     * @var array
     */
    protected $defaultOrderings = [
        'startDate' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING,
        'startTime' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING,
        'timeslot.sorting' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING,
    ];

    
    public function injectDataMapper(DataMapper $dataMapper)
    {
        $this->dataMapper = $dataMapper;
    }

    public function injectUserService(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function initializeObject()
    {
        $querySettings = $this->createQuery()
->getQuerySettings();
        $querySettings->setRespectSysLanguage(false);
        //$querySettings->setLanguageOverlayMode(false);
        $this->setDefaultQuerySettings($querySettings);
        $this->connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
    }

    public function findUnapproved(array $search = [])
    {
        $query = $this->createQuery();

        if ((isset($search['mode']) && $search['mode'] == 'unapproved') || empty($search)) {
            $now = new \DateTime();
            $now->setTime(12, 0, 0);

            $query->matching($query->logicalAnd(
                $query->greaterThanOrEqual('startDate', $now->format('Y-m-d')),
                $query->equals('teammembers', 0),
                $query->equals('status.needsAction', 1)
            ));
        } elseif (isset($search['mode']) && $search['mode'] == 'approved') {
            $now = new \DateTime();
            $now->setTime(12, 0, 0);

            $query->matching($query->logicalAnd($query->equals('status.needsAction', 0)));
            $query->setOrderings([
                'startDate' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING,
                'startTime' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING,
                'timeslot.sorting' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING,
            ]);
        }

        return $query->execute();
    }

    /**
     * search for Alarm Command
     **/
    public function findForAlarm($periode = '-1 week')
    {
        $now = new \DateTime();
        $now->setTime(12, 0, 0);

        $aWeekAgo = new \DateTime();
        $aWeekAgo->setTime(12, 0, 0);
        $aWeekAgo->modify($periode);

        $query = $this->createQuery();
        $query->matching(
            $query->logicalAnd(
                $query->greaterThanOrEqual('startDate', $now->format('Y-m-d')),
                $query->lessThanOrEqual('crdate', $aWeekAgo->getTimestamp()),
                $query->equals('teammembers', 0),
                $query->equals('status.needsAction', 1)
            )
        );

        return $query->execute();
    }

    /**
     * search for Alarm Command
     **/
    public function findForReminder(Reminder $reminder, array $confirmedStatusUids = [])
    {
        $timeModifier = $reminder->getTimeModifier();
        //$calendar = $reminder->getCalendar();
        $now = new \DateTime();

        $limit = new \DateTime();
        $limit->modify($timeModifier);

        $query = $this->createQuery();
        $query->getQuerySettings()->setRespectStoragePage(false);

        $constraints = [
            $query->logicalOr(
                $query->logicalAnd([
                    $query->equals('startDate', $now->format('Y-m-d')),
                    $query->greaterThanOrEqual('startTime', $now->format('H:i:s')),
                ]),

                // Case 2: Events starting tomorrow or the day after, irrespective of time
                $query->logicalAnd([
                    $query->greaterThan('startDate', $now->format('Y-m-d')),
                    $query->lessThanOrEqual('startDate', $limit->format('Y-m-d')),
                ])
            )
        ];

        if(!empty($confirmedStatusUids)) {
            $constraints[] = $query->in('status', $confirmedStatusUids);
        }

        $query->matching($query->logicalAnd(...$constraints));

        return $query->execute();
    }


    public function resolveExistentEvent(\Ig\IgReservations\Domain\Model\Event $event)
    {
        $calendar = $event->getCalendar();
        // Event exists
        $oldEvent = $this->findOneByDateAndTimeslotAndCalendar(
            $event->getStartDate(),
            $event->getTimeslot()->getUid(),
            $calendar->getUid()
        );
        if (is_object($oldEvent)) {
            return $oldEvent;
        }
        $this->secureAdd($event);
        return $event;
    }
    
    public function findOneByDateAndTimeslotAndCalendar(\DateTime $date, int $timeslotUid, int $calendarUid)
    {
        //$conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_igreservations_domain_model_event');
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        //$sql = 'SELECT * FROM tx_igreservations_domain_model_event WHERE start_date = \'' . $date->format('Y-m-d') . '\' AND uid IN (SELECT uid_local FROM tx_igreservations_event_timeslot_mm WHERE uid_foreign= ' . $timeslotUid . ') AND calendar=' . $calendarUid . ' AND hidden = 0';
        $res = $queryBuilder->selectLiteral('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->eq('calendar', $queryBuilder->createNamedParameter($calendarUid)),
                                $queryBuilder->expr()
->eq('start_date', $queryBuilder->createNamedParameter($date->format('Y-m-d'))),
                                $queryBuilder->expr()
->eq('timeslot', $queryBuilder->createNamedParameter($timeslotUid))
                            )
                            ->executeQuery()
                            ->fetchAssociative();
        /*
        $stmt = $conn->prepare($sql);
    	$stmt->execute( [] );
        $res = $stmt->fetch();
        */
        if ($res) {
            $events = $this->dataMapper->map(Event::class, [$res]);
            return $events[0];
        }

        return null;
    }
    
    public function findIsFree(\Ig\IgReservations\Domain\Model\Event $event)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        $startDate = $event->getStartDate();
        $endDate = $event->getEndDate();
        $startTime = $event->getStartTime();
        $endTime = $event->getEndTime();
        $calendarUid = $event->getCalendar()
->getUid();
        $acl = $event->getCalendar()
->getAcl();

        if ($calendarUid <= 0) {
            return false;
        }
        $queryBuilder->select('*')
            ->from('tx_igreservations_domain_model_event')
            ->where(
                $queryBuilder->expr()
->eq('calendar', $calendarUid),
                $queryBuilder->expr()
->orX(
    $queryBuilder->expr()
->lt('start_date', $queryBuilder->createNamedParameter($endDate->format('Y-m-d'))),
    $queryBuilder->expr()
->andX(
    $queryBuilder->expr()
->eq('start_date', $queryBuilder->createNamedParameter($endDate->format('Y-m-d'))),
    $queryBuilder->expr()
->lt('start_time', $queryBuilder->createNamedParameter($endTime)),
)
),
                $queryBuilder->expr()
->orX(
    $queryBuilder->expr()
->gt('end_date', $queryBuilder->createNamedParameter($startDate->format('Y-m-d'))),
    $queryBuilder->expr()
->andX(
    $queryBuilder->expr()
->eq('end_date', $queryBuilder->createNamedParameter($startDate->format('Y-m-d'))),
    $queryBuilder->expr()
->gt('end_time', $queryBuilder->createNamedParameter($startTime)),
)
)
            );

        $user = null;

        if ($acl && $acl->getIsAdmin() && $event->getOwner()) {
            $user = $event->getOwner();
        } elseif ($acl && $acl->hasPermission('team') && $acl->hasPermission(
            'teammemberShowMyEvents'
        ) && $this->userService->isLoggedIn()) {
            $user = $this->userService->getUser();
        }

        if ($user) {
            $queryBuilder->andWhere(
                $queryBuilder->expr()
->eq('owner', $queryBuilder->createNamedParameter($user->getUid(), \PDO::PARAM_INT))
            );
        }

        $res = $queryBuilder->executeQuery()
->fetchAllAssociative();
        return count($res) == 0;
        /*        echo('ANZ=' . count($res));
        var_dump($startDate,$startTime, $endDate,$endTime );
        var_dump($res);
        exit(0);
        */
    }

    public function findForAlarmAdmin($periode = '+10 days')
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );

        $now = new \DateTime();
        $now->setTime(12, 0, 0);

        $inTenDays = clone $now;
        $inTenDays->setTime(12, 0, 0);
        $inTenDays->modify($periode);

        $res = $queryBuilder->select('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->gte('start_date', $queryBuilder->createNamedParameter($now->format('Y-m-d'))),
                                $queryBuilder->expr()
->lte('start_date', $queryBuilder->createNamedParameter($inTenDays->format('Y-m-d'))),
                                $queryBuilder->expr()
->eq('teammembers', 0),
                                $queryBuilder->expr()
->gt('address', 0)
                            )
                            ->orderBy('create_date', 'ASC')
                            ->executeQuery()
                            ->fetchAllAssociative();

        return $this->dataMapper->map(Event::class, $res);
    }
    public function secureAdd(Event $event): bool
    {
        $calendar = $event->getCalendar();

        if ($calendar->getIsDateTimeMode()) {
            if (!$this->findIsFree($event)) {
                return false;
            }
        }

        $this->add($event);
        GeneralUtility::makeInstance(PersistenceManager::class)->persistAll(); // Zum absichern
        return true;
    }
    public function secureUpdate(Event $event): bool
    {
        $this->update($event);
        GeneralUtility::makeInstance(PersistenceManager::class)->persistAll(); // Zum absichern
        return true;
    }
    public function secureRemove(Event $event): bool
    {
        $this->remove($event);
        GeneralUtility::makeInstance(PersistenceManager::class)->persistAll(); // Zum absichern
        return true;
    }

    public function findMinTimeForDateTime($calendar, $startDate, $startTime)
    {
        $queryBuilder = null;
        $user = null;
        $acl = $calendar->getAcl();

        if ($acl && $acl->getIsAdmin()) {
            return [
                'min_time' => null,
                'start_date' => null,
            ];
        }

        /*if($user) {
            $queryBuilder->andWhere(
                $queryBuilder->expr()->eq('owner', $queryBuilder->createNamedParameter($user->getUid(), \PDO::PARAM_INT))
            );
        }*/
        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable(
            'tx_igreservations_domain_model_event'
        );
        $sql = 'select min(start_time) as min_time,start_date from tx_igreservations_domain_model_event WHERE start_date=\'' . $startDate . '\' AND start_time>=\'' . $startTime . '\' AND calendar=' . $calendar->getUid();

        $stmt = $connection->prepare($sql);
        $res = $stmt->executeQuery([]);

        return $res->fetchAssociative();
    }

    public function findMaxTimeForDateTime($calendar, $endDate, $endTime)
    {
        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable(
            'tx_igreservations_domain_model_event'
        );
        $sql = 'select max(end_time) as max_time,end_date from tx_igreservations_domain_model_event WHERE end_date=\'' . $endDate . '\' AND end_time<=\'' . $endTime . '\' AND calendar=' . $calendar->getUid();
        $stmt = $connection->prepare($sql);
        $res = $stmt->executeQuery([]);

        return $res->fetchAssociative();
    }

    public function findByCalendarDateAndTime($calendar, $dateTime)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        $res = $queryBuilder->select('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->eq('calendar', $calendar->getUid()),
                                $queryBuilder->expr()
->eq('start_date', $queryBuilder->createNamedParameter($dateTime->format('Y-m-d'))),
                                $queryBuilder->expr()
->lte('start_time', $queryBuilder->createNamedParameter($dateTime->format('H:i:s'))),
                                $queryBuilder->expr()
->gte('end_time', $queryBuilder->createNamedParameter($dateTime->format('H:i:s')))
                            )
                            ->executeQuery()
                            ->fetchAllAssociative();
        //var_dump($res);
        return $this->dataMapper->map(Event::class, $res);
    }

    public function findByDateAndCalendar($date, $calendar)
    {
        //$conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_igreservations_domain_model_event');
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        //$sql = 'SELECT * FROM tx_igreservations_domain_model_event WHERE start_date = \'' . $date->format('Y-m-d') . '\' AND uid IN (SELECT uid_local FROM tx_igreservations_event_timeslot_mm WHERE uid_foreign= ' . $timeslotUid . ') AND calendar=' . $calendarUid . ' AND hidden = 0';
        $res = $queryBuilder->selectLiteral('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->eq('calendar', $queryBuilder->createNamedParameter($calendar->getUid())),
                                $queryBuilder->expr()
->orX(
    $queryBuilder->expr()
->eq('start_date', $queryBuilder->createNamedParameter($date->format('Y-m-d'))),
    $queryBuilder->expr()
->eq('end_date', $queryBuilder->createNamedParameter($date->format('Y-m-d')))
)
                            )
                            ->orderBy('start_date', 'ASC')
                            ->addOrderBy('start_time', 'ASC')
                            ->executeQuery()
                            ->fetchAllAssociative();
        /*
        $stmt = $conn->prepare($sql);
    	$stmt->execute( [] );
        $res = $stmt->fetch();
        */
        if (!empty($res)) {
            $events = $this->dataMapper->map(Event::class, $res);
            return $events;
        }

        return [];
    }

    public function findPublic($limit = 0, $search = [], $showWithoutReservations = false)
    {
        $date = new \DateTime();
        /*$query = $this->createQuery();
        $query->matching($query->logicalAnd(
            $query->greaterThanOrEqual('startDate', $date->format('Y-m-d')),
            $query->equals('isPublic', 1)
        ));

        if($limit > 0) {
            $query->setLimit($limit);
        }
        return $query->execute();*/

        //$conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_igreservations_domain_model_event');
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        //$sql = 'SELECT * FROM tx_igreservations_domain_model_event WHERE start_date = \'' . $date->format('Y-m-d') . '\' AND uid IN (SELECT uid_local FROM tx_igreservations_event_timeslot_mm WHERE uid_foreign= ' . $timeslotUid . ') AND calendar=' . $calendarUid . ' AND hidden = 0';
        $queryBuilder->select('*')
                            ->from('tx_igreservations_domain_model_event', 'e')
                            ->where(
                                $queryBuilder->expr()
->eq('main_event', $queryBuilder->createNamedParameter(1)),
                                $queryBuilder->expr()
->eq('is_public', $queryBuilder->createNamedParameter(1)),
                                $queryBuilder->expr()
->gte('start_date', $queryBuilder->createNamedParameter($date->format('Y-m-d')))
                            )
                            ->orderBy('start_date', 'ASC')
                            ->addOrderBy('start_time', 'ASC');

        if ($search['bookingType'] ?? null) {
            $queryBuilder->andWhere(
                $queryBuilder->expr()
->eq('booking_type', $queryBuilder->createNamedParameter($search['bookingType']))
            );
        }

        if ($search['location'] ?? null && is_numeric($search['location'])) {
            $queryBuilder->andWhere(
                $queryBuilder->expr()
->eq('location', $queryBuilder->createNamedParameter($search['location']))
            );
        }

        // Exclude events that already have reservations
        if ($showWithoutReservations) {
            $queryBuilder->andWhere(
                'uid NOT IN (SELECT event FROM tx_igreservations_domain_model_reservation AS r WHERE hidden = 0)'
            );
        }


        if ($limit > 0) {
            $queryBuilder->setMaxResults($limit);
        }


        $res = $queryBuilder->executeQuery()
->fetchAllAssociative();

        if (!empty($res)) {
            $events = $this->dataMapper->map(Event::class, $res);
            return $events;
        }

        return [];
    }

    public function findLaterEventsInSameGroup($event)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        //$sql = 'SELECT * FROM tx_igreservations_domain_model_event WHERE start_date = \'' . $date->format('Y-m-d') . '\' AND uid IN (SELECT uid_local FROM tx_igreservations_event_timeslot_mm WHERE uid_foreign= ' . $timeslotUid . ') AND calendar=' . $calendarUid . ' AND hidden = 0';
        $queryBuilder->select('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->eq('event_group', $queryBuilder->createNamedParameter($event->getEventGroup()->getUid())),
                                $queryBuilder->expr()
->gte('start_date', $queryBuilder->createNamedParameter($event->getStartDate()->format('Y-m-d')))
                            )
                            ->orderBy('start_date', 'ASC')
                            ->addOrderBy('start_time', 'ASC');


        $res = $queryBuilder->executeQuery()
->fetchAllAssociative();

        if (!empty($res)) {
            $events = $this->dataMapper->map(Event::class, $res);
            return $events;
        }

        return [];
    }

    public function findCalendarIdsInDatesAndStartAndEndTime($startTime, $endTime, array $dates = [])
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
            'tx_igreservations_domain_model_event'
        );
        //$sql = 'SELECT * FROM tx_igreservations_domain_model_event WHERE start_date = \'' . $date->format('Y-m-d') . '\' AND uid IN (SELECT uid_local FROM tx_igreservations_event_timeslot_mm WHERE uid_foreign= ' . $timeslotUid . ') AND calendar=' . $calendarUid . ' AND hidden = 0';
        $queryBuilder->selectLiteral('DISTINCT calendar')
                            ->from('tx_igreservations_domain_model_event')
                            ->where(
                                $queryBuilder->expr()
->in('start_date', array_map(fn ($value) => $queryBuilder->createNamedParameter($value), $dates)),
                                $queryBuilder->expr()
->lte('start_time', $queryBuilder->createNamedParameter($startTime)),
                                $queryBuilder->expr()
->gte('end_time', $queryBuilder->createNamedParameter($endTime)),
                            )
                            ->orderBy('start_date', 'ASC')
                            ->addOrderBy('start_time', 'ASC');

        $res = $queryBuilder->executeQuery()
->fetchAllAssociative();

        $uids = [];
        foreach ($res as $row) {
            $uids[] = $row['calendar'];
        }

        return $uids;
    }


    public function findByCalendarAndSearch(int $calendarUid, array $search = [])
    {
        $query = $this->createQuery();

        $constraints = [];

        $constraints[] = $query->equals('calendar', $calendarUid);

        if (!empty($search)) {
            if ($search['startDateAndTime'] ?? false) {
                $startDateTime = new \DateTime($search['startDateAndTime']);
                $constraints[] = $query->greaterThanOrEqual('startDate', $startDateTime->format('Y-m-d'));
            }
            if ($search['endDateAndTime'] ?? false) {
                $endDateTime = new \DateTime($search['endDateAndTime']);
                $constraints[] = $query->lessThan('startDate', $endDateTime->format('Y-m-d'));
            }
            if ($search['owner'] ?? false) {
                $constraints[] = $query->equals('owner', $search['owner']);
            }
            //var_dump($search);die();
            if ($search['bookingType'] ?? false) {
                $constraints[] = $query->equals('bookingType', $search['bookingType']);
            }
            if ($search['status'] ?? false) {
                $constraints[] = $query->equals('status', $search['status']);
            }
            if (($search['location'] ?? false) && is_numeric($search['location'])) {
                $constraints[] = $query->equals('location', $search['location']);
            } elseif ($search['location'] ?? false) {
                $reservationType = explode('_', (string) $search['location'])[1];
                $constraints[] = $query->contains('reservationTypes', $reservationType);
            }
        }

        $query->matching($query->logicalAnd(...$constraints));

        return $query->execute();
    }
    public function getByCalendarAndSearch(int $calendarUid, array $search = []): array
    {
        $queryResult = $this->findByCalendarAndSearch($calendarUid, $search);
        return $queryResult->toArray();
    }

    public function findAmountOfEventsPerUser(array $settings = [], array $search = [])
    {
        $qb = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        $this->expandStatisticsQueryBuilder($qb, $search);

        $qb->selectLiteral('TRIM(CONCAT(u.first_name, \' \', u.last_name, \' (\', u.username, \')\')) as x, count(1) as y')
            ->leftJoin('e', 'fe_users', 'u', 'u.uid = e.owner');


        $res = $qb->groupBy('u.username')
            ->orderBy('u.last_name')
            ->addOrderBy('u.first_name')
            ->addOrderBy('u.username')
            ->executeQuery()
            ->fetchAllAssociative();
        return $res;
    }

    public function findAmountOfEventsPerBookingType(array $settings = [], array $search = [])
    {
        $qb = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        $this->expandStatisticsQueryBuilder($qb, $search);

        $qb->selectLiteral('b.name as x, count(1) as y')
            ->leftJoin('e', 'tx_igreservations_domain_model_bookingtype', 'b', 'b.uid = e.booking_type');


        $res = $qb->groupBy('e.booking_type')
            ->orderBy('b.name')
            ->executeQuery()
            ->fetchAllAssociative();
        return $res;
    }

    public function findAmountOfEventsPerReservationType(array $settings = [], array $search = [])
    {
        $qb = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        $this->expandStatisticsQueryBuilder($qb, $search);

        $qb->selectLiteral('rt.name as x, count(1) as y')
            ->leftJoin('r', 'tx_igreservations_domain_model_reservationtype', 'rt', 'rt.uid = r.reservation_type');


        $res = $qb->groupBy('r.reservation_type')
            ->orderBy('rt.name')
            ->executeQuery()
            ->fetchAllAssociative();
        return $res;
    }

    public function findAmountOfEventsPerLocation(array $settings = [], array $search = [])
    {
        $qb = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        $this->expandStatisticsQueryBuilder($qb, $search);

        $qb->selectLiteral('l.name as x, count(1) as y')
            ->leftJoin('e', 'tx_igreservations_domain_model_location', 'l', 'l.uid = e.location');


        $res = $qb->groupBy('e.location')
            ->orderBy('l.name')
            ->executeQuery()
            ->fetchAllAssociative();
        return $res;
    }

    public function findAmountOfEventsPerDateFormat(string $dateFormat, array $settings = [], array $search = [])
    {
        $qb = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_igreservations_domain_model_event');
        $this->expandStatisticsQueryBuilder($qb, $search);
        
        $qb->selectLiteral('DATE_FORMAT(start_date, \'' . $dateFormat . '\') as x, count(1) as y');


        $res = $qb->groupBy('x')
            ->orderBy('x')
            ->executeQuery()
            ->fetchAllAssociative();
        return $res;
    }

    public function findUsersWithReservations(array $settings = [], array $search = [])
    {
        $conn = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_igreservations_domain_model_event');
        
        $qb = $conn->createQueryBuilder();
        $this->expandStatisticsQueryBuilder($qb, $search);

        $qb->selectLiteral('u.*')
            ->join('e', 'fe_users', 'u', 'u.uid = e.owner');


        $res = $qb->groupBy('e.owner')
            ->orderBy('last_name')
            ->addOrderBy('first_name')
            ->addOrderBy('username')
            ->executeQuery()
            ->fetchAllAssociative();

        foreach($res as &$user) {
            $qb = $conn->createQueryBuilder();
            $this->expandStatisticsQueryBuilder($qb, $search);
            
            $qb->select('r.*')
                ->andWhere('e.owner = ' . $user['uid'])
                ->orderBy('e.start_date', 'DESC')
                ->addOrderBy('e.start_time', 'DESC');


            $reservations = $qb->executeQuery()
                               ->fetchAllAssociative();

            foreach($reservations as &$reservation) {
                $qb = $conn->createQueryBuilder();
                $event = $qb->select('*')
                            ->from('tx_igreservations_domain_model_event')
                            ->where('uid = ' . $reservation['event'])
                            ->executeQuery()
                            ->fetchAssociative();

                if ($event['status']) {
                    $qb = $conn->createQueryBuilder();
                    $status = $qb->select('*')
                                ->from('tx_igreservations_domain_model_status')
                                ->where('uid = ' . $event['status'])
                                ->executeQuery()
                                ->fetchAssociative();
                    $event['status'] = $status;
                }

                if ($event['booking_type']) {
                    $qb = $conn->createQueryBuilder();
                    $bookingType = $qb->select('*')
                                ->from('tx_igreservations_domain_model_bookingtype')
                                ->where('uid = ' . $event['booking_type'])
                                ->executeQuery()
                                ->fetchAssociative();
                    $event['booking_type'] = $bookingType;
                }

                if ($event['location']) {
                    $qb = $conn->createQueryBuilder();
                    $location = $qb->select('*')
                                ->from('tx_igreservations_domain_model_location')
                                ->where('uid = ' . $event['location'])
                                ->executeQuery()
                                ->fetchAssociative();
                    $event['location'] = $location;
                }

                if($reservation['reservation_type']) {
                    $qb = $conn->createQueryBuilder();
                    $reservationType = $qb->select('*')
                                ->from('tx_igreservations_domain_model_reservationtype')
                                ->where('uid = ' . $reservation['reservation_type'])
                                ->executeQuery()
                                ->fetchAssociative();
                    $reservation['reservation_type'] = $reservationType;
                }
                $reservation['event'] = $event;
            }

            $user['reservations'] = $reservations;
        }
        return $res;
    }

    protected function expandStatisticsQueryBuilder(QueryBuilder &$qb, array $search = [])
    {

        $qb->from('tx_igreservations_domain_model_event', 'e')
            ->innerJoin('e', 'tx_igreservations_domain_model_reservation', 'r', 'r.event = e.uid');

        if (isset($search['calendar']) && $search['calendar'] !== '') {
            $qb->andWhere($qb->expr()->eq('e.calendar', $qb->createNamedParameter($search['calendar'], \PDO::PARAM_INT)));
        }

        if (isset($search['startDate']) && is_array($search['startDate']) && $search['startDate']['date'] !== '') {
            $dateTime = new \DateTime($search['startDate']['date']);
            $qb->andWhere($qb->expr()->gte('e.start_date', $qb->createNamedParameter($dateTime->format('Y-m-d'))));
        }

        if (isset($search['endDate']) && is_array($search['endDate']) && $search['endDate']['date'] !== '') {
            $dateTime = new \DateTime($search['endDate']['date']);
            $qb->andWhere($qb->expr()->lte('e.start_date', $qb->createNamedParameter($dateTime->format('Y-m-d'))));
        }
    }
}
