vendor/uvdesk/core-framework/Repository/TicketRatingRepository.php line 203

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\TicketRating;
  11. use Symfony\Component\HttpFoundation\ParameterBag;
  12. use Symfony\Component\DependencyInjection\ContainerInterface;
  13. use Symfony\Component\HttpFoundation\RequestStack;
  14. use Doctrine\ORM\EntityManagerInterface;
  16. /**
  17.  * TicketRatingRepository
  18.  *
  19.  * This class was generated by the Doctrine ORM. Add your own custom
  20.  * repository methods below.
  21.  */
  22. class TicketRatingRepository extends \Doctrine\ORM\EntityRepository
  23. {
  24.     public $safeFields = array('page','limit','sort','order','direction');
  25.     const LIMIT = 10;
  26.     private $container;
  28.     public function getRatedTicketList(\Symfony\Component\HttpFoundation\ParameterBag $obj = null, $container) {
  29.         $data = array_reverse($obj->all());
  30.         $userService = $container->get('user.service');
  31.         $startDate = $userService->convertToTimezone(new \DateTime($data['start']),'Y-m-d');
  32.         $endDate = $userService->convertToTimezone(new \DateTime($data['end']),'Y-m-d');
  34.         $json = array();
  35.         $qb = $this->getEntityManager()->createQueryBuilder();
  36.         $qb->select('r,c.id as customerId,c.email,cd.profileImagePath as smallThumbnail,t.id as ticketId')->from($this->getEntityName(), 'r')
  37.                 ->leftJoin('r.ticket', 't')
  38.                 ->leftJoin('t.agent', 'a')
  39.                 ->leftJoin('t.customer', 'tc')
  40.                 ->leftJoin('t.supportGroup', 'gr')
  41.                 ->leftJoin('t.supportTeam', 'te')
  42.                 ->leftJoin('t.priority', 'pr')
  43.                 ->leftJoin('t.type', 'tp')
  44.                 ->leftJoin('r.customer', 'c')
  45.                 ->leftJoin('c.userInstance', 'cd')
  46.                 ->addSelect("CONCAT(c.firstName,' ', c.lastName) AS name")
  47.                 ->where('r.createdAt BETWEEN :startDate AND :endDate')
  48.                 ->setParameter('startDate', $startDate." 00:00:01")
  49.                 ->setParameter('endDate', $endDate." 23:59:59")
  50.                 ->andwhere('cd.supportRole = 4');
  52.         $container->get('report.service')->addPermissionFilter($qb, $container);
  54.         if(isset($data['priority'])) { 
  55.             $qb->andwhere('pr.id IN (:priorityIds)');
  56.             $qb->setParameter('priorityIds', explode(',', $data['priority']));
  57.         } 
  58.         if(isset($data['type'])) { 
  59.             $qb->andwhere('tp.id IN (:typeIds)');
  60.             $qb->setParameter('typeIds', explode(',', $data['type']));
  61.         } 
  62.         if(isset($data['agent'])) { 
  63.             $qb->andwhere('a.id IN (:agentIds)');
  64.             $qb->setParameter('agentIds', explode(',', $data['agent']));
  65.         } 
  66.         if(isset($data['customer'])) { 
  67.             $qb->andwhere('tc.id IN (:customerIds)');
  68.             $qb->setParameter('customerIds', explode(',', $data['customer']));
  69.         } 
  70.         if(isset($data['group'])) { 
  71.             $qb->andwhere('gr.id IN (:groupIds)');
  72.             $qb->setParameter('groupIds', explode(',', $data['group']));
  73.         }
  74.         if(isset($data['team'])) { 
  75.             $qb->andwhere('te.id IN (:teamIds)');
  76.             $qb->setParameter('teamIds', explode(',', $data['team']));
  77.         }
  78.         if(isset($data['source'])) { 
  79.             $qb->andwhere('t.source IN (:sources)');
  80.             $qb->setParameter('sources', explode(',', $data['source']));
  81.         }
  83.         if(!isset($data['sort'])){
  84.             $qb->orderBy('r.createdAt',Criteria::DESC);
  85.         }
  87.         $paginator  = $container->get('knp_paginator');
  88.         $newQb = clone $qb;
  89.         $newQb->select('DISTINCT r.id');
  90.         $results = $paginator->paginate(
  91.             $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', count($newQb->getQuery()->getResult())),
  92.             isset($data['page']) ? $data['page'] : 1,
  93.             self::LIMIT,
  94.             array('distinct' => true)
  95.         );
  97.         $paginationData = $results->getPaginationData();
  98.         $queryParameters = $results->getParams();
  99.         $data = [];
  100.         foreach ($results as $rating) {
  101.             $customer = array(
  102.                                 'id' => $rating['customerId'],
  103.                                 'name' => $rating['name'],
  104.                                 'email' => $rating['email'],
  105.                                 'smallThumbnail' => $rating['smallThumbnail']
  106.                             );
  107.             $data[] = array(
  108.                         'id' => $rating[0]['id'],
  109.                         'ticketId' => $rating['ticketId'],
  110.                         'customer' => $customer,
  111.                         'count' => $rating[0]['stars'],
  112.                         'formatedRatedAt' => $rating[0]['createdAt']->format('d-m-Y H:i A'),
  114.                     );
  115.         }
  117.         $paginationData['url'] = '#'.$container->get('report.service')->symfony_http_build_query($queryParameters);
  118.         $json['ratedTickets'] = $data;
  119.         $json['pagination'] = $paginationData;
  120.        
  121.         return $json;
  122.     }
  124.     public function getRatingData(\Symfony\Component\HttpFoundation\ParameterBag $obj = null, $container)
  125.     {
  126.         $data_time = array_reverse($obj->all());
  127.         $userService = $container->get('user.service');
  128.         $startDate = $userService->convertToTimezone(new \DateTime($data_time['start']),'Y-m-d');
  129.         $endDate = $userService->convertToTimezone(new \DateTime($data_time['end']),'Y-m-d');
  131.         $data = array();
  133.         $qb = $this->getEntityManager()->createQueryBuilder();
  134.         $qb->select('avg(r.stars) as avgCount, count(r.customer) as totalRatedCustomer')->from(TicketRating::class, 'r')
  135.                 ->leftJoin('r.ticket', 't')
  136.                 ->andwhere('r.createdAt BETWEEN :startDate AND :endDate')
  137.                 ->andwhere('t.isTrashed != 1')
  138.                 ->andwhere('t.status != 5')
  139.                 ->groupBy('r.ticket')
  140.                 ->setParameter('startDate', $startDate." 00:00:01")
  141.                 ->setParameter('endDate', $endDate." 23:59:59");
  143.         $container->get('report.service')->addPermissionFilter($qb, $this->container);
  145.         $qb = $this->filterQuerySlim($qb, $data_time);
  146.         $result = $qb->getQuery()->getResult();
  148.         $ratedCustomerCount = 0;
  149.         $rateTotal = 0;
  150.         foreach ($result as $rating) {
  151.             $rateTotal += $rating['avgCount'];
  152.             $ratedCustomerCount += $rating['totalRatedCustomer'];
  153.         }
  154.         $ratePercent = $rateTotal ? ($rateTotal / ( count($result) * 5 )) : 0;
  156.         $data['rating'] = $ratePercent;
  157.         $data['ratedCustomer'] = $ratedCustomerCount;
  158.         $data['totalCustomer'] = $container->get('user.service')->getCustomersCountForKudos($container);
  159.         return $data;
  160.     }
  162.     public function filterQuerySlim($qb, $data_time, $filterAgent = true)
  163.     {
  164.         if(isset($data_time['priority'])) {
  165.             $qb->leftJoin('t.priority', 'pr')
  166.                 ->andwhere('pr.id IN (:priorityIds)')
  167.                 ->setParameter('priorityIds', explode(',', $data_time['priority']));
  168.         }
  170.         if(isset($data_time['type'])) {
  171.             $qb->leftJoin('t.type', 'tp')
  172.                 ->andwhere('tp.id IN (:typeIds)')
  173.                 ->setParameter('typeIds', explode(',', $data_time['type']));
  174.         }
  176.         if($filterAgent && isset($data_time['agent'])) {
  177.             $qb->leftJoin('t.agent', 'a')
  178.                 ->andwhere('a.id IN (:agentIds)')
  179.                 ->setParameter('agentIds', explode(',', $data_time['agent']));
  180.         }
  182.         if(isset($data_time['customer'])) {
  183.             $qb->leftJoin('t.customer', 'c')
  184.                 ->andwhere('c.id IN (:customerIds)')
  185.                 ->setParameter('customerIds', explode(',', $data_time['customer']));
  186.         }
  188.         if(isset($data_time['group'])) {
  189.             $qb->leftJoin('t.supportGroup', 'gr')
  190.                 ->andwhere('gr.id IN (:groupIds)')
  191.                 ->setParameter('groupIds', explode(',', $data_time['group']));
  192.         }
  194.         if(isset($data_time['team'])) {
  195.             $qb->leftJoin('t.supportTeam', 'tSub')
  196.                 ->andwhere('tSub.id IN (:subGroupIds)')
  197.                 ->setParameter('subGroupIds', explode(',', $data_time['team']));
  198.         }
  200.         return $qb;
  201.     }
  203.     public function getRatingByStarCount(\Symfony\Component\HttpFoundation\ParameterBag $obj = null, $rateId, $container)
  204.     {
  205.         $data_time = array_reverse($obj->all());
  206.         $userService = $container->get('user.service');
  207.         $startDate = $userService->convertToTimezone(new \DateTime($data_time['start']),'Y-m-d');
  208.         $endDate = $userService->convertToTimezone(new \DateTime($data_time['end']),'Y-m-d');
  210.         $data = array();
  211.         $qb = $this->getEntityManager()->createQueryBuilder();
  212.         $qb->select('COUNT(r.id)')->from(TicketRating::class, 'r')
  213.                 ->leftJoin('r.ticket', 't')
  214.                 ->andwhere('r.createdAt BETWEEN :startDate AND :endDate')
  215.                 ->andwhere('t.isTrashed != 1')
  216.                 ->andwhere('t.status != 5')
  217.                 ->andwhere('r.stars = :count')
  218.                 ->setParameter('startDate', $startDate." 00:00:01")
  219.                 ->setParameter('endDate', $endDate." 23:59:59")
  220.                 ->setParameter('count', $rateId);
  222.                 $container->get('report.service')->addPermissionFilter($qb, $this->container);
  224.         $qb = $this->filterQuerySlim($qb, $data_time);
  226.         return $qb->getQuery()->getSingleScalarResult();
  227.     }
  229. }