query("SELECT COUNT(*) as total FROM students"); $total_students = $stmt->fetch()['total'] ?? 0; // Active Students (status = 1 or 'active') $stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE status = 1 OR status = 'active'"); $active_students = $stmt->fetch()['total'] ?? 0; // New Admissions This Month $stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE MONTH(admission_date) = MONTH(CURRENT_DATE()) AND YEAR(admission_date) = YEAR(CURRENT_DATE())"); $new_admissions_month = $stmt->fetch()['total'] ?? 0; // Total Marksheets Generated $stmt = $pdo->query("SELECT COUNT(*) as total FROM marksheets"); $total_marksheets = $stmt->fetch()['total'] ?? 0; // Marksheets Today $stmt = $pdo->query("SELECT COUNT(*) as total FROM marksheets WHERE DATE(created_at) = CURDATE()"); $marksheets_today = $stmt->fetch()['total'] ?? 0; // Pending Queries $stmt = $pdo->query("SELECT COUNT(*) as total FROM queries WHERE status = 'pending'"); $pending_queries = $stmt->fetch()['total'] ?? 0; // Resolved Queries $stmt = $pdo->query("SELECT COUNT(*) as total FROM queries WHERE status = 'resolved'"); $resolved_queries = $stmt->fetch()['total'] ?? 0; // Queries Today $stmt = $pdo->query("SELECT COUNT(*) as total FROM queries WHERE DATE(created_at) = CURDATE()"); $queries_today = $stmt->fetch()['total'] ?? 0; // Certificates Issued $stmt = $pdo->query("SELECT COUNT(*) as total FROM certificates"); $certificates_issued = $stmt->fetch()['total'] ?? 0; // Certificates Today $stmt = $pdo->query("SELECT COUNT(*) as total FROM certificates WHERE DATE(issued_date) = CURDATE()"); $certificates_today = $stmt->fetch()['total'] ?? 0; // Migration Certificates $stmt = $pdo->query("SELECT COUNT(*) as total FROM certificates WHERE type = 'migration'"); $migration_issued = $stmt->fetch()['total'] ?? 0; // Students by Stream/Course $stmt = $pdo->query("SELECT course, COUNT(*) as count FROM students GROUP BY course ORDER BY count DESC LIMIT 5"); $course_distribution = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { // Fallback if tables don't exist $total_students = 0; $active_students = 0; $new_admissions_month = 0; $total_marksheets = 0; $marksheets_today = 0; $pending_queries = 0; $resolved_queries = 0; $queries_today = 0; $certificates_issued = 0; $certificates_today = 0; $migration_issued = 0; $course_distribution = []; } // ============================================ // Chart Data - Monthly Admissions // ============================================ try { $stmt = $pdo->query(" SELECT DATE_FORMAT(admission_date, '%b') as month, MONTH(admission_date) as month_num, COUNT(*) as count FROM students WHERE YEAR(admission_date) = YEAR(CURRENT_DATE()) GROUP BY MONTH(admission_date), DATE_FORMAT(admission_date, '%b') ORDER BY MONTH(admission_date) "); $monthly_data = $stmt->fetchAll(PDO::FETCH_ASSOC); $chart_labels = []; $chart_data = []; foreach ($monthly_data as $row) { $chart_labels[] = $row['month']; $chart_data[] = (int)$row['count']; } // If no data, fill with months if (empty($chart_labels)) { $chart_labels = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']; $chart_data = array_fill(0, 12, 0); $current_month = date('n'); for ($i = 0; $i < $current_month; $i++) { $chart_data[$i] = rand(50, 200); // Demo data if empty } } } catch (PDOException $e) { $chart_labels = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']; $chart_data = [650, 780, 920, 850, 1050, 1180]; } // ============================================ // Recent Activities (from database) // ============================================ try { // Union of recent activities from different tables $stmt = $pdo->query(" (SELECT 'Student' as type, CONCAT(first_name, ' ', last_name) as user_name, CONCAT('New admission - ', course) as action, admission_date as activity_time, 'user-plus' as icon, 'green' as color FROM students ORDER BY admission_date DESC LIMIT 5) UNION ALL (SELECT 'Marksheet' as type, student_name as user_name, CONCAT('Marksheet generated - ', roll_number) as action, created_at as activity_time, 'file-alt' as icon, 'blue' as color FROM marksheets ORDER BY created_at DESC LIMIT 3) UNION ALL (SELECT 'Certificate' as type, student_name as user_name, CONCAT('Certificate issued - ', certificate_type) as action, issued_date as activity_time, 'certificate' as icon, 'orange' as color FROM certificates ORDER BY issued_date DESC LIMIT 3) ORDER BY activity_time DESC LIMIT 8 "); $recent_activities = $stmt->fetchAll(PDO::FETCH_ASSOC); // Format time foreach ($recent_activities as &$activity) { $timestamp = strtotime($activity['activity_time']); $now = time(); $diff = $now - $timestamp; if ($diff < 60) { $activity['time_ago'] = 'Just now'; } elseif ($diff < 3600) { $activity['time_ago'] = floor($diff / 60) . ' min ago'; } elseif ($diff < 86400) { $activity['time_ago'] = floor($diff / 3600) . ' hours ago'; } elseif ($diff < 172800) { $activity['time_ago'] = 'Yesterday'; } else { $activity['time_ago'] = date('d M', $timestamp); } } } catch (PDOException $e) { $recent_activities = [ ['user_name' => 'System', 'action' => 'Dashboard loaded', 'time_ago' => 'Just now', 'icon' => 'sync', 'color' => 'blue'] ]; } // ============================================ // Recent Queries // ============================================ try { $stmt = $pdo->query(" SELECT id, student_name, query_type, status, created_at FROM queries ORDER BY created_at DESC LIMIT 5 "); $recent_queries = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { $recent_queries = []; } // ============================================ // Stats array for template // ============================================ $stats = [ 'total_students' => $total_students, 'active_students' => $active_students, 'new_admissions' => $new_admissions_month, 'total_marksheets' => $total_marksheets, 'pending_queries' => $pending_queries, 'resolved_queries' => $resolved_queries, 'certificates_issued' => $certificates_issued, 'migration_issued' => $migration_issued ]; $today_stats = [ 'new_students' => $new_admissions_month, 'marksheets_generated' => $marksheets_today, 'queries_received' => $queries_today, 'certificates_issued' => $certificates_today ]; // Include Header and Sidebar include 'includes/header.php'; include 'includes/sidebar.php'; ?>