<?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 = 'Class'; // 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=yes">
    <meta name="description" content="">
    <meta name="author" content="">
    <title>BRITISH LEARNER'S COLLEGE: ADMIN 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;
        }

        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;
        }
        
            /* Add this CSS to rotate table headers */
    th.vertical-header {
        writing-mode: vertical-rl;
        transform: rotate(180deg);
        white-space: nowrap;
    }
    
    .table {
        font-size: 12px; /* Adjust the font size as needed */
    }

    .table-responsive {
        overflow-x: auto;
    }
    
    .student-name {
        text-transform: capitalize; /* Convert the first letter of each word to uppercase (Title Case) */
    }
    </style>
</head>

<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">

        <?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: Lime'><b>BRITISH LEARNER'S COLLEGE: ADMIN Dashboard</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" name="exampleAccordion">
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Dashboard">
                    <a class="nav-link" href="main.php">
                        <i class="fa fa-fw fa-dashboard" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Admin Centre</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="post_session.php">
                        <i class="fa fa-fw fa-clock-o" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">School Year/Session</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="post_classes.php">
                        <i class="fa fa-fw fa-building" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Create Classes</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="add_students.php">
                        <i class="fa fa-fw fa-building" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Register New Student</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="post_subjects.php">
                        <i class="fa fa-fw fa-book" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Create Subjects</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="teachers.php">
                        <i class="fa fa-fw fa-user-o" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Manage Teachers</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="students.php">
                        <i class="fa fa-fw fa-user-o" style="color: #D8807D"></i>
                        <span class="nav-link-text" style="color: white">Manage Students/Pupils</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Charts">
                    <a class="nav-link" href="deactivate_students.php">
                        <i class="fa fa-fw fa-user-o" style="color: yellow"></i>
                        <span class="nav-link-text" style="color: Green">ACTIVATE</span><span class="nav-link-text" style="color: Orange">/DE-ACTIVATE</span>
                    </a>
                </li>
                <li class="nav-item" data-toggle="tooltip" data-placement="right" title="Tables">
                    <a class="nav-link" href="logout.php">
                        <i class="fa fa-fw fa-money" style="color: #9fda18"></i>
                        <span class="nav-link-text" style="color: white">Logout</span>
                    </a>
                </li>
            </ul>
            <ul class="navbar-nav ml-auto">
                <li class="nav-item">
                    <span class="m-0"><a class="nav-link" data-toggle="modal" href="logout.php" style="color: white; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif, 'Apple Color Emoji', 'Segoe UI Emoji', 'Segoe UI Symbol';"> Logout</a></span>
                </li>
            </ul>
        </div>
    </nav>

    <div class="content-wrapper">
        <div class="container-fluid">

      <!-- Breadcrumbs-->
      <ol class="breadcrumb" style="background: grey">
        <li class="breadcrumb-item">
          <a href="main.php" style="color: black; font-weight: bold">Back to Dashboard</a>        </li>
        <li class="breadcrumb-item active" style="color: black"></li>
      </ol>
      <div class="row">
        <div class="col-lg-12">
          <!-- Example Bar Chart Card-->
      <ol class="breadcrumb"style="background: grey">
                                    <h6 align="center" class="style1"><big>Check & Print Your Class Broadsheet</big></h6>
                                    </ol>   
            <!-- 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">-->
<div class="table-responsive" 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 style="font-size:10px" rowspan="2">S/N</th>
            <th style="font-size:10px; width:230px" rowspan="2" class="student-name">Name of Student</th>
            <th style="font-size:10px; width:80px" rowspan="2">Adm. No</th>
        
            <?php foreach ($subjects as $subject) : ?>
                <th style="font-size:10px" colspan="4" class="vertical-header"><?php echo $subject; ?></th>
            <?php endforeach; ?>
        
            <th style="font-size:10px" rowspan="2" class="vertical-header">Overall Total</th>
            <th style="font-size:10px" rowspan="2" class="vertical-header">Average Score</th>
            <th style="font-size:10px" rowspan="2" class="vertical-header">Position</th>
        </tr>
        <tr>
            <?php foreach ($subjects as $subject) : ?>
                <th style="font-size:10px" class="vertical-header">CA 1</th>
                <th style="font-size:10px" class="vertical-header">CA 2</th>
                <th style="font-size:10px" class="vertical-header">Exam</th>
                <th style="font-size:10px; color:navy" class="vertical-header"><b>Subject Total</b></th> <!-- Add this line for subject total -->
            <?php endforeach; ?>
            <!--<th class="vertical-header">Overall Total</th>  Add this line for overall total -->
        </tr>


