pagemanager/Classes/Domain/Repository/ActivePageRepository.php

<?php

declare(strict_types=1);

namespace Meramo\Pagemanager\Domain\Repository;

use Meramo\Pagemanager\Domain\Model\ActivePage;
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();

    /*
    SELECT p.uid, p.starttime, p.endtime, p.title, p.roof, u.username, u.realName
    FROM pages p LEFT JOIN be_users u ON p.cruser_id = u.uid
    WHERE p.starttime > 0 AND p.endtime > 0 and p.red_status = 12 and p.deleted = 0 and p.hidden = 0
    GROUP BY p.slug ORDER BY p.endtime ASC;

    SELECT `p`.`uid`, `p`.`starttime`, `p`.`endtime`, `p`.`title`, `p`.`roof`, `p`.`slug`, `u`.`username`, `u`.`realName` FROM `pages` `p` LEFT JOIN `be_users` `u` ON `p`.`cruser_id` = `u`.`uid` WHERE (`p`.`starttime` > 0) 
    AND (`p`.`endtime` <= 1683040238) AND (`p`.`red_status` = 12) AND (`p`.`deleted` = 0) AND (`p`.`hidden` = 0) GROUP BY `p`.`slug` ORDER BY `p`.`endtime` ASC
    */

    $statement = $queryBuilder
      ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
      ->from('pages', 'p')
      //->from('be_users', 'u')
      ->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();
  }

  /**
   * Queries the pages and be_users to get Records which expired in the last 30 days
   * 
   * @return QueryResult|array
   */

  public function findExpiredRecordsInTheLast30Days()
  {
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
    $queryBuilder->getRestrictions()->removeAll();

    $currentDate = new \DateTime("now");
    $last30Days = $currentDate->sub(new \DateInterval('P30D'));
    $timestamp = date_format($last30Days, 'U');

    $statement = $queryBuilder
      ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
      ->from('pages', 'p')
      //->from('be_users', 'u')
      ->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()->lt('p.endtime', $queryBuilder->createNamedParameter($timestamp, \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();
  }


  /**
   * Queries the pages and be_users to get Records which expired in the last 30 days
   * 
   * @return QueryResult|array
   */

  public function findExpiredRecordsInTheLast90Days()
  {
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
    $queryBuilder->getRestrictions()->removeAll();

    $currentDate = new \DateTime("now");
    $last90Days = $currentDate->sub(new \DateInterval('P90D'));
    $timestamp = date_format($last90Days, 'U');

    $statement = $queryBuilder
      ->select('p.uid', 'p.starttime', 'p.endtime', 'p.title', 'p.roof', 'p.slug', 'u.username', 'u.realName')
      ->from('pages', 'p')
      //->from('be_users', 'u')
      ->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()->lt('p.endtime', $queryBuilder->createNamedParameter($timestamp, \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');

      var_dump($queryBuilder->getSQL());

    $result = $statement->execute();

    return $result->fetchAllAssociative();
  }

  /**
   * Create category from slug
   * 
   */

  public function createCategoryFromSlug($slug)
  {
    if (substr($slug, 0, 1) === '/') $value = ltrim($slug, '/');

    if (substr($slug, 0, 1) === '_') $value = ltrim($slug, '_');

    return  explode('/', $value)[0];
  }

}