NewsRepository.php

<?php

namespace SGalinski\SgNews\Domain\Repository;

/***************************************************************
 *  Copyright notice
 *
 *  (c) sgalinski Internet Services (https://www.sgalinski.de)
 *
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

use SGalinski\SgNews\Domain\Model\News;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\Generic\QueryResult;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;

/**
 * News Repository
 */
class NewsRepository extends AbstractRepository {
	/**
	 * Finds all news by the given authors.
	 *
	 * @param array $authorIds
	 * @return array Contains only TYPO3\CMS\Extbase\Persistence\Generic\QueryResult items
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function findAllByNewsAuthor(array $authorIds): ?array {
		if (count($authorIds) <= 0) {
			return NULL;
		}

		$result = [];
		foreach ($authorIds as $authorId) {
			$result[] = $this->findNewsByAuthor($authorId);
		}

		return $result;
	}

	/**
	 * Find all news by the given author
	 *
	 * @param int $authorId
	 * @return QueryResultInterface|null
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function findNewsByAuthor(int $authorId): ?QueryResultInterface {
		$query = $this->createQuery();
		$query->matching($query->contains('newsAuthor', $authorId));
		return $query->execute();
	}

	/**
	 * Method returns all news by category id sorted by the field lastUpdated.
	 *
	 * @param array $categoryIds NULL, if the category filter isn't applied, otherwise an array with the categories uid.
	 * @param int $limit
	 * @param int $offset
	 * @param string $sortBy date or positionInTree
	 * @param array $tagIds NULL, if the tag filter isn't applied, otherwise an array with the tag uids.
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @param string $sortDirection either DESC or ASC
	 * @return QueryResultInterface
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function findAllSortedNewsByCategories(
		array $categoryIds = NULL,
		$limit = 0,
		$offset = 0,
		$sortBy = 'date',
		array $tagIds = NULL,
		$startTime = 0,
		$endTime = 0,
		$sortDirection = 'DESC'
	): QueryResultInterface {
		$query = $this->createQuery();

		$constraints = [];
		if ($categoryIds !== NULL && \is_array($categoryIds) && \count($categoryIds)) {
			$constraints[] = $query->in('pid', $categoryIds);
		}

		if ($tagIds !== NULL && \is_array($tagIds)) {
			$tagConstraints = [];
			foreach ($tagIds as $tagId) {
				if ($tagId) {
					$tagConstraints[] = $query->contains('tags', $tagId);
				}
			}
			if (\count($tagConstraints) > 1) {
				$constraints[] = $query->logicalOr($tagConstraints);
			} elseif (\count($tagConstraints)) {
				$constraints[] = $tagConstraints[0];
			}
		}

		$startTime = (int) $startTime;
		if ($startTime) {
			$startTimeObject = \DateTime::createFromFormat('U', (string) $startTime);
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $startTimeObject);
		}

		$endTime = (int) $endTime;
		if ($endTime) {
			$endTimeObject = \DateTime::createFromFormat('U', (string) $endTime);
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $endTimeObject);
		}

		if (\count($constraints)) {
			$query->matching($query->logicalAnd($constraints));
		}

		if ($limit > 0) {
			$query->setLimit($limit);
		}

		if ($offset > 0) {
			$query->setOffset($offset);
		}

		if ($sortDirection === 'ASC') {
			$order = QueryInterface::ORDER_ASCENDING;
		} else {
			$order = QueryInterface::ORDER_DESCENDING;
		}

		if ($sortBy === 'date') {
			$query->setOrderings(
				[
					'lastUpdated' => $order,
					'crdate' => $order,
				]
			);
		} else {
			$query->setOrderings(
				[
					'sorting' => $order,
					'lastUpdated' => QueryInterface::ORDER_DESCENDING,
					'crdate' => QueryInterface::ORDER_DESCENDING,
				]
			);
		}

		return $query->execute();
	}

	/**
	 * Returns the count of all news within the given category ids.
	 *
	 * @param array $categoryIds NULL, if the category filter isn't applied, otherwise an array with the categories uid.
	 * @param array $tagIds NULL, if the tag filter isn't applied, otherwise an array with the tag uids.
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @return int
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function newsCountByCategories(
		array $categoryIds = NULL,
		array $tagIds = NULL,
		$startTime = 0,
		$endTime = 0
	): int {
		$query = $this->createQuery();

		$constraints = [];
		if ($categoryIds !== NULL && is_array($categoryIds) && count($categoryIds)) {
			$constraints[] = $query->in('pid', $categoryIds);
		}

		if ($tagIds !== NULL && is_array($tagIds)) {
			$tagConstraints = [];
			foreach ($tagIds as $tagId) {
				if ($tagId) {
					$tagConstraints[] = $query->contains('tags', $tagId);
				}
			}
			$tagContraintAmount = count($tagConstraints);
			if ($tagContraintAmount > 1) {
				$constraints[] = $query->logicalOr($tagConstraints);
			} elseif ($tagContraintAmount) {
				$constraints[] = $tagConstraints[0];
			}
		}

		$startTime = (int) $startTime;
		if ($startTime) {
			$startTimeObject = \DateTime::createFromFormat('U', (string) $startTime);
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $startTimeObject);
		}

		$endTime = (int) $endTime;
		if ($endTime) {
			$endTimeObject = \DateTime::createFromFormat('U', (string) $endTime);
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $endTimeObject);
		}

		if (count($constraints) > 1) {
			$query->matching($query->logicalAnd($constraints));
		} elseif (count($constraints) === 1) {
			$query->matching($constraints[0]);
		}
		return $this->getCount($query);
	}

	/**
	 * Method returns the last updated news by category id which is highlighted.
	 *
	 * @param int $limit
	 * @param bool $onlyHighlighted
	 * @param array $categoryIds NULL, if the category filter isn't applied, otherwise an array with the categories uid.
	 * @param int $offset
	 * @param bool $hideNeverHighlightedNews
	 * @param string $sortBy date or positionInTree
	 * @param array $tagIds NULL, if the tag filter isn't applied, otherwise an array with the tag uids.
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @return QueryResultInterface
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function findLastUpdatedOrHighlightedNewsByCategories(
		$limit = 1,
		$onlyHighlighted = FALSE,
		array $categoryIds = NULL,
		$offset = 0,
		$hideNeverHighlightedNews = FALSE,
		$sortBy = 'date',
		array $tagIds = NULL,
		$startTime = 0,
		$endTime = 0
	): QueryResultInterface {
		return $this->getQueryForLastUpdatedOrHighlightedNewsByCategories(
			$limit,
			$onlyHighlighted,
			$categoryIds,
			$offset,
			$hideNeverHighlightedNews,
			$sortBy,
			$tagIds,
			$startTime,
			$endTime
		)->execute();
	}

	/**
	 * Returns the query object of the LastUpdatedOrHighlightedNewsByCategories.
	 *
	 * @param int $limit
	 * @param bool $onlyHighlighted
	 * @param array $categoryIds NULL, if the category filter isn't applied, otherwise an array with the categories uid.
	 * @param int $offset
	 * @param bool $hideNeverHighlightedNews
	 * @param string $sortBy date or positionInTree
	 * @param array $tagIds NULL, if the tag filter isn't applied, otherwise an array with the tag uids.
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @return QueryInterface
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	protected function getQueryForLastUpdatedOrHighlightedNewsByCategories(
		$limit = 1,
		$onlyHighlighted = FALSE,
		array $categoryIds = NULL,
		$offset = 0,
		$hideNeverHighlightedNews = FALSE,
		$sortBy = 'date',
		array $tagIds = NULL,
		$startTime = 0,
		$endTime = 0
	): QueryInterface {
		$query = $this->createQuery();
		$constraints = NULL;
		if ($categoryIds !== NULL && is_array($categoryIds) && count($categoryIds)) {
			$constraints[] = $query->in('pid', $categoryIds);
		}

		if ($tagIds !== NULL && is_array($tagIds)) {
			$tagConstraints = [];
			foreach ($tagIds as $tagId) {
				if ($tagId) {
					$tagConstraints[] = $query->contains('tags', $tagId);
				}
			}
			if (count($tagConstraints) > 1) {
				$constraints[] = $query->logicalOr($tagConstraints);
			} elseif (count($tagConstraints)) {
				$constraints[] = $tagConstraints[0];
			}
		}

		if ($onlyHighlighted) {
			$constraints[] = $query->equals('tx_sgnews_highlighted', 1);
		}

		if ($hideNeverHighlightedNews) {
			$constraints[] = $query->equals('tx_sgnews_never_highlighted', 0);
		}

		$startTime = (int) $startTime;
		if ($startTime) {
			$startTimeObject = \DateTime::createFromFormat('U', (string) $startTime);
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $startTimeObject);
		}

		$endTime = (int) $endTime;
		if ($endTime) {
			$endTimeObject = \DateTime::createFromFormat('U', (string) $endTime);
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $endTimeObject);
		}

		if ($sortBy === 'date') {
			$query->setOrderings(
				[
					'tx_sgnews_highlighted' => QueryInterface::ORDER_DESCENDING,
					'lastUpdated' => QueryInterface::ORDER_DESCENDING,
					'crdate' => QueryInterface::ORDER_DESCENDING,
				]
			);
		} else {
			$query->setOrderings(
				[
					'tx_sgnews_highlighted' => QueryInterface::ORDER_DESCENDING,
					'sorting' => QueryInterface::ORDER_ASCENDING,
					'lastUpdated' => QueryInterface::ORDER_DESCENDING,
					'crdate' => QueryInterface::ORDER_DESCENDING,
				]
			);
		}

		if ($constraints !== NULL) {
			$constraints = $query->logicalAnd($constraints);
		}

		if ($limit > 0) {
			$query->setLimit($limit);
		}

		if ($offset > 0) {
			$query->setOffset($offset);
		}

		return $query->matching($constraints);
	}

//	/**
//	 * Method returns the random News of the given amount.
//	 *
//	 * @param int $limit
//	 * @param News $excludeNews
//	 * @return QueryResult
//	 */
//	public function findRandomNews($limit = 0, News $excludeNews = NULL) {
//		$query = $this->createQuery();
//		$excludeClause = ($excludeNews ? 'AND uid != ' . $excludeNews->getUid() : '');
//		$statement = 'SELECT * FROM pages WHERE doktype = 116 ' . $excludeClause .
//			$this->getEnableFieldsStatement('pages') . ' ORDER BY RAND()';
//
//		if ($limit > 0) {
//			$statement .= ' LIMIT ' . $limit;
//		}
//
//		/** @noinspection PhpUndefinedMethodInspection */
//		return $query->statement($statement)->execute();
//	}

