<?php
include 'dbclass/dbclass.php';

// Establish connection to the database
// $con = new mysqli("localhost", "root", "", "smreader_kpamajeedmla");
$con = new mysqli('localhost', 'gdhhsxmy_kpamajeedmla', 'kpamajeedmla', 'gdhhsxmy_kpamajeedmla');

// Check connection
if ($con->connect_error) {
    die('Connection failed: ' . $con->connect_error);
}

// SQL query to select all data from the students table
$sql    = 'SELECT students.*, panchayath.panchayath, ward.ward FROM students INNER JOIN panchayath ON students.panchayath = panchayath.id INNER JOIN ward ON students.ward = ward.id;';
$result = $con->query($sql);

if ($result->num_rows > 0) {
    ?>

    <center>
        <h1 class="p-4">REGISTERED STUDENTS</h1>
    </center>
    <div class="d-none d-md-flex justify-content-center p-4">
        <input type="text" id="searchInput" onkeyup="searchTable()" placeholder="Search..." class="form-control" style="width: 50%; max-width: 600px; padding: 10px; font-size: 16px; border: 2px solid #ced4da; border-radius: 0.25rem;">
      <button class="btn btn-primary" onclick="exportToExcel()">Export to Excel</button>
  </div>

    <div class="table-responsive">
        <table id="example" class="table align-middle mb-0 bg-white w-100 center">
            <thead class="bg-light">
                <tr>
                    <th>S.NO</th>
                    <th>PHOTO</th>
                    <th>NAME</th>
                    <th>EXAM</th>
                    <th>REGNUMBER</th>
                    <th>ADDRESS</th>
                    <th>PHONE NO.</th>
                    <th>PANCHAYATH</th>
                    <th>WARD</th>
                    <th>MARKLIST</th>
                    <th>EDIT</th>
                    <th>DOWNLOAD</th>
                </tr>
            </thead>
            <tbody id="markdetails">
            <?php
            // Output data of each row
            while ($row = $result->fetch_assoc()) {
                echo '<tr>
                <td class="serial-number" >' . $row['id'] . '</td>
                <td>
                    <div class="d-flex align-items-center">';

                if (!empty($row['photo'])) {
                    // $row["photo"] = 'path_to_your_image.jpg'; // Example path to image
                    echo '<div class="photo-container" onclick="downloadImage(\'' . $row['photo'] . '\', \'student_photo.jpg\')">';
                    echo '<img src="' . $row['photo'] . '" alt="Student Photo" />';
                    echo '<div class="icon"><button>⬇️</button></div>'; // You can use any icon here, e.g., from FontAwesome
                    echo '</div>';
                } else {
                    echo '<i class="fa fa-user" style="font-size:45px;"></i>';
                }
                echo '</div>
                </td>
                <td>' . $row['name'] . '</td>
                <td>' . $row['exam'] . '</td>
                <td>' . $row['regnumber'] . '</td>
                <td>' . $row['address'] . '</td>
                <td>' . $row['number'] . '</td>
                <td>' . $row['panchayath'] . '</td>
                <td>' . $row['ward'] . '</td>
                <td>';

                if (!empty($row['marklist'])) {
                    echo '<a id="marklistLink" style="text-decoration:none;color:white;" href="' . $row['marklist'] . '"><button type="button" class="btn btn-danger btn-sm btn-rounded text-white"><i class="fa fa-eye"></i></button></a>';
                } else {
                }
                echo '</td>
                 <form action="edit.php" method="GET">
                <td>
                   
                        <input type="hidden" name="id" value="' . $row['id'] . '">
                        <button type="submit" class="btn btn-primary btn-sm btn-rounded"><i class="fa fa-pen"></i></button>
                    
                </td>
                </form>
                <form>
                <td>
                <input type="hidden" name="id" value="' . $row['id'] . '">
<button type="button" class="downloadButton btn btn-primary btn-sm btn-rounded justify-content-center" data-id="' . $row['id'] . '">
    <i class="fa fa-download"></i>
</button>
                   </td>
                   </form>
            </tr>';
            }

    // End the table
    echo '</tbody>
    </table>';
} else {
    echo '0 results';
}
// Close database connection
$con->close();
?>
    </div>
    
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.0/xlsx.full.min.js"></script>
    <script>
