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

$schoolname = '';
$schooladdress = '';
$estd = '';
$examtype = '';
$class_names = '';
$examType= '';


// Function to calculate GP
function calculateGP($MP)
{
    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 '<b><span style="color:red ">NG</span></b>';
    } else {
        return '<b><span style="color:#0011ff">Abs</span></b>';
    }
}

// Function to get grade
function calculateGrade($MP)
{
    if ($MP >= 0.9) {
        return 'A+';
    } elseif ($MP >= 0.8) {
        return 'A';
    } elseif ($MP >= 0.7) {
        return 'B+';
    } elseif ($MP >= 0.6) {
        return 'B';
    } elseif ($MP >= 0.5) {
        return 'C+';
    } elseif ($MP >= 0.4) {
        return 'C';
    } elseif ($MP >= 0.35) {
        return 'D';
    } elseif ($MP >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs';
    }
}


if (isset($_GET['class_id']) && is_array($_GET['class_id']) && isset($_GET['exam_type'])) {
    $classIds = $_GET['class_id'];
    $examType = $_GET['exam_type']; // New parameter for exam type

    
    // 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, students.symbol_no,
                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, attendance.atten_day, classes.section , subjects.*

            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
              AND marks.exam_type = '$examType'
              LEFT JOIN attendance ON students.student_id = attendance.std_id AND marks.exam_type = attendance.exam_type
        

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

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

    // Prepare data for display
    $ledger = [];
    $currentStudentId = null;
    $currentStudentData = null;
    $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;
        $attenDay = $row['atten_day'];

        // 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['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'],
                'symbol' => $row['symbol_no'],
                'subjects' => [],
                'total_marks' => 0,
                'totalwgp' => 0,
                'totalch' =>0,
                'class_names' => array_values($classNames),
                'atten_day'=>$row['atten_day'],
                'stdsection' =>$row['section'],
            ];

            
            // Reset total marks for the new student
            $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,
                'atten_day'=> $attenDay,
                'stdsection' => $section1,
            ];

// Calculate GP and WGP for the current subject
            $Obtmarks = $marks;
            $fullmarks = $theoryFullMarks;
            $CH = $creditHour;

            // Explicitly cast to float to avoid non-numeric value warning
            $MP = is_numeric($fullmarks) && $fullmarks > 0 ? (float)$Obtmarks / (float)$fullmarks : 0;

            $grade = calculateGrade($MP);
            $GP = calculateGP($MP);

            if ($grade === 'NG' || $GP === '' || $MP < 0.35) {
                // If NG, GP is blank, or MP < 0.35, set WGP to 0
                $WGP = 0;
            } else {
                $WGP = $GP * $CH;
            }

            // Add GP and WGP to the running totals for the student
            $TotalWGP += $WGP;
            $TotalCH += $CH;
        }

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

    // Add the last student's total marks to the ledger
    if ($currentStudentId !== null) {
        $currentStudentData['total_marks'] = $totalMarks;
        $currentStudentData['totalwgp'] = $TotalWGP;
        $ledger[] = $currentStudentData;
    }
}
// Fetch school details
$query = "SELECT * FROM schools LIMIT 1";
$result = $conn->query($query);

if ($result->num_rows > 0) {
    $schoolDetails = $result->fetch_assoc();
    $logoPath = $schoolDetails['logo'];
    // Assign values to the variables
    $schoolname = $schoolDetails['school_name'];
    $schooladdress = $schoolDetails['address'];
    $estd = $schoolDetails['establishment_year'];
}

// Fetch exam details
if (!empty($examType) && is_numeric($examType)) {
    $examquery = "SELECT * FROM examination_type WHERE id = $examType";
    $examresult = $conn->query($examquery);

    if ($examresult === false) {
        echo "Error executing query: " . $conn->error;
        // Handle the error accordingly, such as displaying an error message or logging it
    } else {
        // Fetch exam details from the result
        if ($examresult->num_rows > 0) {
            $examDetails = $examresult->fetch_assoc();
            // Assign values to the variables
            $examtype = $examDetails['exam_type'];
            $examyear = $examDetails['exm_year'];
        } else {
            // Handle the case where no exam details are found
        }
    }
} else {
    // Handle the case where $examType is empty or not valid
}

?>

<!DOCTYPE html>
<html>
<head>
    <title>Failed Students Ledger</title>
         <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="nepali.datepicker.v4.0.1/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" >
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 5px;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }
        th, td {
            border: 1px solid #ccc;
            padding: 5px;
            text-align: center;
            font-size: 12px;
        }
        th {
            background-color: #f4f4f4;
        }
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        .header-buttons {
            margin-bottom: 20px;
            text-align: right;
        }
        .btn {
            padding: 10px 15px;
            margin: 5px;
            background-color: #007bff;
            color: #fff;
            text-decoration: none;
            border-radius: 5px;
            cursor: pointer;
        }
        .btn:hover {
            background-color: #0056b3;
        }
    </style>