	/**
	 * Method returns the next News of the given news.
	 *
	 * @param News $news
	 * @param string $sortBy date or positionInTree
	 * @return QueryResult
	 * @throws \InvalidArgumentException
	 */
	public function findNextNewsEntryFromCurrentNews(News $news, $sortBy = 'date'): QueryResultInterface {
		$query = $this->createQuery();

		$query->getQuerySettings()->setRespectStoragePage(TRUE);
		$query->getQuerySettings()->setStoragePageIds([$news->getPid()]);
		if ($sortBy === 'date') {
			$query->setOrderings([
				'lastUpdated' => QueryInterface::ORDER_DESCENDING,
				'crdate' => QueryInterface::ORDER_DESCENDING
			]);
			return $query->matching(
				$query->lessThan('lastUpdated', $news->getLastUpdated())
			)->execute();
		}

		$query->setOrderings([
			'sorting' => QueryInterface::ORDER_ASCENDING,
			'lastUpdated' => QueryInterface::ORDER_DESCENDING,
			'crdate' => QueryInterface::ORDER_DESCENDING
		]);
		return $query->matching(
			$query->greaterThan('sorting', $news->getSorting())
		)->execute();
	}

	/**
	 * Method returns the previous News of the given news.
	 *
	 * @param News $news
	 * @param string $sortBy date or positionInTree
	 * @return QueryResult
	 * @throws \InvalidArgumentException
	 */
	public function findPreviousNewsEntryFromCurrentNews(News $news, $sortBy = 'date'): QueryResultInterface {
		$query = $this->createQuery();

		$query->getQuerySettings()->setRespectStoragePage(TRUE);
		$query->getQuerySettings()->setStoragePageIds([$news->getPid()]);
		if ($sortBy === 'date') {
			$query->setOrderings([
				'lastUpdated' => QueryInterface::ORDER_ASCENDING,
				'crdate' => QueryInterface::ORDER_ASCENDING
			]);
			return $query->matching(
				$query->greaterThan('lastUpdated', $news->getLastUpdated())
			)->execute();
		}

		$query->setOrderings([
			'sorting' => QueryInterface::ORDER_DESCENDING,
			'lastUpdated' => QueryInterface::ORDER_ASCENDING,
			'crdate' => QueryInterface::ORDER_ASCENDING
		]);
		return $query->matching(
			$query->lessThan('sorting', $news->getSorting())
		)->execute();
	}

