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

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

// 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 ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $examType = isset($_POST['exam_id']) ? $_POST['exam_id'] : null;
    $classIds = isset($_POST['class_id']) ? $_POST['class_id'] : [];
    $ledger_type = isset($_POST['ledger_type']) ? $_POST['ledger_type'] : null;
    $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, 
                     symbolno_data.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
              LEFT JOIN symbolno_data ON students.student_id = symbolno_data.student_id 
                                       AND symbolno_data.exam_id = '$examType'
              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 symbolno_data.symbol_no, subjects.id";

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

    // Prepare data for display
    $ledger = [];
    $currentStudentId = null;
    $currentStudentData = null;
    $totalMarks = 0;
    $TotalWGP = 0;
    $TotalCH = 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;

        }

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

?>

<!-- Load jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

<!-- Load DataTables -->
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>

<!-- Load XLSX library -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.1/xlsx.full.min.js"></script>
<style>
    #customers {
    width: 85%;
    margin: 0 auto; /* Centers the table */
    border-collapse: collapse;
    border: 1px solid #000;
    table-layout: fixed; /* Ensures uniform column width */
}

#customers th, #customers td {
    border: 1px solid #000;
    padding: 8px;
    text-align: left;
    word-wrap: break-word; /* Prevents content from overflowing */
}
</style>
<!-- Your custom 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/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);

        }

</script>
<script>
    $(document).ready(function () {
        var table = $('#customers').DataTable({
            "pageLength": 100,
            "lengthMenu": [10, 25, 50, 100, 500, 1000]
        });

        
        // Export to Excel button functionality
        $('#exportExcelBtn').on('click', function () {
            var exportData = [];

            var headers = [];
            table.columns().every(function () {
                headers.push(this.header().textContent);
            });
            exportData.push(headers);

            table.rows().every(function () {
                var rowData = this.data();
                exportData.push(rowData);
            });

            var ws = XLSX.utils.aoa_to_sheet(exportData);
            var wb = XLSX.utils.book_new();
            XLSX.utils.book_append_sheet(wb, ws, 'Ledger');
            XLSX.writeFile(wb, 'Ledger_of_<?php echo $examtype." _ " . implode('_', $currentStudentData['class_names']); ?>.xlsx');
        });
    });
</script>



<button class="btn btn-info" onclick="printTable()">Print Table</button>
<button id="exportExcelBtn" class="btn btn-success">Export to Excel</button>
<br><br>
<!-- HTML Table -->
<table id="customers" >
    <thead>
        <tr style="text-align: center;">
            <td colspan="<?php echo count($currentStudentData['subjects']) * 4 + 6; ?>">
                <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 rowspan="2">Symbol</th>
            <th rowspan="2">Name</th>
            <th rowspan="2">Sec</th>
            <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                <th colspan="4"><center><?= $subject['subject_name'] ?></center></th>
            <?php endforeach; ?>
            <th rowspan="2">Total GPA</th>
            <th rowspan="2">Atten</th>
        </tr>
        
        <tr>
            <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                <th>Subject<br>[CH,FM]</th>
                <th>Marks [TH]</th>
                <th>GP</th>
                <th>Gd</th>
            <?php endforeach; ?>
          
        </tr>
    </thead>
    <tbody>
    <?php foreach ($ledger as $student) : ?>
        <?php
        // Initialize NG count for each student and total credit hours
        $NGCount1 = 0;
        $totalch1 = 0; // Total credit hours for this student
        ?>
        <tr>
            <td><?= $student['sn'] ?></td>
                <td style="color: red; font-size: 12px; text-align: left; font-weight: bold"><?= $student['symbol'] ?></td>
                <td style="text-align: left; font-size: 13px;"><?= $student['name'] ?></td>
                <td><?= $student['stdsection'] ?></td>

                    <?php foreach ($student['subjects'] as $subject) : ?>
                <td style=" font-size: 80%;">
                    <?= $subject['subject_code'] ?>[CH: <?= $subject['credit_hour'] ?>, FM: <?= $subject['theory_full_marks'] ?>]
                </td>
                <td><?= is_numeric($subject['marks']) ? $subject['marks'] : '' ?></td>
        
                    <?php
                    // Calculate MP, grade, and GP
                    $Obtmarks = $subject['marks'];
                    $fullmarks = $subject['theory_full_marks'];
                    $CH1 = is_numeric($subject['credit_hour']) ? $subject['credit_hour'] : 0;

                    // Calculate MP, grade, and GP
                    $MP = $fullmarks > 0 ? (float)$Obtmarks / (float)$fullmarks : 0;
                    $grade = calculateGrade($MP);
                    $GP = calculateGP($MP);

                    
                     $Passmark = $fullmarks * .35; 
                     ?>
                    
                <!-- Display GP and grade -->
                <td style="font-weight: bold; font-size: 14px;"> <?= $GP; ?> </td>
               <td style="font-weight: bold; font-size: 14px;"><?=  $grade; ?></td>
              <!-- <td><?= $Passmark; ?></td> -->
           <?php
                 if ($Obtmarks < $Passmark) {
                   $NGCount1++;
                 }

           
            
                // Calculate total credit hours for this student
                $totalch1 += $CH1;
                ?>
                
            <?php endforeach; ?>
<!-- <td><?= $NGCount1 ?> </td> -->
            <!--<td><?= $student['total_marks'] ?></td>-->
            <!--<td style="font-size:14px"><?= $student['totalwgp'] . ' / ' . $totalch1 ?></td>-->

            <?php
            // Calculate GPA
            if ($NGCount1 > 0) {
                $gpa = "-";
            } else {
                $gpa = round($student['totalwgp'] / $totalch1, 2);
            }
            ?>

            <td style="font-size:16px; font-weight:bold"><?= $totalch1 > 0 ? $gpa : '' ?></td>
            <td style="padding: -5%; font-size:14px"><?= $subject['atten_day'] ?></td>
        </tr>
    <?php endforeach; ?>
</tbody>

<tr>
       <td style="border:none;"></td>
   </tr>
   <tr></tr>
    

                    <tfoot style="border:none">
                        <tr>
         <td colspan="4" ><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="4"><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>
                

            </strong></td>
        <?php endforeach; ?>
    </tr>
</tfoot>
</table>

