easypay-api/README.md

Student Fee Payment Registration System

A streamlined 2-page PHP application that simplifies the school fee payment process by automatically allocating payments across outstanding fees from oldest to newest.

Features

• AJAX-powered student search - Find students quickly by name or student code
• Outstanding fees display - View all unpaid fees sorted from oldest to newest
• Automatic payment allocation - Payments are automatically distributed across selected fees in chronological order
• Teller validation - Automatic lookup of bank statements with unreconciled amount calculation
• Duplicate prevention - Prevents multiple payments for the same student on the same date
• Transactional integrity - All database operations wrapped in transactions with automatic rollback on error
• Comprehensive receipts - Detailed payment confirmation with all settled fees

Database Tables Used

The application interacts with the following tables in the one_arps_aci database:

• tb_student_registrations - Student information
• tb_academic_levels - Academic level details
• tb_account_school_fees - Fee definitions
• tb_account_receivables - Billed fees (invoices)
• tb_account_bank_statements - Bank transaction data
• tb_account_school_fee_payments - Individual fee payment records
• tb_account_school_fee_sum_payments - Aggregated payment records
• tb_account_student_payments - Cumulative payment tracking
• tb_account_payment_registers - Receipt records
• tb_student_logistics - Student outstanding balance tracking

Installation

Prerequisites

• PHP 7.4 or higher
• MySQL 5.7 or higher
• Web server (Apache, Nginx, or PHP built-in server)
• Existing one_arps_aci database with required tables

Setup Steps

1. Copy files to your web directory

   c:\ESD\ARPS EasyPayments\
   ├── db_config.php
   ├── index.php
   ├── process_payment.php
   └── ajax_handlers.php
   

2. Configure database connection

   Edit db_config.php and update the following constants:

   define('DB_HOST', 'localhost');     // Your MySQL host
   define('DB_USER', 'root');          // Your MySQL username
   define('DB_PASS', '');              // Your MySQL password
   

3. Set proper permissions

   Ensure the web server has read access to all PHP files.

4. Access the application

   Navigate to http://localhost/ARPS%20EasyPayments/index.php in your browser.

Usage Guide

Step 1: Search for a Student

1. Type the student's name or student code in the search box
2. Select the student from the dropdown results
3. The page will reload showing student details and outstanding fees

Step 2: Review Outstanding Fees

• All outstanding fees are displayed in a table, sorted from oldest to newest
• Fees are pre-checked by default
• You can uncheck any fees you don't want to settle
• The table shows:
  • Fee description
  • Academic session and term
  • Billed amount
  • Amount already paid
  • Outstanding balance

Step 3: Process Payment

1. Click the "Process Payment" button
2. Enter the Teller Number from the bank statement
3. The system will automatically:
   • Look up the bank statement
   • Fill in the bank narration
   • Calculate the unreconciled amount on the teller
4. Enter the Amount to Use for Fees (must not exceed unreconciled amount)
5. Click "OK PROCEED!"

Step 4: View Confirmation

• On success, you'll see a detailed receipt showing:
  • Student name and receipt number
  • Payment date and teller information
  • All fees that were settled
  • Remaining unreconciled amount on the teller
• On failure, you'll see an error message (no database changes are made)

Payment Allocation Logic

The system automatically allocates payments using the following rules:

1. Oldest First - Fees are sorted by academic session (ASC), then term (ASC)
2. Full Settlement Priority - Each fee is fully settled before moving to the next
3. Partial Settlement - If the payment amount runs out, the last fee is partially settled
4. Automatic Calculation - No manual allocation required

Example

If a student has these outstanding fees:

• 2024 Term 1: ₦10,000
• 2024 Term 2: ₦15,000
• 2024 Term 3: ₦12,000

And you process a payment of ₦30,000:

• 2024 Term 1: Fully settled (₦10,000)
• 2024 Term 2: Fully settled (₦15,000)
• 2024 Term 3: Partially settled (₦5,000)

ID Generation Rules

The application follows strict ID generation rules as per the database schema:

• transaction_id: student_id + academic_session + payment_date
• school_fee_payment_id: student_code + fee_id + payment_date
• receipt_no: student_code + payment_date
• Date format: Always YYYY-MM-DD

Error Handling

The application includes comprehensive error handling:

• Database connection failures - Clear error message displayed
• Invalid teller numbers - Validation before processing
• Duplicate payments - Prevented at database level
• Insufficient unreconciled amount - Client and server-side validation
• Transaction failures - Automatic rollback with error details

Security Features

• PDO with prepared statements - Protection against SQL injection
• Input validation - Server-side validation of all inputs
• Output sanitization - All user data is escaped before display
• Transaction integrity - All-or-nothing database operations

Browser Compatibility

• Chrome (recommended)
• Firefox
• Edge
• Safari

Troubleshooting

"Database connection failed"

• Check your database credentials in db_config.php
• Ensure MySQL server is running
• Verify the database name is one_arps_aci

"Teller number not found"

• Verify the teller number exists in tb_account_bank_statements
• Check that the teller number is the last token in the description field
• Ensure there's unreconciled amount available

"A payment for this student has already been registered on this date"

• This is a duplicate prevention feature
• You cannot process multiple payments for the same student on the same date
• Use a different payment date or check existing records

Technical Notes

Teller Number Extraction

The system extracts teller information from tb_account_bank_statements.description using this logic:

• Teller Number: Last token (rightmost word) after splitting by spaces
• Teller Name: All text before the last space

Example: "SCHOOL FEES PAYMENT 1234567890"

• Teller Number: 1234567890
• Teller Name: SCHOOL FEES PAYMENT

Unreconciled Amount Calculation

unreconciled_amount = bank_statement.amount_paid - SUM(school_fee_payments.amount_paid)

This shows how much of the bank deposit has not yet been allocated to student fees.

Support

For issues or questions, please contact the system administrator.

License

Internal use only - ARPS School Management System