BackendNewsUtility.php

<?php

namespace SGalinski\SgNews\Utility;

/***************************************************************
 *  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 SGalinski\SgNews\Domain\Model\Tag;
use SGalinski\SgNews\Domain\Repository\NewsRepository;
use SGalinski\SgNews\Domain\Repository\TagRepository;
use TYPO3\CMS\Backend\Utility\BackendUtility;
use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\QueryHelper;
use TYPO3\CMS\Core\Database\Query\Restriction\BackendWorkspaceRestriction;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Database\QueryGenerator;
use TYPO3\CMS\Core\Imaging\IconProvider\BitmapIconProvider;
use TYPO3\CMS\Core\Imaging\IconProvider\SvgIconProvider;
use TYPO3\CMS\Core\Imaging\IconRegistry;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Object\ObjectManager;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Utility\LocalizationUtility;

/**
 * Class Utility
 */
class BackendNewsUtility {

	/**
	 * @var int The category page doktype
	 */
	const CATEGORY_DOKTYPE = 117;

	/**
	 * @var int The news page doktype
	 */
	const NEWS_DOKTYPE = 116;

	/**
	 * Retrieves the next site root in the page hierarchy from the current page
	 *
	 * @param int $currentPid
	 * @return int
	 */
	public static function getRootUidByPageUid($currentPid): int {
		$rootLine = BackendUtility::BEgetRootLine((int) $currentPid);
		$siteRoot = ['uid' => 0];

		foreach ($rootLine as $page) {
			if ((int) $page['is_siteroot'] === 1) {
				$siteRoot = $page;
				break;
			}
		}

		return (int) $siteRoot['uid'];
	}

	/**
	 * Get an array of alternative pages for the BE Module view
	 *
	 * @return array
	 * @throws \InvalidArgumentException
	 */
	public static function getAlternativePageOptions(): array {
		$options = [];
		$andWhere = ' AND sys_language_uid IN (0,-1)';
		/** @var array $rootOptionRows */
		$rootOptionRows = self::getRecordsByField(
			'pages', 'is_siteroot', 1, $andWhere, '', 'sorting'
		);
		if ($rootOptionRows) {
			foreach ($rootOptionRows as $row) {
				$pageInfo = BackendUtility::readPageAccess($row['uid'], $GLOBALS['BE_USER']->getPagePermsClause(1));
				if ($pageInfo) {
					$options[] = self::getOptionPageInfo($pageInfo);
				}

				$categories = self::getCategoriesForSiteRoot((int) $row['uid']);
				/** @var int $categoryUid */
				foreach ($categories as $categoryUid => $categoryTitle) {
					if ((int) $pageInfo['uid'] !== $categoryUid) {
						$categoryPageInfo = BackendUtility::readPageAccess(
							$categoryUid, $GLOBALS['BE_USER']->getPagePermsClause(1)
						);
						if ($categoryPageInfo) {
							$options[] = self::getOptionPageInfo($categoryPageInfo);
						}
					}
				}
			}
		}

		return $options;
	}

	/**
	 * Get an array of alternative pages for the BE Module view
	 *
	 * @param array $pageInfo
	 * @return array
	 */
	private static function getOptionPageInfo(array $pageInfo = []): array {
		if (isset($pageInfo['uid']) && (int) $pageInfo['uid']) {
			$rootline = BackendUtility::BEgetRootLine($pageInfo['uid'], '', TRUE);
			ksort($rootline);
			$path = '/root';
			foreach ($rootline as $page) {
				$path .= '/p' . dechex($page['uid']);
			}

			$pageInfo['path'] = $path;
			$pageInfo['_thePathFull'] = substr($pageInfo['_thePathFull'], 1);
			$pageInfo['_thePathFull'] = substr($pageInfo['_thePathFull'], 0, -1);
		}

		return $pageInfo;
	}

