<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
//require_once('source/restrict_admin.php');

if (isset($_GET['year']) && isset($_GET['term']) && isset($_GET['class'])) {
    $year = $_GET['year'];
    $term = $_GET['term'];
    $class = $_GET['class'];
} else {
    // Set default values or handle the case when parameters are not provided
    $year = '2023'; // Replace this with actual default value
    $term = '1st Term'; // Replace this with actual default value
    $class = 'SS1'; // Replace this with actual default value 
}

// Fetch all subjects for the given year, term, and class
$subjectsQuery = "SELECT DISTINCT subject 
                  FROM tbl_student_results 
                  WHERE year = ? AND term = ? AND class = ?";
$subjectsStmt = $DBH->prepare($subjectsQuery);
$subjectsStmt->execute([$year, $term, $class]);
$subjects = $subjectsStmt->fetchAll(PDO::FETCH_COLUMN); 

// Fetch students with their totals and average for the given year, term, and class
$studentsQuery = "SELECT DISTINCT registration_no, name,
                        SUM(total) AS total,
                        COUNT(subject) AS total_subjects,
                        ROUND(AVG(total), 2) AS average
                  FROM tbl_student_results
                  WHERE year = ? AND term = ? AND class = ?
                  GROUP BY registration_no, name
                  ORDER BY average DESC";
$studentsStmt = $DBH->prepare($studentsQuery);
$studentsStmt->execute([$year, $term, $class]);
$students = $studentsStmt->fetchAll(PDO::FETCH_ASSOC); 

// Fetch distinct classes from the database
$classesQuery = "SELECT DISTINCT class FROM tbl_student_results";
$classesStmt = $DBH->query($classesQuery);
$classes = $classesStmt->fetchAll(PDO::FETCH_ASSOC);

// Fetch distinct years from the database
$yearsQuery = "SELECT DISTINCT year FROM tbl_student_results";
$yearsStmt = $DBH->query($yearsQuery);
$years = $yearsStmt->fetchAll(PDO::FETCH_ASSOC);

// Fetch distinct terms from the database
$termsQuery = "SELECT DISTINCT term FROM tbl_student_results";
$termsStmt = $DBH->query($termsQuery);
$terms = $termsStmt->fetchAll(PDO::FETCH_ASSOC);


?>

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

<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="">
    <title>LAHS: Teacher Dashboard</title>
    <!-- Bootstrap core CSS-->
    <link href="vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <!-- Custom fonts for this template-->
    <link href="vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css">
    <!-- Page level plugin CSS-->
    <link href="vendor/datatables/dataTables.bootstrap4.css" rel="stylesheet">
    <!-- Custom styles for this template-->
    <link href="css/sb-admin.css" rel="stylesheet">
    <link href="css/folder.css" rel="stylesheet">
    <link href="css/user_profile.css" rel="stylesheet">

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <!-- select-->
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">


    <style>
        div.a {
            white-space: nowrap;
            width: 100px;
            overflow: hidden;
            text-overflow: ellipsis;
            border: 0px solid #000000;
            padding-left: 25px;
        }
        
            /* Add this CSS to rotate table headers */
        th.vertical-header {
            writing-mode: vertical-rl;
            transform: rotate(180deg);
            white-space: nowrap;
        }

        div.a:hover {
            overflow: visible;
        }

        .style1 {
            color: #FFFFFF
        }

        .Psychomotor {
            color: #F00;
        }

        .Psychomotor {
            color: #F00;
        }

        Assessment Title {
            color: #F00;
        }

        Assessment Title {
            color: #F00;
        }

        #page-top .content-wrapper .container-fluid .row .col-lg-12 div b center {
            color: #F00;
        }
    </style>
</head>

<body class="fixed-nav sticky-footer bg-dark" id="page-top">
    <!-- Navigation-->
<body class="fixed-nav sticky-footer bg-dark" id="page-top">
  <!-- Navigation-->
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark fixed-top" id="mainNav">
<h4 style='color: #f9ecf9'><b>LAHS: TEACHER DASHBOARD </b></h4>  
<?php
            	
            	$rows = sql($DBH, "SELECT * FROM tbl_admin where status = ?", 
            	array("Active"), "rows");							
            	foreach($rows as $row)
            	{
            		$admin_name 		= $row['username'];
            	}
		
      if (isset($_SESSION['SESS_HASH_ADMIN'])) {
		$user_true 			 = "<h4 style='color: yellow'><!--<b>SCORE SHEET--></b></h4>";
		
}
		echo "$user_true ";
