Bookable
1 month ago
Booking
1 month ago
Cache
3 months ago
Coupon
2 months ago
CustomField
3 months ago
Gallery
3 months ago
Google
2 months ago
Location
3 months ago
Notification
3 months ago
Outlook
2 months ago
Payment
3 months ago
Schedule
2 months ago
Tax
2 months ago
User
2 months ago
AbstractEntityRepository.php
3 months ago
AbstractRepository.php
3 months ago
AbstractRepository.php
591 lines
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * @copyright © Melograno Ventures. All rights reserved. |
| 5 | * @licence See LICENCE.md for license details. |
| 6 | */ |
| 7 | |
| 8 | namespace AmeliaBooking\Infrastructure\Repository; |
| 9 | |
| 10 | use AmeliaBooking\Domain\Collection\Collection; |
| 11 | use AmeliaBooking\Domain\Common\Exceptions\InvalidArgumentException; |
| 12 | use AmeliaBooking\Infrastructure\Common\Exceptions\NotFoundException; |
| 13 | use AmeliaBooking\Infrastructure\Common\Exceptions\QueryExecutionException; |
| 14 | use AmeliaBooking\Domain\Services\Database\ConnectionInterface; |
| 15 | |
| 16 | class AbstractRepository |
| 17 | { |
| 18 | public const FACTORY = ''; |
| 19 | |
| 20 | /** @var ConnectionInterface */ |
| 21 | protected $connection; |
| 22 | |
| 23 | /** @var string */ |
| 24 | protected $table; |
| 25 | |
| 26 | /** |
| 27 | * @param ConnectionInterface $connection |
| 28 | * @param string $table |
| 29 | */ |
| 30 | public function __construct(ConnectionInterface $connection, $table) |
| 31 | { |
| 32 | $this->connection = $connection; |
| 33 | $this->table = $table; |
| 34 | } |
| 35 | |
| 36 | /** |
| 37 | * @param int $id |
| 38 | * |
| 39 | * @return mixed |
| 40 | * @throws NotFoundException |
| 41 | * @throws QueryExecutionException |
| 42 | */ |
| 43 | public function getById($id) |
| 44 | { |
| 45 | try { |
| 46 | $statement = $this->connection->prepare($this->selectQuery() . " WHERE {$this->table}.id = :id"); |
| 47 | $statement->bindParam(':id', $id); |
| 48 | $statement->execute(); |
| 49 | $row = $statement->fetch(); |
| 50 | } catch (\Exception $e) { |
| 51 | throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 52 | } |
| 53 | |
| 54 | if (!$row) { |
| 55 | throw new NotFoundException('Data not found in ' . __CLASS__); |
| 56 | } |
| 57 | |
| 58 | return call_user_func([static::FACTORY, 'create'], $row); |
| 59 | } |
| 60 | |
| 61 | /** |
| 62 | * @param array $ids |
| 63 | * |
| 64 | * @return Collection |
| 65 | * @throws NotFoundException |
| 66 | * @throws QueryExecutionException |
| 67 | * @throws InvalidArgumentException |
| 68 | */ |
| 69 | public function getByIds($ids) |
| 70 | { |
| 71 | $params = []; |
| 72 | |
| 73 | foreach ($ids as $index => $id) { |
| 74 | $params[':id' . $index] = $id; |
| 75 | } |
| 76 | |
| 77 | $where = " WHERE id IN (" . implode(', ', array_keys($params)) . ')'; |
| 78 | |
| 79 | try { |
| 80 | $statement = $this->connection->prepare($this->selectQuery() . $where); |
| 81 | |
| 82 | $statement->execute($params); |
| 83 | |
| 84 | $rows = $statement->fetchAll(); |
| 85 | } catch (\Exception $e) { |
| 86 | throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 87 | } |
| 88 | |
| 89 | $entities = new Collection(); |
| 90 | |
| 91 | foreach ($rows as $row) { |
| 92 | $entities->addItem( |
| 93 | call_user_func([static::FACTORY, 'create'], $row), |
| 94 | $row['id'] |
| 95 | ); |
| 96 | } |
| 97 | |
| 98 | return $entities; |
| 99 | } |
| 100 | |
| 101 | /** |
| 102 | * @return Collection |
| 103 | * @throws InvalidArgumentException |
| 104 | * @throws QueryExecutionException |
| 105 | */ |
| 106 | public function getAll() |
| 107 | { |
| 108 | try { |
| 109 | $statement = $this->connection->query($this->selectQuery()); |
| 110 | $rows = $statement->fetchAll(); |
| 111 | } catch (\Exception $e) { |
| 112 | throw new QueryExecutionException('Unable to get all data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 113 | } |
| 114 | |
| 115 | $items = []; |
| 116 | foreach ($rows as $row) { |
| 117 | $items[] = call_user_func([static::FACTORY, 'create'], $row); |
| 118 | } |
| 119 | |
| 120 | return new Collection($items); |
| 121 | } |
| 122 | |
| 123 | /** |
| 124 | * @return Collection |
| 125 | * @throws InvalidArgumentException |
| 126 | * @throws QueryExecutionException |
| 127 | */ |
| 128 | public function getAllIndexedById() |
| 129 | { |
| 130 | try { |
| 131 | $statement = $this->connection->query($this->selectQuery()); |
| 132 | $rows = $statement->fetchAll(); |
| 133 | } catch (\Exception $e) { |
| 134 | throw new QueryExecutionException('Unable to get all data indexed by id from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 135 | } |
| 136 | |
| 137 | $collection = new Collection(); |
| 138 | foreach ($rows as $row) { |
| 139 | $collection->addItem( |
| 140 | call_user_func([static::FACTORY, 'create'], $row), |
| 141 | $row['id'] |
| 142 | ); |
| 143 | } |
| 144 | |
| 145 | return $collection; |
| 146 | } |
| 147 | |
| 148 | /** |
| 149 | * @return Collection |
| 150 | * @throws InvalidArgumentException |
| 151 | * @throws QueryExecutionException |
| 152 | */ |
| 153 | public function getByFieldValue($field, $value) |
| 154 | { |
| 155 | $params = [ |
| 156 | ":$field" => $value, |
| 157 | ]; |
| 158 | |
| 159 | try { |
| 160 | $statement = $this->connection->prepare( |
| 161 | "SELECT * FROM {$this->table} WHERE {$field} = :{$field}" |
| 162 | ); |
| 163 | |
| 164 | $statement->execute($params); |
| 165 | |
| 166 | $rows = $statement->fetchAll(); |
| 167 | } catch (\Exception $e) { |
| 168 | throw new QueryExecutionException('Unable to find by field value in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 169 | } |
| 170 | |
| 171 | $collection = new Collection(); |
| 172 | foreach ($rows as $row) { |
| 173 | $collection->addItem( |
| 174 | call_user_func([static::FACTORY, 'create'], $row), |
| 175 | $row['id'] |
| 176 | ); |
| 177 | } |
| 178 | |
| 179 | return $collection; |
| 180 | } |
| 181 | |
| 182 | /** |
| 183 | * @param int $id |
| 184 | * |
| 185 | * @return bool |
| 186 | * @throws QueryExecutionException |
| 187 | */ |
| 188 | public function delete($id) |
| 189 | { |
| 190 | try { |
| 191 | $statement = $this->connection->prepare("DELETE FROM {$this->table} WHERE id = :id"); |
| 192 | $statement->bindParam(':id', $id); |
| 193 | $statement->execute(); |
| 194 | return true; |
| 195 | } catch (\Exception $e) { |
| 196 | throw new QueryExecutionException( |
| 197 | 'Unable to delete data from ' . __CLASS__ . |
| 198 | "\n" . $e->getTraceAsString(), |
| 199 | $e->getCode(), |
| 200 | $e |
| 201 | ); |
| 202 | } |
| 203 | } |
| 204 | |
| 205 | /** |
| 206 | * @param int $entityId |
| 207 | * @param String $entityColumnName |
| 208 | * |
| 209 | * @return Collection |
| 210 | * @throws QueryExecutionException |
| 211 | * @throws InvalidArgumentException |
| 212 | */ |
| 213 | public function getByEntityId($entityId, $entityColumnName) |
| 214 | { |
| 215 | $params = [ |
| 216 | ":$entityColumnName" => $entityId, |
| 217 | ]; |
| 218 | |
| 219 | try { |
| 220 | $statement = $this->connection->prepare( |
| 221 | "SELECT * FROM {$this->table} WHERE {$entityColumnName} = :{$entityColumnName}" |
| 222 | ); |
| 223 | |
| 224 | $statement->execute($params); |
| 225 | |
| 226 | $rows = $statement->fetchAll(); |
| 227 | } catch (\Exception $e) { |
| 228 | throw new QueryExecutionException('Unable to find by id in ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 229 | } |
| 230 | |
| 231 | $items = []; |
| 232 | |
| 233 | foreach ($rows as $row) { |
| 234 | $items[] = call_user_func([static::FACTORY, 'create'], $row); |
| 235 | } |
| 236 | |
| 237 | return new Collection($items); |
| 238 | } |
| 239 | |
| 240 | /** |
| 241 | * @param int $entityId |
| 242 | * @param String $entityColumnName |
| 243 | * |
| 244 | * @return bool |
| 245 | * @throws QueryExecutionException |
| 246 | */ |
| 247 | public function deleteByEntityId($entityId, $entityColumnName) |
| 248 | { |
| 249 | $params = [ |
| 250 | ":$entityColumnName" => $entityId, |
| 251 | ]; |
| 252 | |
| 253 | try { |
| 254 | $statement = $this->connection->prepare( |
| 255 | "DELETE FROM {$this->table} WHERE {$entityColumnName} = :{$entityColumnName}" |
| 256 | ); |
| 257 | |
| 258 | $statement->execute($params); |
| 259 | |
| 260 | return true; |
| 261 | } catch (\Exception $e) { |
| 262 | throw new QueryExecutionException( |
| 263 | 'Unable to delete data by entity id from ' . __CLASS__ . |
| 264 | "\n" . $e->getTraceAsString(), |
| 265 | $e->getCode(), |
| 266 | $e |
| 267 | ); |
| 268 | } |
| 269 | } |
| 270 | |
| 271 | /** |
| 272 | * SET $entityColumnName = $entityColumnValue WHERE $entityColumnName = $entityId |
| 273 | * |
| 274 | * @param int $entityId |
| 275 | * @param String $entityColumnValue |
| 276 | * @param String $entityColumnName |
| 277 | * |
| 278 | * @return mixed |
| 279 | * @throws QueryExecutionException |
| 280 | */ |
| 281 | public function updateByEntityId($entityId, $entityColumnValue, $entityColumnName) |
| 282 | { |
| 283 | $params = [ |
| 284 | ":$entityColumnName" => $entityId, |
| 285 | ]; |
| 286 | |
| 287 | if ($entityColumnValue !== null) { |
| 288 | $updateSql = "`{$entityColumnName}` = :value"; |
| 289 | |
| 290 | $params[':value'] = $entityColumnValue; |
| 291 | } else { |
| 292 | $updateSql = "`{$entityColumnName}` = NULL"; |
| 293 | } |
| 294 | |
| 295 | try { |
| 296 | $statement = $this->connection->prepare( |
| 297 | "UPDATE {$this->table} SET |
| 298 | {$updateSql} |
| 299 | WHERE {$entityColumnName} = :{$entityColumnName}" |
| 300 | ); |
| 301 | |
| 302 | $statement->execute($params); |
| 303 | |
| 304 | return true; |
| 305 | } catch (\Exception $e) { |
| 306 | throw new QueryExecutionException( |
| 307 | 'Unable to update by entity id in ' . $this->table . ' in class ' . __CLASS__ . "\n\n" . $e->getTraceAsString(), |
| 308 | $e->getCode(), |
| 309 | $e |
| 310 | ); |
| 311 | } |
| 312 | } |
| 313 | |
| 314 | /** |
| 315 | * SET $fieldName = $fieldValue WHERE id = $id |
| 316 | * |
| 317 | * @param int $id |
| 318 | * @param mixed $fieldValue |
| 319 | * @param string $fieldName |
| 320 | * |
| 321 | * @return mixed |
| 322 | * @throws QueryExecutionException |
| 323 | */ |
| 324 | public function updateFieldById($id, $fieldValue, $fieldName) |
| 325 | { |
| 326 | $params = [ |
| 327 | ':id' => (int)$id, |
| 328 | ":$fieldName" => $fieldValue |
| 329 | ]; |
| 330 | |
| 331 | try { |
| 332 | $statement = $this->connection->prepare( |
| 333 | "UPDATE {$this->table} |
| 334 | SET |
| 335 | `$fieldName` = :$fieldName |
| 336 | WHERE id = :id" |
| 337 | ); |
| 338 | |
| 339 | $statement->execute($params); |
| 340 | |
| 341 | return true; |
| 342 | } catch (\Exception $e) { |
| 343 | throw new QueryExecutionException( |
| 344 | 'Unable to update field by id in table ' . $this->table . ' in class ' . __CLASS__ . "\n\n" . $e->getTraceAsString(), |
| 345 | $e->getCode(), |
| 346 | $e |
| 347 | ); |
| 348 | } |
| 349 | } |
| 350 | |
| 351 | /** |
| 352 | * SET $fieldName = $fieldValue WHERE id in $ids |
| 353 | * |
| 354 | * @param array $ids |
| 355 | * @param mixed $fieldValue |
| 356 | * @param string $fieldName |
| 357 | * |
| 358 | * @return mixed |
| 359 | * @throws QueryExecutionException |
| 360 | */ |
| 361 | public function updateFieldByIds($ids, $fieldValue, $fieldName) |
| 362 | { |
| 363 | $params = [ |
| 364 | ":$fieldName" => $fieldValue |
| 365 | ]; |
| 366 | |
| 367 | $where = ''; |
| 368 | |
| 369 | if (!empty($ids)) { |
| 370 | $queryIds = []; |
| 371 | |
| 372 | foreach ($ids as $index => $value) { |
| 373 | $param = ':id' . $index; |
| 374 | |
| 375 | $queryIds[] = $param; |
| 376 | |
| 377 | $params[$param] = $value; |
| 378 | } |
| 379 | |
| 380 | $where = 'WHERE id IN (' . implode(', ', $queryIds) . ')'; |
| 381 | } |
| 382 | |
| 383 | try { |
| 384 | $statement = $this->connection->prepare( |
| 385 | "UPDATE {$this->table} |
| 386 | SET |
| 387 | `$fieldName` = :$fieldName |
| 388 | {$where}" |
| 389 | ); |
| 390 | |
| 391 | $statement->execute($params); |
| 392 | |
| 393 | return true; |
| 394 | } catch (\Exception $e) { |
| 395 | throw new QueryExecutionException( |
| 396 | 'Unable to update field by ids in table ' . $this->table . ' in class ' . __CLASS__ . "\n\n" . $e->getTraceAsString(), |
| 397 | $e->getCode(), |
| 398 | $e |
| 399 | ); |
| 400 | } |
| 401 | } |
| 402 | |
| 403 | /** |
| 404 | * SET $fieldName = $fieldValue WHERE $columnName = $columnValue |
| 405 | * |
| 406 | * @param string $fieldName |
| 407 | * @param mixed $fieldValue |
| 408 | * @param string $columnName |
| 409 | * @param mixed $columnValue |
| 410 | * |
| 411 | * @return void |
| 412 | * @throws QueryExecutionException |
| 413 | */ |
| 414 | public function updateFieldByColumn($fieldName, $fieldValue, $columnName, $columnValue) |
| 415 | { |
| 416 | $params = [ |
| 417 | ':first' => $fieldValue, |
| 418 | ':second' => $columnValue, |
| 419 | ]; |
| 420 | |
| 421 | try { |
| 422 | $statement = $this->connection->prepare( |
| 423 | "UPDATE {$this->table} |
| 424 | SET |
| 425 | `$fieldName` = :first |
| 426 | WHERE $columnName = :second" |
| 427 | ); |
| 428 | |
| 429 | $statement->execute($params); |
| 430 | } catch (\Exception $e) { |
| 431 | throw new QueryExecutionException( |
| 432 | 'Unable to update field by column in table ' . $this->table . ' in class ' . __CLASS__ . "\n\n" . $e->getTraceAsString(), |
| 433 | $e->getCode(), |
| 434 | $e |
| 435 | ); |
| 436 | } |
| 437 | } |
| 438 | |
| 439 | /** |
| 440 | * @return string |
| 441 | */ |
| 442 | protected function selectQuery() |
| 443 | { |
| 444 | return "SELECT * FROM {$this->table}"; |
| 445 | } |
| 446 | |
| 447 | /** |
| 448 | * @return void |
| 449 | */ |
| 450 | public function beginTransaction() |
| 451 | { |
| 452 | $this->connection->beginTransaction(); |
| 453 | } |
| 454 | |
| 455 | /** |
| 456 | * @return void |
| 457 | */ |
| 458 | public function commit() |
| 459 | { |
| 460 | $this->connection->commit(); |
| 461 | } |
| 462 | |
| 463 | /** |
| 464 | * @return void |
| 465 | */ |
| 466 | public function rollback() |
| 467 | { |
| 468 | $this->connection->rollBack(); |
| 469 | } |
| 470 | |
| 471 | /** |
| 472 | * @param int $page |
| 473 | * @param int $itemsPerPage |
| 474 | * |
| 475 | * @return string |
| 476 | */ |
| 477 | protected function getLimit($page, $itemsPerPage) |
| 478 | { |
| 479 | return $page && $itemsPerPage ? 'LIMIT ' . (int)(($page - 1) * $itemsPerPage) . ', ' . (int)$itemsPerPage : ''; |
| 480 | } |
| 481 | |
| 482 | /** |
| 483 | * @param String $primaryTable |
| 484 | * @param String $primaryColumn |
| 485 | * @param String $corruptedTable |
| 486 | * @param String $corruptedColumn |
| 487 | * @param String $typeColumn |
| 488 | * @param String $typeValue |
| 489 | * |
| 490 | * @return String |
| 491 | * @throws QueryExecutionException |
| 492 | */ |
| 493 | public function getMissingData( |
| 494 | $primaryTable, |
| 495 | $primaryColumn, |
| 496 | $corruptedTable, |
| 497 | $corruptedColumn, |
| 498 | $typeColumn, |
| 499 | $typeValue |
| 500 | ) { |
| 501 | try { |
| 502 | $statement = $this->connection->prepare( |
| 503 | "SELECT pt.{$primaryColumn} AS {$primaryColumn} FROM {$primaryTable} pt |
| 504 | LEFT JOIN {$corruptedTable} ct ON ct.{$corruptedColumn} = pt.{$primaryColumn} |
| 505 | WHERE ct.{$corruptedColumn} IS NULL |
| 506 | AND pt.{$primaryColumn} IS NOT NULL" . |
| 507 | ($typeColumn && $typeValue ? " AND {$typeColumn} = '{$typeValue}'" : '') |
| 508 | ); |
| 509 | |
| 510 | $statement->execute(); |
| 511 | |
| 512 | $rows = $statement->fetchAll(); |
| 513 | } catch (\Exception $e) { |
| 514 | throw new QueryExecutionException('Unable to get missing data from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 515 | } |
| 516 | |
| 517 | if ($rows) { |
| 518 | return "Missing {$primaryColumn} (" . implode(', ', array_unique(array_column($rows, $primaryColumn))) . ") in table {$primaryTable}"; |
| 519 | } |
| 520 | |
| 521 | return ''; |
| 522 | } |
| 523 | |
| 524 | /** |
| 525 | * @return array |
| 526 | * @throws QueryExecutionException |
| 527 | */ |
| 528 | public function getIds($criteria = []) |
| 529 | { |
| 530 | $where = []; |
| 531 | |
| 532 | $params = []; |
| 533 | |
| 534 | foreach ($criteria as $columnName => $columnValues) { |
| 535 | foreach ($columnValues as $index => $columnValue) { |
| 536 | $params[":$columnName$index"] = $columnValue; |
| 537 | } |
| 538 | |
| 539 | $where[] = "$columnName IN (" . implode(', ', array_keys($params)) . ')'; |
| 540 | } |
| 541 | |
| 542 | $where = $where ? ' WHERE ' . implode(' AND ', $where) : ''; |
| 543 | |
| 544 | try { |
| 545 | $statement = $this->connection->prepare( |
| 546 | "SELECT id AS id FROM {$this->table} |
| 547 | {$where}" |
| 548 | ); |
| 549 | |
| 550 | $statement->execute($params); |
| 551 | |
| 552 | $rows = $statement->fetchAll(); |
| 553 | } catch (\Exception $e) { |
| 554 | throw new QueryExecutionException('Unable to get ids from ' . __CLASS__ . '. ' . $e->getMessage(), $e->getCode(), $e); |
| 555 | } |
| 556 | |
| 557 | return array_map('intval', array_column($rows, 'id')); |
| 558 | } |
| 559 | |
| 560 | /** |
| 561 | * @param int $entityId |
| 562 | * @param string $errorMessage |
| 563 | * |
| 564 | * @throws QueryExecutionException |
| 565 | */ |
| 566 | public function updateErrorColumn($entityId, $errorMessage) |
| 567 | { |
| 568 | $params = [ |
| 569 | ':error' => $errorMessage, |
| 570 | ':id' => $entityId |
| 571 | ]; |
| 572 | |
| 573 | try { |
| 574 | $statement = $this->connection->prepare( |
| 575 | "UPDATE {$this->table} e |
| 576 | SET e.error = CONCAT(e.error, ' | ', :error) |
| 577 | WHERE e.id=:id; |
| 578 | " |
| 579 | ); |
| 580 | |
| 581 | $statement->execute($params); |
| 582 | } catch (\Exception $e) { |
| 583 | throw new QueryExecutionException( |
| 584 | 'Unable to add error "' . $errorMessage . '" to ' . $this->table . ' with id ' . $entityId . ' in ' . __CLASS__, |
| 585 | $e->getCode(), |
| 586 | $e |
| 587 | ); |
| 588 | } |
| 589 | } |
| 590 | } |
| 591 |