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

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('t.isTrashed != 1')
  51. ->andWhere('cd.supportRole = 4');
  53. $container->get('report.service')->addPermissionFilter($qb, $container);
  55. if (isset($data['priority'])) {
  56. $qb->andWhere('pr.id IN (:priorityIds)');
  57. $qb->setParameter('priorityIds', explode(',', $data['priority']));
  58. }
  59. if (isset($data['type'])) {
  60. $qb->andWhere('tp.id IN (:typeIds)');
  61. $qb->setParameter('typeIds', explode(',', $data['type']));
  62. }
  63. if (isset($data['agent'])) {
  64. $qb->andWhere('a.id IN (:agentIds)');
  65. $qb->setParameter('agentIds', explode(',', $data['agent']));
  66. }
  67. if (isset($data['customer'])) {
  68. $qb->andWhere('tc.id IN (:customerIds)');
  69. $qb->setParameter('customerIds', explode(',', $data['customer']));
  70. }
  71. if (isset($data['group'])) {
  72. $qb->andWhere('gr.id IN (:groupIds)');
  73. $qb->setParameter('groupIds', explode(',', $data['group']));
  74. }
  75. if (isset($data['team'])) {
  76. $qb->andWhere('te.id IN (:teamIds)');
  77. $qb->setParameter('teamIds', explode(',', $data['team']));
  78. }
  79. if (isset($data['source'])) {
  80. $qb->andWhere('t.source IN (:sources)');
  81. $qb->setParameter('sources', explode(',', $data['source']));
  82. }
  84. if (!isset($data['sort'])){
  85. $qb->orderBy('r.createdAt',Criteria::DESC);
  86. }
  88. $paginator = $container->get('knp_paginator');
  89. $newQb = clone $qb;
  90. $newQb->select('DISTINCT r.id');
  91. $results = $paginator->paginate(
  92. $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', count($newQb->getQuery()->getResult())),
  93. isset($data['page']) ? $data['page'] : 1,
  94. self::LIMIT,
  95. array('distinct' => true)
  96. );
  98. $paginationData = $results->getPaginationData();
  99. $queryParameters = $results->getParams();
  100. $data = [];
  101. foreach ($results as $rating) {
  102. $customer = array(
  103. 'id' => $rating['customerId'],
  104. 'name' => $rating['name'],
  105. 'email' => $rating['email'],
  106. 'smallThumbnail' => $rating['smallThumbnail']
  107. );
  108. $data[] = array(
  109. 'id' => $rating[0]['id'],
  110. 'ticketId' => $rating['ticketId'],
  111. 'customer' => $customer,
  112. 'count' => $rating[0]['stars'],
  113. '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;
  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. ->groupBy('r.ticket')
  139. ->setParameter('startDate', $startDate." 00:00:01")
  140. ->setParameter('endDate', $endDate." 23:59:59");
  142. $container->get('report.service')->addPermissionFilter($qb, $this->container);
  144. $qb = $this->filterQuerySlim($qb, $data_time);
  145. $result = $qb->getQuery()->getResult();
  147. $ratedCustomerCount = 0;
  148. $rateTotal = 0;
  149. foreach ($result as $rating) {
  150. $rateTotal += $rating['avgCount'];
  151. $ratedCustomerCount += $rating['totalRatedCustomer'];
  152. }
  153. $ratePercent = $rateTotal ? ($rateTotal / ( count($result) * 5 )) : 0;
  155. $data['rating'] = $ratePercent;
  156. $data['ratedCustomer'] = $ratedCustomerCount;
  157. $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('r.stars = :count')
  217. ->setParameter('startDate', $startDate." 00:00:01")
  218. ->setParameter('endDate', $endDate." 23:59:59")
  219. ->setParameter('count', $rateId);
  221. $container->get('report.service')->addPermissionFilter($qb, $this->container);
  223. $qb = $this->filterQuerySlim($qb, $data_time);
  225. return $qb->getQuery()->getSingleScalarResult();
  226. }
  227. }