function exportToExcel() {
    const table = document.getElementById('example');
    const headers = table.querySelectorAll('thead th');

    // Find the indices of columns to remove
    let photoColumnIndex = -1;
    let marklistColumnIndex = -1;
    let editColumnIndex = -1;
    let downloadColumnIndex = -1;
    Array.from(headers).forEach((th, index) => {
        const textContent = th.textContent.trim().toUpperCase();
        if (textContent == 'PHOTO') {
            photoColumnIndex = index;
        } else if (textContent == 'MARKLIST') {
            marklistColumnIndex = index;
        } else if (textContent == 'EDIT') {
            editColumnIndex = index;
        } else if (textContent == 'DOWNLOAD') {
            downloadColumnIndex = index;
        }
    });

    // Create a new table to hold the data
    const newTable = document.createElement('table');
    const newHeaderRow = document.createElement('tr');

    // Remove unwanted columns from the headers and add the rest to the new table
    Array.from(headers).forEach((th, index) => {
        if (index != photoColumnIndex && index != marklistColumnIndex && index != editColumnIndex && index != downloadColumnIndex) {
            const newTh = document.createElement('th');
            newTh.textContent = th.textContent;
            newHeaderRow.appendChild(newTh);
        }
    });

    newTable.appendChild(newHeaderRow);

    // Copy rows from the original table to the new table, omitting unwanted cells
    const rows = table.querySelectorAll('tbody tr');
    rows.forEach(row => {
        const newRow = document.createElement('tr');
        Array.from(row.cells).forEach((cell, index) => {
            if (index != photoColumnIndex && index != marklistColumnIndex && index != editColumnIndex && index != downloadColumnIndex) {
                const newCell = document.createElement('td');
                newCell.textContent = cell.textContent;
                newRow.appendChild(newCell);
            }
        });
        newTable.appendChild(newRow);
    });

    // Convert the new table to Excel format
    const wb = XLSX.utils.table_to_book(newTable, { sheet: "Sheet JS" });

    // Download the Excel file
    return XLSX.writeFile(wb, 'students.xlsx');
}
 </script>

    <script>
        document.querySelectorAll('.downloadButton').forEach(button => {
            button.addEventListener('click', function() {
                var id = this.getAttribute('data-id');
               
                if (window.screen.width > 500) {
                    window.location.href = 'ticket.php?id=' + id;
                } else {
                    window.location.href = 'ticket-mobile.php?id=' + id;
                }
            });
        });
    </script>
    <style>
        .photo-container {
            position: relative;
            display: inline-block;
            width: 45px;
            height: 45px;
        }
        .photo-container img {
            width: 100%;
            height: 100%;
        }
        .icon {
            position: absolute;
            top: 50%;
            left: 50%;
            transform: translate(-50%, -50%);
            display: none;
            color: white;
            font-size: 24px;
        }
        .photo-container:hover .icon {
            display: block;
        }
    </style>
    <script>
        function searchTable() {
            var input, filter, table, tr, td, i, j, txtValue;
            input = document.getElementById("searchInput");
            filter = input.value.toUpperCase();
            table = document.getElementById("markdetails");
            tr = table.getElementsByTagName("tr");
            var visibleRowCount = 0; // Counter for visible rows

            // Remove the "No data available" row if it exists
            var lastRow = tr[tr.length - 1];
            if (lastRow && lastRow.getElementsByTagName("td")[0].textContent == "No data available") {
                table.deleteRow(-1);
            }

            // Loop through all table rows
            for (i = 0; i < tr.length; i++) {
                tr[i].style.display = "none"; // Hide all rows initially
            }

            for (i = 0; i < tr.length; i++) {
                td = tr[i].getElementsByTagName("td");
                var isVisible = false; // Flag to check if row is visible after search

                // Loop through all table data cells in the row
                for (j = 0; j < td.length; j++) {
                    var cell = td[j];
                    if (cell) {
                        txtValue = cell.textContent || cell.innerText;

                        // Check if the current cell's value matches the search filter
                        if (txtValue.toUpperCase().indexOf(filter) > -1) {
                            isVisible = true; // Row is visible
                            break; // Break the loop if match found
                        }
                    }
                }

                // Check the photo src attribute if not already matched
                if (!isVisible) {
                    var img = tr[i].getElementsByTagName("img")[0];
                    if (img && img.src.toUpperCase().indexOf(filter) > -1) {
                        isVisible = true; // Row is visible if photo matches
                    }
                }

                // Update the display status based on visibility
                if (isVisible) {
                    tr[i].style.display = "";
                    visibleRowCount++; // Increment the counter for visible rows
                    tr[i].getElementsByClassName("serial-number")[0].textContent = visibleRowCount; // Update serial number
                }
            }

            // Check if no rows are visible after search
            if (visibleRowCount == 0) {
                // Create a new row and cell
                var newRow = table.insertRow(-1);
                var newCell = newRow.insertCell(0);

                // Span the cell across all columns
                newCell.colSpan = tr[0].getElementsByTagName("td").length;
                newCell.textContent = "No data available"; // Set the cell text
            }
        }
    </script>
    <script>
        function downloadImage(imageSrc, imageName) {
            const link = document.createElement('a');
            link.href = imageSrc;
            link.download = imageName;
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    </script>

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.3/css/all.min.css" />
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>