</head>
<script>
    $(document).ready(function () {
        // Check if the DataTable is already initialized
        if (!$.fn.DataTable.isDataTable('#customers')) {
            // Initialize DataTables with a default page length of 50 and a custom length menu
            $('#customers').DataTable({
                "pageLength": 100,
                "lengthMenu": [10, 25, 50, 100, 500]
            });
        }
    });
</script>
<body>
    <div class="header-buttons">
        <button class="btn" onclick="printLedger()">Print</button>
        <button class="btn" onclick="exportToExcel()">Export to Excel</button>
    </div>

    <div class="container">
        <?php if (isset($ledger) && !empty($ledger)) : ?>
            <h3>Failed Students Report for Classes <?php echo implode(', ', $currentStudentData['class_names']); ?></h3>

            <?php






// Count the total number of subjects and additional columns
$totalSubjects = count($ledger[0]['subjects']); // Assuming the first student has all subjects

// Assuming you have additional columns like 'S.N.', 'Name', 'Emis no', 'Final GPA', 'Total', etc.
$additionalColumns = 5; // Adjust this count based on your actual additional columns

// Calculate the total number of columns
$totalColumns = $totalSubjects + $additionalColumns;?>
            <table id="customers" style="border-collapse: collapse; border: 1px solid #000000;">
    <thead>
                <tr>                             

<?php  $totalColumns; ?>
<td colspan='<?php echo $totalColumns; ?>'><span style='border:none; text-align:center;'>

                                        <b><?= $schoolname ?></b> <br><?= $schooladdress ?><br> Estd: <?= $estd ?><br> <?= $examtype .' - '. $examyear ?><br>NG Report for Class <b>
                                            <?php echo implode(', ', $currentStudentData['class_names']); ?>
                                        </b>
                                </td>

                            </tr>
        <tr>
            <th>S.N.</th>
            <th>Symbol</th>
            <th>Name</th>
            <th>Sec</th>
            <th>NG Sub</th>
            <!-- Dynamically generate headers for failed subjects -->
            <?php 
            // Determine the maximum number of failed subjects for column headers
            $maxFailedSubjects = 0;
            foreach ($ledger as $student) {
                $failedSubjectsCount = 0;
                foreach ($student['subjects'] as $subject) {
                    $Obtmarks = $subject['marks'];
                    $fullmarks = $subject['theory_full_marks'];
                    $Passmark = $fullmarks * 0.35;
                    $grade = calculateGrade($Obtmarks / $fullmarks);
                    if ($Obtmarks < $Passmark || $grade == 'NG') {
                        $failedSubjectsCount++;
                    }
                }
                $maxFailedSubjects = max($maxFailedSubjects, $failedSubjectsCount);
            }
            for ($i = 1; $i <= $maxFailedSubjects; $i++) {
                echo "<th>Subject $i</th>";
            }
            ?>
        </tr>
    </thead>
    <tbody>
        <?php 
        $failedSN = 1;

        foreach ($ledger as $student) :
            $failedSubjects = [];

            foreach ($student['subjects'] as $subject) {
                $Obtmarks = $subject['marks'];
                $fullmarks = $subject['theory_full_marks'];
                $Passmark = $fullmarks * 0.35;
                $grade = calculateGrade($Obtmarks / $fullmarks);

                if ($Obtmarks < $Passmark || $grade == 'NG') {
                    $failedSubjects[] = $subject['subject_name'] ;
                }
            }

            if (!empty($failedSubjects)) :
                ?>
                <tr>
                    <td><?= $failedSN ?></td>
                    <td style="text-align:left; color: red; font-weight: bold;"><?= $student['symbol'] ?></td>
                    <td style="text-align:left;font-size:11px"><?= $student['name'] ?></td>
                    <td style="text-align:center; font-weight:bold"><?= $student['stdsection'] ?></td>
                    <td><?= count($failedSubjects) ?></td>
                    <?php
                    // Display failed subjects in separate columns
                    for ($i = 0; $i < $maxFailedSubjects; $i++) {
                        if (isset($failedSubjects[$i])) {
                            echo "<td style='text-align:left; font-size:10px'>" . $failedSubjects[$i] . "</td>";
                        } else {
                            echo "<td>-</td>"; // Empty column for students with fewer failed subjects
                        }
                    }
                    ?>
                </tr>
                <?php 
                $failedSN++;
            endif;
        endforeach; 
        ?>
    </tbody>
</table>

        <?php else : ?>
            <p>No data available for failed students in the selected class.</p>
        <?php endif; ?>
    </div>

    <script>
        function printLedger() {
          
            var printWindow = window.open('', '_blank');
            printWindow.document.write('<html><head><title>Mohanmaya Sec. School, Grade Ledger </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 = '90%';

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

        }
        function exportToExcel() {
            const table = document.getElementById("customers");
            const wb = XLSX.utils.table_to_book(table, {sheet: "Failed Students"});
            XLSX.writeFile(wb, "Failed_Students_Ledger.xlsx");
        }
    </script>
    <script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
</body>
</html>
