<?php
function getDateSpecificAttendance($conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $date, $subject_id) {
    try {
       
        // Build the SQL query
        $sql = "SELECT s.id, s.name, s.admission_number, d.value 
                FROM students s
                LEFT JOIN student_attendance_subjectwise d ON s.id = d.student_id AND d.date = ? AND d.subject_id = ?
                WHERE s.institution_id = ? AND s.branch_id = ? AND s.class_id = ? AND s.section_id = ?";
        
        // Prepare the statement
        $stmt = $conn_rjllearning_specific->prepare($sql);
        
        // Bind parameters and execute the query
        $stmt->execute([$date, $subject_id, $institution_id, $branch_id, $class_id, $section_id]);
        
        // Fetch the results
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Transform the results into the desired format
        $formattedResult = array_map(function($item) {
            return array(
                'student_id' => $item['id'],
                'name' => $item['name'],
                'admission_number' => $item['admission_number'],
                'value' => $item['value'] ?? null // Use null if value is not set
            );
        }, $result);

        // Return the formatted result
        return $formattedResult;
    } catch (PDOException $e) {
        // Handle error and return a meaningful error message
        return "Error executing query: " . $e->getMessage();
    }
}

