query("SELECT id, full_name FROM students ORDER BY full_name")->fetchAll();
// دوال المساعدة
function getTotalCoursePrice($conn, $student_id, $from = '', $to = '') {
$sql = "SELECT SUM(c.price) FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE e.student_id = ?";
$params = [$student_id];
if ($from && $to) {
$sql .= " AND DATE(e.enrollment_date) BETWEEN ? AND ?";
$params[] = $from; $params[] = $to;
}
$stmt = $conn->prepare($sql);
$stmt->execute($params);
return $stmt->fetchColumn() ?? 0;
}
function getTotalPayments($conn, $student_id) {
$stmt = $conn->prepare("SELECT SUM(amount_paid) FROM payments WHERE student_id = ?");
$stmt->execute([$student_id]);
return $stmt->fetchColumn() ?? 0;
}
function getTotalDiscount($conn, $student_id) {
$stmt = $conn->prepare("SELECT SUM(discount) FROM payments WHERE student_id = ?");
$stmt->execute([$student_id]);
return $stmt->fetchColumn() ?? 0;
}
function getStudentCourses($conn, $student_id, $from = '', $to = '') {
$sql = "SELECT c.course_name, c.price, e.enrollment_date FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE e.student_id = ?";
$params = [$student_id];
if ($from && $to) {
$sql .= " AND DATE(e.enrollment_date) BETWEEN ? AND ?";
$params[] = $from; $params[] = $to;
}
$sql .= " ORDER BY e.enrollment_date DESC";
$stmt = $conn->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
$processed_students = [];
$total_remaining_all = 0;
foreach ($students as $student) {
$totalPrice = getTotalCoursePrice($conn, $student['id'], $from_date, $to_date);
$totalPaid = getTotalPayments($conn, $student['id']);
$totalDiscount = getTotalDiscount($conn, $student['id']);
$remaining = $totalPrice - ($totalPaid + $totalDiscount);
$student['total_price'] = $totalPrice;
$student['total_paid'] = $totalPaid;
$student['total_discount'] = $totalDiscount;
$student['remaining'] = $remaining;
$student['courses'] = getStudentCourses($conn, $student['id'], $from_date, $to_date);
$processed_students[] = $student;
$total_remaining_all += ($remaining > 0 ? $remaining : 0);
}
?>