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

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 (isset($data['group']) && $key == 'group') {
  73. // Prüfen ob mehrere Gruppen (Array)
  74. if (is_array($value)) {
  75. // Alle Werte zu Integer konvertieren
  76. $groups = array_map('intval', $value);
  77. $groups = array_filter($groups, fn($v) => $v > 0);
  79. if (!empty($groups)) {
  80. $qb->andWhere($qb->expr()->in('t.supportGroup', $groups));
  81. }
  83. // Prüfen ob Komma-String "1,2,3"
  84. } elseif (strpos($value, ',') !== false) {
  85. $parts = explode(',', $value);
  86. $groups = array_map('intval', $parts);
  87. $groups = array_filter($groups, fn($v) => $v > 0);
  89. if (!empty($groups)) {
  90. $qb->andWhere($qb->expr()->in('t.supportGroup', $groups));
  91. }
  93. // Einzelwert
  94. } else {
  95. $qb->andWhere('t.supportGroup = :singleGroup');
  96. $qb->setParameter('singleGroup', intval($value));
  97. }
  98. } elseif ($key == 'status') {
  99. $qb->andWhere('t.status = '.intval($value));
  100. } elseif ($key == 'nstatus') {
  101. $qb->andWhere('t.status != '.intval($value));
  102. } elseif ($key == 'lstatus') {
  103. $qb->andWhere('t.status IN ('.str_replace('+', ',', $value).')');
  104. }
  105. }
  106. }
  107. $qb->andWhere('t.isTrashed != 1');
  109. if (!isset($data['sort'])) {
  110. $qb->orderBy('t.id',Criteria::DESC);
  111. }
  113. if (isset($data['sort']) && $data['sort'] == "t.updatedAt") {
  114. $qb->orderBy('t.updatedAt',Criteria::DESC);
  115. }
  117. $paginator = $container->get('knp_paginator');
  119. $newQb = clone $qb;
  120. $newQb->select('COUNT(DISTINCT t.id)');
  121. $query = $newQb->getQuery();
  124. try {
  125. $results = $paginator->paginate(
  126. $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  127. isset($data['page']) ? $data['page'] : 1,
  128. isset($data['limit']) && !empty($data['limit']) ? $data['limit'] : self::LIMIT,
  129. array('distinct' => true)
  130. );
  131. } catch (\Throwable $e) {
  132. dump($e->getMessage());
  133. dump($e->getTraceAsString());
  134. die();
  135. }
  137. $paginationData = $results->getPaginationData();
  138. $queryParameters = $results->getParams();
  140. $queryParameters['page'] = "replacePage";
  141. // Arrays in Query-Parameter sauber serialisieren, damit rawurlencode funktioniert
  142. $cleanQueryParameters = $queryParameters;
  144. foreach ($cleanQueryParameters as $key => $value) {
  145. if (is_array($value)) {
  146. // Standard UVDesk expects group=1,4,5 format
  147. $cleanQueryParameters[$key] = implode(',', $value);
  148. }
  149. }
  151. try {
  152. $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($cleanQueryParameters);
  153. } catch (\Throwable $e) {
  154. dump($e->getMessage());
  155. dump($e->getTraceAsString());
  156. die();
  157. }
  159. $data = array();
  160. $userService = $container->get('user.service');
  161. $ticketService = $container->get('ticket.service');
  162. $translatorService = $container->get('translator');
  166. foreach ($results as $key => $ticket) {
  167. $ticket[0]['status']['description'] = $translatorService->trans($ticket[0]['status']['description']);
  168. //print_r($ticket[0]);die();
  170. $data[] = [
  171. 'id' => $ticket[0]['id'],
  172. 'subject' => $ticket[0]['subject'],
  173. 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  174. 'status' => $ticket[0]['status'],
  175. 'source' => $ticket[0]['source'],
  176. 'isStarred' => $ticket[0]['isStarred'],
  177. 'group' => $ticket[0]['supportGroup'],
  178. 'type' => $ticket[0]['type'],
  179. 'priority' => $ticket[0]['priority'],
  180. //'formatedCreatedAt' => $userService->convertToTimezone($ticket[0]['createdAt']),
  181. 'formatedCreatedAt' => $ticket[0]['createdAt'],
  182. //'formatedUpdatedAt' => $userService->convertToTimezone($ticket[0]['updatedAt']),
  183. 'formatedUpdatedAt' => $ticket[0]['updatedAt'],
  184. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  185. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  186. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  187. 'hasAttachments' => $ticketService->hasAttachments($ticket[0]['id'])
  188. ];
  189. }
  191. $json['tickets'] = $data;
  192. $json['pagination'] = $paginationData;
  194. return $json;
  195. }
  197. public function getAllCustomerTickets(ParameterBag $obj = null, $container, $actAsUser = null)
  198. {
  199. $currentUser = $actAsUser ? : $container->get('user.service')->getCurrentUser();
  200. $json = array();
  201. $qb = $this->getEntityManager()->createQueryBuilder();
  202. $qb->select('DISTINCT t,gr,pr,tp,s,a.id as agentId,c.id as customerId')->from($this->getEntityName(), 't');
  203. $qb->leftJoin('t.agent', 'a');
  204. $qb->leftJoin('a.userInstance', 'ad');
  205. $qb->leftJoin('t.status', 's');
  206. $qb->leftJoin('t.customer', 'c');
  207. $qb->leftJoin('t.supportGroup', 'gr');
  208. $qb->leftJoin('t.priority', 'pr');
  209. $qb->leftJoin('t.type', 'tp');
  210. $qb->leftJoin('t.collaborators', 'tc');
  211. $qb->addSelect("CONCAT(a.firstName,' ', a.lastName) AS name");
  212. $qb->andWhere("t.agent IS NULL OR ad.supportRole != 4");
  214. $data = $obj->all();
  215. $data = array_reverse($data);
  216. foreach ($data as $key => $value) {
  217. if (! in_array($key,$this->safeFields)) {
  218. if (isset($data['search']) && $key == 'search') {
  219. $qb->andWhere("t.subject LIKE :subject OR a.email LIKE :agentName OR t.id LIKE :ticketId");
  220. $qb->setParameter('subject', '%'.urldecode(trim($value)).'%');
  221. $qb->setParameter('agentName', '%'.urldecode(trim($value)).'%');
  222. $qb->setParameter('ticketId', '%'.urldecode(trim($value)).'%');
  223. } elseif ($key == 'status') {
  224. $qb->andWhere('t.status = '.intval($value));
  225. }
  226. }
  227. }
  229. $qb->andWhere('t.customer = :customerId OR tc.id =:collaboratorId');
  230. $qb->setParameter('customerId', $currentUser->getId());
  231. $qb->setParameter('collaboratorId', $currentUser->getId());
  232. $qb->andWhere('t.isTrashed != 1');
  234. if(!isset($data['sort'])) {
  235. $qb->orderBy('t.id',Criteria::DESC);
  236. }
  238. $paginator = $container->get('knp_paginator');
  240. $newQb = clone $qb;
  241. $newQb->select('COUNT(DISTINCT t.id)');
  243. $results = $paginator->paginate(
  244. $qb->getQuery()->setHydrationMode(Query::HYDRATE_ARRAY)->setHint('knp_paginator.count', $newQb->getQuery()->getSingleScalarResult()),
  245. isset($data['page']) ? $data['page'] : 1,
  246. self::LIMIT,
  247. array('distinct' => true)
  248. );
  250. $paginationData = $results->getPaginationData();
  251. $queryParameters = $results->getParams();
  253. $queryParameters['page'] = "replacePage";
  254. $paginationData['url'] = '#'.$container->get('uvdesk.service')->buildPaginationQuery($queryParameters);
  256. $data = array();
  257. $userService = $container->get('user.service');
  258. $ticketService = $container->get('ticket.service');
  259. $translatorService = $container->get('translator');
  261. foreach ($results as $key => $ticket) {
  262. $ticket[0]['status']['code'] = $translatorService->trans($ticket[0]['status']['code']);
  264. $data[] = [
  265. 'id' => $ticket[0]['id'],
  266. 'subject' => $ticket[0]['subject'],
  267. 'isCustomerView' => $ticket[0]['isCustomerViewed'],
  268. 'status' => $ticket[0]['status'],
  269. 'group' => $ticket[0]['supportGroup'],
  270. 'type' => $ticket[0]['type'],
  271. 'priority' => $ticket[0]['priority'],
  272. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  273. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  274. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  275. 'formatedCreatedAt' => $userService->getLocalizedFormattedTime($ticket[0]['createdAt'],$userService->getSessionUser()),
  276. ];
  277. }
  279. $json['tickets'] = $data;
  281. $json['pagination'] = $paginationData;
  283. return $json;
  284. }
  286. public function addPermissionFilter($qb, User $user, array $supportGroupReferences = [], array $supportTeamReferences = [])
  287. {
  288. $userInstance = $user->getAgentInstance();
  290. if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $userInstance->getTicketAccesslevel() != self::TICKET_GLOBAL_ACCESS)) {
  291. $qualifiedGroups = empty($this->params['group']) ? $supportGroupReferences : array_intersect($supportGroupReferences, explode(',', $this->params['group']));
  292. $qualifiedTeams = empty($this->params['team']) ? $supportTeamReferences : array_intersect($supportTeamReferences, explode(',', $this->params['team']));
  294. switch ($userInstance->getTicketAccesslevel()) {
  295. case self::TICKET_GROUP_ACCESS:
  296. $qb
  297. ->andWhere("ticket.agent = :agentId OR supportGroup.id IN(:supportGroupIds) OR supportTeam.id IN(:supportTeamIds)")
  298. ->setParameter('agentId', $user->getId())
  299. ->setParameter('supportGroupIds', $qualifiedGroups)
  300. ->setParameter('supportTeamIds', $qualifiedTeams);
  301. break;
  302. case self::TICKET_TEAM_ACCESS:
  303. $qb
  304. ->andWhere("ticket.agent = :agentId OR supportTeam.id IN(:supportTeamIds)")
  305. ->setParameter('agentId', $user->getId())
  306. ->setParameter('supportTeamIds', $qualifiedTeams);
  307. break;
  308. default:
  309. $qb
  310. ->andWhere("ticket.agent = :agentId")
  311. ->setParameter('agentId', $user->getId());
  312. break;
  313. }
  314. }
  316. return $qb;
  317. }
  319. public function prepareBaseTicketQuery(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  320. {
  321. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  322. ->select("
  323. DISTINCT ticket,
  324. supportGroup.name as groupName,
  325. supportTeam.name as teamName,
  326. priority,
  327. type.code as typeName,
  328. agent.id as agentId,
  329. agent.email as agentEmail,
  330. agentInstance.profileImagePath as smallThumbnail,
  331. customer.id as customerId,
  332. customer.email as customerEmail,
  333. customerInstance.profileImagePath as customersmallThumbnail,
  334. CONCAT(customer.firstName, ' ', customer.lastName) AS customerName,
  335. CONCAT(agent.firstName,' ', agent.lastName) AS agentName
  336. ")
  337. ->from(Ticket::class, 'ticket')
  338. ->leftJoin('ticket.type', 'type')
  339. ->leftJoin('ticket.agent', 'agent')
  340. ->leftJoin('ticket.threads', 'threads')
  341. ->leftJoin('ticket.priority', 'priority')
  342. ->leftJoin('ticket.customer', 'customer')
  343. ->leftJoin('ticket.supportTeam', 'supportTeam')
  344. ->leftJoin('ticket.supportTags', 'supportTags')
  345. ->leftJoin('agent.userInstance', 'agentInstance')
  346. ->leftJoin('ticket.supportLabels', 'supportLabel')
  347. ->leftJoin('ticket.supportGroup', 'supportGroup')
  348. ->leftJoin('customer.userInstance', 'customerInstance')
  349. ->where('customerInstance.supportRole = 4')
  350. ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  351. ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false);
  353. if (!isset($params['sort'])) {
  354. $queryBuilder->orderBy('ticket.updatedAt', Criteria::DESC);
  355. }
  357. if ($filterByStatus) {
  358. $queryBuilder->andWhere('ticket.status = :status')->setParameter('status', isset($params['status']) ? $params['status'] : 1);
  359. }
  361. $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  363. // applyFilter according to params
  364. return $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  365. }
  367. public function prepareBasePaginationTicketTypesQuery(array $params)
  368. {
  369. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  370. ->select("ticketType")
  371. ->from(TicketType::class, 'ticketType');
  373. // Apply filters
  374. foreach ($params as $field => $fieldValue) {
  375. if (in_array($field, $this->safeFields)) {
  376. continue;
  377. }
  379. switch ($field) {
  380. case 'search':
  381. $queryBuilder->andWhere("ticketType.code LIKE :searchQuery OR ticketType.description LIKE :searchQuery");
  382. $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  383. break;
  384. case 'isActive':
  385. $queryBuilder->andWhere("ticketType.isActive LIKE :searchQuery");
  386. $queryBuilder->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  387. break;
  388. default:
  389. break;
  390. }
  391. }
  393. // Define sort by
  394. if (empty($params['sort']) || 'a.id' == $params['sort']) {
  395. $queryBuilder->orderBy('ticketType.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  396. } else {
  397. $queryBuilder->orderBy('ticketType.code', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  398. }
  400. return $queryBuilder;
  401. }
  403. public function prepareBasePaginationTagsQuery(array $params)
  404. {
  405. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  406. ->select('supportTag.id as id, supportTag.name as name, COUNT(ticket) as totalTickets')
  407. ->from(Tag::class, 'supportTag')
  408. ->leftJoin('supportTag.tickets', 'ticket')
  409. ->groupBy('supportTag.id');
  411. // Apply filters
  412. foreach ($params as $field => $fieldValue) {
  413. if (in_array($field, $this->safeFields)) {
  414. continue;
  415. }
  417. switch ($field) {
  418. case 'search':
  419. $queryBuilder->andWhere("supportTag.name LIKE :searchQuery")->setParameter('searchQuery', '%' . urldecode(trim($fieldValue)) . '%');
  420. break;
  421. default:
  422. break;
  423. }
  424. }
  426. // Define sort by
  427. if (empty($params['sort']) || 'a.id' == $params['sort']) {
  428. $queryBuilder->orderBy('supportTag.id', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  429. } else {
  430. $queryBuilder->orderBy('supportTag.name', (empty($params['direction']) || 'ASC' == strtoupper($params['direction'])) ? Criteria::ASC : Criteria::DESC);
  431. }
  433. return $queryBuilder;
  434. }
  436. public function getTicketTabDetails(User $user, array $supportGroupIds = [], array $supportTeamIds = [], array $params = [], bool $filterByStatus = true)
  437. {
  438. $data = array(1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0);
  440. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  441. ->select("
  442. COUNT(DISTINCT ticket.id) as countTicket,
  443. status.id as statusId,
  444. status.code as tab
  445. ")
  446. ->from(Ticket::class, 'ticket')
  447. ->leftJoin('ticket.type', 'type')
  448. ->leftJoin('ticket.agent', 'agent')
  449. ->leftJoin('ticket.status', 'status')
  450. ->leftJoin('ticket.threads', 'threads')
  451. ->leftJoin('ticket.priority', 'priority')
  452. ->leftJoin('ticket.customer', 'customer')
  453. ->leftJoin('ticket.supportTeam', 'supportTeam')
  454. ->leftJoin('ticket.supportTags', 'supportTags')
  455. ->leftJoin('ticket.supportGroup', 'supportGroup')
  456. ->leftJoin('agent.userInstance', 'agentInstance')
  457. ->leftJoin('ticket.supportLabels', 'supportLabel')
  458. ->leftJoin('customer.userInstance', 'customerInstance')
  459. ->where('customerInstance.supportRole = 4')
  460. ->andWhere("agent.id IS NULL OR agentInstance.supportRole != 4")
  461. ->andWhere('ticket.isTrashed = :isTrashed')->setParameter('isTrashed', isset($params['trashed']) ? true : false)
  462. ->groupBy('status');
  464. // applyFilter according to permission
  465. $this->addPermissionFilter($queryBuilder, $user, $supportGroupIds, $supportTeamIds);
  467. $queryBuilder = $this->prepareTicketListQueryWithParams($queryBuilder, $params, $user);
  468. $results = $queryBuilder->getQuery()->getResult();
  470. foreach ($results as $status) {
  471. $data[$status['statusId']] += $status['countTicket'];
  472. }
  474. return $data;
  475. }
  477. public function countTicketTotalThreads($ticketId, $threadType = 'reply')
  478. {
  479. $totalThreads = $this->getEntityManager()->createQueryBuilder()
  480. ->select('COUNT(thread.id) as threads')
  481. ->from(Ticket::class, 'ticket')
  482. ->leftJoin('ticket.threads', 'thread')
  483. ->where('ticket.id = :ticketId')->setParameter('ticketId', $ticketId)
  484. ->andWhere('thread.threadType = :threadType')->setParameter('threadType', $threadType)
  485. ->getQuery()->getSingleScalarResult();
  487. return (int) $totalThreads;
  488. }
  490. public function getTicketNavigationIteration($ticket, $container)
  491. {
  492. $ticketsCollection = $this->getEntityManager()->getRepository(Ticket::class)
  493. ->getAllTickets(null, $container);
  495. if ($ticketsCollection)
  496. $results = $ticketsCollection['tickets'];
  498. $nextPrevPage = array('next' => 0,'prev' => 0);
  499. for ($i = 0; $i < count($results); $i++) {
  500. if ($results[$i]['id'] == $ticket->getId()) {
  501. $nextPrevPage['next'] = isset($results[$i + 1]) ? $results[$i + 1]['id'] : 0;
  502. $nextPrevPage['prev'] = isset($results[$i - 1]) ? $results[$i - 1]['id'] : 0;
  503. }
  504. }
  506. return $nextPrevPage;
  507. }
  509. public function countCustomerTotalTickets(User $user, $container)
  510. {
  511. $userService = $container->get('user.service');
  513. $queryBuilder = $this->getEntityManager()->createQueryBuilder()
  514. ->select('COUNT(ticket.id) as tickets')
  515. ->from(Ticket::class, 'ticket')
  516. ->leftJoin('ticket.priority', 'p')
  517. ->leftJoin('ticket.status', 's')
  518. ->leftJoin('ticket.agent', 'a')
  519. ->leftJoin('ticket.type', 'type')
  520. ->leftJoin('ticket.supportGroup', 'supportGroup')
  521. ->leftJoin('ticket.supportTeam', 'supportTeam')
  522. ->leftJoin('a.userInstance', 'ad')
  523. ->andWhere('ticket.customer = :customerId')
  524. ->andWhere('ticket.isTrashed != 1')
  525. ->setParameter('customerId', $user->getId())
  526. ->andWhere("a IS NULL OR ad.supportRole != 4")
  527. ->orderBy('ticket.id', Criteria::DESC);
  529. $agent = $userService->getCurrentUser();
  530. $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($agent);
  531. $supportTeamReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($agent);
  533. $this->addPermissionFilter($queryBuilder, $agent, $supportGroupReference, $supportTeamReference);
  535. return (int) $queryBuilder->getQuery()->getSingleScalarResult();
  536. }
  538. public function isLabelAlreadyAdded($ticket,$label)
  539. {
  540. $qb = $this->getEntityManager()->createQueryBuilder();
  541. $qb->select('COUNT(t.id) as ticketCount')->from(Ticket::class, 't')
  542. ->leftJoin('t.supportLabels','tl')
  543. ->andWhere('tl.id = :labelId')
  544. ->andWhere('t.id = :ticketId')
  545. ->setParameter('labelId',$label->getId())
  546. ->setParameter('ticketId',$ticket->getId());
  548. return $qb->getQuery()->getSingleScalarResult() ? true : false;
  549. }
  551. public function isTicketCollaborator($ticket, $collaboratorEmail)
  552. {
  553. if ($ticket->getCollaborators()) {
  554. foreach ($ticket->getCollaborators() as $collaborator) {
  555. if (strtolower($collaborator->getEmail()) == strtolower($collaboratorEmail)) {
  556. return true;
  557. }
  558. }
  559. }
  561. return false;
  562. }
  564. public function getTicketDetails(ParameterBag $obj = null, $container)
  565. {
  566. $data = $obj->all();
  567. $userService = $container->get('user.service');
  568. $ticketService = $container->get('ticket.service');
  569. $json = [];
  570. $qb = $this->getEntityManager()->createQueryBuilder();
  571. $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')
  572. ->leftJoin('t.agent', 'a')
  573. ->leftJoin('t.status', 's')
  574. ->leftJoin('t.customer', 'c')
  575. ->leftJoin('t.supportGroup', 'gr')
  576. ->leftJoin('t.supportTeam', 'supportTeam')
  577. ->leftJoin('t.priority', 'pr')
  578. ->leftJoin('t.type', 'tp')
  579. ->leftJoin('c.userInstance', 'cd')
  580. ->leftJoin('a.userInstance', 'ad')
  581. ->leftJoin('t.supportTags', 'tg')
  582. ->leftJoin('t.supportLabels', 'tl')
  583. ->andWhere('t.id = :ticketId')
  584. ->setParameter('ticketId', $data['ticketId']);
  586. $results = $qb->getQuery()->getArrayResult();
  587. $ticket = array_shift($results);
  589. return [
  590. 'id' => $ticket[0]['id'],
  591. 'subject' => $ticket[0]['subject'],
  592. 'isStarred' => $ticket[0]['isStarred'],
  593. 'isAgentView' => $ticket[0]['isAgentViewed'],
  594. 'isTrashed' => $ticket[0]['isTrashed'],
  595. 'status' => $ticket[0]['status'],
  596. 'groupName' => $ticket['groupName'],
  597. 'subGroupName' => $ticket['supportTeamName'],
  598. 'typeName' => $ticket['typeName'],
  599. 'priority' => $ticket[0]['priority'],
  600. 'formatedCreatedAt' => $ticketService->timeZoneConverter($ticket[0]['createdAt']),
  601. 'ticketLabels' => $ticketService->getTicketLabels($ticket[0]['id']),
  602. 'totalThreads' => $ticketService->getTicketTotalThreads($ticket[0]['id']),
  603. 'agent' => $ticket['agentId'] ? $userService->getAgentDetailById($ticket['agentId']) : null,
  604. 'customer' => $ticket['customerId'] ? $userService->getCustomerPartialDetailById($ticket['customerId']) : null,
  605. 'lastReplyAgentName' => $ticketService->getlastReplyAgentName($ticket[0]['id']),
  606. 'createThread' => $ticketService->getCreateReply($ticket[0]['id']),
  607. 'lastReply' => $ticketService->getLastReply($ticket[0]['id']),
  608. ];
  609. }
  611. // Get customer more ticket sidebar details
  612. public function getCustomerMoreTicketsSidebar($customerId, $container, $request) {
  613. $userService = $container->get('user.service');
  614. $ticketService = $container->get('ticket.service');
  616. $qb = $this->getEntityManager()->createQueryBuilder();
  617. $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")
  618. ->from($this->getEntityName(), 'ticket')
  619. ->leftJoin('ticket.priority', 'p')
  620. ->leftJoin('ticket.status', 's')
  621. ->leftJoin('ticket.agent', 'a')
  622. ->leftJoin('ticket.type', 'type')
  623. ->leftJoin('ticket.supportGroup', 'supportGroup')
  624. ->leftJoin('ticket.supportTeam', 'supportTeam')
  625. ->leftJoin('a.userInstance', 'ad')
  626. ->andWhere('ticket.customer = :customerId')
  627. ->andWhere('ticket.isTrashed != 1')
  628. ->setParameter('customerId', $customerId)
  629. ->andWhere("a IS NULL OR ad.supportRole != 4")
  630. ->orderBy('ticket.id', Criteria::DESC);
  632. $user = $userService->getCurrentUser();
  633. $supportGroupReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportGroupReferences($user);
  634. $supportTeamReference = $this->getEntityManager()->getRepository(User::class)->getUserSupportTeamReferences($user);
  636. // if($currentUser->getRole() == "ROLE_AGENT" && $currentUser->detail['agent']->getTicketView() != UserData::GLOBAL_ACCESS) {
  637. // $this->em->getRepository('WebkulTicketBundle:Ticket')->addPermissionFilter($qb, $this->container, false);
  638. // $qb->addSelect('gr.name as groupName');
  639. // } else {
  640. // $qb->leftJoin('t.supportGroup', 'gr');
  641. // $qb->addSelect('gr.name as groupName');
  642. // }
  644. $this->addPermissionFilter($qb, $user, $supportGroupReference, $supportTeamReference);
  646. $results = $qb->getQuery()->getArrayResult();
  647. foreach ($results as $key => $ticket) {
  648. $results[$key] = $ticket['tickets'];
  649. unset($ticket['tickets']);
  650. $results[$key] = array_merge($results[$key], $ticket);
  651. $results[$key]['timestamp']= $userService->convertToTimezone($results[$key]['createdAt']);
  652. $results[$key]['formatedCreatedAt'] = $results[$key]['createdAt']->format('d-m-Y H:i A');
  653. $results[$key]['totalThreads']= $ticketService->getTicketTotalThreads($results[$key]['id']);
  655. }
  657. return $results;
  658. }
  660. public function prepareTicketListQueryWithParams($queryBuilder, $params, $actAsUser = null)
  661. {
  662. if (!empty($params['search']) && !empty($params['searchType'])) {
  663. $sanitizedSearchContent = trim(urldecode($params['search']));
  664. $sanitizedSearchTicketIncrementId = str_replace('#', '', $sanitizedSearchContent);
  666. // Initialize search query
  667. switch ($params['searchType']) {
  668. case 'email':
  669. $whereTicketSearchQuery = "customer.email LIKE :search";
  670. break;
  672. case 'subject':
  673. $whereTicketSearchQuery = "ticket.subject LIKE :search";
  674. break;
  676. case 'id':
  677. $whereTicketSearchQuery = "ticket.id = :searchExact";
  678. break;
  680. default:
  681. break;
  682. }
  684. // Apply condition
  685. $queryBuilder->andWhere($whereTicketSearchQuery);
  687. // Set parameters based on search type
  688. if ($params['searchType'] === 'id') {
  689. $queryBuilder->setParameter('searchExact', $sanitizedSearchTicketIncrementId);
  690. } else {
  691. $queryBuilder->setParameter('search', '%' . $sanitizedSearchContent . '%');
  692. }
  693. }
  695. foreach ($params as $field => $fieldValue) {
  696. if (in_array($field, $this->safeFields)) {
  697. continue;
  698. }
  700. if ($actAsUser != null ) {
  701. $userInstance = $actAsUser->getAgentInstance();
  702. if (!empty($userInstance) && ('ROLE_AGENT' == $userInstance->getSupportRole()->getCode() && $field == 'mine') || ('ROLE_ADMIN' == $userInstance->getSupportRole()->getCode()) && $field == 'mine') {
  703. $fieldValue = $actAsUser->getId();
  704. }
  705. }
  707. switch ($field) {
  708. case 'label':
  709. $queryBuilder->andWhere('supportLabel.id = :labelIds');
  710. $queryBuilder->setParameter('labelIds', $fieldValue);
  711. break;
  712. case 'starred':
  713. $queryBuilder->andWhere('ticket.isStarred = 1');
  714. break;
  715. case 'unassigned':
  716. $queryBuilder->andWhere("agent.id is NULL");
  717. break;
  718. case 'notreplied':
  719. $queryBuilder->andWhere('ticket.isReplied = 0');
  720. break;
  721. case 'mine':
  722. $queryBuilder->andWhere('agent = :agentId')->setParameter('agentId', $fieldValue);
  723. break;
  724. case 'new':
  725. $queryBuilder->andWhere('ticket.isNew = 1');
  726. break;
  727. case 'priority':
  728. $queryBuilder->andWhere('priority.id = :priority')->setParameter('priority', $fieldValue);
  729. break;
  730. case 'type':
  731. $queryBuilder->andWhere('type.id IN (:typeCollection)')->setParameter('typeCollection', explode(',', $fieldValue));
  732. break;
  733. case 'agent':
  734. $queryBuilder->andWhere('agent.id IN (:agentCollection)')->setParameter('agentCollection', explode(',', $fieldValue));
  735. break;
  736. case 'customer':
  737. $queryBuilder->andWhere('customer.id IN (:customerCollection)')->setParameter('customerCollection', explode(',', $fieldValue));
  738. break;
  739. case 'group':
  740. $queryBuilder->andWhere('supportGroup.id IN (:groupIds)');
  741. $queryBuilder->setParameter('groupIds', explode(',', $fieldValue));
  742. break;
  743. case 'team':
  744. $queryBuilder->andWhere("supportTeam.id In(:subGrpKeys)");
  745. $queryBuilder->setParameter('subGrpKeys', explode(',', $fieldValue));
  746. break;
  747. case 'tag':
  748. $queryBuilder->andWhere("supportTags.id In(:tagIds)");
  749. $queryBuilder->setParameter('tagIds', explode(',', $fieldValue));
  750. break;
  751. case 'source':
  752. $queryBuilder->andWhere('ticket.source IN (:sources)');
  753. $queryBuilder->setParameter('sources', explode(',', $fieldValue));
  754. break;
  755. case 'after':
  756. $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 23:59');
  757. if ($date) {
  758. // $date = \DateTime::createFromFormat('d-m-Y H:i', $this->userService->convertTimezoneToServer($date, 'd-m-Y H:i'));
  759. $queryBuilder->andWhere('ticket.createdAt > :afterDate');
  760. $queryBuilder->setParameter('afterDate', $date);
  761. }
  762. break;
  763. case 'before':
  764. $date = \DateTime::createFromFormat('d-m-Y H:i', $fieldValue.' 00:00');
  765. if ($date) {
  766. //$date = \DateTime::createFromFormat('d-m-Y H:i', $container->get('user.service')->convertTimezoneToServer($date, 'd-m-Y H:i'));
  767. $queryBuilder->andWhere('ticket.createdAt < :beforeDate');
  768. $queryBuilder->setParameter('beforeDate', $date);
  769. }
  770. break;
  771. case 'repliesLess':
  772. $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  773. ->groupBy('ticket.id')
  774. ->andHaving('count(threads.id) < :threadValueLesser')->setParameter('threadValueLesser', intval($params['repliesLess']));
  775. break;
  776. case 'repliesMore':
  777. $queryBuilder->andWhere('threads.threadType = :threadType')->setParameter('threadType', 'reply')
  778. ->groupBy('ticket.id')
  779. ->andHaving('count(threads.id) > :threadValueGreater')->setParameter('threadValueGreater', intval($params['repliesMore']));
  780. break;
  781. case 'mailbox':
  782. $queryBuilder->andWhere('ticket.mailboxEmail IN (:mailboxEmails)');
  783. $queryBuilder->setParameter('mailboxEmails', explode(',', $fieldValue));
  784. break;
  785. default:
  786. break;
  787. }
  788. }
  790. return $queryBuilder;
  791. }
  793. public function getAgentTickets($agentId,$container) {
  794. $qb = $this->getEntityManager()->createQueryBuilder();
  795. $qb->select('t')->from(Ticket::class, 't');
  797. $qb->andWhere('t.agent = :agentId');
  798. $qb->setParameter('agentId',$agentId);
  800. return $qb->getQuery()->getResult();
  801. }
  802. }