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

// Function to calculate Grade Point (GP)
function calculateGradePoint($obtainedMarks, $fullMarks, $lowerThreshold)
{
    $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; // Updated to 0.0 for internal marks
    } elseif ($MP >= 0.0001) {
        return 0;
    } else {
        return 0; // For ABSENT
    }
}

// Function to calculate Grade based on obtained marks
function calculateGrade($obtainedMarks, $fullMarks, $lowerThreshold)
{
    $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'; // Updated to NG for internal marks
    } elseif ($MP1 >= 0.0001) {
        return 'NG';
    } else {
        return 'Abs'; // For ABSENT
    }
}

?>

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Grade Sheet Generator</title>
    <script src="js/jquery/jquery-2.2.4.min.js"></script>

    <style>
        table {
            border-collapse: collapse;
            border-spacing: 0;
            width: 100%;
            border: 1px solid #ddd;
        }

        th {
            text-align: center;
            padding: 2px;
        }

        td {
            text-align: center;
            padding: 3px;
        }

        #employee {
            font-family: Arial, Helvetica, sans-serif;
            font-size: 12px;
            border-collapse: collapse;
            width: 100%;
        }

  
        #employee tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        #employee tr:hover {
            background-color: #ddd;
        }

        #employee th {
            padding-top: 4px;
            padding-bottom: 4px;
            text-align: left;
            background-color: #04AA6D;
            color: white;
        }

        #employee td {
            padding-top: 4px;
            padding-bottom: 4px;
            text-align: left;
            number-align: right;
            background-color: #;
            color: black;
            border-collapse: collapse;
            border: 1px solid black;
        }
   
    .dotted {
        display: inline-block;
        min-width: 400px;
        border-bottom:1.5px dotted #000;
        text-align: center;
        padding-bottom: 2px;
        margin: 0 4px;
        font-weight: bold;
    }
</style>
</head>