	/**
	 * Get an array of all category uids => titles below the given site root id
	 *
	 * @param int $siteRootUid
	 * @return array
	 * @throws \InvalidArgumentException
	 */
	public static function getCategoriesForSiteRoot($siteRootUid): array {
		$siteRootUid = (int) $siteRootUid;
		// get all pageids below the given siteroot
		$queryGenerator = GeneralUtility::makeInstance(QueryGenerator::class);
		$childPids = $queryGenerator->getTreeList(
			$siteRootUid, PHP_INT_MAX, 0, $GLOBALS['BE_USER']->getPagePermsClause(1)
		);

		$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
		$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
		$result = $queryBuilder->select('uid', 'title')
			->from('pages')
			->where(
				$queryBuilder->expr()->andX(
					$queryBuilder->expr()->eq('doktype', $queryBuilder->createNamedParameter(self::CATEGORY_DOKTYPE, \PDO::PARAM_INT)),
					$queryBuilder->expr()->in('uid', $queryBuilder->createNamedParameter(explode(',', $childPids), Connection::PARAM_INT_ARRAY))
				)
			)
			->execute()->fetchAll();
		$categories = [];
		foreach ($result as $page) {
			$categoryPageInfo = BackendUtility::readPageAccess(
				(int) $page['uid'], $GLOBALS['BE_USER']->getPagePermsClause(1)
			);
			if ($categoryPageInfo) {
				$categories[(int) $page['uid']] = $page['title'];
			}
		}

		return $categories;
	}

	/**
	 * Get an array of all tags uids => titles below the given site root id
	 *
	 * @param int $pageUid
	 * @param int $languageUid
	 * @return array
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 * @throws \InvalidArgumentException
	 */
	public static function getTagsForPage($pageUid, $languageUid = 0): array {
		$temporaryTSFEInstance = FALSE;
		if (!isset($GLOBALS['TSFE'])) {
			$temporaryTSFEInstance = TRUE;
			$GLOBALS['TSFE'] = new \stdClass();
			$GLOBALS['TSFE']->gr_list = '';
		}

		$pageUid = (int) $pageUid;
		$languageUid = (int) $languageUid;
		$tags = [];
		$objectManager = GeneralUtility::makeInstance(ObjectManager::class);
		$tagRepository = $objectManager->get(TagRepository::class);
		$query = $tagRepository->createQuery();
		$querySettings = $query->getQuerySettings();
		$querySettings->setLanguageUid($languageUid);
		$querySettings->setLanguageOverlayMode(TRUE);
		$querySettings->setLanguageMode('content_fallback');
		$query->setQuerySettings($querySettings);
		if ($pageUid) {
			$rootline = BackendUtility::BEgetRootLine($pageUid, '', TRUE);
			$pageTS = BackendUtility::getPagesTSconfig($pageUid, $rootline);

			$tagsPid = 0;
			if (isset($pageTS['TCEFORM.']['pages.']['tx_sgnews_tags.']['PAGE_TSCONFIG_ID'])) {
				$tagsPid = (int) $pageTS['TCEFORM.']['pages.']['tx_sgnews_tags.']['PAGE_TSCONFIG_ID'];
			}

			if ($tagsPid) {
				$query->matching($query->equals('pid', $tagsPid));
			}
		}

		$query->setOrderings(['title' => QueryInterface::ORDER_ASCENDING]);
		$resultTags = $query->execute(TRUE);
		if ($temporaryTSFEInstance) {
			unset($GLOBALS['TSFE']);
		}

		foreach ($resultTags as $tag) {
			$tags[(int) $tag['uid']] = trim($tag['title']);
		}

		return $tags;
	}

