<?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\Resource;
use AmeliaBooking\Domain\Factory\Bookable\Service\ResourceFactory;
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\ResourcesToEntitiesTable;
/**
* Class ResourceRepository
*
* @package AmeliaBooking\Infrastructure\Repository\Service
*/
class ResourceRepository extends AbstractRepository
{
public const FACTORY = ResourceFactory::class;
/**
* @param Connection $connection
* @param string $table
*/
public function __construct(
Connection $connection,
$table
) {
parent::__construct($connection, $table);
}
/**
* @param Resource $entity
*
* @return int
* @throws QueryExecutionException
*/
public function add($entity)
{
$data = $entity->toArray();
$params = [
':name' => $data['name'],
':quantity' => $data['quantity'],
':status' => $data['status'],
':shared' => $data['shared'] ? $data['shared'] : null,
':countAdditionalPeople' => $data['countAdditionalPeople'] ? 1 : 0
];
try {
$statement = $this->connection->prepare(
"INSERT INTO
{$this->table}
(
`name`,
`quantity`,
`status`,
`shared`,
`countAdditionalPeople`
) VALUES (
:name,
:quantity,
:status,
:shared,
:countAdditionalPeople
)"
);
$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 $resourceId
* @param Resource $entity
*
* @throws QueryExecutionException
*/
public function update($resourceId, $entity)
{
$data = $entity->toArray();
$params = [
':name' => $data['name'],
':quantity' => $data['quantity'],
':status' => $data['status'],
':shared' => $data['shared'] ? $data['shared'] : null,
':countAdditionalPeople' => $data['countAdditionalPeople'] ? 1 : 0,
':id' => $resourceId
];
try {
$statement = $this->connection->prepare(
"UPDATE {$this->table}
SET
`name` = :name,
`quantity` = :quantity,
`status` = :status,
`shared` = :shared,
`countAdditionalPeople` = :countAdditionalPeople
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 = [];
// Define ordering
$order = 'ORDER BY r.id ASC';
if (isset($criteria['sort'])) {
if ($criteria['sort'] === '-id') {
$order = 'ORDER BY r.id DESC';
} elseif ($criteria['sort'] === 'name') {
$order = 'ORDER BY r.name ASC';
} elseif ($criteria['sort'] === '-name') {
$order = 'ORDER BY r.name DESC';
} elseif ($criteria['sort'] === 'quantity') {
$order = 'ORDER BY r.quantity ASC';
} elseif ($criteria['sort'] === '-quantity') {
$order = 'ORDER BY r.quantity DESC';
}
}
if (!empty($criteria['search'])) {
$params[':search1'] = $params[':search2'] = "%{$criteria['search']}%";
$where[] = '(r.name LIKE :search1 OR r.id LIKE :search2)';
}
if (!empty($criteria['services'])) {
$query = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="service"';
}
if (!empty($criteria['locations'])) {
$query = [];
foreach ((array)$criteria['locations'] as $index => $value) {
$param = ':location' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="location"';
}
if (!empty($criteria['employees'])) {
$query = [];
foreach ((array)$criteria['employees'] as $index => $value) {
$param = ':employee' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="employee"';
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = 'r.status = :status';
}
$whereSql = $where ? ' AND ' . implode(' AND ', $where) : '';
$resourceEntitiesTable = ResourcesToEntitiesTable::getTableName();
// Define common SELECT and FROM parts
$selectSql = "SELECT
r.id AS resource_id,
r.name AS resource_name,
r.quantity AS resource_quantity,
r.status AS resource_status,
r.shared AS resource_shared,
r.countAdditionalPeople AS resource_countAdditionalPeople,
re.id AS resource_entity_id,
re.resourceId AS resource_entity_resourceId,
re.entityId AS resource_entity_entityId,
re.entityType AS resource_entity_entityType";
$fromSql = "FROM {$this->table} r
LEFT JOIN {$resourceEntitiesTable} re ON re.resourceId = r.id";
try {
// PAGINATION: If limit is set, use subquery to get resource IDs first
if (!empty($criteria['limit'])) {
$itemsPerPage = (int)$criteria['limit'];
$page = !empty($criteria['page']) ? (int)$criteria['page'] : 1;
$offset = ($page - 1) * $itemsPerPage;
// 1. Get paginated resource IDs
$idSql = "SELECT DISTINCT r.id FROM {$this->table} r
LEFT JOIN {$resourceEntitiesTable} re ON re.resourceId = r.id
WHERE 1 = 1{$whereSql} {$order}
LIMIT {$itemsPerPage} OFFSET {$offset}";
$idStmt = $this->connection->prepare($idSql);
$idStmt->execute($params);
$resourceIds = $idStmt->fetchAll(Statement::FETCH_COLUMN);
if (empty($resourceIds)) {
return call_user_func([static::FACTORY, 'createCollection'], []);
}
// 2. Prepare ID parameters for IN clause
$inParams = [];
foreach ($resourceIds as $idx => $id) {
$inParams[":r_id{$idx}"] = $id;
}
$inClause = implode(',', array_keys($inParams));
$params = array_merge($params, $inParams);
$whereSqlWithIds = $whereSql ? $whereSql . ' AND ' : ' AND ';
$whereSqlWithIds .= "r.id IN ($inClause)";
// 3. Get complete resource data
$sql = "{$selectSql} {$fromSql} WHERE 1 = 1 {$whereSqlWithIds} {$order}";
$statement = $this->connection->prepare($sql);
$statement->execute($params);
$rows = $statement->fetchAll();
} else {
// Regular 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 criteria in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
return call_user_func([static::FACTORY, 'createCollection'], $rows);
}
/**
* @param $id
*
* @return Collection
* @throws QueryExecutionException
* @throws InvalidArgumentException
*/
public function getById($id)
{
$params[':id'] = $id;
$resourceEntitiesTable = ResourcesToEntitiesTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT
r.id AS resource_id,
r.name AS resource_name,
r.quantity AS resource_quantity,
r.status AS resource_status,
r.shared AS resource_shared,
r.countAdditionalPeople AS resource_countAdditionalPeople,
re.id AS resource_entity_id,
re.resourceId AS resource_entity_resourceId,
re.entityId AS resource_entity_entityId,
re.entityType AS resource_entity_entityType
FROM {$this->table} r
LEFT JOIN {$resourceEntitiesTable} re ON re.resourceId = r.id
WHERE r.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);
}
/**
* @param int $id
*
* @return bool
* @throws QueryExecutionException|InvalidArgumentException
*/
public function delete($id)
{
$resourceToEntities = ResourcesToEntitiesTable::getTableName();
$params = [
':id' => $id,
];
try {
$statement = $this->connection->prepare(
"DELETE FROM {$this->table} WHERE id = :id"
);
$statement->execute($params);
$statement = $this->connection->prepare(
"DELETE FROM {$resourceToEntities} WHERE resourceId = :id"
);
$statement->execute($params);
return true;
} catch (\Exception $e) {
throw new QueryExecutionException('Unable to delete data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e);
}
}
public function getCount($criteria)
{
$params = [];
$where = [];
if (!empty($criteria['search'])) {
$params[':search1'] = $params[':search2'] = "%{$criteria['search']}%";
$where[] = '(r.name LIKE :search1 OR r.id LIKE :search2)';
}
if (!empty($criteria['services'])) {
$query = [];
foreach ((array)$criteria['services'] as $index => $value) {
$param = ':service' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="service"';
}
if (!empty($criteria['locations'])) {
$query = [];
foreach ((array)$criteria['locations'] as $index => $value) {
$param = ':location' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="location"';
}
if (!empty($criteria['employees'])) {
$query = [];
foreach ((array)$criteria['employees'] as $index => $value) {
$param = ':employee' . $index;
$query[] = $param;
$params[$param] = $value;
}
$where[] = 're.entityId IN (' . implode(', ', $query) . ') AND re.entityType="employee"';
}
if (!empty($criteria['status'])) {
$params[':status'] = $criteria['status'];
$where[] = 'r.status = :status';
}
$where = $where ? ' AND ' . implode(' AND ', $where) : '';
$resourceEntitiesTable = ResourcesToEntitiesTable::getTableName();
try {
$statement = $this->connection->prepare(
"SELECT COUNT(DISTINCT r.id) AS count
FROM {$this->table} r
LEFT JOIN {$resourceEntitiesTable} re ON re.resourceId = r.id
WHERE 1 = 1 {$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;
}
}