Spaces:
Running
Running
File size: 3,527 Bytes
e31284f | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | <?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);
}
|