easypay-api/process_payment.php

<?php
/**
 * Process Payment
 * Handle payment allocation and database transactions
 */

require_once 'db_config.php';

// Initialize response
$success = false;
$message = '';
$paymentDetails = [];

try {
    // Validate POST data
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        throw new Exception('Invalid request method');
    }

    $studentId = $_POST['student_id'] ?? '';
    $studentCode = $_POST['student_code'] ?? '';
    $selectedFeesJson = $_POST['selected_fees'] ?? '';
    $tellerNumber = $_POST['teller_number'] ?? '';
    $bankDescription = $_POST['bank_description'] ?? '';
    $paymentDate = $_POST['payment_date'] ?? '';
    $amountToUse = floatval($_POST['amount_to_use'] ?? 0);

    // Validate required fields
    if (
        empty($studentId) || empty($studentCode) || empty($selectedFeesJson) ||
        empty($tellerNumber) || empty($paymentDate) || $amountToUse <= 0
    ) {
        throw new Exception('Missing required fields');
    }

    // Parse selected fees
    $selectedFees = json_decode($selectedFeesJson, true);
    if (!is_array($selectedFees) || count($selectedFees) === 0) {
        throw new Exception('No fees selected');
    }

    // Sort fees by academic_session ASC, term_of_session ASC (oldest to newest)
    usort($selectedFees, function ($a, $b) {
        if ($a['academic_session'] != $b['academic_session']) {
            return $a['academic_session'] - $b['academic_session'];
        }
        return $a['term_of_session'] - $b['term_of_session'];
    });

    // Re-fetch bank statement to verify
    $sql = "SELECT
                bs.id,
                bs.description,
                bs.amount_paid,
                bs.payment_date,
                COALESCE(fp.total_registered_fee, 0.00) AS registered_amount,
                (bs.amount_paid - COALESCE(fp.total_registered_fee, 0.00)) AS unreconciled_amount
            FROM tb_account_bank_statements bs
            LEFT JOIN (
                SELECT teller_no, SUM(amount_paid) AS total_registered_fee
                FROM tb_account_school_fee_payments
                GROUP BY teller_no
            ) fp ON SUBSTRING_INDEX(bs.description, ' ', -1) = fp.teller_no
            WHERE SUBSTRING_INDEX(bs.description, ' ', -1) = :teller_number
            LIMIT 1";

    $stmt = $pdo->prepare($sql);
    $stmt->execute(['teller_number' => $tellerNumber]);
    $bankStatement = $stmt->fetch();

    if (!$bankStatement) {
        throw new Exception('Bank statement not found for teller number: ' . $tellerNumber);
    }

    // Verify unreconciled amount
    if ($amountToUse > $bankStatement['unreconciled_amount']) {
        throw new Exception('Amount exceeds unreconciled amount on teller');
    }

    // Extract teller name and number from description
    $descParts = explode(' ', $bankStatement['description']);
    $tellerNo = array_pop($descParts);
    $tellerName = implode(' ', $descParts);

    // Use the oldest fee's session/term for transaction_id
    $dominantSession = $selectedFees[0]['academic_session'];
    $dominantTerm = $selectedFees[0]['term_of_session'];

    // Generate transaction_id
    $transactionId = $studentId . $dominantSession . $paymentDate;

    // STEP 1: Guard check - prevent duplicate payments on same date
    $sql = "SELECT COUNT(*) AS cnt
            FROM tb_account_school_fee_sum_payments
            WHERE student_id = :student_id
              AND payment_date = :payment_date";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        'student_id' => $studentId,
        'payment_date' => $paymentDate
    ]);

    $duplicateCheck = $stmt->fetch();
    if ($duplicateCheck['cnt'] > 0) {
        throw new Exception('A payment for this student has already been registered on this date (' . $paymentDate . ')');
    }

    // STEP 2: Allocate payment across fees (oldest to newest)
    $feeAllocations = [];
    $remainingAmount = $amountToUse;

    foreach ($selectedFees as $fee) {
        if ($remainingAmount <= 0) {
            break;
        }

        $outstandingAmount = floatval($fee['outstanding_amount']);

        if ($remainingAmount >= $outstandingAmount) {
            // Fully settle this fee
            $amountForThisFee = $outstandingAmount;
            $remainingAmount -= $outstandingAmount;
        } else {
            // Partially settle this fee
            $amountForThisFee = $remainingAmount;
            $remainingAmount = 0;
        }

        $feeAllocations[] = [
            'fee_id' => $fee['fee_id'],
            'academic_session' => $fee['academic_session'],
            'term_of_session' => $fee['term_of_session'],
            'amount' => $amountForThisFee
        ];
    }

    if (count($feeAllocations) === 0) {
        throw new Exception('No fees could be allocated');
    }

    // Calculate total paid
    $totalPaid = array_sum(array_column($feeAllocations, 'amount'));

    // STEP 3: Begin database transaction
    $pdo->beginTransaction();

    try {
        // Constants
        $creditBankId = '000001373634585148';
        $debitBankId = '514297805530965017';
        $paymodeId = '000001373901891416';
        $recipientId = 'SS0011441283890434';
        $paymentBy = 'SS0011441283890434';
        $createdBy = 'SS0011441283890434';
        $createdAs = 'school';
        $paymentStatus = 'Approved';
        $paymodeCategory = 'BANK';

        // Generate receipt_no (used across all fee records)
        $receiptNo = $studentCode . $paymentDate;

        // 3a) INSERT into tb_account_school_fee_payments (per fee)
        foreach ($feeAllocations as $allocation) {
            $schoolFeePaymentId = $studentCode . $allocation['fee_id'] . $paymentDate; //original code

            //$schoolFeePaymentId = $allocation['fee_id'] . $paymentDate; //Is used to reduce length of id to solve duplicate entry error. 

            $sql = "INSERT INTO tb_account_school_fee_payments (
                        id, fee_id, student_id, transaction_id, amount_paid,
                        teller_no, teller_name, credit_bank_id, debit_bank_id,
                        paymode_id, payment_status, payment_date, recipient_id,
                        academic_session, term_of_session, payment_by, payment_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :transaction_id, :amount_paid,
                        :teller_no, :teller_name, :credit_bank_id, :debit_bank_id,
                        :paymode_id, :payment_status, :payment_date, :recipient_id,
                        :academic_session, :term_of_session, :payment_by, NOW()
                    )";

            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                'id' => $schoolFeePaymentId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'transaction_id' => $transactionId,
                'amount_paid' => $allocation['amount'],
                'teller_no' => $tellerNo,
                'teller_name' => $tellerName,
                'credit_bank_id' => $creditBankId,
                'debit_bank_id' => $debitBankId,
                'paymode_id' => $paymodeId,
                'payment_status' => $paymentStatus,
                'payment_date' => $paymentDate,
                'recipient_id' => $recipientId,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'payment_by' => $paymentBy
            ]);
        }

        // 3b) INSERT into tb_account_school_fee_sum_payments (single record)
        $sumPaymentsId = $studentId . $dominantSession . $paymentDate;

        $sql = "INSERT INTO tb_account_school_fee_sum_payments (
                    id, student_id, total_paid, paymode_id, payment_date,
                    credit_bank_id, debit_bank_id, transaction_id, status,
                    academic_session, term_of_session, registered_by, registered_on
                ) VALUES (
                    :id, :student_id, :total_paid, :paymode_id, :payment_date,
                    :credit_bank_id, :debit_bank_id, :transaction_id, :status,
                    :academic_session, :term_of_session, :registered_by, NOW()
                )";

        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            'id' => $sumPaymentsId,
            'student_id' => $studentId,
            'total_paid' => $totalPaid,
            'paymode_id' => $paymodeId,
            'payment_date' => $paymentDate,
            'credit_bank_id' => $creditBankId,
            'debit_bank_id' => $debitBankId,
            'transaction_id' => $transactionId,
            'status' => $paymentStatus,
            'academic_session' => $dominantSession,
            'term_of_session' => $dominantTerm,
            'registered_by' => $createdBy
        ]);

        // 3c) INSERT into tb_account_student_payments (per fee)
        foreach ($feeAllocations as $allocation) {
            // Get current payment_to_date (sum of all previous payments for this fee/session/term)
            $sql = "SELECT SUM(payment_to_date) AS current_total
                    FROM tb_account_student_payments
                    WHERE student_id = :student_id
                      AND fee_id = :fee_id
                      AND academic_session = :academic_session
                      AND term_of_session = :term_of_session";

            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                'student_id' => $studentId,
                'fee_id' => $allocation['fee_id'],
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session']
            ]);

            $currentPayment = $stmt->fetch();
            $currentTotal = floatval($currentPayment['current_total'] ?? 0);
            $newPaymentToDate = $allocation['amount'];
            //$newPaymentToDate = $currentTotal + $allocation['amount']; --Old code that added new payment amount to current total (total before payment)

            $studentPaymentId = $studentCode . $allocation['fee_id'] . $paymentDate;

            $sql = "INSERT INTO tb_account_student_payments (
                        id, fee_id, student_id, payment_to_date, transaction_id,
                        academic_session, term_of_session, created_by, created_as, created_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :payment_to_date, :transaction_id,
                        :academic_session, :term_of_session, :created_by, :created_as, NOW()
                    )";

            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                'id' => $studentPaymentId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'payment_to_date' => $newPaymentToDate,
                'transaction_id' => $transactionId,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'created_by' => $createdBy,
                'created_as' => $createdAs
            ]);
        }

        // 3d) INSERT into tb_account_payment_registers (per fee)
        foreach ($feeAllocations as $allocation) {
            $paymentRegisterId = $studentCode . $allocation['fee_id'] . $paymentDate;

            $sql = "INSERT INTO tb_account_payment_registers (
                        id, fee_id, student_id, amount_paid, amount_due,
                        receipt_no, recipient_id, payment_date, paymode_category,
                        transaction_id, academic_session, term_of_session,
                        created_by, created_as, created_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :amount_paid, :amount_due,
                        :receipt_no, :recipient_id, :payment_date, :paymode_category,
                        :transaction_id, :academic_session, :term_of_session,
                        :created_by, :created_as, NOW()
                    )";

            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                'id' => $paymentRegisterId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'amount_paid' => $allocation['amount'],
                'amount_due' => $allocation['amount'],
                'receipt_no' => $receiptNo,
                'recipient_id' => $recipientId,
                'payment_date' => $paymentDate,
                'paymode_category' => $paymodeCategory,
                'transaction_id' => $transactionId,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'created_by' => $createdBy,
                'created_as' => $createdAs
            ]);
        }

        // 3e) UPDATE tb_student_logistics
        // Group allocations by session/term to update specific records
        $sessionTermTotals = [];
        foreach ($feeAllocations as $allocation) {
            $key = $allocation['academic_session'] . '-' . $allocation['term_of_session'];
            if (!isset($sessionTermTotals[$key])) {
                $sessionTermTotals[$key] = [
                    'academic_session' => $allocation['academic_session'],
                    'term_of_session' => $allocation['term_of_session'],
                    'total' => 0
                ];
            }
            $sessionTermTotals[$key]['total'] += $allocation['amount'];
        }

        // Update each session/term record
        foreach ($sessionTermTotals as $st) {
            $sql = "UPDATE tb_student_logistics
                    SET fees_outstanding = GREATEST(0, fees_outstanding - :total_paid)
                    WHERE student_id = :student_id
                      AND academic_session = :academic_session
                      AND term_of_session = :term_of_session";

            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                'total_paid' => $st['total'],
                'student_id' => $studentId,
                'academic_session' => $st['academic_session'],
                'term_of_session' => $st['term_of_session']
            ]);
        }

        // Commit transaction
        $pdo->commit();

        $success = true;
        $message = 'Payment processed successfully!';

        // Fetch fee descriptions for display
        $feeIds = array_column($feeAllocations, 'fee_id');
        $placeholders = implode(',', array_fill(0, count($feeIds), '?'));

        $sql = "SELECT id, description FROM tb_account_school_fees WHERE id IN ($placeholders)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($feeIds);
        $feeDescriptions = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

        // Prepare payment details for display
        foreach ($feeAllocations as $key => $allocation) {
            $feeAllocations[$key]['description'] = $feeDescriptions[$allocation['fee_id']] ?? 'Unknown Fee';
        }
        $paymentDetails = [
            'student_code' => $studentCode,
            'payment_date' => $paymentDate,
            'teller_no' => $tellerNo,
            'teller_name' => $tellerName,
            'total_paid' => $totalPaid,
            'receipt_no' => $receiptNo,
            'transaction_id' => $transactionId,
            'allocations' => $feeAllocations,
            'remaining_unreconciled' => $bankStatement['unreconciled_amount'] - $totalPaid
        ];

    } catch (Exception $e) {
        $pdo->rollBack();
        throw new Exception('Transaction failed: ' . $e->getMessage());
    }

} catch (Exception $e) {
    $success = false;
    $message = $e->getMessage();
}

