<?php
/**
* @copyright © Melograno Ventures. All rights reserved.
* @licence See LICENCE.md for license details.
*/
namespace AmeliaBooking\Infrastructure\Repository\Payment;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Entities;
use AmeliaBooking\Domain\Entity\Payment\Payment;
use AmeliaBooking\Domain\Factory\Payment\PaymentFactory;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesCustomersServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\CustomerBookingsToExtrasTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Coupon\CouponsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;
/**
* Class PaymentRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Payment
*/
class PaymentRepository extends AbstractRepository
{
/** @var string */
protected $appointmentsTable;
/** @var string */
protected $bookingsTable;
/** @var string */
protected $servicesTable;
/** @var string */
protected $usersTable;
/** @var string */
protected $eventsTable;
/** @var string */
protected $eventsProvidersTable;
/** @var string */
protected $eventsPeriodsTable;
/** @var string */
protected $customerBookingsToEventsPeriodsTable;
/** @var string */
protected $packagesTable;
/** @var string */
protected $packagesCustomersTable;
/** @var string */
protected $packagesCustomersServiceTable;
/**
* @param Connection $connection
* @param string $table
* @param string $appointmentsTable
* @param string $bookingsTable
* @param string $servicesTable
* @param string $usersTable
* @param string $eventsTable
* @param string $eventsProvidersTable
* @param string $eventsPeriodsTable
* @param string $customerBookingsToEventsPeriodsTable
* @param string $packagesTable
* @param string $packagesCustomersTable
*/
public function __construct(
Connection $connection,
$table,
$appointmentsTable,
$bookingsTable,
$servicesTable,
$usersTable,
$eventsTable,
$eventsProvidersTable,
$eventsPeriodsTable,
$customerBookingsToEventsPeriodsTable,
$packagesTable,
$packagesCustomersTable
) {
parent::__construct($connection, $table);
$this->appointmentsTable = $appointmentsTable;
$this->bookingsTable = $bookingsTable;
$this->servicesTable = $servicesTable;
$this->usersTable = $usersTable;
$this->eventsTable = $eventsTable;
$this->eventsProvidersTable = $eventsProvidersTable;
$this->eventsPeriodsTable = $eventsPeriodsTable;
$this->customerBookingsToEventsPeriodsTable = $customerBookingsToEventsPeriodsTable;
$this->packagesTable = $packagesTable;
$this->packagesCustomersTable = $packagesCustomersTable;
$this->packagesCustomersServiceTable = PackagesCustomersServicesTable::getTableName();
}
public const FACTORY = PaymentFactory::class;
/**
* @param Payment $entity
*
* @return int
* @throws QueryExecutionException
*/
public function add($entity)
{
$data = $entity->toArray();
$params = [
':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
':parentId' => $data['parentId'] ? $data['parentId'] : null,
':amount' => $data['amount'],
':dateTime' => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
':status' => $data['status'],
':gateway' => $data['gateway'],
':gatewayTitle' => $data['gatewayTitle'],
':data' => $data['data'],
':entity' => $data['entity'],
':created' => DateTimeService::getNowDateTimeInUtc(),
':wcOrderId' => !empty($data['wcOrderId']) ? $data['wcOrderId'] : null,
':transactionId' => !empty($data['transactionId']) ? $data['transactionId'] : null,
':wcOrderItemId' => !empty($data['wcOrderItemId']) ? $data['wcOrderItemId'] : null,
];
if (!empty($data['invoiceNumber'])) {
$params[':invoiceNumber'] = $data['invoiceNumber'];
$invoiceNumberText = ":invoiceNumber";
} else {
$invoiceNumberText = "(SELECT COALESCE(MAX(invoiceNumber), 0) + 1 FROM {$this->table} p)";
}
if ($data['parentId']) {
$params[':actionsCompleted'] = null;
} else {
$params[':actionsCompleted'] = !empty($data['actionsCompleted']) ? 1 : 0;
}
try {
$statement = $this->connection->prepare(
"INSERT INTO
{$this->table}
(
`customerBookingId`,
`packageCustomerId`,
`parentId`,
`amount`,
`dateTime`,
`status`,
`gateway`,
`gatewayTitle`,
`data`, `entity`,
`actionsCompleted`,
`created`,
`wcOrderId`,
`wcOrderItemId`,
`transactionId`,
`invoiceNumber`
) VALUES (
:customerBookingId,
:packageCustomerId,
:parentId,
:amount,
:dateTime,
:status,
:gateway,
:gatewayTitle,
:data,
:entity,
:actionsCompleted,
:created,
:wcOrderId,
:wcOrderItemId,
:transactionId,
{$invoiceNumberText}
)"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $this->connection->lastInsertId();
}
/**
* @param int $id
* @param Payment $entity
*
* @return bool
* @throws QueryExecutionException
*/
public function update($id, $entity)
{
$data = $entity->toArray();
$params = [
':customerBookingId' => $data['customerBookingId'] ? $data['customerBookingId'] : null,
':packageCustomerId' => $data['packageCustomerId'] ? $data['packageCustomerId'] : null,
':parentId' => $data['parentId'] ? $data['parentId'] : null,
':amount' => $data['amount'],
':dateTime' => DateTimeService::getCustomDateTimeInUtc($data['dateTime']),
':status' => $data['status'],
':gateway' => $data['gateway'],
':gatewayTitle' => $data['gatewayTitle'],
':data' => $data['data'],
':transactionId' => $data['transactionId'],
':id' => $id,
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
`customerBookingId` = :customerBookingId,
`packageCustomerId` = :packageCustomerId,
`parentId` = :parentId,
`amount` = :amount,
`dateTime` = :dateTime,
`status` = :status,
`gateway` = :gateway,
`gatewayTitle` = :gatewayTitle,
`data` = :data,
`transactionId` = :transactionId
WHERE
id = :id"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
/**
* @param array $criteria
*
* @return Collection
* @throws QueryExecutionException
*/
public function getByCriteria($criteria)
{
$result = new Collection();
$params = [];
$where = [];
if (!empty($criteria['bookingIds'])) {
$queryBookings = [];
foreach ($criteria['bookingIds'] as $index => $value) {
$param = ':id' . $index;
$queryBookings[] = $param;
$params[$param] = $value;
}
$where[] = 'customerBookingId IN (' . implode(', ', $queryBookings) . ')';
}
if (!empty($criteria['packageCustomerId'])) {
$params[':packageCustomerId'] = $criteria['packageCustomerId'];
$where[] = 'packageCustomerId = :packageCustomerId';
}
if (!empty($criteria['ids'])) {
$queryIds = [];
foreach ($criteria['ids'] as $index => $value) {
$param = ':id' . $index;
$queryIds[] = $param;
$params[$param] = $value;
}
$where[] = 'id IN (' . implode(', ', $queryIds) . ')';
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
id AS id,
customerBookingId AS customerBookingId,
packageCustomerId AS packageCustomerId,
parentId AS parentId,
invoiceNumber AS invoiceNumber,
amount AS amount,
dateTime AS dateTime,
status AS status,
gateway AS gateway,
gatewayTitle AS gatewayTitle,
data AS data
FROM {$this->table}
{$where}"
);
$statement->execute($params);
while ($row = $statement->fetch()) {
$result->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
}
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $result;
}
/**
* @param array $criteria
* @param int $itemsPerPage
* @param boolean $invoice
*
* @return array
* @throws QueryExecutionException
*/
public function getFilteredIds($criteria, $itemsPerPage = null, $invoice = false): array
{
$params = [];
$appointmentParams1 = [];
$appointmentParams2 = [];
$eventParams = [];
$whereAppointment1 = [];
$whereAppointment2 = [];
$whereEvent = [];
if ($invoice) {
$whereAppointment1[] = 'p.parentId IS NULL';
$whereAppointment2[] = 'p.parentId IS NULL';
$whereEvent[] = 'p.parentId IS NULL';
}
$basedOnDate = 'created';
if (!empty($criteria['ids'])) {
$queryIds1 = [];
$queryIds2 = [];
$queryIds3 = [];
foreach ($criteria['ids'] as $index => $value) {
$param1 = ':id0' . $index;
$param2 = ':id1' . $index;
$param3 = ':id2' . $index;
$queryIds1[] = $param1;
$queryIds2[] = $param2;
$queryIds3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'p.id IN (' . implode(', ', $queryIds1) . ')';
$whereAppointment2[] = 'p.id IN (' . implode(', ', $queryIds2) . ')';
$whereEvent[] = 'p.id IN (' . implode(', ', $queryIds3) . ')';
}
if (!empty($criteria['dates'])) {
$whereAppointment1[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
$whereAppointment2[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
$appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$appointmentParams1[':paymentAppointmentTo1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
$appointmentParams2[':paymentAppointmentTo2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
$whereEvent[] = "(p.{$basedOnDate} BETWEEN :paymentEventFrom AND :paymentEventTo)";
$eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$eventParams[':paymentEventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
}
if (!empty($criteria['customerId'])) {
$criteria['customers'][] = $criteria['customerId'];
}
if (!empty($criteria['customers'])) {
$queryCustomers1 = [];
$queryCustomers2 = [];
$queryCustomers3 = [];
foreach ((array)$criteria['customers'] as $index => $value) {
$param1 = ':customer0' . $index;
$param2 = ':customer1' . $index;
$param3 = ':customer2' . $index;
$queryCustomers1[] = $param1;
$queryCustomers2[] = $param2;
$queryCustomers3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'cb.customerId IN (' . implode(', ', $queryCustomers1) . ')';
$whereAppointment2[] = 'pc.customerId IN (' . implode(', ', $queryCustomers2) . ')';
$whereEvent[] = 'cb.customerId IN (' . implode(', ', $queryCustomers3) . ')';
}
$eventsProvidersJoin = '';
if (!empty($criteria['providerId'])) {
$criteria['providers'][] = $criteria['providerId'];
}
if (!empty($criteria['providers'])) {
$queryProviders1 = [];
$queryProviders2 = [];
$queryProviders3 = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param1 = ':provider0' . $index;
$param2 = ':provider1' . $index;
$param3 = ':provider2' . $index;
$queryProviders1[] = $param1;
$queryProviders2[] = $param2;
$queryProviders3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'a.providerId IN (' . implode(', ', $queryProviders1) . ')';
$whereAppointment2[] = 'a.providerId IN (' . implode(', ', $queryProviders2) . ')';
$whereEvent[] = 'epu.userId IN (' . implode(', ', $queryProviders3) . ')';
$eventsProvidersJoin = "
INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
";
}
if (!empty($criteria['services'])) {
$queryServices1 = [];
$queryServices2 = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param1 = ':service0' . $index;
$param2 = ':service1' . $index;
$queryServices1[] = $param1;
$queryServices2[] = $param2;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
}
$whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
$whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
}
$appointments2ProvidersServicesJoin = '';
if (!empty($criteria['providers']) || !empty($criteria['services'])) {
$appointments2ProvidersServicesJoin = "
INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
";
}
if (!empty($criteria['status'])) {
$criteria['statuses'][] = $criteria['status'];
}
if (!empty($criteria['statuses'])) {
$queryStatuses1 = [];
$queryStatuses2 = [];
$queryStatuses3 = [];
foreach ($criteria['statuses'] as $index => $value) {
$param1 = ':status0' . $index;
$param2 = ':status1' . $index;
$param3 = ':status2' . $index;
$queryStatuses1[] = $param1;
$queryStatuses2[] = $param2;
$queryStatuses3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'p.status IN (' . implode(', ', $queryStatuses1) . ')';
$whereAppointment2[] = 'p.status IN (' . implode(', ', $queryStatuses2) . ')';
$whereEvent[] = 'p.status IN (' . implode(', ', $queryStatuses3) . ')';
}
if (!empty($criteria['packages'])) {
$queryPackages = [];
foreach ((array)$criteria['packages'] as $index => $value) {
$param = ':package' . $index;
$queryPackages[] = $param;
$appointmentParams2[$param] = $value;
}
$whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
FROM {$this->packagesCustomersTable} pc
WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
}
if (!empty($criteria['events'])) {
$queryEvents = [];
foreach ((array)$criteria['events'] as $index => $value) {
$param = ':event' . $index;
$queryEvents[] = $param;
$eventParams[$param] = $value;
}
$whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
FROM {$this->eventsTable} e
INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id
WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
}
$whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
$whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
$whereEvent = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';
$groupBy = '';
$groupByAppointment1Clause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
$groupByAppointment2Clause = empty($criteria['separateRows']) ? "GROUP BY p.packageCustomerId" : "";
$groupByEventClause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
if ($invoice) {
$groupBy = 'GROUP BY IFNULL(invoiceNumber, id)';
$groupByAppointment1Clause = '';
$groupByAppointment2Clause = '';
$groupByEventClause = '';
}
$appointmentQuery1 = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
'appointment' AS type
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
WHERE 1=1 {$whereAppointment1} {$groupByAppointment1Clause} ORDER BY p.id ASC";
$appointmentQuery2 = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
'package' AS type
FROM {$this->table} p
INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
{$appointments2ProvidersServicesJoin}
WHERE 1=1 {$whereAppointment2} {$groupByAppointment2Clause} ORDER BY p.id ASC";
$eventQuery = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
'event' AS type
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
{$eventsProvidersJoin}
WHERE 1=1 {$whereEvent} {$groupByEventClause} ORDER BY p.id ASC";
$result = [];
if (isset($criteria['events'], $criteria['services'])) {
return $result;
} elseif (isset($criteria['services'])) {
$paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2})";
$params = array_merge($params, $appointmentParams1, $appointmentParams2);
} elseif (isset($criteria['events'])) {
$paymentQuery = "({$eventQuery})";
$params = array_merge($params, $eventParams);
} elseif (isset($criteria['packages'])) {
$paymentQuery = "({$appointmentQuery2})";
$params = array_merge($params, $appointmentParams2);
} else {
$paymentQuery = "({$appointmentQuery1}) UNION ALL ({$appointmentQuery2}) UNION ALL ({$eventQuery})";
$params = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
}
$limit = $this->getLimit(
!empty($criteria['page']) ? (int)$criteria['page'] : 0,
$itemsPerPage ?: (!empty($criteria['limit']) ? (int)$criteria['limit'] : 0)
);
$bookingTypeCondition = $this->buildBookingTypeCondition($criteria, $params);
try {
$order = "ORDER BY id, {$basedOnDate}";
if (!empty($criteria['sort'])) {
$order = "ORDER BY {$criteria['sort']['field']} {$criteria['sort']['order']}";
}
$statement = $this->connection->prepare(
"SELECT * FROM ({$paymentQuery}) payments
{$bookingTypeCondition}
{$groupBy}
{$order}
{$limit}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
foreach ($rows as $row) {
$result[(int)$row['id']] = $row['type'];
}
return $result;
}
/**
* @param array $criteria
* @param boolean $invoice
*
* @return int
* @throws QueryExecutionException
*/
public function getFilteredIdsCount($criteria, $invoice = false): int
{
$params = [];
$appointmentParams1 = [];
$appointmentParams2 = [];
$eventParams = [];
$whereAppointment1 = [];
$whereAppointment2 = [];
$whereEvent = [];
if ($invoice) {
$whereAppointment1[] = 'p.parentId IS NULL';
$whereAppointment2[] = 'p.parentId IS NULL';
$whereEvent[] = 'p.parentId IS NULL';
}
$basedOnDate = 'created';
if (!empty($criteria['ids'])) {
$queryIds1 = [];
$queryIds2 = [];
$queryIds3 = [];
foreach ($criteria['ids'] as $index => $value) {
$param1 = ':id0' . $index;
$param2 = ':id1' . $index;
$param3 = ':id2' . $index;
$queryIds1[] = $param1;
$queryIds2[] = $param2;
$queryIds3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'p.id IN (' . implode(', ', $queryIds1) . ')';
$whereAppointment2[] = 'p.id IN (' . implode(', ', $queryIds2) . ')';
$whereEvent[] = 'p.id IN (' . implode(', ', $queryIds3) . ')';
}
if (!empty($criteria['dates'])) {
$whereAppointment1[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom1 AND :paymentAppointmentTo1)";
$whereAppointment2[] = "(p.{$basedOnDate} BETWEEN :paymentAppointmentFrom2 AND :paymentAppointmentTo2)";
$appointmentParams1[':paymentAppointmentFrom1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$appointmentParams2[':paymentAppointmentFrom2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$appointmentParams1[':paymentAppointmentTo1'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
$appointmentParams2[':paymentAppointmentTo2'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
$whereEvent[] = "(p.{$basedOnDate} BETWEEN :paymentEventFrom AND :paymentEventTo)";
$eventParams[':paymentEventFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$eventParams[':paymentEventTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
}
if (!empty($criteria['customerId'])) {
$criteria['customers'][] = $criteria['customerId'];
}
if (!empty($criteria['customers'])) {
$queryCustomers1 = [];
$queryCustomers2 = [];
$queryCustomers3 = [];
foreach ((array)$criteria['customers'] as $index => $value) {
$param1 = ':customer0' . $index;
$param2 = ':customer1' . $index;
$param3 = ':customer2' . $index;
$queryCustomers1[] = $param1;
$queryCustomers2[] = $param2;
$queryCustomers3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'cb.customerId IN (' . implode(', ', $queryCustomers1) . ')';
$whereAppointment2[] = 'pc.customerId IN (' . implode(', ', $queryCustomers2) . ')';
$whereEvent[] = 'cb.customerId IN (' . implode(', ', $queryCustomers3) . ')';
}
$eventsProvidersJoin = '';
if (!empty($criteria['providerId'])) {
$criteria['providers'][] = $criteria['providerId'];
}
if (!empty($criteria['providers'])) {
$queryProviders1 = [];
$queryProviders2 = [];
$queryProviders3 = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param1 = ':provider0' . $index;
$param2 = ':provider1' . $index;
$param3 = ':provider2' . $index;
$queryProviders1[] = $param1;
$queryProviders2[] = $param2;
$queryProviders3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'a.providerId IN (' . implode(', ', $queryProviders1) . ')';
$whereAppointment2[] = 'a.providerId IN (' . implode(', ', $queryProviders2) . ')';
$whereEvent[] = 'epu.userId IN (' . implode(', ', $queryProviders3) . ')';
$eventsProvidersJoin = "
INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
INNER JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
";
}
if (!empty($criteria['services'])) {
$queryServices1 = [];
$queryServices2 = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param1 = ':service0' . $index;
$param2 = ':service1' . $index;
$queryServices1[] = $param1;
$queryServices2[] = $param2;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
}
$whereAppointment1[] = 'a.serviceId IN (' . implode(', ', $queryServices1) . ')';
$whereAppointment2[] = 'a.serviceId IN (' . implode(', ', $queryServices2) . ')';
}
$appointments2ProvidersServicesJoin = '';
if (!empty($criteria['providers']) || !empty($criteria['services'])) {
$appointments2ProvidersServicesJoin = "
INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
INNER JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
";
}
if (!empty($criteria['status'])) {
$criteria['statuses'][] = $criteria['status'];
}
if (!empty($criteria['statuses'])) {
$queryStatuses1 = [];
$queryStatuses2 = [];
$queryStatuses3 = [];
foreach ($criteria['statuses'] as $index => $value) {
$param1 = ':status0' . $index;
$param2 = ':status1' . $index;
$param3 = ':status2' . $index;
$queryStatuses1[] = $param1;
$queryStatuses2[] = $param2;
$queryStatuses3[] = $param3;
$appointmentParams1[$param1] = $value;
$appointmentParams2[$param2] = $value;
$eventParams[$param3] = $value;
}
$whereAppointment1[] = 'p.status IN (' . implode(', ', $queryStatuses1) . ')';
$whereAppointment2[] = 'p.status IN (' . implode(', ', $queryStatuses2) . ')';
$whereEvent[] = 'p.status IN (' . implode(', ', $queryStatuses3) . ')';
}
if (!empty($criteria['packages'])) {
$queryPackages = [];
foreach ((array)$criteria['packages'] as $index => $value) {
$param = ':package' . $index;
$queryPackages[] = $param;
$appointmentParams2[$param] = $value;
}
$whereAppointment2[] = "p.packageCustomerId IN (SELECT pc.id
FROM {$this->packagesCustomersTable} pc
WHERE pc.packageId IN (" . implode(', ', $queryPackages) . '))';
}
if (!empty($criteria['events'])) {
$queryEvents = [];
foreach ((array)$criteria['events'] as $index => $value) {
$param = ':event' . $index;
$queryEvents[] = $param;
$eventParams[$param] = $value;
}
$whereEvent[] = "p.customerBookingId IN (SELECT cbe.customerBookingId
FROM {$this->eventsTable} e
INNER JOIN {$this->eventsPeriodsTable} ep ON ep.eventId = e.id
INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.eventPeriodId = ep.id
WHERE e.id IN (" . implode(', ', $queryEvents) . '))';
}
$whereAppointment1 = $whereAppointment1 ? ' AND ' . implode(' AND ', $whereAppointment1) : '';
$whereAppointment2 = $whereAppointment2 ? ' AND ' . implode(' AND ', $whereAppointment2) : '';
$whereEvent = $whereEvent ? ' AND ' . implode(' AND ', $whereEvent) : '';
$groupByAppointment1Clause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
$groupByAppointment2Clause = empty($criteria['separateRows']) ? "GROUP BY p.packageCustomerId" : "";
$groupByEventClause = empty($criteria['separateRows']) ? "GROUP BY p.customerBookingId" : "";
// Build list-style subqueries mirroring getFilteredIds (no ORDER BY / LIMIT here)
$listAppointment1 = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.invoiceNumber AS invoiceNumber,
'appointment' AS type
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
WHERE 1=1 {$whereAppointment1} {$groupByAppointment1Clause}";
$listAppointment2 = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.invoiceNumber AS invoiceNumber,
'package' AS type
FROM {$this->table} p
INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
{$appointments2ProvidersServicesJoin}
WHERE 1=1 {$whereAppointment2} {$groupByAppointment2Clause}";
$listEvent = "SELECT
p.id AS id,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.invoiceNumber AS invoiceNumber,
'event' AS type
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
{$eventsProvidersJoin}
WHERE 1=1 {$whereEvent} {$groupByEventClause}";
if (isset($criteria['events'], $criteria['services'])) {
return 0;
}
// Assemble a single union of the list-style subqueries matching the list endpoint
if (isset($criteria['services'])) {
$listPaymentQuery = "({$listAppointment1}) UNION ALL ({$listAppointment2})";
$params = array_merge($params, $appointmentParams1, $appointmentParams2);
} elseif (isset($criteria['events'])) {
$listPaymentQuery = "({$listEvent})";
$params = array_merge($params, $eventParams);
} elseif (isset($criteria['packages'])) {
$listPaymentQuery = "({$listAppointment2})";
$params = array_merge($params, $appointmentParams2);
} else {
$listPaymentQuery = "({$listAppointment1}) UNION ALL ({$listAppointment2}) UNION ALL ({$listEvent})";
$params = array_merge($params, $appointmentParams1, $appointmentParams2, $eventParams);
}
$bookingTypeCondition = $this->buildBookingTypeCondition($criteria, $params);
// Invoice page: count distinct invoices across the union to mirror list grouping
if ($invoice) {
try {
$statement = $this->connection->prepare(
"SELECT COUNT(DISTINCT IFNULL(invoiceNumber, id)) AS cnt FROM ({$listPaymentQuery}) payments {$bookingTypeCondition}"
);
$statement->execute($params);
$row = $statement->fetch();
return (int)($row['cnt'] ?? 0);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
// Non-invoice: simply count rows of the grouped list union (matches separateRows logic)
try {
$statement = $this->connection->prepare(
"SELECT COUNT(*) AS cnt FROM ({$listPaymentQuery}) payments {$bookingTypeCondition}"
);
$statement->execute($params);
$row = $statement->fetch();
return (int)($row['cnt'] ?? 0);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* Returns a collection of customers that have birthday on today's date and where notification is not sent
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
* @throws \Exception
*/
public function getUncompletedActionsForPayments()
{
$params = [];
$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 * FROM {$this->table}
WHERE
actionsCompleted = 0 AND
{$currentDateTime} > DATE_ADD(created, INTERVAL 300 SECOND) AND
{$pastDateTime} < created AND
entity IS NOT NULL"
);
$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 array $data
* @param boolean $invoice
*
* @return array
* @throws QueryExecutionException
*/
public function getSecondaryPaymentIds($data, $invoice)
{
$params = [];
$where = [];
$parentIdParam1 = null;
$parentIdParam2 = null;
$paymentIdParam2 = null;
foreach ($data as $index => $item) {
$paymentIdParam1 = ':paymentId1' . $index;
$params[$paymentIdParam1] = $item['paymentId'];
if ($invoice) {
if (!empty($item['parentId'])) {
$parentIdParam1 = ':parentId1' . $index;
$params[$parentIdParam1] = $item['parentId'];
$parentIdParam2 = ':parentId2' . $index;
$params[$parentIdParam2] = $item['parentId'];
}
$paymentIdParam2 = ':paymentId2' . $index;
$params[$paymentIdParam2] = $item['paymentId'];
}
$relationParam = ':' . $item['columnName'] . $index;
$params[$relationParam] = $item['columnId'];
// change in the future to simply retrieve by invoiceNumber when on invoice page since all the related payments will have the same invoiceNumber
// cannot be done immediately since invoiceNumber is NULL for existing payments
if ($invoice) {
$where[] =
"((id <> $paymentIdParam1 AND " .
$item['columnName'] . " = $relationParam) OR parentId = $paymentIdParam2" .
(!empty($item['parentId']) ? " OR id = $parentIdParam1 OR parentId = $parentIdParam2)" : ")");
} else {
$where[] = "(id <> $paymentIdParam1 AND " . $item['columnName'] . " = $relationParam)";
}
}
$where = $where ? 'WHERE ' . implode(' OR ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
p.id AS id,
p.entity AS entity
FROM {$this->table} p
{$where}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[(int)$row['id']] = $row['entity'];
}
return $result;
}
/**
* @param int $paymentId
* @param string $transactionId
*
* @throws QueryExecutionException
*/
public function updateTransactionId($paymentId, $transactionId)
{
$params = [
':transactionId' => $transactionId,
':paymentId1' => $paymentId,
':paymentId2' => $paymentId
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table} SET `transactionId` = :transactionId WHERE id = :paymentId1 OR parentId = :paymentId2"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to update data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
/**
* Sets the status of the given payments, and of no others.
*
* Payments that a single gateway charge covers are written one by one instead of by their
* parentId relation: that relation also holds payments the charge left out, which must keep
* the status they have.
*
* @param int[] $paymentIds
* @param string $status
* @param string|null $gateway Only payments made with this gateway are updated
* @param string|null $currentStatus Only payments that have this status are updated
*
* @return bool
*
* @throws QueryExecutionException
*/
public function updateStatusByIds(array $paymentIds, $status, $gateway = null, $currentStatus = null): bool
{
$paymentIds = array_values(array_unique(array_map('intval', $paymentIds)));
if (empty($paymentIds)) {
return false;
}
$params = [':status' => $status];
$idParams = [];
foreach ($paymentIds as $index => $paymentId) {
$idParam = ':paymentId' . $index;
$idParams[] = $idParam;
$params[$idParam] = $paymentId;
}
$where = 'id IN (' . implode(', ', $idParams) . ')';
if ($gateway !== null && $gateway !== '') {
$params[':gateway'] = $gateway;
$where .= ' AND gateway = :gateway';
}
if ($currentStatus !== null && $currentStatus !== '') {
$params[':currentStatus'] = $currentStatus;
$where .= ' AND status = :currentStatus';
}
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table} SET `status` = :status WHERE {$where}"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to update data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
/**
* Takes the right to run a booking's post-booking actions, for one caller only.
*
* Notifications, integrations and webhooks must fire exactly once per payment, and for a
* Stripe checkout several parties race to fire them: the browser after the Congrats page,
* the PaymentIntent webhook, and the reconciliation cron. Reading `triggeredActions` and
* then writing it leaves a window where two of them both read 0, so the flag is claimed
* in a single conditional UPDATE instead - only the caller that actually flipped it, and
* therefore only one of them, gets true back.
*
* A payment whose actions were already completed is never re-claimed.
*
* @param int $paymentId
*
* @return bool
* @throws QueryExecutionException
*/
public function claimTriggeredActions($paymentId): bool
{
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET `triggeredActions` = 1
WHERE id = :paymentId
AND COALESCE(`triggeredActions`, 0) = 0
AND COALESCE(`actionsCompleted`, 0) = 0"
);
$statement->execute([':paymentId' => (int)$paymentId]);
return $statement->rowCount() > 0;
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to claim post-booking actions in ' . __CLASS__ . '. ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* Locks a payment row for the rest of the current transaction, so that a second request
* recording the same charge waits here until the first one has committed.
*
* @param int $id
*
* @return void
* @throws QueryExecutionException
*/
public function lockById(int $id): void
{
try {
$statement = $this->connection->prepare(
"SELECT id FROM {$this->table} WHERE id = :id FOR UPDATE"
);
$statement->execute([':id' => $id]);
} catch (\Exception $e) {
throw new QueryExecutionException(
'Unable to lock payment by id in ' . __CLASS__ . '. ' . $e->getMessage(),
$e->getCode(),
$e
);
}
}
/**
* @param array $data
*
* @return bool
* @throws QueryExecutionException
*/
public function setInvoiceNumber($data)
{
$params = [
':id1' => $data['id'],
':id2' => $data['id'],
":{$data['columnName']}" => $data['columnValue']
];
$where = "WHERE id = :id1 OR parentId = :id2 OR {$data['columnName']} = :{$data['columnName']}";
if (!empty($data['parentId'])) {
$params[':parentId1'] = $params[':parentId2'] = $data['parentId'];
$where = ' OR id = :parentId1 OR parentId = :parentId2';
}
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET `invoiceNumber` = (SELECT COALESCE(MAX(invoiceNumber), 0) + 1 FROM (SELECT * FROM {$this->table}) AS p)
{$where}"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save invoice number in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
private function buildBookingTypeCondition(array $criteria, array &$params): string
{
$types = array_intersect(
[Entities::APPOINTMENT, Entities::PACKAGE, Entities::EVENT],
(array) ($criteria['bookingTypes'] ?? [])
);
$placeholders = [];
foreach ($types as $index => $type) {
$placeholders[] = ':bookingType' . $index;
$params[':bookingType' . $index] = $type;
}
if ($placeholders === []) {
return '';
}
return 'WHERE type IN (' . implode(', ', $placeholders) . ')';
}
/**
* @param array $rows
*
* @return array
*/
private function getEntitiesPaymentsResult($rows)
{
$result = [];
foreach ($rows as &$row) {
$customerInfo = $row['info'] ? json_decode($row['info'], true) : null;
if (empty($result[(int)$row['id']])) {
$result[(int)$row['id']] = [
'id' => (int)$row['id'],
'dateTime' => DateTimeService::getCustomDateTimeFromUtc($row['dateTime']),
'created' => DateTimeService::getCustomDateTimeFromUtc($row['created']),
'bookingStart' => $row['bookingStart'] ?
DateTimeService::getCustomDateTimeFromUtc($row['bookingStart']) : null,
'status' => $row['status'],
'parentId' => $row['parentId'],
'wcOrderId' => $row['wcOrderId'],
'wcOrderItemId' => $row['wcOrderItemId'],
'gateway' => $row['gateway'],
'gatewayTitle' => $row['gatewayTitle'],
'transactionId' => $row['transactionId'],
'type' => $row['type'],
'name' => $row['bookableName'],
'customerBookingId' => (int)$row['customerBookingId'] ?: null,
'packageCustomerId' => (int)$row['packageCustomerId'] ?: null,
'amount' => (float)$row['amount'],
'invoiceNumber' => $row['invoiceNumber'],
'providers' => (int)$row['providerId'] ? [
[
'id' => (int)$row['providerId'],
'fullName' => $row['providerFirstName'] . ' ' . $row['providerLastName'],
'email' => $row['providerEmail'],
'firstName' => $row['providerFirstName'],
'lastName' => $row['providerLastName'],
'picture' => $row['providerPictureThumbPath'],
]
] : [],
'location' => (int)$row['locationId'] || $row['location_address'] ?
[
'id' => (int)$row['locationId'],
'location_name' => $row['location_name'],
'location_address' => $row['location_address'],
]
: null,
'customerId' => (int)$row['customerId'],
'serviceId' => (int)$row['serviceId'] ?: null,
'appointmentId' => (int)$row['appointmentId'] ?: null,
'packageId' => (int)$row['packageId'] ?: null,
'bookedPrice' => (float)$row['bookedPrice'] ?: 0,
'bookedTax' => $row['bookedTax'] ?: null,
'bookingId' => !empty($row['bookingId']) ? (int)$row['bookingId'] : null,
'bookableName' => $row['bookableName'],
'customerFirstName' => $customerInfo ? $customerInfo['firstName'] : $row['customerFirstName'],
'customerLastName' => $customerInfo ? $customerInfo['lastName'] : $row['customerLastName'],
'info' => $row['info'],
'customerEmail' => $row['customerEmail'],
'customerStatus' => $row['customerStatus'],
'coupon' => !empty($row['coupon_id']) ? [
'id' => (int)$row['coupon_id'],
'discount' => (float)$row['coupon_discount'],
'deduction' => (float)$row['coupon_deduction'],
'code' => $row['coupon_code']
] : null,
'persons' => (int)$row['persons'],
'aggregatedPrice' => (bool)$row['aggregatedPrice'],
'extras' => [],
];
}
if ($result[(int)$row['id']] && $row['bookingExtra_id']) {
$result[(int)$row['id']]['extras'][] = [
'id' => (int)$row['bookingExtra_id'],
'extraId' => (int)$row['bookingExtra_extraId'],
'quantity' => (int)$row['bookingExtra_quantity'],
'price' => (float)$row['bookingExtra_price'],
'aggregatedPrice' => (bool)$row['bookingExtra_aggregatedPrice'],
'tax' => $row['bookingExtra_tax'] ?: null
];
}
}
return $result;
}
/**
* @param array $ids
*
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getAppointmentsPaymentsByIds($ids)
{
$params = [];
$where = [];
if (!empty($ids)) {
foreach ($ids as $index => $value) {
$param = ':sId' . $index;
$params[$param] = $value;
}
$where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
$customerBookingsExtrasTable = CustomerBookingsToExtrasTable::getTableName();
$couponsTable = CouponsTable::getTableName();
$locationsTable = LocationsTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT
p.id AS id,
p.customerBookingId AS customerBookingId,
NULL AS packageCustomerId,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.wcOrderId AS wcOrderId,
p.wcOrderItemId AS wcOrderItemId,
p.gateway AS gateway,
p.gatewayTitle AS gatewayTitle,
p.transactionId AS transactionId,
p.parentId AS parentId,
p.entity AS type,
NULL AS packageId,
cb.id AS bookingId,
cb.price AS bookedPrice,
cb.tax AS bookedTax,
a.providerId AS providerId,
cb.customerId AS customerId,
cb.persons AS persons,
cb.aggregatedPrice AS aggregatedPrice,
cb.info AS info,
cbe.id AS bookingExtra_id,
cbe.extraId AS bookingExtra_extraId,
cbe.quantity AS bookingExtra_quantity,
cbe.price AS bookingExtra_price,
cbe.aggregatedPrice AS bookingExtra_aggregatedPrice,
cbe.tax AS bookingExtra_tax,
c.id AS coupon_id,
c.discount AS coupon_discount,
c.deduction AS coupon_deduction,
c.code AS coupon_code,
a.serviceId AS serviceId,
a.id AS appointmentId,
a.bookingStart AS bookingStart,
s.name AS bookableName,
cu.firstName AS customerFirstName,
cu.lastName AS customerLastName,
cu.email AS customerEmail,
cu.status AS customerStatus,
pu.firstName AS providerFirstName,
pu.lastName AS providerLastName,
pu.email AS providerEmail,
pu.pictureThumbPath AS providerPictureThumbPath,
l.id AS locationId,
l.name AS location_name,
l.address AS location_address
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
LEFT JOIN {$customerBookingsExtrasTable} cbe ON cbe.customerBookingId = cb.id
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
INNER JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
INNER JOIN {$this->servicesTable} s ON s.id = a.serviceId
INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
INNER JOIN {$this->usersTable} pu ON pu.id = a.providerId
LEFT JOIN {$locationsTable} l ON l.id = a.locationId
WHERE 1=1 {$where}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $this->getEntitiesPaymentsResult($rows);
}
/**
* @param array $ids
*
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getEventsPaymentsByIds($ids)
{
$params = [];
$where = [];
if (!empty($ids)) {
foreach ($ids as $index => $value) {
$param = ':eId' . $index;
$params[$param] = $value;
}
$where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
$couponsTable = CouponsTable::getTableName();
$locationsTable = LocationsTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT
p.id AS id,
p.customerBookingId AS customerBookingId,
NULL AS packageCustomerId,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.wcOrderId AS wcOrderId,
p.wcOrderItemId AS wcOrderItemId,
p.gateway AS gateway,
p.gatewayTitle AS gatewayTitle,
p.transactionId AS transactionId,
p.parentId AS parentId,
p.entity AS type,
NULL AS packageId,
cb.id AS bookingId,
cb.price AS bookedPrice,
cb.tax AS bookedTax,
NULL AS providerId,
cb.customerId AS customerId,
cb.persons AS persons,
cb.aggregatedPrice AS aggregatedPrice,
cb.info AS info,
NULL AS bookingExtra_id,
NULL AS bookingExtra_extraId,
NULL AS bookingExtra_quantity,
NULL AS bookingExtra_price,
NULL AS bookingExtra_aggregatedPrice,
NULL AS bookingExtra_tax,
c.id AS coupon_id,
c.discount AS coupon_discount,
c.deduction AS coupon_deduction,
c.code AS coupon_code,
NULL AS serviceId,
NULL AS appointmentId,
NULL AS bookingStart,
NULL AS bookableName,
cu.firstName AS customerFirstName,
cu.lastName AS customerLastName,
cu.email AS customerEmail,
cu.status AS customerStatus,
NULL AS providerFirstName,
NULL AS providerLastName,
NULL AS providerEmail,
l.id AS locationId,
l.name AS location_name,
(CASE WHEN e.customlocation IS NOT NULL THEN e.customlocation ELSE l.address END) AS location_address
FROM {$this->table} p
INNER JOIN {$this->bookingsTable} cb ON cb.id = p.customerBookingId
LEFT JOIN {$couponsTable} c ON c.id = cb.couponId
INNER JOIN {$this->usersTable} cu ON cu.id = cb.customerId
INNER JOIN {$this->customerBookingsToEventsPeriodsTable} cbe ON cbe.customerBookingId = cb.id
INNER JOIN {$this->eventsPeriodsTable} ep ON ep.id = cbe.eventPeriodId
INNER JOIN {$this->eventsTable} e ON e.id = ep.eventId
LEFT JOIN {$this->eventsProvidersTable} epu ON epu.eventId = ep.eventId
LEFT JOIN {$locationsTable} l ON l.id = e.locationId
WHERE 1=1 {$where}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $this->getEntitiesPaymentsResult($rows);
}
/**
* @param array $ids
*
* @return array
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getPackagesPaymentsByIds($ids)
{
$params = [];
$where = [];
if (!empty($ids)) {
foreach ($ids as $index => $value) {
$param = ':pId' . $index;
$params[$param] = $value;
}
$where[] = 'p.id IN (' . implode(', ', array_keys($params)) . ')';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
$couponsTable = CouponsTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT
p.id AS id,
NULL AS customerBookingId,
p.packageCustomerId AS packageCustomerId,
p.amount AS amount,
p.invoiceNumber AS invoiceNumber,
p.dateTime AS dateTime,
p.created AS created,
p.status AS status,
p.wcOrderId AS wcOrderId,
p.wcOrderItemId AS wcOrderItemId,
p.gateway AS gateway,
p.gatewayTitle AS gatewayTitle,
p.transactionId AS transactionId,
p.parentId AS parentId,
p.entity AS type,
pc.packageId AS packageId,
pc.id AS bookingId,
pc.price AS bookedPrice,
pc.tax AS bookedTax,
NULL AS providerId,
pc.customerId AS customerId,
NULL AS persons,
NULL AS aggregatedPrice,
cb.info AS info,
NULL AS bookingExtra_id,
NULL AS bookingExtra_extraId,
NULL AS bookingExtra_quantity,
NULL AS bookingExtra_price,
NULL AS bookingExtra_aggregatedPrice,
NULL AS bookingExtra_tax,
c.id AS coupon_id,
c.discount AS coupon_discount,
c.deduction AS coupon_deduction,
c.code AS coupon_code,
NULL AS serviceId,
NULL AS appointmentId,
NULL AS bookingStart,
pa.name AS bookableName,
cu.firstName AS customerFirstName,
cu.lastName AS customerLastName,
cu.email AS customerEmail,
cu.status AS customerStatus,
'' AS providerFirstName,
'' AS providerLastName,
'' AS providerEmail,
'' AS locationId,
'' AS location_name,
'' AS location_address
FROM {$this->table} p
INNER JOIN {$this->packagesCustomersTable} pc ON p.packageCustomerId = pc.id
INNER JOIN {$this->usersTable} cu ON cu.id = pc.customerId
LEFT JOIN {$couponsTable} c ON c.id = pc.couponId
INNER JOIN {$this->packagesTable} pa ON pa.id = pc.packageId
INNER JOIN {$this->packagesCustomersServiceTable} pcs ON pc.id = pcs.packageCustomerId
LEFT JOIN {$this->bookingsTable} cb ON cb.packageCustomerServiceId = pcs.id
LEFT JOIN {$this->appointmentsTable} a ON a.id = cb.appointmentId
WHERE 1=1 {$where} ORDER BY p.id ASC"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $this->getEntitiesPaymentsResult($rows);
}
}