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 `u` ```p```.```cruser_id`` = ``be_users```.```uid``` ON , `be_users` `u` WHERE (`p`.`starttime` > ?) AND (`p`.`endtime` > ?) AND (`p`.`red_status` > ?) AND (`p`.`deleted` > ?) AND (`p`.`hidden` > ?) GROUP BY `p`.`slug` ORDER BY `p`.`endtime` ASC'  


    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;
    */

    $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()->eq('p.cruser_id', $queryBuilder->quoteIdentifier('u.uid')),
        $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');
     // ->setMaxResults(40);
      //  var_dump($queryBuilder->getSQL());
      //  die();

      //($queryBuilder->getSQL());

      $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()->eq('p.cruser_id', $queryBuilder->quoteIdentifier('u.uid')),
        $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();

  }

  /**
   * 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];

  }

  /**
   * Returns selected pages from the pages Table
   * 
   * @return QueryResult|array
   */
  // public function findActivePages()
  // {
  //   $activePageData = [];
  //   $activePageData = $this->getActivePagesAndPageCreatetors();

  // }
}