// Fetch student name for display
$studentName = '';
if (!empty($studentId)) {
    try {
        $sql = "SELECT CONCAT(last_name, ' ', first_name, ' ', COALESCE(other_name, '')) AS full_name
                FROM tb_student_registrations
                WHERE id = :student_id";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['student_id' => $studentId]);
        $result = $stmt->fetch();
        $studentName = $result['full_name'] ?? 'Unknown Student';
    } catch (Exception $e) {
        $studentName = 'Unknown Student';
    }
}
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment Processing Result</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 20px;
            display: flex;
            align-items: center;
            justify-content: center;
        }

        .container {
            max-width: 800px;
            width: 100%;
            background: white;
            border-radius: 12px;
            box-shadow: 0 10px 40px rgba(0, 0, 0, 0.2);
            padding: 40px;
        }

        .success-icon {
            width: 80px;
            height: 80px;
            margin: 0 auto 20px;
            background: #28a745;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 48px;
        }

        .error-icon {
            width: 80px;
            height: 80px;
            margin: 0 auto 20px;
            background: #dc3545;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 48px;
        }

        h1 {
            text-align: center;
            color: #333;
            margin-bottom: 10px;
            font-size: 28px;
        }

        .message {
            text-align: center;
            color: #666;
            margin-bottom: 30px;
            font-size: 16px;
        }

        .details-section {
            background: #f8f9fa;
            padding: 20px;
            border-radius: 8px;
            margin-bottom: 20px;
        }

        .details-section h2 {
            color: #333;
            margin-bottom: 15px;
            font-size: 20px;
        }

        .detail-row {
            display: flex;
            justify-content: space-between;
            padding: 10px 0;
            border-bottom: 1px solid #ddd;
        }

        .detail-row:last-child {
            border-bottom: none;
        }

        .detail-label {
            font-weight: 600;
            color: #555;
        }

        .detail-value {
            color: #333;
            text-align: right;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }

        th,
        td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #667eea;
            color: white;
            font-weight: 600;
        }

        .amount {
            text-align: right;
            font-family: 'Courier New', monospace;
        }

        .total-row {
            font-weight: bold;
            background-color: #f0f0f0;
        }

        .btn {
            display: inline-block;
            padding: 12px 30px;
            border: none;
            border-radius: 6px;
            font-size: 16px;
            font-weight: 600;
            cursor: pointer;
            text-decoration: none;
            transition: all 0.3s;
            text-align: center;
        }

        .btn-primary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
        }

        .btn-primary:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(102, 126, 234, 0.4);
        }

        .actions {
            text-align: center;
            margin-top: 30px;
        }

        .highlight {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }

        .highlight p {
            margin: 5px 0;
        }
    </style>
