Symfony Profiler

<?php
namespace Notaris\AdminBundle\Entity;
use Doctrine\ORM\EntityRepository;
/**
* FaqRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class FaqRepository extends EntityRepository
{
/**
* @param $locale
* @param int $offset
* @param array $sort_arr
* @param int $limit
* @return array|bool
*/
public function getAllFaq($locale, $offset = 0, $sort_arr = array(), $limit = 50)
{
//start orders
$orders_arr = array();
$order_type = $sort_arr['type'];
$order_columns = array(
0 => 'f.pub_date',
'f.title',
'f.count_clicked',
'subsites',
'is_active',
'f.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 f.id, f.pub_date, f.title, f.count_clicked,
IF (f.is_active = 1,(IF(f.pub_date <= NOW(), f.is_active, 2)),f.is_active) AS is_active,
f.news_id, f.is_general,
GROUP_CONCAT(DISTINCT s.name ORDER BY bs.position ASC SEPARATOR '|') as subsites
FROM faq f
LEFT JOIN faq_subsite bs ON bs.faq_id = f.id
LEFT JOIN subsite s ON s.id = bs.subsite_id and s.is_active = 1
WHERE f.locale = :locale
GROUP BY f.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 faq f WHERE f.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 faq f WHERE f.is_active = 1 AND f.pub_date <= :now AND f.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 faq f WHERE f.is_active = 1 AND f.pub_date > :now AND f.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 faq f WHERE f.is_active = 0 AND f.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;
// //LEFT JOIN NotarisAdminBundle:FaqSubsite fs WITH fs.faq = f.id
// //LEFT JOIN NotarisAdminBundle:News n WITH n.id = f.news
// $query = $this->getEntityManager()
// ->createQuery('SELECT f FROM
// NotarisAdminBundle:Faq f
// WHERE f.locale = :locale
// ORDER BY f.publicationDate DESC')
//
// ->setParameter('locale', $locale);
//
// $tags = $query->getResult();
//
// if($tags){
// return $tags;
// }
// return false;
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListFaqSubsite($locale, $subsite_id)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Faq b
INNER JOIN NotarisAdminBundle:FaqSubsite fs WITH fs.faq = b.id
WHERE b.locale = :locale AND fs.subsite = :subsite_id
ORDER BY fs.position ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('subsite_id', $subsite_id);
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListFaqSubsitePublic($locale, $subsite_id)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Faq b
INNER JOIN NotarisAdminBundle:FaqSubsite fs WITH fs.faq = b.id
WHERE b.locale = :locale AND fs.subsite = :subsite_id
AND b.isActive = 1 AND b.publicationDate <= :now
ORDER BY fs.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;
}
public function getListFaqFromSubsite($subsite_id)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Faq b
INNER JOIN NotarisAdminBundle:FaqSubsite fs WITH fs.faq = b.id
WHERE fs.subsite = :subsite_id'
)
->setParameter('subsite_id', $subsite_id);
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
/**
* @param $locale
* @param $subsite_id
*/
public function getRandomFaqSubsitePublic($locale, $subsite_id, $filterShowSubsite = false)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Faq b
INNER JOIN NotarisAdminBundle:FaqSubsite fs WITH fs.faq = b.id
WHERE b.locale = :locale AND fs.subsite = :subsite_id
AND b.isActive = 1 AND b.publicationDate <= :now' .
($filterShowSubsite ? ' AND b.showInSubsite = 1' : '') . '
ORDER BY fs.position ASC, b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'))
->setParameter('subsite_id', $subsite_id);
$faqs = $query->getResult();
if ($faqs) {
shuffle($faqs);
return array_shift($faqs);
}
return false;
}
/**
* @param $locale
* @param $subsite_id
* @return array|bool
*/
public function getListFaqGeneralPublic($locale)
{
$query = $this->getEntityManager()
->createQuery(
'SELECT b FROM
NotarisAdminBundle:Faq b
WHERE b.locale = :locale AND b.isGeneral = 1
AND b.isActive = 1 AND b.publicationDate <= :now
ORDER BY b.publicationDate DESC'
)
->setParameter('locale', $locale)
->setParameter('now', new \DateTime('now'));
$tags = $query->getResult();
if ($tags) {
return $tags;
}
return false;
}
public function getRandomFaq($locale)
{
$em = $this->getEntityManager();
$con = $em->getConnection();
$sql = "SELECT f.title, f.slug as faqslug, sub.slug FROM faq as f INNER JOIN faq_subsite as s on f.id = s.faq_id INNER JOIN subsite as sub ON s.subsite_id = sub.id WHERE f.locale = :locale and f.is_active = 1 and f.pub_date <= NOW() ORDER BY rand() limit 1";
$stmt = $con->prepare($sql);
$stmt->bindValue('locale', $locale);
$stmt = $stmt->execute();
$result = $stmt->fetchAll();
if ($result) {
return array_shift($result);
}
return false;
}
public function search($keyword, $locale='nl') {
$searchResults = $this->createQueryBuilder('f')
->select('f')
->addSelect("MATCH_AGAINST (f.title, f.subtitle, f.answer, :address 'IN BOOLEAN MODE') as score")
->where('f.locale = :locale AND f.publicationDate <= :now AND f.isActive = 1')
->andWhere("MATCH_AGAINST(f.title, f.subtitle, f.answer, :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/FaqRepository.php line 162