<?php
/**
* @copyright © Melograno Ventures. All rights reserved.
* @licence See LICENCE.md for license details.
*/
namespace AmeliaBooking\Infrastructure\Repository\Bookable\Service;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Services\DateTime\DateTimeService;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Domain\Entity\Bookable\Service\Service;
use AmeliaBooking\Domain\Factory\Bookable\Service\ServiceFactory;
use AmeliaBooking\Infrastructure\Licence;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Domain\Repository\Bookable\Service\ServiceRepositoryInterface;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\AppointmentsTable;
/**
* Class ServiceRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Service
*/
class ServiceRepository extends AbstractRepository implements ServiceRepositoryInterface
{
public const FACTORY = ServiceFactory::class;
/** @var string */
protected $providerServicesTable;
/** @var string */
protected $extrasTable;
/** @var string */
protected $serviceViewsTable;
/** @var string */
protected $galleriesTable;
/**
* @param Connection $connection
* @param string $table
* @param string $providerServicesTable
* @param string $extrasTable
* @param string $serviceViewsTable
* @param string $galleriesTable
*/
public function __construct(
Connection $connection,
$table,
$providerServicesTable,
$extrasTable,
$serviceViewsTable,
$galleriesTable
) {
parent::__construct($connection, $table);
$this->providerServicesTable = $providerServicesTable;
$this->extrasTable = $extrasTable;
$this->serviceViewsTable = $serviceViewsTable;
$this->galleriesTable = $galleriesTable;
}
/**
* @return Collection
* @throws QueryExecutionException
*/
public function getAllArrayIndexedById($ids = [])
{
$where = '';
$params = [];
if (!empty($ids)) {
$query = [];
foreach ((array)$ids as $index => $value) {
$param = ':id' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where = 'WHERE s.id IN (' . implode(', ', $query) . ')';
}
try {
$statement = $this->connection->prepare("SELECT
s.id AS service_id,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
s.price AS service_price,
s.customPricing AS service_customPricing,
s.limitPerCustomer AS service_limitPerCustomer,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.maxCapacity AS service_maxCapacity,
s.maxExtraPeople AS service_maxExtraPeople,
s.minCapacity AS service_minCapacity,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.bringingAnyone as service_bringingAnyone,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.position AS service_position,
s.show AS service_show,
s.aggregatedPrice AS service_aggregatedPrice,
s.settings AS service_settings,
s.recurringCycle AS service_recurringCycle,
s.recurringSub AS service_recurringSub,
s.recurringPayment AS service_recurringPayment,
s.translations AS service_translations,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS service_fullPayment,
s.mandatoryExtra AS service_mandatoryExtra,
s.minSelectedExtras AS service_minSelectedExtras,
e.id AS extra_id,
e.name AS extra_name,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.position AS extra_position,
e.aggregatedPrice AS extra_aggregatedPrice,
e.description AS extra_description,
e.translations AS extra_translations,
g.id AS gallery_id,
g.pictureFullPath AS gallery_picture_full,
g.pictureThumbPath AS gallery_picture_thumb,
g.position AS gallery_position
FROM {$this->table} s
LEFT JOIN {$this->extrasTable} e ON e.serviceId = s.id
LEFT JOIN {$this->galleriesTable} g ON g.entityId = s.id AND g.entityType = 'service'
{$where}
ORDER BY s.position, s.name ASC, e.position ASC, g.position ASC");
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $services */
$services = call_user_func([static::FACTORY, 'createCollection'], $rows);
/** @var Service $service */
foreach ($services->getItems() as $service) {
if ($service->getSettings() && json_decode($service->getSettings()->getValue(), true) === null) {
$service->setSettings(null);
}
}
return $services;
}
/**
* @param Service $entity
*
* @return int
* @throws QueryExecutionException
*/
public function add($entity)
{
$data = $entity->toArray();
$params = [
':name' => $data['name'],
':description' => $data['description'],
':color' => $data['color'],
':price' => $data['price'],
':status' => $data['status'],
':categoryId' => $data['categoryId'],
':minCapacity' => $data['minCapacity'],
':maxCapacity' => $data['maxCapacity'],
':maxExtraPeople' => $data['maxExtraPeople'],
':duration' => $data['duration'],
':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0,
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':position' => $data['position'],
':mandatoryExtra' => $data['mandatoryExtra'] ? 1 : 0,
':minSelectedExtras' => $data['minSelectedExtras'],
];
$additionalData = Licence\DataModifier::getServiceRepositoryData($data);
$params = array_merge($params, $additionalData['values']);
try {
$statement = $this->connection->prepare(
"INSERT INTO
{$this->table}
(
{$additionalData['columns']}
`name`,
`description`,
`color`,
`price`,
`status`,
`categoryId`,
`minCapacity`,
`maxCapacity`,
`maxExtraPeople`,
`duration`,
`bringingAnyone`,
`aggregatedPrice`,
`pictureFullPath`,
`pictureThumbPath`,
`position`,
`mandatoryExtra`,
`minSelectedExtras`
) VALUES (
{$additionalData['placeholders']}
:name,
:description,
:color,
:price,
:status,
:categoryId,
:minCapacity,
:maxCapacity,
:maxExtraPeople,
:duration,
:bringingAnyone,
:aggregatedPrice,
:pictureFullPath,
:pictureThumbPath,
:position,
:mandatoryExtra,
:minSelectedExtras
)"
);
$statement->execute($params);
return $this->connection->lastInsertId();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param int $id
* @param Service $entity
*
* @return mixed
* @throws QueryExecutionException
*/
public function update($id, $entity)
{
$data = $entity->toArray();
$params = [
':name' => $data['name'],
':description' => $data['description'],
':color' => $data['color'],
':price' => $data['price'],
':status' => $data['status'],
':categoryId' => $data['categoryId'],
':maxExtraPeople' => $data['maxExtraPeople'],
':duration' => $data['duration'],
':bringingAnyone' => $data['bringingAnyone'] ? 1 : 0,
':aggregatedPrice' => $data['aggregatedPrice'] ? 1 : 0,
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':position' => $data['position'],
':mandatoryExtra' => $data['mandatoryExtra'] ? 1 : 0,
':minSelectedExtras' => $data['minSelectedExtras'],
':id' => $id
];
$additionalData = Licence\DataModifier::getServiceRepositoryData($data);
$params = array_merge($params, $additionalData['values']);
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
{$additionalData['columnsPlaceholders']}
`name` = :name,
`description` = :description,
`color` = :color,
`price` = :price,
`status` = :status,
`categoryId` = :categoryId,
`maxExtraPeople` = :maxExtraPeople,
`duration` = :duration,
`bringingAnyone` = :bringingAnyone,
`aggregatedPrice` = :aggregatedPrice,
`pictureFullPath` = :pictureFullPath,
`pictureThumbPath` = :pictureThumbPath,
`position` = :position,
`mandatoryExtra` = :mandatoryExtra,
`minSelectedExtras` = :minSelectedExtras
WHERE
id = :id"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param array $criteria
* @param int $itemsPerPage
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getFiltered($criteria, $itemsPerPage = null)
{
$params = [];
$where = [];
$orderColumn = 's.position, s.id';
$orderDirection = 'ASC';
if (!empty($criteria['sort'])) {
switch ($criteria['sort']) {
case ('nameAsc'):
$orderColumn = 's.name';
$orderDirection = 'ASC';
break;
case ('nameDesc'):
$orderColumn = 's.name';
$orderDirection = 'DESC';
break;
case ('priceAsc'):
$orderColumn = 's.price';
$orderDirection = 'ASC';
break;
case ('priceDesc'):
$orderColumn = 's.price';
$orderDirection = 'DESC';
break;
case ('durationAsc'):
$orderColumn = 's.duration';
$orderDirection = 'ASC';
break;
case ('durationDesc'):
$orderColumn = 's.duration';
$orderDirection = 'DESC';
break;
case ('idAsc'):
$orderColumn = 's.id';
$orderDirection = 'ASC';
break;
case ('idDesc'):
$orderColumn = 's.id';
$orderDirection = 'DESC';
break;
case ('custom'):
$orderColumn = 's.position, s.id';
$orderDirection = 'ASC';
break;
}
}
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
s.name LIKE {$param}
OR s.description LIKE {$param}
OR s.id LIKE {$param}
)";
$termIndex++;
}
}
if (!empty($criteria['categoryId'])) {
$params[':categoryId'] = $criteria['categoryId'];
$where[] = 's.categoryId = :categoryId';
}
$providersJoin = $this->getProvidersJoin($criteria, $params, $where);
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
$limit = $this->getLimit(
!empty($criteria['page']) ? (int)$criteria['page'] : 0,
(int)$itemsPerPage
);
try {
$statement = $this->connection->prepare(
"SELECT DISTINCT s.*
FROM {$this->table} s
{$providersJoin}
{$where}
{$order}
{$limit}"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by ids in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$items = new Collection();
foreach ($rows as $row) {
$items->addItem(call_user_func([static::FACTORY, 'create'], $row), $row['id']);
}
return $items;
}
/**
* @param array $criteria
*
* @return mixed
* @throws QueryExecutionException
*/
public function getCount($criteria)
{
$params = [];
$where = [];
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
s.name LIKE {$param}
OR s.description LIKE {$param}
OR s.id LIKE {$param}
)";
$termIndex++;
}
}
if (!empty($criteria['categoryId'])) {
$params[':categoryId'] = $criteria['categoryId'];
$where[] = 's.categoryId = :categoryId';
}
$providersJoin = $this->getProvidersJoin($criteria, $params, $where);
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT COUNT(DISTINCT s.id) as count
FROM {$this->table} s
{$providersJoin}
{$where}"
);
$statement->execute($params);
$row = $statement->fetch()['count'];
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $row;
}
/**
* @param int $serviceId
* @param int $userId
*
* @return Collection
* @throws QueryExecutionException
*/
public function getProviderServicesWithExtras($serviceId, $userId)
{
try {
$statement = $this->connection->prepare(
"SELECT
s.id AS service_id,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
ps.price AS service_price,
s.status AS service_status,
s.categoryId AS service_categoryId,
ps.minCapacity AS service_minCapacity,
ps.maxCapacity AS service_maxCapacity,
ps.customPricing AS service_customPricing,
s.limitPerCustomer AS service_limitPerCustomer,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.bringingAnyone as service_bringingAnyone,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.aggregatedPrice AS service_aggregatedPrice,
s.settings AS service_settings,
s.recurringPayment AS service_recurringPayment,
s.translations AS service_translations,
s.show AS service_show,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS service_fullPayment,
e.id AS extra_id,
e.name AS extra_name,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.aggregatedPrice AS extra_aggregatedPrice,
e.position AS extra_position,
e.translations AS extra_translations
FROM {$this->table} s
INNER JOIN {$this->providerServicesTable} ps ON s.id = ps.serviceId
LEFT JOIN {$this->extrasTable} e ON e.serviceId = s.id
WHERE ps.userId = :userId AND ps.serviceId = :serviceId"
);
$statement->bindParam(':userId', $userId);
$statement->bindParam(':serviceId', $serviceId);
$statement->execute();
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by ids in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
/**
* @param $criteria
*
* @return Collection
* @throws QueryExecutionException
*/
public function getByCriteria($criteria)
{
$params = [];
$where = [];
$order = 'ORDER BY s.name ASC';
if (isset($criteria['sort'])) {
if ($criteria['sort'] === '') {
$order = 'ORDER BY s.position';
} else {
$orderColumn = strpos($criteria['sort'], 'name') !== false ? 's.name' : 's.price';
$orderDirection = $criteria['sort'][0] === '-' ? 'DESC' : 'ASC';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
}
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
s.name LIKE {$param}
)";
$termIndex++;
}
}
if (!empty($criteria['services'])) {
$queryServices = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$queryServices[] = $param;
$params[$param] = $value;
}
$where[] = 's.id IN (' . implode(', ', $queryServices) . ')';
}
if (!empty($criteria['categories'])) {
$queryCategories = [];
foreach ((array)$criteria['categories'] as $index => $value) {
$param = ':category' . $index;
$queryCategories[] = $param;
$params[$param] = $value;
}
$where[] = 's.categoryId IN (' . implode(', ', $queryCategories) . ')';
}
if (!empty($criteria['providers'])) {
$queryProviders = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param = ':provider' . $index;
$queryProviders[] = $param;
$params[$param] = $value;
}
$where[] = 'ps.userId IN (' . implode(', ', $queryProviders) . ')';
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = 's.status = :status';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
s.id AS service_id,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
s.price AS service_price,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.maxCapacity AS service_maxCapacity,
s.maxExtraPeople AS service_maxExtraPeople,
s.minCapacity AS service_minCapacity,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.bringingAnyone AS service_bringingAnyone,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.show AS service_show,
s.position AS service_position,
s.aggregatedPrice AS service_aggregatedPrice,
s.settings AS service_settings,
s.translations AS service_translations,
s.recurringCycle AS service_recurringCycle,
s.recurringSub AS service_recurringSub,
s.recurringPayment AS service_recurringPayment,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS service_fullPayment,
s.mandatoryExtra AS service_mandatoryExtra,
s.minSelectedExtras AS service_minSelectedExtras,
s.customPricing AS service_customPricing,
s.limitPerCustomer AS service_limitPerCustomer,
e.id AS extra_id,
e.name AS extra_name,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.position AS extra_position,
e.aggregatedPrice AS extra_aggregatedPrice,
e.description AS extra_description,
e.translations AS extra_translations,
g.id AS gallery_id,
g.pictureFullPath AS gallery_picture_full,
g.pictureThumbPath AS gallery_picture_thumb,
g.position AS gallery_position
FROM {$this->table} s
LEFT JOIN {$this->extrasTable} e ON e.serviceId = s.id
LEFT JOIN {$this->providerServicesTable} ps ON ps.serviceId = s.id
LEFT JOIN {$this->galleriesTable} g ON g.entityId = s.id AND g.entityType = 'service'
WHERE 1 = 1 $where
$order"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
/**
* @param int $serviceId
*
* @return Service
* @throws QueryExecutionException
*/
public function getByIdWithExtras($serviceId)
{
try {
$statement = $this->connection->prepare(
"SELECT
s.id AS service_id,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
s.price AS service_price,
s.customPricing AS service_customPricing,
s.limitPerCustomer AS service_limitPerCustomer,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.maxCapacity AS service_maxCapacity,
s.maxExtraPeople AS service_maxExtraPeople,
s.minCapacity AS service_minCapacity,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.bringingAnyone AS service_bringingAnyone,
s.priority AS service_priority,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.aggregatedPrice AS service_aggregatedPrice,
s.settings AS service_settings,
s.translations AS service_translations,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS servie_fullPayment,
e.id AS extra_id,
e.name AS extra_name,
e.description AS extra_description,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.aggregatedPrice AS extra_aggregatedPrice,
e.position AS extra_position,
e.translations AS extra_translations
FROM {$this->table} s
LEFT JOIN {$this->extrasTable} e ON e.serviceId = s.id
WHERE s.id = :serviceId
ORDER BY s.id, e.id"
);
$statement->bindParam(':serviceId', $serviceId);
$statement->execute();
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows)->getItem($serviceId);
}
/**
* @param array $criteria
*
* @return Collection
* @throws QueryExecutionException
*/
public function getWithExtras($criteria)
{
$order = '';
$where = [];
if (isset($criteria['sort'])) {
if ($criteria['sort'] === '') {
$order = 'ORDER BY s.position';
} else {
$orderColumn = strpos($criteria['sort'], 'name') !== false ? 's.name' : 's.price';
$orderDirection = $criteria['sort'][0] === '-' ? 'DESC' : 'ASC';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->query(
"SELECT
s.id AS service_id,
s.name AS service_name,
s.description AS service_description,
s.color AS service_color,
s.price AS service_price,
s.customPricing AS service_customPricing,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.maxCapacity AS service_maxCapacity,
s.maxExtraPeople AS service_maxExtraPeople,
s.minCapacity AS service_minCapacity,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.bringingAnyone as service_bringingAnyone,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.position AS service_position,
s.show AS service_show,
s.aggregatedPrice AS service_aggregatedPrice,
s.settings AS service_settings,
s.recurringCycle AS service_recurringCycle,
s.recurringSub AS service_recurringSub,
s.recurringPayment AS service_recurringPayment,
s.translations AS service_translations,
s.deposit AS service_deposit,
s.depositPayment AS service_depositPayment,
s.depositPerPerson AS service_depositPerPerson,
s.fullPayment AS service_fullPayment,
s.mandatoryExtra AS service_mandatoryExtra,
s.minSelectedExtras AS service_minSelectedExtras,
e.id AS extra_id,
e.name AS extra_name,
e.price AS extra_price,
e.maxQuantity AS extra_maxQuantity,
e.duration AS extra_duration,
e.position AS extra_position,
e.aggregatedPrice AS extra_aggregatedPrice,
e.description AS extra_description,
e.translations AS extra_translations
FROM {$this->table} s
LEFT JOIN {$this->extrasTable} e ON e.serviceId = s.id
{$where}
{$order}"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
/** @var Collection $services */
$services = call_user_func([static::FACTORY, 'createCollection'], $rows);
/** @var Service $service */
foreach ($services->getItems() as $service) {
if ($service->getSettings() && json_decode($service->getSettings()->getValue(), true) === null) {
$service->setSettings(null);
}
}
return $services;
}
/**
* Return an array of services with the number of appointments for the given date period.
* Keys of the array are Services IDs.
*
* @param $criteria
*
* @return array
* @throws QueryExecutionException
* @throws \AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException
*/
public function getAllNumberOfAppointments($criteria)
{
$appointmentTable = AppointmentsTable::getTableName();
$params = [];
$where = [];
if ($criteria['dates']) {
$where[] = "(a.bookingStart BETWEEN :bookingFrom AND :bookingTo)";
$params[':bookingFrom'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][0]);
$params[':bookingTo'] = DateTimeService::getCustomDateTimeInUtc($criteria['dates'][1]);
}
if (isset($criteria['status'])) {
$where[] = 's.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
s.id,
s.name,
COUNT(a.providerId) AS appointments
FROM {$this->table} s
INNER JOIN {$appointmentTable} a ON s.id = a.serviceId
$where
GROUP BY serviceId"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['id']] = $row;
}
return $result;
}
/**
* Return an array of services with the number of views for the given date period.
* Keys of the array are Services IDs.
*
* @param $criteria
*
* @return array
* @throws QueryExecutionException
*/
public function getAllNumberOfViews($criteria)
{
$params = [];
$where = [];
if ($criteria['dates']) {
$where[] = "(sv.date BETWEEN :bookingFrom AND :bookingTo)";
$params[':bookingFrom'] = explode(' ', $criteria['dates'][0])[0];
$params[':bookingTo'] = explode(' ', $criteria['dates'][1])[0];
}
if (isset($criteria['status'])) {
$where[] = 's.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
s.id,
s.name,
SUM(sv.views) AS views
FROM {$this->table} s
INNER JOIN {$this->serviceViewsTable} sv ON sv.serviceId = s.id
$where
GROUP BY s.id"
);
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
$result = [];
foreach ($rows as $row) {
$result[$row['id']] = $row;
}
return $result;
}
/**
* @param $serviceId
*
* @return bool
* @throws QueryExecutionException
*/
public function addViewStats($serviceId)
{
$date = DateTimeService::getNowDate();
$params = [
':serviceId' => $serviceId,
':date' => $date,
':views' => 1
];
try {
// Check if there is already data for this provider for this date
$statement = $this->connection->prepare(
"SELECT COUNT(*) AS count
FROM {$this->serviceViewsTable} AS pv
WHERE pv.serviceId = :serviceId
AND pv.date = :date"
);
$statement->bindParam(':serviceId', $serviceId);
$statement->bindParam(':date', $date);
$statement->execute();
$count = $statement->fetch()['count'];
if (!$count) {
$statement = $this->connection->prepare(
"INSERT INTO {$this->serviceViewsTable}
(`serviceId`, `date`, `views`)
VALUES
(:serviceId, :date, :views)"
);
} else {
$statement = $this->connection->prepare(
"UPDATE {$this->serviceViewsTable} pv SET pv.views = pv.views + :views
WHERE pv.serviceId = :serviceId
AND pv.date = :date"
);
}
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to add data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return true;
}
/**
* @param int $serviceId
*
* @return mixed
* @throws QueryExecutionException
*/
public function deleteViewStats($serviceId)
{
$params = [
':serviceId' => $serviceId,
];
try {
$statement = $this->connection->prepare(
"DELETE FROM {$this->serviceViewsTable} WHERE serviceId = :serviceId"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to delete data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param array $criteria
* @param array $params
* @param array $where
*
* @return string
*/
private function getProvidersJoin($criteria, &$params, &$where)
{
if (empty($criteria['providers'])) {
return '';
}
$queryProviders = [];
foreach ((array)$criteria['providers'] as $index => $value) {
$param = ':provider' . $index;
$queryProviders[] = $param;
$params[$param] = $value;
}
$where[] = 'ps.userId IN (' . implode(', ', $queryProviders) . ')';
return "INNER JOIN {$this->providerServicesTable} ps ON ps.serviceId = s.id";
}
}