<?php
namespace AmeliaBooking\Infrastructure\Repository\Booking\Event;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Booking\Event\Event;
use AmeliaBooking\Domain\Entity\Booking\Event\EventTicket;
use AmeliaBooking\Domain\Factory\Booking\Appointment\CustomerBookingFactory;
use AmeliaBooking\Domain\Factory\Booking\Event\EventFactory;
use AmeliaBooking\Domain\Repository\Booking\Event\EventRepositoryInterface;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Licence;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToEventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingToEventsTicketsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsProvidersTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTagsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTicketsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Gallery\GalleriesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Payment\PaymentsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;
/**
* Class EventRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Booking\Event
*/
class EventRepository extends AbstractRepository implements EventRepositoryInterface
{
public const FACTORY = EventFactory::class;
/**
* @param Event $entity
*
* @return int
* @throws QueryExecutionException
*/
public function add($entity)
{
$data = $entity->toArray();
$params = [
':bookingOpens' => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
':bookingCloses' => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
':bookingOpensRec' => $data['bookingOpensRec'],
':bookingClosesRec' => $data['bookingClosesRec'],
':status' => $data['status'],
':name' => $data['name'],
':description' => $data['description'],
':color' => $data['color'],
':price' => $data['price'],
':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
':bookMultipleTimes' => $data['bookMultipleTimes'] ? 1 : 0,
':maxCapacity' => $data['maxCapacity'],
':maxCustomCapacity' => $data['maxCustomCapacity'],
':maxExtraPeople' => $data['maxExtraPeople'],
':show' => $data['show'] ? 1 : 0,
':notifyParticipants' => $data['notifyParticipants'],
':customLocation' => $data['customLocation'],
':parentId' => $data['parentId'],
':created' => $data['created'],
':closeAfterMin' => $data['closeAfterMin'],
':closeAfterMinBookings' => $data['closeAfterMinBookings'] ? 1 : 0,
':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0,
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':error' => '',
];
$additionalData = Licence\DataModifier::getEventRepositoryData($data);
$params = array_merge($params, $additionalData['values'], $additionalData['addValues']);
try {
$statement = $this->connection->prepare(
"INSERT INTO {$this->table}
(
{$additionalData['columns']}
`bookingOpens`,
`bookingCloses`,
`bookingOpensRec`,
`bookingClosesRec`,
`status`,
`name`,
`description`,
`color`,
`price`,
`bringingAnyone`,
`bookMultipleTimes`,
`maxCapacity`,
`maxCustomCapacity`,
`maxExtraPeople`,
`show`,
`notifyParticipants`,
`customLocation`,
`parentId`,
`created`,
`closeAfterMin`,
`closeAfterMinBookings`,
`aggregatedPrice`,
`pictureFullPath`,
`pictureThumbPath`,
`error`
)
VALUES (
{$additionalData['placeholders']}
:bookingOpens,
:bookingCloses,
:bookingOpensRec,
:bookingClosesRec,
:status,
:name,
:description,
:color,
:price,
:bringingAnyone,
:bookMultipleTimes,
:maxCapacity,
:maxCustomCapacity,
:maxExtraPeople,
:show,
:notifyParticipants,
:customLocation,
:parentId,
:created,
:closeAfterMin,
:closeAfterMinBookings,
:aggregatedPrice,
:pictureFullPath,
:pictureThumbPath,
:error
)"
);
$statement->execute($params);
return $this->connection->lastInsertId();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $id
* @param Event $entity
*
* @return mixed
* @throws QueryExecutionException
*/
public function update($id, $entity)
{
$data = $entity->toArray();
$params = [
':id' => $id,
':bookingOpens' => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
':bookingCloses' => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
':bookingOpensRec' => $data['bookingOpensRec'],
':bookingClosesRec' => $data['bookingClosesRec'],
':status' => $data['status'],
':name' => $data['name'],
':description' => $data['description'],
':color' => $data['color'],
':price' => $data['price'],
':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
':bookMultipleTimes' => $data['bookMultipleTimes'] ? 1 : 0,
':maxCapacity' => $data['maxCapacity'],
':maxCustomCapacity' => $data['maxCustomCapacity'],
':maxExtraPeople' => $data['maxExtraPeople'],
':show' => $data['show'] ? 1 : 0,
':notifyParticipants' => $data['notifyParticipants'] ? 1 : 0,
':customLocation' => $data['customLocation'],
':parentId' => $data['parentId'],
':closeAfterMin' => $data['closeAfterMin'],
':closeAfterMinBookings' => $data['closeAfterMinBookings'] ? 1 : 0,
':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0,
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
];
$additionalData = Licence\DataModifier::getEventRepositoryData($data);
$params = array_merge($params, $additionalData['values']);
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
{$additionalData['columnsPlaceholders']}
`bookingOpens` = :bookingOpens,
`bookingCloses` = :bookingCloses,
`bookingOpensRec` = :bookingOpensRec,
`bookingClosesRec` = :bookingClosesRec,
`status` = :status,
`name` = :name,
`description` = :description,
`color` = :color,
`price` = :price,
`bringingAnyone` = :bringingAnyone,
`bookMultipleTimes` = :bookMultipleTimes,
`maxCapacity` = :maxCapacity,
`maxCustomCapacity` = :maxCustomCapacity,
`maxExtraPeople` = :maxExtraPeople,
`show` = :show,
`notifyParticipants` = :notifyParticipants,
`customLocation` = :customLocation,
`parentId` = :parentId,
`closeAfterMin` = :closeAfterMin,
`closeAfterMinBookings` = :closeAfterMinBookings,
`aggregatedPrice` = :aggregatedPrice,
`pictureFullPath` = :pictureFullPath,
`pictureThumbPath` = :pictureThumbPath
WHERE id = :id"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $id
* @param int|null $parentId
*
* @return mixed
* @throws QueryExecutionException
*/
public function updateParentId($id, $parentId)
{
$params = [
':id' => $id,
':parentId' => $parentId,
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
`parentId` = :parentId
WHERE id = :id"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param array $criteria
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getProvidersEvents($criteria)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$eventsProvidersTable = EventsProvidersTable::getTableName();
$usersTable = UsersTable::getTableName();
$params = [];
$where = [];
if (!empty($criteria['dates'])) {
if (isset($criteria['dates'][0], $criteria['dates'][1])) {
$whereStart = "(ep.periodStart BETWEEN :eventFrom AND :eventTo)";
$params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
$whereEnd = "(ep.periodEnd BETWEEN :bookingFrom2 AND :bookingTo2)";
$params[':bookingFrom2'] = $params[':eventFrom'];
$params[':bookingTo2'] = $params[':eventTo'];
$where[] = "({$whereStart} OR {$whereEnd})";
} elseif (isset($criteria['dates'][0])) {
$where[] = "(ep.periodStart >= :eventFrom)";
$params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
} elseif (isset($criteria['dates'][1])) {
$where[] = "(ep.periodStart <= :eventTo)";
$params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
} else {
$where[] = "(ep.periodStart > :eventFrom)";
$params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
}
}
if (!empty($criteria['providers'])) {
$queryProviders = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param = ':provider' . $index;
$queryProviders[] = $param;
$params[$param] = $value;
}
$where[] = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = 'e.status = :status';
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
e.id AS event_id,
e.name AS event_name,
e.status AS event_status,
e.bookingOpens AS event_bookingOpens,
e.bookingCloses AS event_bookingCloses,
e.recurringCycle AS event_recurringCycle,
e.recurringOrder AS event_recurringOrder,
e.recurringInterval AS event_recurringInterval,
e.recurringUntil AS event_recurringUntil,
e.recurringMonthly AS event_recurringMonthly,
e.monthlyDate AS event_monthlyDate,
e.monthlyOnRepeat AS event_monthlyOnRepeat,
e.monthlyOnDay AS event_monthlyOnDay,
e.bringingAnyone AS event_bringingAnyone,
e.bookMultipleTimes AS event_bookMultipleTimes,
e.maxCapacity AS event_maxCapacity,
e.maxCustomCapacity AS event_maxCustomCapacity,
e.maxExtraPeople AS event_maxExtraPeople,
e.price AS event_price,
e.description AS event_description,
e.color AS event_color,
e.show AS event_show,
e.locationId AS event_locationId,
e.customLocation AS event_customLocation,
e.parentId AS event_parentId,
e.created AS event_created,
e.notifyParticipants AS event_notifyParticipants,
e.translations AS event_translations,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.fullPayment AS event_fullPayment,
e.customPricing AS event_customPricing,
e.aggregatedPrice AS event_aggregatedPrice,
ep.id AS event_periodId,
ep.periodStart AS event_periodStart,
ep.periodEnd AS event_periodEnd,
pu.id AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.note AS provider_note,
pu.description AS provider_description,
pu.phone AS provider_phone,
pu.countryPhoneIso AS provider_countryPhoneIso,
pu.gender AS provider_gender,
pu.pictureFullPath AS provider_pictureFullPath,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.translations AS provider_translations
FROM {$this->table} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
INNER JOIN {$usersTable} pu ON pu.id = epr.userId
{$where}
ORDER BY ep.periodStart"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
/**
* @param array $criteria
* @param int|null $itemsPerPage
*
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getFilteredIds($criteria, $itemsPerPage)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$eventsTagsTable = EventsTagsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$eventsProvidersTable = EventsProvidersTable::getTableName();
$params = [];
$where = [];
$joins = '';
$groupBy = 'GROUP BY e.id';
if (isset($criteria['parentId'])) {
$params[':parentId'] = (int)$criteria['parentId'];
$params[':originParentId'] = (int)$criteria['parentId'];
$where[] = '(e.parentId = :parentId OR e.id = :originParentId)';
}
if (!empty($criteria['events'])) {
$queryEventIds = [];
$index = 0;
foreach ((array)$criteria['events'] as $value) {
$param = ':id' . $index++;
$queryEventIds[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'e.id IN (' . implode(', ', $queryEventIds) . ')';
}
if (!empty($criteria['excludeIds'])) {
$queryExcludeIds = [];
$index = 0;
foreach ((array)$criteria['excludeIds'] as $value) {
$param = ':excludeId' . $index++;
$queryExcludeIds[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'e.id NOT IN (' . implode(', ', $queryExcludeIds) . ')';
}
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$p1 = ":search{$termIndex}_1";
$p2 = ":search{$termIndex}_2";
$p3 = ":search{$termIndex}_3";
$p4 = ":search{$termIndex}_4";
$p5 = ":search{$termIndex}_5";
$params[$p1] = "%{$term}%";
$params[$p2] = "{\"name\":{%{$term}%\"description\":{%";
$params[$p3] = "{\"description\":{%\"name\":{%{$term}%";
$params[$p4] = "{\"name\":{%{$term}%";
$params[$p5] = "%{$term}%";
$where[] = "(
e.name LIKE {$p1}
OR e.translations LIKE {$p2}
OR e.translations LIKE {$p3}
OR (
e.translations LIKE {$p4}
AND e.translations NOT LIKE '%\"description\":{%'
)
OR e.id LIKE {$p5}
)";
$termIndex++;
}
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = "e.status = :status";
}
if (isset($criteria['show'])) {
$where[] = 'e.show = 1';
}
if (!empty($criteria['dates'])) {
if (!empty($criteria['dates'][0]) && !empty($criteria['dates'][1])) {
$where[] = "((ep.periodStart BETWEEN :eventFrom1 AND :eventTo1)
OR (ep.periodEnd BETWEEN :eventFrom2 AND :eventTo2)
OR (:eventFrom3 BETWEEN ep.periodStart AND ep.periodEnd)
OR (:eventTo3 BETWEEN ep.periodStart AND ep.periodEnd))";
$params[':eventFrom1'] = $params[':eventFrom2'] = $params[':eventFrom3'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':eventTo1'] = $params[':eventTo2'] = $params[':eventTo3'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
} elseif (!empty($criteria['dates'][0])) {
$where[] = "(ep.periodStart >= :eventFrom OR (ep.periodEnd >= :eventTo))";
$params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
} elseif (!empty($criteria['dates'][1])) {
$where[] = "(ep.periodStart <= :eventTo)";
$params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
} else {
$where[] = "(ep.periodStart > :eventFrom)";
$params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
}
}
if (!empty($criteria['tag'])) {
$queryTags = [];
$tags = array_values((array)$criteria['tag']);
foreach ($tags as $index => $value) {
$param = ':tag' . $index;
$queryTags[] = $param;
$params[$param] = $value;
}
$where[] = 'et.name IN (' . implode(', ', $queryTags) . ')';
$joins .= "
INNER JOIN {$eventsTagsTable} et ON et.eventId = e.id
";
}
if (!empty($criteria['skipRecurring'])) {
// When skipRecurring is true, only return the first upcoming event from each recurring series
$params[':recurringEventFrom'] = !empty($criteria['dates'][0]) ?
DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]) :
DateTimeService::getNowDateTimeInUtc();
$where[] = "e.id IN (
SELECT e_sub.id
FROM {$this->table} e_sub
INNER JOIN {$eventsPeriodsTable} ep_sub ON ep_sub.eventId = e_sub.id
INNER JOIN (
SELECT COALESCE(e_inner.parentId, e_inner.id) as series_id, MIN(ep_inner.periodStart) as min_start
FROM {$this->table} e_inner
INNER JOIN {$eventsPeriodsTable} ep_inner ON ep_inner.eventId = e_inner.id
WHERE ep_inner.periodStart >= :recurringEventFrom
GROUP BY COALESCE(e_inner.parentId, e_inner.id)
) upcoming ON COALESCE(e_sub.parentId, e_sub.id) = upcoming.series_id
AND ep_sub.periodStart = upcoming.min_start
)";
}
if (!empty($criteria['id'])) {
if (!empty($criteria['recurring'])) {
$whereOr = [];
$index = 0;
foreach ((array)$criteria['id'] as $value) {
$param = 'id' . $index;
$params[':rec1' . $param] = (int)$value;
$params[':rec2' . $param] = (int)$value;
$params[':rec3' . $param] = (int)$value;
$params[':rec4' . $param] = (int)$value;
$whereOr[] = "((e.id = :rec1id" . $index . " AND e.parentId IS NULL) OR
(e.parentId IN (SELECT parentId FROM {$this->table} WHERE parentId IS NOT NULL AND parentId = :rec2id" . $index . ")) OR
(e.id >= :rec3id" . $index . " AND e.parentId IN (SELECT parentId FROM {$this->table} WHERE id = :rec4id" . $index . ")))";
$index++;
}
$where[] = '(' . implode(' OR ', $whereOr) . ')';
} else {
$queryIds = [];
$index = 0;
foreach ((array)$criteria['id'] as $value) {
$param = ':filterId' . $index++;
$queryIds[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
}
}
if (!empty($criteria['customers']) || !empty($criteria['customerId']) || !empty($criteria['customerBookingsIds'])) {
$joins .= "
LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
";
if (!empty($criteria['customerId'])) {
$params[':customerId'] = $criteria['customerId'];
$where[] = 'cb.customerId = :customerId';
}
if (!empty($criteria['customers'])) {
$queryCustomerIds = [];
$index = 0;
foreach ((array)$criteria['customers'] as $value) {
$param = ':customerId' . $index++;
$queryCustomerIds[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'cb.customerId IN (' . implode(', ', $queryCustomerIds) . ')';
}
if (!empty($criteria['customerBookingsIds'])) {
$queryBookingsIds = [];
$index = 0;
foreach ((array)$criteria['customerBookingsIds'] as $value) {
$param = ':customerBookingId' . $index++;
$queryBookingsIds[] = $param;
$params[$param] = (int)$value;
}
$where[] = 'cb.id IN (' . implode(', ', $queryBookingsIds) . ')';
}
if (!empty($criteria['customerBookingStatus'])) {
$params[':customerBookingStatus'] = $criteria['customerBookingStatus'];
$where[] = 'cb.status = :customerBookingStatus';
}
if (!empty($criteria['customerBookingCouponId'])) {
$params[':customerBookingCouponId'] = $criteria['customerBookingCouponId'];
$where[] = 'cb.couponId = :customerBookingCouponId';
}
}
if (!empty($criteria['locationId'])) {
$params[':locationId'] = $criteria['locationId'];
$where[] = 'e.locationId = :locationId';
}
if (!empty($criteria['locations'])) {
$queryLocations = [];
$index = 0;
foreach ((array)$criteria['locations'] as $value) {
$param = ':location' . $index++;
$queryLocations[] = $param;
$params[$param] = (int)$value;
}
$where3 = 'e.locationId IN (' . implode(', ', $queryLocations) . ')';
$where[] = '(' . $where3 . ')';
}
if (!empty($criteria['providers'])) {
$joins .= "
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
";
$queryProviders = [];
$index = 0;
foreach ((array)$criteria['providers'] as $value) {
$param = ':provider' . $index++;
$queryProviders[] = $param;
$params[$param] = (int)$value;
}
$where1 = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
$queryProviders = [];
$index = 0;
foreach ((array)$criteria['providers'] as $value) {
$param = ':organizer' . $index++;
$queryProviders[] = $param;
$params[$param] = (int)$value;
}
$where2 = 'e.organizerId IN (' . implode(', ', $queryProviders) . ')';
$where[] = '(' . $where1 . ' OR ' . $where2 . ')';
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
$limit = $this->getLimit(
!empty($criteria['page']) ? (int)$criteria['page'] : 0,
(int)$itemsPerPage
);
try {
$statement = $this->connection->prepare(
"SELECT
e.id
FROM {$this->table} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
{$joins}
{$where}
{$groupBy}
{$this->getOrderBy(!empty($criteria['sort']) ? $criteria['sort'] : null)}
{$limit}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return array_column($rows, 'id');
}
/**
* @param int $id
*
* @return Event
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getById($id, $criteria = [])
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$eventsTagsTable = EventsTagsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$usersTable = UsersTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$galleriesTable = GalleriesTable::getTableName();
$eventsProvidersTable = EventsProvidersTable::getTableName();
$couponsTable = CouponsTable::getTableName();
$fields = '';
$joins = "INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
LEFT JOIN {$eventsTagsTable} et ON et.eventId = e.id
LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
LEFT JOIN {$usersTable} cu ON cu.id = cb.customerId
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$galleriesTable} g ON g.entityId = e.id AND g.entityType = 'event'
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId";
if (!empty($criteria['fetchBookingsTickets'])) {
$bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$fields .= '
cbt.id AS booking_ticket_id,
cbt.eventTicketId AS booking_ticket_eventTicketId,
cbt.price AS booking_ticket_price,
cbt.persons AS booking_ticket_persons,
';
$joins .= "
LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
";
}
$fields .= 'e.id AS event_id,
e.name AS event_name,
e.status AS event_status,
e.bookingOpens AS event_bookingOpens,
e.bookingCloses AS event_bookingCloses,
e.bookingOpensRec AS event_bookingOpensRec,
e.bookingClosesRec AS event_bookingClosesRec,
e.ticketRangeRec AS event_ticketRangeRec,
e.recurringCycle AS event_recurringCycle,
e.recurringOrder AS event_recurringOrder,
e.recurringInterval AS event_recurringInterval,
e.recurringMonthly AS event_recurringMonthly,
e.monthlyDate AS event_monthlyDate,
e.monthlyOnRepeat AS event_monthlyOnRepeat,
e.monthlyOnDay AS event_monthlyOnDay,
e.recurringUntil AS event_recurringUntil,
e.bringingAnyone AS event_bringingAnyone,
e.bookMultipleTimes AS event_bookMultipleTimes,
e.maxCapacity AS event_maxCapacity,
e.maxCustomCapacity AS event_maxCustomCapacity,
e.maxExtraPeople AS event_maxExtraPeople,
e.price AS event_price,
e.description AS event_description,
e.color AS event_color,
e.show AS event_show,
e.notifyParticipants AS event_notifyParticipants,
e.locationId AS event_locationId,
e.customLocation AS event_customLocation,
e.parentId AS event_parentId,
e.created AS event_created,
e.settings AS event_settings,
e.zoomUserId AS event_zoomUserId,
e.organizerId AS event_organizerId,
e.translations AS event_translations,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.fullPayment AS event_fullPayment,
e.customPricing AS event_customPricing,
e.aggregatedPrice AS event_aggregatedPrice,
ep.id AS event_periodId,
ep.periodStart AS event_periodStart,
ep.periodEnd AS event_periodEnd,
ep.zoomMeeting AS event_periodZoomMeeting,
ep.lessonSpace AS event_periodLessonSpace,
ep.googleCalendarEventId AS event_googleCalendarEventId,
ep.googleMeetUrl AS event_googleMeetUrl,
ep.outlookCalendarEventId AS event_outlookCalendarEventId,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
ep.appleCalendarEventId AS event_appleCalendarEventId,
et.id AS event_tagId,
et.name AS event_tagName,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.persons AS booking_persons,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.token AS booking_token,
cb.utcOffset AS booking_utcOffset,
cb.couponId AS booking_couponId,
cu.id AS customer_id,
cu.firstName AS customer_firstName,
cu.lastName AS customer_lastName,
cu.email AS customer_email,
cu.note AS customer_note,
cu.phone AS customer_phone,
cu.countryPhoneIso AS customer_countryPhoneIso,
cu.gender AS customer_gender,
cu.birthday AS customer_birthday,
p.id AS payment_id,
p.amount AS payment_amount,
p.dateTime AS payment_dateTime,
p.status AS payment_status,
p.gateway AS payment_gateway,
p.gatewayTitle AS payment_gatewayTitle,
p.transactionId AS payment_transactionId,
p.data AS payment_data,
p.wcOrderId AS payment_wcOrderId,
p.wcOrderItemId AS payment_wcOrderItemId,
p.invoiceNumber AS payment_invoiceNumber,
pu.id AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.note AS provider_note,
pu.description AS provider_description,
pu.phone AS provider_phone,
pu.countryPhoneIso AS provider_countryPhoneIso,
pu.gender AS provider_gender,
pu.translations AS provider_translations,
pu.timeZone AS provider_timeZone,
g.id AS gallery_id,
g.pictureFullPath AS gallery_picture_full,
g.pictureThumbPath AS gallery_picture_thumb,
g.position AS gallery_position,
c.id AS coupon_id,
c.code AS coupon_code,
c.discount AS coupon_discount,
c.deduction AS coupon_deduction,
c.limit AS coupon_limit,
c.customerLimit AS coupon_customerLimit,
c.status AS coupon_status';
try {
$statement = $this->connection->prepare(
"SELECT
{$fields}
FROM {$this->table} e
{$joins}
WHERE e.id = :eventId"
);
$statement->bindParam(':eventId', $id);
$statement->execute();
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Event $event */
$event = call_user_func([static::FACTORY, 'createCollection'], $rows)->getItem($id);
$eventTicketRepository = new EventTicketRepository(
$this->connection,
EventsTicketsTable::getTableName()
);
$customTickets = new Collection();
/** @var EventTicket $ticket */
foreach ($eventTicketRepository->getByEntityId($id, 'eventId')->getItems() as $ticket) {
$customTickets->addItem($ticket, $ticket->getId()->getValue());
}
$event->setCustomTickets($customTickets);
return $event;
}
/**
* @param int $id
*
* @return mixed
* @throws QueryExecutionException
*/
public function isRecurring($id)
{
try {
$statement = $this->connection->prepare(
"SELECT
e.recurringOrder AS event_recurringOrder,
e.parentId AS event_parentId
FROM {$this->table} e
WHERE e.id = :eventId"
);
$statement->bindParam(':eventId', $id);
$statement->execute();
return $statement->fetch();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $id
* @param int $parentId
*
* @return mixed
* @throws QueryExecutionException
*/
public function getRecurringIds($id, $parentId)
{
$whereParent = empty($parentId) ? '' : ' OR e.parentId = :parentId';
try {
$statement = $this->connection->prepare(
"SELECT
e.id AS eventId
FROM {$this->table} e
WHERE e.parentId = :eventId" . $whereParent
);
$statement->bindParam(':eventId', $id);
if ($parentId) {
$statement->bindParam(':parentId', $parentId);
}
$statement->execute();
$events = $statement->fetchAll();
return array_column($events, 'eventId');
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $bookingId
* @param array $criteria
*
* @return ?Event
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getByBookingId($bookingId, $criteria = []): ?Event
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$fields = '';
$joins = '';
if (!empty($criteria['fetchEventsCoupons'])) {
$couponsTable = CouponsTable::getTableName();
$fields .= '
ec.id AS coupon_id,
ec.code AS coupon_code,
ec.discount AS coupon_discount,
ec.deduction AS coupon_deduction,
ec.limit AS coupon_limit,
ec.customerLimit AS coupon_customerLimit,
ec.status AS coupon_status,
';
$joins .= "
LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
";
}
if (!empty($criteria['fetchEventsTickets'])) {
$ticketsTable = EventsTicketsTable::getTableName();
$fields .= '
eti.id AS ticket_id,
eti.name AS ticket_name,
eti.enabled AS ticket_enabled,
eti.price AS ticket_price,
eti.spots AS ticket_spots,
eti.waitingListSpots AS ticket_waiting_list_spots,
eti.dateRanges AS ticket_dateRanges,
eti.translations AS ticket_translations,
';
$joins .= "
LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
";
}
if (!empty($criteria['fetchEventsTags'])) {
$tagsTable = EventsTagsTable::getTableName();
$fields .= '
eta.id AS event_tagId,
eta.name AS event_tagName,
';
$joins .= "
LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
";
}
if (!empty($criteria['fetchEventsImages'])) {
$galleriesTable = GalleriesTable::getTableName();
$fields .= '
eg.id AS gallery_id,
eg.pictureFullPath AS gallery_picture_full,
eg.pictureThumbPath AS gallery_picture_thumb,
eg.position AS gallery_position,
';
$joins .= "
LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
";
}
if (!empty($criteria['fetchEventsProviders'])) {
$eventsProvidersTable = EventsProvidersTable::getTableName();
$usersTable = UsersTable::getTableName();
$joins .= "
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
";
$fields .= '
pu.id AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.note AS provider_note,
pu.description AS provider_description,
pu.phone AS provider_phone,
pu.countryPhoneIso AS provider_countryPhoneIso,
pu.gender AS provider_gender,
pu.pictureFullPath AS provider_pictureFullPath,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.translations AS provider_translations,
pu.timeZone AS provider_timeZone,
';
}
$fields .= "
e.id AS event_id,
e.name AS event_name,
e.status AS event_status,
e.bookingOpens AS event_bookingOpens,
e.bookingCloses AS event_bookingCloses,
e.recurringCycle AS event_recurringCycle,
e.recurringOrder AS event_recurringOrder,
e.recurringInterval AS event_recurringInterval,
e.recurringUntil AS event_recurringUntil,
e.bringingAnyone AS event_bringingAnyone,
e.bookMultipleTimes AS event_bookMultipleTimes,
e.maxCapacity AS event_maxCapacity,
e.maxCustomCapacity AS event_maxCustomCapacity,
e.maxExtraPeople AS event_maxExtraPeople,
e.price AS event_price,
e.description AS event_description,
e.color AS event_color,
e.show AS event_show,
e.notifyParticipants AS event_notifyParticipants,
e.locationId AS event_locationId,
e.customLocation AS event_customLocation,
e.customPricing AS event_customPricing,
e.parentId AS event_parentId,
e.created AS event_created,
e.settings AS event_settings,
e.zoomUserId AS event_zoomUserId,
e.translations AS event_translations,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.fullPayment AS event_fullPayment,
e.organizerId AS event_organizerId,
e.aggregatedPrice AS event_aggregatedPrice,
ep.id AS event_periodId,
ep.periodStart AS event_periodStart,
ep.periodEnd AS event_periodEnd,
ep.zoomMeeting AS event_periodZoomMeeting,
ep.lessonSpace AS event_periodLessonSpace,
ep.googleCalendarEventId AS event_googleCalendarEventId,
ep.googleMeetUrl AS event_googleMeetUrl,
ep.outlookCalendarEventId AS event_outlookCalendarEventId,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
ep.appleCalendarEventId AS event_appleCalendarEventId
";
$params = [
':customerBookingId' => $bookingId,
];
try {
$statement = $this->connection->prepare(
"SELECT
{$fields}
FROM {$customerBookingsEventsPeriods} cbe
INNER JOIN {$eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
INNER JOIN {$this->table} e ON e.id = ep.eventId
{$joins}
WHERE cbe.customerBookingId = :customerBookingId"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by booking id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $events */
$events = call_user_func([static::FACTORY, 'createCollection'], $rows);
return $events->length() ? $events->getItem($events->keys()[0]) : null;
}
/**
* @param array $ids
* @param array $criteria
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getByIdsWithEntities($ids, $criteria = [], $sort = null)
{
$params = [];
$where = [];
$fields = '';
$joins = '';
$orderBy = '';
if (!empty($criteria['fetchEventsPeriods'])) {
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$fields .= '
ep.id AS event_periodId,
ep.periodStart AS event_periodStart,
ep.periodEnd AS event_periodEnd,
ep.zoomMeeting AS event_periodZoomMeeting,
ep.lessonSpace AS event_periodLessonSpace,
ep.googleCalendarEventId AS event_googleCalendarEventId,
ep.googleMeetUrl AS event_googleMeetUrl,
ep.outlookCalendarEventId AS event_outlookCalendarEventId,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
ep.appleCalendarEventId AS event_appleCalendarEventId,
';
$joins .= "
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
";
$orderBy = $this->getOrderBy($sort);
}
if (!empty($criteria['fetchEventsLocation'])) {
$locationsTable = LocationsTable::getTableName();
$fields .= '
l.id AS location_id,
l.name AS location_name,
';
$joins .= "
LEFT JOIN {$locationsTable} l ON l.id = e.locationId
";
}
if (!empty($criteria['fetchEventsCoupons'])) {
$couponsTable = CouponsTable::getTableName();
$fields .= '
ec.id AS coupon_id,
ec.code AS coupon_code,
ec.discount AS coupon_discount,
ec.deduction AS coupon_deduction,
ec.limit AS coupon_limit,
ec.customerLimit AS coupon_customerLimit,
ec.status AS coupon_status,
';
$joins .= "
LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
";
}
if (!empty($criteria['fetchEventsTickets'])) {
$ticketsTable = EventsTicketsTable::getTableName();
$fields .= '
eti.id AS ticket_id,
eti.name AS ticket_name,
eti.enabled AS ticket_enabled,
eti.price AS ticket_price,
eti.spots AS ticket_spots,
eti.waitingListSpots AS ticket_waiting_list_spots,
eti.dateRanges AS ticket_dateRanges,
eti.translations AS ticket_translations,
';
$joins .= "
LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
";
$orderBy .= (!empty($orderBy) ? ',' : 'ORDER BY') . ' eti.id ASC';
}
if (!empty($criteria['fetchEventsTags'])) {
$tagsTable = EventsTagsTable::getTableName();
$fields .= '
eta.id AS event_tagId,
eta.name AS event_tagName,
';
$joins .= "
LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
";
}
if (!empty($criteria['fetchEventsImages'])) {
$galleriesTable = GalleriesTable::getTableName();
$fields .= '
eg.id AS gallery_id,
eg.pictureFullPath AS gallery_picture_full,
eg.pictureThumbPath AS gallery_picture_thumb,
eg.position AS gallery_position,
';
$joins .= "
LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
";
}
if (!empty($criteria['fetchEventsOrganizer'])) {
$usersTable = UsersTable::getTableName();
$joins .= "
LEFT JOIN {$usersTable} ou ON ou.id = e.organizerId
";
$fields .= '
ou.id AS organizer_id,
ou.firstName AS organizer_firstName,
ou.lastName AS organizer_lastName,
ou.email AS organizer_email,
ou.badgeId AS organizer_badgeId,
ou.pictureThumbPath AS organizer_pictureThumbPath,
ou.pictureFullPath AS organizer_pictureFullPath,
';
}
if (!empty($criteria['fetchEventsProviders'])) {
$eventsProvidersTable = EventsProvidersTable::getTableName();
$usersTable = UsersTable::getTableName();
$joins .= "
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
";
$fields .= '
pu.id AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.note AS provider_note,
pu.description AS provider_description,
pu.phone AS provider_phone,
pu.countryPhoneIso AS provider_countryPhoneIso,
pu.gender AS provider_gender,
pu.pictureFullPath AS provider_pictureFullPath,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.translations AS provider_translations,
pu.timeZone AS provider_timeZone,
pu.badgeId AS provider_badgeId,
';
}
$fields .= "
e.id AS event_id,
e.name AS event_name,
e.status AS event_status,
e.bookingOpens AS event_bookingOpens,
e.bookingCloses AS event_bookingCloses,
e.bookingOpensRec AS event_bookingOpensRec,
e.bookingClosesRec AS event_bookingClosesRec,
e.ticketRangeRec AS event_ticketRangeRec,
e.recurringCycle AS event_recurringCycle,
e.recurringOrder AS event_recurringOrder,
e.recurringInterval AS event_recurringInterval,
e.recurringMonthly AS event_recurringMonthly,
e.monthlyDate AS event_monthlyDate,
e.monthlyOnRepeat AS event_monthlyOnRepeat,
e.monthlyOnDay AS event_monthlyOnDay,
e.recurringUntil AS event_recurringUntil,
e.bringingAnyone AS event_bringingAnyone,
e.bookMultipleTimes AS event_bookMultipleTimes,
e.maxCapacity AS event_maxCapacity,
e.maxCustomCapacity AS event_maxCustomCapacity,
e.maxExtraPeople AS event_maxExtraPeople,
e.price AS event_price,
e.description AS event_description,
e.color AS event_color,
e.show AS event_show,
e.notifyParticipants AS event_notifyParticipants,
e.locationId AS event_locationId,
e.customLocation AS event_customLocation,
e.parentId AS event_parentId,
e.created AS event_created,
e.settings AS event_settings,
e.zoomUserId AS event_zoomUserId,
e.organizerId AS event_organizerId,
e.translations AS event_translations,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.fullPayment AS event_fullPayment,
e.customPricing AS event_customPricing,
e.closeAfterMin AS event_closeAfterMin,
e.closeAfterMinBookings AS event_closeAfterMinBookings,
e.aggregatedPrice AS event_aggregatedPrice,
e.pictureFullPath AS event_pictureFullPath,
e.pictureThumbPath AS event_pictureThumbPath
";
if (!empty($ids)) {
$queryIds = [];
foreach ($ids as $index => $value) {
$param = ':id' . $index;
$queryIds[] = $param;
$params[$param] = $value;
}
$where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
{$fields}
FROM {$this->table} e
{$joins}
{$where}
{$orderBy}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
/**
* @param array $criteria
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getBookingsByCriteria($criteria = [])
{
$params = [];
$where = [];
$fields = '';
$joins = '';
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
if (!empty($criteria['fetchApprovedBookings'])) {
$where[] = "cb.status = 'approved'";
}
if (!empty($criteria['customerId'])) {
$params[':customerId'] = $criteria['customerId'];
$where[] = 'cb.customerId = :customerId';
}
if (!empty($criteria['customerBookingStatus'])) {
$params[':customerBookingStatus'] = $criteria['customerBookingStatus'];
$where[] = 'cb.status = :customerBookingStatus';
}
if (!empty($criteria['customerBookingId'])) {
$params[':customerBookingId'] = $criteria['customerBookingId'];
$where[] = 'cb.id = :customerBookingId';
}
if (!empty($criteria['fetchBookingsPayments'])) {
$paymentsTable = PaymentsTable::getTableName();
$fields .= '
p.id AS payment_id,
p.amount AS payment_amount,
p.dateTime AS payment_dateTime,
p.created AS payment_created,
p.status AS payment_status,
p.gateway AS payment_gateway,
p.gatewayTitle AS payment_gatewayTitle,
p.transactionId AS payment_transactionId,
p.data AS payment_data,
p.wcOrderId AS payment_wcOrderId,
p.wcOrderItemId AS payment_wcOrderItemId,
p.invoiceNumber AS payment_invoiceNumber,
';
$joins .= "
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
";
}
if (!empty($criteria['fetchBookingsCoupons'])) {
$couponsTable = CouponsTable::getTableName();
$fields .= '
c.id AS coupon_id,
c.code AS coupon_code,
c.discount AS coupon_discount,
c.deduction AS coupon_deduction,
c.limit AS coupon_limit,
c.customerLimit AS coupon_customerLimit,
c.status AS coupon_status,
';
$joins .= "
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
";
}
if (!empty($criteria['fetchBookingsUsers'])) {
$usersTable = UsersTable::getTableName();
$fields .= '
cu.id AS customer_id,
cu.type AS customer_type,
cu.firstName AS customer_firstName,
cu.lastName AS customer_lastName,
cu.email AS customer_email,
cu.note AS customer_note,
cu.phone AS customer_phone,
cu.countryPhoneIso AS customer_countryPhoneIso,
cu.gender AS customer_gender,
cu.birthday AS customer_birthday,
cu.customFields AS customer_customFields,
';
$joins .= "
INNER JOIN {$usersTable} cu ON cu.id = cb.customerId
";
}
if (!empty($criteria['fetchBookingsTickets'])) {
$bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$fields .= '
cbt.id AS booking_ticket_id,
cbt.eventTicketId AS booking_ticket_eventTicketId,
cbt.price AS booking_ticket_price,
cbt.persons AS booking_ticket_persons,
';
$joins .= "
LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
";
}
$fields .= '
ep.eventId AS eventId,
cb.id AS booking_id,
cb.appointmentId AS booking_appointmentId,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.tax AS booking_tax,
cb.persons AS booking_persons,
cb.couponId AS booking_couponId,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.utcOffset AS booking_utcOffset,
cb.token AS booking_token,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.tax AS booking_tax,
cb.qrCodes AS booking_qrCodes,
cb.ivyEntryId AS booking_ivyEntryId
';
if (!empty($criteria['ids'])) {
$queryIds = [];
foreach ($criteria['ids'] as $index => $value) {
$param = ':id' . $index;
$queryIds[] = $param;
$params[$param] = $value;
}
$where[] = 'ep.eventId IN (' . implode(', ', $queryIds) . ')';
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
{$fields}
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
{$joins}
{$where}
ORDER BY cb.id"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$reformattedData = [];
foreach ($rows as $row) {
if (empty($reformattedData[$row['eventId']])) {
$reformattedData[$row['eventId']] = [];
}
$reformattedData[$row['eventId']][] = $row;
}
$result = new Collection();
foreach ($reformattedData as $eventId => $bookingsData) {
$reformattedBookingsData = CustomerBookingFactory::reformat($bookingsData);
$eventBookings = new Collection();
foreach ($reformattedBookingsData as $bookingId => $data) {
$eventBookings->addItem(CustomerBookingFactory::create($data), $bookingId);
}
$result->addItem($eventBookings, $eventId);
}
return $result;
}
/**
* @param Event $event
* @param array $booking
* @param array $limitPerCustomer
* @return int
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getRelevantBookingsCount($event, $booking, $limitPerCustomer)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$params = [
':customerId' => $booking['customerId']
];
$paymentTableJoin = '';
$compareToDate = 'ep.periodStart';
if ($limitPerCustomer['from'] === 'bookingDate') {
$eventStartDate =
(clone $event->getPeriods()->getItems()[0]->getPeriodStart()->getValue())->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
} else {
$paymentTableJoin = 'INNER JOIN ' . PaymentsTable::getTableName() . ' p ON p.customerBookingId = cb.id';
$eventStartDate = DateTimeService::getNowDateTimeObject()->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
$compareToDate = 'p.created';
}
$intervalString = "interval " . $limitPerCustomer['period'] . " " . $limitPerCustomer['timeFrame'];
$where = "(STR_TO_DATE('" . $eventStartDate . "', '%Y-%m-%d %H:%i:%s') BETWEEN " .
"(" . $compareToDate . " - " . $intervalString . " + interval 1 second)" .
" AND (" .
$compareToDate . " + " . $intervalString . " - interval 1 second))";
try {
$statement = $this->connection->prepare(
"SELECT COUNT(DISTINCT cb.id) AS count FROM
{$this->table} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
{$paymentTableJoin}
WHERE cb.customerId = :customerId AND {$where} AND e.status = 'approved' AND cb.status = 'approved'
"
);
$statement->execute($params);
$rows = $statement->fetch()['count'];
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $rows;
}
/**
* @param array $ids
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getEventsSpotsCount($ids)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ") AND cb.status IN ('approved', 'pending', 'waiting')";
try {
$statement = $this->connection->prepare(
"SELECT
t.eventId, t.status, SUM(t.persons) as places
FROM (
SELECT
ep.eventId,
cb.id,
cb.status,
cb.persons
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
{$where}
GROUP BY ep.eventId, cb.id, cb.status
) t
GROUP BY t.eventId, t.status"
);
$statement->execute();
$rows = $statement->fetchAll();
$result = [];
foreach ($rows as $row) {
$result[$row['eventId']][$row['status']] =
$row['places'] +
(!empty($result[$row['eventId']][$row['status']]) ? $result[$row['eventId']][$row['status']] : 0);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
}
return $result;
}
/**
* @param array $ids
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getEventsTicketsCount($ids)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$customerBookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ") AND cb.status IN ('approved', 'pending', 'waiting')";
try {
$statement = $this->connection->prepare(
"SELECT
t.eventId, t.status, t.eventTicketId, SUM(t.persons) as places
FROM (
SELECT
ep.eventId,
cb.id,
cb.status,
cbt.eventTicketId,
cbt.persons
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
INNER JOIN {$customerBookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
{$where}
GROUP BY ep.eventId, cb.id, cb.status, cbt.eventTicketId, cbt.persons
) t
GROUP BY t.eventId, t.status, t.eventTicketId"
);
$statement->execute();
$rows = $statement->fetchAll();
$result = [];
foreach ($rows as $row) {
$result[$row['eventId']][$row['eventTicketId']][$row['status']] =
$row['places'] +
(
!empty($result[$row['eventId']][$row['eventTicketId']][$row['status']])
? $result[$row['eventId']][$row['eventTicketId']][$row['status']]
: 0
);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
}
return $result;
}
/**
* @param int $id
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getEventsPaymentsSummary($id)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
try {
$statementAmount = $this->connection->prepare(
"SELECT
SUM(t.amount) as amount
FROM (
SELECT
cb.id,
p.amount
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
GROUP BY cb.id, p.amount
) t"
);
$statementAmount->execute([':eventId' => $id]);
$rowAmount = $statementAmount->fetch();
$statementStatus = $this->connection->prepare(
"SELECT
t.status, COUNT(t.status) as counter
FROM (
SELECT
cb.id,
p.status
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
INNER JOIN (
SELECT customerBookingId, MAX(id) as maxId
FROM {$paymentsTable}
GROUP BY customerBookingId
) pm ON p.customerBookingId = pm.customerBookingId AND p.id = pm.maxId
WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
GROUP BY cb.id, p.status
) t
GROUP BY t.status"
);
$statementStatus->execute([':eventId' => $id]);
$rowsStatus = $statementStatus->fetchAll();
$statementGateway = $this->connection->prepare(
"SELECT
t.gateway, COUNT(t.gateway) as counter
FROM (
SELECT
p.id,
p.gateway
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
INNER JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
WHERE ep.eventId = :eventId AND cb.status IN ('approved', 'pending', 'waiting')
GROUP BY p.id, p.gateway
) t
GROUP BY t.gateway"
);
$statementGateway->execute([':eventId' => $id]);
$rowsGateway = $statementGateway->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
}
return [
'amount' => $rowAmount['amount'] ? floatval($rowAmount['amount']) : 0,
'method' => array_map(
'intval',
array_column($rowsGateway, 'counter', 'gateway')
),
'status' => array_map(
'intval',
array_column($rowsStatus, 'counter', 'status')
),
];
}
/**
* @param array $ids
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getEventsBookingsStatusesCount($ids)
{
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$customerBookingsTable = CustomerBookingsTable::getTableName();
$where = "WHERE ep.eventId IN (" . implode(', ', $ids) . ")";
try {
$statement = $this->connection->prepare(
"SELECT
t.eventId, t.status, COUNT(t.status) as counter
FROM (
SELECT
ep.eventId,
cb.id,
cb.status
FROM {$eventsPeriodsTable} ep
INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
{$where}
GROUP BY ep.eventId, cb.id, cb.status
) t
GROUP BY t.eventId, t.status"
);
$statement->execute();
$rows = $statement->fetchAll();
$result = [];
foreach ($rows as $row) {
$result[$row['eventId']][$row['status']] =
$row['counter'] +
(!empty($result[$row['eventId']][$row['status']]) ? $result[$row['eventId']][$row['status']] : 0);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
}
return $result;
}
/**
* @param string $sort
* @return string
*/
private function getOrderBy($sort)
{
$order = "ORDER BY ep.periodStart";
if ($sort) {
$column = $sort[0] === '-' ? substr($sort, 1) : $sort;
$orderColumn = 'ep.periodStart';
switch ($column) {
case 'name':
$orderColumn = 'e.name';
break;
case 'id':
$orderColumn = 'e.id';
break;
case 'bookingOpens':
$orderColumn = 'COALESCE(e.bookingOpens, e.created)';
break;
case 'bookingCloses':
$orderColumn = 'COALESCE(e.bookingCloses, ep.periodStart)';
break;
}
$orderDirection = $sort[0] === '-' ? 'DESC' : 'ASC';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
return $order;
}
/**
* @param Event $event
* @param int $visible
* @param boolean $applyGlobally
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function toggleEventVisibility($event, $visible, $applyGlobally)
{
$params = [
':show' => $visible
];
if ($applyGlobally) {
$params[':id1'] = $event->getId()->getValue();
$params[':id2'] = $event->getId()->getValue();
$where = "WHERE id = :id1 OR parentId = :id2";
if ($event->getParentId()) {
$params[':id3'] = $event->getId()->getValue();
$params[':parentId'] = $event->getParentId()->getValue();
$where .= ' OR (id > :id3 AND parentId = :parentId)';
}
} else {
$params[':id'] = $event->getId()->getValue();
$where = 'WHERE id = :id';
}
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
`show` = :show
$where"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to update visibility in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $organizerId
*/
public function removeOrganizerFromEvents($organizerId)
{
$params = [
':organizerId' => $organizerId
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
organizerId = NULL
WHERE organizerId = :organizerId"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to remove organizer in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
}