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;
use Webkul\UVDesk\CoreFrameworkBundle\Services\UserService;
class WorkingHoursController extends AbstractController
{
private $userService;
public function __construct(UserService $userService)
{
$this->userService = $userService;
}
public function workingHoursAction(): Response
{
return $this->render('reports/working-hours.html.twig');
}
public function getWorkingHoursStatusAction(): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Obtener países y horarios desde la tabla uv_support_group y uv_working_hours
$sql = "
SELECT
sg.id as supportGroupId,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
wh.timezone,
wh.start_time,
wh.end_time,
wh.monday,
wh.tuesday,
wh.wednesday,
wh.thursday,
wh.friday,
wh.saturday,
wh.sunday,
wh.is_active,
wh.auto_assignment_enabled
FROM uv_support_group sg
LEFT JOIN uv_working_hours wh ON sg.id = wh.support_group_id
WHERE sg.is_active = 1
AND (sg.name LIKE 'OR %' OR sg.name = 'Casa Central')
ORDER BY
CASE
WHEN sg.name LIKE 'OR %' THEN 1
WHEN sg.name = 'Casa Central' THEN 2
ELSE 3
END,
sg.name
";
$stmt = $connection->prepare($sql);
$stmt->execute();
$supportGroups = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// Obtener solo agentes online
$onlineAgentsStatus = $this->getOnlineAgentsStatus();
// Si no hay agentes online, devolver grupos sin agentes
if (empty($onlineAgentsStatus)) {
$agentsByRegion = [];
} else {
// Obtener solo agentes online agrupados por región
$onlineAgentIds = array_keys($onlineAgentsStatus);
$placeholders = str_repeat('?,', count($onlineAgentIds) - 1) . '?';
$sql = "
SELECT
a.id as agentId,
a.first_name as firstName,
a.last_name as lastName,
a.email,
a.is_enabled as isEnabled,
a.idOR as idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM uv_user a
JOIN uv_user_instance ui ON a.id = ui.user_id
JOIN uv_support_role sr ON ui.supportRole_id = sr.id
LEFT JOIN uv_support_group sg ON a.idOR = sg.id
WHERE a.is_enabled = 1
AND sr.code = 'ROLE_AGENT'
AND a.id IN ($placeholders)
ORDER BY a.first_name, a.last_name
";
$stmt = $connection->prepare($sql);
$stmt->execute($onlineAgentIds);
$agents = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// Agrupar agentes por región
$agentsByRegion = [];
foreach ($agents as $agent) {
$regionName = $agent['supportGroupName'] ?: 'Sin región asignada';
if (!isset($agentsByRegion[$regionName])) {
$agentsByRegion[$regionName] = [];
}
$agentId = $agent['agentId'];
$connectionInfo = $onlineAgentsStatus[$agentId];
$agentsByRegion[$regionName][] = [
'id' => $agent['agentId'],
'name' => $agent['firstName'] . ' ' . $agent['lastName'],
'email' => $agent['email'],
'isEnabled' => $agent['isEnabled'],
'isOnline' => $connectionInfo['isOnline'],
'lastActivity' => $connectionInfo['lastActivity'],
'lastAuthType' => $connectionInfo['lastAuthType'],
'idor' => $agent['idor'],
'role' => $agent['supportRoleCode']
];
}
}
$status = [];
foreach ($supportGroups as $group) {
// Extraer nombre del país del nombre del grupo
if ($group['supportGroupName'] === 'Casa Central') {
$countryName = 'Casa Central';
} else {
// Para oficinas regionales: "OR Colombia" -> "Colombia"
$countryName = trim(str_replace('OR', '', $group['supportGroupName']));
}
// Si no hay configuración de horarios, usar valores por defecto
if (!$group['timezone']) {
$group['timezone'] = 'UTC';
$group['start_time'] = '09:00:00';
$group['end_time'] = '18:00:00';
$group['monday'] = 1;
$group['tuesday'] = 1;
$group['wednesday'] = 1;
$group['thursday'] = 1;
$group['friday'] = 1;
$group['saturday'] = 0;
$group['sunday'] = 0;
}
$localTime = new \DateTime('now', new \DateTimeZone($group['timezone']));
$dayOfWeek = (int)$localTime->format('N'); // 1 = Monday, 7 = Sunday
$hour = (int)$localTime->format('G'); // 0-23 hour format
$minute = (int)$localTime->format('i');
$currentTime = $hour * 60 + $minute; // Convertir a minutos para comparación
// Convertir horarios de inicio y fin a minutos
$startTimeParts = explode(':', $group['start_time']);
$endTimeParts = explode(':', $group['end_time']);
$startTimeMinutes = (int)$startTimeParts[0] * 60 + (int)$startTimeParts[1];
$endTimeMinutes = (int)$endTimeParts[0] * 60 + (int)$endTimeParts[1];
// Verificar si es día laboral
$workingDays = [
1 => $group['monday'],
2 => $group['tuesday'],
3 => $group['wednesday'],
4 => $group['thursday'],
5 => $group['friday'],
6 => $group['saturday'],
7 => $group['sunday']
];
$isWorkingDay = $workingDays[$dayOfWeek] == 1;
// Verificar si está dentro del horario laboral
$isWorkingHours = $isWorkingDay && $currentTime >= $startTimeMinutes && $currentTime < $endTimeMinutes;
// Determinar estado
if ($isWorkingDay && $isWorkingHours) {
$statusCode = 'open';
$statusText = 'Abierto';
$statusColor = 'green';
} else {
$statusCode = 'closed';
$statusText = 'Cerrado';
$statusColor = 'red';
}
// Buscar agentes para este país
$countryAgents = [];
foreach ($agentsByRegion as $regionName => $regionAgents) {
if (stripos($regionName, $countryName) !== false ||
stripos($countryName, $regionName) !== false ||
$this->isCountryInRegion($countryName, $regionName)) {
$countryAgents = array_merge($countryAgents, $regionAgents);
}
}
$status[$countryName] = [
'country' => $countryName,
'timezone' => $group['timezone'],
'localTime' => $localTime->format('H:i:s'),
'localDate' => $localTime->format('Y-m-d'),
'dayOfWeek' => $this->getDayOfWeekInSpanish($localTime->format('N')),
'isWorkingDay' => $isWorkingDay,
'isWorkingHours' => $isWorkingHours,
'status' => $statusCode,
'statusText' => $statusText,
'statusColor' => $statusColor,
'workingHours' => $group['start_time'] . ' - ' . $group['end_time'],
'workingDays' => $this->getWorkingDaysArray($workingDays),
'workingDaysText' => $this->getWorkingDaysText($workingDays),
'supportGroupId' => $group['supportGroupId'],
'autoAssignmentEnabled' => $group['auto_assignment_enabled'] ?? 1, // Por defecto activado
'agents' => $countryAgents
];
}
return new JsonResponse($status);
}
private function isCountryInRegion($country, $regionName): bool
{
// Mapeo de países a posibles nombres de regiones
$countryRegionMap = [
'Casa Central' => ['casa central', 'argentina', 'buenos aires', 'córdoba', 'rosario'],
'Colombia' => ['colombia', 'bogotá', 'medellín', 'cali', 'barranquilla'],
'Mexico' => ['mexico', 'méxico', 'ciudad de mexico', 'cdmx', 'guadalajara', 'monterrey'],
'Brasil' => ['brasil', 'brazil', 'sao paulo', 'rio de janeiro', 'brasilia'],
'España' => ['españa', 'spain', 'madrid', 'barcelona', 'valencia', 'sevilla'],
'Peru' => ['peru', 'perú', 'lima', 'arequipa', 'trujillo'],
'Chile' => ['chile', 'santiago', 'valparaíso', 'concepción'],
'Venezuela' => ['venezuela', 'caracas', 'maracaibo', 'valencia'],
'Costa Rica' => ['costa rica', 'san josé', 'cartago', 'alajuela']
];
if (isset($countryRegionMap[$country])) {
foreach ($countryRegionMap[$country] as $regionKeyword) {
if (stripos($regionName, $regionKeyword) !== false) {
return true;
}
}
}
return false;
}
private function getWorkingDaysText($workingDays): string
{
$days = [];
$dayNames = [
1 => 'Lunes',
2 => 'Martes',
3 => 'Miércoles',
4 => 'Jueves',
5 => 'Viernes',
6 => 'Sábado',
7 => 'Domingo'
];
foreach ($workingDays as $dayNum => $isWorking) {
if ($isWorking) {
$days[] = $dayNames[$dayNum];
}
}
if (empty($days)) {
return 'No hay días laborales configurados';
}
if (count($days) === 7) {
return 'Todos los días';
}
if (count($days) === 5 && $workingDays[1] && $workingDays[2] && $workingDays[3] && $workingDays[4] && $workingDays[5]) {
return 'Lunes a Viernes';
}
return implode(', ', $days);
}
private function getWorkingDaysArray($workingDays): array
{
$days = [];
$dayNames = [
1 => 'Lunes',
2 => 'Martes',
3 => 'Miércoles',
4 => 'Jueves',
5 => 'Viernes',
6 => 'Sábado',
7 => 'Domingo'
];
foreach ($workingDays as $dayNum => $isWorking) {
if ($isWorking) {
$days[] = $dayNames[$dayNum];
}
}
return $days;
}
public function getRecentTicketsAction(): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Query nativo MySQL para obtener los últimos 5 tickets
$sql = "
SELECT
t.id as ticketId,
t.subject,
s.code as status,
p.code as priority,
t.created_at as createdAt,
t.updated_at as updatedAt,
c.id as customerId,
c.first_name as customerFirstName,
c.last_name as customerLastName,
c.email as customerEmail,
c.idOR as idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
a.id as agentId,
a.first_name as agentFirstName,
a.last_name as agentLastName,
a.email as agentEmail
FROM uv_ticket t
JOIN uv_user c ON t.customer_id = c.id
LEFT JOIN uv_ticket_status s ON t.status_id = s.id
LEFT JOIN uv_ticket_priority p ON t.priority_id = p.id
LEFT JOIN uv_support_group sg ON c.idOR = sg.id
LEFT JOIN uv_user a ON t.agent_id = a.id
WHERE t.is_trashed = 0
ORDER BY t.created_at DESC
LIMIT 5
";
$stmt = $connection->prepare($sql);
$stmt->execute();
$tickets = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// 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'],
'updatedAt' => $ticket['updatedAt'],
'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();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Obtener solo agentes online
$onlineAgentsStatus = $this->getOnlineAgentsStatus();
// Si no hay agentes online, devolver array vacío
if (empty($onlineAgentsStatus)) {
return new JsonResponse([]);
}
// Obtener solo agentes online
$onlineAgentIds = array_keys($onlineAgentsStatus);
$placeholders = str_repeat('?,', count($onlineAgentIds) - 1) . '?';
$sql = "
SELECT
a.id as agentId,
a.first_name as firstName,
a.last_name as lastName,
a.email,
a.is_enabled as isEnabled,
a.idOR as idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM uv_user a
JOIN uv_user_instance ui ON a.id = ui.user_id
JOIN uv_support_role sr ON ui.supportRole_id = sr.id
LEFT JOIN uv_support_group sg ON a.idOR = sg.id
WHERE a.is_enabled = 1
AND sr.code = 'ROLE_AGENT'
AND a.id IN ($placeholders)
ORDER BY a.first_name, a.last_name
";
$stmt = $connection->prepare($sql);
$stmt->execute($onlineAgentIds);
$agents = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$formattedAgents = [];
foreach ($agents as $agent) {
$agentId = $agent['agentId'];
$connectionInfo = $onlineAgentsStatus[$agentId];
$formattedAgents[] = [
'id' => $agent['agentId'],
'name' => $agent['firstName'] . ' ' . $agent['lastName'],
'email' => $agent['email'],
'isEnabled' => $agent['isEnabled'],
'isOnline' => $connectionInfo['isOnline'],
'lastActivity' => $connectionInfo['lastActivity'],
'lastAuthType' => $connectionInfo['lastAuthType'],
'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 getActiveOfficeAgentsAction(): JsonResponse
{
$entityManager = $this->getDoctrine()->getManager();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Primero obtener las oficinas que están abiertas actualmente
$sql = "
SELECT
sg.id as supportGroupId,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
wh.timezone,
wh.start_time,
wh.end_time,
wh.monday,
wh.tuesday,
wh.wednesday,
wh.thursday,
wh.friday,
wh.saturday,
wh.sunday,
wh.is_active
FROM uv_support_group sg
LEFT JOIN uv_working_hours wh ON sg.id = wh.support_group_id
WHERE sg.is_active = 1
AND (sg.name LIKE 'OR %' OR sg.name = 'Casa Central')
";
$stmt = $connection->prepare($sql);
$stmt->execute();
$supportGroups = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// Determinar qué oficinas están abiertas
$openOffices = [];
foreach ($supportGroups as $group) {
// Si no hay configuración de horarios, usar valores por defecto
if (!$group['timezone']) {
$group['timezone'] = 'UTC';
$group['start_time'] = '09:00:00';
$group['end_time'] = '18:00:00';
$group['monday'] = 1;
$group['tuesday'] = 1;
$group['wednesday'] = 1;
$group['thursday'] = 1;
$group['friday'] = 1;
$group['saturday'] = 0;
$group['sunday'] = 0;
}
$localTime = new \DateTime('now', new \DateTimeZone($group['timezone']));
$dayOfWeek = (int)$localTime->format('N'); // 1 = Monday, 7 = Sunday
$hour = (int)$localTime->format('G'); // 0-23 hour format
$minute = (int)$localTime->format('i');
$currentTime = $hour * 60 + $minute; // Convertir a minutos para comparación
// Convertir horarios de inicio y fin a minutos
$startTimeParts = explode(':', $group['start_time']);
$endTimeParts = explode(':', $group['end_time']);
$startTimeMinutes = (int)$startTimeParts[0] * 60 + (int)$startTimeParts[1];
$endTimeMinutes = (int)$endTimeParts[0] * 60 + (int)$endTimeParts[1];
// Verificar si es día laboral
$workingDays = [
1 => $group['monday'],
2 => $group['tuesday'],
3 => $group['wednesday'],
4 => $group['thursday'],
5 => $group['friday'],
6 => $group['saturday'],
7 => $group['sunday']
];
$isWorkingDay = $workingDays[$dayOfWeek] == 1;
// Verificar si está dentro del horario laboral
$isWorkingHours = $isWorkingDay && $currentTime >= $startTimeMinutes && $currentTime < $endTimeMinutes;
if ($isWorkingHours) {
$openOffices[] = $group['supportGroupId'];
}
}
// Si no hay oficinas abiertas, devolver array vacío
if (empty($openOffices)) {
return new JsonResponse([]);
}
// Obtener solo agentes online
$onlineAgentsStatus = $this->getOnlineAgentsStatus();
// Si no hay agentes online, devolver array vacío
if (empty($onlineAgentsStatus)) {
return new JsonResponse([]);
}
// Obtener solo agentes online de las oficinas abiertas
$onlineAgentIds = array_keys($onlineAgentsStatus);
$placeholders = str_repeat('?,', count($onlineAgentIds) - 1) . '?';
$officePlaceholders = str_repeat('?,', count($openOffices) - 1) . '?';
$sql = "
SELECT
a.id as agentId,
a.first_name as firstName,
a.last_name as lastName,
a.email,
a.is_enabled as isEnabled,
a.idOR as idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM uv_user a
JOIN uv_user_instance ui ON a.id = ui.user_id
JOIN uv_support_role sr ON ui.supportRole_id = sr.id
LEFT JOIN uv_support_group sg ON a.idOR = sg.id
WHERE a.is_enabled = 1
AND sr.code = 'ROLE_AGENT'
AND a.id IN ($placeholders)
AND a.idOR IN ($officePlaceholders)
ORDER BY a.first_name, a.last_name
";
$params = array_merge($onlineAgentIds, $openOffices);
$stmt = $connection->prepare($sql);
$stmt->execute($params);
$agents = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$formattedAgents = [];
foreach ($agents as $agent) {
$agentId = $agent['agentId'];
$connectionInfo = $onlineAgentsStatus[$agentId];
$formattedAgents[] = [
'id' => $agent['agentId'],
'name' => $agent['firstName'] . ' ' . $agent['lastName'],
'email' => $agent['email'],
'isEnabled' => $agent['isEnabled'],
'isOnline' => $connectionInfo['isOnline'],
'lastActivity' => $connectionInfo['lastActivity'],
'lastAuthType' => $connectionInfo['lastAuthType'],
'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();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Verificar que el ticket existe usando query nativo
$sql = "SELECT id FROM uv_ticket WHERE id = ?";
$stmt = $connection->prepare($sql);
$stmt->execute([$ticketId]);
$ticket = $stmt->fetch(\PDO::FETCH_ASSOC);
if (!$ticket) {
return new JsonResponse([
'success' => false,
'message' => 'El ticket especificado no existe'
], 404);
}
// Verificar que el agente existe y está activo
$sql = "SELECT id, first_name, last_name, is_enabled FROM uv_user WHERE id = ?";
$stmt = $connection->prepare($sql);
$stmt->execute([$agentId]);
$agent = $stmt->fetch(\PDO::FETCH_ASSOC);
if (!$agent) {
return new JsonResponse([
'success' => false,
'message' => 'El agente especificado no existe'
], 404);
}
if (!$agent['is_enabled']) {
return new JsonResponse([
'success' => false,
'message' => 'El agente especificado no está activo'
], 400);
}
// Verificar que el usuario tiene rol de agente
$sql = "
SELECT sr.code
FROM uv_user_instance ui
JOIN uv_support_role sr ON ui.supportRole_id = sr.id
WHERE ui.user_id = ?
";
$stmt = $connection->prepare($sql);
$stmt->execute([$agentId]);
$role = $stmt->fetch(\PDO::FETCH_ASSOC);
if (!$role || $role['code'] !== 'ROLE_AGENT') {
return new JsonResponse([
'success' => false,
'message' => 'El usuario especificado no tiene rol de agente'
], 400);
}
// Asignar el agente al ticket usando query nativo
$sql = "UPDATE uv_ticket SET agent_id = ? WHERE id = ?";
$stmt = $connection->prepare($sql);
$stmt->execute([$agentId, $ticketId]);
return new JsonResponse([
'success' => true,
'message' => 'Ticket asignado exitosamente al agente',
'data' => [
'ticketId' => $ticketId,
'agentId' => $agentId,
'agentName' => $agent['first_name'] . ' ' . $agent['last_name']
]
]);
} catch (\Exception $e) {
return new JsonResponse([
'success' => false,
'message' => 'Error interno del servidor: ' . $e->getMessage()
], 500);
}
}
public function updateWorkingHoursAction(Request $request): JsonResponse
{
try {
$data = json_decode($request->getContent(), true);
if (!isset($data['supportGroupId']) || !isset($data['timezone']) ||
!isset($data['startTime']) || !isset($data['endTime'])) {
return new JsonResponse([
'success' => false,
'message' => 'Se requieren todos los campos obligatorios'
], 400);
}
$supportGroupId = (int) $data['supportGroupId'];
$timezone = $data['timezone'];
$startTime = $data['startTime'];
$endTime = $data['endTime'];
$monday = isset($data['monday']) ? (int) $data['monday'] : 1;
$tuesday = isset($data['tuesday']) ? (int) $data['tuesday'] : 1;
$wednesday = isset($data['wednesday']) ? (int) $data['wednesday'] : 1;
$thursday = isset($data['thursday']) ? (int) $data['thursday'] : 1;
$friday = isset($data['friday']) ? (int) $data['friday'] : 1;
$saturday = isset($data['saturday']) ? (int) $data['saturday'] : 0;
$sunday = isset($data['sunday']) ? (int) $data['sunday'] : 0;
$entityManager = $this->getDoctrine()->getManager();
// Forzar refresh - cerrar y reabrir la conexión para evitar cache
$entityManager->close();
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Forzar que no use cache de transacciones
$connection->executeQuery('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
// Verificar que el grupo de soporte existe
$sql = "SELECT id FROM uv_support_group WHERE id = ? AND is_active = 1";
$stmt = $connection->prepare($sql);
$stmt->execute([$supportGroupId]);
$supportGroup = $stmt->fetch(\PDO::FETCH_ASSOC);
if (!$supportGroup) {
return new JsonResponse([
'success' => false,
'message' => 'El grupo de soporte especificado no existe o no está activo'
], 404);
}
// Verificar si ya existe configuración de horarios para este grupo
$sql = "SELECT id FROM uv_working_hours WHERE support_group_id = ?";
$stmt = $connection->prepare($sql);
$stmt->execute([$supportGroupId]);
$existingHours = $stmt->fetch(\PDO::FETCH_ASSOC);
if ($existingHours) {
// Actualizar horarios existentes
$sql = "
UPDATE uv_working_hours
SET timezone = ?, start_time = ?, end_time = ?,
monday = ?, tuesday = ?, wednesday = ?, thursday = ?,
friday = ?, saturday = ?, sunday = ?, updated_at = CURRENT_TIMESTAMP
WHERE support_group_id = ?
";
$stmt = $connection->prepare($sql);
$stmt->execute([
$timezone, $startTime, $endTime,
$monday, $tuesday, $wednesday, $thursday, $friday, $saturday, $sunday,
$supportGroupId
]);
} else {
// Crear nueva configuración de horarios
$sql = "
INSERT INTO uv_working_hours
(support_group_id, timezone, start_time, end_time,
monday, tuesday, wednesday, thursday, friday, saturday, sunday)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
";
$stmt = $connection->prepare($sql);
$stmt->execute([
$supportGroupId, $timezone, $startTime, $endTime,
$monday, $tuesday, $wednesday, $thursday, $friday, $saturday, $sunday
]);
}
return new JsonResponse([
'success' => true,
'message' => 'Horarios laborales actualizados exitosamente',
'data' => [
'supportGroupId' => $supportGroupId,
'timezone' => $timezone,
'startTime' => $startTime,
'endTime' => $endTime,
'workingDays' => [
'monday' => $monday,
'tuesday' => $tuesday,
'wednesday' => $wednesday,
'thursday' => $thursday,
'friday' => $friday,
'saturday' => $saturday,
'sunday' => $sunday
]
]
]);
} catch (\Exception $e) {
return new JsonResponse([
'success' => false,
'message' => 'Error interno del servidor: ' . $e->getMessage()
], 500);
}
}
/**
* Obtiene solo los agentes que están en línea con su estado de conexión y última actividad
* @return array
*/
private function getOnlineAgentsStatus(): array
{
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Obtener agentes online usando el servicio
$onlineAgents = $this->userService->getUsersOnline();
$onlineAgentIds = array_column($onlineAgents, 'id');
// Si no hay agentes online, devolver array vacío
if (empty($onlineAgentIds)) {
return [];
}
// Obtener última actividad solo de agentes online desde AgentLogin
$placeholders = str_repeat('?,', count($onlineAgentIds) - 1) . '?';
$sql = "
SELECT
al.agent_id as agentId,
MAX(al.date) as lastActivityDate,
al.auth_type as lastAuthType
FROM agent_login al
WHERE al.agent_id IN ($placeholders)
GROUP BY al.agent_id
";
$stmt = $connection->prepare($sql);
$stmt->execute($onlineAgentIds);
$agentActivities = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// Crear array con estado de conexión y última actividad solo para agentes online
$agentStatus = [];
foreach ($agentActivities as $activity) {
$agentId = $activity['agentId'];
$agentStatus[$agentId] = [
'isOnline' => true, // Todos los agentes en este array están online
'lastActivity' => $activity['lastActivityDate'],
'lastAuthType' => $activity['lastAuthType']
];
}
return $agentStatus;
}
/**
* Convierte el número del día de la semana a nombre en español
* @param int $dayNumber 1 = Lunes, 7 = Domingo
* @return string
*/
private function getDayOfWeekInSpanish($dayNumber)
{
$days = [
1 => 'Lunes',
2 => 'Martes',
3 => 'Miércoles',
4 => 'Jueves',
5 => 'Viernes',
6 => 'Sábado',
7 => 'Domingo'
];
return $days[$dayNumber] ?? 'Desconocido';
}
/**
* Método público para obtener un agente disponible de oficinas abiertas
* Versión optimizada que no cierra el EntityManager
* @return array|null Retorna el primer agente disponible o null si no hay agentes
*/
public function getAvailableOfficeAgent(): ?array
{
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Obtener solo agentes online
$onlineAgentsStatus = $this->getOnlineAgentsStatus();
echo "DEBUG - onlineAgentsStatus: ";
var_dump($onlineAgentsStatus);
echo " ";
// Si no hay agentes online, devolver null
if (empty($onlineAgentsStatus)) {
echo "DEBUG - No online agents found ";
return null;
}
// Obtener oficinas abiertas
$openOffices = $this->getOpenOffices();
echo "DEBUG - openOffices: ";
var_dump($openOffices);
echo " ";
// Si no hay oficinas abiertas, devolver null
if (empty($openOffices)) {
echo "DEBUG - No open offices found ";
return null;
}
// Obtener el primer agente online de oficinas abiertas
$onlineAgentIds = array_keys($onlineAgentsStatus);
$placeholders = str_repeat('?,', count($onlineAgentIds) - 1) . '?';
$officePlaceholders = str_repeat('?,', count($openOffices) - 1) . '?';
$sql = "
SELECT
a.id as agentId,
a.first_name as firstName,
a.last_name as lastName,
a.email,
a.is_enabled as isEnabled,
a.idOR as idor,
sg.name as supportGroupName,
sg.description as supportGroupDescription,
sr.code as supportRoleCode
FROM uv_user a
JOIN uv_user_instance ui ON a.id = ui.user_id
JOIN uv_support_role sr ON ui.supportRole_id = sr.id
LEFT JOIN uv_support_group sg ON a.idOR = sg.id
WHERE a.is_enabled = 1
AND sr.code = 'ROLE_AGENT'
AND a.id IN ($placeholders)
AND a.idOR IN ($officePlaceholders)
ORDER BY a.first_name, a.last_name
LIMIT 1
";
$params = array_merge($onlineAgentIds, $openOffices);
echo "DEBUG - SQL params: ";
var_dump($params);
echo " ";
$stmt = $connection->prepare($sql);
$stmt->execute($params);
$agent = $stmt->fetch(\PDO::FETCH_ASSOC);
echo "DEBUG - Found agent: ";
var_dump($agent);
echo " ";
return $agent ?: null;
}
/**
* Obtiene las oficinas que están actualmente abiertas
* @return array
*/
private function getOpenOffices(): array
{
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Obtener grupos de soporte con horarios y asignación automática habilitada
$sql = "
SELECT
sg.id as supportGroupId,
sg.name as supportGroupName,
wh.timezone,
wh.start_time,
wh.end_time,
wh.monday,
wh.tuesday,
wh.wednesday,
wh.thursday,
wh.friday,
wh.saturday,
wh.sunday,
wh.auto_assignment_enabled
FROM uv_support_group sg
LEFT JOIN uv_working_hours wh ON sg.id = wh.support_group_id
WHERE sg.is_active = 1
AND (sg.name LIKE 'OR %' OR sg.name = 'Casa Central')
AND (wh.auto_assignment_enabled = 1 OR wh.auto_assignment_enabled IS NULL)
";
$stmt = $connection->prepare($sql);
$stmt->execute();
$supportGroups = $stmt->fetchAll(\PDO::FETCH_ASSOC);
// Determinar qué oficinas están abiertas
$openOffices = [];
foreach ($supportGroups as $group) {
// Si no hay configuración de horarios, usar valores por defecto
if (!$group['timezone']) {
$group['timezone'] = 'UTC';
$group['start_time'] = '09:00:00';
$group['end_time'] = '18:00:00';
$group['monday'] = 1;
$group['tuesday'] = 1;
$group['wednesday'] = 1;
$group['thursday'] = 1;
$group['friday'] = 1;
$group['saturday'] = 0;
$group['sunday'] = 0;
}
$localTime = new \DateTime('now', new \DateTimeZone($group['timezone']));
$dayOfWeek = (int)$localTime->format('N');
$hour = (int)$localTime->format('G');
$minute = (int)$localTime->format('i');
$currentTime = $hour * 60 + $minute;
// Convertir horarios a minutos
$startTimeParts = explode(':', $group['start_time']);
$endTimeParts = explode(':', $group['end_time']);
$startTimeMinutes = (int)$startTimeParts[0] * 60 + (int)$startTimeParts[1];
$endTimeMinutes = (int)$endTimeParts[0] * 60 + (int)$endTimeParts[1];
// Verificar días laborales
$workingDays = [
1 => $group['monday'],
2 => $group['tuesday'],
3 => $group['wednesday'],
4 => $group['thursday'],
5 => $group['friday'],
6 => $group['saturday'],
7 => $group['sunday']
];
$isWorkingDay = $workingDays[$dayOfWeek] == 1;
$isWorkingHours = $isWorkingDay && $currentTime >= $startTimeMinutes && $currentTime < $endTimeMinutes;
if ($isWorkingHours) {
$openOffices[] = $group['supportGroupId'];
}
}
return $openOffices;
}
/**
* Actualiza el estado de asignación automática para una oficina
* @Route("/working-hours/toggle-auto-assignment", name="helpdesk_report_working_hours_toggle_auto_assignment", methods={"POST"})
*/
public function toggleAutoAssignmentAction(Request $request): JsonResponse
{
try {
$data = json_decode($request->getContent(), true);
if (!isset($data['supportGroupId']) || !isset($data['enabled'])) {
return new JsonResponse([
'success' => false,
'message' => 'Faltan parámetros requeridos'
], 400);
}
$supportGroupId = $data['supportGroupId'];
$enabled = (bool)$data['enabled'];
$entityManager = $this->getDoctrine()->getManager();
$connection = $entityManager->getConnection();
// Verificar si existe el registro en uv_working_hours
$checkSql = "SELECT id FROM uv_working_hours WHERE support_group_id = ?";
$stmt = $connection->prepare($checkSql);
$stmt->execute([$supportGroupId]);
$existingRecord = $stmt->fetch(\PDO::FETCH_ASSOC);
if ($existingRecord) {
// Actualizar registro existente
$updateSql = "UPDATE uv_working_hours SET auto_assignment_enabled = ? WHERE support_group_id = ?";
$stmt = $connection->prepare($updateSql);
$stmt->execute([$enabled ? 1 : 0, $supportGroupId]);
} else {
// Crear nuevo registro con valores por defecto
$insertSql = "
INSERT INTO uv_working_hours
(support_group_id, timezone, start_time, end_time, monday, tuesday, wednesday, thursday, friday, saturday, sunday, is_active, auto_assignment_enabled)
VALUES (?, 'UTC', '09:00:00', '18:00:00', 1, 1, 1, 1, 1, 0, 0, 1, ?)
";
$stmt = $connection->prepare($insertSql);
$stmt->execute([$supportGroupId, $enabled ? 1 : 0]);
}
return new JsonResponse([
'success' => true,
'message' => 'Estado de asignación automática actualizado correctamente',
'enabled' => $enabled
]);
} catch (\Exception $e) {
return new JsonResponse([
'success' => false,
'message' => 'Error al actualizar el estado: ' . $e->getMessage()
], 500);
}
}
}

src/Controller/WorkingHoursController.php line 390