<?php
include('session_validator.php');
// Include database connection code
include("includes/db.php");

if (isset($_GET['class_id']) && isset($_GET['exam_id'])) {
    $classId = $_GET['class_id'];
    $examId = $_GET['exam_id'];

    // Query students, subjects, and marks for the specific class and exam
    $query = "SELECT students.student_id AS student_id, students.name AS student_name,
                subjects.subject_code, subjects.subject_name, subjects.theory_full_marks, 
                subjects.internal_full_marks, subjects.theory_credit_hour, subjects.internal_credit_hour,
                marks.marks, classes.class_name, classes.section          
                FROM students
              INNER JOIN marks ON students.student_id = marks.student_id
              INNER JOIN classes ON students.Class_id = classes.Class_id
              INNER JOIN subjects ON marks.subject_id = subjects.id
              WHERE marks.class_id = ? AND marks.exam_type = ?
              ORDER BY students.Student_id, subjects.id";

    // Use prepared statement to prevent SQL injection
    $stmt = $conn->prepare($query);
    $stmt->bind_param("ii", $classId, $examId);
    $stmt->execute();
    $result = $stmt->get_result();

    // Prepare data for display
    $ledger = [];
    $currentStudentId = null;
    $totalMarks = 0;
    $TotalWGP = 0;
    $TotalCH = 0;
    $totalwgp = 0;

$examquery = "SELECT * FROM examination_type WHERE id = $examId";
        $examresult = $conn->query($examquery);
        $examDetails = $examresult->fetch_assoc();
        $examtype = $examDetails['exam_type'];
        $examyear = $examDetails['exm_year'];

    while ($row = $result->fetch_assoc()) {

        $studentId = $row['student_id'];
        $subjectName = $row['subject_name'];
        $subjectcode = $row['subject_code'];
        $marks = $row['marks'];
        $full_marks = $row['theory_full_marks'];
        $creditHour = $row['theory_credit_hour'];
        $classname1 = $row['class_name'];
        $section1 = $row['section'];
        $Legclass = $classname1 . '-' . $section1;


        if ($studentId != $currentStudentId) {
            // Save total marks for the previous student and reset total marks
            if ($currentStudentId !== null) {
                $currentStudentData['total_marks'] = $totalMarks;
                $currentStudentData['totalwgp'] = $TotalWGP; // Add Total WGP to the data
                $ledger[] = $currentStudentData;
            }

            // Initialize data for the new student
            $currentStudentId = $studentId;
            $currentStudentData = [
                'sn' => count($ledger) + 1,
                'name' => $row['student_name'],
                'subjects' => [],
                'total_marks' => 0,
                'totalwgp' => 0
            ];

            // Reset total marks for the new student
            $totalMarks = 0;
            $TotalWGP = 0;
            $TotalCH = 0; // Reset total credit hour
        }

        // Add subject marks for the current student
        $currentStudentData['subjects'][] = [
            'subject' => $subjectName,
            'subject_code' => $subjectcode,
            'marks' => $marks,
            'full_marks' => $full_marks,
            'creditHour' => $creditHour
        ];

        // Calculate total marks for the current student
        $totalMarks += $marks;

        // Calculate Total WGP for the current student
        $GP = calculateGradePoint($marks, $full_marks);
        $TotalWGP += ($GP * $creditHour);
        $TotalCH += $creditHour; // Increment total credit hour
    }

    // Add the last student's total marks and Total WGP to the ledger
    if ($currentStudentId !== null) {
        $currentStudentData['total_marks'] = $totalMarks;
        $currentStudentData['totalwgp'] = $TotalWGP;
        $ledger[] = $currentStudentData;
    }
}

/**
 * Function to calculate Grade Point (GP) based on obtained marks and full marks.
 *
 * @param float $obtainedMarks
 * @param float $fullMarks
 * @return float
 */
function calculateGradePoint($obtainedMarks, $fullMarks)
{
    $MP = ($obtainedMarks / $fullMarks);

    if ($MP >= 0.9) {
        return 4.0;
    } elseif ($MP >= 0.8) {
        return 3.6;
    } elseif ($MP >= 0.7) {
        return 3.2;
    } elseif ($MP >= 0.6) {
        return 2.8;
    } elseif ($MP >= 0.5) {
        return 2.4;
    } elseif ($MP >= 0.4) {
        return 2.0;
    } elseif ($MP >= 0.35) {
        return 1.6;
    } elseif ($MP >= 0.0001) {
        return 0.0;
    } else {
        return 0.0; // For ABSENT
    }
}

