<?php
// Include database connection code
include("includes/db.php");
Include('session_validator.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 'NG';
    } else {
        return 'Abs';
    }
}

// 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 and exam type
    $query = "SELECT students.student_id AS student_id, students.name AS student_name,
            studentsub.sub1, studentsub.sub2, studentsub.sub3,
            studentsub.sub4, studentsub.sub5, studentsub.sub6, studentsub.sub7,
            class_subject_mapping.subject_id, subjects.subject_name,
            COALESCE(marks.marks, '') AS marks,
            classes.class_name, classes.class_id, 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 class_subject_mapping ON classes.class_id = class_subject_mapping.class_id
        INNER JOIN subjects ON class_subject_mapping.subject_id = subjects.id
        LEFT JOIN marks ON students.student_id = marks.student_id 
            AND students.Class_id = marks.class_id 
            AND class_subject_mapping.subject_id = marks.subject_id
            AND marks.exam_type = '$examType'

        WHERE students.Class_id IN ($classIdString) 
        ORDER BY students.Student_id, class_subject_mapping.subject_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;

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

            
            // 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,
            ];

            // 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 lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <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">



           
    <script src="js/multiselect-dropdown.js"></script>

  <link rel="stylesheet" href="css/styleledger.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/lobipanel/lobipanel.min.css" media="screen" >
        <link rel="stylesheet" href="css/prism/prism.css" media="screen" >
        <link rel="stylesheet" href="css/select2/select2.min.css" >
        <link rel="stylesheet" href="css/main.css" media="screen" >
        <script src="js/modernizr/modernizr.min.js"></script>
  

    <title>Marks Obtained Ledger</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css">
    <style>
        /* Add your CSS styles here */
        #customers {
            overflow: auto;
            max-height: 500px;
            width: 100%; /* Adjust width as needed */
            margin-top: 20px; /* Add margin for better appearance */
            margin-left: 10px;
        }
    </style>
<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
        // Use setTimeout to ensure styles are applied before printing
            setTimeout(function () {
                printWindow.print();
                printWindow.onafterprint = function () {
                    printWindow.close();
                };
            }, 500);

        }
    </script>




</head>

