Symfony Profiler

<?php
namespace Notaris\AdminBundle\Entity;
use Doctrine\ORM\EntityRepository;
/**
* GlossaryRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class GlossaryRepository extends EntityRepository
{
/**
* @param $locale
* @param $filterLetter
* @param int $offset
* @param array $sort_arr
* @param int $limit
* @return array|bool
* @throws \Doctrine\DBAL\DBALException
*/
public function getAllGlossaries($locale, $filterLetter, $offset = 0, $sort_arr = array(), $limit = 50)
{
//start orders
$orders_arr = array();
$order_type = $sort_arr['type'];
$order_columns = array(
0 => 'g.pub_date',
'g.term',
'subsites',
'is_active',
);
if (isset($order_columns[$sort_arr['column']]) && $order_columns[$sort_arr['column']]) {
$orders_arr[] = $order_columns[$sort_arr['column']] . ' ' . $order_type;
}
//end orders
$where_arr = array(1);
//filter letter
if ($filterLetter) {
$where_arr[] = 'g.letter = :letter';
}
$em = $this->getEntityManager();
$con = $em->getConnection();
$sql = "SELECT g.id, g.pub_date, g.term,
IF (g.is_active = 1,(IF(g.pub_date <= NOW(), g.is_active, 2)),g.is_active) AS is_active,
g.slug,
GROUP_CONCAT(DISTINCT s.name ORDER BY s.position ASC SEPARATOR '|') as subsites
FROM glossary g
LEFT JOIN glossary_subsite gs ON gs.glossary_id = g.id
LEFT JOIN subsite s ON s.id = gs.subsite_id and s.is_active = 1
WHERE g.locale = :locale AND " . implode(' AND ', $where_arr) . "
GROUP BY g.id
ORDER BY " . implode(', ', $orders_arr) . '
LIMIT ' . $offset . ', ' . $limit;
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
if ($filterLetter) {
$stmt->bindValue('letter', $filterLetter);
}
$stmt = $stmt->execute();
$result = $stmt->fetchAll();
//all count
$sql = "SELECT count(*) as count FROM glossary g WHERE g.locale = :locale AND " . implode(' AND ', $where_arr);
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
if ($filterLetter) {
$stmt->bindValue('letter', $filterLetter);
}
$stmt = $stmt->execute();
$count = $stmt->fetchOne();
$AvailableLetters = array();
$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 glossary g WHERE g.is_active = 1 AND g.pub_date <= :now AND g.locale = :locale AND " . implode(
' AND ',
$where_arr
);
$stmt = $con->prepare($sql);
$stmt->bindValue('now', $now);
$stmt->bindValue('locale', $locale);
if ($filterLetter) {
$stmt->bindValue('letter', $filterLetter);
}
$stmt = $stmt->execute();
$online = $stmt->fetchOne();
//scheduled
$sql = "SELECT count(*) as count FROM glossary g WHERE g.is_active = 1 AND g.pub_date > :now AND g.locale = :locale AND " . implode(
' AND ',
$where_arr
);
$stmt = $con->prepare($sql);
$stmt->bindValue('now', $now);
$stmt->bindValue('locale', $locale);
if ($filterLetter) {
$stmt->bindValue('letter', $filterLetter);
}
$stmt = $stmt->execute();
$scheduled = $stmt->fetchOne();
//offline
$sql = "SELECT count(*) as count FROM glossary g WHERE g.is_active = 0 AND g.locale = :locale AND " . implode(
' AND ',
$where_arr
);
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
if ($filterLetter) {
$stmt->bindValue('letter', $filterLetter);
}
$stmt = $stmt->execute();
$offline = $stmt->fetchOne();
}
//first init
//available letters
$sql = "SELECT DISTINCT g.letter FROM glossary g WHERE g.locale = :locale ORDER BY g.letter ASC";
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$AvailableLetters = $stmt->fetchAll();
return array(
'result' => $result,
'count' => $count,
'online' => $online,
'offline' => $offline,
'scheduled' => $scheduled,
'AvailableLetters' => $AvailableLetters,
);
}
public function getLetters($locale)
{
$em = $this->getEntityManager();
$conn = $em->getConnection();
$stmt = $conn->prepare('SELECT DISTINCT letter FROM glossary WHERE locale like :locale ORDER BY letter');
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$letters = $stmt->fetchAll();
if (sizeof($letters) > 0):
$new_letters = array();
foreach ($letters as $l):
$new_letters[] = $l['letter'];
endforeach;
return $new_letters;
endif;
return false;
}
public function getAllGlossariesByLetter($locale, $letter= 'A')
{
if(!$letter){
$letter= 'A';
}
$glossaries = $this->createQueryBuilder('g')
->select('g')
->where('g.locale = :locale AND g.letter like :letter')
->andWhere('g.isActive = true')
->andWhere('g.publicationDate <= :now')
->setParameter('now', new \DateTime('now'))
->setParameter('locale', $locale)
->setParameter('letter', $letter)
->orderBy("g.term", "ASC")
->getQuery()
->getResult();
if ($glossaries) {
return $glossaries;
}
return false;
}
public function getAllGlossarieItems($locale)
{
$glossaries = $this->createQueryBuilder('g')
->select('g')
->where('g.locale = :locale AND g.isActive = true')
->andWhere('g.publicationDate <= :now')
->setParameter('now', new \DateTime('now'))
->setParameter('locale', $locale)
->orderBy("g.term", "ASC")
->getQuery()
->getResult();
if ($glossaries) {
return $glossaries;
}
return false;
}
public function getRandomGlossaryFromSubsite($locale, Subsite $subsite)
{
$glossaries = $this->createQueryBuilder('g')
->select('g')
->innerJoin('g.subsites', 's', 'WITH', 's.id = :subsite')
->where('g.locale = :locale')
->andWhere('g.isActive = true')
->andWhere('g.publicationDate <= :now')
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('subsite', $subsite->getId())
->orderBy("g.term", "ASC")
->getQuery()
->getResult();
if ($glossaries) {
shuffle($glossaries);
return array_shift($glossaries);
}
return false;
}
public function getGlossaryFromSubsite(Subsite $subsite)
{
$glossaries = $this->createQueryBuilder('g')
->select('g')
->innerJoin('g.subsites', 's', 'WITH', 's.id = :subsite')
->setParameter('subsite', $subsite->getId())
->getQuery()
->getResult();
if ($glossaries) {
return $glossaries;
}
return false;
}
public function search($keyword, $locale='nl') {
$searchResults = $this->createQueryBuilder('g')
->addSelect("MATCH_AGAINST (g.term, g.description, :address 'IN BOOLEAN MODE') as score")
->where('g.locale = :locale AND g.publicationDate <= :now AND g.isActive = 1')
->andWhere("MATCH_AGAINST(g.term, g.description, :address 'IN BOOLEAN MODE') > 0.2")
->setParameter('address', $keyword)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->orderBy('score', 'desc')
->getQuery()
->getResult();
if($searchResults){
return $searchResults;
}
return false;
}
}

src/Notaris/AdminBundle/Entity/GlossaryRepository.php line 225