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

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() : [];
  64. $data = array_reverse($data);
  65. foreach ($data as $key => $value) {
  66. if (!in_array($key,$this->safeFields)) {
  67. if (isset($data['search']) && $key == 'search') {
  68. $qb->andWhere("t.subject LIKE :subject OR a.email LIKE :agentName OR t.id LIKE :ticketId");
  69. $qb->setParameter('subject', '%'.urldecode(trim($value)).'%');
  70. $qb->setParameter('agentName', '%'.urldecode(trim($value)).'%');
  71. $qb->setParameter('ticketId', '%'.urldecode(trim($value)).'%');
  72. } elseif ($key == 'status') {
  73. $qb->andWhere('t.status = '.intval($value));
  74. }
  75. }
  76. }
  77. $qb->andWhere('t.isTrashed != 1');
  79. if (!isset($data['sort'])) {
  80. $qb->orderBy('t.id',Criteria::DESC);
  81. }
  83. if (isset($data['sort']) && $data['sort'] == "t.updatedAt") {
  84. $qb->orderBy('t.updatedAt',Criteria::DESC);
  85. }
  87. $paginator = $container->get('knp_paginator');
  89. $newQb = clone $qb;
  90. $newQb->select('COUNT(DISTINCT t.id)');
  92. $results = $paginator->paginate(
  93. $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  94. isset($data['page']) ? $data['page'] : 1,
  95. isset($data['limit']) && !empty($data['limit']) ? $data['limit'] : self::LIMIT,
  96. array('distinct' => true)
  97. );
  99. $paginationData = $results->getPaginationData();
  100. $queryParameters = $results->getParams();
  102. $queryParameters['page'] = "replacePage";
  103. $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  105. $data = array();
  106. $userService = $container->get('user.service');
  107. $ticketService = $container->get('ticket.service');
  108. $translatorService = $container->get('translator');
  110. foreach ($results as $key => $ticket) {
  111. $ticket[0]['status']['description'] = $translatorService->trans($ticket[0]['status']['description']);
  113. $data[] = [
  114. 'id' => $ticket[0]['id'],
  115. 'subject' => $ticket[0]['subject'],
  116. 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  117. 'status' => $ticket[0]['status'],
  118. 'source' => $ticket[0]['source'],
  119. 'isStarred' => $ticket[0]['isStarred'],
  120. 'group' => $ticket[0]['supportGroup'],
  121. 'type' => $ticket[0]['type'],
  122. 'priority' => $ticket[0]['priority'],
  123. 'formatedCreatedAt' => $userService->convertToTimezone($ticket[0]['createdAt']),
  124. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  125. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  126. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  127. // 'hasAttachments' => $ticketService->hasAttachments($ticket[0]['id'])
  128. ];
  129. }
  131. $json['tickets'] = $data;
  132. $json['pagination'] = $paginationData;
  134. return $json;
  135. }
  137. public function getAllCustomerTickets(ParameterBag $obj = null, $container, $actAsUser = null)
  138. {
  139. $currentUser = $actAsUser ? : $container->get('user.service')->getCurrentUser();
  140. $json = array();
  141. $qb = $this->getEntityManager()->createQueryBuilder();
  142. $qb->select('DISTINCT t,gr,pr,tp,s,a.id as agentId,c.id as customerId')->from($this->getEntityName(), 't');
  143. $qb->leftJoin('t.agent', 'a');
  144. $qb->leftJoin('a.userInstance', 'ad');
  145. $qb->leftJoin('t.status', 's');
  146. $qb->leftJoin('t.customer', 'c');
  147. $qb->leftJoin('t.supportGroup', 'gr');
  148. $qb->leftJoin('t.priority', 'pr');
  149. $qb->leftJoin('t.type', 'tp');
  150. $qb->leftJoin('t.collaborators', 'tc');
  151. $qb->addSelect("CONCAT(a.firstName,' ', a.lastName) AS name");
  152. $qb->andWhere("t.agent IS NULL OR ad.supportRole != 4");
  154. $data = $obj->all();
  155. $data = array_reverse($data);
  156. foreach ($data as $key => $value) {
  157. if (! in_array($key,$this->safeFields)) {
  158. if (isset($data['search']) && $key == 'search') {
  159. $qb->andWhere("t.subject LIKE :subject OR a.email LIKE :agentName OR t.id LIKE :ticketId");
  160. $qb->setParameter('subject', '%'.urldecode(trim($value)).'%');
  161. $qb->setParameter('agentName', '%'.urldecode(trim($value)).'%');
  162. $qb->setParameter('ticketId', '%'.urldecode(trim($value)).'%');
  163. } elseif ($key == 'status') {
  164. $qb->andWhere('t.status = '.intval($value));
  165. }
  166. }
  167. }
  169. $qb->andWhere('t.customer = :customerId OR tc.id =:collaboratorId');
  170. $qb->setParameter('customerId', $currentUser->getId());
  171. $qb->setParameter('collaboratorId', $currentUser->getId());
  172. $qb->andWhere('t.isTrashed != 1');
  174. if(!isset($data['sort'])) {
  175. $qb->orderBy('t.id',Criteria::DESC);
  176. }
  178. $paginator = $container->get('knp_paginator');
  180. $newQb = clone $qb;
  181. $newQb->select('COUNT(DISTINCT t.id)');
  183. $results = $paginator->paginate(
  184. $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  185. isset($data['page']) ? $data['page'] : 1,
  186. self::LIMIT,
  187. array('distinct' => true)
  188. );
  190. $paginationData = $results->getPaginationData();
  191. $queryParameters = $results->getParams();
  193. $queryParameters['page'] = "replacePage";
  194. $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  196. $data = array();
  197. $userService = $container->get('user.service');
  198. $ticketService = $container->get('ticket.service');
  199. $translatorService = $container->get('translator');
  201. foreach ($results as $key => $ticket) {
  202. $ticket[0]['status']['code'] = $translatorService->trans($ticket[0]['status']['code']);
  204. $data[] = [
  205. 'id' => $ticket[0]['id'],
  206. 'subject' => $ticket[0]['subject'],
  207. 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  208. 'status' => $ticket[0]['status'],
  209. 'group' => $ticket[0]['supportGroup'],
  210. 'type' => $ticket[0]['type'],
  211. 'priority' => $ticket[0]['priority'],
  212. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  213. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  214. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  215. 'formatedCreatedAt' => $userService->getLocalizedFormattedTime($ticket[0]['createdAt'],$userService->getSessionUser()),
  216. ];
  217. }
  219. $json['tickets'] = $data;
  221. $json['pagination'] = $paginationData;
  223. return $json;
  224. }
  226. public function addPermissionFilter($qb, User $user, array $supportGroupReferences = [], array $supportTeamReferences = [])
  227. {
  228. $userInstance = $user->getAgentInstance();
  230. if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $userInstance->getTicketAccesslevel() != self::TICKET_GLOBAL_ACCESS)) {
  231. $qualifiedGroups = empty($this->params['group']) ? $supportGroupReferences : array_intersect($supportGroupReferences, explode(',', $this->params['group']));
  232. $qualifiedTeams = empty($this->params['team']) ? $supportTeamReferences : array_intersect($supportTeamReferences, explode(',', $this->params['team']));
  234. switch ($userInstance->getTicketAccesslevel()) {
  235. case self::TICKET_GROUP_ACCESS:
  236. $qb
  237. ->andWhere("ticket.agent = :agentId OR supportGroup.id IN(:supportGroupIds) OR supportTeam.id IN(:supportTeamIds)")
  238. ->setParameter('agentId', $user->getId())
  239. ->setParameter('supportGroupIds', $qualifiedGroups)
  240. ->setParameter('supportTeamIds', $qualifiedTeams);
  241. break;
  242. case self::TICKET_TEAM_ACCESS:
  243. $qb
  244. ->andWhere("ticket.agent = :agentId OR supportTeam.id IN(:supportTeamIds)")
  245. ->setParameter('agentId', $user->getId())
  246. ->setParameter('supportTeamIds', $qualifiedTeams);
  247. break;
  248. default:
  249. $qb
  250. ->andWhere("ticket.agent = :agentId")
  251. ->setParameter('agentId', $user->getId());
  252. break;
  253. }
  254. }
  256. return $qb;
  257. }
  259. public function prepareBaseTicketQuery(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  260. {
  261. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  262. ->select("
  263. DISTINCT ticket,
  264. supportGroup.name as groupName,
  265. supportTeam.name as teamName,
  266. priority,
  267. type.code as typeName,
  268. agent.id as agentId,
  269. agent.email as agentEmail,
  270. agentInstance.profileImagePath as smallThumbnail,
  271. customer.id as customerId,
  272. customer.email as customerEmail,
  273. customerInstance.profileImagePath as customersmallThumbnail,
  274. CONCAT(customer.firstName, ' ', customer.lastName) AS customerName,
  275. CONCAT(agent.firstName,' ', agent.lastName) AS agentName
  276. ")
  277. ->from(Ticket::class, 'ticket')
  278. ->leftJoin('ticket.type', 'type')
  279. ->leftJoin('ticket.agent', 'agent')
  280. ->leftJoin('ticket.threads', 'threads')
  281. ->leftJoin('ticket.priority', 'priority')
  282. ->leftJoin('ticket.customer', 'customer')
  283. ->leftJoin('ticket.supportTeam', 'supportTeam')
  284. ->leftJoin('ticket.supportTags', 'supportTags')
  285. ->leftJoin('agent.userInstance', 'agentInstance')
  286. ->leftJoin('ticket.supportLabels', 'supportLabel')
  287. ->leftJoin('ticket.supportGroup', 'supportGroup')
  288. ->leftJoin('customer.userInstance', 'customerInstance')
  289. ->where('customerInstance.supportRole = 4')
  290. ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  291. ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false);
  293. if (!isset($params['sort'])) {
  294. $queryBuilder->orderBy('ticket.updatedAt', Criteria::DESC);
  295. }
  297. if ($filterByStatus) {
  298. $queryBuilder->andWhere('ticket.status = :status')->setParameter('status', isset($params['status']) ? $params['status'] : 1);
  299. }
  301. $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  303. // applyFilter according to params
  304. return $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  305. }
  307. public function prepareBasePaginationTicketTypesQuery(array $params)
  308. {
  309. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  310. ->select("ticketType")
  311. ->from(TicketType::class, 'ticketType');
  313. // Apply filters
  314. foreach ($params as $field => $fieldValue) {
  315. if (in_array($field, $this->safeFields)) {
  316. continue;
  317. }
  319. switch ($field) {
  320. case 'search':
  321. $queryBuilder->andWhere("ticketType.code LIKE :searchQuery OR ticketType.description LIKE :searchQuery");
  322. $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  323. break;
  324. case 'isActive':
  325. $queryBuilder->andWhere("ticketType.isActive LIKE :searchQuery");
  326. $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  327. break;
  328. default:
  329. break;
  330. }
  331. }
  333. // Define sort by
  334. if (empty($params['sort']) || 'a.id' == $params['sort']) {
  335. $queryBuilder->orderBy('ticketType.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  336. } else {
  337. $queryBuilder->orderBy('ticketType.code', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  338. }
  340. return $queryBuilder;
  341. }
  343. public function prepareBasePaginationTagsQuery(array $params)
  344. {
  345. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  346. ->select('supportTag.id as id, supportTag.name as name, COUNT(ticket) as totalTickets')
  347. ->from(Tag::class, 'supportTag')
  348. ->leftJoin('supportTag.tickets', 'ticket')
  349. ->groupBy('supportTag.id');
  351. // Apply filters
  352. foreach ($params as $field => $fieldValue) {
  353. if (in_array($field, $this->safeFields)) {
  354. continue;
  355. }
  357. switch ($field) {
  358. case 'search':
  359. $queryBuilder->andWhere("supportTag.name LIKE :searchQuery")->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  360. break;
  361. default:
  362. break;
  363. }
  364. }
  366. // Define sort by
  367. if (empty($params['sort']) || 'a.id' == $params['sort']) {
  368. $queryBuilder->orderBy('supportTag.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  369. } else {
  370. $queryBuilder->orderBy('supportTag.name', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  371. }
  373. return $queryBuilder;
  374. }
  376. public function getTicketTabDetails(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  377. {
  378. $data = array(1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0);
  380. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  381. ->select("
  382. COUNT(DISTINCT ticket.id) as countTicket,
  383. status.id as statusId,
  384. status.code as tab
  385. ")
  386. ->from(Ticket::class, 'ticket')
  387. ->leftJoin('ticket.type', 'type')
  388. ->leftJoin('ticket.agent', 'agent')
  389. ->leftJoin('ticket.status', 'status')
  390. ->leftJoin('ticket.threads', 'threads')
  391. ->leftJoin('ticket.priority', 'priority')
  392. ->leftJoin('ticket.customer', 'customer')
  393. ->leftJoin('ticket.supportTeam', 'supportTeam')
  394. ->leftJoin('ticket.supportTags', 'supportTags')
  395. ->leftJoin('ticket.supportGroup', 'supportGroup')
  396. ->leftJoin('agent.userInstance', 'agentInstance')
  397. ->leftJoin('ticket.supportLabels', 'supportLabel')
  398. ->leftJoin('customer.userInstance', 'customerInstance')
  399. ->where('customerInstance.supportRole = 4')
  400. ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  401. ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false)
  402. ->groupBy('status');
  404. // applyFilter according to permission
  405. $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  407. $queryBuilder = $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  408. $results = $queryBuilder->getQuery()->getResult();
  410. foreach ($results as $status) {
  411. $data[$status['statusId']] += $status['countTicket'];
  412. }
  414. return $data;
  415. }
  417. public function countTicketTotalThreads($ticketId, $threadType = 'reply')
  418. {
  419. $totalThreads = $this->getEntityManager()->createQueryBuilder()
  420. ->select('COUNT(thread.id) as threads')
  421. ->from(Ticket::class, 'ticket')
  422. ->leftJoin('ticket.threads', 'thread')
  423. ->where('ticket.id = :ticketId')->setParameter('ticketId', $ticketId)
  424. ->andWhere('thread.threadType = :threadType')->setParameter('threadType', $threadType)
  425. ->getQuery()->getSingleScalarResult();
  427. return (int) $totalThreads;
  428. }
  430. public function getTicketNavigationIteration($ticket, $container)
  431. {
  432. $ticketsCollection = $this->getEntityManager()->getRepository(Ticket::class)
  433. ->getAllTickets(null, $container);
  435. if ($ticketsCollection)
  436. $results = $ticketsCollection['tickets'];
  438. $nextPrevPage = array('next' => 0,'prev' => 0);
  439. for ($i = 0; $i < count($results); $i++) {
  440. if ($results[$i]['id'] == $ticket->getId()) {
  441. $nextPrevPage['next'] = isset($results[$i + 1]) ? $results[$i + 1]['id'] : 0;
  442. $nextPrevPage['prev'] = isset($results[$i - 1]) ? $results[$i - 1]['id'] : 0;
  443. }
  444. }
  446. return $nextPrevPage;
  447. }
  449. public function countCustomerTotalTickets(User $user, $container)
  450. {
  451. $userService = $container->get('user.service');
  453. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  454. ->select('COUNT(ticket.id) as tickets')
  455. ->from(Ticket::class, 'ticket')
  456. ->leftJoin('ticket.priority', 'p')
  457. ->leftJoin('ticket.status', 's')
  458. ->leftJoin('ticket.agent', 'a')
  459. ->leftJoin('ticket.type', 'type')
  460. ->leftJoin('ticket.supportGroup', 'supportGroup')
  461. ->leftJoin('ticket.supportTeam', 'supportTeam')
  462. ->leftJoin('a.userInstance', 'ad')
  463. ->andWhere('ticket.customer = :customerId')
  464. ->andWhere('ticket.isTrashed != 1')
  465. ->setParameter('customerId', $user->getId())
  466. ->andWhere("a IS NULL OR ad.supportRole != 4")
  467. ->orderBy('ticket.id', Criteria::DESC);
  469. $agent = $userService->getCurrentUser();
  470. $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($agent);
  471. $supportTeamReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($agent);
  473. $this->addPermissionFilter($queryBuilder, $agent, $supportGroupReference, $supportTeamReference);
  475. return (int) $queryBuilder->getQuery()->getSingleScalarResult();
  476. }
  478. public function isLabelAlreadyAdded($ticket,$label)
  479. {
  480. $qb = $this->getEntityManager()->createQueryBuilder();
  481. $qb->select('COUNT(t.id) as ticketCount')->from(Ticket::class, 't')
  482. ->leftJoin('t.supportLabels','tl')
  483. ->andWhere('tl.id = :labelId')
  484. ->andWhere('t.id = :ticketId')
  485. ->setParameter('labelId',$label->getId())
  486. ->setParameter('ticketId',$ticket->getId());
  488. return $qb->getQuery()->getSingleScalarResult() ? true : false;
  489. }
  491. public function isTicketCollaborator($ticket, $collaboratorEmail)
  492. {
  493. if ($ticket->getCollaborators()) {
  494. foreach ($ticket->getCollaborators() as $collaborator) {
  495. if (strtolower($collaborator->getEmail()) == strtolower($collaboratorEmail)) {
  496. return true;
  497. }
  498. }
  499. }
  501. return false;
  502. }
  504. public function getTicketDetails(ParameterBag $obj = null, $container)
  505. {
  506. $data = $obj->all();
  507. $userService = $container->get('user.service');
  508. $ticketService = $container->get('ticket.service');
  509. $json = [];
  510. $qb = $this->getEntityManager()->createQueryBuilder();
  511. $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')
  512. ->leftJoin('t.agent', 'a')
  513. ->leftJoin('t.status', 's')
  514. ->leftJoin('t.customer', 'c')
  515. ->leftJoin('t.supportGroup', 'gr')
  516. ->leftJoin('t.supportTeam', 'supportTeam')
  517. ->leftJoin('t.priority', 'pr')
  518. ->leftJoin('t.type', 'tp')
  519. ->leftJoin('c.userInstance', 'cd')
  520. ->leftJoin('a.userInstance', 'ad')
  521. ->leftJoin('t.supportTags', 'tg')
  522. ->leftJoin('t.supportLabels', 'tl')
  523. ->andWhere('t.id = :ticketId')
  524. ->setParameter('ticketId', $data['ticketId']);
  526. $results = $qb->getQuery()->getArrayResult();
  527. $ticket = array_shift($results);
  529. return [
  530. 'id' => $ticket[0]['id'],
  531. 'subject' => $ticket[0]['subject'],
  532. 'isStarred' => $ticket[0]['isStarred'],
  533. 'isAgentView' => $ticket[0]['isAgentViewed'],
  534. 'isTrashed' => $ticket[0]['isTrashed'],
  535. 'status' => $ticket[0]['status'],
  536. 'groupName' => $ticket['groupName'],
  537. 'subGroupName' => $ticket['supportTeamName'],
  538. 'typeName' => $ticket['typeName'],
  539. 'priority' => $ticket[0]['priority'],
  540. 'formatedCreatedAt' => $ticketService->timeZoneConverter($ticket[0]['createdAt']),
  541. 'ticketLabels' => $ticketService->getTicketLabels($ticket[0]['id']),
  542. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  543. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  544. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  545. 'lastReplyAgentName' => $ticketService->getlastReplyAgentName($ticket[0]['id']),
  546. 'createThread' => $ticketService->getCreateReply($ticket[0]['id']),
  547. 'lastReply' => $ticketService->getLastReply($ticket[0]['id']),
  548. ];
  549. }
  551. // Get customer more ticket sidebar details
  552. public function getCustomerMoreTicketsSidebar($customerId, $container, $request) {
  553. $userService = $container->get('user.service');
  554. $ticketService = $container->get('ticket.service');
  556. $qb = $this->getEntityManager()->createQueryBuilder();
  557. $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")
  558. ->from($this->getEntityName(), 'ticket')
  559. ->leftJoin('ticket.priority', 'p')
  560. ->leftJoin('ticket.status', 's')
  561. ->leftJoin('ticket.agent', 'a')
  562. ->leftJoin('ticket.type', 'type')
  563. ->leftJoin('ticket.supportGroup', 'supportGroup')
  564. ->leftJoin('ticket.supportTeam', 'supportTeam')
  565. ->leftJoin('a.userInstance', 'ad')
  566. ->andWhere('ticket.customer = :customerId')
  567. ->andWhere('ticket.isTrashed != 1')
  568. ->setParameter('customerId', $customerId)
  569. ->andWhere("a IS NULL OR ad.supportRole != 4")
  570. ->orderBy('ticket.id', Criteria::DESC);
  572. $user = $userService->getCurrentUser();
  573. $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($user);
  574. $supportTeamReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($user);
  576. // if($currentUser->getRole() == "ROLE_AGENT" && $currentUser->detail['agent']->getTicketView() != UserData::GLOBAL_ACCESS) {
  577. // $this->em->getRepository('WebkulTicketBundle:Ticket')->addPermissionFilter($qb, $this->container, false);
  578. // $qb->addSelect('gr.name as groupName');
  579. // } else {
  580. // $qb->leftJoin('t.supportGroup', 'gr');
  581. // $qb->addSelect('gr.name as groupName');
  582. // }
  584. $this->addPermissionFilter($qb, $user, $supportGroupReference, $supportTeamReference);
  586. $results = $qb->getQuery()->getArrayResult();
  587. foreach ($results as $key => $ticket) {
  588. $results[$key] = $ticket['tickets'];
  589. unset($ticket['tickets']);
  590. $results[$key] = array_merge($results[$key], $ticket);
  591. $results[$key]['timestamp']= $userService->convertToTimezone($results[$key]['createdAt']);
  592. $results[$key]['formatedCreatedAt'] = $results[$key]['createdAt']->format('d-m-Y H:i A');
  593. $results[$key]['totalThreads']= $ticketService->getTicketTotalThreads($results[$key]['id']);
  595. }
  597. return $results;
  598. }
  600. public function prepareTicketListQueryWithParams($queryBuilder, $params, $actAsUser = null)
  601. {
  602. foreach ($params as $field => $fieldValue) {
  603. if (in_array($field, $this->safeFields)) {
  604. continue;
  605. }
  607. if ($actAsUser != null ) {
  608. $userInstance = $actAsUser->getAgentInstance();
  609. if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $field == 'mine') || ('ROLE_ADMIN' == $userInstance->getSupportRole()->getCode()) && $field == 'mine') {
  610. $fieldValue = $actAsUser->getId();
  611. }
  612. }
  614. switch ($field) {
  615. case 'label':
  616. $queryBuilder->andWhere('supportLabel.id = :labelIds');
  617. $queryBuilder->setParameter('labelIds', $fieldValue);
  618. break;
  619. case 'starred':
  620. $queryBuilder->andWhere('ticket.isStarred = 1');
  621. break;
  622. case 'search':
  623. $value = trim($fieldValue);
  624. $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");
  625. $queryBuilder->setParameter('search', '%'.urldecode($value).'%');
  626. break;
  627. case 'unassigned':
  628. $queryBuilder->andWhere("agent.id is NULL");
  629. break;
  630. case 'notreplied':
  631. $queryBuilder->andWhere('ticket.isReplied = 0');
  632. break;
  633. case 'mine':
  634. $queryBuilder->andWhere('agent = :agentId')->setParameter('agentId', $fieldValue);
  635. break;
  636. case 'new':
  637. $queryBuilder->andWhere('ticket.isNew = 1');
  638. break;
  639. case 'priority':
  640. $queryBuilder->andWhere('priority.id = :priority')->setParameter('priority', $fieldValue);
  641. break;
  642. case 'type':
  643. $queryBuilder->andWhere('type.id IN (:typeCollection)')->setParameter('typeCollection', explode(',', $fieldValue));
  644. break;
  645. case 'agent':
  646. $queryBuilder->andWhere('agent.id IN (:agentCollection)')->setParameter('agentCollection', explode(',', $fieldValue));
  647. break;
  648. case 'customer':
  649. $queryBuilder->andWhere('customer.id IN (:customerCollection)')->setParameter('customerCollection', explode(',', $fieldValue));
  650. break;
  651. case 'group':
  652. $queryBuilder->andWhere('supportGroup.id IN (:groupIds)');
  653. $queryBuilder->setParameter('groupIds', explode(',', $fieldValue));
  654. break;
  655. case 'team':
  656. $queryBuilder->andWhere("supportTeam.id In(:subGrpKeys)");
  657. $queryBuilder->setParameter('subGrpKeys', explode(',', $fieldValue));
  658. break;
  659. case 'tag':
  660. $queryBuilder->andWhere("supportTags.id In(:tagIds)");
  661. $queryBuilder->setParameter('tagIds', explode(',', $fieldValue));
  662. break;
  663. case 'source':
  664. $queryBuilder->andWhere('ticket.source IN (:sources)');
  665. $queryBuilder->setParameter('sources', explode(',', $fieldValue));
  666. break;
  667. case 'after':
  668. $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 23:59');
  669. if ($date) {
  670. // $date = \DateTime::createFromFormat('d-m-Y H:i', $this->userService->convertTimezoneToServer($date, 'd-m-Y H:i'));
  671. $queryBuilder->andWhere('ticket.createdAt > :afterDate');
  672. $queryBuilder->setParameter('afterDate', $date);
  673. }
  674. break;
  675. case 'before':
  676. $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 00:00');
  677. if ($date) {
  678. //$date = \DateTime::createFromFormat('d-m-Y H:i', $container->get('user.service')->convertTimezoneToServer($date, 'd-m-Y H:i'));
  679. $queryBuilder->andWhere('ticket.createdAt < :beforeDate');
  680. $queryBuilder->setParameter('beforeDate', $date);
  681. }
  682. break;
  683. case 'repliesLess':
  684. $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  685. ->groupBy('ticket.id')
  686. ->andHaving('count(threads.id) < :threadValueLesser')->setParameter('threadValueLesser', intval($params['repliesLess']));
  687. break;
  688. case 'repliesMore':
  689. $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  690. ->groupBy('ticket.id')
  691. ->andHaving('count(threads.id) > :threadValueGreater')->setParameter('threadValueGreater', intval($params['repliesMore']));
  692. break;
  693. case 'mailbox':
  694. $queryBuilder->andWhere('ticket.mailboxEmail IN (:mailboxEmails)');
  695. $queryBuilder->setParameter('mailboxEmails', explode(',', $fieldValue));
  696. break;
  697. default:
  698. break;
  699. }
  700. }
  702. return $queryBuilder;
  703. }
  705. public function getAgentTickets($agentId,$container) {
  706. $qb = $this->getEntityManager()->createQueryBuilder();
  707. $qb->select('t')->from(Ticket::class, 't');
  709. $qb->andWhere('t.agent = :agentId');
  710. $qb->setParameter('agentId',$agentId);
  712. return $qb->getQuery()->getResult();
  713. }
  714. }