📁 File Manager
Editing:
/home/wvl7mof7402e/www/remindyourdata.live/emply/offerlatterlist.php
<?php include('dbconnect.php'); $limit = 10; $page = isset($_GET['page']) ? $_GET['page'] : 1; $start_from = ($page - 1) * $limit; $search_query = ''; if (isset($_GET['search'])) { $passport_no = $_GET['passport_no']; $search_query = " AND j.passport_no LIKE '%$passport_no%'"; } $applicants_sql = "SELECT j.id, j.name, j.passport_no, j.position, j.job_country, j.reference, ol.offer_status, ol.offer_letter_file FROM job_applicants j LEFT JOIN offer_letters ol ON j.id = ol.applicant_id WHERE 1=1 $search_query LIMIT $start_from, $limit"; $applicants_result = $con->query($applicants_sql); $total_sql = "SELECT COUNT(*) FROM job_applicants j LEFT JOIN offer_letters ol ON j.id = ol.applicant_id WHERE 1=1 $search_query"; $total_result = $con->query($total_sql); $total_rows = $total_result->fetch_row()[0]; $total_pages = ceil($total_rows / $limit); if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['applicant_id'])) { $applicant_id = $_POST['applicant_id']; $offer_status = $_POST['offer_status']; $offer_letter_file = NULL; if (isset($_FILES['offer_letter_file']) && $_FILES['offer_letter_file']['error'] == 0) { $upload_dir = 'uploads/offer_letters/'; $file_name = basename($_FILES['offer_letter_file']['name']); $file_path = $upload_dir . $file_name; if (!file_exists($upload_dir)) { mkdir($upload_dir, 0777, true); } if (move_uploaded_file($_FILES['offer_letter_file']['tmp_name'], $file_path)) { $offer_letter_file = $file_path; } } $sql = "INSERT INTO offer_letters (applicant_id, offer_status, offer_letter_file) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE offer_status = ?, offer_letter_file = ?"; if ($stmt = $con->prepare($sql)) { $stmt->bind_param("issss", $applicant_id, $offer_status, $offer_letter_file, $offer_status, $offer_letter_file); if ($stmt->execute()) { echo "<script>alert('Offer letter status updated successfully.');</script>"; } else { echo "<script>alert('Error: " . $stmt->error . "');</script>"; } $stmt->close(); } else { echo "<script>alert('Error preparing query: " . $con->error . "');</script>"; } } if (isset($_POST['export'])) { $export_sql = "SELECT j.name, j.passport_no, j.position, j.job_country, j.reference, ol.offer_status, ol.offer_letter_file FROM job_applicants j LEFT JOIN offer_letters ol ON j.id = ol.applicant_id $search_query"; $export_result = $con->query($export_sql); header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="offer_letters.csv"'); $output = fopen('php://output', 'w'); fputcsv($output, ['Name', 'Passport No', 'Position', 'Job Country', 'Reference', 'Offer Status', 'Offer Letter File']); while ($row = $export_result->fetch_assoc()) { fputcsv($output, $row); } fclose($output); exit; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Offer Letter Management</title> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <style> body { margin: 0; font-family: 'Segoe UI', sans-serif; background-color: #eef1f5; } header { background-color: #2c3e50; color: white; padding: 15px 20px; display: flex; justify-content: space-between; align-items: center; flex-wrap: wrap; } header h2 { margin: 0; font-size: 22px; } .logout-btn { background-color: #e74c3c; color: white; padding: 8px 14px; border: none; border-radius: 6px; font-size: 14px; cursor: pointer; } .logout-btn:hover { background-color: #c0392b; } .container { width: 95%; max-width: 1100px; margin: 30px auto; background-color: #fff; padding: 25px; border-radius: 10px; box-shadow: 0 4px 16px rgba(0, 0, 0, 0.1); } h2, h3 { text-align: center; margin-bottom: 20px; } .search-container, .form-container { margin: 20px 0; padding: 15px; background-color: #f7f7f7; border-radius: 8px; } input[type="text"], input[type="file"], select { width: 100%; padding: 10px; margin: 10px 0 15px; border: 1px solid #ccc; border-radius: 4px; } input[type="submit"], .edit-btn { padding: 10px 15px; background-color: #007bff; color: white; border: none; border-radius: 5px; cursor: pointer; font-weight: bold; } input[type="submit"]:hover, .edit-btn:hover { background-color: #0056b3; } table { width: 100%; border-collapse: collapse; margin-top: 15px; } table th, table td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; } table th { background-color: #007bff; color: white; } table tr:nth-child(even) { background-color: #f9f9f9; } .status-received { color: green; font-weight: bold; } .status-rejected { color: red; font-weight: bold; } .pagination { text-align: center; margin-top: 25px; } .pagination a { display: inline-block; padding: 6px 12px; margin: 0 4px; background-color: #007bff; color: white; text-decoration: none; border-radius: 4px; } .pagination a:hover { background-color: #0056b3; } .no-records { text-align: center; color: #555; font-style: italic; } @media screen and (max-width: 768px) { table, thead, tbody, th, td, tr { display: block; } table thead { display: none; } table td { position: relative; padding-left: 50%; border: none; border-bottom: 1px solid #eee; } table td::before { position: absolute; top: 12px; left: 12px; width: 45%; padding-right: 10px; font-weight: bold; white-space: nowrap; } table td:nth-of-type(1)::before { content: "Name"; } table td:nth-of-type(2)::before { content: "Passport No"; } table td:nth-of-type(3)::before { content: "Position"; } table td:nth-of-type(4)::before { content: "Job Country"; } table td:nth-of-type(5)::before { content: "Reference"; } table td:nth-of-type(6)::before { content: "Offer Status"; } table td:nth-of-type(7)::before { content: "Offer Letter File"; } table td:nth-of-type(8)::before { content: "Actions"; } } </style> </head> <body> <header> <h2>Offer Letter Management</h2> <form method="POST" action="logout.php"> <button class="logout-btn">Logout</button> </form> </header> <div class="container"> <div class="search-container"> <form method="GET" action=""> <label for="passport_no">Search by Passport No:</label> <input type="text" name="passport_no" placeholder="Enter passport number" value="<?php echo isset($_GET['passport_no']) ? $_GET['passport_no'] : ''; ?>"> <input type="submit" name="search" value="Search"> </form> </div> <form method="POST" action=""> <input type="submit" name="export" value="Export Data as CSV"> </form> <table> <thead> <tr> <th>Name</th> <th>Passport No</th> <th>Position</th> <th>Job Country</th> <th>Reference</th> <th>Offer Status</th> <th>Offer Letter</th> <th>Actions</th> </tr> </thead> <tbody> <?php if ($applicants_result->num_rows > 0): ?> <?php while ($row = $applicants_result->fetch_assoc()): ?> <tr> <td><?= $row['name']; ?></td> <td><?= $row['passport_no']; ?></td> <td><?= $row['position']; ?></td> <td><?= $row['job_country']; ?></td> <td><?= $row['reference']; ?></td> <td class="<?= $row['offer_status'] == 'Received' ? 'status-received' : ($row['offer_status'] == 'Rejected' ? 'status-rejected' : ''); ?>"> <?= $row['offer_status'] ?: 'Pending'; ?> </td> <td> <?= $row['offer_letter_file'] ? "<a href='{$row['offer_letter_file']}' target='_blank'>Download</a>" : 'Not Uploaded'; ?> </td> <td> <a href="?edit=<?= $row['id']; ?>"><button class="edit-btn">Edit</button></a> </td> </tr> <?php endwhile; ?> <?php else: ?> <tr><td colspan="8" class="no-records">No records found</td></tr> <?php endif; ?> </tbody> </table> <div class="pagination"> <?php for ($i = 1; $i <= $total_pages; $i++): ?> <a href="?page=<?= $i; ?>"><?= $i; ?></a> <?php endfor; ?> </div> <?php if (isset($_GET['edit'])) { $edit_id = $_GET['edit']; $applicant_sql = "SELECT j.id, j.name, j.passport_no, j.position, j.job_country, j.reference, ol.offer_status, ol.offer_letter_file FROM job_applicants j LEFT JOIN offer_letters ol ON j.id = ol.applicant_id WHERE j.id = $edit_id"; $applicant_result = $con->query($applicant_sql); $applicant = $applicant_result->fetch_assoc(); ?> <div class="form-container"> <h3>Edit Offer Letter Status</h3> <form method="POST" action="" enctype="multipart/form-data"> <input type="hidden" name="applicant_id" value="<?= $applicant['id']; ?>"> <label for="offer_status">Offer Status:</label> <select name="offer_status" required> <option value="Received" <?= $applicant['offer_status'] == 'Received' ? 'selected' : ''; ?>>Received</option> <option value="Rejected" <?= $applicant['offer_status'] == 'Rejected' ? 'selected' : ''; ?>>Rejected</option> <option value="Pending" <?= $applicant['offer_status'] == 'Pending' ? 'selected' : ''; ?>>Pending</option> </select> <label for="offer_letter_file">Upload New Offer Letter (optional):</label> <input type="file" name="offer_letter_file" accept="application/pdf"> <input type="submit" value="Update Status"> </form> </div> <?php } ?> </div> </body> </html>
Save
Cancel