You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
254 lines
8.5 KiB
254 lines
8.5 KiB
<?php
|
|
header('Content-Type: application/json');
|
|
header('Access-Control-Allow-Origin: *');
|
|
|
|
$host = 'localhost';
|
|
$user = 'reportes';
|
|
$pass = 'Rep0rt3s2024';
|
|
$db = 'asteriskcdrdb';
|
|
|
|
try {
|
|
$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);
|
|
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
|
|
|
// Obtener parámetros y agregar hora completa
|
|
$fecha_inicio = isset($_GET['fecha_inicio']) ? $_GET['fecha_inicio'] . ' 00:00:00' : date('Y-m-01') . ' 00:00:00';
|
|
$fecha_fin = isset($_GET['fecha_fin']) ? $_GET['fecha_fin'] . ' 23:59:59' : date('Y-m-t') . ' 23:59:59';
|
|
|
|
$response = [
|
|
'periodo' => [
|
|
'inicio' => $_GET['fecha_inicio'] ?? date('Y-m-01'),
|
|
'fin' => $_GET['fecha_fin'] ?? date('Y-m-t')
|
|
],
|
|
'salientes' => getSalientes($conn, $fecha_inicio, $fecha_fin),
|
|
'entrantes' => getEntrantes($conn, $fecha_inicio, $fecha_fin),
|
|
'internas' => getInternas($conn, $fecha_inicio, $fecha_fin)
|
|
];
|
|
|
|
echo json_encode($response, JSON_PRETTY_PRINT);
|
|
|
|
} catch(PDOException $e) {
|
|
echo json_encode(['error' => $e->getMessage()]);
|
|
}
|
|
|
|
function getSalientes($conn, $inicio, $fin) {
|
|
// Llamadas salientes: dst externo (>4 caracteres); extensión = src con longitud <= 4 (excluye números largos).
|
|
// # Extensiones: COUNT(DISTINCT src) solo entre esas filas.
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
COUNT(*) as total_llamadas,
|
|
SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
|
|
SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
|
|
SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
|
|
SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
|
|
SUM(billsec) as minutos_totales,
|
|
AVG(billsec) as duracion_promedio,
|
|
COUNT(DISTINCT NULLIF(TRIM(src), '')) as extensiones
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND LENGTH(dst) > 4
|
|
AND TRIM(IFNULL(src, '')) <> ''
|
|
AND LENGTH(TRIM(src)) <= 4
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$stats = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
// Por día
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
DATE(calldate) as fecha,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND LENGTH(dst) > 4
|
|
AND TRIM(IFNULL(src, '')) <> ''
|
|
AND LENGTH(TRIM(src)) <= 4
|
|
GROUP BY DATE(calldate)
|
|
ORDER BY fecha
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Top extensiones
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
src as extension,
|
|
cnam as nombre,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND LENGTH(dst) > 4
|
|
AND TRIM(IFNULL(src, '')) <> ''
|
|
AND LENGTH(TRIM(src)) <= 4
|
|
GROUP BY src, cnam
|
|
ORDER BY llamadas DESC
|
|
LIMIT 10
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$top_extensiones = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Destinos más llamados
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
dst as destino,
|
|
COUNT(*) as llamadas
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND LENGTH(dst) > 4
|
|
AND TRIM(IFNULL(src, '')) <> ''
|
|
AND LENGTH(TRIM(src)) <= 4
|
|
GROUP BY dst
|
|
ORDER BY llamadas DESC
|
|
LIMIT 5
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return [
|
|
'stats' => $stats,
|
|
'por_dia' => $por_dia,
|
|
'top_extensiones' => $top_extensiones,
|
|
'destinos' => $destinos
|
|
];
|
|
}
|
|
|
|
function getEntrantes($conn, $inicio, $fin) {
|
|
// Llamadas entrantes: src vacío o externo
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
COUNT(*) as total_llamadas,
|
|
SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
|
|
SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
|
|
SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
|
|
SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
|
|
SUM(billsec) as minutos_totales,
|
|
COUNT(DISTINCT dst) as extensiones
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
|
|
AND LENGTH(dst) <= 4
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$stats = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
// Por día
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
DATE(calldate) as fecha,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
|
|
AND LENGTH(dst) <= 4
|
|
GROUP BY DATE(calldate)
|
|
ORDER BY fecha
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Top destinos (extensiones que reciben)
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
dst as extension,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND (LENGTH(src) > 4 OR src = '' OR src IS NULL)
|
|
AND LENGTH(dst) <= 4
|
|
GROUP BY dst
|
|
ORDER BY llamadas DESC
|
|
LIMIT 10
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$top_destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return [
|
|
'stats' => $stats,
|
|
'por_dia' => $por_dia,
|
|
'top_destinos' => $top_destinos
|
|
];
|
|
}
|
|
|
|
function getInternas($conn, $inicio, $fin) {
|
|
// Llamadas internas: src y dst extensión exactamente 4 dígitos numéricos
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
COUNT(*) as total_llamadas,
|
|
SUM(CASE WHEN disposition = 'ANSWERED' THEN 1 ELSE 0 END) as contestadas,
|
|
SUM(CASE WHEN disposition = 'NO ANSWER' THEN 1 ELSE 0 END) as no_contestadas,
|
|
SUM(CASE WHEN disposition = 'FAILED' THEN 1 ELSE 0 END) as fallidas,
|
|
SUM(CASE WHEN disposition = 'BUSY' THEN 1 ELSE 0 END) as ocupadas,
|
|
SUM(billsec) as minutos_totales,
|
|
COUNT(DISTINCT NULLIF(TRIM(src), '')) as extensiones_origen
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
|
|
AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$stats = $stmt->fetch(PDO::FETCH_ASSOC);
|
|
|
|
// Por día
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
DATE(calldate) as fecha,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
|
|
AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
|
|
GROUP BY DATE(calldate)
|
|
ORDER BY fecha
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$por_dia = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Top orígenes
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
src as extension,
|
|
cnam as nombre,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
|
|
AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
|
|
GROUP BY src, cnam
|
|
ORDER BY llamadas DESC
|
|
LIMIT 10
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$top_origen = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
// Top destinos
|
|
$stmt = $conn->prepare("
|
|
SELECT
|
|
dst as extension,
|
|
COUNT(*) as llamadas,
|
|
SUM(billsec) as minutos
|
|
FROM cdr
|
|
WHERE calldate BETWEEN :inicio AND :fin
|
|
AND TRIM(IFNULL(src, '')) REGEXP '^[0-9]{4}$'
|
|
AND TRIM(IFNULL(dst, '')) REGEXP '^[0-9]{4}$'
|
|
GROUP BY dst
|
|
ORDER BY llamadas DESC
|
|
LIMIT 5
|
|
");
|
|
$stmt->execute(['inicio' => $inicio, 'fin' => $fin]);
|
|
$top_destinos = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
|
|
|
return [
|
|
'stats' => $stats,
|
|
'por_dia' => $por_dia,
|
|
'top_origen' => $top_origen,
|
|
'top_destinos' => $top_destinos
|
|
];
|
|
}
|
|
?>
|