<?php
$serialNumber = 1;

// Initialize arrays to store subject totals and overall totals
$subjectTotals = array_fill_keys($subjects, 0);
$overallTotals = array_fill_keys(['ca1', 'ca2', 'exam', 'total'], 0);

// Iterate through each student
foreach ($students as $student) : ?>
    <tr>
        <td style="font-size:10px"><?php echo $serialNumber++; ?></td>
        <td style="font-size:10px" class="student-name"><b><?php echo ucwords(strtolower($student['name'])); ?></b></td>
                <td style="font-size:10px" ><b><?php echo $student['registration_no']; ?></b></td>

        <?php
        // Initialize variables to store student's total
        $studentTotal = 0;

        // Iterate through each subject
        foreach ($subjects as $subject) :
            // Fetch and display CA 1, CA 2, and Exam for the subject
            $subjectQuery = "SELECT test1 AS ca1, test2 AS ca2, exam
                             FROM tbl_student_results 
                             WHERE registration_no = ? AND subject = ? AND class = ? AND term = ? AND year = ?";
            $subjectStmt = $DBH->prepare($subjectQuery);
            $subjectStmt->execute([$student['registration_no'], $subject, $class, $term, $year]);
            $subjectData = $subjectStmt->fetch(PDO::FETCH_ASSOC);
            ?>
            <td style="font-size:10px"><?php echo $subjectData['ca1']; ?></td>
            <td style="font-size:10px"><?php echo $subjectData['ca2']; ?></td>
            <td style="font-size:10px"><?php echo $subjectData['exam']; ?></td>
            <td style="font-size:10px; color:navy"><b><?php echo $subjectData['ca1'] + $subjectData['ca2'] + $subjectData['exam']; ?></b></td> <!-- Subject Total -->
            <?php
            // Add subject totals to the corresponding array
            $subjectTotals[$subject] += $subjectData['ca1'] + $subjectData['ca2'] + $subjectData['exam'];
            // Add to student's total
            $studentTotal += $subjectData['ca1'] + $subjectData['ca2'] + $subjectData['exam'];
            ?>
        <?php endforeach;

        // Add to overall totals
        $overallTotals['ca1'] += $subjectData['ca1'];
        $overallTotals['ca2'] += $subjectData['ca2'];
        $overallTotals['exam'] += $subjectData['exam'];
        $overallTotals['total'] += $studentTotal;

        // Calculate and display total, average, and position
        $average = round($studentTotal / count($subjects), 2);
        $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 style="font-size:10px"><b><big><?php echo $studentTotal; ?></big></b></td>
        <td style="font-size:10px"><?php echo $average; ?></td>
        <td style="font-size:10px"><b><big><?php echo $position; ?></big></b></td>
    </tr>

<?php endforeach; ?>

<!-- ... -->

<!-- Add the total rows after iterating through students
<tr>
    <td></td>
    <td>Total</td>
    <td></td>

    <?php
    // Iterate through each subject
    foreach ($subjects as $subject) :
    ?>
        <td style="font-size:10px"><?php echo $subjectTotals[$subject]; ?></td>
    <?php endforeach; ?>

    <td><?php echo $overallTotals['total']; ?></td>
    <td></td>
    <td></td>
</tr> -->

</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: #D8807D">
        <div class="container">
            <div class="text-center"> <small><strong><span class="style1">Copyright © 2023-2024 BRITISH LEARNER'S COLLEGE: 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>
