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\DatabaseConnection;
use TYPO3\CMS\Core\Database\QueryGenerator;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Core\Utility\VersionNumberUtility;
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 = [];
		/** @var array $rootOptionRows */
		$rootOptionRows = self::getRecordsByField(
			'pages', 'is_siteroot', 1, ' AND sys_language_uid IN (0,-1)', '', '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)
		);

		// if doktype = 117 (category) then get the category name (page title)
		/** @var DatabaseConnection $databaseConnection */
		$databaseConnection = $GLOBALS['TYPO3_DB'];

		$where = 'deleted = 0 AND doktype = ' . self::CATEGORY_DOKTYPE . ' AND uid in (' . $childPids . ')';
		$result = $databaseConnection->exec_SELECTgetRows('uid, title', 'pages', $where);
		$categories = [];
		/** @var array $result */
		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;
		}

		list($select, $tables, $where) = self::getNewsQueryParts($allowedUids, $filters, $languageUid);
		if ($tables === '') {
			return $out;
		}

		/** @var DatabaseConnection $databaseConnection */
		$databaseConnection = $GLOBALS['TYPO3_DB'];
		$result = $databaseConnection->exec_SELECTquery($select, $tables, $where, '`pages`.`uid`', '`pages`.`sorting`');
		while ($row = $result->fetch_assoc()) {
			$out[] = $row;
		}
		$databaseConnection->sql_free_result($result);
		return $out;
	}

	/**
	 * Creates news query parts
	 *
	 * @param array $allowedUids
	 * @param array $filters
	 * @param int $languageUid
	 * @return array
	 */
	private static function getNewsQueryParts(array $allowedUids, array $filters = [], $languageUid = 0): array {
		$out = [0 => '
		`pages`.`uid` AS uid,
		`pages`.`pid` AS pid,
		`pages`.`hidden` AS hidden,
		`pages`.`sorting` AS sorting,
		`pages`.`doktype` AS doktype,
		`pages`.`title` AS title
		', 1 => '', 2 => ''];
		$out[1] = '`pages`';
		$out[2] = '`pages`.`uid` IN(' . implode(',', $allowedUids) . ') ' . BackendUtility::deleteClause('pages') .
			' AND `pages`.`doktype` = ' . self::NEWS_DOKTYPE;
		if ($languageUid) {
			$out[0] .= ', `translation`.`title` AS translation_title';
			$out[0] .= ', `translation`.`uid` AS translation_uid';
			$out[1] .= ' LEFT JOIN `pages_language_overlay` AS `translation` ON `translation`.`pid` = `pages`.`uid` ' .
				BackendUtility::deleteClause('pages_language_overlay', 'translation') .
				' AND `translation`.`sys_language_uid` = ' . $languageUid;
		}
		if (isset($filters['tags']) && is_array($filters['tags']) && count($filters['tags'])) {
			$tagUids = [];
			/** @var array $filterTags */
			$filterTags = $filters['tags'];
			foreach ($filterTags as $tagUid) {
				if ((int) $tagUid && !in_array((int) $tagUid, $tagUids, TRUE)) {
					$tagUids[] = (int) $tagUid;
				}
			}
			if (count($tagUids)) {
				$out[1] .= ' INNER JOIN `sys_category_record_mm` AS `tag` ON `tag`.`tablenames` = \'pages\'' .
					' AND `tag`.`fieldname` = \'tx_sgnews_tags\' AND `tag`.`uid_foreign` = `pages`.`uid`' .
					' AND `tag`.`uid_local` IN (' . implode(',', $filters['tags']) . ')';
			}
		}
		if (isset($filters['search']) && trim($filters['search'])) {
			$out[2] .= self::getNewsSearchClause(trim($filters['search']), $languageUid);
		}

		return $out;
	}

	/**
	 * Creates constraints of query for searching news by search-word
	 *
	 * @param string $searchString
	 * @param int $languageUid
	 * @return string
	 */
	private static function getNewsSearchClause($searchString = '', $languageUid = 0): string {
		$out = '';
		$searchString = strtolower(trim($searchString));
		$languageUid = (int) $languageUid;
		if (!$searchString) {
			return $out;
		}
		$out = ' AND (';
		/** @var DatabaseConnection $databaseConnection */
		$databaseConnection = $GLOBALS['TYPO3_DB'];
		$likeString = 'LIKE \'%' . $databaseConnection->escapeStrForLike($searchString, 'pages') . '%\'';
		$constraints = [];
		$pageFields = [
			'title' => TRUE,
			'description' => TRUE,
			'author' => TRUE,
			'abstract' => TRUE,
		];
		foreach ($pageFields as $fieldName => $isCommonField) {
			if ($isCommonField) {
				$constraints[] = 'LOWER(`pages`.`' . $fieldName . '`) ' . $likeString;
			}
		}
		if (!$languageUid) {
			foreach ($pageFields as $fieldName => $isCommonField) {
				if (!$isCommonField) {
					$constraints[] = 'LOWER(`pages`.`' . $fieldName . '`) ' . $likeString;
				}
			}
		} else {
			foreach ($pageFields as $fieldName => $isCommonField) {
				if ($isCommonField) {
					$constraints[] = 'LOWER(`translation`.`' . $fieldName . '`) ' . $likeString;
				}
			}
		}
		$out .= implode(' OR ', $constraints) . ')';
		return $out;
	}

	/**
	 * 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]
		];
		/** @var DatabaseConnection $databaseConnection */
		$databaseConnection = $GLOBALS['TYPO3_DB'];
		$orderBy = '';
		if (VersionNumberUtility::convertVersionNumberToInteger(TYPO3_version) >= 8000000) {
			$orderBy = 'sorting';
		}
		$languageRows = $databaseConnection->exec_SELECTgetRows(
			'uid, title, flag', 'sys_language', 'hidden = 0', '', $orderBy
		);
		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])) {
			/** @var DatabaseConnection $databaseConnection */
			$databaseConnection = $GLOBALS['TYPO3_DB'];
			$result = $databaseConnection->exec_SELECTquery(
				'*',
				$theTable,
				$theField . '=' . $databaseConnection->fullQuoteStr($theValue, $theTable) .
				($useDeleteClause ? BackendUtility::deleteClause($theTable) . ' ' : '') .
				BackendUtility::versioningPlaceholderClause($theTable) . ' ' .
				$whereClause,
				$groupBy,
				$orderBy,
				$limit
			);
			$rows = [];
			while ($row = $databaseConnection->sql_fetch_assoc($result)) {
				$rows[] = $row;
			}
			$databaseConnection->sql_free_result($result);
			if (!empty($rows)) {
				return $rows;
			}
		}
		return NULL;
	}
}