Symfony Profiler

<?php
namespace Slivki\Repository;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Slivki\Entity\Category;
use Slivki\Entity\City;
use Slivki\Entity\Comment;
use Slivki\Entity\Director;
use Slivki\Entity\EntityVote;
use Slivki\Entity\MainMenu;
use Slivki\Entity\MallBrand;
use Slivki\Entity\Offer;
use Slivki\Entity\Sale;
use Slivki\Entity\Seo;
/**
* @deprecated Use CommentRepositoryInterface. Refactoring required.
*/
class CommentRepository extends EntityRepository
{
const PG_SERIAL_MAX_VALUE = 2147483647;
const CACHE_NAME = 'details-comments-3-';
const MOBILE_CACHE_NAME = 'mobile-details-comment-3-';
const COMMENTS_PER_PAGE = 20;
public const COMMENTS_FOR_DIRECTOR_RATING_PERIOD_IN_DAYS = 90;
public function getCommentsByEntityID($entityID, $limit, $lastCommentID = self::PG_SERIAL_MAX_VALUE) {
if (!$lastCommentID) {
$lastCommentID = self::PG_SERIAL_MAX_VALUE;
}
$dql = "select comment, user from Slivki:Comment comment
join comment.user user
where comment.entityID = :entityID
and comment.hidden != true
and comment.parentID is null
and comment.ID < :lastCommentID
and comment.confirmedPhone = true
order by comment.createdOn desc";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', $entityID)
->setParameter('lastCommentID', $lastCommentID);
$query->setMaxResults($limit);
return $query->getResult();
}
public function getCommentsByCategoryID($categoryID, $limit, $lastCommentID = self::PG_SERIAL_MAX_VALUE) {
if (!$lastCommentID) {
$lastCommentID = self::PG_SERIAL_MAX_VALUE;
}
$categoryCached = $this->getEntityManager()->getRepository(Category::class)->findCached($categoryID);
$dql = "select comment, user, media, likes from Slivki:Comment comment
left join comment.likes likes
left join comment.medias media
join comment.user user
where comment.entityID in (:entityID)
and comment.typeID = :typeID
and comment.hidden != true
and comment.ID < :lastCommentID
and comment.confirmedPhone = true
order by comment.createdOn desc";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', array_values($categoryCached['entityList']))
->setParameter('typeID', Comment::TYPE_OFFER_COMMENT)
->setParameter('lastCommentID', $lastCommentID);
$query->setMaxResults($limit);
return $query->getResult();
}
public function getOfferCategoryComments($categoryID, $page, $perPage = 10) {
$offerIDList = $this->getEntityManager()->getRepository(Offer::class)->getOfferIDListByCategory($categoryID);
$dql = "select comment, user from Slivki:Comment comment
join comment.user user
where comment.entityID in (:offerIDList)
and comment.typeID = :typeID
and (comment.hidden = false or comment.hidden is null)
and (comment.deleted = false or comment.deleted is null)
and comment.confirmedPhone = true
order by comment.ID desc";
return $this->getEntityManager()->createQuery($dql)
->setParameter('typeID', Comment::TYPE_OFFER_COMMENT)
->setParameter('offerIDList', $offerIDList)
->setMaxResults($perPage)
->setFirstResult($perPage * ($page - 1))
->getResult();
}
public function getOfferCategoryCommentsCount($categoryID) {
$sql = "select count(*) from comment
inner join customer on customer.id = comment.user_id
where entity_id in (select entity_id from category2entity
where category_id = $categoryID) and type_id = " . Comment::TYPE_OFFER_COMMENT . "
and (hidden is null or not hidden) and (deleted is null or not deleted) and confirmed_phone";
return $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn();
}
public function getCommentsByDirectorID($directorID, $limit = self::COMMENTS_PER_PAGE, $lastCommentID = null) {
$director = $this->getEntityManager()->getRepository(Director::class)->find($directorID);
if (!$director) {
return [];
}
$directorEntityIDList = $director->getEntityIDList();
if (empty($directorEntityIDList)) {
return [];
}
if (!$lastCommentID) {
$lastCommentID = self::PG_SERIAL_MAX_VALUE;
}
$dql = "select comment, media from Slivki:Comment comment
left join comment.medias media
where comment.entityID in (:directorEntityIDList) and (comment.hidden = false or comment.hidden is null)
and comment.confirmedPhone = true
and comment.typeID in (:typeIDs) and comment.ID < :lastCommentID
order by comment.createdOn desc";
$comments = $this->getEntityManager()->createQuery($dql)
->setParameter('directorEntityIDList', $directorEntityIDList)
->setParameter('typeIDs', [Comment::TYPE_OFFER_COMMENT, Comment::TYPE_SALE_COMMENT])
->setParameter('lastCommentID', $lastCommentID)
->setMaxResults($limit)
->getResult();
return $comments;
}
public function getCommentsByOfferIDReversed($offerID, $offset, $limit = null) {
$dql = "select comment, user from Slivki:Comment comment
join comment.user user
where comment.entityID = :offerID
and comment.typeID = :typeID
and comment.hidden != true
and comment.confirmedPhone = true
and comment.parentID is null
and (comment.deleted is null or comment.deleted = false)
order by comment.createdOn desc";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('typeID', Comment::TYPE_OFFER_COMMENT)
->setParameter('offerID', $offerID)
->setFirstResult($offset);
if ($limit) {
$query->setMaxResults($limit);
}
return $query->getResult();
}
public function getLiveComments(int $limit, ?int $lastCommentID = null): array
{
if (null === $lastCommentID) {
$lastCommentID = self::PG_SERIAL_MAX_VALUE;
}
$qb = $this->createQueryBuilder('comment');
$expr = $qb->expr();
return $qb
->addSelect('user')
->innerJoin('comment.user','user')
->innerJoin(Offer::class, 'offer', Join::WITH, $expr->eq('offer.ID', 'comment.entityID'))
->andWhere($expr->eq('comment.hidden', ':hidden'))
->andWhere($expr->eq('comment.confirmedPhone', ':confirmedPhone'))
->andWhere($expr->neq('offer.defaultCity', ':tashkentCityId'))
->andWhere(
$expr->orX(
$expr->isNull('comment.hiddenEntity'),
$expr->eq('comment.hiddenEntity', ':hiddenEntity')
)
)
->andWhere($expr->lt('comment.ID', ':lastCommentID'))
->setParameters([
'hidden' => false,
'hiddenEntity' => false,
'confirmedPhone' => true,
'tashkentCityId' => City::TASHKENT_CITY_ID,
'lastCommentID' => $lastCommentID,
])
->addOrderBy($expr->desc('comment.createdOn'))
->setMaxResults($limit)
->getQuery()
->getResult()
;
}
public function updateCommentEntityHidden($entityID, $typeID, $entityHidden) {
$this->getEntityManager()->getConnection()
->executeQuery("update comment set hidden_entity = $entityHidden where type_id = $typeID and entity_id = $entityID");
}
public function getNotSeenByDirectorOfferComments($offerID, $userID, $limit) {
$dql = "select comment, media from Slivki:Offer offer
join Slivki:Comment comment with comment.entityID = offer.ID
left join comment.medias media
where comment.typeID = :commentTypeID and comment.userID != :userID and offer.ID = :offerID
and (comment.seenBySupplier = false or comment.seenBySupplier is null)
and (comment.hidden = false or comment.hidden is null) and comment.confirmedPhone = true
order by comment.createdOn desc";
return $this->getEntityManager()->createQuery($dql)
->setParameter('offerID', $offerID)
->setParameter('userID', $userID)
->setParameter('commentTypeID', Comment::TYPE_OFFER_COMMENT)
->setMaxResults($limit)
->getResult();
}
public function getNotSeenOfferCommentsByDirectorID($directorID, $userID, $limit) {
$dql = "select comment, media from Slivki:Director director
join director.offers offer
join Slivki:Comment comment with comment.entityID = offer.ID
left join comment.medias media
where director.ID in (:directorID) and comment.typeID = :commentTypeID and comment.userID != :userID
and (comment.seenBySupplier = false or comment.seenBySupplier is null)
and (comment.hidden = false or comment.hidden is null) and comment.confirmedPhone = true
order by comment.createdOn desc";
$directorRepository = $this->getEntityManager()->getRepository(Director::class);
$director = $directorRepository->find($directorID);
$partners = $directorRepository->findBy(['email' => $director->getEmail()]);
$partnerList = [];
foreach ($partners as $partner) {
$partnerList[] = $partner->getID();
}
$comments = $this->getEntityManager()->createQuery($dql)
->setParameter('directorID', $partnerList)
->setParameter('userID', $userID)
->setParameter('commentTypeID', Comment::TYPE_OFFER_COMMENT)
->setMaxResults($limit)
->getResult();
return $comments;
}
public function getDirectorsByOfferComment(Comment $comment) {
if($comment->getTypeID() != Comment::TYPE_OFFER_COMMENT) {
return false;
}
$offer = $this->getEntityManager()->getRepository(Offer::class)->find($comment->getEntityID());
return $offer->getDirectors()->toArray();
}
public function isUserAllowedToRate($userID, $entityID, $typeID) {
$dql = "select max(comment.createdOn) from Slivki:Comment comment
where comment.userID = :userID
and comment.entityID = :offerID
and comment.typeID = :typeID
and comment.rating > 0";
$userLastRatedCommentCreatedOn = $this->getEntityManager()->createQuery($dql)
->setParameter('userID', $userID)
->setParameter('offerID', $entityID)
->setParameter('typeID', $typeID)
->getSingleResult();
if(!$userLastRatedCommentCreatedOn[1]) {
return true;
}
return (strtotime($userLastRatedCommentCreatedOn[1]) < strtotime('-7 days'));
}
/**
* @deprecated use CommentDaoInterface
*/
public function getCommentsCountByEntityID($entityID, $typeID) {
$dql = "select count(comment) as commentAmount
from Slivki:Comment as comment
where comment.entityID = :entityID
and comment.typeID = :typeID
and comment.confirmedPhone = true
and comment.hidden != true and (comment.deleted != true or comment.deleted is null)";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', $entityID)
->setParameter('typeID', $typeID);
$result = $query->getScalarResult();
return $result[0]['commentAmount'];
}
public function getRootCommentsCountByEntityID($entityID, $typeID) {
$dql = "select count(comment) as commentAmount
from Slivki:Comment as comment
where comment.entityID = :entityID
and comment.typeID = :typeID
and comment.confirmedPhone = true
and comment.hidden != true and (comment.deleted != true or comment.deleted is null) and comment.parent is null";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', $entityID)
->setParameter('typeID', $typeID);
$result = $query->getScalarResult();
return $result[0]['commentAmount'];
}
public function getCommentsCountByUserID($userID, $entityID, $typeID) {
$dql = "select count(comment) as commentAmount
from Slivki:Comment as comment
where comment.entityID = :entityID
and comment.typeID = :typeID
and comment.userID = :userID
and comment.confirmedPhone = true
and comment.hidden != true ";
$query = $this->getEntityManager()->createQuery($dql)
->setParameter('entityID', $entityID)
->setParameter('typeID', $typeID)
->setParameter('userID', $userID);
$query->setMaxResults(1);
$result = $query->getScalarResult();
return $result[0]['commentAmount'];
}
public function getCommentEntity($entityID, $typeID) {
switch($typeID) {
case Comment::TYPE_OFFER_COMMENT:
$repositoryName = Offer::class;
break;
case Comment::TYPE_SALE_COMMENT:
$repositoryName = Sale::class;
break;
case Comment::TYPE_MALL_BRAND_COMMENT:
$repositoryName = MallBrand::class;
break;
case Comment::TYPE_DIRECTOR_COMMENT:
$repositoryName = Director::class;
break;
default:
$repositoryName = '';
break;
}
if ($repositoryName == '') {
return false;
}
return $this->getEntityManager()->getRepository($repositoryName)->find($entityID);
}
public function getTopMenu() {
$seoRepository = $this->getEntityManager()->getRepository(Seo::class);
$mainMenu = $this->getEntityManager()->getRepository(MainMenu::class)->getItemListCached(1, 1);
$removeMenuItemIDList = [23, 14, 3];
foreach($mainMenu as $key => $item) {
if($item->getType() != MainMenu::TYPE_OFFER_CATEGORY || in_array($item->getEntityID(), $removeMenuItemIDList)) {
unset($mainMenu[$key]);
continue;
}
$seo = $seoRepository->getByEntity(SeoRepository::RESOURCE_URL_CATEGORY_COMMENTS, $item->getEntityID());
$item->setURL($seo->getMainAlias());
}
return array_values($mainMenu);
}
public function getEntityRating($entityType, $entityID, $dateFrom = false, $dateTo = false) {
$dateCondition = "";
if ($dateFrom) {
$dateCondition = " and created_on between '$dateFrom' and '$dateTo'";
}
$sql = "select avg(rating) as rating from comment where type_id=$entityType and entity_id=$entityID and rating > 0 and checked and confirmed_phone and not hidden $dateCondition";
$result = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetchColumn(0);
return $result ? round($result, 2) : 0;
}
public function getEntityRatingWithCount($entityType, $entityID, $dateFrom = false, $dateTo = false) {
$dateCondition = "";
if ($dateFrom) {
$dateCondition = " and created_on between '$dateFrom' and '$dateTo'";
}
$sql = "select avg(rating) as rating, count(*) as amount from comment where created_on > date_trunc('day', now() + '-90 days')::timestamp without time zone and type_id=$entityType and entity_id=$entityID and rating > 0 and confirmed_phone and not hidden $dateCondition";
$dbConnection = $this->getEntityManager()->getConnection();
$result = $dbConnection->executeQuery($sql)->fetch(\PDO::FETCH_ASSOC);
if (!$result) {
return ['rating' => 0, 'amount' => 0, 'ratingCount' => 0];
}
$result['ratingCount'] = $result['amount'];
$sql = "select count(*) as amount from comment where type_id=$entityType and entity_id=$entityID and confirmed_phone and not hidden";
$countAll = $dbConnection->executeQuery($sql)->fetchColumn(0);
$result['amount'] = $countAll;
if ($entityType == Category::SALE_CATEGORY_ID) {
$sql = 'select count(*) as amount, coalesce(avg(vote), 0) as rating from entity_vote where entity_id = ' . $entityID . ' and entity_type_id = ' . EntityVote::TYPE_SALE . $dateCondition;
$voteStatistic = $dbConnection->executeQuery($sql)->fetchAll(\PDO::FETCH_ASSOC);
$voteStatistic = $voteStatistic[0];
$totalAmount = $voteStatistic['amount'] + $result['ratingCount'];
if ($totalAmount > 0) {
$totalRating = ($voteStatistic['amount'] * $voteStatistic['rating'] + $result['ratingCount'] * $result['rating']) / $totalAmount;
$result['amount'] = $voteStatistic['amount'] + $result['amount'];
$result['rating'] = $totalRating;
$result['ratingCount'] = $totalAmount;
}
}
return $result['amount'] > 0 ? $result : ['rating' => 0, 'amount' => 0, 'ratingCount' => 0];
}
public function getCategoryRatingForPeriod($category, $dateFrom, $dateTo) {
$sql = "select avg(comment.rating) as rating, count(*) as amount from comment inner join offer on comment.entity_id = offer.id
inner join category2entity on offer.id = category2entity.entity_id where comment.rating > 0 and comment.checked and comment.confirmed_phone = true and not comment.hidden
and category2entity.category_id = " . $category->getID() . ' and comment.type_id = ' . $category->getDomainObjectID() . "
and comment.created_on between '$dateFrom' and '$dateTo'";
$result = $this->getEntityManager()->getConnection()->executeQuery($sql)->fetch(\PDO::FETCH_ASSOC);
return $result ? $result : ['rating' => 0, 'amount' => 0];
}
}

src/Repository/CommentRepository.php line 49