/** 
 * *
 *
  @param float $obtainedMarks
 * @param float $fullMarks
 * @return float
 */
function calculateGrade($obtainedMarks, $fullMarks)
{
    $MP1 = ($obtainedMarks / $fullMarks);

    if ($MP1 >= 0.9) {
        return 'A+';
    } elseif ($MP1 >= 0.8) {
        return 'A';
    } elseif ($MP1 >= 0.7) {
        return 'B+';
    } elseif ($MP1 >= 0.6) {
        return 'B';
    } elseif ($MP1 >= 0.5) {
        return 'C+';
    } elseif ($MP1 >= 0.4) {
        return 'C';
    } elseif ($MP1 >= 0.35) {
        return 'D';
    } elseif ($MP1 >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs'; // For ABSENT
    }
}

?>

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

<!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">
        <title>SRMS Admin Manage Subjects</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>
    </head>
    <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">Result Analysis</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> Reports </li>
                                        <li class="">Average Report</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>Result Analysis</h5>
                                                </div>
                                            </div>
                                            <div class="panel-body">
     <script>
        $(document).ready(function () {
            // DataTable initialization
            var table = $('#customer').DataTable();

            // Add print button
            $('#printBtn').on('click', function () {
                window.print();
            });

            // Add export to Excel button
            $('#exportExcelBtn').on('click', function () {
                var exportData = [];
                table.rows().every(function () {
                    var data = this.data();
                    exportData.push(data);
                });

                // // Sort data alphabetically by student name
                // exportData.sort(function (a, b) {
                //     return a[1].localeCompare(b[1]);
                // });

                // Create worksheet and add data
                var ws = XLSX.utils.json_to_sheet(exportData);

                // Create workbook and add worksheet
                var wb = XLSX.utils.book_new();
                XLSX.utils.book_append_sheet(wb, ws, 'Ledger');

                // Save the workbook
                XLSX.writeFile(wb, 'ledger_export.xlsx');
            });
        });
    </script>

<script>
    function printTable() {
        var printWindow = window.open('', '_blank');
        printWindow.document.write('<html><head><title>MMSS, Result Analsis</title>');
        printWindow.document.write('<link rel="stylesheet" href="css/tablestyle.css" type="text/css">');

        
         printWindow.document.write('</head><body>');
        printWindow.document.write('</head><body>');



        printWindow.document.write(document.getElementById('customer').outerHTML);
        printWindow.document.write('</body></html>');
        printWindow.document.close();

        printWindow.document.documentElement.style.zoom = '90%';

        // Use setTimeout to ensure styles are applied before printing
        setTimeout(function () {
            printWindow.print();
            printWindow.onafterprint = function () {
                printWindow.close();
            };
        }, 500);
    } 
</script>

   
    <div class="container">
    <form action="average.php" method="get">


         <label for="exam_id">Select Exam:</label>
  <select id="exam_id" name="exam_id" class="form-control" style="width: 300px;" required>
    <option value="">Select Exam</option>
                    <?php
                    // Include the database configuration file
                    include("includes/db.php");

                    // Fetch class names from the 'classes' table
                    $queryexam = "SELECT id, exm_year , exam_type FROM examination_type";
                    $resultexam = $conn->query($queryexam);

                    // Populate the dropdown with class names and sections
                    while ($row = $resultexam->fetch_assoc()) {
                        echo "<option value='" . $row['id'] . "'>" . $row['exam_type'] . " - " . $row['exm_year'] . "</option>";
                    }
 $conn->close();
                    ?>

</select>



        <label for="class_id">Select Class:</label>
        <select name="class_id" id="class_id" class="form-control" style="width: 300px;">
            <option> Select Class </option>
            <!-- Populate dropdown options dynamically from your database -->
            <?php
            // Include database connection code
            include("includes/db.php");

            // Query classes from the database
            $classQuery = "SELECT class_id, class_name, section FROM classes";
            $classResult = $conn->query($classQuery);

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

            echo $row['class_name'];
            // Close the database connection
            $conn->close();
            ?>
        </select>
        <br>
        <button type="submit" class="btn btn-primary">Show Ledger</button>
    </form>
</div>
    <div>
        <?php if (isset($ledger) && !empty($ledger)) : ?>
            <h2>Result Analysis Data for Class <?php echo $Legclass."[". $examtype ."]" ?></h2>

            <button onclick="printTable()">Print Table</button>
            <button id="exportExcelBtn" class="btn btn-success">Export to Excel</button>
    
               <br><br>

             <table id="customer" style="border-collapse: collapse;"><tr>
                <td>
                    
