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

// 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 'NG';
    } else {
        return 'Abs';
    }
}

// Function to calculate internal GP
function calculateInternalGP($MP, $creditHour, $passingGrade)
{
    if ($MP < $passingGrade) {
        return 0;
    }

    return $creditHour * (floor(($MP - $passingGrade) / 0.1) + 1);
}

// 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'])) {
    $classIds = $_GET['class_id'];

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

    // Query students, subjects, marks, and internal marks for the selected classes
    if (isset($_GET['class_id']) && is_array($_GET['class_id']) && isset($_GET['exam_id'])) {
    $classIds = $_GET['class_id'];
    $examId = $_GET['exam_id'];

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

    // Query students, subjects, marks, and internal marks for the selected classes and exam_type
    $query = "SELECT students.student_id AS student_id, students.name AS student_name, students.regd_id AS iemis_no, students.gender,
            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.internal_full_marks, subjects.theory_credit_hour, subjects.internal_credit_hour,
            COALESCE(marks.marks, '') AS marks,
            COALESCE(int_marks.internal_marks, '') AS internal_marks,
            classes.class_name, classes.class_id, classes.section,
            attendance.atten_day
            
            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
          LEFT JOIN int_marks ON students.student_id = int_marks.student_id AND students.Class_id = int_marks.class_id AND subjects.id = int_marks.subject_id
          LEFT JOIN attendance ON students.student_id = attendance.std_id AND marks.exam_type = attendance.exam_type
          WHERE students.Class_id IN ($classIdString) AND marks.exam_type = $examId AND int_marks.exam_type = $examId
          ORDER BY students.Student_id, subjects.id";


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

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



    $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'];
        $intMarks = $row['internal_marks'];
        $creditHourth = $row['theory_credit_hour'];
        $creditHourin = $row['internal_credit_hour'];
        $creditHour = $creditHourth + $creditHourin;
        $theoryFullMarks = $row['theory_full_marks'];
        $internalFullMarks = $row['internal_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['totalIntMarks'] = $totalIntMarks;
                $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'],
                'emis' => $row['iemis_no'],
                'gender' => $row['gender'],
                'subjects' => [],
                'total_marks' => 0,
                'totalIntMarks' => 0,
                'totalwgp' => 0,
                'totalch' => 0,
                'class_names' => array_values($classNames),
                'atten_day'=>$row['atten_day'],
            ];

            // Reset total marks for the new student
            $totalMarks = 0;
            $totalIntMarks = 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_credit_hour' => $creditHourth,
                'internal_credit_hour' => $creditHourin,
                'theory_full_marks' => $theoryFullMarks,
                'internal_full_marks' =>  $internalFullMarks,
                'marks' => $marks,
                'int_marks' => $intMarks,
                'atten_day'=> $attenDay,

            ];

            // Calculate GP and WGP for the current subject
            $Obtmarks = $marks + $intMarks; // Combine external and internal 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;
            }

            // Calculate internal GP for the current subject
            $internalGP = calculateInternalGP($MP, $creditHour, 0.4);

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

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

        // 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['totalIntMarks'] = $totalIntMarks;
        $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'];
} else {
    $schoolDetails = [];
}
$schoolname = $schoolDetails['school_name'];
$schooladdress = $schoolDetails['address'];
$estd = $schoolDetails['establishment_year'];
?>

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="css/bootstrap.min.css" media="screen">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.7/css/jquery.dataTables.min.css">
    <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/styleledger.css" type="text/css">
    <link rel="stylesheet" href="css/main.css" media="screen">
    <script src="js/multiselect-dropdown.js"></script>
    <script src="js/modernizr/modernizr.min.js"></script>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css">
    <title>Ledger</title>
</head>

<style>
    
</style>

