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

Open in your IDE?
  1. <?php
  3. namespace Webkul\UVDesk\CoreFrameworkBundle\Repository;
  5. use Doctrine\ORM\Query;
  6. use Doctrine\Common\Collections\Criteria;
  7. use Webkul\UVDesk\CoreFrameworkBundle\Entity\User;
  8. use Webkul\UVDesk\CoreFrameworkBundle\Entity\UserInstance;
  9. use Webkul\UVDesk\CoreFrameworkBundle\Entity\Ticket;
  10. use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportGroup;
  11. use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportTeam;
  12. use Symfony\Component\HttpFoundation\ParameterBag;
  13. use Symfony\Component\DependencyInjection\ContainerInterface;
  15. /**
  16.  * UserRepository
  17.  *
  18.  * This class was generated by the Doctrine ORM. Add your own custom
  19.  * repository methods below.
  20.  */
  21. class UserRepository extends \Doctrine\ORM\EntityRepository
  22. {
  23.     const LIMIT = 10;
  24.     public $safeFields = ['page', 'limit', 'sort', 'order', 'direction'];
  26.     public function getAllAgents(ParameterBag $params = null, ContainerInterface $container) {
  27.         $params = !empty($params) ? array_reverse($params->all()) : [];
  28.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  29.             ->select("user, userInstance, supportRole")
  30.             ->from(User::class, 'user')
  31.             ->leftJoin('user.userInstance', 'userInstance')
  32.             ->leftJoin('userInstance.supportRole', 'supportRole')
  33.             ->where('supportRole.id != :customerRole')->setParameter('customerRole', 4)
  34.             ->orderBy('userInstance.createdAt', !isset($params['sort']) ? Criteria::DESC : Criteria::ASC);
  36.         foreach ($params as $field => $fieldValue) {
  37.             if (in_array($field, $this->safeFields))
  38.                 continue;
  39.             
  40.             if (!in_array($field, ['dateUpdated', 'dateAdded', 'search', 'isActive'])) {
  41.                 $queryBuilder->andWhere("user.$field = :$field")->setParameter($field, $fieldValue);
  42.             } else {
  43.                 if ('search' == $field) {
  44.                     $queryBuilder->andWhere("CONCAT(a.firstName,' ', a.lastName) LIKE :fullName OR a.email LIKE :email")
  45.                         ->setParameter('fullName', '%' . urldecode(trim($fieldValue)) . '%')
  46.                         ->setParameter('email', '%' . urldecode(trim($fieldValue)) . '%');
  47.                 } else if ('isActive' == $field) {
  48.                     $queryBuilder->andWhere('userInstance.isActive = :isActive')->setParameter('isActive', $fieldValue);
  49.                 }
  50.             }
  51.         }
  53.         // Pagination
  54.         $options = ['distinct' => true, 'wrap-queries' => true];
  55.         $currentPage = isset($params['page']) ? $params['page'] : 1;
  56.         
  57.         $paginationQueryBuilder = clone $queryBuilder;
  58.         $totalUsers = (int) $paginationQueryBuilder->select('COUNT (DISTINCT user.id)')->getQuery()->getSingleScalarResult();
  59.         $query = $queryBuilder->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $totalUsers);
  61.         $pagination = $container->get('knp_paginator')->paginate($query, $currentPage, self::LIMIT, $options);
  62.         
  63.         // Parse result
  64.         $paginationParams = $pagination->getParams();
  65.         $paginationAttributes = $pagination->getPaginationData();
  66.         
  67.         $paginationParams['page'] = 'replacePage';
  68.         $paginationAttributes['url'] = '#' . $container->get('uvdesk.service')->buildPaginationQuery($paginationParams);
  69.         
  70.         return [
  71.             'pagination_data' => $paginationAttributes,
  72.             'users' => array_map(function ($user) {
  73.                 return [
  74.                     'id'             => $user['id'],
  75.                     'email'          => $user['email'],
  76.                     'smallThumbnail' => $user['userInstance'][0]['profileImagePath'] ?: null,
  77.                     'isActive'       => $user['userInstance'][0]['isActive'],
  78.                     'name'           => ucwords(trim(implode(' ', [$user['firstName'], $user['lastName']]))),
  79.                     'role'           => $user['userInstance'][0]['supportRole']['description'],
  80.                     'roleCode'       => $user['userInstance'][0]['supportRole']['code'],
  81.                 ];
  82.             }, $pagination->getItems()),
  83.         ];
  84.     }
  86.     public function getAllAgentsForChoice(ParameterBag $obj = null, $container)
  87.     {
  88.         $qb = $this->getEntityManager()->createQueryBuilder();
  89.         $qb->select('a')->from($this->getEntityName(), 'a')
  90.                 ->leftJoin('a.userInstance', 'userInstance')
  91.                 ->leftJoin('userInstance.supportRole', 'supportRole')
  92.                 ->andWhere('userInstance.supportRole NOT IN (:roles)')
  93.                 ->setParameter('roles', [4]);
  95.         return $qb;
  96.     }
  98.     public function getAllCustomer(ParameterBag $obj = null, $container)
  99.     {
  100.         $json = array();
  101.         $qb = $this->getEntityManager()->createQueryBuilder();
  102.         $qb->select('a,userInstance')->from($this->getEntityName(), 'a');
  103.         $qb->leftJoin('a.userInstance', 'userInstance');
  104.         $qb->addSelect("CONCAT(a.firstName,' ',a.lastName) AS name");
  106.         $data = $obj->all();
  107.         $data = array_reverse($data);
  108.         
  109.         foreach ($data as $key => $value) {
  110.             if (! in_array($key,$this->safeFields)) {
  111.                 if ($key!='dateUpdated' AND $key!='dateAdded' AND $key!='search' AND $key!='starred' AND $key!='isActive') {
  112.                     $qb->andWhere('a.'.$key.' = :'.$key);
  113.                     $qb->setParameter($key, $value);
  114.                 } else {
  115.                     if ($key == 'search') {
  116.                         $qb->andWhere("CONCAT(a.firstName,' ', a.lastName) LIKE :fullName OR a.email LIKE :email");
  117.                         $qb->setParameter('fullName', '%'.urldecode(trim($value)).'%');
  118.                         $qb->setParameter('email', '%'.urldecode(trim($value)).'%'); 
  119.                     } elseif ($key == 'starred') {
  120.                         $qb->andWhere('userInstance.isStarred = 1');
  121.                     } elseif ($key == 'isActive') {
  122.                         $qb->andWhere('userInstance.isActive = :isActive');
  123.                         $qb->setParameter('isActive', $value);
  124.                     }
  125.                 }
  126.             }
  127.         } 
  129.         $qb->andWhere('userInstance.supportRole = :roles');
  130.         $qb->setParameter('roles', 4);
  132.         if (!isset($data['sort'])) {
  133.             $qb->orderBy('userInstance.createdAt',Criteria::DESC);
  134.         }
  136.         $paginator  = $container->get('knp_paginator');
  138.         $newQb = clone $qb;
  139.         $newQb->select('DISTINCT a.id');
  140.         $results = $paginator->paginate(
  141.             $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', count($newQb->getQuery()->getResult())),
  142.             isset($data['page']) ? $data['page'] : 1,
  143.             self::LIMIT,
  144.             array('distinct' => true, 'wrap-queries' => true)
  145.         );
  147.         $paginationData = $results->getPaginationData();
  148.         $queryParameters = $results->getParams();
  149.         $queryParameters['page'] = "replacePage";
  150.         $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  152.         $this->container = $container;
  153.         $data = array();
  155.         foreach ($results as $key => $customer) {
  156.             $data[] =   [
  157.                             'id'             => $customer[0]['id'],
  158.                             'email'          => $customer[0]['email'],
  159.                             'smallThumbnail' => $customer[0]['userInstance'][0]['profileImagePath'],
  160.                             'isStarred'      => $customer[0]['userInstance'][0]['isStarred'],
  161.                             'isActive'       => $customer[0]['userInstance'][0]['isActive'],
  162.                             'name'           => $customer[0]['firstName'].' '.$customer[0]['lastName'],
  163.                             'source'         => $customer[0]['userInstance'][0]['source'],
  164.                             'count'          => $this->getCustomerTicketCount($customer[0]['id']),
  165.                         ];
  166.         }
  168.         $json['customers'] = $data;
  169.         $json['pagination_data'] = $paginationData;
  170.         $json['customer_count'] = $this->getCustomerCountDetails($container);
  172.         return $json;
  173.     }
  175.     public function getCustomerCountDetails($container) {
  176.         $starredQb = $this->getEntityManager()->createQueryBuilder();
  177.         $starredQb->select('COUNT(u.id) as countUser')
  178.                 ->from($this->getEntityName(), 'u')
  179.                 ->leftJoin('u.userInstance', 'userInstance')
  180.                 ->andWhere('userInstance.isActive = 1')
  181.                 ->andWhere('userInstance.supportRole = :roles')
  182.                 ->setParameter('roles', 4);
  184.         $all = $starredQb->getQuery()->getResult();
  186.         $starredQb->andWhere('userInstance.isStarred = 1');
  187.         $starred = $starredQb->getQuery()->getResult();
  189.         return array('all' => $all[0]['countUser'],'starred' => $starred[0]['countUser']);
  190.     }
  192.     public function getCustomerTicketCount($customerId) {
  193.         $qb = $this->getEntityManager()->createQueryBuilder();
  194.         $qb->select('COUNT(t.id) as countTicket')->from(Ticket::class, 't');
  195.         $qb->andWhere('t.status = 1');
  196.         $qb->andWhere('t.isTrashed != 1');
  197.         $qb->andWhere('t.customer = :customerId');
  198.         $qb->setParameter('customerId', $customerId);
  199.         $result = $qb->getQuery()->getResult();
  201.         return $result[0]['countTicket'];
  202.     }
  204.     public function getAgentByEmail($username)
  205.     {
  206.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  207.             ->select('u, dt')
  208.             ->from(User::class, 'u')
  209.             ->leftJoin('u.userInstance', 'dt')
  210.             ->where('u.email = :email')->setParameter('email', $username)
  211.             ->andWhere('dt.supportRole != :roles')->setParameter('roles', 4)
  212.         ;
  214.         return $queryBuilder->getQuery()->getOneOrNullResult();
  215.     }
  216.     
  217.     public function getSupportGroups(Request $request = null)
  218.     {
  219.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  220.             ->select('supportGroup.id, supportGroup.name')->from(SupportGroup::class, 'supportGroup')
  221.             ->where('supportGroup.isActive = :isActive')->setParameter('isActive', true);
  223.         if ($request) {
  224.             $queryBuilder
  225.                 ->andWhere("supportGroup.name LIKE :groupName")->setParameter('groupName', '%' . urldecode($request->query->get('query')) . '%')
  226.                 ->andWhere("supportGroup.id NOT IN (:ids)")->setParameter('ids', explode(',', urldecode($request->query->get('not'))));
  227.         }
  229.         return $queryBuilder->getQuery()->getArrayResult();
  230.     }
  232.     public function getSupportTeams(Request $request = null)
  233.     {
  234.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  235.             ->select('supportTeam.id, supportTeam.name')->from(SupportTeam::class, 'supportTeam')
  236.             ->where('supportTeam.isActive = :isActive')->setParameter('isActive', true);
  237.         
  238.         if ($request) {
  239.             $queryBuilder
  240.                 ->andWhere("supportTeam.name LIKE :subGroupName")->setParameter('subGroupName', '%' . urldecode($request->query->get('query')) . '%')
  241.                 ->andWhere("supportTeam.id NOT IN (:ids)")->setParameter('ids', explode(',',urldecode($request->query->get('not'))));
  242.         }
  244.         return $queryBuilder->getQuery()->getResult();
  245.     }
  246.     
  247.     public function getUserSupportGroupReferences(User $user)
  248.     {
  249.         $query = $this->getEntityManager()->createQueryBuilder()
  250.             ->select('ug.id')->from(User::class, 'u') 
  251.             ->leftJoin('u.userInstance','userInstance')
  252.             ->leftJoin('userInstance.supportGroups','ug')
  253.             ->andWhere('u.id = :userId')
  254.             ->setParameter('userId', $user->getId())
  255.             ->andWhere('ug.isActive = 1');
  257.         return array_map('current', $query->getQuery()->getResult());
  258.     }
  260.     public function getUserSupportTeamReferences(User $user)
  261.     {
  262.         $query = $this->getEntityManager()->createQueryBuilder()
  263.             ->select('ut.id')->from(User::class, 'u')
  264.             ->leftJoin('u.userInstance','userInstance')
  265.             ->leftJoin('userInstance.supportTeams','ut')
  266.             ->andWhere('u.id = :userId')
  267.             ->andWhere('userInstance.supportRole != :agentRole')
  268.             ->andWhere('ut.isActive = 1')
  269.             ->setParameter('userId', $user->getId())
  270.             ->setParameter('agentRole', '4');
  272.         return array_map('current', $query->getQuery()->getResult());
  273.     }
  275.     public function lastUpdatedRole($user)
  276.     {
  277.         $qb = $this->getEntityManager()->createQueryBuilder();
  278.         $qb->select('us')->from(UserInstance::class, 'us');
  280.         $qb->andWhere('us.user = :userId');
  281.         $qb->setParameter('userId',$user->getId());
  282.         $qb->setMaxResults(1);
  283.         $qb->orderBy('us.createdAt', Criteria::DESC);
  285.         return $qb->getQuery()->getResult();
  286.     }
  288.     public function retrieveHelpdeskCustomerInstances($username)
  289.     {
  290.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  291.             ->select('u, dt')
  292.             ->from(User::class, 'u')
  293.             ->leftJoin('u.userInstance', 'dt')
  294.             ->where('u.email = :email')->setParameter('email', $username)
  295.             ->andWhere('dt.supportRole = :roles')->setParameter('roles', 4)
  296.         ;
  298.         return $queryBuilder->getQuery()->getOneOrNullResult();
  299.     }
  300. }