Symfony Profiler

<?php
namespace Notaris\AdminBundle\Entity;
use Doctrine\ORM\EntityRepository;
use Notaris\AdminBundle\ValueObject\BrochureType;
/**
* BrochureRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class BrochureRepository extends EntityRepository
{
/**
* @param $locale
* @param int $offset
* @param array $sort_arr
* @param int $limit
* @return array|bool
*/
public function getAllBrochures($locale, $offset = 0, $sort_arr = array(), $limit = 50)
{
//start orders
$orders_arr = array();
$order_type = $sort_arr['type'];
$order_columns = array(
0 => 'b.pub_date',
'b.title',
'b.is_izimi',
'b.count_clicked',
'primary_subsite_name',
'is_active',
'b.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 b.id, b.pub_date, b.title, b.count_clicked, b.is_izimi,
IF (b.is_active = 1,(IF(b.pub_date <= NOW(), b.is_active, 2)),b.is_active) AS is_active,
b.is_general, b.is_general_primary,
IF(b.is_general = 1 and is_general_primary = 1, 'General', sp.name) as primary_subsite_name, b.news_id, bs.brochure,
GROUP_CONCAT(DISTINCT s.name ORDER BY bs.position ASC SEPARATOR '|') as subsites
FROM brochure b
LEFT JOIN brochure_subsite bs ON bs.brochure = b.id
LEFT JOIN subsite s ON s.id = bs.subsite_id and s.is_active = 1
LEFT JOIN subsite sp ON sp.id = b.primary_subsite and sp.is_active = 1
WHERE b.locale = :locale
GROUP BY b.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 brochure b WHERE b.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 brochure b WHERE b.is_active = 1 AND b.pub_date <= :now AND b.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 brochure b WHERE b.is_active = 1 AND b.pub_date > :now AND b.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 brochure b WHERE b.is_active = 0 AND b.locale = :locale";
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$offline = $stmt->fetchOne();
}
if ($result) {
return array(
'result' => $result,
'count' => $count,
'online' => $online,
'offline' => $offline,
'scheduled' => $scheduled
);
}
return false;
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListBrochuresSubsite($locale, $subsite_id)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Brochure b
INNER JOIN NotarisAdminBundle:BrochureSubsite bs WITH bs.brochure = b.id
WHERE b.locale = :locale AND bs.subsite = :subsite_id
ORDER BY bs.position ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('subsite_id', $subsite_id);
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
public function getAllBrochuresSubsite($subsite_id)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Brochure b
INNER JOIN NotarisAdminBundle:BrochureSubsite bs WITH bs.brochure = b.id
WHERE bs.subsite = :subsite_id'
)
->setParameter('subsite_id', $subsite_id);
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListBrochuresSubsitePublic($locale, $subsite_id, BrochureType $brochureType = null)
{
if ($brochureType instanceof BrochureType) {
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Brochure b
INNER JOIN NotarisAdminBundle:BrochureSubsite bs WITH bs.brochure = b.id
WHERE b.locale = :locale AND bs.subsite = :subsite_id AND b.isActive = 1 AND b.publicationDate <= :now AND b.brochureType = :brochureType
ORDER BY bs.position ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('subsite_id', $subsite_id)
->setParameter('brochureType', (string) $brochureType);
} else {
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Brochure b
INNER JOIN NotarisAdminBundle:BrochureSubsite bs WITH bs.brochure = b.id
WHERE b.locale = :locale AND bs.subsite = :subsite_id AND b.isActive = 1 AND b.publicationDate <= :now
ORDER BY bs.position ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('subsite_id', $subsite_id);
}
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
/**
* @param $locale
* @return array|bool
*/
public function getListBrochuresIsGeneral($locale)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Brochure b
WHERE b.locale = :locale AND b.isGeneral = :is_general
ORDER BY b.generalPosition ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('is_general', 1);
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
/**
* @param $locale
* @return array|bool
*/
public function getListBrochuresIsGeneralPublic($locale, BrochureType $brochureType = null){
if ($brochureType instanceof BrochureType) {
$query = $this->getEntityManager()
->createQuery('SELECT b FROM
NotarisAdminBundle:Brochure b
WHERE b.locale = :locale AND b.isGeneral = :is_general AND b.isActive = 1 AND b.publicationDate <= :now AND b.brochureType = :brochureType
ORDER BY b.generalPosition ASC, b.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('is_general', 1)
->setParameter('brochureType', $brochureType->getType());
} else {
$query = $this->getEntityManager()
->createQuery('SELECT b FROM
NotarisAdminBundle:Brochure b
WHERE b.locale = :locale AND b.isGeneral = :is_general AND b.isActive = 1 AND b.publicationDate <= :now
ORDER BY b.generalPosition ASC, b.publicationDate DESC')
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('is_general', 1);
}
$tags = $query->getResult();
if($tags){
return $tags;
}
return false;
}
public function getAllIzimiBrochures($page, $perPage, $locale = null, $minUpdateDate = null, $maxUpdateDate = null, $sortBy = null, $sortDirection = null, $category = null)
{
$brochures = $this->createQueryBuilder('n');
if (!empty($category)) {
$brochures = $brochures->leftJoin('n.categories', 'c');
}
$brochures = $brochures->where('n.isIzimi = 1 AND n.isActive = 1');
if (!empty($locale)) {
$brochures = $brochures->andWhere('n.locale = :locale');
$brochures = $brochures->setParameter('locale', $locale);
}
if (!empty($minUpdateDate)) {
$brochures = $brochures->andWhere('n.updated >= :minUpdateDate');
$brochures = $brochures->setParameter('minUpdateDate', $minUpdateDate);
}
if (!empty($maxUpdateDate)) {
$brochures = $brochures->andWhere('n.updated <= :maxUpdateDate');
$brochures = $brochures->setParameter('maxUpdateDate', $maxUpdateDate);
}
if (!empty($category)) {
$brochures = $brochures->andWhere('c.id = :category');
$brochures = $brochures->setParameter('category', $category);
}
if (!empty($sortBy)) {
$order = $sortDirection === 'desc' ? 'desc' : 'asc';
if ($sortBy === 'created_datetime') {
$brochures = $brochures->orderBy('n.created', $order);
}
if ($sortBy === 'updated_datetime') {
$brochures = $brochures->orderBy('n.updated', $order);
}
if ($sortBy === 'publication_date') {
$brochures = $brochures->orderBy('n.publicationDate', $order);
}
}
$brochuresTotalResults = $brochures->getQuery()
->getResult();
$brochuresTotalCount = empty($brochuresTotalResults) ? 0 : count($brochuresTotalResults);
if (empty($perPage)) {
$brochures = $brochuresTotalResults;
} else {
$brochures = $brochures->setFirstResult($perPage * ($page - 1))
->setMaxResults($perPage)
->getQuery()
->getResult();
}
if(!empty($brochures)){
return [
'items' => $brochures,
'totalCount' => $brochuresTotalCount,
];
}
return [
'items' => [],
'totalCount' => $brochuresTotalCount,
];
}
}

src/Notaris/AdminBundle/Entity/BrochureRepository.php line 167