Symfony Profiler

<?php
namespace App\Controller;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;
class WorkingHoursController extends AbstractController
{
public function workingHoursAction(): Response
{
return $this->render('reports/working-hours.html.twig');
}
public function getWorkingHoursStatusAction(): JsonResponse
{
$timezones = [
'Argentina' => 'America/Argentina/Buenos_Aires',
'Colombia' => 'America/Bogota',
'Mexico' => 'America/Mexico_City',
'Perú' => 'America/Lima',
'España' => 'Europe/Madrid'
];
$status = [];
$now = new \DateTime('now', new \DateTimeZone('UTC'));
// Obtener agentes agrupados por región
$entityManager = $this->getDoctrine()->getManager();
$agentsQuery = $entityManager->createQuery("
SELECT
a.id as agentId,
a.firstName,
a.lastName,
a.email,
a.isEnabled,
a.lastactivity,
a.idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM UVDeskCoreFrameworkBundle:User a
JOIN UVDeskCoreFrameworkBundle:UserInstance ui WITH a.id = ui.user
JOIN UVDeskCoreFrameworkBundle:SupportRole sr WITH ui.supportRole = sr.id
LEFT JOIN UVDeskCoreFrameworkBundle:SupportGroup sg WITH a.idor = sg.id
WHERE a.isEnabled = 1
AND sr.code = 'ROLE_AGENT'
ORDER BY a.firstName, a.lastName
");
$agents = $agentsQuery->getResult();
// Agrupar agentes por región
$agentsByRegion = [];
foreach ($agents as $agent) {
$regionName = $agent['supportGroupName'] ?: 'Sin región asignada';
if (!isset($agentsByRegion[$regionName])) {
$agentsByRegion[$regionName] = [];
}
$agentsByRegion[$regionName][] = [
'id' => $agent['agentId'],
'name' => $agent['firstName'] . ' ' . $agent['lastName'],
'email' => $agent['email'],
'isActive' => $agent['isEnabled'],
'lastActivity' => $agent['lastactivity'] ? $agent['lastactivity']->format('Y-m-d H:i:s') : 'N/A',
'idor' => $agent['idor'],
'role' => $agent['supportRoleCode']
];
}
foreach ($timezones as $country => $timezone) {
$localTime = new \DateTime('now', new \DateTimeZone($timezone));
$dayOfWeek = (int)$localTime->format('N'); // 1 = Monday, 7 = Sunday
$hour = (int)$localTime->format('G'); // 0-23 hour format
// Check if it's a working day (Monday to Friday)
$isWorkingDay = $dayOfWeek >= 1 && $dayOfWeek <= 5;
// Check if it's within working hours (9 AM to 6 PM)
$isWorkingHours = $hour >= 9 && $hour < 18;
// Determine status
if ($isWorkingDay && $isWorkingHours) {
$statusCode = 'open';
$statusText = 'Abierto';
$statusColor = 'green';
} else {
$statusCode = 'closed';
$statusText = 'Cerrado';
$statusColor = 'red';
}
// Buscar agentes para este país basándose en el nombre del país
$countryAgents = [];
foreach ($agentsByRegion as $regionName => $regionAgents) {
if (stripos($regionName, $country) !== false ||
stripos($country, $regionName) !== false ||
$this->isCountryInRegion($country, $regionName)) {
$countryAgents = array_merge($countryAgents, $regionAgents);
}
}
$status[$country] = [
'country' => $country,
'timezone' => $timezone,
'localTime' => $localTime->format('H:i:s'),
'localDate' => $localTime->format('Y-m-d'),
'dayOfWeek' => $localTime->format('l'),
'isWorkingDay' => $isWorkingDay,
'isWorkingHours' => $isWorkingHours,
'status' => $statusCode,
'statusText' => $statusText,
'statusColor' => $statusColor,
'workingHours' => '9:00 AM - 6:00 PM',
'workingDays' => 'Lunes a Viernes',
'agents' => $countryAgents
];
}
return new JsonResponse($status);
}
private function isCountryInRegion($country, $regionName): bool
{
// Mapeo de países a posibles nombres de regiones
$countryRegionMap = [
'Argentina' => ['argentina', 'argentine', 'buenos aires', 'córdoba', 'rosario'],
'Colombia' => ['colombia', 'bogotá', 'medellín', 'cali', 'barranquilla'],
'Mexico' => ['mexico', 'méxico', 'ciudad de mexico', 'cdmx', 'guadalajara', 'monterrey'],
'Perú' => ['peru', 'perú', 'lima', 'arequipa', 'trujillo'],
'España' => ['españa', 'spain', 'madrid', 'barcelona', 'valencia', 'sevilla']
];
if (isset($countryRegionMap[$country])) {
foreach ($countryRegionMap[$country] as $regionKeyword) {
if (stripos($regionName, $regionKeyword) !== false) {
return true;
}
}
}
return false;
}
public function getRecentTicketsAction(): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
// Query para obtener los últimos 5 tickets con información del cliente y región
$query = $entityManager->createQuery("
SELECT
t.id as ticketId,
t.subject,
s.code as status,
p.code as priority,
t.createdAt,
t.updatedAt,
c.id as customerId,
c.firstName as customerFirstName,
c.lastName as customerLastName,
c.email as customerEmail,
c.idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
a.id as agentId,
a.firstName as agentFirstName,
a.lastName as agentLastName,
a.email as agentEmail
FROM UVDeskCoreFrameworkBundle:Ticket t
JOIN UVDeskCoreFrameworkBundle:User c WITH t.customer = c.id
LEFT JOIN UVDeskCoreFrameworkBundle:TicketStatus s WITH t.status = s.id
LEFT JOIN UVDeskCoreFrameworkBundle:TicketPriority p WITH t.priority = p.id
LEFT JOIN UVDeskCoreFrameworkBundle:SupportGroup sg WITH c.idor = sg.id
LEFT JOIN UVDeskCoreFrameworkBundle:User a WITH t.agent = a.id
WHERE t.isTrashed = 0
ORDER BY t.createdAt DESC
");
$query->setMaxResults(5);
$tickets = $query->getResult();
// Formatear los datos para la respuesta
$formattedTickets = [];
foreach ($tickets as $ticket) {
$formattedTickets[] = [
'ticketId' => $ticket['ticketId'],
'subject' => $ticket['subject'],
'status' => $ticket['status'],
'priority' => $ticket['priority'],
'createdAt' => $ticket['createdAt']->format('Y-m-d H:i:s'),
'updatedAt' => $ticket['updatedAt'] ? $ticket['updatedAt']->format('Y-m-d H:i:s') : null,
'customer' => [
'id' => $ticket['customerId'],
'name' => $ticket['customerFirstName'] . ' ' . $ticket['customerLastName'],
'email' => $ticket['customerEmail'],
'idor' => $ticket['idor']
],
'region' => [
'name' => $ticket['supportGroupName'] ?: 'Sin región asignada',
'description' => $ticket['supportGroupDescription'] ?: ''
],
'agent' => $ticket['agentId'] ? [
'id' => $ticket['agentId'],
'name' => $ticket['agentFirstName'] . ' ' . $ticket['agentLastName'],
'email' => $ticket['agentEmail']
] : null
];
}
return new JsonResponse($formattedTickets);
}
public function getAgentsAction(): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
$query = $entityManager->createQuery("
SELECT
a.id as agentId,
a.firstName,
a.lastName,
a.email,
a.isEnabled,
a.lastactivity,
a.idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM UVDeskCoreFrameworkBundle:User a
JOIN UVDeskCoreFrameworkBundle:UserInstance ui WITH a.id = ui.user
JOIN UVDeskCoreFrameworkBundle:SupportRole sr WITH ui.supportRole = sr.id
LEFT JOIN UVDeskCoreFrameworkBundle:SupportGroup sg WITH a.idor = sg.id
WHERE a.isEnabled = 1
AND sr.code = 'ROLE_AGENT'
ORDER BY a.firstName, a.lastName
");
$agents = $query->getResult();
$formattedAgents = [];
foreach ($agents as $agent) {
$formattedAgents[] = [
'id' => $agent['agentId'],
'name' => $agent['firstName'] . ' ' . $agent['lastName'],
'email' => $agent['email'],
'isActive' => $agent['isEnabled'],
'lastActivity' => $agent['lastactivity'] ? $agent['lastactivity']->format('Y-m-d H:i:s') : 'N/A',
'idor' => $agent['idor'],
'role' => $agent['supportRoleCode'],
'region' => [
'name' => $agent['supportGroupName'] ?: 'Sin región asignada',
'description' => $agent['supportGroupDescription'] ?: 'No hay descripción disponible'
]
];
}
return new JsonResponse($formattedAgents);
}
public function assignAgentAction(Request $request): JsonResponse
{
try {
$data = json_decode($request->getContent(), true);
if (!isset($data['ticketId']) || !isset($data['agentId'])) {
return new JsonResponse([
'success' => false,
'message' => 'Se requieren ticketId y agentId'
], 400);
}
$ticketId = (int) $data['ticketId'];
$agentId = (int) $data['agentId'];
$entityManager = $this->getDoctrine()->getManager();
// Verificar que el ticket existe
$ticket = $entityManager->getRepository('UVDeskCoreFrameworkBundle:Ticket')->find($ticketId);
if (!$ticket) {
return new JsonResponse([
'success' => false,
'message' => 'El ticket especificado no existe'
], 404);
}
// Verificar que el agente existe y está activo
$agent = $entityManager->getRepository('UVDeskCoreFrameworkBundle:User')->find($agentId);
if (!$agent) {
return new JsonResponse([
'success' => false,
'message' => 'El agente especificado no existe'
], 404);
}
if (!$agent->getIsEnabled()) {
return new JsonResponse([
'success' => false,
'message' => 'El agente especificado no está activo'
], 400);
}
// Verificar que el usuario tiene rol de agente
$userInstance = $entityManager->getRepository('UVDeskCoreFrameworkBundle:UserInstance')
->findOneBy(['user' => $agentId]);
if (!$userInstance) {
return new JsonResponse([
'success' => false,
'message' => 'El usuario especificado no tiene instancia de agente'
], 400);
}
$supportRole = $userInstance->getSupportRole();
if (!$supportRole || $supportRole->getCode() !== 'ROLE_AGENT') {
return new JsonResponse([
'success' => false,
'message' => 'El usuario especificado no tiene rol de agente'
], 400);
}
// Asignar el agente al ticket
$ticket->setAgent($agent);
// Persistir los cambios
$entityManager->flush();
return new JsonResponse([
'success' => true,
'message' => 'Ticket asignado exitosamente al agente',
'data' => [
'ticketId' => $ticketId,
'agentId' => $agentId,
'agentName' => $agent->getFirstName() . ' ' . $agent->getLastName()
]
]);
} catch (\Exception $e) {
return new JsonResponse([
'success' => false,
'message' => 'Error interno del servidor: ' . $e->getMessage()
], 500);
}
}
}

src/Controller/WorkingHoursController.php line 54