</head>

<body>
    <div class="container">
        <?php if ($success): ?>
            <div class="success-icon">✓</div>
            <h1>Payment Processed Successfully!</h1>
            <p class="message"><?php echo htmlspecialchars($message); ?></p>

            <div class="highlight">
                <p><strong>Student:</strong> <?php echo htmlspecialchars($studentName); ?></p>
                <p><strong>Receipt No:</strong> <?php echo htmlspecialchars($paymentDetails['receipt_no']); ?></p>
                <p><strong>Transaction ID:</strong> <?php echo htmlspecialchars($paymentDetails['transaction_id']); ?></p>
            </div>

            <div class="details-section">
                <h2>Payment Details</h2>
                <div class="detail-row">
                    <span class="detail-label">Payment Date:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($paymentDetails['payment_date']); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Teller Number:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($paymentDetails['teller_no']); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Bank Narration:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($paymentDetails['teller_name']); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Total Amount Used:</span>
                    <span class="detail-value">₦<?php echo number_format($paymentDetails['total_paid'], 2); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Remaining Unreconciled on Teller:</span>
                    <span
                        class="detail-value">₦<?php echo number_format($paymentDetails['remaining_unreconciled'], 2); ?></span>
                </div>
            </div>

            <div class="details-section">
                <h2>Fees Settled</h2>
                <table>
                    <thead>
                        <tr>
                            <th>Fee Description</th>
                            <th width="100">Session</th>
                            <th width="80">Term</th>
                            <th width="120" class="amount">Amount Paid</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($paymentDetails['allocations'] as $allocation): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($allocation['description']); ?></td>
                                <td><?php echo htmlspecialchars($allocation['academic_session']); ?></td>
                                <td><?php echo htmlspecialchars($allocation['term_of_session']); ?></td>
                                <td class="amount">₦<?php echo number_format($allocation['amount'], 2); ?></td>
                            </tr>
                        <?php endforeach; ?>
                        <tr class="total-row">
                            <td colspan="3" style="text-align: right;">Total:</td>
                            <td class="amount">₦<?php echo number_format($paymentDetails['total_paid'], 2); ?></td>
                        </tr>
                    </tbody>
                </table>
            </div>

        <?php else: ?>
            <div class="error-icon">✗</div>
            <h1>Payment Processing Failed</h1>
            <p class="message" style="color: #dc3545;"><?php echo htmlspecialchars($message); ?></p>

            <div class="details-section">
                <h2>Error Details</h2>
                <p>The payment could not be processed. No changes were made to the database.</p>
                <p style="margin-top: 10px;"><strong>Error:</strong> <?php echo htmlspecialchars($message); ?></p>
            </div>
        <?php endif; ?>

        <div class="actions">
            <a href="index.php" class="btn btn-primary">Return to Main Page</a>
            <?php if ($success && !empty($paymentDetails['receipt_no'])): ?>
                <a href="download_receipt.php?receipt_no=<?php echo urlencode($paymentDetails['receipt_no']); ?>"
                    class="btn btn-primary" style="background: #28a745; margin-left: 10px;">Download Receipt</a>
            <?php endif; ?>
        </div>
    </div>
</body>

</html>