File "read_students_grouped.php"
Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/hall_tickets/read_students_grouped.php
File size: 12.76 KiB (13063 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
ob_start();
error_reporting(0);
ini_set('display_errors', '0');
ini_set('display_startup_errors', '0');
require_once '../../config/config.php';
header('Content-Type: application/json');
try {
$draw = isset($_GET['draw']) ? (int)$_GET['draw'] : 1;
$start = isset($_GET['start']) ? (int)$_GET['start'] : 0;
$length = isset($_GET['length']) ? (int)$_GET['length'] : 10;
$search = isset($_GET['search']['value']) ? trim($_GET['search']['value']) : '';
$academicYear = isset($_GET['academic_year']) ? trim($_GET['academic_year']) : '';
$courseId = isset($_GET['course_id']) && $_GET['course_id'] !== '' ? (int)$_GET['course_id'] : 0;
$examDate = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
$batchId = isset($_GET['batch_id']) && $_GET['batch_id'] !== '' ? (int)$_GET['batch_id'] : 0;
$session = isset($_GET['session']) ? trim($_GET['session']) : '';
// Build WHERE with filters (based on registrations + optional timetable for date/session)
$where = [];
$params = [];
if ($academicYear !== '') { $where[] = 'e.academic_year = ?'; $params[] = $academicYear; }
if ($courseId > 0) { $where[] = 's.course_id = ?'; $params[] = $courseId; }
if ($examDate !== '') { $where[] = 'et.exam_date = ?'; $params[] = $examDate; }
if ($session !== '') { $where[] = 'et.session = ?'; $params[] = $session; }
if ($batchId > 0) { $where[] = 'se.batch_id = ?'; $params[] = $batchId; }
// Search on student name or roll #
$searchSql = '';
if ($search !== '') {
$searchSql = ' AND (st.university_rollno LIKE ? OR st.first_name LIKE ? OR st.last_name LIKE ? OR CONCAT(st.first_name, " ", st.last_name) LIKE ?)';
$like = '%'.$search.'%';
}
$whereSql = '';
if (!empty($where)) { $whereSql = 'WHERE ' . implode(' AND ', $where); }
// Count total distinct students (without filters) for DataTables total
$totalSql = 'SELECT COUNT(DISTINCT st.student_id) FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
JOIN exams e ON es.exam_id = e.exam_id
JOIN subjects s ON es.subject_id = s.subject_id
LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
LEFT JOIN student_enrollments se ON se.student_id = st.student_id';
$total = (int)$pdo->query($totalSql)->fetchColumn();
// Count filtered
$countSql = 'SELECT COUNT(*) FROM (
SELECT DISTINCT st.student_id
FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
JOIN exams e ON es.exam_id = e.exam_id
JOIN subjects s ON es.subject_id = s.subject_id
LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
LEFT JOIN student_enrollments se ON se.student_id = st.student_id
' . $whereSql . ($search !== '' ? $searchSql : '') . '
) x';
$countStmt = $pdo->prepare($countSql);
$bindIdx = 1;
foreach ($params as $p) { $countStmt->bindValue($bindIdx++, $p); }
if ($search !== '') { $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); $countStmt->bindValue($bindIdx++, $like); }
$countStmt->execute();
$filtered = (int)$countStmt->fetchColumn();
// Fetch paginated rows with exams count
$dataSql = 'SELECT st.student_id,
st.university_rollno AS roll_number,
CONCAT(st.first_name, " ", st.last_name) AS student_name,
c.course_name,
COUNT(DISTINCT es.exam_subject_id) AS exams_count
FROM students st
JOIN student_registrations sr ON st.student_id = sr.student_id
JOIN exam_subjects es ON sr.exam_subject_id = es.exam_subject_id
JOIN exams e ON es.exam_id = e.exam_id
JOIN subjects s ON es.subject_id = s.subject_id
JOIN courses c ON s.course_id = c.course_id
LEFT JOIN exam_timetable et ON et.exam_subject_id = es.exam_subject_id
LEFT JOIN student_enrollments se ON se.student_id = st.student_id
' . $whereSql . ($search !== '' ? $searchSql : '') . '
GROUP BY st.student_id, st.university_rollno, st.first_name, st.last_name, c.course_name
ORDER BY st.university_rollno ASC
LIMIT ? OFFSET ?';
$stmt = $pdo->prepare($dataSql);
$bindIdx = 1;
foreach ($params as $p) { $stmt->bindValue($bindIdx++, $p); }
if ($search !== '') { $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); $stmt->bindValue($bindIdx++, $like); }
$stmt->bindValue($bindIdx++, $length, PDO::PARAM_INT);
$stmt->bindValue($bindIdx++, $start, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Clean any accidental output before JSON
while (ob_get_level() > 0) { ob_end_clean(); }
echo json_encode([
'draw' => $draw,
'recordsTotal' => $total,
'recordsFiltered' => $filtered,
'data' => $rows
]);
exit;
} catch (Throwable $e) {
http_response_code(500);
while (ob_get_level() > 0) { ob_end_clean(); }
echo json_encode(['draw'=>isset($_GET['draw'])?(int)$_GET['draw']:1,'recordsTotal'=>0,'recordsFiltered'=>0,'data'=>[],'error'=>$e->getMessage()]);
exit;
}
?>
<?php
require_once '../../config/config.php';
header('Content-Type: application/json');
try {
// Log the request for debugging
error_log("Hall Tickets API Request: " . json_encode($_GET));
// 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', 'exams_count'];
$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;
$examDate = isset($_GET['exam_date']) ? trim($_GET['exam_date']) : '';
$session = isset($_GET['session']) ? trim($_GET['session']) : '';
error_log("Filter parameters: academic_year=$academicYear, course_id=$courseId, exam_date=$examDate, session=$session");
// 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 ($examDate !== '') {
$where[] = 'et.exam_date = ?';
$bindings[] = $examDate;
}
if ($session !== '') {
$where[] = 'et.session = ?';
$bindings[] = $session;
}
// Add search condition
if (!empty($searchValue)) {
$where[] = '(st.first_name LIKE ? OR st.last_name LIKE ? OR st.university_rollno LIKE ? OR c.course_name LIKE ?)';
$searchParam = "%$searchValue%";
$bindings[] = $searchParam;
$bindings[] = $searchParam;
$bindings[] = $searchParam;
$bindings[] = $searchParam;
}
$whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';
// Base SQL query to get grouped students with exam counts
$sqlData = "SELECT
st.student_id,
CONCAT(st.first_name, ' ', st.last_name) as student_name,
st.university_rollno as roll_number,
c.course_name,
COUNT(DISTINCT es.exam_subject_id) as exams_count
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
GROUP BY st.student_id, st.first_name, st.last_name, st.university_rollno, c.course_name
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();
// Log the response for debugging
$response = [
"draw" => isset($_GET['draw']) ? (int)$_GET['draw'] : 0,
"recordsTotal" => (int)$totalRecords,
"recordsFiltered" => (int)$totalFilteredRecords,
"data" => $data
];
error_log("Hall Tickets API Response: " . json_encode($response));
// Return DataTables response
echo json_encode($response);
} 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;
}
?>