Reports & Export - Dashboard Dilemas

Overview

The Reports and Export functions enable generation of comprehensive analytics reports and data export capabilities. These are used by the weekly report system and CSV export endpoints.

Weekly Report Generation

Weekly Report KPIs

The weekly report system (weekly_report.php) generates executive summaries with the following metrics:

Session Statistics

SELECT COUNT(*) as total_sessions,
       COUNT(DISTINCT userId) as unique_users,
       ROUND(AVG(score), 1) as avg_score,
       COUNT(DISTINCT CASE WHEN finishedAt IS NOT NULL OR endedAt IS NOT NULL THEN id END) as sessions_finished,
       ROUND(COUNT(DISTINCT CASE WHEN finishedAt IS NOT NULL OR endedAt IS NOT NULL THEN id END) / NULLIF(COUNT(*), 0) * 100, 1) as completion_rate
FROM de_app_sessions
WHERE startedAt BETWEEN :start_date AND :end_date

Tracked Metrics

total_sessions

int

Total sessions in the reporting period

unique_users

int

Unique users who played during the period

avg_score

float

Average score across all sessions

sessions_finished

int

Count of completed sessions

completion_rate

float

Percentage of sessions that were completed

Example Usage

// Generate weekly report for the last 7 days
$weekEnd = date('Y-m-d');
$weekStart = date('Y-m-d', strtotime('-7 days'));