<body class="top-navbar-fixed">
    <script>
        $(document).ready(function () {
            // DataTable initialization
            var table = $('#customers').DataTable({
                "scrollX": true,
                "paging": false,
                "searching": false,
                "info": false
            });

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

    <script>
        function printTable() {
            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/styleledger.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>

    <script>
        // Function to go back
        function goBack() {
            window.history.back();
        }
    </script>

    

    <div class="ledger">
        <?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>
            <button onclick="goBack()" class="btn btn-primary">Go Back</button>

            <br><br>
            <!-- Wrap the table in a container div for styling -->
            <div id="table-container">
                <table id="customers" class="display">
                        <thead>
                            <tr>
                             <?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 = 6; // Adjust this count based on your actual additional columns

// Calculate the total number of columns
$totalColumns = $totalSubjects * 7 + $additionalColumns;?>


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

                                        <b><?= $schoolname ?></b> <br><?= $schooladdress ?><br> Estd: <?= $estd ?><br> <?= $examtype .' - '. $examyear ?><br>Ledger for Class <b>
                                            <?php echo implode(', ', $currentStudentData['class_names']); ?>
                                        </b>
                                </td>
                            </tr>
                            <tr>
                                <th rowspan="2">S.N.</th>
                                <th width="" rowspan="2">Name</th>
                                <th width="" rowspan="2">Emis no</th>
                                <th width="" rowspan="2">Gender</th>
                              <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                                    <th colspan="4" style="font-size:10px;">
                                        <center><?= $subject['subject_name'] ?></center>
                                    </th>
                                <?php endforeach; ?>
                              <!--   <th rowspan="2">Std WGP</th>
                                <th rowspan="2">Sub CH</th> -->
                                <th rowspan="2">Final GPA</th>
                                <th rowspan="2">Total</th>
                            </tr>
                            <tr>
                                <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                                    <th style="font-size:11px">SUB</th>
                                    <!-- <th>Obt. Marks [TH]</th> -->
                                    <th><center>GP [TH]</center></th>
                                    <!-- <th>Internal Marks</th> -->
                                    <th><center>GP [IN]</center></th>
                                    <!-- <th>WGP</th> -->
                                    <th>GP</th>
                                <?php endforeach; ?>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($ledger as $student) : ?>
                                <?php
                                // Initialize NG count for each student and total credit hours
                                $NGCount = 0;
                                $totalch1 = 0; // Total credit hours for this student
                                ?>
                                <tr>

    <td><?= $student['sn'] ?></td>
    <td style="font-size: 13px; text-align: left;"><?= $student['name'] ?></td>
    <td style="font-size: 12px; padding: -2%;"><?= $student['emis'] ?></td>
    <td style="font-size: 12px; padding: -2%;"><?= $student['gender'] ?></td>
    <?php 
    $TotalSubGp = 0; // Initialize total for $SubGP for each student
    $TotalSubCH =0;
    foreach ($student['subjects'] as $subject) : ?>
        <td style="padding: -2%;">
            <strong><?= $subject['subject_code'] ?></strong>
            
        </td>
        <!-- <td><?= is_numeric($subject['marks']) ? $subject['marks'] : '' ?></td> -->

        <?php
        $TheoryGP = '';

        if (isset($subject['theory_full_marks']) && $subject['theory_full_marks'] > 0) {
            $TheoryGP = is_numeric($subject['marks']) ? calculateGP($subject['marks'] / $subject['theory_full_marks']) : '';
        }
        ?>

        <td style="padding: -2%;"><?= $TheoryGP ?></td>

        <!-- <td><?= is_numeric($subject['int_marks']) ? $subject['int_marks'] : '' ?></td> -->

        <?php
        $internalGP = '';

        if (isset($subject['internal_full_marks']) && $subject['internal_full_marks'] > 0) {
            $internalGP = is_numeric($subject['int_marks']) ? calculateGP($subject['int_marks'] / $subject['internal_full_marks'], $subject['internal_credit_hour'], 0.4) : '';
        }
        ?>

        <td style="padding: -2%;"><?= $internalGP ?></td>

        <?php  
        $THCH = 0;
        $INCH = 0;

        // Check if $TheoryGP is not 'NG' or 'Abs', then calculate $THCH
        if ($TheoryGP !== 'NG' && $TheoryGP !== 'Abs') {
            $THCH = $TheoryGP * $subject['theory_credit_hour'];
        }

        // Check if $internalGP is not 'NG' or 'Abs', then calculate $INCH
        if ($internalGP !== 'NG' && $internalGP !== 'Abs') {
            $INCH = $internalGP * $subject['internal_credit_hour'];
        }

        // Calculate $SubjectWGP
        $SubjectWGP = $THCH + $INCH;
        ?>

        <!-- <td><?= is_numeric($SubjectWGP) ? $SubjectWGP : ''  ?> </td> -->

        <?php
        $subCH = $subject['theory_credit_hour'] + $subject['internal_credit_hour'];

        $SubGP = "";

        // Check if either $TheoryGP or $internalGP is 'NG' or 'Abs', then set $SubGP to "-"
        if ($TheoryGP === 'NG' || $TheoryGP === 'Abs' || $internalGP === 'NG' || $internalGP === 'Abs') {
            $SubGP = "-";
        } else {
            // Calculate $SubGP only if neither $TheoryGP nor $internalGP is 'NG' or 'Abs'
            $SubGP = number_format(($SubjectWGP / $subCH), 2);
        }
        ?>

<td style=" padding: -2%;" class="<?= ($SubGP === '-' || $SubGP === 'NG' || $SubGP === 'Abs') ? 'fail-cell' : '' ?>">
    <span style="font-size: 105%; font-weight: bold;"><?= $SubGP ?></span>
</td>



        <?php 
        $TotalSubGp += is_numeric($SubjectWGP) ? $SubjectWGP : 0; // Summing up $SubGP for each subject 
        $TotalSubCH += is_numeric($subCH) ? $subCH : 0;
        endforeach; 
        ?>
  <!--       <td></td>
        <td></td>
        <td></td> -->
        <!-- <td><?= $TotalSubGp ?></td>
        <td><?= $TotalSubCH ?></td> -->
<?php 
$TotalGPA = number_format(($TotalSubGp / $TotalSubCH), 2); 
?>

<td class="<?= ($SubGP === '-' || $SubGP === 'NG' || $SubGP === 'Abs' || $TotalSubGp === 0) ? 'fail-cell' : '' ?>">
    <span style="font-size: 110%; font-weight: bold; padding: -2%;"><?= $TotalGPA ?></span>
</td>


        <!-- <td><?= number_format(($TotalSubGp / $TotalSubCH), 2)  ?></td> -->
        <td style="padding: -5%;"><?= $subject['atten_day'] ?></td>
    </tr>

                            <?php endforeach; ?>
                        </tbody>
                              
   <tr>
      <td colspan='<?php echo $totalColumns; ?>'><span style='border:none'>
          
       </td>
   </tr>

  



   <tr></tr>
                    <tfoot style="border:none">
                        <tr>
         <td colspan="2" ><strong>Subject Code</strong></td>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <td colspan="4" style="text-align:center"><strong><?= $subject['subject_code'] ?></strong></td>
        <?php endforeach; ?> 
    </tr>
 
    <tr>
        <td colspan="2"><strong>Subject Name</strong></td>
        <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
            <td colspan="4" style="text-align:center; font-size: 12px;"><strong><?= $subject['subject_name'] ?><br>
                [CREDIT HOUR <?= $subject['theory_credit_hour'] ?>/<?= $subject['internal_credit_hour'] ?>]


            </strong></td>
        <?php endforeach; ?>
    </tr>
                <!-- New section for Prepared By, Checked By, and Verified By -->
                <tr style="border:none">
                    <td colspan="8" style="border:none">
                        <br>
                        <strong>Prepared By:</strong> _______________________
                       </td>
                        <td colspan="8" style="border:none">
                        <strong>Checked By:</strong> ________________________
                       </td>
                        <td colspan="8" style="border:none">

                       <strong>Verified By:</strong> _______________________
                    </td>
                </tr>
            </tfoot> 
             </table>
           </div>

            <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
            <script src="https://cdn.datatables.net/1.11.7/js/jquery.dataTables.min.js"></script>
            <!-- Include your existing script tags for Bootstrap, DataTables initialization, and other scripts -->
            <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>

            <script>
                // Initialize DataTable
                $(document).ready(function () {
                    $('#customers').DataTable({
                        "scrollX": true,
                        "scrollY": true,
                        "paging": false,
                        "searching": false,
                        "info": false
                    });
                });
            </script>

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

</html>