?>
    <button class="navbar-toggler navbar-toggler-right" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
     <div class="collapse navbar-collapse" id="navbarResponsive">
<ul class="navbar-nav navbar-sidenav" id="exampleAccordion">
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Dashboard">
    <a class="nav-link" href="main.php">
      <i class="fa fa-dashboard" style="color: #3498db"></i>
      <span class="nav-link-text" style="color: white">Teacher Dashboard</span>
    </a>
  </li>
  <!--<li class="nav-item" data-toggle="tooltip" data-placement="right" title="Generate Score Sheet">
    <a class="nav-link" href="students.php">
      <i class="fa fa-clock-o" style="color: #3498db"></i>
      <span class="nav-link-text" style="color: white">Generate Score Sheet</span>
    </a>
  </li> -->
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Download Broadsheet">
    <a class="nav-link" href="download_broadsheet.php">
      <i class="fa fa-download" style="color: blue"></i>
      <span class="nav-link-text" style="color: white">Download Broadsheet</span>
    </a>
  </li>
  <!--<li class="nav-item" data-toggle="tooltip" data-placement="right" title="Fill Attendance Sheet">
    <a class="nav-link" href="fill_attendance_sheet.php">
      <i class="fa fa-download" style="color: red"></i>
      <span class="nav-link-text" style="color: white">Download Specimen Excel Sheets</span>
    </a>
  </li> -->
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Upload Tests (CA 1, C.A.2, EXAM)">
    <a class="nav-link" href="upload_ca1.php">
      <i class="fa fa-upload" style="color: orange"></i>
      <span class="nav-link-text" style="color: white">Upload Tests (CA 1, CA 2, EXAM)</span>
    </a>
  </li>
    <!--<li class="nav-item" data-toggle="tooltip" data-placement="right" title="Upload Tests (CA 2)">
    <a class="nav-link" href="upload_ca2.php">
      <i class="fa fa-upload" style="color: orange"></i>
      <span class="nav-link-text" style="color: white">Upload Tests (CA 2)</span>
    </a>
  </li>
    <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Upload Tests (CA 3)">
    <a class="nav-link" href="upload_ca3.php">
      <i class="fa fa-upload" style="color: orange"></i>
      <span class="nav-link-text" style="color: white">Upload Tests (CA 3)</span>
    </a>
  
    <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Upload Exams">
    <a class="nav-link" href="upload_exam.php">
      <i class="fa fa-upload" style="color: orange"></i>
      <span class="nav-link-text" style="color: white">Upload Exam Scores</span>
    </a></li>-->
  </li>
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Fill Attendance Sheet">
    <a class="nav-link" href="upload_attendance.php">
      <i class="fa fa-upload" style="color: red"></i>
      <span class="nav-link-text" style="color: white">Upload Attendance Scores</span>
    </a>
  </li>
  <!--<li class="nav-item" data-toggle="tooltip" data-placement="right" title="Fill Attendance Sheet">
    <a class="nav-link" href="fill_attendance_sheet.php">
      <i class="fa fa-upload" style="color: red"></i>
      <span class="nav-link-text" style="color: white">Upload Exam Scores</span>
    </a>
  </li> -->
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Upload Skills, Psychomotor, and others">
    <a class="nav-link" href="upload_skills.php">
      <i class="fa fa-upload" style="color: purple"></i>
      <span class="nav-link-text" style="color: white">Upload Skills, Psychomotor, and others</span>
    </a>
  </li>
  <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Logout">
    <a class="nav-link" href="logout.php">
      <i class="fa fa-sign-out" style="color: #3498db"></i>
      <span class="nav-link-text" style="color: white">Logout</span>
    </a>
  </li>
</ul>
    </div>
  </nav>

    <div class="content-wrapper">
        <div class="container-fluid">
            <!-- Search Form -->
            <div class="row mb-3">
                <div class="col-lg-12">
                    <form method="GET">
                        <div class="form-row">
                            <div class="form-group col-md-3">
                                <label for="class">Class</label>
                                <select class="form-control" name="class" id="class">
                                    <?php foreach ($classes as $classItem) : ?>
                                        <option value="<?php echo $classItem['class']; ?>" <?php echo ($class == $classItem['class']) ? 'selected' : ''; ?>><?php echo $classItem['class']; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="form-group col-md-3">
                                <label for="year">Year</label>
                                <select class="form-control" name="year" id="year">
                                    <?php foreach ($years as $yearItem) : ?>
                                        <option value="<?php echo $yearItem['year']; ?>" <?php echo ($year == $yearItem['year']) ? 'selected' : ''; ?>><?php echo $yearItem['year']; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="form-group col-md-3">
                                <label for="term">Term</label>
                                <select class="form-control" name="term" id="term">
                                    <?php foreach ($terms as $termItem) : ?>
                                        <option value="<?php echo $termItem['term']; ?>" <?php echo ($term == $termItem['term']) ? 'selected' : ''; ?>><?php echo $termItem['term']; ?></option>
                                    <?php endforeach; ?>
                                </select>
                            </div>
                            <div class="form-group col-md-3">
                                <label>&nbsp;</label>
                                <button type="submit" class="btn btn-primary form-control" name="submit">Search</button>
                            </div>
                        </div>
                    </form>

                </div>
            </div>

