<?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: 8px;
        }

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

        #employee td,
        #employee th {
            border: 1px solid black;
            padding: 8px;
        }

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

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

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

        #employee td {
            padding-top: 10px;
            padding-bottom: 10px;
            text-align: left;
            number-align: right;
            background-color: #;
            color: black;
            border-collapse: collapse;
            border: 1px solid black;
        }
    </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);
}
            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:-4%;'><h3><b><u> GRADE SHEET </u></b></h3> </td>";
                echo "</tr>";
                echo "</center>";
                echo "</tbody>";

              echo "<tr> <td colspan='3' style='font-size:16px; text-align:left; padding:10px;'>
                THE GRADE(S) SECURED BY <b>$studentName</b>  DATE OF BIRTH <b> $stddob </b> IEMIS REGD. NO. <b> $stdregd </b> 
                SYMBOL NO <b> $stdsym </b>  GRADE : <b>$className </b> SECTION: <b> $sectionName </b> IN THE <b> $examtype  
                </b> CONDUCTED BY SCHOOL IN <b> 2080 B.S. (2024 AD.) </b> 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 = array_filter($subjects);

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

                echo "<thead style='border:1px solid black; font-size:14px'>";
                echo "<tr style='border:1px solid black'>";
                echo "<th style='border:1px solid black'><strong>SUBJECT CODE</strong> </th>";
                echo "<th width='190px;' style='border:1px solid black'><left><strong>SUBJECT NAME</strong></left> </th>";
                echo "<th style='border:1px solid black'><strong>CREDIT HOUR</strong> </th>";
                echo "<th style='border:1px solid black'><strong>GRADE POINT</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>REMARK</strong> </th>";
                echo "</tr>";
                echo "</thead>";
                echo "<tbody>";

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

                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, marks.full_marks, marks.marks
                                        FROM marks
                                        INNER JOIN subjects ON marks.subject_id = subjects.id
                                        WHERE marks.student_id = $studentId AND marks.class_id = $classId AND subjects.subject_code = '$subjectCode'";
                        $marksResult = $conn->query($marksQuery);
                        $marks = $marksResult->fetch_assoc();

                        if ($marks !== null) {
                            $subjectName = $marks['subject_name'];
                            $creditHour = $marks['theory_credit_hour'];
                            $fullMarks = $marks['theory_full_marks'];
                            $marks = $marks['marks'];

                            $Obtmarks = $marks;
                            $MP = $Obtmarks / $fullMarks;
                            $GP1 = round(($MP * 4), 2);
                            $grade = '';
                            $GP = '';

                            if ($MP >= 0.9) {
                                $grade = 'A+';
                                $GP = '4.0';
                            } elseif ($MP >= 0.8) {
                                $grade = 'A';
                                $GP = '3.6';
                            } elseif ($MP >= 0.7) {
                                $grade = 'B+';
                                $GP = '3.2';
                            } elseif ($MP >= 0.6) {
                                $grade = 'B';
                                $GP = '2.8';
                            } elseif ($MP >= 0.5) {
                                $grade = 'C+';
                                $GP = '2.4';
                            } elseif ($MP >= 0.4) {
                                $grade = 'C';
                                $GP = '2.0';
                            } elseif ($MP >= 0.35) {
                                $grade = 'D';
                                $GP = '1.6';
                            } elseif ($MP >= 0.0001) {
                                $grade = 'NG';
                                $GP = '0';
                            } else {
                                $grade = 'Abs';
                            }

                            if ($grade == 'NG' || $GP == '0') {
                                $grade = 'NG';
                                $GP = '-';
                                $gpa = '-';
                                $wgp = 0;
                                if ($grade === 'NG') {
                                    $NGCount++;
                                }
                            } elseif ($MP == 0 || $grade == 'Abs') {
                                $grade = 'Abs';
                            } else {
                                $wgp = $creditHour * $GP;
                                $totalcredithour += $creditHour;
                                $totalwgp += $wgp;
                                $totalSubjects++;
                            }

                            if ($NGCount > 0) {
                                $gpa = "-";
                            } else {
                                $gpa = number_format(($totalwgp / $totalcredithour), 2);
                            }

                            echo "<tr>";
                            echo "<td  style='border:1px solid black'> $subjectCode </td>";
                            echo "<td style='text-align:left; border:1px solid black; font-size:13px'> $subjectName </td>";
                            echo "<td style='border:1px solid black'> $creditHour</td>";
                            echo "<td style='border:1px solid black'>$GP</td>";
                            echo "<td style='border:1px solid black'> $grade  </td>";
                            echo "<td style='border:1px solid black'> $grade</td>";
                            echo "<td style='border:1px solid black'> </td>";

                            echo "</tr>";
                        } else {
                            // Handle the case when marks are null
                        }
                    } else {
                        // Handle the case when subject code is null
                    }
                }

                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>";
                echo "<td colspan='2' style='border:1px solid black'>Remarks </td>";
                while ($attendanceData = $attendanceResult->fetch_assoc()) {
                    $atten_day = $attendanceData['atten_day'];
                    echo "<td colspan='4'  rowspan='2' style='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:1px solid black;' 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:$Preparedby</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'> </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></td>";
                echo "<td colspan='' style='border-top: 2px dotted; font-size:14px'>$Chkdby <br> Checked By</td> ";
                echo "<td colspan='2' > </td>";
                echo "<td colspan='3' style='border-top: 2px dotted; font-size:14px'>SHER BAHADUR SHRESTHA<br>HEAD TEACHER <br> Date of Issue: $issuedate B.S.</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. </td> ";
                echo "</tr>";
                echo "<tr>";
                echo "<td colspan=3> Abs= ABSENT</td> ";
                echo "<td colspan=3> W=WITHHELD </td> ";
                // echo "<td colspan=3> k = CONSTANT FACTOR </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>
