<?php
include("includes/db.php");
include('session_validator.php');

// Fetch assigned subjects and classes from the database
$assignedSubjectsQuery = "SELECT class_subject_mapping.id, classes.class_name, subjects.subject_name, classes.section
                         FROM class_subject_mapping
                         INNER JOIN classes ON class_subject_mapping.class_id = classes.Class_id
                         INNER JOIN subjects ON class_subject_mapping.subject_id = subjects.id";

$assignedSubjectsResult = $conn->query($assignedSubjectsQuery);
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <!-- Meta tags, title, and stylesheets can be added here -->
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>SRMS Admin </title>
        <link rel="stylesheet" href="css/bootstrap.min.css" media="screen" >
        <link rel="stylesheet" href="css/font-awesome.min.css" media="screen" >
        <link rel="stylesheet" href="css/animate-css/animate.min.css" media="screen" >
        <link rel="stylesheet" href="css/lobipanel/lobipanel.min.css" media="screen" >
        <link rel="stylesheet" href="css/prism/prism.css" media="screen" >
        <link rel="stylesheet" href="css/select2/select2.min.css" >
        <link rel="stylesheet" href="css/main.css" media="screen" >
        <script src="js/modernizr/modernizr.min.js"></script>
    <!-- Include jQuery -->
    <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>

    <!-- Include SweetAlert -->
    <script src="js\sweetalret.js"></script>


<body class="top-navbar-fixed">
        <div class="main-wrapper">

            <!-- ========== TOP NAVBAR ========== -->
  <?php  include('includes/topbar.php');?> 
            <!-- ========== WRAPPER FOR BOTH SIDEBARS & MAIN CONTENT ========== -->
            <div class="content-wrapper">
                <div class="content-container">

                    <!-- ========== LEFT SIDEBAR ========== -->
                   <?php include('includes/leftbar.php');?>  
                    <!-- /.left-sidebar -->

                    <div class="main-page">

                     <div class="container-fluid">
                            <div class="row page-title-div">
                                <div class="col-md-6">
                                    <h2 class="title">Select to display data</h2>
                                
                                </div>
                                
                                <!-- /.col-md-6 text-right -->
                            </div>
                            <!-- /.row -->
                            <div class="row breadcrumb-div">
                                <div class="col-md-6">
                                    <ul class="breadcrumb">
                                        <li><a href="dashboard.php"><i class="fa fa-home"></i> Home</a></li>
                                        <li> Report </li>
                                        <li class="">Student list</li>
                                    </ul>
                                </div>
                             
                            </div>
                            <!-- /.row -->
                        </div>
                        <div class="container-fluid">
                           
                        <div class="row">
                                    <div class="col-md-12">
                                        <div class="panel">
                                            <div class="panel-heading">
                                                <div class="panel-title">
                                                    <h5>Select field to display</h5>
                                                </div>
                                            </div>
                                            <div class="panel-body">
