prepare($sql);
$stmt->execute(['student_id' => $studentId]);
$studentData = $stmt->fetch();
if ($studentData) {
// Fetch outstanding fees per fee_id
$sql = "SELECT
ar.id AS receivable_id,
ar.fee_id,
asf.description AS fee_description,
ar.academic_session,
ar.term_of_session,
ar.actual_value AS billed_amount,
COALESCE(asp.total_paid_for_period, 0) AS total_paid, -- Renamed column for clarity
(ar.actual_value - COALESCE(asp.total_paid_for_period, 0)) AS outstanding_amount,
ar.created_on
FROM
tb_account_receivables ar
JOIN
tb_account_school_fees asf ON asf.id = ar.fee_id
LEFT JOIN (
-- Subquery now calculates total payments specific to a session, term, and fee
SELECT
fee_id,
student_id,
academic_session,
term_of_session,
SUM(payment_to_date) AS total_paid_for_period
FROM
tb_account_student_payments
GROUP BY
fee_id,
student_id,
academic_session,
term_of_session
) asp ON asp.fee_id = ar.fee_id
AND asp.student_id = ar.student_id
AND asp.academic_session = ar.academic_session
AND asp.term_of_session = ar.term_of_session
WHERE
ar.student_id = :student_id
AND ar.academic_session > 2023
-- Only show records where the calculated outstanding amount is greater than zero
AND (ar.actual_value - COALESCE(asp.total_paid_for_period, 0)) > 0
ORDER BY
ar.academic_session ASC, ar.term_of_session ASC, ar.created_on ASC";
$stmt = $pdo->prepare($sql);
$stmt->execute(['student_id' => $studentId]);
$outstandingFees = $stmt->fetchAll();
}
} catch (PDOException $e) {
$error = "Error fetching student data: " . $e->getMessage();
}
}
?>
Student Fee Payment Registration