	/**
	 * Get an array of all tags uids => titles for the specified news uid
	 *
	 * @param int $newsItemUid
	 * @param int $languageUid
	 * @return array
	 * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
	 * @throws \InvalidArgumentException
	 */
	public static function getTagsForNewsItem($newsItemUid, $languageUid = 0): array {
		$temporaryTSFEInstance = FALSE;
		if (!isset($GLOBALS['TSFE'])) {
			$temporaryTSFEInstance = TRUE;
			$GLOBALS['TSFE'] = new \stdClass();
			$GLOBALS['TSFE']->gr_list = '';
		}

		$newsItemUid = (int) $newsItemUid;
		$languageUid = (int) $languageUid;
		$tags = [];
		$objectManager = GeneralUtility::makeInstance(ObjectManager::class);
		$newsRepository = $objectManager->get(NewsRepository::class);
		$query = $newsRepository->createQuery();
		$querySettings = $query->getQuerySettings();
		$querySettings->setLanguageUid($languageUid);
		$querySettings->setLanguageOverlayMode(TRUE);
		$querySettings->setLanguageMode('content_fallback');
		$query->setQuerySettings($querySettings);
		if ($newsItemUid) {
			$query->matching($query->equals('uid', $newsItemUid));
		}

		/** @var News $newsItem */
		$newsItem = $query->execute()->getFirst();
		if ($temporaryTSFEInstance) {
			unset($GLOBALS['TSFE']);
		}

		if ($newsItem) {
			/** @var Tag $tag */
			foreach ($newsItem->getTags() as $tag) {
				$tags[(int) $tag->getUid()] = trim($tag->getTitle());
			}
		}

		return $tags;
	}

	/**
	 * Get all news for the given categories (all if filter is empty)
	 *
	 * @param int $rootPageUid
	 * @param array $filters
	 * @param int $languageUid
	 * @return array
	 * @throws \InvalidArgumentException
	 */
	public static function getNewsByFilters($rootPageUid = 0, array $filters = [], $languageUid = 0): array {
		$out = [];
		$rootPageUid = (int) $rootPageUid;
		$languageUid = (int) $languageUid;
		if (!$rootPageUid) {
			return $out;
		}

		$categories = [];
		if (!isset($filters['categories']) || !is_array($filters['categories']) || !count($filters['categories'])) {
			$rootCategories = self::getCategoriesForSiteRoot($rootPageUid);
			foreach ($rootCategories as $categoryUid => $categoryTitle) {
				$categories[] = (int) $categoryUid;
			}
		} else {
			/** @var array $filterCategories */
			$filterCategories = $filters['categories'];
			foreach ($filterCategories as $categoryUid) {
				$categoryPageInfo = BackendUtility::readPageAccess(
					(int) $categoryUid, $GLOBALS['BE_USER']->getPagePermsClause(1)
				);
				if ($categoryPageInfo) {
					$categories[] = (int) $categoryUid;
				}
			}
		}

		if (!count($categories)) {
			return $out;
		}

		$queryGenerator = GeneralUtility::makeInstance(QueryGenerator::class);
		$allowedUids = [];
		foreach ($categories as $categoryUid) {
			$allowedUidsTemp = GeneralUtility::intExplode(
				',', $queryGenerator->getTreeList(
				$categoryUid, 1, 0, $GLOBALS['BE_USER']->getPagePermsClause(1)
			), TRUE
			);
			$allowedUids = array_unique(array_merge($allowedUids, $allowedUidsTemp));
		}

		if (!count($allowedUids)) {
			return $out;
		}

		$connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
		$deletedRestriction = GeneralUtility::makeInstance(DeletedRestriction::class);
		$queryBuilder = $connectionPool->getQueryBuilderForTable('pages');
		$queryBuilder->getRestrictions()->removeAll()->add($deletedRestriction);
		$queryBuilder->select('p.uid',
				'p.pid',
				'p.hidden',
				'p.sorting',
				'p.doktype',
				'p.title'
			)->from('pages', 'p')
			->where(
				$queryBuilder->expr()->andX(
					$queryBuilder->expr()->in('p.uid', $queryBuilder->createNamedParameter($allowedUids, Connection::PARAM_INT_ARRAY)),
					$queryBuilder->expr()->eq('p.doktype', $queryBuilder->createNamedParameter(self::NEWS_DOKTYPE, \PDO::PARAM_INT))
				)
			)
			->groupBy('p.uid')
			->orderBy('p.sorting');
		if ($languageUid) {
			$queryBuilder->leftJoin('p', 'pages', 'translation',
				$queryBuilder->expr()->andX(
					$queryBuilder->expr()->eq('translation.l10n_parent', 'p.uid'),
					$queryBuilder->expr()->eq('translation.sys_language_uid', $queryBuilder->createNamedParameter($languageUid, \PDO::PARAM_INT))
				)
			);
		}

		if (isset($filters['tags']) && is_array($filters['tags']) && count($filters['tags'])) {
			$queryBuilder->innerJoin('p', 'sys_category_record_mm', 'tag',
				$queryBuilder->expr()->andX(
					$queryBuilder->expr()->eq('tag.tablenames', $queryBuilder->createNamedParameter('pages')),
					$queryBuilder->expr()->eq('tag.fieldname', $queryBuilder->createNamedParameter('tx_sgnews_tags')),
					$queryBuilder->expr()->eq('tag.uid_foreign', 'p.uid'),
					$queryBuilder->expr()->in('tag.uid_local', $queryBuilder->createNamedParameter(\array_unique($filters['tags']), Connection::PARAM_INT_ARRAY))
				)
			);
		}

		if (isset($filters['search']) && trim($filters['search'])) {
			$searchParameter = $queryBuilder->createNamedParameter('%' . trim($filters['search']) . '%');
			$expressions = [
				$queryBuilder->expr()->like('p.title', $searchParameter),
				$queryBuilder->expr()->like('p.description', $searchParameter),
				$queryBuilder->expr()->like('p.abstract', $searchParameter),
			];
			if ($languageUid) {
				$expressions[] = $queryBuilder->expr()->like('translation.title', $searchParameter);
				$expressions[] = $queryBuilder->expr()->like('translation.description', $searchParameter);
				$expressions[] = $queryBuilder->expr()->like('translation.abstract', $searchParameter);
			}

			$authorQueryBuilder = $connectionPool->getQueryBuilderForTable('tx_sgnews_domain_model_author');
			$authorQueryBuilder->getRestrictions()->removeAll()->add($deletedRestriction);
			$authorSearchParameter = $authorQueryBuilder->createNamedParameter('%' . trim($filters['search']) . '%');
			$authors = $authorQueryBuilder->select('author.uid')->from('tx_sgnews_domain_model_author', 'author')
				->where(
					$authorQueryBuilder->expr()->orX(
						$authorQueryBuilder->expr()->like('author.name', $authorSearchParameter),
						$authorQueryBuilder->expr()->like('author.email', $authorSearchParameter)
					)
				)->execute()->fetchAll();

			if (count($authors) > 0) {
				foreach (array_column($authors, 'uid') as $authorUid) {
					$expressions[] = $queryBuilder->expr()->inSet('p.tx_sgnews_news_author', $authorUid);
				}
			}

			$queryBuilder->andWhere(
				$queryBuilder->expr()->orX(
					...$expressions
				)
			);
		}

		return $queryBuilder->execute()->fetchAll();
	}

