<?php
|
declare(strict_types=1);
|
|
namespace App\Repository;
|
|
use App\Entity\Book;
|
use App\Enum\LabelType;
|
use App\Model\BookSearch;
|
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
|
use Doctrine\DBAL\Types\Types;
|
use Doctrine\ORM\QueryBuilder;
|
use Doctrine\Persistence\ManagerRegistry;
|
|
/**
|
* @extends ServiceEntityRepository<Book>
|
*/
|
class BookRepository extends ServiceEntityRepository {
|
public function __construct(ManagerRegistry $registry) {
|
parent::__construct($registry, Book::class);
|
}
|
|
public function createListQueryBuilder(): QueryBuilder {
|
// Book::$borrow is the inverse side of a one-to-one, so Doctrine would otherwise load it
|
// row by row. Labels are batch-loaded through their EAGER mapping.
|
return $this->createQueryBuilder('b')
|
->addSelect('br')
|
->leftJoin('b.borrow', 'br')
|
->orderBy('b.id', 'ASC');
|
}
|
|
public function createSearchQueryBuilder(BookSearch $search): QueryBuilder {
|
$builder = $this->createListQueryBuilder();
|
|
$fields = [
|
'title' => $search->title,
|
'author' => $search->author,
|
'edition' => $search->edition,
|
'publisher' => $search->publisher,
|
];
|
|
foreach ($fields as $field => $value) {
|
if (trim((string)$value) !== '') {
|
$builder
|
->andWhere(\sprintf("b.%s LIKE :%s ESCAPE '!'", $field, $field))
|
->setParameter($field, self::containsPattern($value));
|
}
|
}
|
|
return $builder;
|
}
|
|
/**
|
* Quick search: an ID or ISBN/ISSN matches exactly, anything else word by word.
|
*/
|
public function createQuickSearchQueryBuilder(string $query): QueryBuilder {
|
$builder = $this->createListQueryBuilder();
|
$words = preg_split('/\s+/u', trim($query), flags: \PREG_SPLIT_NO_EMPTY) ?: [];
|
|
foreach ($words as $index => $word) {
|
$conditions = [
|
\sprintf("b.title LIKE :word%d ESCAPE '!'", $index),
|
\sprintf("b.author LIKE :word%d ESCAPE '!'", $index),
|
\sprintf("b.publisher LIKE :word%d ESCAPE '!'", $index),
|
];
|
$builder->setParameter('word' . $index, self::containsPattern($word));
|
|
if (\count($words) === 1) {
|
$conditions[] = 'EXISTS (SELECT 1 FROM App\Entity\Label n WHERE n.book = b AND n.type <> :textType AND n.label = :number)';
|
$builder
|
->setParameter('textType', LabelType::TEXT->value)
|
->setParameter('number', LabelType::ISBN->normalize($word));
|
|
if (ctype_digit($word) && \strlen($word) <= 9) {
|
$conditions[] = 'b.id = :id';
|
$builder->setParameter('id', (int)$word);
|
}
|
}
|
|
$builder->andWhere($builder->expr()->orX(...$conditions));
|
}
|
|
return $builder;
|
}
|
|
/**
|
* @param list<int> $ids
|
* @return list<Book>
|
*/
|
public function findForInventorySync(array $ids): array {
|
return $this->createListQueryBuilder()
|
->andWhere('b.id IN (:ids)')
|
->setParameter('ids', $ids)
|
->getQuery()
|
->getResult();
|
}
|
|
/**
|
* Books on the shelves (not lent out) that were not seen during an inventory on or after
|
* $since, including those never seen at all.
|
*/
|
public function createNotSeenSinceQueryBuilder(\DateTimeImmutable $since): QueryBuilder {
|
return $this->createListQueryBuilder()
|
->andWhere('b.lastSeen IS NULL OR b.lastSeen < :since')
|
->andWhere('br.book IS NULL')
|
->setParameter('since', $since, Types::DATETIME_IMMUTABLE);
|
}
|
|
public function countNotSeenSince(\DateTimeImmutable $since, bool $borrowed = false): int {
|
return (int)$this->createQueryBuilder('b')
|
->select('COUNT(b.id)')
|
->leftJoin('b.borrow', 'br')
|
->andWhere('b.lastSeen IS NULL OR b.lastSeen < :since')
|
->andWhere($borrowed ? 'br.book IS NOT NULL' : 'br.book IS NULL')
|
->setParameter('since', $since, Types::DATETIME_IMMUTABLE)
|
->getQuery()
|
->getSingleScalarResult();
|
}
|
|
/**
|
* A short fingerprint of exactly which books createNotSeenSinceQueryBuilder() selects.
|
*/
|
public function notSeenSinceFingerprint(\DateTimeImmutable $since): string {
|
$ids = $this->createNotSeenSinceQueryBuilder($since)
|
->select('b.id')
|
->getQuery()
|
->getSingleColumnResult();
|
|
return hash('sha256', implode(',', $ids));
|
}
|
|
/**
|
* Everything the offline inventory page needs, as plain arrays: hydrating thousands of books
|
* with their labels would be far slower.
|
*
|
* @return list<array{id: int, title: string, author: string, placement: ?string, lastSeen: ?string, borrowed: bool, isbn: list<string>, issn: list<string>}>
|
*/
|
public function findInventoryData(): array {
|
$entityManager = $this->getEntityManager();
|
|
$numbers = [];
|
$labels = $entityManager->createQuery('SELECT IDENTITY(l.book) AS book, l.type, l.label FROM App\Entity\Label l WHERE l.type IN (:types) ORDER BY l.label')
|
->setParameter('types', [LabelType::ISBN->value, LabelType::ISSN->value])
|
->getArrayResult();
|
foreach ($labels as $label) {
|
$numbers[(int)$label['book']][$label['type']->value][] = $label['label'];
|
}
|
|
$rows = $this->createQueryBuilder('b')
|
->select('b.id, b.title, b.author, b.placement, b.lastSeen, IDENTITY(br.book) AS borrowedBook')
|
->leftJoin('b.borrow', 'br')
|
->orderBy('b.id', 'ASC')
|
->getQuery()
|
->getArrayResult();
|
|
return array_map(static fn(array $row): array => [
|
'id' => $row['id'],
|
'title' => $row['title'],
|
'author' => $row['author'],
|
'placement' => $row['placement'],
|
'lastSeen' => $row['lastSeen']?->format(\DateTimeInterface::ATOM),
|
'borrowed' => $row['borrowedBook'] !== null,
|
'isbn' => $numbers[$row['id']][LabelType::ISBN->value] ?? [],
|
'issn' => $numbers[$row['id']][LabelType::ISSN->value] ?? [],
|
], $rows);
|
}
|
|
private static function containsPattern(string $value): string {
|
return '%' . strtr(trim($value), ['!' => '!!', '%' => '!%', '_' => '!_']) . '%';
|
}
|
}
|