<div id="table-container">
            <!-- Table Structure -->
              <!-- Add the table title -->
            <h3>Broadsheet for: <?php echo "$term, $class, $year"; ?></h3>
            <!-- Add the summary information -->
            <h4><div>Total Subjects: <?php echo count($subjects); ?></div></h4>
            <h4><div>Total Students: <?php echo count($students); ?></div></h4>

            
<table border='1'>
    <tr>
        <th>Name</th>
        <th>Admission No</th>

        <?php foreach ($subjects as $subject) : ?>
            <th class="vertical-header"><?php echo $subject; ?></th>
        <?php endforeach; ?>

        <th>Total</th>
        <th>Avg.</th>
        <th>Pos.</th>
    </tr>

    <?php
    // Iterate through each student
    foreach ($students as $student) :
    ?>

        <tr>
            <td><?php echo $student['name']; ?></td>
            <td><?php echo $student['registration_no']; ?></td>

            <?php
            $subjectTotals = [];

            // Iterate through each subject
            foreach ($subjects as $subject) :
                // Fetch and display total for the subject
                $totalQuery = "SELECT SUM(total) AS subject_total FROM tbl_student_results WHERE registration_no = ? AND subject = ? AND class = ? AND term = ? AND year = ?";
                $totalStmt = $DBH->prepare($totalQuery);
                $totalStmt->execute([$student['registration_no'], $subject, $class, $term, $year]);
                $subjectTotal = $totalStmt->fetch(PDO::FETCH_COLUMN);
                ?>
                <td><?php echo $subjectTotal; ?></td>
            <?php
                // Collect subject totals for later use
                $subjectTotals[] = $subjectTotal;
            endforeach;

            // Calculate and display total, average, and position
            $total = array_sum($subjectTotals);
            $average = count($subjectTotals) > 0 ? round($total / count($subjectTotals), 2) : 0;

            // Position logic: Get the rank of the student based on the average
            $positionQuery = "SELECT COUNT(DISTINCT registration_no) AS higher_count
                FROM (
                    SELECT registration_no, AVG(total) AS avg_total
                    FROM tbl_student_results
                    WHERE year = ? AND term = ? AND class = ?
                    GROUP BY registration_no
                ) AS ranked
                WHERE avg_total > (
                    SELECT AVG(total)
                    FROM tbl_student_results
                    WHERE year = ? AND term = ? AND class = ? AND registration_no = ?
                )";
            $positionStmt = $DBH->prepare($positionQuery);
            $positionStmt->execute([$year, $term, $class, $year, $term, $class, $student['registration_no']]);
            $positionResult = $positionStmt->fetch(PDO::FETCH_COLUMN);
            $position = $positionResult + 1;
            ?>
            <td><?php echo $total; ?></td>
            <td><?php echo $average; ?></td>
            <td><?php echo $position; ?></td>
        </tr>

    <?php endforeach; ?>

</table>
<!-- End Table Structure -->

<!-- Print Button --><Br>
<P><button onclick="printTable()">Print/Save This Broadsheet</button></P>

<!-- Download Button -->
<!--<button onclick="downloadTable('excel')">Download Excel</button>
<button onclick="downloadTable('pdf')">Download PDF</button> -->

    <!-- JavaScript for Print and Download functionality -->
    <script>
        function printTable() {
            var printContent = document.getElementById("table-container").innerHTML;
            var originalContent = document.body.innerHTML;
            document.body.innerHTML = printContent;
            window.print();
            document.body.innerHTML = originalContent;
        }

        function downloadTable(format) {
            // Implement download logic based on the selected format (excel or pdf)
            // You can use external libraries like jsPDF for PDF download and SheetJS for Excel download
            // Example using jsPDF for PDF download:
            // var doc = new jsPDF();
            // doc.autoTable({ html: '#my-table' });
            // doc.save('table.pdf');
        }
    </script>
