Symfony Profiler

<?php
namespace Webkul\UVDesk\CoreFrameworkBundle\Repository;
use Doctrine\ORM\Query;
use Doctrine\Common\Collections\Criteria;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\User;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\UserInstance;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\Ticket;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportGroup;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportTeam;
use Symfony\Component\HttpFoundation\ParameterBag;
use Symfony\Component\DependencyInjection\ContainerInterface;
/**
* UserRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class UserRepository extends \Doctrine\ORM\EntityRepository
{
const LIMIT = 10;
public $safeFields = ['page', 'limit', 'sort', 'order', 'direction'];
public function getAllAgents(ParameterBag $params = null, ContainerInterface $container) {
$params = !empty($params) ? array_reverse($params->all()) : [];
$queryBuilder = $this->getEntityManager()->createQueryBuilder()
->select("user, userInstance, supportRole")
->from(User::class, 'user')
->leftJoin('user.userInstance', 'userInstance')
->leftJoin('userInstance.supportRole', 'supportRole')
->where('supportRole.id != :customerRole')->setParameter('customerRole', 4)
->orderBy('userInstance.createdAt', !isset($params['sort']) ? Criteria::DESC : Criteria::ASC);
foreach ($params as $field => $fieldValue) {
if (in_array($field, $this->safeFields))
continue;
if (!in_array($field, ['dateUpdated', 'dateAdded', 'search', 'isActive'])) {
$queryBuilder->andWhere("user.$field = :$field")->setParameter($field, $fieldValue);
} else {
if ('search' == $field) {
$queryBuilder->andWhere("CONCAT(a.firstName,' ', a.lastName) LIKE :fullName OR a.email LIKE :email")
->setParameter('fullName', '%' . urldecode(trim($fieldValue)) . '%')
->setParameter('email', '%' . urldecode(trim($fieldValue)) . '%');
} else if ('isActive' == $field) {
$queryBuilder->andWhere('userInstance.isActive = :isActive')->setParameter('isActive', $fieldValue);
}
}
}
// Pagination
$options = ['distinct' => true, 'wrap-queries' => true];
$currentPage = isset($params['page']) ? $params['page'] : 1;
$paginationQueryBuilder = clone $queryBuilder;
$totalUsers = (int) $paginationQueryBuilder->select('COUNT (DISTINCT user.id)')->getQuery()->getSingleScalarResult();
$query = $queryBuilder->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $totalUsers);
$pagination = $container->get('knp_paginator')->paginate($query, $currentPage, self::LIMIT, $options);
// Parse result
$paginationParams = $pagination->getParams();
$paginationAttributes = $pagination->getPaginationData();
$paginationParams['page'] = 'replacePage';
$paginationAttributes['url'] = '#' . $container->get('uvdesk.service')->buildPaginationQuery($paginationParams);
return [
'pagination_data' => $paginationAttributes,
'users' => array_map(function ($user) {
return [
'id' => $user['id'],
'email' => $user['email'],
'smallThumbnail' => $user['userInstance'][0]['profileImagePath'] ?: null,
'isActive' => $user['userInstance'][0]['isActive'],
'name' => ucwords(trim(implode(' ', [$user['firstName'], $user['lastName']]))),
'role' => $user['userInstance'][0]['supportRole']['description'],
'roleCode' => $user['userInstance'][0]['supportRole']['code'],
];
}, $pagination->getItems()),
];
}
public function getAllAgentsForChoice(ParameterBag $obj = null, $container)
{
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('a')->from($this->getEntityName(), 'a')
->leftJoin('a.userInstance', 'userInstance')
->leftJoin('userInstance.supportRole', 'supportRole')
->andWhere('userInstance.supportRole NOT IN (:roles)')
->setParameter('roles', [4]);
return $qb;
}
public function getAllCustomer(ParameterBag $obj = null, $container)
{
$json = array();
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('a,userInstance')->from($this->getEntityName(), 'a');
$qb->leftJoin('a.userInstance', 'userInstance');
$qb->addSelect("CONCAT(a.firstName,' ',a.lastName) AS name");
$data = $obj->all();
$data = array_reverse($data);
foreach ($data as $key => $value) {
if (! in_array($key,$this->safeFields)) {
if ($key!='dateUpdated' AND $key!='dateAdded' AND $key!='search' AND $key!='starred' AND $key!='isActive') {
$qb->andWhere('a.'.$key.' = :'.$key);
$qb->setParameter($key, $value);
} else {
if ($key == 'search') {
$qb->andWhere("CONCAT(a.firstName,' ', a.lastName) LIKE :fullName OR a.email LIKE :email");
$qb->setParameter('fullName', '%'.urldecode(trim($value)).'%');
$qb->setParameter('email', '%'.urldecode(trim($value)).'%');
} elseif ($key == 'starred') {
$qb->andWhere('userInstance.isStarred = 1');
} elseif ($key == 'isActive') {
$qb->andWhere('userInstance.isActive = :isActive');
$qb->setParameter('isActive', $value);
}
}
}
}
$qb->andWhere('userInstance.supportRole = :roles');
$qb->setParameter('roles', 4);
if (!isset($data['sort'])) {
$qb->orderBy('userInstance.createdAt',Criteria::DESC);
}
$paginator = $container->get('knp_paginator');
$newQb = clone $qb;
$newQb->select('DISTINCT a.id');
$results = $paginator->paginate(
$qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', count($newQb->getQuery()->getResult())),
isset($data['page']) ? $data['page'] : 1,
self::LIMIT,
array('distinct' => true, 'wrap-queries' => true)
);
$paginationData = $results->getPaginationData();
$queryParameters = $results->getParams();
$queryParameters['page'] = "replacePage";
$paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
$this->container = $container;
$data = array();
foreach ($results as $key => $customer) {
$data[] = [
'id' => $customer[0]['id'],
'email' => $customer[0]['email'],
'smallThumbnail' => $customer[0]['userInstance'][0]['profileImagePath'],
'isStarred' => $customer[0]['userInstance'][0]['isStarred'],
'isActive' => $customer[0]['userInstance'][0]['isActive'],
'name' => $customer[0]['firstName'].' '.$customer[0]['lastName'],
'source' => $customer[0]['userInstance'][0]['source'],
'count' => $this->getCustomerTicketCount($customer[0]['id']),
];
}
$json['customers'] = $data;
$json['pagination_data'] = $paginationData;
$json['customer_count'] = $this->getCustomerCountDetails($container);
return $json;
}
public function getCustomerCountDetails($container) {
$starredQb = $this->getEntityManager()->createQueryBuilder();
$starredQb->select('COUNT(u.id) as countUser')
->from($this->getEntityName(), 'u')
->leftJoin('u.userInstance', 'userInstance')
->andWhere('userInstance.isActive = 1')
->andWhere('userInstance.supportRole = :roles')
->setParameter('roles', 4);
$all = $starredQb->getQuery()->getResult();
$starredQb->andWhere('userInstance.isStarred = 1');
$starred = $starredQb->getQuery()->getResult();
return array('all' => $all[0]['countUser'],'starred' => $starred[0]['countUser']);
}
public function getCustomerTicketCount($customerId) {
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('COUNT(t.id) as countTicket')->from(Ticket::class, 't');
$qb->andWhere('t.status = 1');
$qb->andWhere('t.isTrashed != 1');
$qb->andWhere('t.customer = :customerId');
$qb->setParameter('customerId', $customerId);
$result = $qb->getQuery()->getResult();
return $result[0]['countTicket'];
}
public function getAgentByEmail($username)
{
$queryBuilder = $this->getEntityManager()->createQueryBuilder()
->select('u, dt')
->from(User::class, 'u')
->leftJoin('u.userInstance', 'dt')
->where('u.email = :email')->setParameter('email', $username)
->andWhere('dt.supportRole != :roles')->setParameter('roles', 4)
;
return $queryBuilder->getQuery()->getOneOrNullResult();
}
public function getSupportGroups(Request $request = null)
{
$queryBuilder = $this->getEntityManager()->createQueryBuilder()
->select('supportGroup.id, supportGroup.name')->from(SupportGroup::class, 'supportGroup')
->where('supportGroup.isActive = :isActive')->setParameter('isActive', true);
if ($request) {
$queryBuilder
->andWhere("supportGroup.name LIKE :groupName")->setParameter('groupName', '%' . urldecode($request->query->get('query')) . '%')
->andWhere("supportGroup.id NOT IN (:ids)")->setParameter('ids', explode(',', urldecode($request->query->get('not'))));
}
return $queryBuilder->getQuery()->getArrayResult();
}
public function getSupportTeams(Request $request = null)
{
$queryBuilder = $this->getEntityManager()->createQueryBuilder()
->select('supportTeam.id, supportTeam.name')->from(SupportTeam::class, 'supportTeam')
->where('supportTeam.isActive = :isActive')->setParameter('isActive', true);
if ($request) {
$queryBuilder
->andWhere("supportTeam.name LIKE :subGroupName")->setParameter('subGroupName', '%' . urldecode($request->query->get('query')) . '%')
->andWhere("supportTeam.id NOT IN (:ids)")->setParameter('ids', explode(',',urldecode($request->query->get('not'))));
}
return $queryBuilder->getQuery()->getResult();
}
public function getUserSupportGroupReferences(User $user)
{
$query = $this->getEntityManager()->createQueryBuilder()
->select('ug.id')->from(User::class, 'u')
->leftJoin('u.userInstance','userInstance')
->leftJoin('userInstance.supportGroups','ug')
->andWhere('u.id = :userId')
->setParameter('userId', $user->getId())
->andWhere('ug.isActive = 1');
return array_map('current', $query->getQuery()->getResult());
}
public function getUserSupportTeamReferences(User $user)
{
$query = $this->getEntityManager()->createQueryBuilder()
->select('ut.id')->from(User::class, 'u')
->leftJoin('u.userInstance','userInstance')
->leftJoin('userInstance.supportTeams','ut')
->andWhere('u.id = :userId')
->andWhere('userInstance.supportRole != :agentRole')
->andWhere('ut.isActive = 1')
->setParameter('userId', $user->getId())
->setParameter('agentRole', '4');
return array_map('current', $query->getQuery()->getResult());
}
public function lastUpdatedRole($user)
{
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('us')->from(UserInstance::class, 'us');
$qb->andWhere('us.user = :userId');
$qb->setParameter('userId',$user->getId());
$qb->setMaxResults(1);
$qb->orderBy('us.createdAt', Criteria::DESC);
return $qb->getQuery()->getResult();
}
public function retrieveHelpdeskCustomerInstances($username)
{
$queryBuilder = $this->getEntityManager()->createQueryBuilder()
->select('u, dt')
->from(User::class, 'u')
->leftJoin('u.userInstance', 'dt')
->where('u.email = :email')->setParameter('email', $username)
->andWhere('dt.supportRole = :roles')->setParameter('roles', 4)
;
return $queryBuilder->getQuery()->getOneOrNullResult();
}
}

vendor/uvdesk/core-framework/Repository/UserRepository.php line 232