File "read.php"
Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/hall_tickets/read.php
File size: 7.24 KiB (7416 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
require_once '../../config/config.php';
header('Content-Type: application/json');
try {
// Get the query parameters sent by DataTables
$limit = isset($_GET['length']) ? (int)$_GET['length'] : 10;
$offset = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$orderColumnIndex = isset($_GET['order'][0]['column']) ? (int)$_GET['order'][0]['column'] : 0;
$orderDir = isset($_GET['order'][0]['dir']) && in_array(strtolower($_GET['order'][0]['dir']), ['asc', 'desc'])
? $_GET['order'][0]['dir']
: 'asc';
$searchValue = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';
// Map column indices to actual database columns
$columns = ['student_id', 'student_name', 'roll_number', 'course_name', 'subject_code', 'exam_date', 'session', 'hall_name', 'seat_number', 'academic_year'];
$orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'student_id';
// Filter parameters
$academicYear = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
$courseId = isset($_GET['course_id']) ? (int)$_GET['course_id'] : 0;
$hallId = isset($_GET['hall_id']) ? (int)$_GET['hall_id'] : 0;
$examDate = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
$session = isset($_GET['session']) ? trim($_GET['session']) : '';
$subjectId = isset($_GET['subject_id']) ? (int)$_GET['subject_id'] : 0;
// Build WHERE clause
$where = [];
$bindings = [];
// Add filter conditions
if ($academicYear !== '') {
$where[] = 'e.academic_year = ?';
$bindings[] = $academicYear;
}
if ($courseId > 0) {
$where[] = 's.course_id = ?';
$bindings[] = $courseId;
}
if ($hallId > 0) {
$where[] = 'h.hall_id = ?';
$bindings[] = $hallId;
}
if ($examDate !== '') {
$where[] = 'et.exam_date = ?';
$bindings[] = $examDate;
}
if ($session !== '') {
$where[] = 'et.session = ?';
$bindings[] = $session;
}
if ($subjectId > 0) {
$where[] = 's.subject_id = ?';
$bindings[] = $subjectId;
}
// Add search condition
if (!empty($searchValue)) {
$where[] = '(st.first_name LIKE ? OR st.last_name LIKE ? OR st.university_rollno LIKE ? OR s.subject_name LIKE ? OR h.hall_name LIKE ?)';
$searchParam = "%$searchValue%";
$bindings[] = $searchParam;
$bindings[] = $searchParam;
$bindings[] = $searchParam;
$bindings[] = $searchParam;
$bindings[] = $searchParam;
}
$whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';
// Base SQL query to get students with hall allocation details
$sqlData = "SELECT DISTINCT
st.student_id,
CONCAT(st.first_name, ' ', st.last_name) as student_name,
st.university_rollno as roll_number,
c.course_name,
s.subject_id,
s.subject_code,
s.subject_name,
et.exam_date,
et.session,
h.hall_name,
ss.seat_number,
e.academic_year,
es.exam_subject_id
FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN student_seating ss ON sr.registration_id = ss.registration_id
JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
JOIN halls h ON ha.hall_id = h.hall_id
JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
JOIN subjects s ON es.subject_id = s.subject_id
JOIN courses c ON s.course_id = c.course_id
JOIN exams e ON es.exam_id = e.exam_id
$whereClause
ORDER BY $orderBy $orderDir
LIMIT ? OFFSET ?";
$bindings[] = $limit;
$bindings[] = $offset;
// Prepare and execute the query
$stmt = $pdo->prepare($sqlData);
foreach ($bindings as $index => $value) {
$stmt->bindValue($index + 1, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Total records count (without filtering)
$sqlTotal = "SELECT COUNT(DISTINCT st.student_id) as total
FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN student_seating ss ON sr.registration_id = ss.registration_id
JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
JOIN halls h ON ha.hall_id = h.hall_id
JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
JOIN subjects s ON es.subject_id = s.subject_id
JOIN courses c ON s.course_id = c.course_id
JOIN exams e ON es.exam_id = e.exam_id";
$totalRecords = $pdo->query($sqlTotal)->fetchColumn();
// Total filtered records count (with filtering)
$sqlFiltered = "SELECT COUNT(DISTINCT st.student_id) as total
FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN student_seating ss ON sr.registration_id = ss.registration_id
JOIN hall_allocations ha ON ss.allocation_id = ha.allocation_id
JOIN exam_timetable et ON ha.timetable_id = et.timetable_id
JOIN halls h ON ha.hall_id = h.hall_id
JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
JOIN subjects s ON es.subject_id = s.subject_id
JOIN courses c ON s.course_id = c.course_id
JOIN exams e ON es.exam_id = e.exam_id
$whereClause";
$stmtFiltered = $pdo->prepare($sqlFiltered);
// Remove limit and offset from bindings for filtered count
$filterBindings = array_slice($bindings, 0, -2);
foreach ($filterBindings as $index => $value) {
$stmtFiltered->bindValue($index + 1, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
}
$stmtFiltered->execute();
$totalFilteredRecords = $stmtFiltered->fetchColumn();
// Return DataTables response
echo json_encode([
"draw" => isset($_GET['draw']) ? (int)$_GET['draw'] : 0,
"recordsTotal" => (int)$totalRecords,
"recordsFiltered" => (int)$totalFilteredRecords,
"data" => $data
]);
} catch (PDOException $e) {
http_response_code(500);
echo json_encode(["error" => "Database error: " . $e->getMessage()]);
error_log("Database error: " . $e->getMessage());
exit;
} catch (Exception $e) {
http_response_code(500);
echo json_encode(["error" => "An unexpected error occurred: " . $e->getMessage()]);
error_log("Error: " . $e->getMessage());
exit;
}
?>