<?php
namespace App\Repository;
use App\Entity\Content;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method Content|null find($id, $lockMode = null, $lockVersion = null)
* @method Content|null findOneBy(array $criteria, array $orderBy = null)
* @method Content[] findAll()
* @method Content[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ContentRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Content::class);
}
//TODO: test this and other functions after site changes
public function slugIsUnique ($slug = "", $content = "", $site = "")
{
$count = [];
if(empty($content)) {
$count = $this->createQueryBuilder("a")
->select("count(a.id)");
if($site) {
$count = $count->join("a.site", "s");
}
$count = $count->where("a.slug = :slug");
if($site) {
$count = $count->andWhere("s.id IN (:sites)")
->setParameter("sites", [$site]);
}
$count = $count->setParameter("slug", $slug)
->getQuery()
->getResult();
}
else {
$count = $this->createQueryBuilder("a")
->select("count(a.id)")
->join("a.site", "s")
->where("a.id != :id")
->andWhere("a.slug = :slug")
//->andWhere("s.id = :site")
->andWhere("s.id IN (:sites)")
->setParameter("id", $content->getId())
->setParameter("slug", $slug)
->setParameter("sites", $content->getSiteIds())
->getQuery()
->getResult();
}
if (!empty($count)) {
return ( ((int) $count[0][1]) < 1 );
}
else {
return true;
}
}
/*
* takes a page title or a slug and returns a unique slug
*/
public function getUniqueSlug ($title = "", $content = "", $site = "")
{
$slug = trim(trim(strtolower($title)), '-');
$slug = str_replace(' ', '-', $slug);
$slug = preg_replace('/-+/', '-', $slug);
$slug = preg_replace('/[^a-z0-9-]/', '', $slug);
$final_slug = $slug;
$x = 2;
while(!$this->slugIsUnique($final_slug, $content, $site)) {
$final_slug = $slug . '-' . $x++;
}
return $final_slug;
}
public function getPostsByCategory (
$category = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true,
$site = ""
) {
static $content_ids;
if (is_null($content_ids)) {
$content_ids = array ();
}
// always include children for now ...
$result = $this->createQueryBuilder("a")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id = :id")
->orWhere("d.id = :id")
->orWhere("e.id = :id")
->orWhere("f.id = :id")
->setParameter("id", $category)
->distinct()
->andWhere("a.type = 2")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.published_at", "DESC")
->setFirstResult($length * ($page - 1))
->setMaxResults($length)
->getQuery()
->getResult();
for ($i = 0; $i < count($result); $i++) {
$content_ids[] = $result[$i]->getId();
}
return $result;
}
public function recommendedContent (
$category = "",
$length = 3,
$ignoreID = 0,
$site = ""
) {
static $content_ids;
if (is_null($content_ids)) {
$content_ids = array ();
}
// // always include children for now ...
// $result = $this->createQueryBuilder("a")
// ->join("a.secondary_categories", "c");
// if($site) {
// $result = $result->join("a.site", "s");
// }
// $result = $result->leftJoin("c.prnt", "d")
// ->leftJoin("d.prnt", "e")
// ->leftJoin("e.prnt", "f")
// ->where("c.id = :id")
// ->orWhere("d.id = :id")
// ->orWhere("e.id = :id")
// ->orWhere("f.id = :id")
// ->setParameter("id", $category)
// ->distinct()
// ->andWhere("a.type = 2")
// ->andWhere("a.status = 1");
// if($site) {
// $result = $result->andWhere("s.id = {$site}");
// }
// if ($ignoreID) {
// $result = $result->andWhere("a.id != {$ignoreID}");
// }
// $result = $result->orderBy("a.published_at", "DESC")
// ->setMaxResults($length)
// ->getQuery()
// ->getResult();
// for ($i = 0; $i < count($result); $i++) {
// $content_ids[] = $result[$i]->getId();
// }
// $hot = $this->createQueryBuilder("a")->join("a.secondary_categories", "c")->join("a.site", "s")->join('a.content_analytics_daily', 'd');
// $hot = $hot->where("c.id = :id")->setParameter("id", $category)->distinct();
// if($site) {
// $hot = $hot->andWhere("s.id = {$site}");
// }
// if ($ignoreID) {
// $hot = $hot->andWhere("c.id != {$ignoreID}")->andWhere("a.id != {$ignoreID}");
// }
// $hot = $hot->orderBy('a.total_impressions', 'DESC')->setMaxResults(1)->getQuery()->getResult();
// $hot[0]->hot = true;
// $hot = $this->createQueryBuilder("a")->join("a.secondary_categories", "c")->join("a.site", "s")->join('a.content_analytics_daily', 'd');
// $tenDaysAgo = date_create(date("Y-m-d"))->modify('-10 days')->format('Y-m-d');
// $hot = $hot->where("d.day > {$tenDaysAgo}");
// $hot = $hot->orderBy('d.views', 'DESC')->setMaxResults(20)->getQuery()->getResult();
// $howManyHot = count($hot);
$query = implode(" ", array("SELECT cad.content_id, MAX(cad.impressions), MAX(cad.views)",
"FROM `content_analytics_daily` cad",
"LEFT JOIN `content` c ",
"ON cad.content_id = c.id ",
"LEFT JOIN `content_category` cc",
"ON cc.content_id = c.id",
"WHERE cc.category_id = 19",
"AND cad.day > '2022-08-07'",
"GROUP BY cad.content_id",
"ORDER BY MAX(cad.`views`) DESC;"));
$stmt = $this->entityManager->getConnection()->prepare($query);
$hot = '';
// if ($howManyHot) {
// $hotIndex = array_rand($hot, 1);
// $hot[$hotIndex]->hot = true;
// $hot = $hot[$hotIndex];
// }
$result = $this->createQueryBuilder("a")->join("a.secondary_categories", "c")->join("a.site", "s");
$result = $result->where("c.id = :id")->setParameter("id", $category)->distinct();
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
// if ($ignoreID) {
// $result = $result->andWhere("c.id != {$ignoreID}")->andWhere("a.id != {$ignoreID}")->andWhere("a.id != {$hot->getId()}")->andWhere("c.id != {$hot->getId()}");
// }
// if ($howManyHot) {
// $result = $result->andWhere("a.id != {$hot->getId()}")->andWhere("c.id != {$hot->getId()}");
// }
$result = $result->orderBy("rand()");
// if ($howManyHot) {
// $result = $result->setMaxResults($length-1);
// }else{
$result = $result->setMaxResults($length);
// }
$result = $result->getQuery()->getResult();
for ($i = 0; $i < count($result); $i++) {
$result[$i]->hot = false;
}
// array_unshift($result , $hot);
shuffle($result);
return $result;
}
public function getPostsByCategories (
$categories = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true,
$site = ""
) {
static $content_ids;
if (is_null($content_ids)) {
$content_ids = array ();
}
$category_ids = [];
foreach($categories as $category) {
$category_ids[] = $category->getId();
}
// always include children for now ...
$result = $this->createQueryBuilder("a")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id IN (:ids)")
->orWhere("d.id IN (:ids)")
->orWhere("e.id IN (:ids)")
->orWhere("f.id IN (:ids)")
->setParameter("ids", $category_ids)
->distinct()
->andWhere("a.type = 2")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.published_at", "DESC")
->setFirstResult($length * ($page - 1))
->setMaxResults($length)
->getQuery()
->getResult();
for ($i = 0; $i < count($result); $i++) {
$content_ids[] = $result[$i]->getId();
}
return $result;
}
public function getContentByCategories (
$type = 0,
$categories = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true,
$site = ""
) {
static $content_ids;
if (is_null($content_ids)) {
$content_ids = array ();
}
$category_ids = [];
foreach($categories as $category) {
$category_ids[] = $category->getId();
}
// always include children for now ...
$result = $this->createQueryBuilder("a")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id IN (:ids)")
->orWhere("d.id IN (:ids)")
->orWhere("e.id IN (:ids)")
->orWhere("f.id IN (:ids)")
->setParameter("ids", $category_ids)
->distinct()
->andWhere("a.status = 1");
if($type) {
$result = $result->andWhere("a.type = {$type}");
}
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.published_at", "DESC")
->setFirstResult($length * ($page - 1))
->setMaxResults($length)
->getQuery()
->getResult();
for ($i = 0; $i < count($result); $i++) {
$content_ids[] = $result[$i]->getId();
}
return $result;
}
public function getDirectoriesByCategory (
$category = "",
$length = 10,
$page = 1,
$sort = "",
$includeChildren = true,
$site = ""
) {
static $content_ids;
if (is_null($content_ids)) {
$content_ids = array ();
}
// always include children for now ...
$result = $this->createQueryBuilder("a")
->join("a.category", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->where("c.id = :id")
->setParameter("id", $category)
->distinct()
->andWhere("a.type = 4")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.published_at", "DESC")
->setFirstResult($length * ($page - 1))
->setMaxResults($length)
->getQuery()
->getResult();
for ($i = 0; $i < count($result); $i++) {
$content_ids[] = $result[$i]->getId();
}
return $result;
}
public function countPostsByCategory (
$category = "",
$includeChildren = true,
$site = ""
) {
$result = $this->createQueryBuilder("a")
->select("count(a.id)")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id = :id")
->orWhere("d.id = :id")
->orWhere("e.id = :id")
->orWhere("f.id = :id")
->setParameter("id", $category)
->distinct()
->andWhere("a.type = 2")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.created_at", "DESC")
->getQuery()
->getResult();
return $result;
}
public function countPostsByCategories (
$categories = "",
$includeChildren = true,
$site = ""
) {
foreach($categories as $category) {
$category_ids[] = $category->getId();
}
$result = $this->createQueryBuilder("a")
->select("count(a.id)")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id IN (:ids)")
->orWhere("d.id IN (:ids)")
->orWhere("e.id IN (:ids)")
->orWhere("f.id IN (:ids)")
->setParameter("ids", $category_ids)
->distinct()
->andWhere("a.type = 2")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.created_at", "DESC")
->getQuery()
->getResult();
return $result;
}
public function countContentByCategories (
$type = 0,
$categories = "",
$includeChildren = true,
$site = ""
) {
foreach($categories as $category) {
$category_ids[] = $category->getId();
}
$result = $this->createQueryBuilder("a")
->select("count(a.id)")
->join("a.secondary_categories", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->leftJoin("c.prnt", "d")
->leftJoin("d.prnt", "e")
->leftJoin("e.prnt", "f")
->where("c.id IN (:ids)")
->orWhere("d.id IN (:ids)")
->orWhere("e.id IN (:ids)")
->orWhere("f.id IN (:ids)")
->setParameter("ids", $category_ids)
->distinct()
->andWhere("a.status = 1");
if($type) {
$result = $result->andWhere("a.type = {$type}");
}
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->orderBy("a.created_at", "DESC")
->getQuery()
->getResult();
return $result;
}
public function getGalleries (
$category = "",
$sort = "",
$site = ""
) {
//just sorting by title for now
$result = $this->createQueryBuilder("a")
->join("a.category", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->where("c.slug = :slug")
->setParameter("slug", $category)
->distinct()
->andWhere("a.type = 5")
->andWhere("a.status = 1");
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
//->orderBy("a.created_at", "DESC")
$result = $result->orderBy("a.title", "ASC")
->getQuery()
->getResult();
return $result;
}
//TODO: filter by site
public function getEbooks(
$customer = "",
$site = ""
){
$result = $this->createQueryBuilder("a")
->join("a.customers", "c");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->distinct()
->Where("a.type = 19")
->andWhere("a.status = 1");
if($customer){
$result = $result->andWhere("c.id = {$customer}");
}
if($site) {
$result = $result->andWhere("s.id = {$site}");
}
$result = $result
->getQuery()
->getResult();
return $result;
}
public function getEbooksByCategory(
$category = "",
$site = ""
){
$result = $this->createQueryBuilder("a")
->join("a.secondary_categories", "sc");
if($site) {
$result = $result->join("a.site", "s");
}
$result = $result->where("sc.id = :category")
->andWhere("a.status = 1");
if($site) {
if($site == Content::SITE_AAR) {
$result = $result->andWhere("sc.taxonomy = 'aar_ebook-category'");
}
else {
$result = $result->andWhere("sc.taxonomy = 'ebook-category'");
}
$result = $result->andWhere("s.id = {$site}");
}
$result = $result->setParameter("category", $category->getId())
->getQuery()
->getResult();
return $result;
}
public function countTopicsPostsByForum (
$slug = ""
) {
$result = $this->createQueryBuilder("a")
->select("COUNT(DISTINCT b.id), COUNT(c.id)")
->leftJoin("a.children", "b")
->leftJoin("b.children", "c")
->where("a.slug = :slug")
->setParameter("slug", $slug)
->andWhere("a.type = 9")
->andWhere("a.status = 1")
->andWhere("b.type = 10")
->andWhere("b.status = 1")
->andWhere("c.type = 11 OR c.status IS NULL")
->andWhere("c.status = 1 OR c.status IS NULL")
->getQuery()
->getResult();
return $result;
}
public function getContentByUrl (
$url = "", $site = 1
) {
$pos = strrpos($url, '/');
$slug = $pos === false ? $url : substr($url, $pos + 1);
$results = $this->createQueryBuilder("a")
->join("a.site", "s")
->where("a.slug = :slug")
->andWhere("s.id = :site")
->setParameter("slug", $slug)
->setParameter("site", $site)
->getQuery()
->getResult();
foreach($results as $result) {
if($url == $result->getURL()) {
return $result;
}
}
return null;
}
}