<?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\Entity\Bookable\Service\Package;
use AmeliaBooking\Domain\Factory\Bookable\Service\PackageFactory;
use AmeliaBooking\Infrastructure\Connection;
use AmeliaBooking\Infrastructure\DB\WPDB\Statement;
use AmeliaBooking\Infrastructure\Repository\AbstractRepository;
use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesServicesLocationsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesServicesProvidersTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\PackagesServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Bookable\ServicesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Gallery\GalleriesTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\Location\LocationsTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\Provider\ProvidersServiceTable;
use AmeliaBooking\Infrastructure\WP\InstallActions\DB\User\UsersTable;
/**
* Class PackageRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Service
*/
class PackageRepository extends AbstractRepository
{
public const FACTORY = PackageFactory::class;
/**
* @param Connection $connection
* @param string $table
*/
public function __construct(
Connection $connection,
$table
) {
parent::__construct($connection, $table);
}
/**
* @param Package $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'],
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':position' => $data['position'],
':calculatedPrice' => $data['calculatedPrice'] ? 1 : 0,
':discount' => $data['discount'],
':settings' => $data['settings'],
':endDate' => $data['endDate'],
':durationCount' => $data['durationCount'],
':durationType' => $data['durationType'],
':translations' => $data['translations'],
':deposit' => $data['deposit'],
':depositPayment' => $data['depositPayment'],
':fullPayment' => $data['fullPayment'] ? 1 : 0,
':sharedCapacity' => $data['sharedCapacity'] ? 1 : 0,
':quantity' => $data['quantity'],
':limitPerCustomer' => $data['limitPerCustomer']
];
try {
$statement = $this->connection->prepare(
"INSERT INTO
{$this->table}
(
`name`,
`description`,
`color`,
`price`,
`status`,
`pictureFullPath`,
`pictureThumbPath`,
`calculatedPrice`,
`discount`,
`position`,
`settings`,
`endDate`,
`durationCount`,
`durationType`,
`translations`,
`deposit`,
`depositPayment`,
`fullPayment`,
`sharedCapacity`,
`quantity`,
`limitPerCustomer`
) VALUES (
:name,
:description,
:color,
:price,
:status,
:pictureFullPath,
:pictureThumbPath,
:calculatedPrice,
:discount,
:position,
:settings,
:endDate,
:durationCount,
:durationType,
:translations,
:deposit,
:depositPayment,
:fullPayment,
:sharedCapacity,
:quantity,
:limitPerCustomer
)"
);
$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 $packageId
* @param Package $entity
*
* @throws QueryExecutionException
*/
public function update($packageId, $entity)
{
$data = $entity->toArray();
$params = [
':name' => $data['name'],
':description' => $data['description'],
':color' => $data['color'],
':price' => $data['price'],
':status' => $data['status'],
':pictureFullPath' => $data['pictureFullPath'],
':pictureThumbPath' => $data['pictureThumbPath'],
':position' => $data['position'],
':calculatedPrice' => $data['calculatedPrice'] ? 1 : 0,
':discount' => $data['discount'],
':settings' => $data['settings'],
':endDate' => $data['endDate'],
':durationCount' => $data['durationCount'],
':durationType' => $data['durationType'],
':translations' => $data['translations'],
':deposit' => $data['deposit'],
':depositPayment' => $data['depositPayment'],
':fullPayment' => $data['fullPayment'] ? 1 : 0,
':sharedCapacity' => $data['sharedCapacity'] ? 1 : 0,
':quantity' => $data['quantity'],
':limitPerCustomer' => $data['limitPerCustomer'],
':id' => $packageId
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
`name` = :name,
`description` = :description,
`color` = :color,
`price` = :price,
`status` = :status,
`pictureFullPath` = :pictureFullPath,
`pictureThumbPath` = :pictureThumbPath,
`position` = :position,
`calculatedPrice` = :calculatedPrice,
`discount` = :discount,
`settings` = :settings,
`endDate` = :endDate,
`durationCount` = :durationCount,
`durationType` = :durationType,
`translations` = :translations,
`deposit` = :deposit,
`depositPayment` = :depositPayment,
`fullPayment` = :fullPayment,
`sharedCapacity` = :sharedCapacity,
`quantity` = :quantity,
`limitPerCustomer` = :limitPerCustomer
WHERE
id = :id"
);
$statement->execute($params);
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to save data in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
/**
* @param $criteria
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getByCriteria($criteria)
{
$params = [];
$where = [];
$order = 'ORDER BY p.name, ps.position ASC, ps.id ASC';
if (isset($criteria['sort'])) {
if ($criteria['sort'] === '') {
$order = 'ORDER BY p.position';
} else {
$sortField = $criteria['sort'];
if (strpos($sortField, '-') === 0) {
$sortField = substr($sortField, 1);
}
switch ($sortField) {
case 'name':
$orderColumn = 'p.name';
break;
case 'price':
$orderColumn = 'p.price';
break;
case 'id':
$orderColumn = 'p.id';
break;
case 'services':
$packageServicesTable = PackagesServicesTable::getTableName();
$orderColumn = "(SELECT COUNT(*) FROM {$packageServicesTable} WHERE packageId = p.id)";
break;
default:
$orderColumn = 'p.name';
}
$orderDirection = $criteria['sort'][0] === '-' ? 'DESC' : 'ASC';
$order = "ORDER BY {$orderColumn} {$orderDirection}";
}
}
if (!empty($criteria['id'])) {
$params[':id'] = $criteria['id'];
$where[] = 'p.id = :id';
}
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
p.name LIKE {$param}
OR p.id 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['packages'])) {
$queryPackages = [];
foreach ((array)$criteria['packages'] as $index => $value) {
$param = ':package' . $index;
$queryPackages[] = $param;
$params[$param] = $value;
}
$where[] = 'p.id IN (' . implode(', ', $queryPackages) . ')';
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = 's.status = :status';
}
$whereSql = $where ? ' AND ' . implode(' AND ', $where) : '';
$servicesTable = ServicesTable::getTableName();
$usersTable = UsersTable::getTableName();
$locationsTable = LocationsTable::getTableName();
$packageServicesTable = PackagesServicesTable::getTableName();
$providersToServicesTable = ProvidersServiceTable::getTableName();
$packageServicesProvidersTable = PackagesServicesProvidersTable::getTableName();
$packageServicesLocationsTable = PackagesServicesLocationsTable::getTableName();
$galleriesTable = GalleriesTable::getTableName();
// Define the common SELECT part of the query
$selectSql = "SELECT
p.id AS package_id,
p.name AS package_name,
p.description AS package_description,
p.color AS package_color,
p.price AS package_price,
p.status AS package_status,
p.pictureFullPath AS package_picture_full,
p.pictureThumbPath AS package_picture_thumb,
p.calculatedPrice AS package_calculated_price,
p.discount AS package_discount,
p.position AS package_position,
p.settings AS package_settings,
p.endDate AS package_endDate,
p.durationCount AS package_durationCount,
p.durationType AS package_durationType,
p.translations AS package_translations,
p.deposit AS package_deposit,
p.depositPayment AS package_depositPayment,
p.fullPayment AS package_fullPayment,
p.sharedCapacity AS package_sharedCapacity,
p.quantity AS package_quantity,
p.limitPerCustomer AS package_limitPerCustomer,
ps.id AS package_service_id,
ps.quantity AS package_service_quantity,
ps.minimumScheduled AS package_service_minimumScheduled,
ps.maximumScheduled AS package_service_maximumScheduled,
ps.allowProviderSelection AS package_service_allowProviderSelection,
ps.position AS package_service_position,
s.id AS service_id,
s.price AS service_price,
s.minCapacity AS service_minCapacity,
s.maxCapacity AS service_maxCapacity,
s.name AS service_name,
s.description AS service_description,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.translations AS service_translations,
s.show AS service_show,
s.color AS service_color,
l.id AS location_id,
l.name AS location_name,
l.address AS location_address,
l.phone AS location_phone,
l.latitude AS location_latitude,
l.longitude AS location_longitude,
COALESCE(psp.userId, pts.userId) AS provider_id,
pu.firstName AS provider_firstName,
pu.lastName AS provider_lastName,
pu.email AS provider_email,
pu.status AS provider_status,
pu.translations AS provider_translations,
pu.pictureFullPath AS provider_picture_full,
pu.pictureThumbPath AS provider_picture_thumb,
g.id AS gallery_id,
g.pictureFullPath AS gallery_picture_full,
g.pictureThumbPath AS gallery_picture_thumb,
g.position AS gallery_position";
$fromSql = "FROM {$this->table} p
LEFT JOIN {$packageServicesTable} ps ON ps.packageId = p.id
LEFT JOIN {$servicesTable} s ON ps.serviceId = s.id
LEFT JOIN {$packageServicesProvidersTable} psp ON psp.packageServiceId = ps.id
LEFT JOIN {$providersToServicesTable} pts ON pts.serviceId = ps.serviceId
LEFT JOIN {$usersTable} pu ON pu.id = COALESCE(psp.userId, pts.userId)
LEFT JOIN {$packageServicesLocationsTable} psl ON psl.packageServiceId = ps.id
LEFT JOIN {$locationsTable} l ON l.id = psl.locationId
LEFT JOIN {$galleriesTable} g ON g.entityId = p.id AND g.entityType = 'package'";
try {
// PAGINATION: If limit is set, use subquery for package IDs
if (!empty($criteria['limit'])) {
$itemsPerPage = (int)$criteria['limit'];
$page = !empty($criteria['page']) ? (int)$criteria['page'] : 1;
$offset = ($page - 1) * $itemsPerPage;
// Get paginated package IDs
$idSql = "SELECT DISTINCT p.id FROM {$this->table} p
LEFT JOIN {$packageServicesTable} ps ON ps.packageId = p.id
LEFT JOIN {$servicesTable} s ON ps.serviceId = s.id
WHERE 1 = 1{$whereSql} {$order}
LIMIT {$itemsPerPage} OFFSET {$offset}";
$idStmt = $this->connection->prepare($idSql);
$idStmt->execute($params);
$packageIds = $idStmt->fetchAll(Statement::FETCH_COLUMN);
if (empty($packageIds)) {
return call_user_func([static::FACTORY, 'createCollection'], []);
}
// Prepare ID parameters
$inParams = [];
foreach ($packageIds as $idx => $id) {
$inParams[":p_id{$idx}"] = $id;
}
$inClause = implode(',', array_keys($inParams));
$params = array_merge($params, $inParams);
$whereSqlWithIds = $whereSql ? $whereSql . ' AND ' : ' AND ';
$whereSqlWithIds .= "p.id IN ($inClause)";
$sql = "{$selectSql} {$fromSql} WHERE 1 = 1 {$whereSqlWithIds} {$order}";
$statement = $this->connection->prepare($sql);
} else {
// Standard query without pagination
$sql = "{$selectSql} {$fromSql} WHERE 1 = 1 {$whereSql} {$order}";
$statement = $this->connection->prepare($sql);
}
$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 $id
*
* @return Package
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getById($id)
{
$params[':id'] = $id;
$servicesTable = ServicesTable::getTableName();
$usersTable = UsersTable::getTableName();
$locationsTable = LocationsTable::getTableName();
$packageServicesTable = PackagesServicesTable::getTableName();
$packageServicesProvidersTable = PackagesServicesProvidersTable::getTableName();
$packageServicesLocationsTable = PackagesServicesLocationsTable::getTableName();
$galleriesTable = GalleriesTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT
p.id AS package_id,
p.name AS package_name,
p.description AS package_description,
p.color AS package_color,
p.price AS package_price,
p.status AS package_status,
p.pictureFullPath AS package_picture_full,
p.pictureThumbPath AS package_picture_thumb,
p.calculatedPrice AS package_calculated_price,
p.discount AS package_discount,
p.position AS package_position,
p.settings AS package_settings,
p.endDate AS package_endDate,
p.durationCount AS package_durationCount,
p.durationType AS package_durationType,
p.translations AS package_translations,
p.deposit AS package_deposit,
p.depositPayment AS package_depositPayment,
p.fullPayment AS package_fullPayment,
p.sharedCapacity AS package_sharedCapacity,
p.quantity AS package_quantity,
p.limitPerCustomer AS package_limitPerCustomer,
ps.id AS package_service_id,
ps.quantity AS package_service_quantity,
ps.minimumScheduled AS package_service_minimumScheduled,
ps.maximumScheduled AS package_service_maximumScheduled,
ps.allowProviderSelection AS package_service_allowProviderSelection,
ps.position AS package_service_position,
s.id AS service_id,
s.price AS service_price,
s.minCapacity AS service_minCapacity,
s.maxCapacity AS service_maxCapacity,
s.name AS service_name,
s.status AS service_status,
s.categoryId AS service_categoryId,
s.duration AS service_duration,
s.timeBefore AS service_timeBefore,
s.timeAfter AS service_timeAfter,
s.pictureFullPath AS service_picture_full,
s.pictureThumbPath AS service_picture_thumb,
s.show AS service_show,
s.color AS service_color,
l.id AS location_id,
l.name AS location_name,
l.address AS location_address,
l.phone AS location_phone,
l.latitude AS location_latitude,
l.longitude AS location_longitude,
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.countryPhoneIso AS provider_countryPhoneIso,
pu.translations AS provider_translations,
pu.stripeConnect AS provider_stripeConnect,
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} p
LEFT JOIN {$packageServicesTable} ps ON ps.packageId = p.id
LEFT JOIN {$servicesTable} s ON ps.serviceId = s.id
LEFT JOIN {$packageServicesProvidersTable} psp ON psp.packageServiceId = ps.id
LEFT JOIN {$packageServicesLocationsTable} psl ON psl.packageServiceId = ps.id
LEFT JOIN {$usersTable} pu ON pu.id = psp.userId
LEFT JOIN {$locationsTable} l ON l.id = psl.locationId
LEFT JOIN {$galleriesTable} g ON g.entityId = p.id AND g.entityType = 'package'
WHERE p.id = :id"
);
$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)->getItem($id);
}
public function getCount($criteria)
{
$params = [];
$where = [];
// Only add filters if criteria is not empty
if (!empty($criteria)) {
if (!empty($criteria['search'])) {
$terms = preg_split('/\s+/', trim($criteria['search']));
$termIndex = 0;
foreach ($terms as $term) {
$param = ":search{$termIndex}";
$params[$param] = "%{$term}%";
$where[] = "(
p.name LIKE {$param}
OR p.id 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) . ")";
}
}
$where = $where ? 'WHERE ' . implode(' AND ', $where) : '';
$packageServicesTable = PackagesServicesTable::getTableName();
$servicesTable = ServicesTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT COUNT(DISTINCT p.id) AS count
FROM {$this->table} p
LEFT JOIN {$packageServicesTable} ps ON ps.packageId = p.id
LEFT JOIN {$servicesTable} s ON ps.serviceId = s.id
{$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;
}
}