<?php
namespace Webkul\UVDesk\CoreFrameworkBundle\Controller;
use Webkul\UVDesk\CoreFrameworkBundle\Entity;
use Webkul\UVDesk\CoreFrameworkBundle\Form;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\User;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Doctrine\ORM\EntityManagerInterface;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportGroup;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\SupportTeam;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\UserInstance;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Webkul\UVDesk\CoreFrameworkBundle\Services\UserService;
use Webkul\UVDesk\CoreFrameworkBundle\Services\UVDeskService;
use Webkul\UVDesk\CoreFrameworkBundle\Services\ReportService;
use Symfony\Contracts\Translation\TranslatorInterface;
use Knp\Component\Pager\PaginatorInterface;
use Doctrine\ORM\Query;
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\TicketRating;
use Webkul\UVDesk\CoreFrameworkBundle\Entity\Ticket;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;
class Dash extends AbstractController
{
private $userService;
private $reportService;
private $uvdeskService;
private $paginator;
private $translator;
private $entityManager;
public function __construct(UserService $userService, UVDeskService $uvdeskService,ReportService $reportService, PaginatorInterface $paginator, TranslatorInterface $translator, EntityManagerInterface $entityManager)
{
$this->userService = $userService;
$this->reportService = $reportService;
$this->uvdeskService = $uvdeskService;
$this->paginator = $paginator;
$this->translator = $translator;
$this->entityManager = $entityManager;
}
public function listDashActivity(Request $request)
{
if (!$this->userService->isAccessAuthorized('ROLE_AGENT_MANAGE_AGENT_ACTIVITY')){
return $this->redirect($this->generateUrl('helpdesk_member_dashboard'));
}
// Determine date param (single date, format YYYY-MM-DD)
$dateParam = $request->query->get('date') ?: (new \DateTimeImmutable('today'))->format('Y-m-d');
// Prepare initial activity payload for the view (agentDashData expects Request)
$clonedRequest = $request->duplicate(null, null, ['_route_params' => []]);
$clonedRequest->query->set('date', $dateParam);
try {
$agentActivity = $this->agentDashData($clonedRequest);
} catch (\Exception $e) {
$agentActivity = ['data' => [], 'dueDatesByDate' => [], 'dueTickets' => []];
}
// Compute bucket counts server-side so template can render initial numbers immediately
$dueCounts = [
'today' => 0,
'tomorrow' => 0,
'in7' => 0,
'od7' => 0,
'odgt7' => 0,
];
$baseDate = new \DateTimeImmutable($dateParam);
// Prefer computing counts from dueTickets if available
$dueTicketRows = $agentActivity['dueTickets'] ?? [];
if (!empty($dueTicketRows) && is_array($dueTicketRows)) {
foreach ($dueTicketRows as $r) {
if (empty($r['dueDate'])) continue;
try {
$d = new \DateTimeImmutable($r['dueDate']);
} catch (\Exception $e) { continue; }
$diff = (int) $d->diff($baseDate)->format('%r%a');
if ($diff === 0) $dueCounts['today']++;
elseif ($diff === 1) $dueCounts['tomorrow']++;
elseif ($diff > 1 && $diff <= 7) $dueCounts['in7']++;
elseif ($diff < 0 && $diff >= -7) $dueCounts['od7']++;
elseif ($diff < -7) $dueCounts['odgt7']++;
}
} else {
// Fallback: compute from dueDatesByDate
if (!empty($agentActivity['dueDatesByDate']) && is_array($agentActivity['dueDatesByDate'])) {
foreach ($agentActivity['dueDatesByDate'] as $dueDateKey => $ticketIds) {
try { $d = new \DateTimeImmutable($dueDateKey); } catch (\Exception $e) { continue; }
$diff = (int) $d->diff($baseDate)->format('%r%a');
$count = is_array($ticketIds) ? count($ticketIds) : 0;
if ($diff === 0) $dueCounts['today'] += $count;
elseif ($diff === 1) $dueCounts['tomorrow'] += $count;
elseif ($diff > 1 && $diff <= 7) $dueCounts['in7'] += $count;
elseif ($diff < 0 && $diff >= -7) $dueCounts['od7'] += $count;
elseif ($diff < -7) $dueCounts['odgt7'] += $count;
}
}
}
return $this->render('@UVDeskCoreFramework/Reports/listDash.html.twig', [
'agents' => $this->userService->getAgentsPartialDetails(),
'agentActivity' => $agentActivity,
'date' => $dateParam,
'dueCounts' => $dueCounts,
]);
}
public function agentDashData(Request $request)
{
// Check access permissions
if (!$this->userService->isAccessAuthorized('ROLE_AGENT_MANAGE_AGENT_ACTIVITY')) {
throw new \Exception('Access Denied', 403);
}
// Prepare sensible empty return structure
$result = [
'data' => [],
'dueDatesByDate' => [],
'dueTickets' => [],
];
try {
$connection = $this->entityManager->getConnection(); // ✅ Injected EntityManager
// Query tickets with their status
$query = "
SELECT
t.id AS ticket_id,
t.due_date,
s.code AS status_code,
s.description AS status_description
FROM uv_ticket t
JOIN uv_ticket_status s ON t.status_id = s.id
";
$tickets = $connection->fetchAllAssociative($query);
} catch (\Throwable $e) {
// If DB isn't accessible (tests, cache warmup, etc.) return empty structures
return $result;
}
// Build normalized structures expected by listDashActivity and templates
$rows = [];
$dueTickets = [];
$dueDatesByDate = [];
foreach ($tickets as $ticket) {
if (empty($ticket['due_date'])) {
continue;
}
try {
// Use fully-qualified DateTimeImmutable
$dt = \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $ticket['due_date']) ?: new \DateTimeImmutable($ticket['due_date']);
} catch (\Exception $e) {
// skip unparseable dates
continue;
}
$dueDateNormalized = $dt->format('Y-m-d');
$row = [
'ticketId' => $ticket['ticket_id'],
'dueDate' => $dueDateNormalized,
'statusCode' => $ticket['status_code'] ?? null,
'statusDescription' => $ticket['status_description'] ?? null,
];
$rows[] = $row;
$dueTickets[] = $row;
$dueDatesByDate[$dueDateNormalized][] = $ticket['ticket_id'];
}
$result['data'] = $rows;
$result['dueDatesByDate'] = $dueDatesByDate;
$result['dueTickets'] = $dueTickets;
return $result;
}
/**
* Returns To-Do items for tabs: today | upcoming | completed.
* - today: due_date = today
* - upcoming: due_date in next 15 days (excluding today)
* - completed: due_date within last 7 days up to today AND status = closed
*
* @Route("/agent/dashboard/todo", name="helpdesk_member_dash_todo", methods={"GET"})
*/
public function todoData(Request $request): JsonResponse
{
if (!$this->userService->isAccessAuthorized('ROLE_AGENT_MANAGE_AGENT_ACTIVITY')) {
return new JsonResponse(['error' => 'Access denied'], 403);
}
$tab = $request->query->get('tab', 'today'); // today | upcoming | completed
// Use your server TZ (or set a specific one like 'Asia/Kolkata')
$tz = new \DateTimeZone(date_default_timezone_get());
$today = (new \DateTimeImmutable('today', $tz));
// Dates we’ll need
$fromUpcoming = $today->modify('+1 day')->format('Y-m-d');
$toUpcoming = $today->modify('+15 days')->format('Y-m-d');
$fromCompleted= $today->modify('-7 days')->format('Y-m-d');
$todayStr = $today->format('Y-m-d');
// Base SQL (your join), + filter by tab
// NOTE: We DON’T hardcode status_id=5; instead we treat any status whose code is 'closed' (case-insensitive) as completed.
$sql = "
SELECT
t.id AS ticket_id,
t.subject,
DATE(t.due_date) AS due_date,
t.agent_id,
t.status_id,
u.first_name AS agent_first_name,
u.last_name AS agent_last_name,
s.code AS status_code
FROM uv_ticket t
LEFT JOIN uv_user u ON t.agent_id = u.id
LEFT JOIN uv_ticket_status s ON t.status_id = s.id
WHERE t.due_date IS NOT NULL
";
$params = [];
switch ($tab) {
case 'today':
$sql .= " AND DATE(t.due_date) = :today";
$params['today'] = $todayStr;
break;
case 'upcoming':
$sql .= " AND DATE(t.due_date) BETWEEN :from AND :to";
$params['from'] = $fromUpcoming;
$params['to'] = $toUpcoming;
break;
case 'completed':
// last 7 days up to today, and closed status
// if you must use a numeric id, swap the status filter line for: AND t.status_id = :closedId
$sql .= " AND DATE(t.due_date) BETWEEN :from AND :to
AND LOWER(s.code) = 'closed'";
$params['from'] = $fromCompleted;
$params['to'] = $todayStr;
break;
default:
// default to today
$sql .= " AND DATE(t.due_date) = :today";
$params['today'] = $todayStr;
break;
}
$sql .= " ORDER BY t.due_date ASC, t.id ASC";
$conn = $this->entityManager->getConnection();
$rows = $conn->fetchAllAssociative($sql, $params);
// Normalize response for UI
$items = array_map(function ($r) {
$agent = trim(($r['agent_first_name'] ?? '') . ' ' . ($r['agent_last_name'] ?? ''));
return [
'ticket_id' => (int) $r['ticket_id'],
'subject' => $r['subject'] ?? '',
'due_date' => $r['due_date'] ?? null, // Y-m-d
'agent_name' => $agent !== '' ? $agent : null,
'status_code' => $r['status_code'] ?? null,
'status_id' => isset($r['status_id']) ? (int)$r['status_id'] : null,
];
}, $rows);
return new JsonResponse(['items' => $items]);
}
/**
* @Route("/agent/dashboard/tasks", name="helpdesk_member_dash_tasks", methods={"GET"})
*/
public function tasksData(Request $request): JsonResponse
{
if (!$this->userService->isAccessAuthorized('ROLE_AGENT_MANAGE_AGENT_ACTIVITY')) {
return new JsonResponse(['error' => 'Access denied'], 403);
}
$tab = $request->query->get('tab', 'pending'); // pending | hold | inprogress | completed
// Map UI tabs to status_id
$map = [
'pending' => 2, // Pending
'hold' => 4, // Hold / Resolved (as per your mapping)
'inprogress' => 1, // Open
'completed' => 5, // Closed
];
$statusId = $map[$tab] ?? 2;
$conn = $this->entityManager->getConnection();
$sql = "
SELECT
t.id AS ticket_id,
t.subject,
DATE(t.due_date) AS due_date,
t.agent_id,
t.status_id,
u.first_name AS agent_first_name,
u.last_name AS agent_last_name,
s.code AS status_code
FROM uv_ticket t
LEFT JOIN uv_user u ON t.agent_id = u.id
LEFT JOIN uv_ticket_status s ON t.status_id = s.id
WHERE t.status_id = :sid
ORDER BY t.due_date IS NULL, t.due_date ASC, t.id ASC
";
$rows = $conn->fetchAllAssociative($sql, ['sid' => $statusId]);
$items = array_map(function ($r) {
$agent = trim(($r['agent_first_name'] ?? '') . ' ' . ($r['agent_last_name'] ?? ''));
return [
'ticket_id' => (int) $r['ticket_id'],
'subject' => $r['subject'] ?? '',
'due_date' => $r['due_date'] ?? null, // Y-m-d or null
'agent_name' => $agent !== '' ? $agent : null,
'status_code' => $r['status_code'] ?? null,
'status_id' => isset($r['status_id']) ? (int)$r['status_id'] : null,
];
}, $rows);
return new JsonResponse(['items' => $items]);
}
}