	/**
	 * Returns all news.
	 *
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @return QueryResultInterface
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 * @api
	 */
	public function findAll($startTime = 0, $endTime = 0): QueryResultInterface {
		$query = $this->createQuery();
		$constraints = [];

		$startTime = (int) $startTime;
		if ($startTime) {
			$startTimeObject = \DateTime::createFromFormat('U', (string) $startTime);
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $startTimeObject);
		}

		$endTime = (int) $endTime;
		if ($endTime) {
			$endTimeObject = \DateTime::createFromFormat('U', (string) $endTime);
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $endTimeObject);
		}

		if (count($constraints) > 1) {
			$query->matching($query->logicalAnd($constraints));
		} elseif (count($constraints) === 1) {
			$query->matching($constraints[0]);
		}

		return $query->execute();
	}

	/**
	 * Returns the total number of news.
	 *
	 * @param int $startTime unix timestamp of the lower limit of the news results date
	 * @param int $endTime unix timestamp of the upper limit of the news results date
	 * @return int The news count
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 * @api
	 */
	public function countAll($startTime = 0, $endTime = 0): int {
		$query = $this->createQuery();
		$constraints = [];

		$startTime = (int) $startTime;
		if ($startTime) {
			$startTimeObject = \DateTime::createFromFormat('U', (string) $startTime);
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $startTimeObject);
		}

		$endTime = (int) $endTime;
		if ($endTime) {
			$endTimeObject = \DateTime::createFromFormat('U', (string) $endTime);
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $endTimeObject);
		}

		if (count($constraints) > 1) {
			$query->matching($query->logicalAnd($constraints));
		} elseif (count($constraints) === 1) {
			$query->matching($constraints[0]);
		}

		return $this->getCount($query);
	}

	/**
	 * Find news record by uid, but with ignoring enable fields
	 *
	 * @param int $uid
	 * @return object
	 */
	public function findByUidIgnoreEnableFields($uid) {
		$query = $this->createQuery();
		/** @var \TYPO3\CMS\Extbase\Persistence\Generic\Typo3QuerySettings $querySettings */
		$querySettings = $query->getQuerySettings();
		$querySettings->setIgnoreEnableFields(TRUE);
		$querySettings->setRespectStoragePage(FALSE);
		$query->setQuerySettings($querySettings);

		$query->matching($query->equals('uid', $uid));
		return $query->execute()->getFirst();
	}

	/**
	 * This method finds news related by Tag or Category to the given news record
	 *
	 * @param News $news The news to find related news to
	 * @param int $limit Limit the amount of related news
	 * @return QueryResultInterface
	 * @throws \Doctrine\DBAL\Driver\Exception
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function findRelated(News $news, int $limit = 0): QueryResultInterface {
		$connection = $this->getConnection();
		$qb = $connection->createQueryBuilder();
		// We need to build the constraint for the tags/categories
		$constraints = [];
		$tags = $news->getTags();
		if ($tags->count() > 0) {
			$qb->leftJoin('pages', 'sys_category_record_mm', 'tags', 'pages.uid=tags.uid_foreign');
			$tagConstraints = [];
			foreach ($tags as $tag) {
				$tagConstraints[] = $qb->expr()->eq('tags.uid_local', $tag->getUid());
			}
			$constraints[] = $qb->expr()->eq('tags.tablenames', $qb->createNamedParameter('pages'));
			$constraints[] = $qb->expr()->eq('tags.fieldname', $qb->createNamedParameter('tx_sgnews_tags'));
			$constraints[] = $qb->expr()->orX(...$tagConstraints);
		} else {
			$constraints[] = $qb->expr()->eq('pid', $news->getPid());
		}

		if ($limit > 0) {
			$qb->setMaxResults($limit);
		}

		// here we fetch the lastUpdated of the $limit amount of news with newer lastUpdated dates
		$result = $qb->select('lastUpdated')
			->from('pages', 'pages')
			->where(
				$qb->expr()->eq('doktype', $qb->createNamedParameter(News::DOK_TYPE_NEWS, Connection::PARAM_INT)),
				$qb->expr()->gte('lastUpdated', $news->getLastUpdated()->getTimestamp())
			)->andWhere(...$constraints)
			->orderBy('lastUpdated', 'desc')
			->execute();
		$newest = $result->fetchOne();

		// Here we fetch the lastUpdated of the $limit amount of news with older lastUpdated dates
		$result = $qb->where(
			$qb->expr()->eq('doktype', $qb->createNamedParameter(News::DOK_TYPE_NEWS, Connection::PARAM_INT)),
			$qb->expr()->lte('lastUpdated', $news->getLastUpdated()->getTimestamp())
		)->andWhere(...$constraints)
			->orderBy('lastUpdated', 'asc')
			->execute();
		$oldest = $result->fetchOne();

		$query = $this->createQuery();
		$query->getQuerySettings()->setRespectStoragePage(FALSE);
		$query->setOrderings([
			'lastUpdated' => QueryInterface::ORDER_DESCENDING
		]);
		$constraints = [
			$query->logicalNot(
				$query->equals('uid', $news->getUid())
			)
		];
		if ($newest) {
			$constraints[] = $query->lessThanOrEqual('lastUpdated', $newest);
		}
		if ($oldest) {
			$constraints[] = $query->greaterThanOrEqual('lastUpdated', $oldest);
		}

		// Now we fetch the $limit amount of news via extbase query and limit them to the newest and oldest date
		// remember, we fetched the oldest and newest date from the $limit amount of news newer and older then
		// the given news. If we limit the result of the following query to $limit, we get $limit amount of news
		// "around" the given news, where newer news are preferred due to the ordering.
		$tags = $news->getTags();
		if ($tags->count() > 0) {
			$tagConstraints = [];
			foreach ($tags as $tag) {
				$tagConstraints[] = $query->contains('tags', $tag);
			}

			$constraints[] = $query->logicalOr($tagConstraints);
		} else {
			$constraints[] = $query->equals('pid', $news->getPid());
		}

		$query->matching($query->logicalAnd($constraints));
		if ($limit > 0) {
			$query->setLimit($limit);
		}

		return $query->execute();
	}

	/**
	 * Sums up the number of likes per news entry for all existing translations.
	 *
	 * @param int $uid
	 * @return int The like count
	 * @throws \Doctrine\DBAL\DBALException
	 * @throws \Doctrine\DBAL\Driver\Exception
	 */
	public function sumLikes(int $uid): int {
		$connection = $this->getConnection();
		$qb = $connection->createQueryBuilder();
		$constraints = [];

		$constraints[] = $qb->expr()->eq('uid', $qb->createNamedParameter($uid, Connection::PARAM_INT));
		$constraints[] = $qb->expr()->eq('l10n_source', $qb->createNamedParameter($uid, Connection::PARAM_INT));

		 $qb->addSelectLiteral($qb->expr()->sum('tx_sgnews_likes', 'sum_likes'))
			 ->from('pages', 'pages')
			 ->where($qb->expr()->or(...$constraints));

		return $qb->execute()->fetchOne();
	}

	protected function getConnection(): Connection {
		return GeneralUtility::makeInstance(ConnectionPool::class)
			->getConnectionForTable('pages');
	}
}