<table id="customers" style="border-collapse: collapse; border: 1px solid #000000;">  
    <tr> 
        <td colspan="6" style="border:none;font-size: 18px;"><b><u><center>Result Analysis Data for Class <?php echo $Legclass ." in  [". $examtype ."] (TH only)" ?></center></u></b></td>
    </tr>
    <tr>
        <th rowspan="2" style="width:400px">Subjects</th>

        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <th style="font-size: 12px;">
                <center > <?= $subject['subject'] . ' [' . $subject['subject_code'] . ']'  ?></center>
            </th>
        <?php endforeach; ?>
                    
    </tr>
    <tr>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <!-- <th> Obt. Marks [TH]</th> -->
            <!--    <th> GP</th>
            <th> Grade</th> -->
        <?php endforeach; ?>
    </tr>

    <tr>
        <td colspan="">Total</td>
        <?php
        $subjectTotals = array_fill(0, count($ledger[0]['subjects']), 0); // Initialize array for subject totals
        $totalGP = 0;
        ?>
        <?php foreach ($ledger as $student) : ?>
            <?php foreach ($student['subjects'] as $key => $subject) : ?>
                <?php
                $subjectTotals[$key] += $subject['marks']; // Sum each subject's marks
                ?>
            <?php endforeach; ?>
            <?php
            $totalGP += $student['totalwgp']; // Sum total WGP for all students
            ?>
        <?php endforeach; ?>
        <?php foreach ($subjectTotals as $total) : ?>
            <td colspan=""><?= $total ?></td> 
            <!-- Display subject-wise totals for Obt. Marks [TH] -->
        <?php endforeach; ?>
    </tr>

    <tr>
        <td colspan="">Exam Appear Students</td>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <?php
            $studentsWithMarks = 0;
            ?>
            <?php foreach ($ledger as $student) : ?>
                <?php foreach ($student['subjects'] as $subj) : ?>
                    <?php if ($subj['subject'] === $subject['subject'] && $subj['marks'] > 0) : ?>
                        <?php
                        $studentsWithMarks++;
                        ?>
                    <?php endif; ?>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <td colspan=""><?= $studentsWithMarks ?></td>
        <?php endforeach; ?>
    </tr>

    <!-- <tr>
        <th> SUBJECTS </th>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <td style="writing-mode: vertical-rl; transform: rotate(180deg);"><?= $subject['subject'] ?></td>
        <?php endforeach; ?>
    </tr> -->

    <tr>
        <th> Average </th>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <?php
            $totalMarksSubject = 0;
            $studentCountSubject = 0;
            foreach ($ledger as $student) {
                foreach ($student['subjects'] as $subj) {
                    if ($subj['subject'] === $subject['subject']) {
                        $totalMarksSubject += $subj['marks'];
                        if ($subj['marks'] > 0) {
                            $studentCountSubject++;
                        }
                    }
                }
            }
            $averageMarks = ($studentCountSubject > 0) ? number_format($totalMarksSubject / $studentCountSubject, 2) : 0;
            ?>
            <td><?= $averageMarks ?></td> 
        <?php endforeach; ?>
    </tr>
</table>




  <table id="customers" style="border-collapse: collapse; border: 1px solid #000000;">
    <tr> 
            <td colspan="6" style="border: none;">

    <span style="font-size: 20px; "> <b> <U> Grade Count Details for Subjects </U></b></span>
            </td></tr>

    <tr> <center>