</div>
            <!-- Add the analysis section -->
            <div>
                <h3>General Analysis:</h3>
                <ol>
                    <li>How many students scored above 75% in average? <?php echo getGeneralAnalysisCount($students, 75, 'above'); ?></li>
                    <li>How many students scored between 50% and 75% in average? <?php echo getGeneralAnalysisCount($students, 50, 75); ?></li>
                    <li>How many students scored below 50% in average? <?php echo getGeneralAnalysisCount($students, 'below', 50); ?></li>
                </ol>
            </div>

            <div>
                <h3>Per Subject Analysis:</h3>
                <?php
                // Iterate through each subject
                foreach ($subjects as $subject) :
                ?>
                    <div>
                        <h4><?php echo $subject; ?></h4>
                        <ol>
                            <li>How many students scored above 75% in <?php echo $subject; ?>? <?php echo getSubjectAnalysisCount($subject, 75, 100); ?></li>
                            <li>How many students scored between 50% and 75% in <?php echo $subject; ?>? <?php echo getSubjectAnalysisCount($subject, 50, 75); ?></li>
                            <li>How many students scored below 50% in <?php echo $subject; ?>? <?php echo getSubjectAnalysisCount($subject, 0, 49.99); ?></li>
                        </ol>
                    </div>
                <?php endforeach; ?>
            </div>

        </div>
    </div>
    <!-- /.container-fluid-->
    <!-- /.content-wrapper-->
    <footer class="sticky-footer" style="background: #126b3c">
        <div class="container">
            <div class="text-center"> <small><strong><span class="style1">Copyright © 2023-2024 LITTLE ANGELS HIGH SCHOOL, OWERRI, IMO STATE: All Rights Reserved!
                        <br>| <a href="https://dataswift.com.ng"; color="pink">POWERED BY DATASWIFT PORTALS</a> And <a href="skulport.com.ng">SKULPORT</a></span></strong></small> </div>
        </div>
    </footer>
    <!-- Scroll to Top Button-->
    <a class="scroll-to-top rounded" href="#page-top">
        <i class="fa fa-angle-up"></i>
    </a>
    <!-- Logout Modal-->
    <div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLabel">Ready to Leave?</h5>
                    <button class="close" type="button" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">×</span>
                    </button>
                </div>
                <div class="modal-body">Select "Logout" below if you are ready to end your current session.</div>
                <div class="modal-footer">
                    <button class="btn btn-secondary" type="button" data-dismiss="modal">Cancel</button>
                    <a class="btn btn-primary" href="Sign_in.php">Logout</a>
                </div>
            </div>
        </div>
    </div>
    <!-- Bootstrap core JavaScript--> 
    <script src="vendor/jquery/jquery.min.js"></script>
    <script src="vendor/bootstrap/js/bootstrap.bundle.min.js"></script> 
    <!-- Core plugin JavaScript-->
    <script src="vendor/jquery-easing/jquery.easing.min.js"></script>
    <!-- Page level plugin JavaScript-->
    <script src="vendor/chart.js/Chart.min.js"></script>
    <script src="vendor/datatables/jquery.dataTables.js"></script>
    <script src="vendor/datatables/dataTables.bootstrap4.js"></script>
    <!-- Custom scripts for all pages-->
    <script src="js/sb-admin.min.js"></script>
    <!-- Custom scripts for this page-->
    <script src="js/sb-admin-datatables.min.js"></script>
    <script src="js/sb-admin-charts.min.js"></script>
    
    <?php
    // Function to get the general analysis count
    function getGeneralAnalysisCount($students, $min, $max)
    {
        $filteredStudents = array_filter($students, function ($student) use ($min, $max) {
            $average = $student['average'];
            return $average >= $min && $average <= $max;
        });

        return count($filteredStudents);
    }

// Function to get the subject-specific analysis count
function getSubjectAnalysisCount($subject, $min, $max)
{
    global $DBH, $year, $term, $class;

    $analysisQuery = "SELECT COUNT(*) 
                      FROM (
                          SELECT registration_no
                          FROM tbl_student_results 
                          WHERE year = ? AND term = ? AND class = ? AND subject = ? AND total BETWEEN ? AND ?
                          GROUP BY registration_no
                          HAVING SUM(total) BETWEEN ? AND ?
                      ) AS subject_analysis";
    
    $analysisStmt = $DBH->prepare($analysisQuery);
    $analysisStmt->execute([$year, $term, $class, $subject, $min, $max, $min, $max]);
    $count = $analysisStmt->fetch(PDO::FETCH_COLUMN);

    return $count;
}

    ?>

</body>

</html>
