src/webkul/uvdesk/core-framework/Repository/TicketRepository.php line 46

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