	/**
	 * Returns the available languages for the current BE user
	 *
	 * @param int $pageUid
	 * @return array
	 * @throws \InvalidArgumentException
	 */
	public static function getAvailableLanguages($pageUid = 0): array {
		$pageUid = (int) $pageUid;
		$rootline = BackendUtility::BEgetRootLine($pageUid, '', TRUE);
		$defaultLanguage = LocalizationUtility::translate('backend.language.default', 'SgNews');
		$pageTS = BackendUtility::getPagesTSconfig($pageUid, $rootline);
		if (isset($pageTS['mod.']['SHARED.']['defaultLanguageLabel'])) {
			$defaultLanguage = $pageTS['mod.']['SHARED.']['defaultLanguageLabel'] . ' (' . $defaultLanguage . ')';
		}

		$defaultLanguageFlag = 'empty-empty';
		if (isset($pageTS['mod.']['SHARED.']['defaultLanguageFlag'])) {
			$defaultLanguageFlag = 'flags-' . $pageTS['mod.']['SHARED.']['defaultLanguageFlag'];
		}

		$languages = [
			0 => ['title' => $defaultLanguage, 'flag' => $defaultLanguageFlag]
		];
		$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_language');
		$languageRows = $queryBuilder->select('uid', 'title', 'flag')
			->from('sys_language')
			->orderBy('sorting')
			->execute()->fetchAll();
		if ($languageRows) {
			/** @var BackendUserAuthentication $backendUser */
			$backendUser = $GLOBALS['BE_USER'];
			foreach ($languageRows as $languageRow) {
				if ($backendUser->checkLanguageAccess($languageRow['uid'])) {
					$languages[(int) $languageRow['uid']] = [
						'title' => $languageRow['title'],
						'flag' => 'flags-' . $languageRow['flag'],
					];
				}
			}
		}

		return $languages;
	}

