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

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