<?php
namespace App\Repository;
use App\Entity\Video;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method Video|null find($id, $lockMode = null, $lockVersion = null)
* @method Video|null findOneBy(array $criteria, array $orderBy = null)
* @method Video[] findAll()
* @method Video[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class VideoRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Video::class);
}
public function getEntityManager()
{
return parent::getEntityManager();
}
public function save(Video $video)
{
$this->_em->persist($video);
$this->_em->flush();
}
public function delete(Video $video)
{
$this->_em->remove($video);
$this->_em->flush();
}
public function getVideos(?string $filter): QueryBuilder
{
$qb = $this->createQueryBuilder('v');
$qb->andWhere($qb->expr()->like('v.url', ':filter'));
$qb->setParameter('filter', '%'.$filter.'%');
return $qb;
}
public function getNumberOfVideosPerDay($day, $month, $year)
{
$day = str_pad($day, 2, '0', STR_PAD_LEFT);
$month = str_pad($month, 2, '0', STR_PAD_LEFT);
$str = '%' . $year.'-'.$month.'-'.$day . '%';
$qb = $this->createQueryBuilder('v')
->select('COUNT(DISTINCT v.id) AS count')
->where('v.createdAt LIKE :str');
$qb->setParameter('str', $str);
return $qb->getQuery()->getResult();
}
public function getNumberOfVideosPerMonth(int $year, int $month)
{
$str = '\'' . $year.'-'.$month.'-01' . '\' and \'' . $year.'-'.$month.'-31' . '\'';
$qb = $this->createQueryBuilder('v')
->select('COUNT(DISTINCT v.id) AS count')
->where('v.createdAt between ' . $str);
return $qb->getQuery()->getResult();
}
public function getYearsOfVideos(): array
{
$qb = $this->createQueryBuilder('v')
->select('DISTINCT(YEAR(v.createdAt)) AS y');
return $qb->getQuery()->getArrayResult();
}
public function getStorageOfVideosPerMonth(string $year, int $month)
{
$str = '\'' . $year.'-'.$month.'-01' . '\' and \'' . $year.'-'.$month.'-31' . '\'';
$qb = $this->createQueryBuilder('v')
->select('SUM(v.size) as total')
->where('v.createdAt between ' . $str);
return $qb->getQuery()->getResult();
}
/**
* @param int $int
* @return Video[]
*/
public function findNotProcessedForPastDays(int $int): array
{
$query = $this->_em->createQuery("SELECT v FROM App\Entity\Video v WHERE v.createdAt > DATE_SUB(CURRENT_DATE(), ".$int.", 'DAY') and v.filename LIKE 'stream-%' and v.postprocessed = 0 and v.size > 4500000");
return $query->getResult();
}
/**
* @param int $int
* @return Video[]
*/
public function findNotProcessedOnlyForPastDays(int $int): array
{
$query = $this->_em->createQuery("SELECT v FROM App\Entity\Video v WHERE v.createdAt > DATE_SUB(CURRENT_DATE(), ".$int.", 'DAY') and v.filename LIKE 'stream-%' and v.chromeBugReady = 0");
return $query->getResult();
}
public function getAllUploadVideos(): array
{
$qb = $this->createQueryBuilder('v')
->where('v.filename LIKE :param')
->andWhere('v.createdAt > :date')
->setParameter('param', 'upload_%')
->setParameter('date', new DateTime('2021-03-16 06:00:00'))
->orderBy('v.createdAt', 'desc');
return $qb->getQuery()->getResult();
}
public function getDeviceTypeData()
{
$qb = $this->createQueryBuilder('v')
->select("SUM(case when v.deviceType = 'mobile' then 1 else 0 end) as mobile,
SUM(case when v.deviceType = 'desktop' then 1 else 0 end) as desktop");
return $qb->getQuery()->getResult();
}
public function getOsData()
{
$qb = $this->createQueryBuilder('v')
->select('v.os, count(v) as count')
->groupBy('v.os')
->having('count(v) > 0');
return $qb->getQuery()->getResult();
}
public function getBrowserData()
{
$qb = $this->createQueryBuilder('v')
->select('v.browser, count(v) as count')
->groupBy('v.browser')
->having('count(v) > 0');
return $qb->getQuery()->getResult();
}
}