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

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("user.firstName LIKE :fullName OR user.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.                     // 'isEnabled' => $user['isEnabled'],
  77.                     'smallThumbnail' => $user['userInstance'][0]['profileImagePath'] ?: null,
  78.                     'isActive' => $user['userInstance'][0]['isActive'],
  79.                     'name' => ucwords(trim(implode(' ', [$user['firstName'], $user['lastName']]))),
  80.                     'role' => $user['userInstance'][0]['supportRole']['description'],
  81.                     'roleCode' =>  $user['userInstance'][0]['supportRole']['code'],
  82.                 ];
  83.             }, $pagination->getItems()),
  84.         ];
  85.     }
  87.     public function getAllAgentsForChoice(ParameterBag $obj = null, $container)
  88.     {
  89.         $qb = $this->getEntityManager()->createQueryBuilder();
  90.         $qb->select('a')->from($this->getEntityName(), 'a')
  91.                 ->leftJoin('a.userInstance', 'userInstance')
  92.                 ->leftJoin('userInstance.supportRole', 'supportRole')
  93.                 ->andwhere('userInstance.supportRole NOT IN (:roles)')
  94.                 ->setParameter('roles', [4]);
  96.         return $qb;
  97.     }
  99.     public function getAllCustomer(ParameterBag $obj = null, $container)
  100.     {
  101.         $json = array();
  102.         $qb = $this->getEntityManager()->createQueryBuilder();
  103.         $qb->select('a,userInstance')->from($this->getEntityName(), 'a');
  104.         $qb->leftJoin('a.userInstance', 'userInstance');
  105.         $qb->addSelect("CONCAT(a.firstName,' ',a.lastName) AS name");
  107.         $data = $obj->all();
  108.         $data = array_reverse($data);
  109.         
  110.         foreach ($data as $key => $value) {
  111.             if(!in_array($key,$this->safeFields)) {
  112.                 if($key!='dateUpdated' AND $key!='dateAdded' AND $key!='search' AND $key!='starred' AND $key!='isActive') {
  113.                     $qb->Andwhere('a.'.$key.' = :'.$key);
  114.                     $qb->setParameter($key, $value);
  115.                 } else {
  116.                     if($key == 'search') {
  117.                         $qb->Andwhere("CONCAT(a.firstName,' ', a.lastName) LIKE :fullName OR a.email LIKE :email");
  118.                         $qb->setParameter('fullName', '%'.urldecode($value).'%'); 
  119.                         $qb->setParameter('email', '%'.urldecode($value).'%');    
  120.                     } elseif($key == 'starred') {
  121.                         $qb->andwhere('userInstance.isStarred = 1');
  122.                     } else if($key == 'isActive') {
  123.                         $qb->andwhere('userInstance.isActive = :isActive');
  124.                         $qb->setParameter('isActive', $value);
  125.                     }
  126.                 }
  127.             }
  128.         } 
  130.         $qb->andwhere('userInstance.supportRole = :roles');
  131.         $qb->setParameter('roles', 4);
  133.         if(!isset($data['sort'])){
  134.             $qb->orderBy('userInstance.createdAt',Criteria::DESC);
  135.         }
  137.         $paginator  = $container->get('knp_paginator');
  139.         $newQb = clone $qb;
  140.         $newQb->select('DISTINCT a.id');
  141.         $results = $paginator->paginate(
  142.             $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', count($newQb->getQuery()->getResult())),
  143.             isset($data['page']) ? $data['page'] : 1,
  144.             self::LIMIT,
  145.             array('distinct' => true, 'wrap-queries' => true)
  146.         );
  148.         $paginationData = $results->getPaginationData();
  149.         $queryParameters = $results->getParams();
  150.         $queryParameters['page'] = "replacePage";
  151.         $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  153.         $this->container = $container;
  154.         $data = array();
  156.         foreach ($results as $key => $customer) {
  157.             $data[] = array(
  158.                                 'id' => $customer[0]['id'],
  159.                                 'email' => $customer[0]['email'],
  160.                                 // 'isActive'=> $customer[0]['isActive'],
  161.                                 'smallThumbnail' => $customer[0]['userInstance'][0]['profileImagePath'],
  162.                                 'isStarred' => $customer[0]['userInstance'][0]['isStarred'],
  163.                                 'isActive' => $customer[0]['userInstance'][0]['isActive'],
  164.                                 'name' => $customer[0]['firstName'].' '.$customer[0]['lastName'],
  165.                                 'source' => $customer[0]['userInstance'][0]['source'],
  166.                                 'count' => $this->getCustomerTicketCount($customer[0]['id']),
  167.                             );
  168.         }   
  169.         $json['customers'] = $data;
  170.         $json['pagination_data'] = $paginationData;
  171.         $json['customer_count'] = $this->getCustomerCountDetails($container);
  172.         return $json;
  173.     }
  176.     public function getCustomerCountDetails($container) {
  177.         $starredQb = $this->getEntityManager()->createQueryBuilder();
  178.         $starredQb->select('COUNT(u.id) as countUser')
  179.                 ->from($this->getEntityName(), 'u')
  180.                 ->leftJoin('u.userInstance', 'userInstance')
  181.                 ->andwhere('userInstance.isActive = 1')
  182.                 ->Andwhere('userInstance.supportRole = :roles')
  183.                 ->setParameter('roles', 4);
  185.         $all = $starredQb->getQuery()->getResult();
  187.         $starredQb->andwhere('userInstance.isStarred = 1');
  188.         $starred = $starredQb->getQuery()->getResult();
  190.         return array('all' => $all[0]['countUser'],'starred' => $starred[0]['countUser']);
  191.     }
  193.     public function getCustomerTicketCount($customerId) {
  194.         $qb = $this->getEntityManager()->createQueryBuilder();
  195.         $qb->select('COUNT(t.id) as countTicket')->from(Ticket::class, 't');
  196.         $qb->andwhere('t.status = 1');
  197.         $qb->andwhere('t.isTrashed != 1');
  198.         $qb->andwhere('t.customer = :customerId');
  199.         $qb->setParameter('customerId', $customerId);
  200.         $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.     }
  287. }