<body>

    <?php
    include("includes/db.php");

    $query = "SELECT * FROM schools LIMIT 1"; // Assuming you only have one school record in the database
    $result = $conn->query($query);
    $schoolDetails = $result->fetch_assoc();
    $logoPath = $schoolDetails['logo'];

    if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["class_id"]) && isset($_POST["exam_id"])) {
        $classId = $_POST['class_id'];
        $examId = $_POST['exam_id'];
        $issuedate = $_POST['issuedate'];
        $Chkdby = $_POST['checked_by'];
        $Preparedby = $_POST['prepared_by'];

        $classQuery = "SELECT class_name, section FROM classes WHERE class_id = $classId";
        $classResult = $conn->query($classQuery);
        $classDetails = $classResult->fetch_assoc();
        $className = $classDetails['class_name'];
        $sectionName = $classDetails['section'];

        $studentsQuery = "SELECT * FROM students WHERE Class_id = $classId";
        $studentsResult = $conn->query($studentsQuery);

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

// Check if $examtype is not already in uppercase
if ($examtype !== strtoupper($examtype)) {
    // Convert $examtype to uppercase
    $examtype = strtoupper($examtype);
}

// Now $examtype is guaranteed to be in uppercase



        while ($studentRow = $studentsResult->fetch_assoc()) {
            $studentId = $studentRow['student_id'];
            $studentName = $studentRow['name'];
            $stdsym = $studentRow['symbol_no'];
            $stdregd =  $studentRow['regd_id'];
            $stddob =  $studentRow['dob'];

            echo "<div class=''>";
            echo "<center>";
            echo "<div class='container' style='border:9px double #0f0f0f'>";
            echo "<center>";
            echo "<div>";
            echo "<table>";
            echo "<thead>";
            echo " <th colspan='3' style='text-align:left'>IEMIS CODE: " . $schoolDetails['school_code'] . "</th>";
            echo "</thead>";
            echo "<tbody>";

            echo "<td >";
            echo "<img src='$logoPath' alt='Logo' width='100' height='90'>";
            echo "</td>";
            echo "<center>";
            echo "<td >";
            echo "<span style='margin-left:-10%; font-size:30px'><strong><b>" . $schoolDetails['school_name'] . "</b></strong></span> <br>";
            echo "<span style='margin-left:-10%; font-size:25px''>" . $schoolDetails['address'] . "</span> <br>";
            echo "<span style='margin-left:-10%; font-size:20px'> ESTD: " . $schoolDetails['establishment_year'] . "    B.S.</span> <br>";
            echo "<span style='margin-left:-10%;font-size:18px'>Phone:" . $schoolDetails['phone'] . "</h4>";
            echo "</td>";
            echo "<tr>";
            echo "<td colspan='3' style='margin-bottom:-6%; font-size: 21px'><b><u> GRADE SHEET </u></b> </td>";
            echo "</tr>";
            echo "</center>";
            echo "</tbody>";

            echo "<tr> <td colspan='3' style='font-size:16px; text-align:left; padding:10px;'>
                <style>
    .dotted-name {
        display: inline-block;
        min-width: 400px;
        border-bottom: 1px dotted #000;
        text-align: center;
        padding-bottom: 2px;
        margin: 0 4px;
        font-weight: bold;
    }
    
       .dotted {
        display: inline-block;
        min-width: 140px;
        border-bottom: 1px dotted #000;
        text-align: center;
        padding-bottom: 2px;
        margin: 0 4px;
        font-weight: bold;
    }
</style>
THE GRADE(S) SECURED BY <span class='dotted-name'> $studentName </span><br>

DATE OF BIRTH <span class='dotted'> $stddob </span><br>

REGISTRATION NO. <span class='dotted'> $stdregd </span>  SYMBOL NO <span class='dotted'> $stdsym </span><br>

GRADE <span class='dotted'> $className </span>IN THE <span class='dotted'> $examtype </span> CONDUCTED IN <span class='dotted'>2082 B.S. (2025 A.D.)</span> ARE GIVEN BELOW.
 </td>";

            $attendanceQuery = "SELECT atten_day FROM attendance WHERE std_id = $studentId";
            $attendanceResult = $conn->query($attendanceQuery);

            $subjectQuery = "SELECT sub1, sub2, sub3, sub4, sub5, sub6 ,sub7 FROM studentsub WHERE student_id = $studentId AND class_id = $classId";
            $subjectResult = $conn->query($subjectQuery);
            $subjects = $subjectResult->fetch_assoc();

            $subjectIds = [];
            if ($subjects !== null) {
                $subjectIds = array_filter($subjects);
            }

            echo "<div class='container1'>";
            echo "<table class='employee'>";

            echo "<thead style='border:1px solid black; font-size:12px'>";
            echo "<tr style='border:1px solid black'>";
            echo "<th style='border:1px solid black'><strong>SUBJECT<br> CODE</strong> </th>";
            echo "<th width='';' style='border:1px solid black'><left><strong>SUBJECT NAME</strong></left> </th>";
            echo "<th style='border:1px solid black'><strong>CREDIT HOUR</strong> </th>";
            "<th style='border:1px solid black'><strong>CREDIT HOUR[total]</strong> </th>";
            echo "<th style='border:1px solid black'><strong>GRADE POINT</strong> </th>";
            "<th style='border:1px solid black'><strong>WGP [GP*CH]</strong> </th>";
            echo "<th style='border:1px solid black'><strong>GRADE</strong> </th>";
            echo "<th style='border:1px solid black'><strong>FINAL GRADE</strong> </th>";
            echo "<th style='border:1px solid black'><strong>REMARKS</strong> </th>";
            echo "</tr>";
            echo "</thead>";
            echo "<tbody>";

            $totalcredithour = 0;
            $totalwgp = 0;
            $totalSubjects = 0;
            $NGCount = 0;

            $displayedCHSum = 0; // Variable to store the total credit hour for displayed subjects

            foreach ($subjectIds as $subjectId) {
                $subjectCodeQuery = "SELECT subject_code FROM subjects WHERE subject_code = '$subjectId'";
                $subjectCodeResult = $conn->query($subjectCodeQuery);
                $subjectCodeRow = $subjectCodeResult->fetch_assoc();

                if ($subjectCodeRow !== null) {
                    $subjectCode = $subjectCodeRow['subject_code'];

                    $marksQuery = "SELECT subjects.subject_code, 
                                      subjects.subject_name, 
                                      subjects.theory_full_marks, 
                                      subjects.theory_credit_hour, 
                                      subjects.internal_full_marks, 
                                      subjects.internal_credit_hour, 
                                      marks.full_marks, marks.marks, 
                                      int_marks.internal_marks
                                      
                                FROM marks
                                INNER JOIN subjects ON marks.subject_id = subjects.id
                                INNER JOIN int_marks ON marks.student_id = int_marks.student_id AND marks.subject_id = int_marks.subject_id
                                WHERE marks.student_id = $studentId AND marks.class_id = $classId AND marks.exam_type = $examId AND subjects.subject_code = '$subjectCode'";
                    $marksResult = $conn->query($marksQuery);
                    $marks = $marksResult->fetch_assoc();

                    if ($marks !== null) {
                        $subjectName = $marks['subject_name'];
                        $creditHourTheory = $marks['theory_credit_hour'];
                        $fullMarksTheory = $marks['theory_full_marks'];
                        $marksTheory = $marks['marks'];
                        $creditHourInternal = $marks['internal_credit_hour'];
                        $fullMarksInternal = $marks['internal_full_marks'];
                        $marksInternal = $marks['internal_marks'];
                        $subtotalch = $creditHourTheory + $creditHourInternal;

                        // Calculate theory grade and grade point
                        $MPTheory = $marksTheory / $fullMarksTheory;
                        $GPTheory = number_format(calculateGradePoint($marksTheory, $fullMarksTheory, 0.35), 1);
                        $gradeTheory = calculateGrade($marksTheory, $fullMarksTheory, 0.35);

                        // Calculate internal grade and grade point
                        $MPInternal = $marksInternal / $fullMarksInternal;
                        $GPInternal = number_format(calculateGradePoint($marksInternal, $fullMarksInternal, 0.4), 1);
                        $gradeInternal = calculateGrade($marksInternal, $fullMarksInternal, 0.4);

                       if ($gradeTheory === 'NG' || $gradeTheory === 0 || $gradeInternal === 'NG' || $gradeInternal === 0) {
    // If either grade is 'NG' or 0, set $subGP to 'NG'
    $subGP = 'NG';

} else {
    // Otherwise, calculate $subGP normally
    $subGP = number_format(((($creditHourTheory * $GPTheory) + ($creditHourInternal * $GPInternal)) / ($subtotalch)), 2);
}
// If either grade is 'NG', increment NGCount
        if ($gradeTheory === 'NG' || $gradeInternal === 'NG') {
            $NGCount++;
        }
if ($gradeTheory === 'NG' || $gradeTheory === 0 || $gradeInternal === 'NG' || $gradeInternal === 0) {
    // If either grade is 'NG' or 0, set $subGrade to 'NG'
    $subGrade = 'NG';
} else {
    // Calculate $subGrade based on the provided conditions
    if ($subGP > 3.61 && $subGP <= 4.0) {
        $subGrade = 'A+';
    } elseif ($subGP > 3.2 && $subGP <= 3.6) {
        $subGrade = 'A';
    } elseif ($subGP > 2.81 && $subGP <= 3.2) {
        $subGrade = 'B+';
    } elseif ($subGP > 2.41 && $subGP <= 2.8) {
        $subGrade = 'B';
    } elseif ($subGP > 2.01 && $subGP <= 2.4) {
        $subGrade = 'C+';
    } elseif ($subGP > 1.61 && $subGP <= 2.0) {
        $subGrade = 'C';
    } elseif ($subGP == 1.6) {
        $subGrade = 'D';
    } else {
        $subGrade = 'NG';
    }
}

                    
                        $subGPsum = (($creditHourTheory * $GPTheory) + ($creditHourInternal * $GPInternal));
                        $totalwgp += $subGPsum;
                        $totalcredithour += $subtotalch;

                        // Display the grades in the table
                        echo "<tr>";
                        echo "<td style='border:1px solid black' rowspan='2'> $subjectCode </td>";
                        echo "<td style='text-align:left; border:1px solid black; font-size:12px'> $subjectName <b>[TH]</b></td>";
                        echo "<td style='border:1px solid black'> $creditHourTheory </td>";
                        "<td style='border:1px solid black' rowspan='2' > $subtotalch</td>"; // Corrected: $CHsum to $subtotalch
                        echo "<td style='border:1px solid black'>$GPTheory</td>";
                        $WgpsubTH = ($creditHourTheory * $GPTheory);
                        "<td style='border:1px solid black' rowspan='2' > $WgpsubTH</td>";
                        echo "<td style='border:1px solid black'> $gradeTheory  </td>";
                        echo "<td style='border:1px solid black' rowspan='2' > $subGrade </td>";
                        echo "<td style='border:1px solid black' rowspan='2' > </td>";
                        echo "</tr>";

                        // Second row for internal
                        echo "<tr>";
                        echo "<td style='text-align:left; border:1px solid black; font-size:12px'> $subjectName <b>[IN]</b></td>";
                        echo "<td style='border:1px solid black'> $creditHourInternal</td>";
                        echo "<td style='border:1px solid black'>$GPInternal</td>";
                        $WgpsubIN = ($creditHourInternal * $GPInternal);
                        "<td style='border:1px solid black' rowspan='2' >$WgpsubIN </td>";
                        echo "<td style='border:1px solid black'>$gradeInternal</td>";
                        echo "</tr>";

                        // Increment the displayed credit hour sum
                        $displayedCHSum += $subtotalch;
                    } else {
                        // Handle the case when marks are null
                    }
                } else {
                    // Handle the case when subject code is null
                }
            }

            // Display the total credit hour for displayed subjects
            // echo "<tr> <td colspan='2' style='border:1px solid black'> <b> Total Credit Hour for Displayed Subjects: $displayedCHSum </b></td></tr>";
if ($NGCount > 0 || $totalcredithour == 0) {
    // If any grade is 'NG' or total credit hour is 0, set $gpa to '-'
    $gpa = '-';
} else {
    // Otherwise, calculate GPA normally
    $gpa = number_format($totalwgp / $totalcredithour, 2);
}
            // // Calculate GPA
            // if ($totalcredithour != 0) {
            //     $gpa = number_format($totalwgp / $totalcredithour, 2);
            // } else {
            //     $gpa = 0.00;
            // }

           // Display the total WGP and credit hour
echo "<tr> <td colspan=7 style='border:1px solid black'> <b> GRADE POINT AVERAGE [GPA] : $gpa </b></td>";
                "<td style='border:1px solid black'>   $totalwgp</td> 
                    <td style='border:1px solid black'> $totalcredithour </td>
                        <td> Total 'NG' grades: $NGCount </td>";



            echo "<tr>
            
            <tr > <td colspan='7' style='text-align:left'> <b > EXTRA CREDIT SUBJECT </b> </td></tr>
            <tr style='height='100px' > 
            <td style='border:2px solid black; height='100px' >' </td>
           <td style='border:2px solid black; height='100px' > </td>
           <td style='border:2px solid black; height='100px' > </td>
           <td style='border:2px solid black; height='100px' > </td>
           <td style='border:2px solid black; height='100px' > </td>
           <td style='border:2px solid black; height='100px' > </td>
           
           <td style='border:2px solid black; height='100px'> </td>";
            echo "<td colspan='2' style='border:none'> </td>";
            while ($attendanceData = $attendanceResult->fetch_assoc()) {
                $atten_day = $attendanceData['atten_day'];
                echo "<td colspan='4'  rowspan='2' style='border:none; margin-top:-5px; text-align:left; font-size: 16px'> 
                        <b> Result: ...............   </b> <br><br>
                        Total Working Days: <b>223</b>  Days <br>
                         Total School Days: <b>212</b> Days  <br>
                         Total Present Days: <b>$atten_day</b> Days
                      </td>";
            }
            echo "<td style=''> </td>";
            echo "</tr>";
            echo "<tr>";
            echo "<td  rowspan='2' colspan='2'  style='border:none;' height='60px'> </td>";
            echo "<td colspan='4' style=' font-size: 14px'>  </td>";
            echo "</tr>";
            echo "<tr>";
            echo "<td colspan='4' style=' font-size: 14px'>   </td>";
            echo "<td style=''> </td>";
            echo "</tr>";

            echo "<tr>";
            echo "<td colspan='2'> Prepared By:...................................</td>";
            echo "</tr>";
            echo "<tr>";
            echo "<td colspan='2'> </td>";
            echo "<td></td>";
            echo "<td></td>";
            echo "<td colspan='3' rowspan='2'></td>";

            echo "</tr>";
                 echo "<tr>";
            echo "<td colspan='2'> </td>";
            echo "<td></td>";
            echo "<td></td>";
            echo "<td colspan='3' rowspan='2'> </td>";

            echo "</tr>";
                 echo "<tr>";
            echo "<td colspan='2'> </td>";
            echo "<td></td>";
            echo "<td></td>";
             echo "<td colspan='3' rowspan='2'> <img src='uploads/sign.png' alt='Logo' width='60' height='50'></td>";

            echo "</tr>";
                 echo "<tr>";
            echo "<td colspan='2'>Checked By:...................................  </td>";
            echo "<td></td>";
            echo "<td></td>";
            echo "<td colspan='3' rowspan='2'> </td>";

            echo "</tr>";
            echo "<tr>";
            echo "<td colspan='2'> </td>";

            echo "<td colspan='2' style='color:#babcbf'> school seal </td>";

            echo "</tr>";
            echo "<tr>";
            echo "<td colspan='2'>   Date of Issue: $issuedate B.S. </td>";
            echo "<td colspan='' </td> ";
            echo "<td colspan='1' > </td>";
            echo "<td colspan='4' style='border-top: 2px dotted; font-size:14px'>SHER BAHADUR SHRESTHA<br>HEAD TEACHER </td>";

            echo "</tr>";
            // echo "<tr>";
            // echo "<td colspan=4></td>";
            // echo "<td colspan=3  ></td>";
            // echo "</tr>";
            echo "<tr>";
            echo "<td colspan=7> NOTE :  ONE CREDIT HOUR EQUALS TO 32 WORKING HOURS. <br>
            INTERNAL (IN) : THIS COVERS THE PARTICIPATION, PRACTICAL/PROJECT WORKS, COMMUNITY WORKS, INTERNSHIP, PRESENTATIONS TERMINAL EXAMINATIONS. <br>
             THEORY(TH): THIS COVERS WRITTEN EXTERNAL EXAMINATION. </td> ";
            echo "</tr>";
            echo "<tr>";
            echo "<td colspan=2> Abs= ABSENT</td> ";
            echo "<td colspan=2>  </td> ";
            echo "<td colspan=3> W=WITHHELD </td> ";
            echo "</tr>";

            echo "</tfooter>";
            echo "</table>";
            echo "</div>";

            echo "</table>";
            echo "</div>";
            echo "</center>";
            echo "</div>";
            echo "</center>";
            echo "</span>";

            echo "</div>";
            // Add a page break after each student's marksheet
            echo "<div style='page-break-after: always;'></div>";
        }
    }
    ?>

    <script>
        function exportAsPDF() {
            // Code to export marksheets as PDF
            // You can use libraries like jsPDF to generate PDFs
            // For example:
            var doc = new jsPDF();
            doc.text("Marksheet content", 10, 10);
            doc.save('marksheet.pdf');
        }
    </script>
</body>

</html>