// Fetch weekly KPIs
$stmt = $pdo->query("
    SELECT COUNT(*) as total_sessions,
           COUNT(DISTINCT userId) as unique_users,
           ROUND(AVG(score), 1) as avg_score
    FROM de_app_sessions
    WHERE startedAt BETWEEN '{$weekStart} 00:00:00' AND '{$weekEnd} 23:59:59'
");
$weeklyStats = $stmt->fetch(PDO::FETCH_ASSOC);

Weekly Report Components

Top Dilemas by Activity

Identifies the most played games during the reporting period.

SELECT g.post_title as name, COUNT(s.id) as sessions
FROM de_app_sessions s
JOIN de_posts g ON s.gameId = g.ID
WHERE s.startedAt BETWEEN :start_date AND :end_date
GROUP BY g.ID 
ORDER BY sessions DESC 
LIMIT 3

Top Active Clients

Ranks clients by user engagement during the period.

SELECT c.post_title as name, COUNT(DISTINCT s.userId) as users
FROM de_app_sessions s
JOIN de_posts g ON s.gameId = g.ID
JOIN de_postmeta pm ON g.ID = pm.post_id AND pm.meta_key = '_cliente_asociado'
JOIN de_posts c ON CAST(pm.meta_value AS UNSIGNED) = c.ID
WHERE s.startedAt BETWEEN :start_date AND :end_date
  AND c.post_status = 'publish'
GROUP BY c.ID 
ORDER BY users DESC 
LIMIT 3

New Users

Counts users registered during the reporting period.

SELECT COUNT(*) 
FROM de_users 
WHERE user_registered BETWEEN :start_date AND :end_date

Pending Users

Identifies users with incomplete sessions (inactive for 3+ days).

SELECT COUNT(DISTINCT userId) 
FROM de_app_sessions
WHERE (finishedAt IS NULL OR finishedAt < '1000-01-01')
  AND startedAt < DATE_SUB(NOW(), INTERVAL 3 DAY)

Daily Session Distribution

Breaks down sessions by date for trend visualization.

SELECT DATE(startedAt) as day, COUNT(*) as sessions
FROM de_app_sessions
WHERE startedAt BETWEEN :start_date AND :end_date
GROUP BY DATE(startedAt) 
ORDER BY day ASC

Email Report Delivery

Sending Weekly Reports

The system supports sending HTML-formatted reports via email:

// Send weekly report
if ($_POST['action'] === 'send_email') {
    $emails = array_map('trim', explode(',', $_POST['email_to']));
    $valid_emails = array_filter($emails, fn($e) => filter_var($e, FILTER_VALIDATE_EMAIL));
    
    $subject = "📊 Reporte Semanal — Dilemas Éticos (" . 
               date('d/m/Y', strtotime($weekStart)) . " al " . 
               date('d/m/Y') . ")";
    
    ob_start();
    include __DIR__ . '/includes/weekly_report_email.php';
    $htmlBody = ob_get_clean();
    
    $headers = "MIME-Version: 1.0\r\n" .
               "Content-type: text/html; charset=utf-8\r\n" .
               "From: Dilemas Éticos <[email protected]>\r\n";
    
    $success = mail(implode(', ', $valid_emails), $subject, $htmlBody, $headers);
}

Email Template Variables

The email template (includes/weekly_report_email.php) has access to:

$row - Main statistics array
$topGames - Top 3 games array
$topClients - Top 3 clients array
$newUsers - New user count
$pendingUsers - Pending user count
$weekStart, $weekEnd - Date range

CSV Export Functions

Client Export

Exports strategic client data to CSV format. Endpoint: export_clients.php Columns:

ID
Nombre Cliente
Fecha Alta
Areas Activas
Dilemas Asignados
Usuarios Activos
Usuarios Invitados
Tasa Finalizacion (%)
Promedio Aciertos (%)
Indice Riesgo (%)

Example Usage

// export_clients.php
require_once 'includes/config.php';
require_once 'includes/auth.php';
require_once 'includes/functions.php';

require_login();
if (!check_is_admin()) {
    header('HTTP/1.0 403 Forbidden');
    exit('Access Denied');
}

$filters = [
    'search' => $_GET['search'] ?? '',
    'start_date' => $_GET['start_date'] ?? '',
    'end_date' => $_GET['end_date'] ?? ''
];

$clients = get_strategic_clients_list($filters);

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=clientes_export_' . date('Y-m-d') . '.csv');

$output = fopen('php://output', 'w');
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM for Excel

fputcsv($output, ['ID', 'Nombre Cliente', 'Fecha Alta', ...]);

foreach ($clients as $c) {
    fputcsv($output, [
        $c['id'],
        $c['name'],
        $c['created_at'],
        $c['areas_count'],
        $c['games_count'],
        $c['active_users'],
        $c['invited_users'],
        $c['completion_rate'],
        $c['avg_correctness'],
        $c['risk_index']
    ]);
}

fclose($output);
exit;

Generic CSV Export

The export_csv.php endpoint supports multiple export types. Endpoint: export_csv.php?type=[clients|users]

Client Export

Parameters:

type=clients
search (optional)
start_date (optional)
end_date (optional)
order_by (optional)
industry (optional)
segment (optional)

Columns:

ID
Cliente
Industria
Segmento
Areas
Juegos
Usuarios Invitados
Usuarios Activos
Tasa Finalización (%)
Avg Correctas (%)
Índice Riesgo (%)

User Export

Parameters:

type=users
client (optional)
area (optional)
game_id (optional)
search (optional)

Columns:

ID
Nombre
Email
Cliente
Área
Asignados
Finalizados
Finalización (%)
Performance (%)
Riesgo (%)

Example Usage

// Export all clients with filters
$filters = [
    'search' => $_GET['search'] ?? '',
    'start_date' => $_GET['start_date'] ?? '',
    'end_date' => $_GET['end_date'] ?? '',
    'order_by' => $_GET['order_by'] ?? 'name_asc',
    'industry' => $_GET['industry'] ?? 'all',
    'segment' => $_GET['segment'] ?? 'all',
];

$clients = get_strategic_clients_list($filters, 9999, 0);

Question Analytics Export

Endpoint: export_questions.php Exports detailed question analytics data. Parameters:

client (optional)
area (optional)
game_id (optional)
search (optional)
category (optional)

Columns:

ID
Pregunta
Categoría
Cliente
Dilema
Es Crítica
Total Intentos
% Acierto
% Error
Tiempo Promedio (s)
Dificultad (0-1)
Riesgo Crítico

Example Implementation

// export_questions.php
require_once 'includes/config.php';
require_once 'includes/auth.php';
require_once 'includes/functions.php';
require_once 'includes/admin_log.php';

require_login();
if (!check_is_admin()) {
    header('HTTP/1.0 403 Forbidden');
    exit('Access Denied');
}

$filters = [
    'client' => $_GET['client'] ?? '',
    'area' => $_GET['area'] ?? '',
    'game_id' => $_GET['game_id'] ?? '',
    'search' => $_GET['search'] ?? '',
    'category' => $_GET['category'] ?? '',
];

admin_log('export_questions', 'Export analítica de preguntas', $filters);

$questions = get_transversal_questions($filters, null, 0);

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=reporte_preguntas_' . date('Y-m-d') . '.csv');

$output = fopen('php://output', 'w');
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM

fputcsv($output, ['ID', 'Pregunta', 'Categoría', 'Cliente', 'Dilema', ...]);

foreach ($questions as $q) {
    fputcsv($output, [
        $q['id'] ?? '',
        $q['text'] ?? '',
        $q['category'] ?? '',
        $q['client_name'] ?? 'N/A',
        $q['dilemma_name'] ?? 'N/A',
        (!empty($q['is_critical']) && $q['is_critical']) ? 'Sí' : 'No',
        $q['total_attempts'] ?? 0,
        $q['success_rate'] ?? 0,
        $q['error_rate'] ?? 0,
        $q['avg_time'] ?? 0,
        $q['difficulty_index'] ?? 0,
        $q['risk_index'] ?? 0
    ]);
}

fclose($output);
exit;

CSV Export Best Practices

UTF-8 BOM for Excel Compatibility

Always include the UTF-8 BOM to ensure proper character encoding in Excel:

$output = fopen('php://output', 'w');
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

Security Considerations

Always validate user permissions before allowing exports:

require_login();
if (!check_is_admin()) {
    header('HTTP/1.0 403 Forbidden');
    exit('Access Denied');
}

Large Dataset Handling

// Use unbounded limits for exports (not pagination)
$data = get_strategic_clients_list($filters, 9999, 0);

// For very large datasets, consider streaming:
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($output, $row);
    flush(); // Send data incrementally
}

