easypay-api/ajax_handlers.php

<?php
/**
 * AJAX Handlers
 * Endpoints for student search and teller lookup
 */

require_once 'db_config.php';

header('Content-Type: application/json');

$action = $_GET['action'] ?? '';

try {
    switch ($action) {
        case 'search_students':
            searchStudents($pdo);
            break;

        case 'lookup_teller':
            lookupTeller($pdo);
            break;

        default:
            echo json_encode(['error' => 'Invalid action']);
    }
} catch (Exception $e) {
    echo json_encode(['error' => $e->getMessage()]);
}

/**
 * Search for students by name or student code
 */
function searchStudents($pdo)
{
    $search = $_GET['search'] ?? '';

    if (strlen($search) < 2) {
        echo json_encode([]);
        return;
    }

    $sql = "SELECT 
                id,
                student_code,
                CONCAT(last_name, ' ', first_name, ' ', COALESCE(other_name, '')) AS full_name
            FROM tb_student_registrations
            WHERE 
		admission_status = 'Active' -- New condition added here
    		AND (
        	    student_code LIKE :search1
	            OR last_name LIKE :search2
        	    OR first_name LIKE :search3
        	    OR other_name LIKE :search4
      		    OR CONCAT(last_name, ' ', first_name, ' ', COALESCE(other_name, '')) LIKE :search5
    		)
            ORDER BY last_name, first_name
            LIMIT 20";

    $stmt = $pdo->prepare($sql);
    $searchParam = '%' . $search . '%';
    $stmt->execute([
        'search1' => $searchParam,
        'search2' => $searchParam,
        'search3' => $searchParam,
        'search4' => $searchParam,
        'search5' => $searchParam
    ]);

    $results = $stmt->fetchAll();
    echo json_encode($results);
}

/**
 * Lookup bank statement by teller number
 */
function lookupTeller($pdo)
{
    $tellerNumber = $_GET['teller_number'] ?? '';

    if (empty($tellerNumber)) {
        echo json_encode(['error' => 'Teller number is required']);
        return;
    }

    // Query to find bank statement and calculate unreconciled amount
    $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
            HAVING unreconciled_amount >= 0
            LIMIT 1";

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

    $result = $stmt->fetch();

    if (!$result) {
        echo json_encode(['error' => 'Teller number not found or fully reconciled']);
        return;
    }

    // Extract teller name (description without last token)
    $descParts = explode(' ', $result['description']);
    array_pop($descParts); // Remove teller number
    $tellerName = implode(' ', $descParts);

    $result['teller_name'] = $tellerName;
    $result['teller_no'] = $tellerNumber;

    echo json_encode($result);
}