<body class="top-navbar-fixed">
     <script>
        $(document).ready(function () {
            // DataTable initialization
            var table = $('#customers').DataTable();

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

                // // Sort data alphabetically by student name
                // exportData.sort(function (a, b) {
                //     return a[1].localeCompare(b[1]);
                // });

                // 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>

    

        <div class="main-wrapper">

            <!-- ========== TOP NAVBAR ========== -->
  <?php  include('includes/topbar.php');?> 
            <!-- ========== WRAPPER FOR BOTH SIDEBARS & MAIN CONTENT ========== -->
            <div class="content-wrapper">
                <div class="content-container">

                    <!-- ========== LEFT SIDEBAR ========== -->
                   <?php include('includes/leftbar.php');?>  
                    <!-- /.left-sidebar -->

                    <div class="main-page">

                     <div class="container-fluid">
                            <div class="row page-title-div">
                                <div class="col-md-6">
                                    <h2 class="title">Full Ledger</h2>
                                
                                </div>
                                
                                
                            </div>
                            <!-- /.row -->
                            <div class="row breadcrumb-div">
                                <div class="col-md-6">
                                    <ul class="breadcrumb">
                                        <li><a href="dashboard.php"><i class="fa fa-home"></i> Home</a></li>
                                        <li> Result </li>
                                        <li class="">Full Ledger</li>
                                    </ul>
                                </div>
                             
                            </div>
                            <!-- /.row -->
                        </div>
                        <div class="container-fluid">
                           
                        <div class="row">
                                    <div class="col-md-12">
                                        <div class="panel">
                                            <div class="panel-heading">
                                                <div class="panel-title">
                                                    <h5>Full Ledger</h5>
                                                </div>
                                            </div>
                                            <div class="panel-body">
            <!-- Update the form in marks_ledger.php -->
            <form action="finalledger.php" method="get">
                <div class="row">
                    <div class="form-group col-md-4">
                    <label for="exam_type">Select Exam Type:</label>
            <select name="exam_type" id="exam_type" class="form-control" required>
                          <option value="">Select Exam</option>
            <?php
            $queryexam = "SELECT id, exm_year, exam_type FROM examination_type";
            $resultexam = $conn->query($queryexam);

            while ($row = $resultexam->fetch_assoc()) {
                $selected = ($_GET['exam_type'] == $row['id']) ? 'selected' : '';
                echo "<option value='" . $row['id'] . "' $selected>" . $row['exm_year'] . " - " . $row['exam_type'] . "</option>";
            }
            ?>
        </select>
    </div>
     <div class="form-group col-md-4">
                    <label for="class_id">Select Class:</label>
                    <select name="class_id[]" id="class_id" class="form-control" style="width: 450px;" multiple>
                        <!-- Populate dropdown options dynamically from your database -->
                        <?php
                        // Include database connection code
                        include("includes/db.php");

                        // Query classes from the database
                        $classQuery = "SELECT class_id, class_name, section FROM classes";
                        $classResult = $conn->query($classQuery);

                        while ($row = $classResult->fetch_assoc()) {
                            echo "<option value='" . $row['class_id'] . "'>" . $row['class_name'] . " - " . $row['section'] . "</option>";
                        }

                        // Close the database connection
                        $conn->close();
                        ?>
                    </select>
                </div>
                <br>
                <div style="width: 150px;">
                    <button type="submit" class="btn btn-primary">Show Ledger</button>
                </div>
            </form>

          <div>
        <?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>




    <?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 * 4 + $additionalColumns;?>
               <br><br>
                  <table id="customers" style="border-collapse: collapse; border: 1px solid #000000;">
    <thead>
        <tr>                             

<?php echo $totalColumns; ?>
<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="150px" rowspan="2">Name</th>
            <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                <th colspan="4">
                    <center><?= $subject['subject_name']?></center>
                </th>
            <?php endforeach; ?>
            <!-- <th>Total</th> -->
            <th rowspan="2">TOTAL [WGP / CH]</th>
            <th rowspan="2">Total GPA </th> <!--[Totalwgpa/Total CH]-->
           <th rowspan="2">NG Count</th>  <!-- < Added NG count column -->
        </tr>
        <tr>
            <?php foreach ($ledger[0]['subjects'] as $subject) : ?>
                <th>Subject<br>[CH, FM]</th>
                <th>Marks [TH]</th>
                <th>GP</th>
                <th>Grade</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=" text-align: left;"><?= $student['name'] ?></td>
            <?php foreach ($student['subjects'] as $subject) : ?>
                <td style="width: 90px; font-size: 80%;">
                    <?= $subject['subject_code'] ?><br>
                    [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);



                // Increment NG count if the grade is 'NG'
                if ($grade === '<b><span style="color:#0011ff">NG</b>' || $grade === '<b><span style="color:red">Abs</b>') {
                    $NGCount++;
                }

                // // Display GP and grade
                echo '<td >' . $GP . '</td>';
                echo '<td class="<?= ($GP === "-" || $GP === "NG" || $GP === "Abs") ? "fail-cell" : ">' . $grade . '</td>';

                // Calculate total credit hours for this student
                $totalch1 += $CH1;
                ?>
            <?php endforeach; ?>

            <!-- <td><?= $student['total_marks'] ?></td> -->
            <td style="font-size:14px"><?= $student['totalwgp'] . ' / ' . $totalch1 ?></td>

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

            <td><?= $totalch1 > 0 ? $gpa : '' ?></td>
            <td><?= $NGCount  ?></td>
        </tr>
    <?php endforeach; ?>
</tbody>

<tr>
       <td></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>
                

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



            <script>
                $(document).ready(function () {
                    // Initialize DataTables
                    $('#customers').DataTable();
                });
            </script>

        <?php else : ?>
            <p>No data available for the selected class.</p>
        <?php endif; ?>
    </div>
   </div>
                                            </div>
                                        </div>
                                    </div>
                                    <!-- /.col-md-12 -->
                                </div>
                    </div>
                </div>
                <!-- /.content-container -->
            </div>
            <!-- /.content-wrapper -->
        </div>
        <!-- /.main-wrapper -->
        <script src="js/jquery/jquery-2.2.4.min.js"></script>
        <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>

        
            <!-- Add this modal at the end of your HTML body -->
<div class="modal fade" id="printConfirmationModal" tabindex="-1" role="dialog" aria-labelledby="printConfirmationModalLabel" aria-hidden="true">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="printConfirmationModalLabel">Print Confirmation</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&times;</span>
                </button>
            </div>
            <div class="modal-body">
                Are you sure you want to print the ledger?
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
                <button type="button" class="btn btn-primary" onclick="printTable()">Print</button>
            </div>
        </div>
    </div>
</div>

    </body>
</html>

