34915-vm/admin_submissions.php

<?php
session_start();
require_once 'includes/header.php';
require_once 'db/config.php';

// Check if user is logged in and is an admin (for simplicity, we'll just check if they are logged in)
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit;
}

$pdo = db();

// Handle status update
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submission_id']) && isset($_POST['status'])) {
    $submission_id = $_POST['submission_id'];
    $status = $_POST['status'];

    // Begin transaction
    $pdo->beginTransaction();

    try {
        // Update submission status
        $stmt = $pdo->prepare("UPDATE submissions SET status = ? WHERE id = ?");
        $stmt->execute([$status, $submission_id]);

        if ($status == 'approved') {
            // Check if a certificate already exists
            $stmt_check = $pdo->prepare("SELECT id FROM certificates WHERE submission_id = ?");
            $stmt_check->execute([$submission_id]);
            if ($stmt_check->rowCount() == 0) {
                // Generate a unique certificate code
                $certificate_code = uniqid('CERT-');
                $stmt_insert = $pdo->prepare("INSERT INTO certificates (submission_id, certificate_code) VALUES (?, ?)");
                $stmt_insert->execute([$submission_id, $certificate_code]);
            }
        } else {
            // If status is not 'approved', delete any existing certificate
            $stmt_delete = $pdo->prepare("DELETE FROM certificates WHERE submission_id = ?");
            $stmt_delete->execute([$submission_id]);
        }

        // Commit transaction
        $pdo->commit();
        echo "<div class='alert alert-success'>Submission status updated successfully.</div>";

    } catch (Exception $e) {
        // Rollback transaction on error
        $pdo->rollBack();
        echo "<div class='alert alert-danger'>Failed to update submission status: " . $e->getMessage() . "</div>";
    }
}

// Fetch all competitions
$stmt_competitions = $pdo->query("SELECT id, title FROM competitions ORDER BY start_date DESC");
$competitions = $stmt_competitions->fetchAll();

// Fetch all submissions with user and competition info
$stmt_submissions = $pdo->query("
    SELECT 
        s.id, 
        s.file_path, 
        s.uploaded_at, 
        s.status,
        u.name as user_name, 
        c.title as competition_title, 
        c.id as competition_id
    FROM 
        submissions s
    JOIN 
        users u ON s.user_id = u.id
    JOIN 
        competitions c ON s.competition_id = c.id
    ORDER BY 
        c.id, s.uploaded_at DESC
");
$submissions_by_competition = [];
while ($row = $stmt_submissions->fetch()) {
    $submissions_by_competition[$row['competition_id']][] = $row;
}

?>

<div class="container mt-5">
    <h1 class="mb-4">Competition Submissions</h1>

    <?php if (empty($competitions)): ?>
        <div class="alert alert-info">No competitions found.</div>
    <?php else: ?>
        <div class="accordion" id="competitionsAccordion">
            <?php foreach ($competitions as $competition): ?>
                <div class="accordion-item">
                    <h2 class="accordion-header" id="heading-<?php echo $competition['id']; ?>">
                        <button class="accordion-button collapsed" type="button" data-bs-toggle="collapse" data-bs-target="#collapse-<?php echo $competition['id']; ?>" aria-expanded="false" aria-controls="collapse-<?php echo $competition['id']; ?>">
                            <?php echo htmlspecialchars($competition['title']); ?>
                        </button>
                    </h2>
                    <div id="collapse-<?php echo $competition['id']; ?>" class="accordion-collapse collapse" aria-labelledby="heading-<?php echo $competition['id']; ?>" data-bs-parent="#competitionsAccordion">
                        <div class="accordion-body">
                            <?php if (isset($submissions_by_competition[$competition['id']]) && !empty($submissions_by_competition[$competition['id']])) : ?>
                                <table class="table table-striped">
                                    <thead>
                                        <tr>
                                            <th>User</th>
                                            <th>File</th>
                                            <th>Uploaded At</th>
                                            <th>Status</th>
                                            <th>Action</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($submissions_by_competition[$competition['id']] as $submission): ?>
                                            <tr>
                                                <td><?php echo htmlspecialchars($submission['user_name']); ?></td>
                                                <td><a href="<?php echo htmlspecialchars($submission['file_path']); ?>" target="_blank">View Submission</a></td>
                                                <td><?php echo date("F j, Y, g:i a", strtotime($submission['uploaded_at'])); ?></td>
                                                <td><?php echo htmlspecialchars($submission['status']); ?></td>
                                                <td>
                                                    <form action="admin_submissions.php" method="post">
                                                        <input type="hidden" name="submission_id" value="<?php echo $submission['id']; ?>">
                                                        <select name="status" class="form-select form-select-sm">
                                                            <option value="pending" <?php echo ($submission['status'] == 'pending') ? 'selected' : ''; ?>>Pending</option>
                                                            <option value="approved" <?php echo ($submission['status'] == 'approved') ? 'selected' : ''; ?>>Approved</option>
                                                            <option value="rejected" <?php echo ($submission['status'] == 'rejected') ? 'selected' : ''; ?>>Rejected</option>
                                                        </select>
                                                        <button type="submit" class="btn btn-primary btn-sm mt-1">Update</button>
                                                    </form>
                                                </td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>
                            <?php else: ?>
                                <p>No submissions for this competition yet.</p>
                            <?php endif; ?>
                        </div>
                    </div>
                </div>
            <?php endforeach; ?>
        </div>
    <?php endif; ?>

</div>

<?php require_once 'includes/footer.php'; ?>