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:
   ```php
   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

```sql
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