= 0; $i--) {
$month = date('M Y', strtotime("-{$i} months"));
$month_start = date('Y-m-01', strtotime("-{$i} months"));
$month_end = date('Y-m-t', strtotime("-{$i} months"));
$monthly_query = "SELECT COUNT(*) as total FROM students WHERE created_at BETWEEN '{$month_start}' AND '{$month_end}'";
$monthly_result = mysqli_query($conn, $monthly_query);
$count = $monthly_result ? mysqli_fetch_assoc($monthly_result)['total'] : 0;
$monthly_labels[] = $month;
$monthly_data[] = $count;
}
// ===== CERTIFICATES STATS =====
$total_certificates_query = "SELECT COUNT(*) as total FROM certificates";
$total_certificates = mysqli_fetch_assoc(mysqli_query($conn, $total_certificates_query))['total'] ?? 0;
$cert_type_query = "SELECT certificate_type, COUNT(*) as count FROM certificates GROUP BY certificate_type";
$cert_type_result = mysqli_query($conn, $cert_type_query);
$cert_type_labels = [];
$cert_type_data = [];
if ($cert_type_result) {
while ($row = mysqli_fetch_assoc($cert_type_result)) {
$cert_type_labels[] = ucfirst($row['certificate_type']);
$cert_type_data[] = $row['count'];
}
}
// ===== MARKSHEETS STATS =====
$total_marksheets_query = "SELECT COUNT(*) as total FROM marksheets";
$total_marksheets = mysqli_fetch_assoc(mysqli_query($conn, $total_marksheets_query))['total'] ?? 0;
$pass_count_query = "SELECT COUNT(*) as total FROM marksheets WHERE result = 'Pass'";
$pass_count = mysqli_fetch_assoc(mysqli_query($conn, $pass_count_query))['total'] ?? 0;
$fail_count_query = "SELECT COUNT(*) as total FROM marksheets WHERE result = 'Fail'";
$fail_count = mysqli_fetch_assoc(mysqli_query($conn, $fail_count_query))['total'] ?? 0;
// ===== FRANCHISE STATS =====
$total_franchises_query = "SELECT COUNT(*) as total FROM franchises";
$total_franchises = mysqli_fetch_assoc(mysqli_query($conn, $total_franchises_query))['total'] ?? 0;
$active_franchises_query = "SELECT COUNT(*) as total FROM franchises WHERE status = 'active'";
$active_franchises = mysqli_fetch_assoc(mysqli_query($conn, $active_franchises_query))['total'] ?? 0;
// ===== RECENT ACTIVITIES =====
$recent_query = "SELECT 'student' as type, student_name as name, 'New admission' as action, created_at
FROM students
UNION ALL
SELECT 'marksheet', student_name, 'Marksheet generated', created_at
FROM marksheets
UNION ALL
SELECT 'certificate', student_name, 'Certificate issued', created_at
FROM certificates
ORDER BY created_at DESC LIMIT 10";
$recent_result = mysqli_query($conn, $recent_query);
$recent_activities = [];
if ($recent_result) {
while ($row = mysqli_fetch_assoc($recent_result)) {
$recent_activities[] = $row;
}
}
include 'includes/header.php';
include 'includes/sidebar.php';
?>
Reports - GSOS
Total Students
+ this month