query("SELECT id, full_name, phone 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();
}
$students_with_remaining = [];
$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);
if ($remaining > 0.05) { // تصفية الديون الحقيقية فقط
$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);
$students_with_remaining[] = $student;
$total_remaining_all += $remaining;
}
}
?>