Filename Conventions

// Include type and date in filename
$filename = $type . "_export_" . date('Y-m-d') . ".csv";
header('Content-Disposition: attachment; filename=' . $filename);

Audit Logging

// Log all exports for audit trail
require_once 'includes/admin_log.php';
admin_log('export_csv', "Export tipo '{$type}'", $filters);

Report Visualization

Chart.js Integration

The analytics dashboard uses Chart.js for visualizations:

// Daily session chart
new Chart(document.getElementById('weeklyChart'), {
    type: 'bar',
    data: {
        labels: <?= json_encode($chartLabels) ?>,
        datasets: [{
            label: 'Sesiones',
            data: <?= json_encode($chartData) ?>,
            backgroundColor: 'rgba(37,99,235,0.15)',
            borderColor: '#2563eb',
            borderWidth: 2,
            borderRadius: 8
        }]
    },
    options: {
        responsive: true,
        scales: {
            y: { beginAtZero: true }
        }
    }
});

Data Preparation for Charts

// Prepare daily sessions for chart
$chartLabels = [];
$chartData = [];
$dailyMap = array_column($dailySessions, 'sessions', 'day');

$startTs = strtotime($weekStart);
$endTs = strtotime($weekEnd);
$daysDiff = min(max(0, floor(($endTs - $startTs) / 86400)), 60);

for ($i = 0; $i <= $daysDiff; $i++) {
    $d = date('Y-m-d', strtotime("+$i days", $startTs));
    $chartLabels[] = date('D d', strtotime($d));
    $chartData[] = (int)($dailyMap[$d] ?? 0);
}

