PluginProbe ʕ •ᴥ•ʔ
Booking for Appointments and Events Calendar – Amelia / 1.2.5
Booking for Appointments and Events Calendar – Amelia v1.2.5
2.4.9 2.4.8 2.4.7 2.4.6 2.4.5 2.4.4 2.4.3 2.4.2 2.4.1 2.4 trunk 1.2.1 1.2.10 1.2.11 1.2.12 1.2.13 1.2.14 1.2.15 1.2.16 1.2.17 1.2.18 1.2.19 1.2.2 1.2.20 1.2.21 1.2.22 1.2.23 1.2.24 1.2.25 1.2.26 1.2.27 1.2.28 1.2.29 1.2.3 1.2.30 1.2.31 1.2.32 1.2.33 1.2.34 1.2.35 1.2.36 1.2.37 1.2.38 1.2.4 1.2.5 1.2.6 1.2.7 1.2.8 1.2.9 2.0 2.0.1 2.0.2 2.1 2.1.1 2.1.2 2.1.3 2.2 2.2.1 2.3
ameliabooking / src / Infrastructure / Repository / Booking / Event / EventRepository.php
ameliabooking / src / Infrastructure / Repository / Booking / Event Last commit date
CustomerBookingEventPeriodRepository.php 1 year ago CustomerBookingEventTicketRepository.php 1 year ago EventPeriodsRepository.php 1 year ago EventProvidersRepository.php 1 year ago EventRepository.php 1 year ago EventTagsRepository.php 1 year ago EventTicketRepository.php 1 year ago
EventRepository.php
2398 lines
1 <?php
2
3 namespace AmeliaBooking\Infrastructure\Repository\Booking\Event;
4
5 use AmeliaBooking\Domain\Collection\Collection;
6 use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
7 use AmeliaBooking\Domain\Entity\Booking\Event\Event;
8 use AmeliaBooking\Domain\Factory\Booking\Appointment\CustomerBookingFactory;
9 use AmeliaBooking\Domain\Factory\Booking\Event\EventFactory;
10 use AmeliaBooking\Domain\Repository\Booking\Event\EventRepositoryInterface;
11 use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
12 use AmeliaBooking\Domain\ValueObjects\String\Status;
13 use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
14 use AmeliaBooking\Infrastructure\Licence;
15 use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
16 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsTable;
17 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToEventsPeriodsTable;
18 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingToEventsTicketsTable;
19 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
20 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsProvidersTable;
21 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTagsTable;
22 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTicketsTable;
23 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
24 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsToEventsTable;
25 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Gallery\GalleriesTable;
26 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Payment\PaymentsTable;
27 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersGoogleCalendarTable;
28 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersOutlookCalendarTable;
29 use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;
30
31 /**
32 * Class EventRepository
33 *
34 * @package AmeliaBooking\Infrastructure\Repository\Booking\Event
35 */
36 class EventRepository extends AbstractRepository implements EventRepositoryInterface
37 {
38
39 const FACTORY = EventFactory::class;
40
41 /**
42 * @param Event $entity
43 *
44 * @return bool
45 * @throws QueryExecutionException
46 */
47 public function add($entity)
48 {
49 $data = $entity->toArray();
50
51 $params = [
52 ':bookingOpens' => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
53 ':bookingCloses' => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
54 ':bookingOpensRec' => $data['bookingOpensRec'],
55 ':bookingClosesRec' => $data['bookingClosesRec'],
56 ':status' => $data['status'],
57 ':name' => $data['name'],
58 ':description' => $data['description'],
59 ':color' => $data['color'],
60 ':price' => $data['price'],
61 ':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
62 ':bookMultipleTimes' => $data['bookMultipleTimes'] ? 1 : 0,
63 ':maxCapacity' => $data['maxCapacity'],
64 ':maxCustomCapacity' => $data['maxCustomCapacity'],
65 ':maxExtraPeople' => $data['maxExtraPeople'],
66 ':show' => $data['show'] ? 1 : 0,
67 ':notifyParticipants' => $data['notifyParticipants'],
68 ':customLocation' => $data['customLocation'],
69 ':parentId' => $data['parentId'],
70 ':created' => $data['created'],
71 ':closeAfterMin' => $data['closeAfterMin'],
72 ':closeAfterMinBookings' => $data['closeAfterMinBookings'] ? 1 : 0,
73 ':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0
74 ];
75
76 $additionalData = Licence\DataModifier::getEventRepositoryData($data);
77
78 $params = array_merge($params, $additionalData['values'], $additionalData['addValues']);
79
80 try {
81 $statement = $this->connection->prepare(
82 "INSERT INTO {$this->table}
83 (
84 {$additionalData['columns']}
85 `bookingOpens`,
86 `bookingCloses`,
87 `bookingOpensRec`,
88 `bookingClosesRec`,
89 `status`,
90 `name`,
91 `description`,
92 `color`,
93 `price`,
94 `bringingAnyone`,
95 `bookMultipleTimes`,
96 `maxCapacity`,
97 `maxCustomCapacity`,
98 `maxExtraPeople`,
99 `show`,
100 `notifyParticipants`,
101 `customLocation`,
102 `parentId`,
103 `created`,
104 `closeAfterMin`,
105 `closeAfterMinBookings`,
106 `aggregatedPrice`
107 )
108 VALUES (
109 {$additionalData['placeholders']}
110 :bookingOpens,
111 :bookingCloses,
112 :bookingOpensRec,
113 :bookingClosesRec,
114 :status,
115 :name,
116 :description,
117 :color,
118 :price,
119 :bringingAnyone,
120 :bookMultipleTimes,
121 :maxCapacity,
122 :maxCustomCapacity,
123 :maxExtraPeople,
124 :show,
125 :notifyParticipants,
126 :customLocation,
127 :parentId,
128 :created,
129 :closeAfterMin,
130 :closeAfterMinBookings,
131 :aggregatedPrice
132 )"
133 );
134
135 $res = $statement->execute($params);
136
137 if (!$res) {
138 throw new QueryExecutionException('Unable to add data in ' . __CLASS__);
139 }
140
141 return $this->connection->lastInsertId();
142 } catch (\Exception $e) {
143 throw new QueryExecutionException('Unable to add data in ' . __CLASS__, $e->getCode(), $e);
144 }
145 }
146
147 /**
148 * @param int $id
149 * @param Event $entity
150 *
151 * @return mixed
152 * @throws QueryExecutionException
153 */
154 public function update($id, $entity)
155 {
156 $data = $entity->toArray();
157
158 $params = [
159 ':id' => $id,
160 ':bookingOpens' => $data['bookingOpens'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingOpens']) : null,
161 ':bookingCloses' => $data['bookingCloses'] ? DateTimeService::getCustomDateTimeInUtc($data['bookingCloses']) : null,
162 ':bookingOpensRec' => $data['bookingOpensRec'],
163 ':bookingClosesRec' => $data['bookingClosesRec'],
164 ':status' => $data['status'],
165 ':name' => $data['name'],
166 ':description' => $data['description'],
167 ':color' => $data['color'],
168 ':price' => $data['price'],
169 ':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
170 ':bookMultipleTimes' => $data['bookMultipleTimes'] ? 1 : 0,
171 ':maxCapacity' => $data['maxCapacity'],
172 ':maxCustomCapacity' => $data['maxCustomCapacity'],
173 ':maxExtraPeople' => $data['maxExtraPeople'],
174 ':show' => $data['show'] ? 1 : 0,
175 ':notifyParticipants' => $data['notifyParticipants'] ? 1 : 0,
176 ':customLocation' => $data['customLocation'],
177 ':parentId' => $data['parentId'],
178 ':closeAfterMin' => $data['closeAfterMin'],
179 ':closeAfterMinBookings' => $data['closeAfterMinBookings'] ? 1 : 0,
180 ':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0
181 ];
182
183 $additionalData = Licence\DataModifier::getEventRepositoryData($data);
184
185 $params = array_merge($params, $additionalData['values']);
186
187 try {
188 $statement = $this->connection->prepare(
189 "UPDATE {$this->table}
190 SET
191 {$additionalData['columnsPlaceholders']}
192 `bookingOpens` = :bookingOpens,
193 `bookingCloses` = :bookingCloses,
194 `bookingOpensRec` = :bookingOpensRec,
195 `bookingClosesRec` = :bookingClosesRec,
196 `status` = :status,
197 `name` = :name,
198 `description` = :description,
199 `color` = :color,
200 `price` = :price,
201 `bringingAnyone` = :bringingAnyone,
202 `bookMultipleTimes` = :bookMultipleTimes,
203 `maxCapacity` = :maxCapacity,
204 `maxCustomCapacity` = :maxCustomCapacity,
205 `maxExtraPeople` = :maxExtraPeople,
206 `show` = :show,
207 `notifyParticipants` = :notifyParticipants,
208 `customLocation` = :customLocation,
209 `parentId` = :parentId,
210 `closeAfterMin` = :closeAfterMin,
211 `closeAfterMinBookings` = :closeAfterMinBookings,
212 `aggregatedPrice` = :aggregatedPrice
213 WHERE id = :id"
214 );
215
216 $res = $statement->execute($params);
217
218 if (!$res) {
219 throw new QueryExecutionException('Unable to save data in ' . __CLASS__);
220 }
221
222 return $res;
223 } catch (\Exception $e) {
224 throw new QueryExecutionException('Unable to save data in ' . __CLASS__, $e->getCode(), $e);
225 }
226 }
227
228 /**
229 * @param int $id
230 * @param int $status
231 *
232 * @return mixed
233 * @throws QueryExecutionException
234 */
235 public function updateStatusById($id, $status)
236 {
237 $params = [
238 ':id' => $id,
239 ':status' => $status
240 ];
241
242 try {
243 $statement = $this->connection->prepare(
244 "UPDATE {$this->table}
245 SET
246 `status` = :status
247 WHERE id = :id"
248 );
249
250 $res = $statement->execute($params);
251
252 if (!$res) {
253 throw new QueryExecutionException('Unable to save data in ' . __CLASS__);
254 }
255
256 return $res;
257 } catch (\Exception $e) {
258 throw new QueryExecutionException('Unable to save data in ' . __CLASS__, $e->getCode(), $e);
259 }
260 }
261
262 /**
263 * @param int $id
264 * @param int $parentId
265 *
266 * @return mixed
267 * @throws QueryExecutionException
268 */
269 public function updateParentId($id, $parentId)
270 {
271 $params = [
272 ':id' => $id,
273 ':parentId' => $parentId,
274 ];
275
276 try {
277 $statement = $this->connection->prepare(
278 "UPDATE {$this->table}
279 SET
280 `parentId` = :parentId
281 WHERE id = :id"
282 );
283
284 $res = $statement->execute($params);
285
286 if (!$res) {
287 throw new QueryExecutionException('Unable to save data in ' . __CLASS__);
288 }
289
290 return $res;
291 } catch (\Exception $e) {
292 throw new QueryExecutionException('Unable to save data in ' . __CLASS__, $e->getCode(), $e);
293 }
294 }
295
296 /**
297 * @param array $criteria
298 *
299 * @return Collection
300 * @throws QueryExecutionException
301 * @throws InvalidArgumentException
302 */
303 public function getFiltered($criteria)
304 {
305 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
306 $eventsTagsTable = EventsTagsTable::getTableName();
307 $eventsTicketTable = EventsTicketsTable::getTableName();
308
309 $galleriesTable = GalleriesTable::getTableName();
310 $paymentsTable = PaymentsTable::getTableName();
311 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
312 $customerBookingsTable = CustomerBookingsTable::getTableName();
313 $eventsProvidersTable = EventsProvidersTable::getTableName();
314 $usersTable = UsersTable::getTableName();
315
316 $params = [];
317 $where = [];
318
319 if (!empty($criteria['ids'])) {
320 $queryIds = [];
321
322 foreach ((array)$criteria['ids'] as $index => $value) {
323 $param = ':id' . $index;
324 $queryIds[] = $param;
325 $params[$param] = $value;
326 }
327
328 $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
329 }
330
331 if (isset($criteria['parentId'])) {
332 $params[':parentId'] = $criteria['parentId'];
333 $params[':originParentId'] = $criteria['parentId'];
334
335 $where[] = 'e.parentId = :parentId OR e.id = :originParentId';
336 }
337
338 if (isset($criteria['search'])) {
339 $params[':search'] = "%{$criteria['search']}%";
340
341 $where[] = 'e.name LIKE :search';
342 }
343
344 if (isset($criteria['status'])) {
345 $params[':status'] = $criteria['status'];
346
347 $where[] = 'e.status = :status';
348 }
349
350 if (!empty($criteria['dates'])) {
351 if (isset($criteria['dates'][0], $criteria['dates'][1])) {
352 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') BETWEEN :eventFrom AND :eventTo)";
353 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
354 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
355 } elseif (isset($criteria['dates'][0])) {
356 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') >= :eventFrom OR (DATE_FORMAT(ep.periodEnd, '%Y-%m-%d %H:%i:%s') >= :eventTo))";
357 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
358 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
359 } elseif (isset($criteria['dates'][1])) {
360 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') <= :eventTo)";
361 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
362 } else {
363 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') > :eventFrom)";
364 $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
365 }
366 }
367
368 if (!empty($criteria['locations'])) {
369 $queryLocations = [];
370
371 foreach ((array)$criteria['locations'] as $index => $value) {
372 $param = ':location' . $index;
373 $queryLocations[] = $param;
374 $params[$param] = $value;
375 }
376
377 $where[] = 'e.locationId IN (' . implode(', ', $queryLocations) . ')';
378 }
379
380 $providerJoin = '';
381 $providerFields = '';
382
383 if (!empty($criteria['providers']) || !empty($criteria['allProviders'])) {
384 $joinType = !empty($criteria['providers']) ? 'INNER' : 'LEFT';
385
386 $providerJoin = "
387 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
388 $joinType JOIN {$usersTable} pu ON pu.id = epr.userId OR pu.id = e.organizerId";
389
390 $queryProviders = [];
391
392 $providerFields = '
393 pu.id AS provider_id,
394 pu.firstName AS provider_firstName,
395 pu.lastName AS provider_lastName,
396 pu.email AS provider_email,
397 pu.note AS provider_note,
398 pu.description AS provider_description,
399 pu.phone AS provider_phone,
400 pu.gender AS provider_gender,
401 pu.pictureFullPath AS provider_pictureFullPath,
402 pu.pictureThumbPath AS provider_pictureThumbPath,
403 pu.translations AS provider_translations,
404 ';
405
406 if (!empty($criteria['providers'])) {
407 foreach ((array)$criteria['providers'] as $index => $value) {
408 $param = ':provider' . $index;
409
410 $queryProviders[] = $param;
411
412 $params[$param] = $value;
413 }
414
415 $where1 = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
416
417
418 $queryProviders = [];
419 foreach ((array)$criteria['providers'] as $index => $value) {
420 $param = ':organizer' . $index;
421 $queryProviders[] = $param;
422 $params[$param] = $value;
423 }
424
425 $where2 = 'e.organizerId IN (' . implode(', ', $queryProviders) . ')';
426
427 $where[] = '(' . $where1 . ' OR ' . $where2 . ')';
428 }
429 }
430
431 if (isset($criteria['tag'])) {
432 $params[':tag'] = $criteria['tag'];
433
434 $tagJoin = "INNER JOIN {$eventsTagsTable} et ON et.eventId = e.id AND et.name = :tag";
435 } else {
436 $tagJoin = "LEFT JOIN {$eventsTagsTable} et ON et.eventId = e.id";
437 }
438
439 if (isset($criteria['bookingCouponId'])) {
440 $where[] = "cb.couponId = {$criteria['bookingCouponId']}";
441 }
442
443 $paymentJoin = '';
444 $paymentFields = '';
445
446 if (!empty($criteria['fetchPayments'])) {
447 $paymentFields = '
448 p.id AS payment_id,
449 p.amount AS payment_amount,
450 p.dateTime AS payment_dateTime,
451 p.status AS payment_status,
452 p.gateway AS payment_gateway,
453 p.gatewayTitle AS payment_gatewayTitle,
454 p.transactionId AS payment_transactionId,
455 p.data AS payment_data,
456 p.wcOrderId AS payment_wcOrderId,
457 p.wcOrderItemId AS payment_wcOrderItemId,
458 ';
459
460 $paymentJoin = "LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id";
461 }
462
463 $couponJoin = '';
464 $couponFields = '';
465
466 if (!empty($criteria['fetchCoupons'])) {
467 $couponsTable = CouponsTable::getTableName();
468
469 $couponFields = '
470 c.id AS coupon_id,
471 c.code AS coupon_code,
472 c.discount AS coupon_discount,
473 c.deduction AS coupon_deduction,
474 c.limit AS coupon_limit,
475 c.customerLimit AS coupon_customerLimit,
476 c.status AS coupon_status,
477 ';
478
479 $couponJoin = "LEFT JOIN {$couponsTable} c ON c.id = cb.couponId";
480 }
481
482 if (!empty($criteria['customerId'])) {
483 $params[':customerId'] = $criteria['customerId'];
484
485 $where[] = 'cb.customerId = :customerId';
486 }
487
488 if (array_key_exists('bookingStatus', $criteria)) {
489 $where[] = 'cb.status = :bookingStatus';
490 $params[':bookingStatus'] = $criteria['bookingStatus'];
491 }
492
493 if (array_key_exists('show', $criteria)) {
494 $where[] = 'e.show = :show';
495
496 $params[':show'] = $criteria['show'];
497 }
498
499 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
500
501 $limit = $this->getLimit(
502 !empty($criteria['page']) ? (int)$criteria['page'] : 0,
503 !empty($criteria['itemsPerPage']) ? (int)$criteria['itemsPerPage'] : 0
504 );
505
506 $groupBy = '';
507 if (!empty($limit)) {
508 $groupBy = 'GROUP BY e.id';
509 }
510
511 try {
512 $statement = $this->connection->prepare(
513 "SELECT
514 e.id AS event_id,
515 e.name AS event_name,
516 e.status AS event_status,
517 e.bookingOpens AS event_bookingOpens,
518 e.bookingCloses AS event_bookingCloses,
519 e.bookingOpensRec AS event_bookingOpensRec,
520 e.bookingClosesRec AS event_bookingClosesRec,
521 e.recurringCycle AS event_recurringCycle,
522 e.recurringOrder AS event_recurringOrder,
523 e.recurringInterval AS event_recurringInterval,
524 e.recurringMonthly AS event_recurringMonthly,
525 e.monthlyDate AS event_monthlyDate,
526 e.monthlyOnRepeat AS event_monthlyOnRepeat,
527 e.monthlyOnDay AS event_monthlyOnDay,
528 e.recurringUntil AS event_recurringUntil,
529 e.bringingAnyone AS event_bringingAnyone,
530 e.bookMultipleTimes AS event_bookMultipleTimes,
531 e.maxCapacity AS event_maxCapacity,
532 e.maxCustomCapacity AS event_maxCustomCapacity,
533 e.maxExtraPeople AS event_maxExtraPeople,
534 e.price AS event_price,
535 e.description AS event_description,
536 e.color AS event_color,
537 e.show AS event_show,
538 e.locationId AS event_locationId,
539 e.customLocation AS event_customLocation,
540 e.parentId AS event_parentId,
541 e.created AS event_created,
542 e.notifyParticipants AS event_notifyParticipants,
543 e.settings AS event_settings,
544 e.zoomUserId AS event_zoomUserId,
545 e.organizerId AS event_organizerId,
546 e.translations AS event_translations,
547 e.deposit AS event_deposit,
548 e.depositPayment AS event_depositPayment,
549 e.depositPerPerson AS event_depositPerPerson,
550 e.fullPayment AS event_fullPayment,
551 e.customPricing AS event_customPricing,
552 e.closeAfterMin AS event_closeAfterMin,
553 e.closeAfterMinBookings AS event_closeAfterMinBookings,
554 e.aggregatedPrice AS event_aggregatedPrice,
555
556 ep.id AS event_periodId,
557 ep.periodStart AS event_periodStart,
558 ep.periodEnd AS event_periodEnd,
559 ep.zoomMeeting AS event_periodZoomMeeting,
560 ep.lessonSpace AS event_periodLessonSpace,
561 ep.googleCalendarEventId AS event_googleCalendarEventId,
562 ep.googleMeetUrl AS event_googleMeetUrl,
563 ep.outlookCalendarEventId AS event_outlookCalendarEventId,
564
565 et.id AS event_tagId,
566 et.name AS event_tagName,
567
568 cb.id AS booking_id,
569 cb.customerId AS booking_customerId,
570 cb.status AS booking_status,
571 cb.price AS booking_price,
572 cb.persons AS booking_persons,
573 cb.customFields AS booking_customFields,
574 cb.info AS booking_info,
575 cb.token AS booking_token,
576 cb.aggregatedPrice AS booking_aggregatedPrice,
577 cb.couponId AS booking_couponId,
578
579 cu.id AS customer_id,
580 cu.firstName AS customer_firstName,
581 cu.lastName AS customer_lastName,
582 cu.email AS customer_email,
583 cu.note AS customer_note,
584 cu.phone AS customer_phone,
585 cu.gender AS customer_gender,
586
587 t.id AS ticket_id,
588 t.name AS ticket_name,
589 t.enabled AS ticket_enabled,
590 t.price AS ticket_price,
591 t.spots AS ticket_spots,
592 t.dateRanges AS ticket_dateRanges,
593 t.translations AS ticket_translations,
594
595 {$couponFields}
596
597 {$paymentFields}
598
599 {$providerFields}
600
601 g.id AS gallery_id,
602 g.pictureFullPath AS gallery_picture_full,
603 g.pictureThumbPath AS gallery_picture_thumb,
604 g.position AS gallery_position
605 FROM {$this->table} e
606 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
607 LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
608 LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
609 LEFT JOIN {$usersTable} cu ON cu.id = cb.customerId
610 LEFT JOIN {$galleriesTable} g ON g.entityId = e.id AND g.entityType = 'event'
611 LEFT JOIN {$eventsTicketTable} t ON t.eventId = e.id
612 {$providerJoin}
613 {$couponJoin}
614 {$paymentJoin}
615 {$tagJoin}
616 {$where}
617 {$groupBy}
618 ORDER BY ep.periodStart
619 {$limit}"
620 );
621
622 $statement->execute($params);
623
624 $rows = $statement->fetchAll();
625 } catch (\Exception $e) {
626 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
627 }
628
629 return call_user_func([static::FACTORY, 'createCollection'], $rows);
630 }
631
632 /**
633 * @param array $criteria
634 *
635 * @return Collection
636 * @throws QueryExecutionException
637 * @throws InvalidArgumentException
638 */
639 public function getProvidersEvents($criteria)
640 {
641 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
642 $eventsProvidersTable = EventsProvidersTable::getTableName();
643 $usersTable = UsersTable::getTableName();
644
645 $params = [];
646 $where = [];
647
648 if (!empty($criteria['dates'])) {
649 if (isset($criteria['dates'][0], $criteria['dates'][1])) {
650 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') BETWEEN :eventFrom AND :eventTo)";
651 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
652 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
653 } elseif (isset($criteria['dates'][0])) {
654 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') >= :eventFrom)";
655 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
656 } elseif (isset($criteria['dates'][1])) {
657 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') <= :eventTo)";
658 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
659 } else {
660 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') > :eventFrom)";
661 $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
662 }
663 }
664
665 if (!empty($criteria['providers'])) {
666 $queryProviders = [];
667
668 foreach ((array)$criteria['providers'] as $index => $value) {
669 $param = ':provider' . $index;
670 $queryProviders[] = $param;
671 $params[$param] = $value;
672 }
673
674 $where[] = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
675 }
676
677 if (!empty($criteria['status'])) {
678 $params[':status'] = $criteria['status'];
679
680 $where[] = 'e.status = :status';
681 }
682
683 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
684
685 try {
686 $statement = $this->connection->prepare(
687 "SELECT
688 e.id AS event_id,
689 e.name AS event_name,
690 e.status AS event_status,
691 e.bookingOpens AS event_bookingOpens,
692 e.bookingCloses AS event_bookingCloses,
693 e.recurringCycle AS event_recurringCycle,
694 e.recurringOrder AS event_recurringOrder,
695 e.recurringInterval AS event_recurringInterval,
696 e.recurringUntil AS event_recurringUntil,
697 e.recurringMonthly AS event_recurringMonthly,
698 e.monthlyDate AS event_monthlyDate,
699 e.monthlyOnRepeat AS event_monthlyOnRepeat,
700 e.monthlyOnDay AS event_monthlyOnDay,
701 e.bringingAnyone AS event_bringingAnyone,
702 e.bookMultipleTimes AS event_bookMultipleTimes,
703 e.maxCapacity AS event_maxCapacity,
704 e.maxCustomCapacity AS event_maxCustomCapacity,
705 e.maxExtraPeople AS event_maxExtraPeople,
706 e.price AS event_price,
707 e.description AS event_description,
708 e.color AS event_color,
709 e.show AS event_show,
710 e.locationId AS event_locationId,
711 e.customLocation AS event_customLocation,
712 e.parentId AS event_parentId,
713 e.created AS event_created,
714 e.notifyParticipants AS event_notifyParticipants,
715 e.translations AS event_translations,
716 e.deposit AS event_deposit,
717 e.depositPayment AS event_depositPayment,
718 e.depositPerPerson AS event_depositPerPerson,
719 e.fullPayment AS event_fullPayment,
720 e.customPricing AS event_customPricing,
721 e.aggregatedPrice AS event_aggregatedPrice,
722
723 ep.id AS event_periodId,
724 ep.periodStart AS event_periodStart,
725 ep.periodEnd AS event_periodEnd,
726
727 pu.id AS provider_id,
728 pu.firstName AS provider_firstName,
729 pu.lastName AS provider_lastName,
730 pu.email AS provider_email,
731 pu.note AS provider_note,
732 pu.description AS provider_description,
733 pu.phone AS provider_phone,
734 pu.gender AS provider_gender,
735 pu.pictureFullPath AS provider_pictureFullPath,
736 pu.pictureThumbPath AS provider_pictureThumbPath,
737 pu.translations AS provider_translations
738 FROM {$this->table} e
739 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
740 INNER JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
741 INNER JOIN {$usersTable} pu ON pu.id = epr.userId
742 {$where}
743 ORDER BY ep.periodStart"
744 );
745
746 $statement->execute($params);
747
748 $rows = $statement->fetchAll();
749 } catch (\Exception $e) {
750 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
751 }
752
753 return call_user_func([static::FACTORY, 'createCollection'], $rows);
754 }
755
756 /**
757 * @param array $criteria
758 * @param int $itemsPerPage
759 *
760 * @return array
761 * @throws QueryExecutionException
762 * @throws InvalidArgumentException
763 */
764 public function getFilteredIds($criteria, $itemsPerPage)
765 {
766 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
767 $eventsTagsTable = EventsTagsTable::getTableName();
768 $customerBookingsTable = CustomerBookingsTable::getTableName();
769 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
770 $eventsProvidersTable = EventsProvidersTable::getTableName();
771 $usersTable = UsersTable::getTableName();
772
773 $params = [];
774
775 $where = [];
776
777 if (!empty($criteria['search'])) {
778 $where[] = "(e.name LIKE '%" . $criteria['search'] . "%'
779 OR e.translations LIKE '{\"name\":{%" . $criteria['search'] . "%\"description\":{%'
780 OR e.translations LIKE '{\"description\":{%\"name\":{%" . $criteria['search'] . "%'
781 OR (e.translations LIKE '{\"name\":{%" . $criteria['search'] . "%' AND e.translations NOT LIKE '%\"description\":{%'))";
782 }
783
784
785 if (isset($criteria['show'])) {
786 $where[] = 'e.show = 1';
787 }
788
789 if (!empty($criteria['dates'])) {
790 if (isset($criteria['dates'][0], $criteria['dates'][1])) {
791 $where[] = "((DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') BETWEEN :eventFrom1 AND :eventTo1) OR (DATE_FORMAT(ep.periodEnd, '%Y-%m-%d %H:%i:%s') BETWEEN :eventFrom2 AND :eventTo2))";
792 $params[':eventFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
793 $params[':eventTo1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
794
795 $params[':eventFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
796 $params[':eventTo2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
797 } elseif (isset($criteria['dates'][0])) {
798 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') >= :eventFrom OR (DATE_FORMAT(ep.periodEnd, '%Y-%m-%d %H:%i:%s') >= :eventTo))";
799 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
800 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
801 } elseif (isset($criteria['dates'][1])) {
802 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') <= :eventTo)";
803 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
804 } else {
805 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') > :eventFrom)";
806 $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
807 }
808 }
809
810 $tagJoin = '';
811
812 if (isset($criteria['tag'])) {
813 $queryTags = [];
814
815 $tags = $criteria['tag'];
816 foreach ((array)$tags as $index => $value) {
817 $param = ':tag' . $index;
818
819 $queryTags[] = $param;
820
821 $params[$param] = $value;
822 }
823
824 $where[] = 'et.name IN (' . implode(', ', $queryTags) . ')';
825
826 $tagJoin = "INNER JOIN {$eventsTagsTable} et ON et.eventId = e.id";
827 }
828
829 if (!empty($criteria['id'])) {
830 if (!empty($criteria['recurring'])) {
831 $whereOr = [];
832 foreach ((array)$criteria['id'] as $index => $value) {
833 $param = 'id' . $index;
834
835 $params[':rec1' . $param] = (int)$value;
836 $params[':rec2' . $param] = (int)$value;
837 $params[':rec3' . $param] = (int)$value;
838 $params[':rec4' . $param] = (int)$value;
839
840 $whereOr[] = "((e.id = :rec1id" . $index . " AND e.parentId IS NULL) OR
841 (e.parentId IN (SELECT parentId FROM {$this->table} WHERE parentId = :rec2id" . $index . ")) OR
842 (e.id >= :rec3id" . $index . " AND e.parentId IN (SELECT parentId FROM {$this->table} WHERE id = :rec4id" . $index . ")))";
843 }
844 $where[] = implode(' OR ', $whereOr);
845 } else {
846 $queryIds = [];
847
848 foreach ((array)$criteria['id'] as $index => $value) {
849 $param = ':id' . $index;
850
851 $queryIds[] = $param;
852
853 $params[$param] = (int)$value;
854 }
855
856 $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
857 }
858 }
859
860 $customerJoin = '';
861
862 if (!empty($criteria['customerId'])) {
863 $customerJoin = "
864 LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
865 LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId";
866
867 $params[':customerId'] = $criteria['customerId'];
868
869 $where[] = 'cb.customerId = :customerId';
870 }
871
872 if (!empty($criteria['locationId'])) {
873 $params[':locationId'] = $criteria['locationId'];
874
875 $where[] = 'e.locationId = :locationId';
876 }
877
878 $groupBy = '';
879 if (!empty($criteria['groupById'])) {
880 $groupBy = 'GROUP BY e.id';
881 }
882
883
884 if (!empty($criteria['locations'])) {
885 foreach ((array)$criteria['locations'] as $index => $value) {
886 $param = ':location' . $index;
887 $queryLocations[] = $param;
888 $params[$param] = $value;
889 }
890
891 $where3 = 'e.locationId IN (' . implode(', ', $queryLocations) . ')';
892
893 $where[] = '(' . $where3 . ')';
894 }
895
896 $providerJoin = '';
897
898 if (!empty($criteria['providers'])) {
899 $providerJoin = "
900 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
901 INNER JOIN {$usersTable} pu ON pu.id = epr.userId OR pu.id = e.organizerId";
902 $queryProviders = [];
903
904 foreach ((array)$criteria['providers'] as $index => $value) {
905 $param = ':provider' . $index;
906 $queryProviders[] = $param;
907 $params[$param] = $value;
908 }
909
910 $where1 = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
911
912 $queryProviders = [];
913 foreach ((array)$criteria['providers'] as $index => $value) {
914 $param = ':organizer' . $index;
915 $queryProviders[] = $param;
916 $params[$param] = $value;
917 }
918
919 $where2 = 'e.organizerId IN (' . implode(', ', $queryProviders) . ')';
920
921 $where[] = '(' . $where1 . ' OR ' . $where2 . ')';
922
923 }
924
925 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
926
927 $limit = $this->getLimit(
928 !empty($criteria['page']) ? (int)$criteria['page'] : 0,
929 (int)$itemsPerPage
930 );
931
932 try {
933 $statement = $this->connection->prepare(
934 "SELECT
935 e.id
936 FROM {$this->table} e
937 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
938 {$tagJoin}
939 {$providerJoin}
940 {$customerJoin}
941 {$where}
942 {$groupBy}
943 ORDER BY ep.periodStart, e.id
944 {$limit}"
945 );
946
947 $statement->execute($params);
948
949 $rows = $statement->fetchAll();
950 } catch (\Exception $e) {
951 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
952 }
953
954 return $rows;
955 }
956
957 /**
958 * @param array $criteria
959 *
960 * @return int
961 * @throws QueryExecutionException
962 * @throws InvalidArgumentException
963 */
964 public function getFilteredIdsCount($criteria)
965 {
966 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
967 $eventsTagsTable = EventsTagsTable::getTableName();
968 $eventsProvidersTable = EventsProvidersTable::getTableName();
969 $usersTable = UsersTable::getTableName();
970
971
972 $params = [];
973 $where = [];
974
975 if (!empty($criteria['search'])) {
976 $where[] = "(e.name LIKE '%" . $criteria['search'] . "%'
977 OR e.translations LIKE '{\"name\":{%" . $criteria['search'] . "%\"description\":{%'
978 OR e.translations LIKE '{\"description\":{%\"name\":{%" . $criteria['search'] . "%'
979 OR (e.translations LIKE '{\"name\":{%" . $criteria['search'] . "%' AND e.translations NOT LIKE '%\"description\":{%'))";
980 }
981
982 if (isset($criteria['show'])) {
983 $where[] = 'e.show = 1';
984 }
985
986 if (!empty($criteria['dates'])) {
987 if (isset($criteria['dates'][0], $criteria['dates'][1])) {
988 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') BETWEEN :eventFrom AND :eventTo)";
989 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
990 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
991 } elseif (isset($criteria['dates'][0])) {
992 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') >= :eventFrom OR (DATE_FORMAT(ep.periodEnd, '%Y-%m-%d %H:%i:%s') >= :eventTo))";
993 $params[':eventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
994 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
995 } elseif (isset($criteria['dates'][1])) {
996 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') <= :eventTo)";
997 $params[':eventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
998 } else {
999 $where[] = "(DATE_FORMAT(ep.periodStart, '%Y-%m-%d %H:%i:%s') > :eventFrom)";
1000 $params[':eventFrom'] = DateTimeService::getNowDateTimeInUtc();
1001 }
1002 }
1003
1004 if (!empty($criteria['locationId'])) {
1005 $params[':locationId'] = $criteria['locationId'];
1006
1007 $where[] = 'e.locationId = :locationId';
1008 }
1009
1010 if (!empty($criteria['locations'])) {
1011 foreach ((array)$criteria['locations'] as $index => $value) {
1012 $param = ':location' . $index;
1013 $queryLocations[] = $param;
1014 $params[$param] = $value;
1015 }
1016
1017 $where3 = 'e.locationId IN (' . implode(', ', $queryLocations) . ')';
1018
1019 $where[] = '(' . $where3 . ')';
1020 }
1021
1022
1023 $tagJoin = '';
1024
1025 if (isset($criteria['tag'])) {
1026 $queryTags = [];
1027
1028 $tags = $criteria['tag'];//explode(',', $criteria['tag']);
1029 foreach ((array)$tags as $index => $value) {
1030 $param = ':tag' . $index;
1031
1032 $queryTags[] = $param;
1033
1034 $params[$param] = $value;//trim($value, '{}');
1035 }
1036
1037 $where[] = 'et.name IN (' . implode(', ', $queryTags) . ')';
1038
1039 $tagJoin = "INNER JOIN {$eventsTagsTable} et ON et.eventId = e.id";
1040 }
1041
1042 if (!empty($criteria['id'])) {
1043 if (!empty($criteria['recurring'])) {
1044 $whereOr = [];
1045 foreach ((array)$criteria['id'] as $index => $value) {
1046 $param = 'id' . $index;
1047
1048 $params[':rec1' . $param] = (int)$value;
1049 $params[':rec2' . $param] = (int)$value;
1050 $params[':rec3' . $param] = (int)$value;
1051 $params[':rec4' . $param] = (int)$value;
1052
1053 $whereOr[] = "((e.id = :rec1id" . $index . " AND e.parentId IS NULL) OR
1054 (e.parentId IN (SELECT parentId FROM {$this->table} WHERE parentId = :rec2id" . $index . ")) OR
1055 (e.id >= :rec3id" . $index . " AND e.parentId IN (SELECT parentId FROM {$this->table} WHERE id = :rec4id" . $index . ")))";
1056 }
1057 $where[] = implode(' OR ', $whereOr);
1058 } else {
1059 $queryIds = [];
1060
1061 foreach ((array)$criteria['id'] as $index => $value) {
1062 $param = ':id' . $index;
1063
1064 $queryIds[] = $param;
1065
1066 $params[$param] = (int)$value;
1067 }
1068
1069 $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
1070 }
1071 }
1072
1073 $providerJoin = '';
1074
1075 if (!empty($criteria['providers'])) {
1076 $providerJoin = "
1077 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
1078 INNER JOIN {$usersTable} pu ON pu.id = epr.userId OR pu.id = e.organizerId";
1079
1080 $queryProviders = [];
1081
1082 foreach ((array)$criteria['providers'] as $index => $value) {
1083 $param = ':provider' . $index;
1084 $queryProviders[] = $param;
1085 $params[$param] = $value;
1086 }
1087 $where1 = 'epr.userId IN (' . implode(', ', $queryProviders) . ')';
1088
1089 $queryProviders = [];
1090 foreach ((array)$criteria['providers'] as $index => $value) {
1091 $param = ':organizer' . $index;
1092 $queryProviders[] = $param;
1093 $params[$param] = $value;
1094 }
1095 $where2 = 'e.organizerId IN (' . implode(', ', $queryProviders) . ')';
1096
1097 $where[] = '(' . $where1 . ' OR ' . $where2 . ')';
1098 }
1099
1100 $customerJoin = '';
1101
1102 $customerBookingsTable = CustomerBookingsTable::getTableName();
1103 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
1104
1105 if (!empty($criteria['customerId'])) {
1106 $customerJoin = "
1107 LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
1108 LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId";
1109
1110 $params[':customerId'] = $criteria['customerId'];
1111
1112 $where[] = 'cb.customerId = :customerId';
1113 }
1114
1115 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
1116
1117 try {
1118 $statement = $this->connection->prepare(
1119 "SELECT e.id
1120 FROM {$this->table} e
1121 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
1122 {$tagJoin}
1123 {$providerJoin}
1124 {$customerJoin}
1125 {$where}
1126 GROUP BY e.id
1127 ORDER BY ep.periodStart"
1128 );
1129
1130 $statement->execute($params);
1131
1132 $rows = $statement->fetchAll();
1133 } catch (\Exception $e) {
1134 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
1135 }
1136
1137 return sizeOf($rows);
1138 }
1139
1140 /**
1141 * @param int $id
1142 *
1143 * @return Event
1144 * @throws QueryExecutionException
1145 * @throws InvalidArgumentException
1146 */
1147 public function getById($id)
1148 {
1149 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
1150 $eventsTagsTable = EventsTagsTable::getTableName();
1151 $eventsTicketTable = EventsTicketsTable::getTableName();
1152
1153 $customerBookingsTable = CustomerBookingsTable::getTableName();
1154 $paymentsTable = PaymentsTable::getTableName();
1155 $usersTable = UsersTable::getTableName();
1156 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
1157 $galleriesTable = GalleriesTable::getTableName();
1158 $eventsProvidersTable = EventsProvidersTable::getTableName();
1159 $couponsTable = CouponsTable::getTableName();
1160
1161 try {
1162 $statement = $this->connection->prepare(
1163 "SELECT
1164 e.id AS event_id,
1165 e.name AS event_name,
1166 e.status AS event_status,
1167 e.bookingOpens AS event_bookingOpens,
1168 e.bookingCloses AS event_bookingCloses,
1169 e.bookingOpensRec AS event_bookingOpensRec,
1170 e.bookingClosesRec AS event_bookingClosesRec,
1171 e.ticketRangeRec AS event_ticketRangeRec,
1172 e.recurringCycle AS event_recurringCycle,
1173 e.recurringOrder AS event_recurringOrder,
1174 e.recurringInterval AS event_recurringInterval,
1175 e.recurringMonthly AS event_recurringMonthly,
1176 e.monthlyDate AS event_monthlyDate,
1177 e.monthlyOnRepeat AS event_monthlyOnRepeat,
1178 e.monthlyOnDay AS event_monthlyOnDay,
1179 e.recurringUntil AS event_recurringUntil,
1180 e.bringingAnyone AS event_bringingAnyone,
1181 e.bookMultipleTimes AS event_bookMultipleTimes,
1182 e.maxCapacity AS event_maxCapacity,
1183 e.maxCustomCapacity AS event_maxCustomCapacity,
1184 e.maxExtraPeople AS event_maxExtraPeople,
1185 e.price AS event_price,
1186 e.description AS event_description,
1187 e.color AS event_color,
1188 e.show AS event_show,
1189 e.notifyParticipants AS event_notifyParticipants,
1190 e.locationId AS event_locationId,
1191 e.customLocation AS event_customLocation,
1192 e.parentId AS event_parentId,
1193 e.created AS event_created,
1194 e.settings AS event_settings,
1195 e.zoomUserId AS event_zoomUserId,
1196 e.organizerId AS event_organizerId,
1197 e.translations AS event_translations,
1198 e.deposit AS event_deposit,
1199 e.depositPayment AS event_depositPayment,
1200 e.depositPerPerson AS event_depositPerPerson,
1201 e.fullPayment AS event_fullPayment,
1202 e.customPricing AS event_customPricing,
1203 e.aggregatedPrice AS event_aggregatedPrice,
1204
1205 ep.id AS event_periodId,
1206 ep.periodStart AS event_periodStart,
1207 ep.periodEnd AS event_periodEnd,
1208 ep.zoomMeeting AS event_periodZoomMeeting,
1209 ep.lessonSpace AS event_periodLessonSpace,
1210 ep.googleCalendarEventId AS event_googleCalendarEventId,
1211 ep.googleMeetUrl AS event_googleMeetUrl,
1212 ep.outlookCalendarEventId AS event_outlookCalendarEventId,
1213
1214 et.id AS event_tagId,
1215 et.name AS event_tagName,
1216
1217 cb.id AS booking_id,
1218 cb.customerId AS booking_customerId,
1219 cb.status AS booking_status,
1220 cb.price AS booking_price,
1221 cb.persons AS booking_persons,
1222 cb.customFields AS booking_customFields,
1223 cb.info AS booking_info,
1224 cb.aggregatedPrice AS booking_aggregatedPrice,
1225 cb.token AS booking_token,
1226 cb.utcOffset AS booking_utcOffset,
1227 cb.couponId AS booking_couponId,
1228
1229 cu.id AS customer_id,
1230 cu.firstName AS customer_firstName,
1231 cu.lastName AS customer_lastName,
1232 cu.email AS customer_email,
1233 cu.note AS customer_note,
1234 cu.phone AS customer_phone,
1235 cu.gender AS customer_gender,
1236 cu.birthday AS customer_birthday,
1237
1238 p.id AS payment_id,
1239 p.amount AS payment_amount,
1240 p.dateTime AS payment_dateTime,
1241 p.status AS payment_status,
1242 p.gateway AS payment_gateway,
1243 p.gatewayTitle AS payment_gatewayTitle,
1244 p.transactionId AS payment_transactionId,
1245 p.data AS payment_data,
1246 p.wcOrderId AS payment_wcOrderId,
1247 p.wcOrderItemId AS payment_wcOrderItemId,
1248
1249 pu.id AS provider_id,
1250 pu.firstName AS provider_firstName,
1251 pu.lastName AS provider_lastName,
1252 pu.email AS provider_email,
1253 pu.note AS provider_note,
1254 pu.description AS provider_description,
1255 pu.phone AS provider_phone,
1256 pu.gender AS provider_gender,
1257 pu.translations AS provider_translations,
1258 pu.timeZone AS provider_timeZone,
1259
1260 g.id AS gallery_id,
1261 g.pictureFullPath AS gallery_picture_full,
1262 g.pictureThumbPath AS gallery_picture_thumb,
1263 g.position AS gallery_position,
1264
1265 c.id AS coupon_id,
1266 c.code AS coupon_code,
1267 c.discount AS coupon_discount,
1268 c.deduction AS coupon_deduction,
1269 c.limit AS coupon_limit,
1270 c.customerLimit AS coupon_customerLimit,
1271 c.status AS coupon_status,
1272
1273 t.id AS ticket_id,
1274 t.name AS ticket_name,
1275 t.enabled AS ticket_enabled,
1276 t.price AS ticket_price,
1277 t.spots AS ticket_spots,
1278 t.dateRanges AS ticket_dateRanges,
1279 t.translations AS ticket_translations
1280
1281 FROM {$this->table} e
1282 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
1283 LEFT JOIN {$eventsTagsTable} et ON et.eventId = e.id
1284 LEFT JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
1285 LEFT JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
1286 LEFT JOIN {$usersTable} cu ON cu.id = cb.customerId
1287 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
1288 LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
1289 LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
1290 LEFT JOIN {$galleriesTable} g ON g.entityId = e.id AND g.entityType = 'event'
1291 LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
1292 LEFT JOIN {$eventsTicketTable} t ON t.eventId = e.id
1293
1294 WHERE e.id = :eventId"
1295 );
1296
1297 $statement->bindParam(':eventId', $id);
1298
1299 $statement->execute();
1300
1301 $rows = $statement->fetchAll();
1302 } catch (\Exception $e) {
1303 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
1304 }
1305
1306 return call_user_func([static::FACTORY, 'createCollection'], $rows)->getItem($id);
1307 }
1308
1309
1310 /**
1311 * @param int $id
1312 *
1313 * @return mixed
1314 * @throws QueryExecutionException
1315 */
1316 public function isRecurring($id)
1317 {
1318 try {
1319 $statement = $this->connection->prepare(
1320 "SELECT
1321 e.recurringOrder AS event_recurringOrder,
1322 e.parentId AS event_parentId
1323 FROM {$this->table} e
1324 WHERE e.id = :eventId"
1325 );
1326
1327 $statement->bindParam(':eventId', $id);
1328
1329 $statement->execute();
1330
1331 return $statement->fetch();
1332 } catch (\Exception $e) {
1333 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
1334 }
1335 }
1336
1337
1338 /**
1339 * @param int $id
1340 * @param int $parentId
1341 *
1342 * @return mixed
1343 * @throws QueryExecutionException
1344 */
1345 public function getRecurringIds($id, $parentId)
1346 {
1347 $whereParent = empty($parentId) ? '' : ' OR e.parentId = :parentId';
1348 try {
1349 $statement = $this->connection->prepare(
1350 "SELECT
1351 e.id AS eventId
1352 FROM {$this->table} e
1353 WHERE e.parentId = :eventId" . $whereParent
1354 );
1355
1356 $statement->bindParam(':eventId', $id);
1357 if ($parentId) {
1358 $statement->bindParam(':parentId', $parentId);
1359 }
1360
1361 $statement->execute();
1362
1363 $events = $statement->fetchAll();
1364
1365 return array_column($events, 'eventId');
1366 } catch (\Exception $e) {
1367 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
1368 }
1369 }
1370
1371
1372 /**
1373 * @param array $ids
1374 *
1375 * @return Event
1376 * @throws QueryExecutionException
1377 * @throws InvalidArgumentException
1378 */
1379 public function getByBookingIds($ids)
1380 {
1381 $paymentsTable = PaymentsTable::getTableName();
1382 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
1383 $eventsTagsTable = EventsTagsTable::getTableName();
1384 $providersGoogleCalendarTable = ProvidersGoogleCalendarTable::getTableName();
1385
1386 $usersTable = UsersTable::getTableName();
1387 $customerBookingsTable = CustomerBookingsTable::getTableName();
1388 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
1389 $eventsProvidersTable = EventsProvidersTable::getTableName();
1390 $couponsTable = CouponsTable::getTableName();
1391 $providersOutlookCalendarTable = ProvidersOutlookCalendarTable::getTableName();
1392
1393 $params = [];
1394 $where = [];
1395
1396 foreach ($ids as $key => $id) {
1397 $params[":customerBookingId$key"] = $id;
1398 $where[] = "cb.id = :customerBookingId$key";
1399 }
1400
1401 $where = $where ? 'WHERE ' . implode(' OR ', $where) : '';
1402
1403 try {
1404 $statement = $this->connection->prepare(
1405 "SELECT
1406 e.id AS event_id,
1407 e.name AS event_name,
1408 e.status AS event_status,
1409 e.bookingOpens AS event_bookingOpens,
1410 e.bookingCloses AS event_bookingCloses,
1411 e.recurringCycle AS event_recurringCycle,
1412 e.recurringOrder AS event_recurringOrder,
1413 e.recurringInterval AS event_recurringInterval,
1414 e.recurringUntil AS event_recurringUntil,
1415 e.bringingAnyone AS event_bringingAnyone,
1416 e.bookMultipleTimes AS event_bookMultipleTimes,
1417 e.maxCapacity AS event_maxCapacity,
1418 e.maxCustomCapacity AS event_maxCustomCapacity,
1419 e.maxExtraPeople AS event_maxExtraPeople,
1420 e.price AS event_price,
1421 e.description AS event_description,
1422 e.color AS event_color,
1423 e.show AS event_show,
1424 e.notifyParticipants AS event_notifyParticipants,
1425 e.locationId AS event_locationId,
1426 e.customLocation AS event_customLocation,
1427 e.parentId AS event_parentId,
1428 e.created AS event_created,
1429 e.settings AS event_settings,
1430 e.zoomUserId AS event_zoomUserId,
1431 e.organizerId AS event_organizerId,
1432 e.translations AS event_translations,
1433 e.deposit AS event_deposit,
1434 e.depositPayment AS event_depositPayment,
1435 e.depositPerPerson AS event_depositPerPerson,
1436 e.fullPayment AS event_fullPayment,
1437 e.customPricing AS event_customPricing,
1438 e.aggregatedPrice AS event_aggregatedPrice,
1439
1440 ep.id AS event_periodId,
1441 ep.periodStart AS event_periodStart,
1442 ep.periodEnd AS event_periodEnd,
1443 ep.zoomMeeting AS event_periodZoomMeeting,
1444 ep.lessonSpace AS event_periodLessonSpace,
1445 ep.googleCalendarEventId AS event_googleCalendarEventId,
1446 ep.googleMeetUrl AS event_googleMeetUrl,
1447 ep.outlookCalendarEventId AS event_outlookCalendarEventId,
1448
1449 et.id AS event_tagId,
1450 et.name AS event_tagName,
1451
1452 cb.id AS booking_id,
1453 cb.appointmentId AS booking_appointmentId,
1454 cb.customerId AS booking_customerId,
1455 cb.status AS booking_status,
1456 cb.price AS booking_price,
1457 cb.persons AS booking_persons,
1458 cb.persons AS booking_couponId,
1459 cb.customFields AS booking_customFields,
1460 cb.info AS booking_info,
1461 cb.utcOffset AS booking_utcOffset,
1462 cb.token AS booking_token,
1463 cb.aggregatedPrice AS booking_aggregatedPrice,
1464
1465 cu.id AS customer_id,
1466 cu.firstName AS customer_firstName,
1467 cu.lastName AS customer_lastName,
1468 cu.email AS customer_email,
1469 cu.note AS customer_note,
1470 cu.phone AS customer_phone,
1471 cu.gender AS customer_gender,
1472 cu.birthday AS customer_birthday,
1473
1474 p.id AS payment_id,
1475 p.amount AS payment_amount,
1476 p.dateTime AS payment_dateTime,
1477 p.status AS payment_status,
1478 p.gateway AS payment_gateway,
1479 p.gatewayTitle AS payment_gatewayTitle,
1480 p.transactionId AS payment_transactionId,
1481 p.data AS payment_data,
1482 p.wcOrderId AS payment_wcOrderId,
1483 p.wcOrderItemId AS payment_wcOrderItemId,
1484
1485 pu.id AS provider_id,
1486 pu.firstName AS provider_firstName,
1487 pu.lastName AS provider_lastName,
1488 pu.email AS provider_email,
1489 pu.note AS provider_note,
1490 pu.description AS provider_description,
1491 pu.phone AS provider_phone,
1492 pu.gender AS provider_gender,
1493 pu.translations AS provider_translations,
1494
1495 gd.id AS google_calendar_id,
1496 gd.token AS google_calendar_token,
1497 gd.calendarId AS google_calendar_calendar_id,
1498
1499 od.id AS outlook_calendar_id,
1500 od.token AS outlook_calendar_token,
1501 od.calendarId AS outlook_calendar_calendar_id,
1502
1503 c.id AS coupon_id,
1504 c.code AS coupon_code,
1505 c.discount AS coupon_discount,
1506 c.deduction AS coupon_deduction,
1507 c.limit AS coupon_limit,
1508 c.customerLimit AS coupon_customerLimit,
1509 c.status AS coupon_status
1510
1511 FROM {$this->table} e
1512 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
1513 INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
1514 INNER JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
1515 INNER JOIN {$usersTable} cu ON cu.id = cb.customerId
1516 LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
1517 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
1518 LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
1519 LEFT JOIN {$providersGoogleCalendarTable} gd ON gd.userId = pu.id
1520 LEFT JOIN {$providersOutlookCalendarTable} od ON od.userId = pu.id
1521 LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
1522 LEFT JOIN {$eventsTagsTable} et ON et.eventId = e.id
1523
1524 {$where}"
1525 );
1526
1527 $statement->execute($params);
1528
1529 $rows = $statement->fetchAll();
1530 } catch (\Exception $e) {
1531 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
1532 }
1533
1534 return call_user_func([static::FACTORY, 'createCollection'], $rows);
1535 }
1536
1537 /**
1538 * @param $criteria
1539 *
1540 * @return Collection
1541 * @throws InvalidArgumentException
1542 * @throws QueryExecutionException
1543 * @throws InvalidArgumentException
1544 */
1545 public function getWithCoupons($criteria)
1546 {
1547 $couponToEventsTable = CouponsToEventsTable::getTableName();
1548 $couponsTable = CouponsTable::getTableName();
1549 $eventsProvidersTable = EventsProvidersTable::getTableName();
1550 $usersTable = UsersTable::getTableName();
1551 $eventsTicketTable = EventsTicketsTable::getTableName();
1552
1553 $params = [];
1554
1555 $where = [];
1556
1557 foreach ((array)$criteria as $index => $value) {
1558 $params[':event' . $index] = $value['eventId'];
1559
1560 if ($value['couponId']) {
1561 $params[':coupon' . $index] = $value['couponId'];
1562 $params[':couponStatus' . $index] = Status::VISIBLE;
1563 }
1564
1565 $where[] = "(e.id = :event$index"
1566 . ($value['couponId'] ? " AND c.id = :coupon$index AND c.status = :couponStatus$index" : '') . ')';
1567 }
1568
1569 $where = $where ? 'WHERE ' . implode(' OR ', $where) : '';
1570
1571 try {
1572 $statement = $this->connection->prepare(
1573 "SELECT
1574 e.id AS event_id,
1575 e.name AS event_name,
1576 e.status AS event_status,
1577 e.bookingOpens AS event_bookingOpens,
1578 e.bookingCloses AS event_bookingCloses,
1579 e.recurringCycle AS event_recurringCycle,
1580 e.recurringOrder AS event_recurringOrder,
1581 e.recurringInterval AS event_recurringInterval,
1582 e.recurringUntil AS event_recurringUntil,
1583 e.bringingAnyone AS event_bringingAnyone,
1584 e.bookMultipleTimes AS event_bookMultipleTimes,
1585 e.maxCapacity AS event_maxCapacity,
1586 e.maxCustomCapacity AS event_maxCustomCapacity,
1587 e.maxExtraPeople AS event_maxExtraPeople,
1588 e.price AS event_price,
1589 e.description AS event_description,
1590 e.color AS event_color,
1591 e.show AS event_show,
1592 e.notifyParticipants AS event_notifyParticipants,
1593 e.locationId AS event_locationId,
1594 e.customLocation AS event_customLocation,
1595 e.parentId AS event_parentId,
1596 e.created AS event_created,
1597 e.translations AS event_translations,
1598 e.deposit AS event_deposit,
1599 e.depositPayment AS event_depositPayment,
1600 e.depositPerPerson AS event_depositPerPerson,
1601 e.fullPayment AS event_fullPayment,
1602 e.customPricing AS event_customPricing,
1603 e.aggregatedPrice AS event_aggregatedPrice,
1604
1605 pu.id AS provider_id,
1606 pu.firstName AS provider_firstName,
1607 pu.lastName AS provider_lastName,
1608 pu.email AS provider_email,
1609 pu.note AS provider_note,
1610 pu.description AS provider_description,
1611 pu.phone AS provider_phone,
1612 pu.gender AS provider_gender,
1613 pu.translations AS provider_translations,
1614
1615 t.id AS ticket_id,
1616 t.name AS ticket_name,
1617 t.enabled AS ticket_enabled,
1618 t.price AS ticket_price,
1619 t.spots AS ticket_spots,
1620 t.dateRanges AS ticket_dateRanges,
1621 t.translations AS ticket_translations,
1622
1623 c.id AS coupon_id,
1624 c.code AS coupon_code,
1625 c.discount AS coupon_discount,
1626 c.deduction AS coupon_deduction,
1627 c.limit AS coupon_limit,
1628 c.customerLimit AS coupon_customerLimit,
1629 c.status AS coupon_status
1630 FROM {$this->table} e
1631 LEFT JOIN {$couponToEventsTable} ce ON ce.eventId = e.id
1632 LEFT JOIN {$couponsTable} c ON c.id = ce.couponId
1633 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
1634 LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
1635 LEFT JOIN {$eventsTicketTable} t ON t.eventId = e.id
1636 {$where}"
1637 );
1638
1639 $statement->execute($params);
1640
1641 $rows = $statement->fetchAll();
1642 } catch (\Exception $e) {
1643 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
1644 }
1645
1646 return call_user_func([static::FACTORY, 'createCollection'], $rows);
1647 }
1648
1649 /**
1650 * @param int $bookingId
1651 * @param array $criteria
1652 *
1653 * @return Event
1654 * @throws QueryExecutionException
1655 * @throws InvalidArgumentException
1656 */
1657 public function getByBookingId($bookingId, $criteria = [])
1658 {
1659 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
1660
1661 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
1662
1663 $fields = '';
1664
1665 $joins = '';
1666
1667 if (!empty($criteria['fetchEventsCoupons'])) {
1668 $couponsTable = CouponsTable::getTableName();
1669
1670 $fields .= '
1671 ec.id AS coupon_id,
1672 ec.code AS coupon_code,
1673 ec.discount AS coupon_discount,
1674 ec.deduction AS coupon_deduction,
1675 ec.limit AS coupon_limit,
1676 ec.customerLimit AS coupon_customerLimit,
1677 ec.status AS coupon_status,
1678 ';
1679
1680 $joins .= "
1681 LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
1682 ";
1683 }
1684
1685 if (!empty($criteria['fetchEventsTickets'])) {
1686 $ticketsTable = EventsTicketsTable::getTableName();
1687
1688 $fields .= '
1689 eti.id AS ticket_id,
1690 eti.name AS ticket_name,
1691 eti.enabled AS ticket_enabled,
1692 eti.price AS ticket_price,
1693 eti.spots AS ticket_spots,
1694 eti.dateRanges AS ticket_dateRanges,
1695 eti.translations AS ticket_translations,
1696 ';
1697
1698 $joins .= "
1699 LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
1700 ";
1701 }
1702
1703 if (!empty($criteria['fetchEventsTags'])) {
1704 $tagsTable = EventsTagsTable::getTableName();
1705
1706 $fields .= '
1707 eta.id AS event_tagId,
1708 eta.name AS event_tagName,
1709 ';
1710
1711 $joins .= "
1712 LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
1713 ";
1714 }
1715
1716 if (!empty($criteria['fetchEventsImages'])) {
1717 $galleriesTable = GalleriesTable::getTableName();
1718
1719 $fields .= '
1720 eg.id AS gallery_id,
1721 eg.pictureFullPath AS gallery_picture_full,
1722 eg.pictureThumbPath AS gallery_picture_thumb,
1723 eg.position AS gallery_position,
1724 ';
1725
1726 $joins .= "
1727 LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
1728 ";
1729 }
1730
1731 if (!empty($criteria['fetchEventsProviders'])) {
1732 $eventsProvidersTable = EventsProvidersTable::getTableName();
1733
1734 $usersTable = UsersTable::getTableName();
1735
1736 $joins .= "
1737 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
1738 LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
1739 ";
1740
1741 $fields .= '
1742 pu.id AS provider_id,
1743 pu.firstName AS provider_firstName,
1744 pu.lastName AS provider_lastName,
1745 pu.email AS provider_email,
1746 pu.note AS provider_note,
1747 pu.description AS provider_description,
1748 pu.phone AS provider_phone,
1749 pu.gender AS provider_gender,
1750 pu.pictureFullPath AS provider_pictureFullPath,
1751 pu.pictureThumbPath AS provider_pictureThumbPath,
1752 pu.translations AS provider_translations,
1753 pu.timeZone AS provider_timeZone,
1754 ';
1755 }
1756
1757 $fields .= "
1758 e.id AS event_id,
1759 e.name AS event_name,
1760 e.status AS event_status,
1761 e.bookingOpens AS event_bookingOpens,
1762 e.bookingCloses AS event_bookingCloses,
1763 e.recurringCycle AS event_recurringCycle,
1764 e.recurringOrder AS event_recurringOrder,
1765 e.recurringInterval AS event_recurringInterval,
1766 e.recurringUntil AS event_recurringUntil,
1767 e.bringingAnyone AS event_bringingAnyone,
1768 e.bookMultipleTimes AS event_bookMultipleTimes,
1769 e.maxCapacity AS event_maxCapacity,
1770 e.maxCustomCapacity AS event_maxCustomCapacity,
1771 e.maxExtraPeople AS event_maxExtraPeople,
1772 e.price AS event_price,
1773 e.description AS event_description,
1774 e.color AS event_color,
1775 e.show AS event_show,
1776 e.notifyParticipants AS event_notifyParticipants,
1777 e.locationId AS event_locationId,
1778 e.customLocation AS event_customLocation,
1779 e.customPricing AS event_customPricing,
1780 e.parentId AS event_parentId,
1781 e.created AS event_created,
1782 e.settings AS event_settings,
1783 e.zoomUserId AS event_zoomUserId,
1784 e.translations AS event_translations,
1785 e.deposit AS event_deposit,
1786 e.depositPayment AS event_depositPayment,
1787 e.depositPerPerson AS event_depositPerPerson,
1788 e.fullPayment AS event_fullPayment,
1789 e.organizerId AS event_organizerId,
1790 e.aggregatedPrice AS event_aggregatedPrice,
1791
1792 ep.id AS event_periodId,
1793 ep.periodStart AS event_periodStart,
1794 ep.periodEnd AS event_periodEnd,
1795 ep.zoomMeeting AS event_periodZoomMeeting,
1796 ep.lessonSpace AS event_periodLessonSpace,
1797 ep.googleCalendarEventId AS event_googleCalendarEventId,
1798 ep.googleMeetUrl AS event_googleMeetUrl,
1799 ep.outlookCalendarEventId AS event_outlookCalendarEventId
1800 ";
1801
1802 $params = [
1803 ':customerBookingId' => $bookingId,
1804 ];
1805
1806 try {
1807 $statement = $this->connection->prepare(
1808 "SELECT
1809 {$fields}
1810 FROM {$customerBookingsEventsPeriods} cbe
1811 INNER JOIN {$eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
1812 INNER JOIN {$this->table} e ON e.id = ep.eventId
1813 {$joins}
1814 WHERE cbe.customerBookingId = :customerBookingId"
1815 );
1816
1817 $statement->execute($params);
1818
1819 $rows = $statement->fetchAll();
1820 } catch (\Exception $e) {
1821 throw new QueryExecutionException('Unable to find event by booking id in ' . __CLASS__, $e->getCode(), $e);
1822 }
1823
1824 /** @var Collection $events */
1825 $events = call_user_func([static::FACTORY, 'createCollection'], $rows);
1826
1827 return $events->length() ? $events->getItem($events->keys()[0]) : null;
1828 }
1829
1830 /**
1831 * @param array $criteria
1832 *
1833 * @return Collection
1834 * @throws QueryExecutionException
1835 * @throws InvalidArgumentException
1836 */
1837 public function getByCriteria($criteria = [])
1838 {
1839 $params = [];
1840
1841 $where = [];
1842
1843 $fields = '';
1844
1845 $joins = '';
1846
1847 $orderBy = '';
1848
1849 if (!empty($criteria['fetchBookings']) || !empty($criteria['fetchEventsPeriods'])) {
1850 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
1851
1852 $fields .= '
1853 ep.id AS event_periodId,
1854 ep.periodStart AS event_periodStart,
1855 ep.periodEnd AS event_periodEnd,
1856 ep.zoomMeeting AS event_periodZoomMeeting,
1857 ep.lessonSpace AS event_periodLessonSpace,
1858 ep.googleCalendarEventId AS event_googleCalendarEventId,
1859 ep.googleMeetUrl AS event_googleMeetUrl,
1860 ep.outlookCalendarEventId AS event_outlookCalendarEventId,
1861 ';
1862
1863 $joins .= "
1864 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
1865 ";
1866
1867 $orderBy = 'ORDER BY ep.periodStart';
1868 }
1869
1870 if (!empty($criteria['fetchBookings'])) {
1871 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
1872
1873 $customerBookingsTable = CustomerBookingsTable::getTableName();
1874
1875 $fields .= '
1876 cb.id AS booking_id,
1877 cb.appointmentId AS booking_appointmentId,
1878 cb.customerId AS booking_customerId,
1879 cb.status AS booking_status,
1880 cb.price AS booking_price,
1881 cb.persons AS booking_persons,
1882 cb.couponId AS booking_couponId,
1883 cb.customFields AS booking_customFields,
1884 cb.info AS booking_info,
1885 cb.utcOffset AS booking_utcOffset,
1886 cb.token AS booking_token,
1887 cb.aggregatedPrice AS booking_aggregatedPrice,
1888 ';
1889
1890 if (!empty($criteria['fetchApprovedBookings'])) {
1891 $where[] = "cb.status = 'approved'";
1892 }
1893
1894 if (!empty($criteria['customerBookingId'])) {
1895 $params[':customerBookingId'] = $criteria['customerBookingId'];
1896
1897 $where[] = 'cb.id = :customerBookingId';
1898 }
1899
1900 $joins .= "
1901 INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
1902 INNER JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
1903 ";
1904
1905 if (!empty($criteria['fetchBookingsPayments'])) {
1906 $paymentsTable = PaymentsTable::getTableName();
1907
1908 $fields .= '
1909 p.id AS payment_id,
1910 p.amount AS payment_amount,
1911 p.dateTime AS payment_dateTime,
1912 p.status AS payment_status,
1913 p.gateway AS payment_gateway,
1914 p.gatewayTitle AS payment_gatewayTitle,
1915 p.transactionId AS payment_transactionId,
1916 p.data AS payment_data,
1917 p.wcOrderId AS payment_wcOrderId,
1918 p.wcOrderItemId AS payment_wcOrderItemId,
1919 ';
1920
1921 $joins .= "
1922 LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
1923 ";
1924 }
1925
1926 if (!empty($criteria['fetchBookingsCoupons'])) {
1927 $couponsTable = CouponsTable::getTableName();
1928
1929 $fields .= '
1930 c.id AS coupon_id,
1931 c.code AS coupon_code,
1932 c.discount AS coupon_discount,
1933 c.deduction AS coupon_deduction,
1934 c.limit AS coupon_limit,
1935 c.customerLimit AS coupon_customerLimit,
1936 c.status AS coupon_status,
1937 ';
1938
1939 $joins .= "
1940 LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
1941 ";
1942 }
1943
1944 if (!empty($criteria['fetchBookingsUsers'])) {
1945 $usersTable = UsersTable::getTableName();
1946
1947 $fields .= '
1948 cu.id AS customer_id,
1949 cu.type AS customer_type,
1950 cu.firstName AS customer_firstName,
1951 cu.lastName AS customer_lastName,
1952 cu.email AS customer_email,
1953 cu.note AS customer_note,
1954 cu.phone AS customer_phone,
1955 cu.gender AS customer_gender,
1956 cu.birthday AS customer_birthday,
1957 ';
1958
1959 $joins .= "
1960 INNER JOIN {$usersTable} cu ON cu.id = cb.customerId
1961 ";
1962 }
1963
1964 if (!empty($criteria['fetchBookingsTickets'])) {
1965 $bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
1966
1967 $fields .= '
1968 cbt.id AS booking_ticket_id,
1969 cbt.eventTicketId AS booking_ticket_eventTicketId,
1970 cbt.price AS booking_ticket_price,
1971 cbt.persons AS booking_ticket_persons,
1972 ';
1973
1974 $joins .= "
1975 LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
1976 ";
1977 }
1978 }
1979
1980 if (!empty($criteria['fetchEventsCoupons'])) {
1981 $couponsTable = CouponsTable::getTableName();
1982
1983 $fields .= '
1984 ec.id AS coupon_id,
1985 ec.code AS coupon_code,
1986 ec.discount AS coupon_discount,
1987 ec.deduction AS coupon_deduction,
1988 ec.limit AS coupon_limit,
1989 ec.customerLimit AS coupon_customerLimit,
1990 ec.status AS coupon_status,
1991 ';
1992
1993 $joins .= "
1994 LEFT JOIN {$couponsTable} ec ON ec.id = cb.couponId
1995 ";
1996 }
1997
1998 if (!empty($criteria['fetchEventsTickets'])) {
1999 $ticketsTable = EventsTicketsTable::getTableName();
2000
2001 $fields .= '
2002 eti.id AS ticket_id,
2003 eti.name AS ticket_name,
2004 eti.enabled AS ticket_enabled,
2005 eti.price AS ticket_price,
2006 eti.spots AS ticket_spots,
2007 eti.dateRanges AS ticket_dateRanges,
2008 eti.translations AS ticket_translations,
2009 ';
2010
2011 $joins .= "
2012 LEFT JOIN {$ticketsTable} eti ON eti.eventId = e.id
2013 ";
2014 }
2015
2016 if (!empty($criteria['fetchEventsTags'])) {
2017 $tagsTable = EventsTagsTable::getTableName();
2018
2019 $fields .= '
2020 eta.id AS event_tagId,
2021 eta.name AS event_tagName,
2022 ';
2023
2024 $joins .= "
2025 LEFT JOIN {$tagsTable} eta ON eta.eventId = e.id
2026 ";
2027 }
2028
2029 if (!empty($criteria['fetchEventsImages'])) {
2030 $galleriesTable = GalleriesTable::getTableName();
2031
2032 $fields .= '
2033 eg.id AS gallery_id,
2034 eg.pictureFullPath AS gallery_picture_full,
2035 eg.pictureThumbPath AS gallery_picture_thumb,
2036 eg.position AS gallery_position,
2037 ';
2038
2039 $joins .= "
2040 LEFT JOIN {$galleriesTable} eg ON eg.entityId = e.id AND eg.entityType = 'event'
2041 ";
2042 }
2043
2044 if (!empty($criteria['fetchEventsProviders'])) {
2045 $eventsProvidersTable = EventsProvidersTable::getTableName();
2046
2047 $usersTable = UsersTable::getTableName();
2048
2049 $joins .= "
2050 LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
2051 LEFT JOIN {$usersTable} pu ON pu.id = epr.userId
2052 ";
2053
2054 $fields .= '
2055 pu.id AS provider_id,
2056 pu.firstName AS provider_firstName,
2057 pu.lastName AS provider_lastName,
2058 pu.email AS provider_email,
2059 pu.note AS provider_note,
2060 pu.description AS provider_description,
2061 pu.phone AS provider_phone,
2062 pu.gender AS provider_gender,
2063 pu.pictureFullPath AS provider_pictureFullPath,
2064 pu.pictureThumbPath AS provider_pictureThumbPath,
2065 pu.translations AS provider_translations,
2066 pu.timeZone AS provider_timeZone,
2067 ';
2068 }
2069
2070 $fields .= "
2071 e.id AS event_id,
2072 e.name AS event_name,
2073 e.status AS event_status,
2074 e.bookingOpens AS event_bookingOpens,
2075 e.bookingCloses AS event_bookingCloses,
2076 e.bookingOpensRec AS event_bookingOpensRec,
2077 e.bookingClosesRec AS event_bookingClosesRec,
2078 e.ticketRangeRec AS event_ticketRangeRec,
2079 e.recurringCycle AS event_recurringCycle,
2080 e.recurringOrder AS event_recurringOrder,
2081 e.recurringInterval AS event_recurringInterval,
2082 e.recurringMonthly AS event_recurringMonthly,
2083 e.monthlyDate AS event_monthlyDate,
2084 e.monthlyOnRepeat AS event_monthlyOnRepeat,
2085 e.monthlyOnDay AS event_monthlyOnDay,
2086 e.recurringUntil AS event_recurringUntil,
2087 e.bringingAnyone AS event_bringingAnyone,
2088 e.bookMultipleTimes AS event_bookMultipleTimes,
2089 e.maxCapacity AS event_maxCapacity,
2090 e.maxCustomCapacity AS event_maxCustomCapacity,
2091 e.maxExtraPeople AS event_maxExtraPeople,
2092 e.price AS event_price,
2093 e.description AS event_description,
2094 e.color AS event_color,
2095 e.show AS event_show,
2096 e.notifyParticipants AS event_notifyParticipants,
2097 e.locationId AS event_locationId,
2098 e.customLocation AS event_customLocation,
2099 e.parentId AS event_parentId,
2100 e.created AS event_created,
2101 e.settings AS event_settings,
2102 e.zoomUserId AS event_zoomUserId,
2103 e.organizerId AS event_organizerId,
2104 e.translations AS event_translations,
2105 e.deposit AS event_deposit,
2106 e.depositPayment AS event_depositPayment,
2107 e.depositPerPerson AS event_depositPerPerson,
2108 e.fullPayment AS event_fullPayment,
2109 e.customPricing AS event_customPricing,
2110 e.closeAfterMin AS event_closeAfterMin,
2111 e.closeAfterMinBookings AS event_closeAfterMinBookings,
2112 e.aggregatedPrice AS event_aggregatedPrice
2113 ";
2114
2115 if (!empty($criteria['ids'])) {
2116 $queryIds = [];
2117
2118 foreach ($criteria['ids'] as $index => $value) {
2119 $param = ':id' . $index;
2120
2121 $queryIds[] = $param;
2122
2123 $params[$param] = $value;
2124 }
2125
2126 $where[] = 'e.id IN (' . implode(', ', $queryIds) . ')';
2127 }
2128
2129 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
2130
2131 try {
2132 $statement = $this->connection->prepare(
2133 "SELECT
2134 {$fields}
2135 FROM {$this->table} e
2136 {$joins}
2137 {$where}
2138 {$orderBy}"
2139 );
2140
2141 $statement->execute($params);
2142
2143 $rows = $statement->fetchAll();
2144 } catch (\Exception $e) {
2145 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
2146 }
2147
2148 return call_user_func([static::FACTORY, 'createCollection'], $rows);
2149 }
2150
2151 /**
2152 * @param array $criteria
2153 *
2154 * @return Collection
2155 * @throws QueryExecutionException
2156 * @throws InvalidArgumentException
2157 */
2158 public function getBookingsByCriteria($criteria = [])
2159 {
2160 $params = [];
2161
2162 $where = [];
2163
2164 $fields = '';
2165
2166 $joins = '';
2167
2168 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
2169
2170 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
2171
2172 $customerBookingsTable = CustomerBookingsTable::getTableName();
2173
2174 if (!empty($criteria['fetchApprovedBookings'])) {
2175 $where[] = "cb.status = 'approved'";
2176 }
2177
2178 if (!empty($criteria['customerBookingId'])) {
2179 $params[':customerBookingId'] = $criteria['customerBookingId'];
2180
2181 $where[] = 'cb.id = :customerBookingId';
2182 }
2183
2184 if (!empty($criteria['fetchBookingsPayments'])) {
2185 $paymentsTable = PaymentsTable::getTableName();
2186
2187 $fields .= '
2188 p.id AS payment_id,
2189 p.amount AS payment_amount,
2190 p.dateTime AS payment_dateTime,
2191 p.status AS payment_status,
2192 p.gateway AS payment_gateway,
2193 p.gatewayTitle AS payment_gatewayTitle,
2194 p.transactionId AS payment_transactionId,
2195 p.data AS payment_data,
2196 p.wcOrderId AS payment_wcOrderId,
2197 p.wcOrderItemId AS payment_wcOrderItemId,
2198 ';
2199
2200 $joins .= "
2201 LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
2202 ";
2203 }
2204
2205 if (!empty($criteria['fetchBookingsCoupons'])) {
2206 $couponsTable = CouponsTable::getTableName();
2207
2208 $fields .= '
2209 c.id AS coupon_id,
2210 c.code AS coupon_code,
2211 c.discount AS coupon_discount,
2212 c.deduction AS coupon_deduction,
2213 c.limit AS coupon_limit,
2214 c.customerLimit AS coupon_customerLimit,
2215 c.status AS coupon_status,
2216 ';
2217
2218 $joins .= "
2219 LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
2220 ";
2221 }
2222
2223 if (!empty($criteria['fetchBookingsUsers'])) {
2224 $usersTable = UsersTable::getTableName();
2225
2226 $fields .= '
2227 cu.id AS customer_id,
2228 cu.type AS customer_type,
2229 cu.firstName AS customer_firstName,
2230 cu.lastName AS customer_lastName,
2231 cu.email AS customer_email,
2232 cu.note AS customer_note,
2233 cu.phone AS customer_phone,
2234 cu.gender AS customer_gender,
2235 cu.birthday AS customer_birthday,
2236 ';
2237
2238 $joins .= "
2239 INNER JOIN {$usersTable} cu ON cu.id = cb.customerId
2240 ";
2241 }
2242
2243 if (!empty($criteria['fetchBookingsTickets'])) {
2244 $bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
2245
2246 $fields .= '
2247 cbt.id AS booking_ticket_id,
2248 cbt.eventTicketId AS booking_ticket_eventTicketId,
2249 cbt.price AS booking_ticket_price,
2250 cbt.persons AS booking_ticket_persons,
2251 ';
2252
2253 $joins .= "
2254 LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id
2255 ";
2256 }
2257
2258 $fields .= '
2259 ep.eventId AS eventId,
2260 cb.id AS booking_id,
2261 cb.appointmentId AS booking_appointmentId,
2262 cb.customerId AS booking_customerId,
2263 cb.status AS booking_status,
2264 cb.price AS booking_price,
2265 cb.tax AS booking_tax,
2266 cb.persons AS booking_persons,
2267 cb.couponId AS booking_couponId,
2268 cb.customFields AS booking_customFields,
2269 cb.info AS booking_info,
2270 cb.utcOffset AS booking_utcOffset,
2271 cb.token AS booking_token,
2272 cb.aggregatedPrice AS booking_aggregatedPrice
2273 ';
2274
2275 if (!empty($criteria['ids'])) {
2276 $queryIds = [];
2277
2278 foreach ($criteria['ids'] as $index => $value) {
2279 $param = ':id' . $index;
2280
2281 $queryIds[] = $param;
2282
2283 $params[$param] = $value;
2284 }
2285
2286 $where[] = 'ep.eventId IN (' . implode(', ', $queryIds) . ')';
2287 }
2288
2289 $where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
2290
2291 try {
2292 $statement = $this->connection->prepare(
2293 "SELECT
2294 {$fields}
2295 FROM {$eventsPeriodsTable} ep
2296 INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
2297 INNER JOIN {$customerBookingsTable} cb ON cb.id = cbe.customerBookingId
2298 {$joins}
2299 {$where}
2300 ORDER BY cb.id"
2301 );
2302
2303 $statement->execute($params);
2304
2305 $rows = $statement->fetchAll();
2306 } catch (\Exception $e) {
2307 throw new QueryExecutionException('Unable to find event by id in ' . __CLASS__, $e->getCode(), $e);
2308 }
2309
2310 $reformattedData = [];
2311
2312 foreach ($rows as $row) {
2313 if (empty($reformattedData[$row['eventId']])) {
2314 $reformattedData[$row['eventId']] = [];
2315 }
2316
2317 $reformattedData[$row['eventId']][] = $row;
2318 }
2319
2320 $result = new Collection();
2321
2322 foreach ($reformattedData as $eventId => $bookingsData) {
2323 $reformattedBookingsData = CustomerBookingFactory::reformat($bookingsData);
2324
2325 $eventBookings = new Collection();
2326
2327 foreach ($reformattedBookingsData as $bookingId => $data) {
2328 $eventBookings->addItem(CustomerBookingFactory::create($data), $bookingId);
2329 }
2330
2331 $result->addItem($eventBookings, $eventId);
2332 }
2333
2334 return $result;
2335 }
2336
2337
2338 /**
2339 * @param Event $event
2340 * @param array $booking
2341 * @param array $limitPerCustomer
2342 * @return int
2343 * @throws QueryExecutionException
2344 * @throws InvalidArgumentException
2345 */
2346 public function getRelevantBookingsCount($event, $booking, $limitPerCustomer)
2347 {
2348 $eventsPeriodsTable = EventsPeriodsTable::getTableName();
2349
2350 $customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
2351
2352 $customerBookingsTable = CustomerBookingsTable::getTableName();
2353
2354 $params = [
2355 ':customerId' => $booking['customerId']
2356 ];
2357
2358 $paymentTableJoin = '';
2359 $compareToDate = 'ep.periodStart';
2360
2361 if ($limitPerCustomer['from'] === 'bookingDate') {
2362 $eventStartDate = (clone $event->getPeriods()->getItems()[0]->getPeriodStart()->getValue())->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
2363 } else {
2364 $paymentTableJoin = 'INNER JOIN ' . PaymentsTable::getTableName() . ' p ON p.customerBookingId = cb.id';
2365 $eventStartDate = DateTimeService::getNowDateTimeObject()->setTimezone(new \DateTimeZone('UTC'))->format('Y-m-d H:i');
2366 $compareToDate = 'p.created';
2367 }
2368
2369 $intervalString = "interval " . $limitPerCustomer['period'] . " " . $limitPerCustomer['timeFrame'];
2370
2371 $where = "(STR_TO_DATE('". $eventStartDate ."', '%Y-%m-%d %H:%i:%s') BETWEEN " .
2372 "(" . $compareToDate . " - " . $intervalString . " + interval 1 second)" .
2373 " AND (".
2374 $compareToDate . " + " . $intervalString . " - interval 1 second))";
2375
2376 try {
2377 $statement = $this->connection->prepare(
2378 "SELECT COUNT(DISTINCT cb.id) AS count FROM
2379 {$this->table} e
2380 INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
2381 INNER JOIN {$customerBookingsEventsPeriods} cbep ON cbep.eventPeriodId = ep.id
2382 INNER JOIN {$customerBookingsTable} cb ON cb.id = cbep.customerBookingId
2383 {$paymentTableJoin}
2384 WHERE cb.customerId = :customerId AND {$where} AND e.status = 'approved' AND cb.status = 'approved'
2385 "
2386 );
2387
2388 $statement->execute($params);
2389
2390 $rows = $statement->fetch()['count'];
2391 } catch (\Exception $e) {
2392 throw new QueryExecutionException('Unable to find by id in ' . __CLASS__, $e->getCode(), $e);
2393 }
2394
2395 return $rows;
2396 }
2397 }
2398