false, 'message' => 'User ID is required']); exit; } $user_id = (int)$_GET['user_id']; $db = getDbConnection(); try { $stmt = $db->prepare("SELECT membership_type FROM users WHERE id = ? AND status = 'approved'"); $stmt->execute([$user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); if (!$user) { echo json_encode(['success' => false, 'message' => 'User not found or not approved']); exit; } $membership_type = $user['membership_type']; $stmt = $db->prepare("SELECT designation, designation_hindi FROM membership_designations WHERE membership_type = ? AND status = 'active' ORDER BY sort_order"); $stmt->execute([$membership_type]); $designations = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode([ 'success' => true, 'designations' => $designations ]); } catch (Exception $e) { logError('Error fetching designations: ' . $e->getMessage()); echo json_encode(['success' => false, 'message' => 'Failed to fetch designations']); } exit; } // Handle AJAX request for user photo if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['ajax']) && $_GET['ajax'] === 'get_user_photo') { header('Content-Type: application/json'); $user_id = (int)$_GET['user_id']; $db = getDbConnection(); try { $stmt = $db->prepare("SELECT profile_image FROM users WHERE id = ?"); $stmt->execute([$user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); echo json_encode([ 'success' => !!$user, 'photo' => $user['profile_image'] ?? '' ]); } catch (Exception $e) { logError('Error fetching user photo: ' . $e->getMessage()); echo json_encode(['success' => false, 'message' => 'Failed to fetch user photo']); } exit; } // Fetch site configuration from settings table $db = getDbConnection(); $siteConfig = []; $configKeys = ['organization_address', 'organization_phone', 'organization_email']; foreach ($configKeys as $key) { $stmt = $db->prepare("SELECT setting_value FROM settings WHERE setting_key = ?"); $stmt->execute([$key]); $result = $stmt->fetch(PDO::FETCH_ASSOC); $siteConfig[$key] = $result ? htmlspecialchars($result['setting_value']) : ''; } // Define signatories using constants from config.php $signatories = [ 'Chairman' => [ 'name' => CERTIFICATE_CHAIRMAN_NAME, 'title' => CERTIFICATE_CHAIRMAN_TITLE, 'signature_path' => SITE_URL . '/img/signature.png' ], 'Secretary' => [ 'name' => CERTIFICATE_SECRETARY_NAME, 'title' => CERTIFICATE_SECRETARY_TITLE, 'signature_path' => SITE_URL . '/img/signature1.png' ] ]; // Fetch designations and photos for initial dropdown $designations = []; $profile_photo = ''; $certificate_photo = ''; if ($action === 'add' && isset($_GET['user_id']) && is_numeric($_GET['user_id'])) { $user_id = (int)$_GET['user_id']; $stmt = $db->prepare("SELECT u.membership_type, u.profile_image, u.name FROM users u WHERE u.id = ? AND status = 'approved'"); $stmt->execute([$user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); if ($user) { $membership_type = $user['membership_type']; $profile_photo = $user['profile_image'] ?? ''; $stmt = $db->prepare("SELECT designation, designation_hindi FROM membership_designations WHERE membership_type = ? AND status = 'active' ORDER BY sort_order"); $stmt->execute([$membership_type]); $designations = $stmt->fetchAll(PDO::FETCH_ASSOC); } else { $error = "User not found."; $action = 'list'; } } elseif ($action === 'edit' && $id > 0) { $stmt = $db->prepare("SELECT c.*, u.membership_type, u.profile_image AS profile_photo FROM certificates c JOIN users u ON c.user_id = u.id WHERE c.id = ?"); $stmt->execute([$id]); $certificate = $stmt->fetch(PDO::FETCH_ASSOC); if ($certificate) { $membership_type = $certificate['membership_type']; $profile_photo = $certificate['profile_photo'] ?? ''; $certificate_photo = $certificate['photo_path'] ?? ''; $stmt = $db->prepare("SELECT designation, designation_hindi FROM membership_designations WHERE membership_type = ? AND status = 'active' ORDER BY sort_order"); $stmt->execute([$membership_type]); $designations = $stmt->fetchAll(PDO::FETCH_ASSOC); } else { $error = "Certificate not found."; $action = 'list'; } } // Initialize certificate array for new certificates if ($action === 'add') { $certificate = [ 'certificate_type' => '', 'recipient_name' => '', 'post_name' => '', 'event_or_reason' => '', 'issue_date' => '', 'end_date' => '', 'photo_path' => '', 'user_id' => '', 'status' => 'active' ]; if (isset($_GET['user_id']) && is_numeric($_GET['user_id'])) { $user_id = (int)$_GET['user_id']; $stmt = $db->prepare("SELECT name FROM users WHERE id = ? AND status = 'approved'"); $stmt->execute([$user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); if ($user) { $certificate['recipient_name'] = $user['name']; $certificate['user_id'] = $user_id; $certificate['certificate_type'] = isset($_GET['certificate_type']) && $_GET['certificate_type'] === 'Birthday Wishes' ? 'Birthday Wishes' : ''; $certificate['event_or_reason'] = $certificate['certificate_type'] === 'Birthday Wishes' ? 'Birthday Wishes' : ''; $certificate['issue_date'] = date('Y-m-d'); // Default to today } else { $error = "User not found."; $action = 'list'; } } } elseif ($action === 'edit' && $id > 0) { if (!$certificate) { $error = "Certificate not found."; $action = 'list'; } } // Update the certificate content configuration to use constants $certificateContent = [ 'organization_name' => ORGANIZATION_NAME, 'organization_name_hindi' => ORGANIZATION_NAME_HINDI, // Keep if needed, or translate if required 'header_text' => 'A Dedicated Effort Towards Change', 'registration_number' => 'Registration No.: 238', 'address' => $siteConfig['organization_address'], 'email' => $siteConfig['organization_email'], 'phone' => $siteConfig['organization_phone'], 'chairman_name' => CERTIFICATE_CHAIRMAN_NAME, 'chairman_title' => CERTIFICATE_CHAIRMAN_TITLE, 'template_path' => SITE_URL . '/templates/certificate-template.png', 'signature_path' => SITE_URL . '/img/signature.png', 'secretary_signature_path' => SITE_URL . '/img/signature1.png', 'secretary_name' => CERTIFICATE_SECRETARY_NAME, 'secretary_title' => CERTIFICATE_SECRETARY_TITLE, 'seal_path' => SITE_URL . '/img/seal.png' ]; // Function to generate a unique certificate number function generateCertificateNumber() { global $db; $stmt = $db->query("SELECT certificate_no FROM certificates ORDER BY id DESC LIMIT 1"); $lastCertificate = $stmt->fetch(); $newNumber = $lastCertificate ? ((int) substr($lastCertificate['certificate_no'], 4)) + 1 : 1; return 'SCF' . str_pad($newNumber, 5, '0', STR_PAD_LEFT); } // Handle form submission if ($_SERVER['REQUEST_METHOD'] === 'POST') { if (!isset($_POST['csrf_token']) || !verifyCSRF($_POST['csrf_token'])) { $error = 'Invalid CSRF token.'; } else if (isset($_POST['action'])) { $formAction = $_POST['action']; if ($formAction === 'add' || $formAction === 'edit') { $certificate_type = isset($_POST['certificate_type']) ? sanitizeInput($_POST['certificate_type']) : ''; $recipient_name = isset($_POST['recipient_name']) ? sanitizeInput($_POST['recipient_name']) : ''; $post_name = isset($_POST['post_name']) ? sanitizeInput($_POST['post_name']) : ''; $event_or_reason = isset($_POST['event_or_reason']) ? sanitizeInput($_POST['event_or_reason']) : ''; $issue_date = isset($_POST['issue_date']) ? sanitizeInput($_POST['issue_date']) : ''; $end_date = isset($_POST['end_date']) ? sanitizeInput($_POST['end_date']) : null; $user_id = isset($_POST['user_id']) ? (int)$_POST['user_id'] : null; $status = isset($_POST['status']) ? sanitizeInput($_POST['status']) : 'active'; // Valid certificate types $validCertificateTypes = ['Appointment', 'Appreciation', 'Participation', 'Achievement', 'Birthday Wishes']; // Validate designation based on user's membership_type $stmt = $db->prepare("SELECT membership_type FROM users WHERE id = ? AND status = 'approved'"); $stmt->execute([$user_id]); $user = $stmt->fetch(PDO::FETCH_ASSOC); $validDesignations = []; if ($user && $certificate_type !== 'Birthday Wishes') { $membership_type = $user['membership_type']; $stmt = $db->prepare("SELECT designation FROM membership_designations WHERE membership_type = ? AND status = 'active'"); $stmt->execute([$membership_type]); $validDesignations = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'designation'); } // Enhanced validation if (!in_array($certificate_type, $validCertificateTypes)) { $error = "Invalid certificate type."; } elseif (empty($recipient_name) || ($certificate_type !== 'Birthday Wishes' && empty($post_name)) || empty($event_or_reason) || empty($issue_date) || empty($user_id)) { $error = "Please fill all required fields."; } elseif (!$user) { $error = "User not found or not approved."; } elseif ($certificate_type !== 'Birthday Wishes' && !in_array($post_name, $validDesignations)) { $error = "Invalid designation. Please select according to user's membership level."; } elseif (!in_array($status, ['active', 'inactive'])) { $error = "Invalid status."; } else { try { $photo_path = ''; if ($formAction === 'edit') { $cert_id = isset($_POST['cert_id']) ? (int)$_POST['cert_id'] : 0; $stmt = $db->prepare("SELECT photo_path FROM certificates WHERE id = ?"); $stmt->execute([$cert_id]); $currentData = $stmt->fetch(PDO::FETCH_ASSOC); $photo_path = $currentData['photo_path'] ?? ''; } if (isset($_FILES['photo']) && $_FILES['photo']['error'] === UPLOAD_ERR_OK) { $uploadResult = uploadFile($_FILES['photo'], 'img/certificates'); if ($uploadResult['success']) { // Delete old photo if exists if ($formAction === 'edit' && !empty($photo_path)) { $oldPhotoPath = __DIR__ . '/../img/certificates/' . $photo_path; if (file_exists($oldPhotoPath)) { unlink($oldPhotoPath); } } $photo_path = $uploadResult['filename']; } else { throw new Exception($uploadResult['message']); } } if ($formAction === 'add') { $certificate_no = generateCertificateNumber(); $stmt = $db->prepare(" INSERT INTO certificates (certificate_type, certificate_no, recipient_name, post_name, event_or_reason, issue_date, end_date, photo_path, status, user_id, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) "); $stmt->execute([$certificate_type, $certificate_no, $recipient_name, $post_name, $event_or_reason, $issue_date, $end_date, $photo_path, $status, $user_id]); } else { $cert_id = isset($_POST['cert_id']) ? (int)$_POST['cert_id'] : 0; $stmt = $db->prepare(" UPDATE certificates SET certificate_type = ?, recipient_name = ?, post_name = ?, event_or_reason = ?, issue_date = ?, end_date = ?, photo_path = ?, status = ?, user_id = ?, updated_at = NOW() WHERE id = ? "); $success = $stmt->execute([$certificate_type, $recipient_name, $post_name, $event_or_reason, $issue_date, $end_date, $photo_path, $status, $user_id, $cert_id]); if (!$success) { throw new Exception("Failed to update certificate in database."); } } $success = "Certificate successfully " . ($formAction === 'add' ? 'created!' : 'updated!'); header("Location: certificates.php?success=" . urlencode($success)); exit; } catch (Exception $e) { logError('Certificate processing error: ' . $e->getMessage()); $error = "Error: " . $e->getMessage(); } } } elseif ($formAction === 'delete') { $cert_id = isset($_POST['cert_id']) ? (int)$_POST['cert_id'] : 0; try { $stmt = $db->prepare("SELECT photo_path FROM certificates WHERE id = ?"); $stmt->execute([$cert_id]); $data = $stmt->fetch(PDO::FETCH_ASSOC); $stmt = $db->prepare("DELETE FROM certificates WHERE id = ?"); $stmt->execute([$cert_id]); if (!empty($data['photo_path'])) { $photoPath = __DIR__ . '/../img/certificates/' . $data['photo_path']; if (file_exists($photoPath)) { unlink($photoPath); } } $success = "Certificate successfully deleted!"; header("Location: certificates.php?success=" . urlencode($success)); exit; } catch (Exception $e) { logError('Certificate deletion error: ' . $e->getMessage()); $error = "Deletion error: " . $e->getMessage(); } } } } // Get certificates for list with pagination $page = isset($_GET['page']) ? (int)$_GET['page'] : 1; $limit = 12; $offset = ($page - 1) * $limit; try { $stmt = $db->prepare("SELECT COUNT(*) FROM certificates"); $stmt->execute(); $totalRecords = $stmt->fetchColumn(); $stmt = $db->prepare("SELECT c.*, u.name as user_name, u.profile_image AS profile_photo, u.dob FROM certificates c LEFT JOIN users u ON c.user_id = u.id ORDER BY c.created_at DESC LIMIT ? OFFSET ?"); $stmt->execute([$limit, $offset]); $certificates = $stmt->fetchAll(PDO::FETCH_ASSOC); $stmt = $db->prepare("SELECT designation, designation_hindi FROM membership_designations WHERE status = 'active'"); $stmt->execute(); $allDesignations = $stmt->fetchAll(PDO::FETCH_ASSOC); $totalPages = ceil($totalRecords / $limit); // Fetch users with birthdays today $today = date('Y-m-d'); // For testing, use '2025-09-14'; in production, use CURRENT_DATE $stmt = $db->prepare("SELECT id, name, email, mobile, dob FROM users WHERE DAY(dob) = DAY(CURRENT_DATE) AND MONTH(dob) = MONTH(CURRENT_DATE) AND status = 'approved' ORDER BY name"); $stmt->execute(); $birthdayUsers = $stmt->fetchAll(PDO::FETCH_ASSOC); } catch (PDOException $e) { logError('Database error in certificate listing: ' . $e->getMessage()); $error = "Database error: " . $e->getMessage(); $certificates = []; $allDesignations = []; $birthdayUsers = []; $totalPages = 0; } $pageTitle = ($action === 'add') ? "Add New Certificate" : (($action === 'edit') ? "Edit Certificate" : "Certificate Management"); include 'includes/header.php'; ?>

Certificate Type *

Recipient Name *

User *

Designation *' : ''; ?>

Upload Photo

Certificate Photo

Uploading a new photo will replace the current one.

Profile photo (will be replaced if new photo uploaded).

No photo available.

Subject / Reason *

Issue Date *

End Date

Status *

Cancel

User List

0): ?>

Today's Birthdays:

No birthdays today.

query("SELECT id, name, email, mobile, dob FROM users WHERE status = 'approved' ORDER BY name"); $users = $userStmt->fetchAll(PDO::FETCH_ASSOC); foreach ($users as $user): $isBirthdayToday = in_array($user['id'], array_column($birthdayUsers, 'id')); ?>

Name	Birth Date	Email	Mobile	Actions

Certificate List

0): ?>

Certificate No.	Type	Name	Birth Date	Designation	Subject	Issue Date	End Date	Status	Actions
	'Appointment Certificate', 'Appreciation' => 'Appreciation Certificate', 'Participation' => 'Participation Certificate', 'Achievement' => 'Achievement Certificate', 'Birthday Wishes' => 'Birthday Wishes' ]; echo htmlspecialchars($types[$cert['certificate_type']] ?? $cert['certificate_type']); ?>

No certificates found.

1): ?>