<!-- Form to select exam type, class, subject, and enter specific marks -->
<form action="" method="GET">
    
            <div class="form-row">
                <div class="form-group col-md-3">
                    <label for="exam_id1">Select Exam:</label>
                    <select id="exam_id1" name="exam_id1" class="form-control" required>
                        <option value="">Select Exam</option>
                        <?php
                        $queryexam = "SELECT id, exm_year, exam_type FROM examination_type";
                        $resultexam = $conn->query($queryexam);

                        while ($row = $resultexam->fetch_assoc()) {
                            echo "<option value='" . $row['id'] . "'>" . $row['id'] . "-" . $row['exam_type'] . " - " . $row['exm_year'] . "</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="form-group col-md-3">
                    <label for="class_id">Select Class:</label>
                    <select id="class_id" name="class_id" class="form-control" required>
                        <option value="">Select Class</option>
                        <?php
                        $query = "SELECT class_id, class_name, section FROM classes";
                        $result = $conn->query($query);

                        while ($row = $result->fetch_assoc()) {
                            echo "<option value='" . $row['class_id'] . "'>" . $row['class_name'] . " - " . $row['section'] . "</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="form-group col-md-4">
                    <label for="subject_id">Select Subject:</label>
                    <select id="subject_id" name="subject_id" class="form-control" >
                        <option value="">Select Subject</option>
                    </select>
                </div>
    <label for="specific_mark">Enter Specific Mark:</label>
    <input type="number" name="specific_mark" id="specific_mark" required><br><br>

    <button type="submit">Submit</button>
</form>

<!-- Placeholder for displaying filtered ledger data -->
<div id="filtered-ledger-data"></div>

<!-- Include necessary scripts -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
<script>
    $(document).ready(function() {
        // Initialize DataTable for the filtered ledger table
        $('#filtered-ledger-table').DataTable();
    });
</script>
</div>
                                            </div>
                                        </div>
                                    </div>
                                    <!-- /.col-md-12 -->
                                </div>
 
     <div class="panel">
     <div class="panel-body">
<?php
// Include database connection file
include("includes/db.php");

// Validate input and retrieve data
if (isset($_GET['class_id'], $_GET['subject_id'], $_GET['exam_id1'], $_GET['specific_mark'])) {
    // Sanitize input
    $classId = mysqli_real_escape_string($conn, $_GET['class_id']);
    $subjectId = mysqli_real_escape_string($conn, $_GET['subject_id']);
    $examId = mysqli_real_escape_string($conn, $_GET['exam_id1']);
    $specificMark = mysqli_real_escape_string($conn, $_GET['specific_mark']);

    // Fetch student data from marks table based on the provided parameters
    $query = "SELECT students.name, students.symbol_no, marks.marks
              FROM students
              INNER JOIN marks ON students.student_id = marks.student_id
              WHERE marks.class_id = '$classId'
              AND marks.subject_id = '$subjectId'
              AND marks.exam_type = '$examId'
              AND marks.marks < '$specificMark'";
    
    $result = $conn->query($query);

    if ($result && $result->num_rows > 0) {
        // Output the filtered ledger table
        echo '<table id="filtered-ledger-table" class="display" style="width:100%;">';
        echo '<thead>';
        echo '<tr>';
        echo '<th>Name</th>';
        echo '<th>Symbol No</th>';
        echo '<th>' . $_GET['subject_id'] . ' Marks</th>';
        echo '</tr>';
        echo '</thead>';
        echo '<tbody>';

        // Output each student's data in a table row
        while ($row = $result->fetch_assoc()) {
            echo '<tr>';
            echo '<td>' . $row['name'] . '</td>';
            echo '<td>' . $row['symbol_no'] . '</td>';
            echo '<td>' . $row['marks'] . '</td>';
            echo '</tr>';
        }

        echo '</tbody>';
        echo '</table>';
    } else {
        echo '<p>No students found with marks less than ' . $specificMark . ' for the selected criteria.</p>';
    }
} else {
    // Handle case where required parameters are missing
    echo '<p>Select above field to get data</p>';
}

// Close database connection
$conn->close();
?>
</div> </div>

        </div>     </div>
                </div>
                <!-- /.content-container -->
            </div>
            <!-- /.content-wrapper -->


<script src="js/jquery/jquery-2.2.4.min.js"></script>
        <script src="js/bootstrap/bootstrap.min.js"></script>
        <script src="js/pace/pace.min.js"></script>
        <script src="js/lobipanel/lobipanel.min.js"></script>
        <script src="js/iscroll/iscroll.js"></script>
        <script src="js/prism/prism.js"></script>
        <script src="js/main.js"></script>
        <script src="js/modernizr/modernizr.min.js"></script>

        
        <script>
            $(document).ready(function () {
                // Function to fetch and populate students based on class and subject
                function fetchStudents(classId, subjectId) {
                    $.ajax({
                        type: 'POST',
                        url: 'get_students.php',
                        data: { classId: classId, subjectId: subjectId },
                        success: function (response) {
                            $('#student-marks-list').html(response);
                        },
                        error: function (xhr, status, error) {
                            console.error('Error fetching students:', error);
                        }
                    });
                }

                // Event handler for class selection
                $('#class_id').change(function () {
                    var classId = $(this).val();
                    var subjectId = $('#subject_id').val();

                    // Fetch and populate students based on class and subject
                    fetchStudents(classId, subjectId);
                });

                // Event handler for subject selection
                $('#subject_id').change(function () {
                    var subjectId = $(this).val();
                    var classId = $('#class_id').val();

                    // Fetch and populate students based on class and subject
                    fetchStudents(classId, subjectId);
                });
            });
        </script>
        <script>
            document.getElementById("class_id").addEventListener("change", function () {
                var classId = this.value;
                var subjectDropdown = document.getElementById("subject_id");

                subjectDropdown.innerHTML = "<option value=''>Select Subject</option>";

                fetch(`getSubjects.php?class_id=${classId}`)
                    .then(response => response.json())
                    .then(data => {
                        data.forEach(subject => {
                            var option = document.createElement("option");
                            option.value = subject.id;
                            option.textContent = subject.subject_name;
                            subjectDropdown.appendChild(option);
                        });
                    })
                    .catch(error => {
                        console.error('Error:', error);
                    });
            });
        </script>
    </body>

</html>