pagemanager/Classes/Domain/Repository/ActivePageRepository.php

<?php

declare(strict_types=1);

namespace Meramo\Pagemanager\Domain\Repository;

use TYPO3\CMS\Extbase\Persistence\Repository;
use \TYPO3\CMS\Extbase\Persistence\Generic\QueryResult;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * This file is part of the "ABI2021 Page Manager" Extension for TYPO3 CMS.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * (c) 2023 Meramo Developer <development@meramo.de>, Meramo Verlag GmbH
 */

/**
 * The repository for ActivePages
 */
class ActivePageRepository extends Repository
{
  /**
   * Queries the pages and be_users tables and returns the Data as an array
   * 
   * @return QueryResult|array
   */
  public  function findActivePagesAndPageCreatetors()
  {
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
    $queryBuilder->getRestrictions()->removeAll();

    $statement = $queryBuilder
      ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
      ->from('pages', 'p')
      ->leftJoin(
        'p',
        'be_users',
        'u',
        $queryBuilder->expr()->eq('p.cruser_id', $queryBuilder->quoteIdentifier('u.uid'))
      )
      ->where(
        $queryBuilder->expr()->gt('p.starttime', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
        $queryBuilder->expr()->gt('p.endtime', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('p.red_status', $queryBuilder->createNamedParameter(12, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('p.deleted', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
        $queryBuilder->expr()->eq('p.hidden', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT))
      )
      ->groupBy('p.slug')
      ->orderBy('p.endtime', 'ASC');

    $result = $statement->execute();

    return $result->fetchAllAssociative();
  }

    public function findExpiredRecordsInTheLast30OR90Days(int $daysPast = 0)
    {
        $currentDate = new \DateTime("now");
        $timestampToday = $currentDate->getTimestamp();
        $period = ($daysPast == 30) ? 'P30D' : 'P90D';
        $timeperiod = $currentDate->sub(new \DateInterval($period));
        $timestamp = $timeperiod->getTimestamp();

        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll(); // Remove all restrictions

        $queryBuilder
            ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
            ->from('pages', 'p')
            ->leftJoin(
                'p',
                'be_users',
                'u',
                $queryBuilder->expr()->eq('p.cruser_id', $queryBuilder->quoteIdentifier('u.uid'))
            )
            ->where(
                $queryBuilder->expr()->gt('p.starttime', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.red_status', $queryBuilder->createNamedParameter(22, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.deleted', $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)),
                $queryBuilder->expr()->eq('p.hidden', $queryBuilder->createNamedParameter(1, \PDO::PARAM_INT))
            )
            ->andWhere(
                $queryBuilder->expr()->gte('p.endtime', $queryBuilder->createNamedParameter($timestamp, \PDO::PARAM_INT)),
                $queryBuilder->expr()->lte('p.endtime', $queryBuilder->createNamedParameter($timestampToday, \PDO::PARAM_INT))
            )
            ->groupBy('p.slug')
            ->orderBy('p.endtime', 'ASC');
        return $queryBuilder->execute()->fetchAllAssociative();
    }

}