<?php
namespace Boab\CmsBundle\Repository;
use Boab\CmsBundle\Repository\BaseRepository;
use Boab\CmsBundle\Entity\ParentableInterface;
use Boab\CmsBundle\Entity\Page;
use Doctrine\ORM\Query\ResultSetMapping;
use Doctrine\DBAL\Connection;
use Boab\CmsBundle\Entity\Content;
use Boab\CmsBundle\Entity\ContentInterface;
use Boab\CmsBundle\Service\PaginationInterface;
use Boab\CmsBundle\Util\DataTable\DataTableContextInterface;
use Boab\CmsBundle\Repository\ContentRepositoryInterface;
class ContentRepository extends BaseRepository implements ContentRepositoryInterface
{
public function findContents(array $params)
{
$qb = $this->addContentQueryCriteria($params["order"], $params["sortColumn"]);
return $this->pagination
->setLimit($params["limit"])
->setOffset($params["offset"])
->paginate($qb);
}
private function addContentQueryCriteria($order, $sortColumn)
{
$qb = $this->getContentQuery();
if($sortColumn == 'contentType'){
$qb->addSelect('Type(c) as HIDDEN contentType');
$qb->addOrderBy($sortColumn, $order);
}elseif($sortColumn == 'author'){
$qb->addSelect("CONCAT(u.firstname,' ',u.lastname) as HIDDEN author");
$qb->addOrderBy($sortColumn, $order);
}else{
$qb->addOrderBy(sprintf('c.%s',$sortColumn), $order);
}
return $qb;
}
public function findContentBySearchTerm(array $params)
{
$qb = $this->addContentQueryCriteria($params["order"], $params["sortColumn"]);
$qb->where('1 = 1');
if(isset($params["searchTerm"])){
$qb->andWhere('c.title LIKE :search_term')
->setParameter('search_term', '%'.$params["searchTerm"].'%');
}
if(isset($params["startDate"])){
$qb->andWhere('c.dateCreated BETWEEN :start AND :end')
//->orWhere('c.summary LIKE :criteria')
->setParameter("start", $params["startDate"], \Doctrine\DBAL\Types\Type::DATETIME)
->setParameter("end", $params["endDate"], \Doctrine\DBAL\Types\Type::DATETIME);
}
return $this->pagination
->setLimit($params["limit"])
->setOffset($params["offset"])
->paginate($qb);
}
public function findContentsByType(string $type, int $page, string $status=ContentInterface::STATUS_PUBLISHED)
{
$qb = $this->getContentQuery($type)
->andWhere('c.status = :status')
->setParameter('status', $status)
->orderBy('c.dateCreated', 'DESC');
return $this->pagination->paginate($qb, $page);
}
public function findContentInArray(array $ids, $limit, $order)
{
return $this->getContentQuery(Page::class)
->where('c.id IN (:ids)')
->andWhere('c.status = :status')
->setParameter('ids', $ids, Connection::PARAM_STR_ARRAY)
->setParameter('status', ContentInterface::STATUS_PUBLISHED)
->orderBy('c.datePublished', $order)
->getQuery()
->getResult();
}
public function findPossiblePageParent(string $contentType, int $parentId=null): ?array
{
$qb = $this->getContentQuery(Page::class);
if(!empty($id)){
$qb->where('c.id NOT IN (:id)')
->setParameter('id', $parentId);
}
return $qb->orderBy('c.dateCreated', 'DESC')
->getQuery()
->getResult();
}
public function findAllContentByType(string $contentType):?iterable
{
$qb = $this->getContentQuery($contentType)
->orderBy('c.dateCreated', 'DESC');
return $qb->getQuery()->getResult();
}
public function findContentById(int $id): ContentInterface
{
$qb = $this->getContentQuery()
->where('c.id = :id')
->setParameter('id',$id);
return $qb->getQuery()->getOneOrNullResult();
}
public function findContentByRouteId($id): ?ContentInterface
{
$qb = $this->getContentQuery(Page::class)
->addSelect('m')
->leftJoin('c.route', 'm')
->where('m.id = :id')
->andWhere('c.status = :status')
->setParameter('id', $id)
->setParameter('status', Content::STATUS_PUBLISHED);
return $qb->getQuery()->getOneOrNullResult();
}
public function findContentBySlug(string $slug): ContentInterface
{
$qb = $this->getContentQuery()
->where('c.slug = :slug')
->andWhere('c.status = :status')
->setParameter('slug',$slug)
->setParameter('status', Content::STATUS_PUBLISHED);
return $qb->getQuery()->getOneOrNullResult();
}
public function findContentByUserId($userId)
{
$qb = $this->getContentQuery()
->where('u.id = :id')
->setParameter('id',$userId);
return $qb->getQuery()->getResult();
}
public function findLatestContent($contentType, $limit)
{
$qb = $this->getContentQuery($contentType)
->where('c.status = :status')
->orderBy('c.datePublished', 'DESC')
->setParameter('status', Content::STATUS_PUBLISHED)
->getQuery()
->setFirstResult(0)
->setMaxResults($limit);
return $qb->getResult();
}
public function findRelatedContentType($contentType, $limit, $excludeIds=[])
{
$ids = implode(',',$excludeIds);
$qb = $this->getContentQuery($contentType)
->where('c.status = :status')
->andWhere('c.id NOT IN (:ids)')
->orderBy('c.datePublished', 'DESC')
->setParameter('status', Content::STATUS_PUBLISHED)
->setParameter('ids', $ids)
->getQuery()
->setFirstResult(0)
->setMaxResults($limit);
return $qb->getResult();
}
public function findContentByTerm($contentType, $term, $page)
{
$qb = $this->getContentQuery($contentType);
$qb->where('t.slug = :slug')
->andWhere('c.status = :status')
->setParameter('slug', $term)
->setParameter('status', Content::STATUS_PUBLISHED)
->orderBy('c.datePublished', 'DESC');
return $this->paginate($qb->getQuery(), $page);
}
public function findFeaturedContent()
{
$qb = $this->getContentQuery()
->where('c.isFeatured = :featured')
->andWhere('c.status = :status')
->setParameter('featured',1)
->setParameter('status', Content::STATUS_PUBLISHED)
->setFirstResult(0)
->setMaxResults(1);
return $qb->getQuery()->getOneOrNullResult();
}
public function findFeaturedContentsByType($contentType, $limit, $order)
{
$qb = $this->getContentQuery($contentType)
->where('c.isFeatured = :featured')
->andWhere('c.status = :status')
->orderBy('c.datePublished', $order)
->setParameter('featured', Content::IS_FEATURED)
->setParameter('status', Content::STATUS_PUBLISHED)
->setFirstResult(0)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
public function getPromotedContents()
{
$qb = $this->getContentQuery()
->where('c.promoted = :promoted')
->andWhere('c.status = :status')
->setParameter('promoted',1)
->setParameter('status', Content::STATUS_PUBLISHED)
->orderBy('c.datePublished', 'DESC');
return $qb->getQuery()->getResult();
}
public function findChildContentsByParent(ParentableInterface $content, $limit=0)
{
$qb = $this->getContentQuery(Content::class)
->where('c.parentId = :parentId')
->andWhere('c.status = :status')
->setParameter('parentId', $content->getId())
->setParameter('status', Content::STATUS_PUBLISHED)
->orderBy('c.datePublished', 'DESC');
if($limit > 0){
$qb->setFirstResult(0)
->setMaxResults($limit);
}
return $qb->getQuery()->getResult();
}
public function findPublishedContentByType($contentType, $page, $params=[])
{
$qb = $this->getContentQuery($contentType)
->where('c.status = :status');
$this->addParams($qb, $params);
$qb->orderBy('c.datePublished', 'DESC')
->setParameter('status', Content::STATUS_PUBLISHED);
return $this->paginate($qb->getQuery(), $page);
}
public function findContentCollection(array $classes, int $page): PaginationInterface
{
$dql = $this->getContentByEntitiesDql($classes);
$qb = $this->createQuery($dql);
$qb->setParameter('status', Content::STATUS_PUBLISHED);
return $this->pagination->paginate($qb);
}
public function findContentByClasses($classes, $page)
{
$dql = $this->getContentByEntitiesDql($classes);
$qb = $this->createQuery($dql);
$qb->setParameter('status', Content::STATUS_PUBLISHED);
return $this->paginate($qb, $page);
}
private function getContentByEntitiesDql($classes, $order='DESC')
{
$dql = sprintf('SELECT c, u
FROM %s c
LEFT JOIN c.user u
WHERE (c INSTANCE OF %s)
AND c.status = :status
ORDER BY c.datePublished %s', Content::class,
implode(' OR c INSTANCE OF ', $classes), $order);
return $dql;
}
public function findContentByEntitiesWidget($entityClasses, $limit=3, $order='DESC')
{
$dql = $this->getContentByEntitiesDql($entityClasses, $order);
$qb = $this->createQuery($dql);
$qb->setParameter('status', Content::STATUS_PUBLISHED)
->setFirstResult(0)
->setMaxResults($limit);
return $qb->getResult();
}
public function hasChildContents(ParentableInterface $page)
{
$query = $this->_em->createQueryBuilder()
->select('COUNT(c.id)')
->from(Contents::class, 'c')
->where('c.parentId = :parentId')
->setParameter('parentId', $page->getId());
return $query->getQuery()->getSingleScalarResult();
}
public function findContentCount()
{
$query = $this->_em->createQueryBuilder()
->select('COUNT(c.id)')
->from('BoabCmsBundle:Content', 'c');
return $query->getQuery()->getSingleScalarResult();
}
public function findContentsByParentId($parentId, $limit=0, $orderBy="datePublished", $order):iterable
{
$qb = $this->getContentQuery(Page::class)
//->leftJoin('c.parent', 'p')
->where('c.parentId = :parentId')
->andWhere('c.status = :status')
//->andWhere('c.id NOT IN (:ids)')
->setParameter('parentId', $parentId)
//->setParameter('ids', $excludeIds, Connection::PARAM_STR_ARRAY)
->setParameter('status', Content::STATUS_PUBLISHED)
->orderBy(sprintf('c.%s', $orderBy), $order);
if($limit > 0){
$qb->setFirstResult(0)
->setMaxResults($limit);
}
return $qb->getQuery()->getResult();
}
public function findTotalContentByYear()
{
$sql = "SELECT
c.content_type AS type,
count(c.id) as count
FROM contents c
GROUP BY type";
$rsm = new ResultSetMapping;
$rsm->addScalarResult('type', 'type');
$rsm->addScalarResult('count', 'count');
$query = $this->_em->createNativeQuery($sql, $rsm);
return $query->getResult();
//$dql = 'SELECT SUBSTRING(c.dateCreated, 1, 4) AS year, count(c) as total FROM BoabCmsBundle:Content c GROUP BY year';
$dql = 'SELECT c.discr AS type, count(c) as total FROM BoabCmsBundle:Content c GROUP BY type';
$query = $this->_em->createQuery($dql);
return $query->getArrayResult();
//return $query->getResult();
}
public function publishContent(array $references=[])
{
$qb = $this->_em->createQueryBuilder();
$q = $qb->update(Content::class, 'c')
->set('c.datePublish', $qb->expr()->literal('verify'))
->where('m.id IN (:ids)')
->setParameter('ids', $references)
->getQuery();
return $q->execute();
}
public function findContentByTermId($termId, $limit=0, $dateOrder='DESC'):?PaginationInterface
{
$qb = $this->getContentQuery()
->leftJoin('c.term', 't')
->where('t = :termId')
->andWhere('c.status = :status')
->setParameter('termId', $termId)
->setParameter('status', Content::STATUS_PUBLISHED)
->orderBy('c.datePublished', $dateOrder);
return $this->pagination->paginate($qb);
}
public function findAllSocialMediaPost()
{
$qb = $this->getContentQuery()
->andWhere('c.status = :status')
->andWhere('c.publishOnSocialMedia = :onSocialMedia')
->setParameter('status', Content::STATUS_PUBLISHED)
->setParameter('onSocialMedia', true)
->orderBy('c.datePublished', 'ASC');
return $qb->getQuery()->getResult();
}
}