JobRepository.php

<?php

namespace SGalinski\SgJobs\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 TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\QueryInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface;
use TYPO3\CMS\Extbase\Persistence\QueryResultInterface as ExtbaseQueryResultInterface;
use TYPO3\CMS\Extbase\Persistence\Repository;

/**
 * Job Repository
 */
class JobRepository extends Repository {
	const TABLENAME = 'tx_sgjobs_domain_model_job';

	/**
	 * initializes the object
	 */
	public function initializeObject() {
		$querySettings = $this->createQuery()->getQuerySettings();
		$querySettings->setIgnoreEnableFields(TRUE);
		$querySettings->setEnableFieldsToBeIgnored(['disabled']);
		$this->setDefaultQuerySettings($querySettings);
	}

	/**
	 * Queries the job records based on filters (for the backend)
	 *
	 * @param int $recordPageId
	 * @param array $filters
	 * @param int $limit
	 * @param int $offset
	 * @return mixed
	 * @throws \InvalidArgumentException
	 */
	public function findJobs($recordPageId, array $filters = [], $limit = 0, $offset = 0) {
		// get all company ids
		$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
			'tx_sgjobs_domain_model_company'
		);

		$statement = $queryBuilder->select('a.uid')
			->from('tx_sgjobs_domain_model_job', 'a')
			->join(
				'a', 'tx_sgjobs_domain_model_company', 'b'
				, $queryBuilder->expr()->eq('a.company', 'b.uid')
			);

		if ($filters['locations'] !== '' && $filters['locations'] !== NULL) {

			$statement->andWhere(
				$queryBuilder->expr()->eq(
					'b.city', $queryBuilder->createNamedParameter($filters['locations'])
				)
			);
		}

		if ($filters['search'] !== '' && $filters['search'] !== NULL) {
			$statement->andWhere(
				$queryBuilder->expr()->eq(
					'a.description', $queryBuilder->createNamedParameter($filters['search'])
				)
			);
		}

		if ($filters['search'] !== '' && $filters['search'] !== NULL) {
			$statement->andWhere(
				$queryBuilder->expr()->eq(
					'a.title', $queryBuilder->createNamedParameter($filters['search'])
				)
			);
		}

		$statement->andWhere(
			$queryBuilder->expr()->eq(
				'b.pid', $queryBuilder->createNamedParameter($recordPageId)
			)
		);

		$companies = $statement->execute()->fetchAll();

		$result = [];
		$result[0] = '';
		foreach ($companies as $company) {
			$result[$company['uid']] = $company['uid'];
		}

		// when filters are set, but there are no companies found, return an empty result
		if (empty(!$filters) && empty($companies)) {
			return $companies;
		}

		// return the filtered jobs
		return $this->findByJobIds($recordPageId, $result, $limit, $offset);
	}

	/**
	 * Returns all areas filtered by page id
	 *
	 * @param int $pageUid
	 * @return mixed
	 * @throws \InvalidArgumentException
	 */
	public function getAllAreas($pageUid) {
		$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable(
			'tx_sgjobs_domain_model_job'
		);

		$queryBuilder
			->getRestrictions()
			->removeAll()
			->add(GeneralUtility::makeInstance(DeletedRestriction::class))
			->add(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
		$statement = $queryBuilder->select('area')->from('tx_sgjobs_domain_model_job');

		$statement->where(
			$queryBuilder->expr()->eq(
				'pid', $queryBuilder->createNamedParameter($pageUid)
			)
		);
		$result = $statement->execute()->fetchAll();
		$areaArray = [''];
		foreach ($result as $area) {
			$areaArray[$area['area']] = $area['area'];
		}

		return $areaArray;
	}

	/**
	 * Returns a job filtered by company and page id
	 *
	 * @param int $pageUid
	 * @param array $companyIds
	 * @param int $limit
	 * @param int $offset
	 * @return QueryResultInterface
	 */
	public function findByCompanyId($pageUid, array $companyIds = [], $limit = 0, $offset = 0
	): ExtbaseQueryResultInterface {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$querySettings = $query->getQuerySettings();
		$querySettings->setStoragePageIds([$pageUid]);
		$query->setQuerySettings($querySettings);

		$query->setOrderings(
			[
				'sorting' => QueryInterface::ORDER_ASCENDING,
			]
		);

		$constraints = [];

		if (isset($companyIds) && \is_array($companyIds) && \count($companyIds)) {
			$companyConstraints = [];
			foreach ($companyIds as $companyId) {
				if ($companyId) {
					$companyConstraints[] = $query->equals('company', $companyId);
				}
			}
			if (\count($companyConstraints)) {
				$constraints[] = $query->logicalOr($companyConstraints);
			}
		}

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

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

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

		return $query->execute();
	}

	/**
	 * Returns a job filtered by company and page id
	 *
	 * @param int $pageUid
	 * @param array $jobIds
	 * @param int $limit
	 * @param int $offset
	 * @return QueryResultInterface
	 */
	public function findByJobIds($pageUid, array $jobIds = [], $limit = 0, $offset = 0
	): ExtbaseQueryResultInterface {
		/** @var QueryInterface $query */
		$query = $this->createQuery();
		$querySettings = $query->getQuerySettings();
		$querySettings->setStoragePageIds([$pageUid]);
		$query->setQuerySettings($querySettings);

		$query->setOrderings(
			[
				'sorting' => QueryInterface::ORDER_ASCENDING,
			]
		);

		$constraints = [];

		if (isset($jobIds) && \is_array($jobIds) && \count($jobIds)) {
			$companyConstraints = [];
			foreach ($jobIds as $jobId) {
				if ($jobId) {
					$companyConstraints[] = $query->equals('uid', $jobId);
				}
			}
			if (\count($companyConstraints)) {
				$constraints[] = $query->logicalOr($companyConstraints);
			}
		}

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

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

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

		return $query->execute();
	}
}