prepare("SELECT COUNT(*) as total FROM call_tracking WHERE DATE(call_start_time) = ?");
$stmt_calls_today->execute([$today]);
$total_calls_today = $stmt_calls_today->fetch()['total'];
$stmt_calls_week = $pdo->prepare("SELECT COUNT(*) as total FROM call_tracking WHERE call_start_time >= ?");
$stmt_calls_week->execute([$this_week_start]);
$total_calls_week = $stmt_calls_week->fetch()['total'];
$stmt_calls_month = $pdo->prepare("SELECT COUNT(*) as total FROM call_tracking WHERE call_start_time >= ?");
$stmt_calls_month->execute([$this_month_start]);
$total_calls_month = $stmt_calls_month->fetch()['total'];
// Calls by source (for pie chart)
$stmt_calls_by_source = $pdo->query("
SELECT traffic_source, COUNT(*) as count
FROM call_tracking
GROUP BY traffic_source
ORDER BY count DESC
");
$calls_by_source = $stmt_calls_by_source->fetchAll();
$chart_labels_source = json_encode(array_column($calls_by_source, 'traffic_source'));
$chart_data_source = json_encode(array_column($calls_by_source, 'count'));
// AI Rescue Rate
$stmt_ai_rescue = $pdo->query("
SELECT
(SUM(CASE WHEN was_ai_rescue = 1 THEN 1 ELSE 0 END) / COUNT(*)) * 100 as rescue_rate
FROM call_tracking
WHERE call_status = 'missed' OR answered_by != 'human'
");
$ai_rescue_rate = $stmt_ai_rescue->fetchColumn() ?? 0;
// Recent calls
$stmt_recent_calls = $pdo->query('SELECT * FROM call_tracking ORDER BY call_start_time DESC LIMIT 10');
$recent_calls = $stmt_recent_calls->fetchAll();
} catch (PDOException $e) {
$error = "Database error: " . $e->getMessage();
}
$project_name = "HVAC Command Center";
$page_title = "Call Tracking";
?>
<?= htmlspecialchars($page_title) ?> | <?= htmlspecialchars($project_name) ?>
| Caller | Source | Status | Answered By | Duration | Date |
|---|
|
|
|
|
|
|
| No calls recorded yet. |