vendor/uvdesk/core-framework/Repository/TicketRepository.php line 464

Open in your IDE?
  1. <?php
  3. namespace Webkul\UVDesk\CoreFrameworkBundle\Repository;
  5. use Doctrine\ORM\Query;
  6. use Doctrine\ORM\QueryBuilder;
  7. use Doctrine\Common\Collections\Criteria;
  8. use Webkul\UVDesk\CoreFrameworkBundle\Entity\User;
  9. use Webkul\UVDesk\CoreFrameworkBundle\Entity\Ticket;
  10. use Webkul\UVDesk\CoreFrameworkBundle\Entity\TicketType;
  11. use Webkul\UVDesk\CoreFrameworkBundle\Entity\Tag;
  12. use Symfony\Component\HttpFoundation\ParameterBag;
  13. use Symfony\Component\DependencyInjection\ContainerInterface;
  15. /**
  16.  * TicketRepository
  17.  *
  18.  * This class was generated by the Doctrine ORM. Add your own custom
  19.  * repository methods below.
  20.  */
  21. class TicketRepository extends \Doctrine\ORM\EntityRepository
  22. {
  23.     const LIMIT = 15;
  24.     const TICKET_GLOBAL_ACCESS = 1;
  25.     const TICKET_GROUP_ACCESS = 2;
  26.     const TICKET_TEAM_ACCESS  = 3;
  27.     const DEFAULT_PAGINATION_LIMIT = 15;
  29.     private $container;
  30.     private $requestStack;
  31.     private $safeFields = ['page', 'limit', 'sort', 'order', 'direction'];
  33.     public function getTicketLabelCollection(Ticket $ticket, User $user)
  34.     {
  35.         // $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  36.         //     ->select("DISTINCT supportLabel.id, supportLabel.name, supportLabel.colorCode as color")
  37.         //     ->from(Ticket::class, 'ticket')
  38.         //     ->leftJoin('ticket.supportLabels', 'supportLabel')
  39.         //     // ->leftJoin('supportLabel.user', 'user')
  40.         //     ->where('ticket.id = :ticketId')->setParameter('ticketId', $ticket->getId())
  41.         //     ->andWhere('supportLabel.user = :user')->setParameter('user', $user);
  43.         return [];
  44.     }
  46.     public function getAllTickets(ParameterBag $obj = null, $container, $actAsUser = null)
  47.     {
  48.         $currentUser = $actAsUser ? : $container->get('user.service')->getCurrentUser();
  50.         $json = array();
  51.         $qb = $this->getEntityManager()->createQueryBuilder();
  52.         $qb->select('DISTINCT t,gr,pr,tp,s,a.id as agentId,c.id as customerId')->from($this->getEntityName(), 't');
  53.         $qb->leftJoin('t.agent', 'a');
  54.         $qb->leftJoin('a.userInstance', 'ad');
  55.         $qb->leftJoin('t.status', 's');
  56.         $qb->leftJoin('t.customer', 'c');
  57.         $qb->leftJoin('t.supportGroup', 'gr');
  58.         $qb->leftJoin('t.priority', 'pr');
  59.         $qb->leftJoin('t.type', 'tp');
  60.         $qb->addSelect("CONCAT(a.firstName,' ', a.lastName) AS name");
  61.         $qb->andwhere("t.agent IS NULL OR ad.supportRole != 4");
  62.         $data = $obj ? $obj->all() : [];
  63.         $data = array_reverse($data);
  64.         $groupIds = [];
  65.         if(isset($data['agent'])) {
  66.             $query = $this->getEntityManager()->createQueryBuilder()
  67.                 ->select('ug.id')->from(User::class, 'u') 
  68.                 ->leftJoin('u.userInstance','userInstance')
  69.                 ->leftJoin('userInstance.supportGroups','ug')
  70.                 ->andwhere('u.id = :userId')
  71.                 ->setParameter('userId', $data['agent'])
  72.                 ->andwhere('ug.isActive = 1');
  73.             $groupIds = array_map('current', $query->getQuery()->getResult());
  74.         } 
  75.         foreach ($data as $key => $value) {
  76.             if(!in_array($key,$this->safeFields)) {
  77.                 if(isset($data['search']) && $key == 'search') {
  78.                     $qb->andwhere("t.subject LIKE :subject OR a.email LIKE :agentName OR t.id LIKE :ticketId");
  79.                     $qb->setParameter('subject', '%'.urldecode($value).'%');
  80.                     $qb->setParameter('agentName', '%'.urldecode($value).'%');
  81.                     $qb->setParameter('ticketId', '%'.urldecode($value).'%');
  82.                 } elseif($key == 'status') {
  83.                     $qb->andwhere('t.status = '.intval($value));
  84.                 }
  85.                 if(isset($data['agent']) && $key == 'agent') {
  86.                     $qb->andWhere("a.id = :agentID OR gr.id IN(:supportGroupIds)");
  87.                     $qb->setParameter('agentID',intval($value));
  88.                     $qb->setParameter('supportGroupIds', $groupIds);
  89.                 }
  90.             }
  91.         }
  92.         $qb->andwhere('t.isTrashed != 1');
  94.         if(!isset($data['sort'])) {
  95.             $qb->orderBy('t.id',Criteria::DESC);
  96.         }
  98.         if(isset($data['sort']) && $data['sort'] == "t.updatedAt") {
  99.             $qb->orderBy('t.updatedAt',Criteria::DESC);
  100.         }
  102.         $paginator = $container->get('knp_paginator');
  104.         $newQb = clone $qb;
  105.         $newQb->select('COUNT(DISTINCT t.id)');
  107.         $results = $paginator->paginate(
  108.             $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  109.             isset($data['page']) ? $data['page'] : 1,
  110.             isset($data['limit']) && !empty($data['limit']) ? $data['limit'] : self::LIMIT,
  111.             array('distinct' => true)
  112.         );
  114.         $paginationData = $results->getPaginationData();
  115.         $queryParameters = $results->getParams();
  117.         $queryParameters['page'] = "replacePage";
  118.         $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  120.         $data = array();
  121.         $userService = $container->get('user.service');
  122.         $ticketService = $container->get('ticket.service');
  123.         $translatorService = $container->get('translator');
  125.         foreach ($results as $key => $ticket) {
  126.          //   $ticket[0]['status']['description'] = $translatorService->trans($ticket[0]['status']['description']);
  128.             $data[] = [
  129.                 'id' => $ticket[0]['id'],
  130.                 'subject' => $ticket[0]['subject'],
  131.                 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  132.                 'status' => $ticket[0]['status'],
  133.                 'source' => $ticket[0]['source'],
  134.                 'isStarred' => $ticket[0]['isStarred'],
  135.                 'group' => $ticket[0]['supportGroup'],
  136.                 'type' => $ticket[0]['type'],
  137.                 'priority' => $ticket[0]['priority'],
  138.                 'formatedCreatedAt' => $userService->convertToTimezone($ticket[0]['createdAt']),
  139.                 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  140.                 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  141.                 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  142.                 // 'hasAttachments' => $ticketService->hasAttachments($ticket[0]['id'])
  143.             ];
  144.         }
  146.         $json['tickets'] = $data;
  147.         $json['pagination'] = $paginationData;
  149.         return $json;
  150.     }
  152.     public function getAllCustomerTickets(ParameterBag $obj = null, $container, $actAsUser = null)
  153.     {
  154.         $currentUser = $actAsUser ? : $container->get('user.service')->getCurrentUser();
  155.         $json = array();
  156.         $qb = $this->getEntityManager()->createQueryBuilder();
  157.         $qb->select('DISTINCT t,gr,pr,tp,s,a.id as agentId,c.id as customerId')->from($this->getEntityName(), 't');
  158.         $qb->leftJoin('t.agent', 'a');
  159.         $qb->leftJoin('a.userInstance', 'ad');
  160.         $qb->leftJoin('t.status', 's');
  161.         $qb->leftJoin('t.customer', 'c');
  162.         $qb->leftJoin('t.supportGroup', 'gr');
  163.         $qb->leftJoin('t.priority', 'pr');
  164.         $qb->leftJoin('t.type', 'tp');
  165.         $qb->leftJoin('t.collaborators', 'tc');
  166.         $qb->addSelect("CONCAT(a.firstName,' ', a.lastName) AS name");
  167.         $qb->andwhere("t.agent IS NULL OR ad.supportRole != 4");
  169.         $data = $obj->all();
  170.         $data = array_reverse($data);
  171.         foreach ($data as $key => $value) {
  172.             if(!in_array($key,$this->safeFields)) {
  173.                 if(isset($data['search']) && $key == 'search') {
  174.                     $qb->andwhere("t.subject LIKE :subject OR a.email LIKE :agentName OR t.id LIKE :ticketId");
  175.                     $qb->setParameter('subject', '%'.urldecode($value).'%');
  176.                     $qb->setParameter('agentName', '%'.urldecode($value).'%');
  177.                     $qb->setParameter('ticketId', '%'.urldecode($value).'%');
  178.                 } elseif($key == 'status') {
  179.                     $qb->andwhere('t.status = '.intval($value));
  180.                 }
  181.             }
  182.         }
  184.         $qb->andwhere('t.customer = :customerId OR tc.id =:collaboratorId');
  185.         $qb->setParameter('customerId', $currentUser->getId());
  186.         $qb->setParameter('collaboratorId', $currentUser->getId());
  187.         $qb->andwhere('t.isTrashed != 1');
  189.         if(!isset($data['sort'])) {
  190.             $qb->orderBy('t.id',Criteria::DESC);
  191.         }
  193.         $paginator = $container->get('knp_paginator');
  195.         $newQb = clone $qb;
  196.         $newQb->select('COUNT(DISTINCT t.id)');
  198.         $results = $paginator->paginate(
  199.             $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  200.             isset($data['page']) ? $data['page'] : 1,
  201.             self::LIMIT,
  202.             array('distinct' => true)
  203.         );
  205.         $paginationData = $results->getPaginationData();
  206.         $queryParameters = $results->getParams();
  208.         $queryParameters['page'] = "replacePage";
  209.         $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  211.         $data = array();
  212.         $userService = $container->get('user.service');
  213.         $ticketService = $container->get('ticket.service');
  214.         $translatorService = $container->get('translator');
  216.         foreach ($results as $key => $ticket) {
  217.             $ticket[0]['status']['code'] = $translatorService->trans($ticket[0]['status']['code']);
  219.             $data[] = [
  220.                 'id' => $ticket[0]['id'],
  221.                 'subject' => $ticket[0]['subject'],
  222.                 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  223.                 'status' => $ticket[0]['status'],
  224.                 'group' => $ticket[0]['supportGroup'],
  225.                 'type' => $ticket[0]['type'],
  226.                 'priority' => $ticket[0]['priority'],
  227.                 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  228.                 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  229.                 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  230.                 'formatedCreatedAt' => $userService->getLocalizedFormattedTime($ticket[0]['createdAt'],$userService->getSessionUser()),
  231.             ];
  232.         }
  234.         $json['tickets'] = $data;
  236.         $json['pagination'] = $paginationData;
  238.         return $json;
  239.     }
  241.     public function addPermissionFilter($qb, User $user, array $supportGroupReferences = [], array $supportTeamReferences = [])
  242.     {
  243.         $userInstance = $user->getAgentInstance();
  245.         if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $userInstance->getTicketAccesslevel() != self::TICKET_GLOBAL_ACCESS)) {
  246.             $qualifiedGroups = empty($this->params['group']) ? $supportGroupReferences : array_intersect($supportGroupReferences, explode(',', $this->params['group']));
  247.             $qualifiedTeams = empty($this->params['team']) ? $supportTeamReferences : array_intersect($supportTeamReferences, explode(',', $this->params['team']));
  249.             switch ($userInstance->getTicketAccesslevel()) {
  250.                 case self::TICKET_GROUP_ACCESS:
  251.                     $qb
  252.                         ->andWhere("ticket.agent = :agentId OR supportGroup.id IN(:supportGroupIds) OR supportTeam.id IN(:supportTeamIds)")
  253.                         ->setParameter('agentId', $user->getId())
  254.                         ->setParameter('supportGroupIds', $qualifiedGroups)
  255.                         ->setParameter('supportTeamIds', $qualifiedTeams);
  256.                     break;
  257.                 case self::TICKET_TEAM_ACCESS:
  258.                     $qb
  259.                         ->andWhere("ticket.agent = :agentId OR supportTeam.id IN(:supportTeamIds)")
  260.                         ->setParameter('agentId', $user->getId())
  261.                         ->setParameter('supportTeamIds', $qualifiedTeams);
  262.                     break;
  263.                 default:
  264.                     $qb
  265.                         ->andWhere("ticket.agent = :agentId")
  266.                         ->setParameter('agentId', $user->getId());
  267.                     break;
  268.             }
  269.         }
  271.         return $qb;
  272.     }
  274.     public function prepareBaseTicketQuery(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  275.     {
  276.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  277.             ->select("
  278.                 DISTINCT ticket,
  279.                 supportGroup.name as groupName,
  280.                 supportTeam.name as teamName, 
  281.                 priority,
  282.                 type.code as typeName, 
  283.                 agent.id as agentId,
  284.                 agent.email as agentEmail,
  285.                 agentInstance.profileImagePath as smallThumbnail, 
  286.                 customer.id as customerId, 
  287.                 customer.email as customerEmail, 
  288.                 customerInstance.profileImagePath as customersmallThumbnail, 
  289.                 CONCAT(customer.firstName, ' ', customer.lastName) AS customerName, 
  290.                 CONCAT(agent.firstName,' ', agent.lastName) AS agentName
  291.             ")
  292.             ->from(Ticket::class, 'ticket')
  293.             ->leftJoin('ticket.type', 'type')
  294.             ->leftJoin('ticket.agent', 'agent')
  295.             ->leftJoin('ticket.threads', 'threads')
  296.             ->leftJoin('ticket.priority', 'priority')
  297.             ->leftJoin('ticket.customer', 'customer')
  298.             ->leftJoin('ticket.supportTeam', 'supportTeam')
  299.             ->leftJoin('ticket.supportTags', 'supportTags')
  300.             ->leftJoin('agent.userInstance', 'agentInstance')
  301.             ->leftJoin('ticket.supportLabels', 'supportLabel')
  302.             ->leftJoin('ticket.supportGroup', 'supportGroup')
  303.             ->leftJoin('customer.userInstance', 'customerInstance')
  304.             ->where('customerInstance.supportRole = 4')
  305.             ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  306.             ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false);
  308.         if (!isset($params['sort'])) {
  309.             $queryBuilder->orderBy('ticket.updatedAt', Criteria::DESC);
  310.         }
  312.         if ($filterByStatus) {
  313.             $queryBuilder->andWhere('ticket.status = :status')->setParameter('status', isset($params['status']) ? $params['status'] : 1);
  314.         }
  316.         $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  318.         // applyFilter according to params
  319.         return $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  320.     }
  322.     public function prepareBasePaginationTicketTypesQuery(array $params)
  323.     {
  324.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  325.             ->select("ticketType")
  326.             ->from(TicketType::class, 'ticketType');
  328.         // Apply filters
  329.         foreach ($params as $field => $fieldValue) {
  330.             if (in_array($field, $this->safeFields)) {
  331.                 continue;
  332.             }
  334.             switch ($field) {
  335.                 case 'search':
  336.                     $queryBuilder->andwhere("ticketType.code LIKE :searchQuery OR ticketType.description LIKE :searchQuery");
  337.                     $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  338.                     break;
  339.                 case 'isActive':
  340.                     $queryBuilder->andwhere("ticketType.isActive LIKE :searchQuery");
  341.                     $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  342.                     break;
  343.                 default:
  344.                     break;
  345.             }
  346.         }
  348.         // Define sort by
  349.         if (empty($params['sort']) || 'a.id' == $params['sort']) {
  350.             $queryBuilder->orderBy('ticketType.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  351.         } else {
  352.             $queryBuilder->orderBy('ticketType.code', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  353.         }
  355.         return $queryBuilder;
  356.     }
  358.     public function prepareBasePaginationTagsQuery(array $params)
  359.     {
  360.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  361.             ->select('supportTag.id as id, supportTag.name as name, COUNT(ticket) as totalTickets')
  362.             ->from(Tag::class, 'supportTag')
  363.             ->leftJoin('supportTag.tickets', 'ticket')
  364.             ->groupBy('supportTag.id');
  366.         // Apply filters
  367.         foreach ($params as $field => $fieldValue) {
  368.             if (in_array($field, $this->safeFields)) {
  369.                 continue;
  370.             }
  372.             switch ($field) {
  373.                 case 'search':
  374.                     $queryBuilder->andwhere("supportTag.name LIKE :searchQuery")->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  375.                     break;
  376.                 default:
  377.                     break;
  378.             }
  379.         }
  381.         // Define sort by
  382.         if (empty($params['sort']) || 'a.id' == $params['sort']) {
  383.             $queryBuilder->orderBy('supportTag.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  384.         } else {
  385.             $queryBuilder->orderBy('supportTag.name', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  386.         }
  388.         return $queryBuilder;
  389.     }
  391.     public function getTicketTabDetails(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  392.     {
  393.         $data = array(1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0);
  394.         
  395.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  396.             ->select("
  397.                 COUNT(DISTINCT ticket.id) as countTicket, 
  398.                 status.id as statusId, 
  399.                 status.code as tab
  400.             ")
  401.             ->from(Ticket::class, 'ticket')
  402.             ->leftJoin('ticket.type',   'type')
  403.             ->leftJoin('ticket.agent', 'agent')
  404.             ->leftJoin('ticket.status', 'status')
  405.             ->leftJoin('ticket.threads', 'threads')
  406.             ->leftJoin('ticket.priority', 'priority')
  407.             ->leftJoin('ticket.customer', 'customer')
  408.             ->leftJoin('ticket.supportTeam', 'supportTeam')
  409.             ->leftJoin('ticket.supportTags', 'supportTags')
  410.             ->leftJoin('ticket.supportGroup', 'supportGroup')
  411.             ->leftJoin('agent.userInstance', 'agentInstance')
  412.             ->leftJoin('ticket.supportLabels', 'supportLabel')
  413.             ->leftJoin('customer.userInstance', 'customerInstance')
  414.             ->where('customerInstance.supportRole = 4')
  415.             ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  416.             ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false)
  417.             ->groupBy('status');
  419.         // applyFilter according to permission
  420.         $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  421.         
  422.         $queryBuilder = $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  423.         $results = $queryBuilder->getQuery()->getResult();
  425.         foreach($results as $status) {
  426.             $data[$status['statusId']] += $status['countTicket'];
  427.         }
  429.         return $data;
  430.     }
  432.     public function countTicketTotalThreads($ticketId, $threadType = 'reply')
  433.     {
  434.         $totalThreads = $this->getEntityManager()->createQueryBuilder()
  435.             ->select('COUNT(thread.id) as threads')
  436.             ->from(Ticket::class, 'ticket')
  437.             ->leftJoin('ticket.threads', 'thread')
  438.             ->where('ticket.id = :ticketId')->setParameter('ticketId', $ticketId)
  439.             ->andWhere('thread.threadType = :threadType')->setParameter('threadType', $threadType)
  440.             ->getQuery()->getSingleScalarResult();
  441.         
  442.         return (int) $totalThreads;
  443.     }
  445.     public function getTicketNavigationIteration($ticket, $container)
  446.     {
  447.         $ticketsCollection = $this->getEntityManager()->getRepository(Ticket::class)
  448.                    ->getAllTickets(null, $container);
  450.         if ($ticketsCollection)
  451.             $results = $ticketsCollection['tickets'];
  453.         $nextPrevPage = array('next' => 0,'prev' => 0);
  454.         for ($i = 0; $i < count($results); $i++) {
  455.             if($results[$i]['id'] == $ticket->getId()) {
  456.                 $nextPrevPage['next'] = isset($results[$i + 1]) ? $results[$i + 1]['id'] : 0;
  457.                 $nextPrevPage['prev'] = isset($results[$i - 1]) ? $results[$i - 1]['id'] : 0;
  458.             }
  459.         }
  461.         return $nextPrevPage;
  462.     }
  464.     public function countCustomerTotalTickets(User $user, $container)
  465.     {
  466.         $userService = $container->get('user.service');
  468.         $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  469.             ->select('COUNT(ticket.id) as tickets')
  470.             ->from(Ticket::class, 'ticket')
  471.             ->leftJoin('ticket.priority', 'p')
  472.             ->leftJoin('ticket.status', 's')
  473.             ->leftJoin('ticket.agent', 'a')
  474.             ->leftJoin('ticket.type', 'type')
  475.             ->leftJoin('ticket.supportGroup', 'supportGroup')
  476.             ->leftJoin('ticket.supportTeam', 'supportTeam')
  477.             ->leftJoin('a.userInstance', 'ad')
  478.             ->andWhere('ticket.customer = :customerId')
  479.             ->andWhere('ticket.isTrashed != 1')
  480.             ->setParameter('customerId', $user->getId())
  481.             ->andwhere("a IS NULL OR ad.supportRole != 4")
  482.             ->orderBy('ticket.id', Criteria::DESC);
  484.         $agent = $userService->getCurrentUser();
  485.         $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($agent);
  486.         $supportTeamReference  = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($agent);
  487.         
  488.         $this->addPermissionFilter($queryBuilder, $agent, $supportGroupReference, $supportTeamReference);
  490.         return (int) $queryBuilder->getQuery()->getSingleScalarResult();
  491.     }
  493.     public function isLabelAlreadyAdded($ticket,$label)
  494.     {
  495.         $qb = $this->getEntityManager()->createQueryBuilder();
  496.         $qb->select('COUNT(t.id) as ticketCount')->from(Ticket::class, 't')
  497.                 ->leftJoin('t.supportLabels','tl')
  498.                 ->andwhere('tl.id = :labelId')
  499.                 ->andwhere('t.id = :ticketId')
  500.                 ->setParameter('labelId',$label->getId())
  501.                 ->setParameter('ticketId',$ticket->getId());
  503.         return $qb->getQuery()->getSingleScalarResult() ? true : false;
  504.     }
  506.     public function isTicketCollaborator($ticket, $collaboratorEmail)
  507.     {
  508.         if ($ticket->getCollaborators()) {
  509.             foreach ($ticket->getCollaborators() as $collaborator) {
  510.                 if (strtolower($collaborator->getEmail()) == strtolower($collaboratorEmail)) {
  511.                     return true;
  512.                 }
  513.             }
  514.         }
  516.         return false;
  517.     }
  519.     public function getTicketDetails(ParameterBag $obj = null, $container)
  520.     {
  521.         $data = $obj->all();
  522.         $userService = $container->get('user.service');
  523.         $ticketService = $container->get('ticket.service');
  524.         $json = [];
  525.         $qb = $this->getEntityManager()->createQueryBuilder();
  526.         $qb->select('DISTINCT t,gr.name as groupName,supportTeam.name as supportTeamName,tp.code as typeName,s,pr,a.id as agentId,c.id as customerId')->from($this->getEntityName(), 't')
  527.                 ->leftJoin('t.agent', 'a')
  528.                 ->leftJoin('t.status', 's')
  529.                 ->leftJoin('t.customer', 'c')
  530.                 ->leftJoin('t.supportGroup', 'gr')
  531.                 ->leftJoin('t.supportTeam', 'supportTeam')
  532.                 ->leftJoin('t.priority', 'pr')
  533.                 ->leftJoin('t.type', 'tp')
  534.                 ->leftJoin('c.userInstance', 'cd')
  535.                 ->leftJoin('a.userInstance', 'ad')
  536.                 ->leftJoin('t.supportTags', 'tg')
  537.                 ->leftJoin('t.supportLabels', 'tl')
  538.                 ->andwhere('t.id = :ticketId')
  539.                 ->setParameter('ticketId', $data['ticketId']);
  541.         $results = $qb->getQuery()->getArrayResult();
  542.         $ticket = array_shift($results);
  543.         
  544.         return [
  545.             'id' => $ticket[0]['id'],
  546.             'subject' => $ticket[0]['subject'],
  547.             'isStarred' => $ticket[0]['isStarred'],
  548.             'isAgentView' => $ticket[0]['isAgentViewed'],
  549.             'isAgentCustomViewed' => $ticket[0]['isAgentCustomViewed'],
  550.             'isTrashed' => $ticket[0]['isTrashed'],
  551.             'status' => $ticket[0]['status'],
  552.             'groupName' => $ticket['groupName'],
  553.             'subGroupName' => $ticket['supportTeamName'],
  554.             'typeName' => $ticket['typeName'],
  555.             'priority' => $ticket[0]['priority'],
  556.             'formatedCreatedAt' => $ticketService->timeZoneConverter($ticket[0]['createdAt']),      
  557.             'ticketLabels' => $ticketService->getTicketLabels($ticket[0]['id']),
  558.             'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  559.             'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  560.             'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  561.             'lastReplyAgentName' => $ticketService->getlastReplyAgentName($ticket[0]['id']),
  562.             'createThread' => $ticketService->getCreateReply($ticket[0]['id']),
  563.             'lastReply' => $ticketService->getLastReply($ticket[0]['id']),
  564.         ];
  565.     }
  567.     // Get customer more ticket sidebar details
  568.     public function getCustomerMoreTicketsSidebar($customerId, $container, $request) {
  569.         $userService = $container->get('user.service');
  570.         $ticketService = $container->get('ticket.service');
  572.         $qb = $this->getEntityManager()->createQueryBuilder();
  573.         $qb->select("DISTINCT ticket as tickets,s.code as statusName, supportTeam.name as teamName,supportGroup.name as groupName, p.code as priorityName, p.colorCode as priorityColor, type.code as typeName, a.id as agentId, CONCAT(a.firstName, ' ', a.lastName) AS agentName")
  574.                 ->from($this->getEntityName(), 'ticket')
  575.                 ->leftJoin('ticket.priority', 'p')
  576.                 ->leftJoin('ticket.status', 's')
  577.                 ->leftJoin('ticket.agent', 'a')
  578.                 ->leftJoin('ticket.type', 'type')
  579.                 ->leftJoin('ticket.supportGroup', 'supportGroup')
  580.                 ->leftJoin('ticket.supportTeam', 'supportTeam')
  581.                 ->leftJoin('a.userInstance', 'ad')
  582.                 ->andWhere('ticket.customer = :customerId')
  583.                 ->andWhere('ticket.isTrashed != 1')
  584.                 ->setParameter('customerId', $customerId)
  585.                 ->andwhere("a IS NULL OR ad.supportRole != 4")
  586.                 ->orderBy('ticket.id', Criteria::DESC);
  588.         $user = $userService->getCurrentUser();
  589.         $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($user);
  590.         $supportTeamReference  = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($user);
  592.         // if($currentUser->getRole() == "ROLE_AGENT" && $currentUser->detail['agent']->getTicketView() != UserData::GLOBAL_ACCESS) {
  593.         //     $this->em->getRepository('WebkulTicketBundle:Ticket')->addPermissionFilter($qb, $this->container, false);
  594.         //     $qb->addSelect('gr.name as groupName');
  595.         // } else {
  596.         //     $qb->leftJoin('t.supportGroup', 'gr');
  597.         //     $qb->addSelect('gr.name as groupName');
  598.         // }
  600.         $this->addPermissionFilter($qb, $user, $supportGroupReference, $supportTeamReference);
  601.        
  602.         $results = $qb->getQuery()->getArrayResult();
  603.         foreach ($results as $key => $ticket) {
  604.             $results[$key] = $ticket['tickets'];
  605.             unset($ticket['tickets']);
  606.             $results[$key] = array_merge($results[$key], $ticket);
  607.             $results[$key]['timestamp']= $userService->convertToTimezone($results[$key]['createdAt']);
  608.             $results[$key]['formatedCreatedAt'] = $results[$key]['createdAt']->format('d-m-Y H:i A');
  609.             $results[$key]['totalThreads']= $ticketService->getTicketTotalThreads($results[$key]['id']);
  610.             
  611.         }
  613.         return $results;
  614.     }
  616.     public function prepareTicketListQueryWithParams($queryBuilder, $params, $actAsUser = null)
  617.     {
  618.         foreach ($params as $field => $fieldValue) {
  619.             if (in_array($field, $this->safeFields)) {
  620.                 continue;
  621.             }
  623.             if($actAsUser != null ) {
  624.                 $userInstance = $actAsUser->getAgentInstance();
  625.                 if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $field == 'mine') || ('ROLE_ADMIN' == $userInstance->getSupportRole()->getCode()) && $field == 'mine') {
  626.                     $fieldValue = $actAsUser->getId();
  627.                 }
  628.             } 
  630.             switch ($field) {
  631.                 case 'label':
  632.                     $queryBuilder->andwhere('supportLabel.id = :labelIds');
  633.                     $queryBuilder->setParameter('labelIds', $fieldValue);
  634.                     break;
  635.                 case 'starred':
  636.                     $queryBuilder->andWhere('ticket.isStarred = 1');
  637.                     break;
  638.                 case 'search':
  639.                     $value = trim($fieldValue);
  640.                     $queryBuilder->andwhere("ticket.subject LIKE :search OR ticket.id  LIKE :search OR customer.email LIKE :search OR CONCAT(customer.firstName,' ', customer.lastName) LIKE :search OR agent.email LIKE :search OR CONCAT(agent.firstName,' ', agent.lastName) LIKE :search");
  641.                     $queryBuilder->setParameter('search', '%'.urldecode($value).'%');
  642.                     break;
  643.                 case 'unassigned':
  644.                     $queryBuilder->andWhere("agent.id is NULL");
  645.                     break;
  646.                 case 'notreplied':
  647.                     $queryBuilder->andWhere('ticket.isReplied = 0');
  648.                     break;
  649.                 case 'mine':
  650.                     $queryBuilder->andWhere('agent = :agentId')->setParameter('agentId', $fieldValue);
  651.                     break;
  652.                 case 'new':
  653.                     $queryBuilder->andwhere('ticket.isNew = 1');
  654.                     break;
  655.                 case 'priority':
  656.                     $queryBuilder->andwhere('priority.id = :priority')->setParameter('priority', $fieldValue);
  657.                     break;
  658.                 case 'type':
  659.                     $queryBuilder->andwhere('type.id IN (:typeCollection)')->setParameter('typeCollection', explode(',', $fieldValue));
  660.                     break;
  661.                 case 'agent':
  662.                     $queryBuilder->andwhere('agent.id IN (:agentCollection)')->setParameter('agentCollection', explode(',', $fieldValue));
  663.                     break;
  664.                 case 'customer':
  665.                     $queryBuilder->andwhere('customer.id IN (:customerCollection)')->setParameter('customerCollection', explode(',', $fieldValue));
  666.                     break;
  667.                 case 'group':
  668.                     $queryBuilder->andwhere('supportGroup.id IN (:groupIds)');
  669.                     $queryBuilder->setParameter('groupIds', explode(',', $fieldValue));
  670.                     break;
  671.                 case 'team':
  672.                     $queryBuilder->andwhere("supportTeam.id In(:subGrpKeys)");
  673.                     $queryBuilder->setParameter('subGrpKeys', explode(',', $fieldValue));
  674.                     break;
  675.                 case 'tag':
  676.                     $queryBuilder->andwhere("supportTags.id In(:tagIds)");
  677.                     $queryBuilder->setParameter('tagIds', explode(',', $fieldValue));
  678.                     break;
  679.                 case 'source':
  680.                     $queryBuilder->andwhere('ticket.source IN (:sources)');
  681.                     $queryBuilder->setParameter('sources', explode(',', $fieldValue));
  682.                     break;
  683.                 case 'after':
  684.                     $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 23:59');
  685.                     if ($date) {
  686.                        // $date = \DateTime::createFromFormat('d-m-Y H:i', $this->userService->convertTimezoneToServer($date, 'd-m-Y H:i'));
  687.                         $queryBuilder->andwhere('ticket.createdAt > :afterDate');
  688.                         $queryBuilder->setParameter('afterDate', $date);
  689.                     }
  690.                     break;
  691.                 case 'before':
  692.                     $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 00:00');
  693.                     if ($date) {
  694.                         //$date = \DateTime::createFromFormat('d-m-Y H:i', $container->get('user.service')->convertTimezoneToServer($date, 'd-m-Y H:i'));
  695.                         $queryBuilder->andwhere('ticket.createdAt < :beforeDate');
  696.                         $queryBuilder->setParameter('beforeDate', $date);
  697.                     }
  698.                     break;
  699.                 case 'repliesLess':
  700.                     $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  701.                         ->groupBy('ticket.id')
  702.                         ->andHaving('count(threads.id) < :threadValueLesser')->setParameter('threadValueLesser', intval($params['repliesLess']));
  703.                     break;
  704.                 case 'repliesMore':
  705.                     $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  706.                         ->groupBy('ticket.id')
  707.                         ->andHaving('count(threads.id) > :threadValueGreater')->setParameter('threadValueGreater', intval($params['repliesMore']));
  708.                     break;
  709.                 case 'mailbox':
  710.                         $queryBuilder->andwhere('ticket.mailboxEmail IN (:mailboxEmails)');
  711.                         $queryBuilder->setParameter('mailboxEmails', explode(',', $fieldValue));
  712.                     break;
  713.                 default:
  714.                     break;
  715.             }
  716.         }
  718.         return $queryBuilder;
  719.     }
  721.     public function getAgentTickets($agentId,$container) {
  722.         $qb = $this->getEntityManager()->createQueryBuilder();
  723.         $qb->select('t')->from(Ticket::class, 't');
  725.         $qb->andwhere('t.agent = :agentId');
  726.         $qb->setParameter('agentId',$agentId);
  728.         return $qb->getQuery()->getResult();
  729.     }
  730. }