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

if (isset($_GET['class_id']) && is_array($_GET['class_id'])) {
    $classIds = $_GET['class_id'];

    // Construct a comma-separated string for SQL query
    $classIdString = implode(",", $classIds);

    // Query students, subjects, and marks for the selected classes
    $query = "SELECT students.student_id AS student_id, students.name AS student_name,
                studentsub.sub1, studentsub.sub2, studentsub.sub3,
                studentsub.sub4, studentsub.sub5, studentsub.sub6,studentsub.sub7,
                subjects.subject_code, subjects.subject_name,
                subjects.theory_full_marks, subjects.theory_credit_hour,
                COALESCE(marks.marks, '') AS marks,
                classes.class_name,classes.class_id, classes.section

                FROM students

              INNER JOIN classes ON students.Class_id = classes.Class_id
              INNER JOIN studentsub ON students.student_id = studentsub.student_id AND students.Class_id = studentsub.class_id
              INNER JOIN subjects ON studentsub.sub1 = subjects.subject_code OR
                                      studentsub.sub2 = subjects.subject_code OR
                                      studentsub.sub3 = subjects.subject_code OR
                                      studentsub.sub4 = subjects.subject_code OR
                                      studentsub.sub5 = subjects.subject_code OR
                                      studentsub.sub6 = subjects.subject_code OR
                                      studentsub.sub7 = subjects.subject_code
              LEFT JOIN marks ON students.student_id = marks.student_id AND students.Class_id = marks.class_id AND subjects.id = marks.subject_id

              WHERE students.Class_id IN ($classIdString)
              ORDER BY students.Student_id, subjects.id";

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

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

    while ($row = $result->fetch_assoc()) {
        $studentId = $row['student_id'];
        $subjectName = $row['subject_name'];
        $subjectcode = $row['subject_code'];
        $marks = $row['marks'];
        $creditHour = $row['theory_credit_hour'];
        $theoryFullMarks = $row['theory_full_marks'];
        $classname1 = $row['class_name'];
        $section1 = $row['section'];
        $Legclass = $classname1 . '-' . $section1;

        // Collect unique class and section names
        $classKey = $row['class_id'];
        if (!isset($classNames[$classKey])) {
            $classNames[$classKey] = $classname1 . ' - ' . $section1;
        }

        if ($studentId != $currentStudentId) {
            // Save total marks for the previous student and reset total marks
            if ($currentStudentId !== null) {
                $currentStudentData['fullmarkssum'] = $totalfullmarks;
                $currentStudentData['total_marks'] = $totalMarks;
                $currentStudentData['totalwgp'] = $TotalWGP;
                $currentStudentData['totalch'] = $TotalCH;
                $ledger[] = $currentStudentData;
            }

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

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

        // Check if the subject is already added for the current student
        $existingSubject = array_filter(
            $currentStudentData['subjects'],
            function ($subj) use ($subjectcode) {
                return $subj['subject_code'] === $subjectcode;
            }
        );

        // Add subject only if it's not already added
        if (empty($existingSubject)) {
            // Add subject marks for the current student
            $currentStudentData['subjects'][] = [
                'subject_name' => $subjectName,
                'subject_code' => $subjectcode,
                'credit_hour' => $creditHour,
                'theory_full_marks' => $theoryFullMarks,
                'marks' => $marks,
            ];
        }

        // Calculate total marks for the current student
        $totalMarks += is_numeric($marks) ? $marks : 0;
        $totalfullmarks += is_numeric($theoryFullMarks) ? $theoryFullMarks : 0;
    }

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

    // Sort the ledger by total marks or percentage
    usort($ledger, function ($a, $b) {
        $percentageA = $a['total_marks'] / $a['fullmarkssum'];
        $percentageB = $b['total_marks'] / $b['fullmarkssum'];
        return $percentageB <=> $percentageA;
    });
}
?>

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="js/jquery/jquery-2.2.4.min.js"></script>
    <script src="https://cdn.rawgit.com/eKoopmans/html2pdf/master/dist/html2pdf.bundle.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.4/xlsx.full.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="css/tablestyle.css" type="text/css">
    <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/prism/prism.css" media="screen">
    <!--<link rel="stylesheet" href="css/main.css" media="screen" >-->
    <script src="js/multiselect-dropdown.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css">
    <title>Marks Obtained Ledger</title>
</head>
<p>
    Go back to home: <a href="#">
        <img border="0" alt="SRMS" src="favicon.ico" width="40" height="40" Value="Dashboard">
    </a>
</p>

<body>
    <script>
        $(document).ready(function() {
            // DataTable initialization
            var table = $('#customers').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);
                });

                // 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');
            });
        });

        function printTable() {
            var printWindow = window.open('', '_blank');
            printWindow.document.write('<html><head><title>Mohanmaya Sec. School, Grade Ledger , Third Term Examination 2080</title>');
            printWindow.document.write('<link rel="stylesheet" href="css/tablestyle.css" type="text/css">');
            printWindow.document.write('</head><body>');
            printWindow.document.write(document.getElementById('customers').outerHTML);
            printWindow.document.write('</body></html>');
            printWindow.document.close();

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

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

    <div class="container">
        <h2>Select Class</h2>
        <div class="container" style="margin-left:-120px">
            <!-- Update the form in marks_ledger.php -->
            <form action="top.php" method="get">
                <div>
                    <label for="class_id">Select Class:</label>
                    <select name="class_id[]" id="class_id" class="form-control" style="width: 450px;" multiple>
                        <!-- 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>";
                        }

                        // Close the database connection
                        $conn->close();
                        ?>
                    </select>
                    <button type="submit" class="btn btn-primary">Show Ledger</button>
                </div>
            </form>

            <div class="container">
                <?php if (isset($ledger) && !empty($ledger)) : ?>
                    <h3>Ledger for Classes <?php echo implode(', ', $currentStudentData['class_names']); ?></h3>
                    <!-- Add print and export buttons -->
                    <button onclick="printTable()">Print Table</button>
                    <button id="exportExcelBtn" class="btn btn-success">Export to Excel</button>
                    <br><br>
                    <table id="customers" style="border-collapse: collapse; border: 1px solid #000000;">
                        <thead>
                            <tr>
                                <td colspan='4'>
                                    <span style='border:none'>
                                        <b>Mohanmaya Secondary School</b> <br> Birtamode-02, Jhapa<br> Monthly Test Mark Ledger 2081 Jestha<br>Ledger for Class <b>
                                            <?php echo implode(', ', $currentStudentData['class_names']); ?>
                                        </b>
                                    </span>
                                </td>
                            </tr>
                            <tr>
                                <th rowspan="2">S.N.</th>
                                <th rowspan="2">Name</th>
                                <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                                    <?= $subject['subject_name'] ?>
                                <?php endforeach; ?>
                                <th rowspan="2">Total</th>
                                <th rowspan="2">Percent</th>
                            </tr>
                        </thead>
                        <tbody>
                           <?php
                            $serial = 1;
                            foreach ($ledger as $student) : ?>
                              <tr>
                                <td><?= $serial++ ?></td>
                                    <td style=" font-size: 16px;"><?= $student['name'] ?></td>
                                    <?php foreach ($student['subjects'] as $subject) : ?>
                                    <?php endforeach; ?>
                                    <td style=" font-size: 14px;" ><?= $student['total_marks'] ?></td>
                                    <td style=" font-size: 14px;" ><?= number_format(($student['total_marks'] / $student['fullmarkssum']) * 100, 2) ?></td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                <?php else : ?>
                    <p>No data available for the selected class.</p>
                <?php endif; ?>
            </div>
        </div>

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