	/**
	 * Returns records from table, $theTable, where a field ($theField) equals the value, $theValue
	 * The records are returned in an array
	 * If no records were selected, the function returns nothing
	 *
	 * @param string $theTable Table name present in $GLOBALS['TCA']
	 * @param string $theField Field to select on
	 * @param string $theValue Value that $theField must match
	 * @param string $whereClause Optional additional WHERE clauses put in the end of the query. DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
	 * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
	 * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
	 * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
	 * @param bool $useDeleteClause Use the deleteClause to check if a record is deleted (default TRUE)
	 * @return mixed Multidimensional array with selected records (if any is selected)
	 */
	public static function getRecordsByField(
		$theTable, $theField, $theValue, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '',
		$useDeleteClause = TRUE
	) {
		if (is_array($GLOBALS['TCA'][$theTable])) {
			$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($theTable);
			$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(BackendWorkspaceRestriction::class));;
			if ($useDeleteClause) {
				$queryBuilder->getRestrictions()
					->add(GeneralUtility::makeInstance(DeletedRestriction::class));
			}

			$queryBuilder->select('*')
				->from($theTable)
				->where(
					$queryBuilder->expr()->eq($theField, $queryBuilder->createNamedParameter($theValue))
				);
			// additional where
			if ($whereClause) {
				$queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($whereClause));
			}

			// group by
			if ($groupBy !== '') {
				$queryBuilder->groupBy(QueryHelper::parseGroupBy($groupBy));
			}

			// order by
			if ($orderBy !== '') {
				foreach (QueryHelper::parseOrderBy($orderBy) as $orderPair) {
					list($fieldName, $order) = $orderPair;
					$queryBuilder->addOrderBy($fieldName, $order);
				}
			}

			// limit
			if ($limit !== '') {
				if (strpos($limit, ',')) {
					$limitOffsetAndMax = GeneralUtility::intExplode(',', $limit);
					$queryBuilder->setFirstResult((int)$limitOffsetAndMax[0]);
					$queryBuilder->setMaxResults((int)$limitOffsetAndMax[1]);
				} else {
					$queryBuilder->setMaxResults((int)$limit);
				}
			}

			return $queryBuilder->execute()->fetchAll();
		}

		return NULL;
	}

	/**
	 * Register the extension icons
	 * For use in ext_localconf.php
	 */
	public static function registerIcons() {
		$iconRegistry = GeneralUtility::makeInstance(IconRegistry::class);
		$iconRegistry->registerIcon(
			'actions-document-open-white',
			SvgIconProvider::class,
			['source' => 'EXT:sg_news/Resources/Public/Icons/actions-document-open-white.svg']
		);
		$iconRegistry->registerIcon(
			'sg_news-module',
			SvgIconProvider::class,
			['source' => 'EXT:sg_news/Resources/Public/Icons/module-sgnews.svg']
		);
		$iconRegistry->registerIcon(
			'sg_news-module-transparent',
			SvgIconProvider::class,
			['source' => 'EXT:sg_news/Resources/Public/Icons/module-sgnews-transparent.svg']
		);
		$iconRegistry->registerIcon(
			'tcarecords-pages-' . self::CATEGORY_DOKTYPE,
			BitmapIconProvider::class,
			['source' => 'EXT:sg_news/Resources/Public/Images/Category.png']
		);
		$iconRegistry->registerIcon(
			'tcarecords-pages-' . self::NEWS_DOKTYPE,
			BitmapIconProvider::class,
			['source' => 'EXT:sg_news/Resources/Public/Images/News.png']
		);
	}
}