File "read_students.php"

Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/hall_tickets/read_students.php
File size: 6.46 KiB (6610 bytes)
MIME-type: text/x-php
Charset: utf-8

Download   Open   Back

<?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']) : '';

    // Get timetable_id parameter
    $timetableId = isset($_GET['timetable_id']) ? (int)$_GET['timetable_id'] : 0;
    
    if ($timetableId <= 0) {
        echo json_encode([
            "draw" => isset($_GET['draw']) ? (int)$_GET['draw'] : 0,
            "recordsTotal" => 0,
            "recordsFiltered" => 0,
            "data" => []
        ]);
        exit;
    }

    // Map column indices to actual database columns
    $columns = ['student_id', 'student_name', 'roll_number', 'course_name', 'subject_code', 'hall_name', 'seat_number'];
    $orderBy = isset($columns[$orderColumnIndex]) ? $columns[$orderColumnIndex] : 'student_id';

    // Build WHERE clause
    $where = ['ha.timetable_id = ?'];
    $bindings = [$timetableId];

    // 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 ' . implode(' AND ', $where);

    // Base SQL query to get students for specific timetable
    $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,
                       h.hall_name,
                       ss.seat_number,
                       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
                 WHERE ha.timetable_id = ?";
    $totalRecords = $pdo->prepare($sqlTotal);
    $totalRecords->execute([$timetableId]);
    $totalRecords = $totalRecords->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;
}
?>