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\Extbase\Persistence\Generic\QueryResult;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;

/**
 * News Repository
 */
class NewsRepository extends AbstractRepository {
	/**
	 * 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
	 * @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
	) {
		$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 ($sortBy === 'date') {
			$query->setOrderings(
				[
					'lastUpdated' => QueryInterface::ORDER_DESCENDING,
					'crdate' => QueryInterface::ORDER_DESCENDING,
				]
			);
		} else {
			$query->setOrderings(
				[
					'sorting' => QueryInterface::ORDER_ASCENDING,
					'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
	) {
		$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 $query->count();
	}

	/**
	 * 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
	) {
		return $this->getQueryForLastUpdatedOrHighlightedNewsByCategories(
			$limit, $onlyHighlighted, $categoryIds, $offset, $hideNeverHighlightedNews, $sortBy, $tagIds,
			$startTime, $endTime
		)->execute();
	}

	/**
	 * Returns the count of all possible last news.
	 *
	 * @param bool $onlyHighlighted
	 * @param array $categoryIds NULL, if the category filter isn't applied, otherwise an array with the categories uid.
	 * @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 int
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 */
	public function getCountOfLastUpdatedOrHighlightedNewsByCategories(
		$onlyHighlighted = FALSE, array $categoryIds = NULL, $hideNeverHighlightedNews = FALSE, $sortBy = 'date',
		array $tagIds = NULL, $startTime = 0, $endTime = 0
	) {
		return $this->getQueryForLastUpdatedOrHighlightedNewsByCategories(
			0, $onlyHighlighted, $categoryIds, 0, $hideNeverHighlightedNews, $sortBy, $tagIds, $startTime, $endTime
		)->count();
	}

	/**
	 * 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
	) {
		$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
	 */
	public function findNextNewsEntryFromCurrentNews(News $news, $sortBy = 'date') {
		$query = $this->createQuery();

		if ($sortBy === 'date') {
			$orderBy = 'ORDER BY lastUpdated DESC, crdate DESC';
		} else {
			$orderBy = 'ORDER BY sorting ASC, lastUpdated DESC, crdate DESC';
		}

		$statement = 'SELECT * FROM pages WHERE uid =
			(SELECT MIN(uid) FROM pages WHERE pid = ' . $news->getPid() . ' AND uid > ' . $news->getUid() .
			$this->getEnableFieldsStatement('pages') . ' ' . $orderBy . ')' .
			$this->getEnableFieldsStatement('pages');

		/** @noinspection PhpUndefinedMethodInspection */
		return $query->statement($statement)->execute();
	}

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

		if ($sortBy === 'date') {
			$orderBy = 'ORDER BY lastUpdated DESC, crdate DESC';
		} else {
			$orderBy = 'ORDER BY sorting ASC, lastUpdated DESC, crdate DESC';
		}

		$statement = 'SELECT * FROM pages WHERE uid =
			(SELECT MAX(uid) FROM pages WHERE pid = ' . $news->getPid() . ' AND uid < ' . $news->getUid() .
			$this->getEnableFieldsStatement('pages') . ' ' . $orderBy . ')' .
			$this->getEnableFieldsStatement('pages');

		/** @noinspection PhpUndefinedMethodInspection */
		return $query->statement($statement)->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|array
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 * @api
	 */
	public function findAll($startTime = 0, $endTime = 0)
	{
		$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)
	{
		$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->count();
	}
}