Symfony Profiler

<?php
namespace Notaris\AdminBundle\Entity;
use Doctrine\ORM\EntityRepository;
/**
* VideoRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class VideoRepository extends EntityRepository
{
/**
* @param $locale
* @param int $offset
* @param array $sort_arr
* @param int $limit
* @return array|bool
*/
public function getAllVideo($locale, $offset = 0, $sort_arr = array(), $limit = 50)
{
//start orders
$orders_arr = array();
$order_type = $sort_arr['type'];
$order_columns = array(
0 => 'v.pub_date',
'v.title',
'v.is_izimi',
'v.count_clicked',
'primary_subsite_name',
'is_active',
'v.news_id',
);
if (isset($order_columns[$sort_arr['column']]) && $order_columns[$sort_arr['column']]) {
$orders_arr[] = $order_columns[$sort_arr['column']] . ' ' . $order_type;
}
//end orders
$em = $this->getEntityManager();
$con = $em->getConnection();
$sql = "SELECT v.id,
v.pub_date,
v.title,
v.is_izimi,
v.count_clicked,
IF (v.is_active = 1,(IF(v.pub_date <= NOW(), v.is_active, 2)),v.is_active) AS is_active,
v.is_varia,
v.is_varia_primary,
IF(v.is_varia = 1 and is_varia_primary = 1, 'Varia', sp.name) as primary_subsite_name,
v.news_id, vs.video_id,
GROUP_CONCAT(DISTINCT s.name ORDER BY vs.position ASC SEPARATOR '|') as subsites
FROM video v
LEFT JOIN video_subsite vs ON vs.video_id = v.id
LEFT JOIN subsite s ON s.id = vs.subsite_id and s.is_active = 1
LEFT JOIN subsite sp ON sp.id = v.primary_subsite and sp.is_active = 1
WHERE v.locale = :locale
GROUP BY v.id
ORDER BY " . implode(', ', $orders_arr) . '
LIMIT ' . $offset . ', ' . $limit;
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$result = $stmt->fetchAll();
//all count
$sql = "SELECT count(*) as count FROM video v WHERE v.locale = :locale";
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$count = $stmt->fetchOne();
$online = $offline = $scheduled = 0;
if ($count) {
$time = new \DateTime('now');
$now = $time->format('Y-m-d 00:00:00');
//online
$sql = "SELECT count(*) as count FROM video v WHERE v.is_active = 1 AND v.pub_date <= :now AND v.locale = :locale";
$stmt = $con->prepare($sql);
$stmt->bindValue('now', $now);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$online = $stmt->fetchOne();
//scheduled
$sql = "SELECT count(*) as count FROM video v WHERE v.is_active = 1 AND v.pub_date > :now AND v.locale = :locale";
$stmt = $con->prepare($sql);
$stmt->bindValue('now', $now);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$scheduled = $stmt->fetchOne();
//offline
$sql = "SELECT count(*) as count FROM video v WHERE v.is_active = 0 AND v.locale = :locale";
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$offline = $stmt->fetchOne();
}
return array(
'result' => $result,
'count' => $count,
'online' => $online,
'offline' => $offline,
'scheduled' => $scheduled
);
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListVideosSubsite($locale, $subsite_id){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:video v
INNER JOIN NotarisAdminBundle:VideoSubsite vs WITH vs.video = v.id
WHERE v.locale = :locale AND vs.subsite = :subsite_id
ORDER BY vs.position ASC, v.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('subsite_id', $subsite_id);
$tags = $query->getResult();
if($tags){
return $tags;
}
return false;
}
/**
* @param $locale
* @return array|bool
*/
public function getListVideoIsGeneral($locale){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
WHERE v.locale = :locale AND v.isVaria = :is_varia
ORDER BY v.variaPosition ASC, v.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('is_varia', 1);
$tags = $query->getResult();
if($tags){
return $tags;
}
return false;
}
public function getListVideoIsVaria($locale, $page, $perpage){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
WHERE v.locale = :locale AND v.isVaria = :is_varia
AND v.publicationDate <= :publicationDate
AND v.isActive = true
ORDER BY v.variaPosition ASC, v.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('publicationDate', new \DateTime('now'))
->setParameter('is_varia', 1);
if($page){
$query
->setFirstResult($page * $perpage)
->setMaxResults($perpage);
}
$tags = $query->getResult();
if($tags){
return $tags;
}
return false;
}
public function getListVideoIsVariaCount($locale){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
WHERE v.locale = :locale AND v.isVaria = :is_varia
AND v.publicationDate <= :publicationDate
AND v.isActive = true
ORDER BY v.variaPosition ASC, v.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('publicationDate', new \DateTime('now'))
->setParameter('is_varia', 1);
$tags = $query->getResult();
if($tags){
return count($tags);
}
return false;
}
public function getRecentVideos($locale, $limit = 5){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
WHERE v.locale = :locale AND v.isActive = true
AND v.publicationDate <= :now
ORDER BY v.publicationDate DESC')
->setMaxResults($limit)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'));
$videos = $query->getResult();
if($videos){
return $videos;
}
return false;
}
public function getSubsiteVideos($subsite, $page, $perpage){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
INNER JOIN NotarisAdminBundle:VideoSubsite sv WITH sv.video = v
WHERE v.isActive = true
AND v.publicationDate <= :now
AND sv.subsite = :subsite
ORDER BY v.publicationDate DESC')
->setParameter('subsite', $subsite)
->setParameter('now', new \DateTime('now'));
if($page){
$query
->setFirstResult($page * $perpage)
->setMaxResults($perpage);
}
$videos = $query->getResult();
if($videos){
return $videos;
}
return false;
}
public function getRandomSubsiteVideos($subsite, $video){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
INNER JOIN NotarisAdminBundle:VideoSubsite sv WITH sv.video = v
WHERE v.isActive = true
AND v.publicationDate <= :now
AND v != :video
AND sv.subsite = :subsite')
->setParameter('subsite', $subsite)
->setParameter('video', $video)
->setParameter('now', new \DateTime('now'));
$videos = $query->getResult();
if($videos){
shuffle($videos);
return array_slice($videos, 0, 6);
}
return false;
}
public function getLatestSubsiteVideos($subsite, $video){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
INNER JOIN NotarisAdminBundle:VideoSubsite sv WITH sv.video = v
WHERE v.isActive = true
AND v.publicationDate <= :now
AND v != :video
AND sv.subsite = :subsite
ORDER BY v.publicationDate DESC')
->setParameter('subsite', $subsite)
->setParameter('video', $video)
->setParameter('now', new \DateTime('now'))
->setMaxResults(10);
$videos = $query->getResult();
if($videos){
// shuffle($videos);
// return array_slice($videos, 0, 6);
return $videos;
}
return false;
}
public function getVideosFromSubsite($subsite){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
INNER JOIN NotarisAdminBundle:VideoSubsite sv WITH sv.video = v
WHERE sv.subsite = :subsite')
->setParameter('subsite', $subsite);
$videos = $query->getResult();
if($videos){
return $videos;
}
return false;
}
/**
* @param $subsite
* @return bool|int
* @throws \Exception
*/
public function getTotalSubsiteVideos($subsite){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
INNER JOIN NotarisAdminBundle:VideoSubsite sv WITH sv.video = v
WHERE v.isActive = true
AND v.publicationDate <= :now
AND sv.subsite = :subsite
ORDER BY v.publicationDate DESC')
->setParameter('subsite', $subsite)
->setParameter('now', new \DateTime('now'));
$videos = $query->getResult();
if($videos){
return count($videos);
}
return false;
}
public function getVideoBySlug($slug){
$query = $this->getEntityManager()
->createQuery('SELECT v FROM
NotarisAdminBundle:Video v
WHERE v.isActive = true
AND v.publicationDate <= :now
AND v.slug = :slug')
->setParameter('slug', $slug)
->setParameter('now', new \DateTime('now'));
$videos = $query->getResult();
if($videos){
return array_shift($videos);
}
return false;
}
public function getAllIzimiVideos($page, $perPage, $locale = null, $minUpdateDate = null, $maxUpdateDate = null, $sortBy = null, $sortDirection = null, $category = null)
{
$videos = $this->createQueryBuilder('n');
if (!empty($category)) {
$videos = $videos->leftJoin('n.categories', 'c');
}
$videos = $videos->where('n.isIzimi = 1 AND n.isActive = 1');
if (!empty($locale)) {
$videos = $videos->andWhere('n.locale = :locale');
$videos = $videos->setParameter('locale', $locale);
}
if (!empty($minUpdateDate)) {
$videos = $videos->andWhere('n.updated >= :minUpdateDate');
$videos = $videos->setParameter('minUpdateDate', $minUpdateDate);
}
if (!empty($maxUpdateDate)) {
$videos = $videos->andWhere('n.updated <= :maxUpdateDate');
$videos = $videos->setParameter('maxUpdateDate', $maxUpdateDate);
}
if (!empty($category)) {
$videos = $videos->andWhere('c.id = :category');
$videos = $videos->setParameter('category', $category);
}
if (!empty($sortBy)) {
$order = $sortDirection === 'desc' ? 'desc' : 'asc';
if ($sortBy === 'created_datetime') {
$videos = $videos->orderBy('n.created', $order);
}
if ($sortBy === 'updated_datetime') {
$videos = $videos->orderBy('n.updated', $order);
}
if ($sortBy === 'publication_date') {
$videos = $videos->orderBy('n.publicationDate', $order);
}
}
$videosTotalResults = $videos->getQuery()
->getResult();
$videosTotalCount = empty($videosTotalResults) ? 0 : count($videosTotalResults);
if (empty($perPage)) {
$videos = $videosTotalResults;
} else {
$videos = $videos->setFirstResult($perPage * ($page - 1))
->setMaxResults($perPage)
->getQuery()
->getResult();
}
if(!empty($videos)){
return [
'items' => $videos,
'totalCount' => $videosTotalCount,
];
}
return [
'items' => [],
'totalCount' => $videosTotalCount,
];
}
}

src/Notaris/AdminBundle/Entity/VideoRepository.php line 361