AJAX Report Sending

Frontend Implementation

// Send email via AJAX
const form = document.getElementById('ajaxEmailForm');
form.addEventListener('submit', async (e) => {
    e.preventDefault();
    const btn = document.getElementById('btnSendEmail');
    btn.disabled = true;
    btn.innerHTML = '⏳ Enviando...';
    
    try {
        const formData = new FormData(form);
        const response = await fetch('', {
            method: 'POST',
            body: formData,
            headers: { 'X-Requested-With': 'XMLHttpRequest' }
        });
        
        const data = await response.json();
        
        if (data.success) {
            alert('✅ Reporte enviado correctamente');
            form.reset();
        } else {
            alert('❌ Error: ' + data.message);
        }
    } catch (err) {
        alert('❌ Error al enviar el correo');
    } finally {
        btn.disabled = false;
        btn.innerHTML = '📤 Enviar';
    }
});

Backend JSON Response

if (!empty($_SERVER['HTTP_X_REQUESTED_WITH']) && 
    strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') {
    header('Content-Type: application/json');
    echo json_encode([
        'success' => $emailSent,
        'message' => $emailSent ? 'Reporte enviado correctamente' : $emailError
    ]);
    exit;
}

Complete Export Workflow

// 1. Authenticate and authorize
require_login();
if (!check_is_admin()) {
    http_response_code(403);
    exit('Access Denied');
}

// 2. Collect filters
$filters = [
    'client' => $_GET['client'] ?? 'all',
    'start_date' => $_GET['start_date'] ?? '',
    'end_date' => $_GET['end_date'] ?? ''
];

// 3. Log the export
admin_log('export_analytics', 'Exported analytics data', $filters);

// 4. Fetch data (no pagination limit)
$data = get_analytics_data($filters, 9999, 0);

// 5. Set headers
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=analytics_' . date('Y-m-d_His') . '.csv');

// 6. Write CSV
$output = fopen('php://output', 'w');
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// 7. Write headers and data
fputcsv($output, ['Column1', 'Column2', ...]);
foreach ($data as $row) {
    fputcsv($output, $row);
}

// 8. Clean up
fclose($output);
exit;

Core Functions

Analytics

​Overview

​Weekly Report Generation

​Weekly Report KPIs

​Session Statistics

​Tracked Metrics

​Example Usage

​Weekly Report Components

​Top Dilemas by Activity

​Top Active Clients

​New Users

​Pending Users

​Daily Session Distribution

​Email Report Delivery

​Sending Weekly Reports

​Email Template Variables

​CSV Export Functions

​Client Export

​Example Usage

​Generic CSV Export

​Client Export

​User Export

​Example Usage

​Question Analytics Export

​Example Implementation

​CSV Export Best Practices

​UTF-8 BOM for Excel Compatibility

​Security Considerations

​Large Dataset Handling

​Filename Conventions

​Audit Logging

​Report Visualization

​Chart.js Integration

​Data Preparation for Charts

​AJAX Report Sending

​Frontend Implementation

​Backend JSON Response

​Complete Export Workflow

Build docs developers (and LLMs) love

Overview

Weekly Report Generation

Weekly Report KPIs

Session Statistics

Tracked Metrics

Example Usage

Weekly Report Components

Top Dilemas by Activity

Top Active Clients

New Users

Pending Users

Daily Session Distribution

Email Report Delivery

Sending Weekly Reports

Email Template Variables

CSV Export Functions

Client Export

Example Usage

Generic CSV Export

Client Export

User Export

Example Usage

Question Analytics Export

Example Implementation

CSV Export Best Practices

UTF-8 BOM for Excel Compatibility

Security Considerations

Large Dataset Handling

Filename Conventions

Audit Logging

Report Visualization

Chart.js Integration

Data Preparation for Charts

AJAX Report Sending

Frontend Implementation

Backend JSON Response

Complete Export Workflow