<?php
namespace AmeliaBooking\Infrastructure\Repository\Notification;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Entities;
use AmeliaBooking\Domain\Entity\Notification\Notification;
use AmeliaBooking\Domain\Entity\User\AbstractUser;
use AmeliaBooking\Domain\Factory\Booking\Appointment\AppointmentFactory;
use AmeliaBooking\Domain\Factory\Booking\Event\EventFactory;
use AmeliaBooking\Domain\Factory\Notification\NotificationLogFactory;
use AmeliaBooking\Domain\Factory\User\UserFactory;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Domain\ValueObjects\String\Status;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToEventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToExtrasTable;
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\EventsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTicketsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Payment\PaymentsTable;
/**
* Class NotificationLogRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Notification
*/
class NotificationLogRepository extends AbstractRepository
{
public const FACTORY = NotificationLogFactory::class;
/** @var string */
protected $notificationsTable;
/** @var string */
protected $appointmentsTable;
/** @var string */
protected $bookingsTable;
/** @var string */
protected $usersTable;
/**
* NotificationLogRepository constructor.
*
* @param Connection $connection
* @param string $table
* @param string $notificationsTable
* @param string $appointmentsTable
* @param string $bookingsTable
* @param string $usersTable
*/
public function __construct(
Connection $connection,
$table,
$notificationsTable,
$appointmentsTable,
$bookingsTable,
$usersTable
) {
parent::__construct($connection, $table);
$this->notificationsTable = $notificationsTable;
$this->appointmentsTable = $appointmentsTable;
$this->bookingsTable = $bookingsTable;
$this->usersTable = $usersTable;
}
/**
* @param Notification $notification
* @param int|null $userId
* @param int|null $appointmentId
* @param int|null $eventId
* @param int|null $packageCustomerId
* @param string|null $data
*
* @return int
*
* @throws QueryExecutionException
* @throws \Exception
*/
public function add($notification, $userId, $appointmentId = null, $eventId = null, $packageCustomerId = null, $data = null)
{
$notificationData = $notification->toArray();
$params = [
':notificationId' => $notificationData['id'],
':userId' => $userId,
':appointmentId' => $appointmentId,
':packageCustomerId' => $packageCustomerId,
':eventId' => $eventId,
':sentDateTime' => DateTimeService::getNowDateTimeInUtc(),
':data' => $data,
];
try {
$statement = $this->connection->prepare(
"INSERT INTO {$this->table}
(`notificationId`, `userId`, `appointmentId`, `eventId`, `packageCustomerId`, `sentDateTime`, `sent`, `data`)
VALUES (:notificationId, :userId, :appointmentId, :eventId, :packageCustomerId, :sentDateTime, 0, :data)"
);
$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 $entityId
* @param string $entityType
* @param int $userId
* @param array $notificationsIds
*
* @return void
* @throws QueryExecutionException
*/
public function invalidateSentEmails($entityId, $entityType, $userId, $notificationsIds)
{
if (empty($notificationsIds)) {
return;
}
$params = [
":$entityType" . 'Id' => $entityId,
];
$userQuery = '';
if ($userId) {
$params[':userId'] = $userId;
$userQuery = ' AND userId = :userId';
}
$queryNotificationsIds = [];
foreach ($notificationsIds as $index => $value) {
$param = ':notificationId' . $index;
$queryNotificationsIds[] = $param;
$params[$param] = $value;
}
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table} SET
`sent` = -1
WHERE
{$entityType}Id = :{$entityType}Id
AND notificationId IN (" . implode(', ', $queryNotificationsIds) . ')'
. $userQuery
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Return a collection of tomorrow appointments where customer notification is not sent and should be.
*
* @param int $notificationId
* @param bool $nextDay
* @param array $statuses
*
* @return Collection
*
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getCustomersNextDayAppointments($notificationId, $nextDay = true, $statuses = [])
{
$couponsTable = CouponsTable::getTableName();
$customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$startDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(0, 0, 0)->format('Y-m-d H:i:s')
);
$endDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(23, 59, 59)->format('Y-m-d H:i:s')
);
if ($nextDay) {
$startDate = $startDate->modify('+1 day');
$endDate = $endDate->modify('+1 day');
}
$startCurrentDate = "STR_TO_DATE('" . $startDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$endCurrentDate = "STR_TO_DATE('" . $endDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$whereStatuses = [];
foreach ($statuses as $key => $status) {
$whereStatuses[] = "cb.status = '$status'";
}
$whereStatuses = $whereStatuses ? 'AND (' . implode(' OR ', $whereStatuses) . ')' : '';
try {
$statement = $this->connection->query(
"SELECT
a.id AS appointment_id,
a.bookingStart AS appointment_bookingStart,
a.bookingEnd AS appointment_bookingEnd,
a.notifyParticipants AS appointment_notifyParticipants,
a.createPaymentLinks AS appointment_createPaymentLinks,
a.serviceId AS appointment_serviceId,
a.providerId AS appointment_providerId,
a.locationId AS appointment_locationId,
a.internalNotes AS appointment_internalNotes,
a.status AS appointment_status,
a.zoomMeeting AS appointment_zoom_meeting,
a.lessonSpace AS appointment_lesson_space,
a.googleMeetUrl AS appointment_google_meet_url,
a.microsoftTeamsUrl AS appointment_microsoft_teams_url,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.utcOffset AS booking_utcOffset,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.persons AS booking_persons,
cb.duration AS booking_duration,
cb.created AS booking_created,
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.data AS payment_data,
cbe.id AS bookingExtra_id,
cbe.extraId AS bookingExtra_extraId,
cbe.customerBookingId AS bookingExtra_customerBookingId,
cbe.quantity AS bookingExtra_quantity,
cbe.price AS bookingExtra_price,
cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
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
FROM {$this->appointmentsTable} a
INNER JOIN {$this->bookingsTable} cb ON cb.appointmentId = a.id
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
WHERE a.bookingStart BETWEEN $startCurrentDate AND $endCurrentDate
{$whereStatuses}
AND a.notifyParticipants = 1 AND
a.id NOT IN (
SELECT nl.appointmentId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notificationId} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.appointmentId IS NOT NULL
)"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find appointments in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return AppointmentFactory::createCollection($rows);
}
/**
* Return a collection of tomorrow events where customer notification is not sent and should be.
*
* @param $notificationId
*
* @return Collection
*
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getCustomersNextDayEvents($notificationId, $nextDay = true)
{
$couponsTable = CouponsTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$eventsTable = EventsTable::getTableName();
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$eventsProvidersTable = EventsProvidersTable::getTableName();
$eventsTicketsTable = EventsTicketsTable::getTableName();
$bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$startDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(0, 0, 0)->format('Y-m-d H:i:s')
);
$endDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(23, 59, 59)->format('Y-m-d H:i:s')
);
if ($nextDay) {
$startDate = $startDate->modify('+1 day');
$endDate = $endDate->modify('+1 day');
}
$startCurrentDate = "STR_TO_DATE('" . $startDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$endCurrentDate = "STR_TO_DATE('" . $endDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
try {
$statement = $this->connection->query(
"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.recurringUntil AS event_recurringUntil,
e.maxCapacity AS event_maxCapacity,
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.zoomUserId AS event_zoomUserId,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.organizerId AS event_organizerId,
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.googleMeetUrl AS event_googleMeetUrl,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.utcOffset AS booking_utcOffset,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.persons AS booking_persons,
cb.created AS booking_created,
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.data AS payment_data,
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.gender AS provider_gender,
pu.pictureFullPath AS provider_pictureFullPath,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.translations AS provider_translations,
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,
t.id AS ticket_id,
t.name AS ticket_name,
t.enabled AS ticket_enabled,
t.price AS ticket_price,
t.spots AS ticket_spots,
t.waitingListSpots AS ticket_waiting_list_spots,
t.dateRanges AS ticket_dateRanges,
t.translations AS ticket_translations,
cbt.id AS booking_ticket_id,
cbt.eventTicketId AS booking_ticket_eventTicketId,
cbt.price AS booking_ticket_price,
cbt.persons AS booking_ticket_persons
FROM {$eventsTable} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
INNER JOIN {$this->bookingsTable} cb ON cb.id = cbe.customerBookingId
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
LEFT JOIN {$this->usersTable} pu ON pu.id = epr.userId
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
LEFT JOIN {$eventsTicketsTable} t ON t.eventId = e.id
LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id AND cbt.eventTicketId = t.id
WHERE ep.periodStart BETWEEN {$startCurrentDate} AND {$endCurrentDate}
AND cb.status = 'approved'
AND e.status = 'approved'
AND e.notifyParticipants = 1 AND
e.id NOT IN (
SELECT nl.eventId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notificationId} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.eventId IS NOT NULL
)"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find appointments in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return EventFactory::createCollection($rows);
}
/**
* Return a collection of tomorrow appointments where provider notification is not sent and should be.
*
* @param int $notificationId
* @param bool $nextDay
* @param array $statuses
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getProvidersNextDayAppointments($notificationId, $nextDay, $statuses)
{
$couponsTable = CouponsTable::getTableName();
$customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$startDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(0, 0, 0)->format('Y-m-d H:i:s')
);
$endDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(23, 59, 59)->format('Y-m-d H:i:s')
);
if ($nextDay) {
$startDate = $startDate->modify('+1 day');
$endDate = $endDate->modify('+1 day');
}
$startCurrentDate = "STR_TO_DATE('" . $startDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$endCurrentDate = "STR_TO_DATE('" . $endDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$whereStatuses = [];
foreach ($statuses as $key => $status) {
$whereStatuses[] = "cb.status = '$status'";
}
$whereStatuses = $whereStatuses ? 'AND (' . implode(' OR ', $whereStatuses) . ')' : '';
try {
$statement = $this->connection->query(
"SELECT
a.id AS appointment_id,
a.bookingStart AS appointment_bookingStart,
a.bookingEnd AS appointment_bookingEnd,
a.notifyParticipants AS appointment_notifyParticipants,
a.serviceId AS appointment_serviceId,
a.providerId AS appointment_providerId,
a.locationId AS appointment_locationId,
a.internalNotes AS appointment_internalNotes,
a.status AS appointment_status,
a.zoomMeeting AS appointment_zoom_meeting,
a.lessonSpace AS appointment_lesson_space,
a.googleMeetUrl AS appointment_google_meet_url,
a.microsoftTeamsUrl AS appointment_microsoft_teams_url,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.persons AS booking_persons,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.duration AS booking_duration,
cb.created AS booking_created,
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.data AS payment_data,
cbe.id AS bookingExtra_id,
cbe.extraId AS bookingExtra_extraId,
cbe.customerBookingId AS bookingExtra_customerBookingId,
cbe.quantity AS bookingExtra_quantity,
cbe.price AS bookingExtra_price,
cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
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
FROM {$this->appointmentsTable} a
INNER JOIN {$this->bookingsTable} cb ON cb.appointmentId = a.id
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
WHERE a.bookingStart BETWEEN $startCurrentDate AND $endCurrentDate
{$whereStatuses}
AND a.id NOT IN (
SELECT nl.appointmentId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notificationId} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.appointmentId IS NOT NULL
)"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find appointments in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return AppointmentFactory::createCollection($rows);
}
/**
* Return a collection of tomorrow events where provider notification is not sent and should be.
*
* @param $notificationId
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getProvidersNextDayEvents($notificationId, $nextDay)
{
$couponsTable = CouponsTable::getTableName();
$eventsTable = EventsTable::getTableName();
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$eventsProvidersTable = EventsProvidersTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$eventsTicketsTable = EventsTicketsTable::getTableName();
$bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$startDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(0, 0, 0)->format('Y-m-d H:i:s')
);
$endDate = DateTimeService::getCustomDateTimeObjectInUtc(
DateTimeService::getNowDateTimeObject()->setTime(23, 59, 59)->format('Y-m-d H:i:s')
);
if ($nextDay) {
$startDate = $startDate->modify('+1 day');
$endDate = $endDate->modify('+1 day');
}
$startCurrentDate = "STR_TO_DATE('" . $startDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
$endCurrentDate = "STR_TO_DATE('" . $endDate->format('Y-m-d H:i:s') . "', '%Y-%m-%d %H:%i:%s')";
try {
$statement = $this->connection->query(
"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.recurringUntil AS event_recurringUntil,
e.maxCapacity AS event_maxCapacity,
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.zoomUserId AS event_zoomUserId,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.organizerId AS event_organizerId,
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.googleMeetUrl AS event_googleMeetUrl,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
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.gender AS provider_gender,
pu.pictureFullPath AS provider_pictureFullPath,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.timeZone AS provider_timeZone,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.persons AS booking_persons,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.created AS booking_created,
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.data AS payment_data,
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,
t.id AS ticket_id,
t.name AS ticket_name,
t.enabled AS ticket_enabled,
t.price AS ticket_price,
t.spots AS ticket_spots,
t.waitingListSpots AS ticket_waiting_list_spots,
t.dateRanges AS ticket_dateRanges,
t.translations AS ticket_translations,
cbt.id AS booking_ticket_id,
cbt.eventTicketId AS booking_ticket_eventTicketId,
cbt.price AS booking_ticket_price,
cbt.persons AS booking_ticket_persons
FROM {$eventsTable} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
INNER JOIN {$this->bookingsTable} cb ON cb.id = cbe.customerBookingId
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
LEFT JOIN {$eventsProvidersTable} epr ON epr.eventId = e.id
LEFT JOIN {$this->usersTable} pu ON pu.id = epr.userId
LEFT JOIN {$eventsTicketsTable} t ON t.eventId = e.id
LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id AND cbt.eventTicketId = t.id
WHERE ep.periodStart BETWEEN {$startCurrentDate} AND {$endCurrentDate}
AND cb.status = 'approved'
AND e.status = 'approved'
AND e.id NOT IN (
SELECT nl.eventId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notificationId} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.eventId IS NOT NULL
)"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find events in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return EventFactory::createCollection($rows);
}
/**
* Return a collection of today's past appointments where follow up notification is not sent and should be.
*
* @param Notification $notification
* @param array $statuses
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getScheduledAppointments($notification, $statuses = [])
{
$couponsTable = CouponsTable::getTableName();
$customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
try {
$currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";
$where = '';
if ($notification->getTimeAfter()) {
$timeAfter =
apply_filters(
'amelia_modify_scheduled_notification_time_after',
$notification->getTimeAfter()->getValue(),
$notification->toArray()
);
$lastTime = apply_filters('amelia_modify_scheduled_notification_last_time', $timeAfter + 259200, $notification->toArray());
$where =
"{$currentDateTime} BETWEEN DATE_ADD(a.bookingEnd, INTERVAL {$timeAfter} SECOND) AND DATE_ADD(a.bookingEnd, INTERVAL {$lastTime} SECOND)";
} elseif ($notification->getTimeBefore()) {
$timeBefore =
apply_filters(
'amelia_modify_scheduled_notification_time_before',
$notification->getTimeBefore()->getValue(),
$notification->toArray()
);
$where =
"({$currentDateTime} BETWEEN
DATE_SUB(a.bookingStart, INTERVAL {$timeBefore} SECOND) AND a.bookingStart) AND
(a.bookingStart >= DATE_ADD(cb.created, INTERVAL {$timeBefore} SECOND))";
}
$whereStatuses = [];
foreach ($statuses as $key => $status) {
$whereStatuses[] = "cb.status = '$status'";
}
$whereStatuses = $whereStatuses ? ($where ? ' AND ' : '') . '(' . implode(' OR ', $whereStatuses) . ')' : '';
$statement = $this->connection->query(
"SELECT
a.id AS appointment_id,
a.bookingStart AS appointment_bookingStart,
a.bookingEnd AS appointment_bookingEnd,
a.notifyParticipants AS appointment_notifyParticipants,
a.createPaymentLinks AS appointment_createPaymentLinks,
a.serviceId AS appointment_serviceId,
a.providerId AS appointment_providerId,
a.locationId AS appointment_locationId,
a.internalNotes AS appointment_internalNotes,
a.status AS appointment_status,
a.googleMeetUrl AS appointment_google_meet_url,
a.lessonSpace AS appointment_lesson_space,
a.zoomMeeting AS appointment_zoom_meeting,
a.microsoftTeamsUrl AS appointment_microsoft_teams_url,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.utcOffset AS booking_utcOffset,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.persons AS booking_persons,
cb.duration AS booking_duration,
cb.created AS booking_created,
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.data AS payment_data,
cbe.id AS bookingExtra_id,
cbe.extraId AS bookingExtra_extraId,
cbe.customerBookingId AS bookingExtra_customerBookingId,
cbe.quantity AS bookingExtra_quantity,
cbe.price AS bookingExtra_price,
cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
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
FROM {$this->appointmentsTable} a
INNER JOIN {$this->bookingsTable} cb ON cb.appointmentId = a.id
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
WHERE {$where}
AND a.notifyParticipants = 1
{$whereStatuses}
AND a.id NOT IN (
SELECT nl.appointmentId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notification->getId()->getValue()} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.appointmentId IS NOT NULL
)"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find appointments in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return AppointmentFactory::createCollection($rows);
}
/**
* Return a collection of today's past appointments where follow-up notification is not sent and should be.
*
* @param Notification $notification
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getScheduledEvents($notification)
{
$eventsTable = EventsTable::getTableName();
$eventsPeriodsTable = EventsPeriodsTable::getTableName();
$customerBookingsEventsPeriods = CustomerBookingsToEventsPeriodsTable::getTableName();
$paymentsTable = PaymentsTable::getTableName();
$eventsTicketsTable = EventsTicketsTable::getTableName();
$bookingsTicketsTable = CustomerBookingToEventsTicketsTable::getTableName();
$currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";
$where = "WHERE e.notifyParticipants = 1
AND cb.status = 'approved'
AND e.status = 'approved'
AND e.id NOT IN (
SELECT nl.eventId
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.id = {$notification->getId()->getValue()} AND (nl.sent IS NULL OR nl.sent = 1) AND nl.eventId IS NOT NULL
)";
if ($notification->getTimeAfter()) {
$timeAfter = $notification->getTimeAfter()->getValue();
$lastTime = $timeAfter + 432000;
$where .=
" AND {$currentDateTime} BETWEEN DATE_ADD(ep.periodEnd, INTERVAL {$timeAfter} SECOND)
AND DATE_ADD(ep.periodEnd, INTERVAL {$lastTime} SECOND)";
} elseif ($notification->getTimeBefore()) {
$timeBefore = $notification->getTimeBefore()->getValue();
$where .=
" AND ({$currentDateTime} BETWEEN DATE_SUB(ep.periodStart, INTERVAL {$timeBefore} SECOND) AND ep.periodStart)
AND (ep.periodStart >= DATE_ADD(p.created, INTERVAL {$timeBefore} SECOND))";
}
try {
$statement = $this->connection->query(
"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.recurringUntil AS event_recurringUntil,
e.recurringInterval AS event_recurringInterval,
e.bringingAnyone AS event_bringingAnyone,
e.bookMultipleTimes AS event_bookMultipleTimes,
e.maxCapacity AS event_maxCapacity,
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.zoomUserId AS event_zoomUserId,
e.deposit AS event_deposit,
e.depositPayment AS event_depositPayment,
e.depositPerPerson AS event_depositPerPerson,
e.organizerId AS event_organizerId,
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.lessonSpace AS event_periodLessonSpace,
ep.zoomMeeting AS event_periodZoomMeeting,
ep.googleMeetUrl AS event_googleMeetUrl,
ep.microsoftTeamsUrl AS event_microsoftTeamsUrl,
cb.id AS booking_id,
cb.customerId AS booking_customerId,
cb.status AS booking_status,
cb.price AS booking_price,
cb.customFields AS booking_customFields,
cb.info AS booking_info,
cb.utcOffset AS booking_utcOffset,
cb.aggregatedPrice AS booking_aggregatedPrice,
cb.persons AS booking_persons,
cb.duration AS booking_duration,
cb.created AS booking_created,
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.data AS payment_data,
t.id AS ticket_id,
t.name AS ticket_name,
t.enabled AS ticket_enabled,
t.price AS ticket_price,
t.spots AS ticket_spots,
t.waitingListSpots AS ticket_waiting_list_spots,
t.dateRanges AS ticket_dateRanges,
t.translations AS ticket_translations,
cbt.id AS booking_ticket_id,
cbt.eventTicketId AS booking_ticket_eventTicketId,
cbt.price AS booking_ticket_price,
cbt.persons AS booking_ticket_persons
FROM {$eventsTable} e
INNER JOIN {$eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$customerBookingsEventsPeriods} cbe ON cbe.eventPeriodId = ep.id
INNER JOIN {$this->bookingsTable} cb ON cb.id = cbe.customerBookingId
LEFT JOIN {$paymentsTable} p ON p.customerBookingId = cb.id
LEFT JOIN {$eventsTicketsTable} t ON t.eventId = e.id
LEFT JOIN {$bookingsTicketsTable} cbt ON cbt.customerBookingId = cb.id AND cbt.eventTicketId = t.id
{$where}"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find events in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return EventFactory::createCollection($rows);
}
/**
* Returns a collection of customers that have birthday on today's date and where notification is not sent
*
* @param $notificationType
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getBirthdayCustomers($notificationType)
{
$currentDate = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d')";
$params = [
':type' => AbstractUser::USER_ROLE_CUSTOMER,
':statusVisible' => Status::VISIBLE,
];
try {
$statement = $this->connection->prepare(
"SELECT * FROM {$this->usersTable} as u
WHERE
u.type = :type AND
u.status = :statusVisible AND
MONTH(u.birthday) = MONTH({$currentDate}) AND
DAY(u.birthday) = DAY({$currentDate}) AND
u.id NOT IN (
SELECT nl.userID
FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE n.name = 'customer_birthday_greeting' AND n.type = '{$notificationType}' AND
YEAR(nl.sentDateTime) = YEAR({$currentDate}) AND (nl.sent IS NULL OR nl.sent = 1)
)"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$items = [];
foreach ($rows as $row) {
$items[] = call_user_func([UserFactory::class, 'create'], $row);
}
return new Collection($items);
}
/**
* Returns a collection of undelivered notifications
*
* @param string $type
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getUndeliveredNotifications($type)
{
$params = [
':type' => $type,
];
$currentDateTime = "STR_TO_DATE('" . DateTimeService::getNowDateTimeInUtc() . "', '%Y-%m-%d %H:%i:%s')";
$pastDateTime =
"STR_TO_DATE('" .
DateTimeService::getNowDateTimeObjectInUtc()->modify('-1 day')->format('Y-m-d H:i:s') .
"', '%Y-%m-%d %H:%i:%s')";
try {
$statement = $this->connection->prepare(
"SELECT nl.* FROM {$this->table} nl
INNER JOIN {$this->notificationsTable} n ON nl.notificationId = n.id
WHERE
nl.sent = 0 AND
{$currentDateTime} > DATE_ADD(nl.sentDateTime, INTERVAL 300 SECOND) AND
{$pastDateTime} < nl.sentDateTime AND
nl.data IS NOT NULL AND
n.type = :type"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$items = [];
foreach ($rows as $row) {
$items[] = call_user_func([static::FACTORY, 'create'], $row);
}
return new Collection($items);
}
/**
* @param int $userId
* @param string $type
* @param string $entityType
* @param int $entityId
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getSentNotificationsByUserAndEntity($userId, $type, $entityType, $entityId)
{
$entityColumn = '';
switch ($entityType) {
case (Entities::APPOINTMENT):
$entityColumn = 'nl.appointmentId';
break;
case (Entities::EVENT):
$entityColumn = 'nl.eventId';
break;
case (Entities::PACKAGE):
$entityColumn = 'nl.packageCustomerId';
break;
}
$params = [
':entityId' => $entityId,
':userId' => $userId,
':type' => $type,
];
try {
$statement = $this->connection->prepare(
"SELECT * FROM {$this->table} nl
WHERE nl.userId = :userId
AND {$entityColumn} = :entityId
AND nl.notificationId IN (SELECT id FROM {$this->notificationsTable} WHERE type = :type)
ORDER BY nl.sentDateTime DESC"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$items = [];
foreach ($rows as $row) {
$items[] = call_user_func([static::FACTORY, 'create'], $row);
}
return new Collection($items);
}
}