<?php
namespace AmeliaBooking\Infrastructure\Repository\Location;
use AmeliaBooking\Domain\Collection\Collection;
use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException;
use AmeliaBooking\Domain\Entity\Location\Location;
use AmeliaBooking\Domain\Factory\Bookable\Service\ServiceFactory;
use AmeliaBooking\Domain\Repository\Location\LocationRepositoryInterface;
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\Domain\Factory\Location\LocationFactory;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\CategoriesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\ServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\AppointmentsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsPeriodsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Booking\EventsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersLocationTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersPeriodLocationTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersPeriodTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersServiceTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;
/**
* Class LocationRepositoryInterface
*
* @package AmeliaBooking\Infrastructure\Repository
*/
class LocationRepository extends AbstractRepository implements LocationRepositoryInterface
{
public const FACTORY = LocationFactory::class;
public const SERVICE_FACTORY = ServiceFactory::class;
/** @var string */
protected $providerServicesTable;
/** @var string */
protected $providerLocationTable;
/** @var string */
protected $servicesTable;
/** @var string */
protected $locationViewsTable;
/**
* @param Connection $connection
* @param string $table
* @param string $providerLocationTable
* @param string $providerServicesTable
* @param string $servicesTable
* @param $locationViewsTable
*/
public function __construct(
Connection $connection,
$table,
$providerLocationTable,
$providerServicesTable,
$servicesTable,
$locationViewsTable
) {
parent::__construct($connection, $table);
$this->providerServicesTable = $providerServicesTable;
$this->providerLocationTable = $providerLocationTable;
$this->servicesTable = $servicesTable;
$this->locationViewsTable = $locationViewsTable;
}
/**
* @param Location $location
*
* @return int
* @throws QueryExecutionException
*/
public function add($location)
{
$data = $location->toArray();
$params = [
':status' => $data['status'],
':name' => $data['name'],
':description' => $data['description'],
':address' => $data['address'],
':phone' => $data['phone'],
':latitude' => $data['latitude'],
':longitude' => $data['longitude'],
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':pin' => $data['pin'],
':translations' => $data['translations'],
':countryPhoneIso' => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null
];
try {
$statement = $this->connection->prepare(
"INSERT INTO {$this->table}
(
`status`,
`name`,
`description`,
`address`,
`phone`,
`latitude`,
`longitude`,
`pictureFullPath`,
`pictureThumbPath`,
`pin`,
`translations`,
`countryPhoneIso`
)
VALUES (
:status,
:name,
:description,
:address,
:phone,
:latitude,
:longitude,
:pictureFullPath,
:pictureThumbPath,
:pin,
:translations,
:countryPhoneIso
)"
);
$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 Location $location
*
* @return bool
* @throws QueryExecutionException
*/
public function update($id, $location)
{
$data = $location->toArray();
$params = [
':status' => $data['status'],
':name' => $data['name'],
':description' => $data['description'],
':address' => $data['address'],
':phone' => $data['phone'],
':latitude' => $data['latitude'],
':longitude' => $data['longitude'],
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':pin' => $data['pin'],
':translations' => $data['translations'],
':id' => $id,
':countryPhoneIso' => isset($data['countryPhoneIso']) ? $data['countryPhoneIso'] : null
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET `status` = :status, `name` = :name, `description` = :description, `address` = :address,
`phone` = :phone, `latitude` = :latitude, `longitude` = :longitude,
`pictureFullPath` = :pictureFullPath, `pictureThumbPath` = :pictureThumbPath,
`pin` = :pin, `translations` = :translations, `countryPhoneIso` = :countryPhoneIso
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);
}
}
/**
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getAllOrderedByName()
{
try {
$statement = $this->connection->query(
"SELECT * FROM {$this->table} ORDER BY name"
);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __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
* @param int $itemsPerPage
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getFiltered($criteria, $itemsPerPage)
{
$params = [];
$order = '';
if (!empty($criteria['sort'])) {
$orderColumn = $criteria['sort']['field'];
$orderDirection = $criteria['sort']['order'];
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
$search = '';
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
$where = [];
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
l.name LIKE {$param}
OR l.address LIKE {$param}
OR l.id LIKE {$param}
)";
$termIndex++;
}
$search = ' AND (' . implode(' AND ', $where) . ')';
}
$services = '';
if (!empty($criteria['services'])) {
foreach ((array)$criteria['services'] as $index => $value) {
++$index;
$services .= ':service' . $index . ', ';
$params[':service' . $index] = $value;
}
$services = ' AND s.id IN (' . rtrim($services, ', ') . ')';
}
$status = '';
if (isset($criteria['status'])) {
$status = ' AND l.status = :status';
$params[':status'] = $criteria['status'];
}
$limit = $this->getLimit(
!empty($criteria['page']) ? (int)$criteria['page'] : 0,
(int)$itemsPerPage
);
try {
$statement = $this->connection->prepare(
"SELECT
l.id,
l.status,
l.name,
l.description,
l.address,
l.phone,
l.latitude,
l.longitude,
l.pictureFullPath,
l.pictureThumbPath,
l.pin,
l.translations,
l.countryPhoneIso
FROM {$this->table} l
LEFT JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
LEFT JOIN {$this->providerServicesTable} ps ON ps.userId = pl.userId
LEFT JOIN {$this->servicesTable} s ON s.id = ps.serviceId
WHERE 1 = 1 $search $services $status
GROUP BY l.id
{$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);
}
$items = [];
foreach ($rows as $row) {
$items[] = call_user_func([static::FACTORY, 'create'], $row);
}
return new Collection($items);
}
/**
* @param $criteria
*
* @return array
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getCount($criteria)
{
$providerLocationTable = ProvidersLocationTable::getTableName();
$providerServicesTable = ProvidersServiceTable::getTableName();
$servicesTable = ServicesTable::getTableName();
$params = [];
$search = '';
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
$where = [];
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
l.name LIKE {$param}
OR l.address LIKE {$param}
OR l.id LIKE {$param}
)";
$termIndex++;
}
$search = ' AND (' . implode(' AND ', $where) . ')';
}
$services = '';
if (!empty($criteria['services'])) {
foreach ((array)$criteria['services'] as $index => $value) {
++$index;
$services .= ':service' . $index . ', ';
$params[':service' . $index] = $value;
}
$services = ' AND s.id IN (' . rtrim($services, ', ') . ')';
}
try {
$statement = $this->connection->prepare(
"SELECT COUNT(*) as count
FROM (
SELECT l.id
FROM {$this->table} l
LEFT JOIN {$providerLocationTable} pl ON pl.locationId = l.id
LEFT JOIN {$providerServicesTable} ps ON ps.userId = pl.userId
LEFT JOIN {$servicesTable} s ON s.id = ps.serviceId
WHERE l.status IN (:visibleStatus, :hiddenStatus) $search $services
GROUP BY l.id
) as t"
);
$params[':visibleStatus'] = Status::VISIBLE;
$params[':hiddenStatus'] = Status::HIDDEN;
$statement->execute($params);
$rows = $statement->fetch()['count'];
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return $rows;
}
/**
* @param $id
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getServicesById($id)
{
$params = [
':id' => $id
];
try {
$statement = $this->connection->prepare(
"
SELECT s.*
FROM {$this->table} l
INNER JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
INNER JOIN {$this->providerServicesTable} ps ON ps.userId = pl.userId
INNER JOIN {$this->servicesTable} s ON s.id = ps.serviceId
WHERE l.id = :id
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);
}
$items = [];
foreach ($rows as $row) {
$items[] = call_user_func([static::SERVICE_FACTORY, 'create'], $row);
}
return new Collection($items);
}
/**
* Return an array of locations with the number of appointments for the given date period.
* Keys of the array are Locations IDs.
*
* @param $criteria
*
* @return array
* @throws QueryExecutionException
* @throws \AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException
*/
public function getAllNumberOfAppointments($criteria)
{
$userTable = UsersTable::getTableName();
$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[] = 'l.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
l.id,
l.name,
COUNT(l.id) AS appointments
FROM {$this->table} l
INNER JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
INNER JOIN {$userTable} u ON u.id = pl.userId
INNER JOIN {$appointmentTable} a ON u.id = a.providerId
$where
GROUP BY l.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;
}
/**
* Return an array of locations with the number of views for the given date period.
* Keys of the array are Locations IDs.
*
* @param $criteria
*
* @return array
* @throws QueryExecutionException
*/
public function getAllNumberOfViews($criteria)
{
$params = [];
$where = [];
if ($criteria['dates']) {
$where[] = "(lv.date BETWEEN :bookingFrom AND :bookingTo)";
$params[':bookingFrom'] = explode(' ', $criteria['dates'][0])[0];
$params[':bookingTo'] = explode(' ', $criteria['dates'][1])[0];
}
if (isset($criteria['status'])) {
$where[] = 'l.status = :status';
$params[':status'] = $criteria['status'];
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
try {
$statement = $this->connection->prepare(
"SELECT
l.id,
l.name,
SUM(lv.views) AS views
FROM {$this->table} l
INNER JOIN {$this->locationViewsTable} lv ON lv.locationId = l.id
$where
GROUP BY l.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 $locationId
*
* @return boolean
* @throws QueryExecutionException
*/
public function addViewStats($locationId)
{
$date = DateTimeService::getNowDate();
$params = [
':locationId' => $locationId,
':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->locationViewsTable} AS pv
WHERE pv.locationId = :locationId
AND pv.date = :date"
);
$statement->bindParam(':locationId', $locationId);
$statement->bindParam(':date', $date);
$statement->execute();
$count = $statement->fetch()['count'];
if (!$count) {
$statement = $this->connection->prepare(
"INSERT INTO {$this->locationViewsTable}
(`locationId`, `date`, `views`)
VALUES
(:locationId, :date, :views)"
);
} else {
$statement = $this->connection->prepare(
"UPDATE {$this->locationViewsTable} pv SET pv.views = pv.views + :views
WHERE pv.locationId = :locationId
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 $locationId
*
* @return mixed
* @throws QueryExecutionException
*/
public function deleteViewStats($locationId)
{
$params = [
':locationId' => $locationId,
];
try {
$statement = $this->connection->prepare(
"DELETE FROM {$this->locationViewsTable} WHERE locationId = :locationId"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to delete data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param $id
*
* @return Collection
* @throws InvalidArgumentException
* @throws QueryExecutionException
*/
public function getByIdWithEntities($id)
{
$usersTable = UsersTable::getTableName();
$providerPeriodsTable = ProvidersPeriodTable::getTableName();
$providerPeriodsLocations = ProvidersPeriodLocationTable::getTableName();
$eventsTable = EventsTable::getTableName();
$categoriesTable = CategoriesTable::getTableName();
$eventPeriodsTable = EventsPeriodsTable::getTableName();
$params = [
':id' => $id,
':eventLocationId' => $id
];
try {
$statement = $this->connection->prepare("
SELECT
l.id AS location_id,
l.status AS location_status,
l.name AS location_name,
l.description AS location_description,
l.address AS location_address,
l.phone AS location_phone,
l.latitude AS location_latitude,
l.longitude AS location_longitude,
l.pictureFullPath AS location_pictureFullPath,
l.pictureThumbPath AS location_pictureThumbPath,
l.pin AS location_pin,
l.translations AS location_translations,
pu.id AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.phone AS provider_phone,
pu.pictureThumbPath AS provider_pictureThumbPath,
pu.pictureFullPath AS provider_pictureFullPath,
s.id AS service_id,
s.name AS service_name,
s.color AS service_color,
c.id AS category_id,
c.name AS category_name,
e.id AS event_id,
e.name AS event_name,
e.color AS event_color,
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
FROM {$this->table} l
LEFT JOIN {$this->providerLocationTable} pl ON pl.locationId = l.id
LEFT JOIN {$providerPeriodsTable} pp ON pp.locationId = l.id
LEFT JOIN {$providerPeriodsLocations} ppl ON ppl.locationId = l.id
LEFT JOIN {$usersTable} pu ON pu.id = pl.userId
LEFT JOIN {$this->providerServicesTable} ps ON ps.userId = pl.userId
LEFT JOIN (SELECT * FROM {$this->servicesTable} LIMIT 11) s ON s.id = ps.serviceId
LEFT JOIN {$categoriesTable} c ON c.id = s.categoryId
LEFT JOIN (SELECT * FROM {$eventsTable} WHERE locationId = :eventLocationId LIMIT 11 ) e ON e.locationId = l.id
LEFT JOIN {$eventPeriodsTable} ep ON ep.eventId = e.id
WHERE l.id = :id
");
$statement->execute($params);
$rows = $statement->fetchAll();
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to get data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
}