<!--  --> <th> SUBJECTS / GRADE </th>
            <th style="width: 50px;"> <center>A+</center> </th>
            <th style="width: 50px;"><center>  A  </center></th>
            <th style="width: 50px;"><center>  B+ </center> </th>
            <th style="width: 50px;"><center>  B  </center></th>
            <th style="width: 50px;"><center>  C+ </center> </th>
            <th style="width: 50px;"><center>  C  </center></th>
            <th style="width: 50px;"><center>  D  </center></th>
            <th style="width: 50px;"><center>  NG </center> </th>
           <th style="width: 50px;"> <center> ABSENT </center> </th>
           <th style="width: 100px; border: none;"></th></center>
     <!--       <th></th>
           <th></th>
             <th></th>
           <th></th>
           <th></th> -->


    <!-- Count of Grades for each subject -->
    <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
        <tr>

            <td colspan=""><?= $subject['subject'] ?></td>
            <?php
            $gradeCounts = array_fill_keys(['A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'NG', 'Abs'], 0);

            foreach ($ledger as $student) {
                foreach ($student['subjects'] as $subj) {
                    if ($subj['subject'] === $subject['subject']) {
                        $grade = calculateGrade($subj['marks'], $subj['full_marks']);
                        $gradeCounts[$grade]++;
                    }
                }
            }
            ?>
            <?php foreach ($gradeCounts as $count) : ?>
                <td><center><?= $count ?></center></td> <!-- Display grade counts for each subject -->
            <?php endforeach; ?>
            
        </tr>
    <?php endforeach; ?>


        <tr> 
            <td colspan="6" style="border: none;">

    <span style="font-size: 20px; "> <b> <U> Average Marks Details for Subjects </U></b></span>
            </td></tr>







<?php
// Include database connection code
include("includes/db.php");

// Check if 'class_id' and 'exam_id' are set in GET parameters
if (isset($_GET['class_id']) && isset($_GET['exam_id'])) {
    $classId = $_GET['class_id'];
    $examId = $_GET['exam_id'];

    // Query to fetch students' marks for the selected class and exam
    $query = "SELECT students.student_id AS student_id, students.name AS student_name,
                subjects.subject_code, subjects.subject_name, subjects.theory_full_marks, 
                marks.marks
                FROM students
                INNER JOIN marks ON students.student_id = marks.student_id
                INNER JOIN subjects ON marks.subject_id = subjects.id
                WHERE marks.class_id = $classId AND marks.exam_type = $examId
                ORDER BY students.student_id, subjects.subject_code";

    $result = $conn->query($query);

    // Prepare data for display
    $ledger = [];

    while ($row = $result->fetch_assoc()) {
        $studentId = $row['student_id'];
        $studentName = $row['student_name'];
        $subjectCode = $row['subject_code'];
        $subjectName = $row['subject_name'];
        $obtainedMarks = $row['marks'];
        $fullMarks = $row['theory_full_marks'];

        // Calculate Grade Point and Grade for each subject
        $gradePoint = calculateGradePoint($obtainedMarks, $fullMarks);
        $grade = calculateGrade($obtainedMarks, $fullMarks);

        // Store data in the ledger array
        if (!isset($ledger[$subjectCode])) {
            $ledger[$subjectCode] = [
                'subject_name' => $subjectName,
                'total_grade_point' => 0,
                'total_students' => 0
            ];
        }

        // Update total grade point for the subject
        $ledger[$subjectCode]['total_grade_point'] += $gradePoint;

        // Count only students with obtained marks greater than 0
        if ($obtainedMarks > 0) {
            if (!isset($ledger[$subjectCode]['students_with_marks'])) {
                $ledger[$subjectCode]['students_with_marks'] = [];
            }
            // Add unique student IDs for each subject with obtained marks
            $ledger[$subjectCode]['students_with_marks'][$studentId] = true;
        }
    }

    // Count total students having obtained marks for each subject
    foreach ($ledger as $subjectCode => $data) {
        if (isset($data['students_with_marks'])) {
            $ledger[$subjectCode]['total_students'] = count($data['students_with_marks']);
        } else {
            $ledger[$subjectCode]['total_students'] = 0;
        }
    }

    // Display total students with obtained marks for each subject
    echo "<table id='customers' style='border-collapse: collapse; border: 1px solid #000000;'>";
    echo "<tr><th>Subject Name</th><th>Total Grade Point</th><th>Total Present Students</th><th> Subject Average</th></tr>";
    foreach ($ledger as $subjectCode => $data) {
        $totalGradepoint = $data['total_grade_point'];
        $totalstd = $data['total_students'];
        $gradeaverage = number_format(($totalGradepoint / $totalstd), 2);

        echo "<tr>";
        echo "<td>" . $data['subject_name'] . "</td>";
        echo "<td>" . $data['total_grade_point'] . "</td>";
        echo "<td>" . $totalstd . "</td>";
        echo "<td>" . $gradeaverage . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p>Please select a class and exam to view the ledger.</p>";
}

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

</tr>
</tfoot>
</table>

</table>
          
        <?php else : ?>
            <p>No data available for the selected class.</p>
        <?php endif; ?>
</div>
                                            </div>
                                        </div>
                                    </div>
                                    <!-- /.col-md-12 -->
                                </div>
                    </div>
                </div>
                <!-- /.content-container -->
            </div>
            <!-- /.content-wrapper -->
        </div>
        <!-- /.main-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/select2/select2.min.js"></script>
        <script src="js/main.js"></script>

    </body>
</html>