false, 'message' => 'Invalid membership type']); exit; } try { $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); foreach ($designations as &$des) { $des['designation'] = html_entity_decode($des['designation'], ENT_QUOTES | ENT_HTML5, 'UTF-8'); $des['designation_hindi'] = html_entity_decode($des['designation_hindi'], ENT_QUOTES | ENT_HTML5, 'UTF-8'); } echo json_encode(['success' => true, 'designations' => $designations]); } catch (Exception $e) { echo json_encode(['success' => false, 'message' => 'Error loading designations: ' . $e->getMessage()]); } exit; } // Handle form submission if ($_SERVER['REQUEST_METHOD'] === 'POST') { if (isset($_POST['action'])) { $formAction = $_POST['action']; if ($formAction === 'add' || $formAction === 'edit') { // Note: The primary ID is handled by MySQL's AUTO_INCREMENT and cannot be // set manually during INSERT. To control the sequence, use the SQL command: // ALTER TABLE `users` AUTO_INCREMENT = [desired_start_id]; // --- Foreign Key Integrity Check (Ensures created_by is a valid INT) --- $created_by_user_id = isset($_SESSION['user_id']) && is_numeric($_SESSION['user_id']) && (int)$_SESSION['user_id'] > 0 ? (int)$_SESSION['user_id'] : null; if ($created_by_user_id === null && $formAction === 'add') { $error = "Authentication error: Cannot create user. Your session ID is missing or invalid. Please re-login."; } if (empty($error)) { $name = sanitizeInput($_POST['name']); $sdw_type = sanitizeInput($_POST['sdw_type']); $sdw_name = sanitizeInput($_POST['sdw_name']); $mobile = sanitizeInput($_POST['mobile']); $email = sanitizeInput($_POST['email']); $password = !empty($_POST['password']) ? password_hash($_POST['password'], PASSWORD_BCRYPT) : null; $gender = sanitizeInput($_POST['gender']); $dob = sanitizeInput($_POST['dob']); $profession = sanitizeInput($_POST['profession']); $blood_group = sanitizeInput($_POST['blood_group']); $aadhar = sanitizeInput($_POST['aadhar']); $state = sanitizeInput($_POST['state']); $district = sanitizeInput($_POST['district']); $address = sanitizeInput($_POST['address']); $working_area = sanitizeInput($_POST['working_area']); $pincode = sanitizeInput($_POST['pincode']); $membership_type = sanitizeInput($_POST['membership_type']); // Note: html_entity_decode is used here to correctly handle stored HTML entities in designation. $designation = html_entity_decode(sanitizeInput($_POST['designation'], false), ENT_QUOTES | ENT_HTML5, 'UTF-8'); $status = sanitizeInput($_POST['status']); $user_type = ($currentUserType === 'coordinator') ? 'member' : sanitizeInput($_POST['user_type']); $payment_id = isset($_POST['payment_id']) ? sanitizeInput($_POST['payment_id']) : null; $payment_method = isset($_POST['payment_method']) ? sanitizeInput($_POST['payment_method']) : null; $valid_until = isset($_POST['valid_until']) ? sanitizeInput($_POST['valid_until']) : null; $valid_from = isset($_POST['valid_from']) ? sanitizeInput($_POST['valid_from']) : null; if ($currentUserType === 'coordinator' && $membership_type !== 'active') { $error = "Coordinators can only create active membership users."; } // Validation elseif (empty($name) || empty($sdw_type) || empty($sdw_name) || empty($mobile) || empty($gender) || empty($dob) || empty($aadhar) || empty($state) || empty($district) || empty($address) || empty($pincode) || empty($membership_type) || empty($designation) || empty($user_type)) { $error = "All required fields must be filled."; } elseif ($formAction === 'add' && empty($_POST['password'])) { $error = "Password is required when adding a new user."; } elseif (!preg_match('/^[0-9]{10}$/', $mobile)) { $error = "Mobile number must be 10 digits."; } elseif (!preg_match('/^[0-9]{12}$/', $aadhar)) { $error = "Aadhar number must be 12 digits."; } elseif (!preg_match('/^[0-9]{6}$/', $pincode)) { $error = "Pincode must be 6 digits."; } elseif (!filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($email)) { $error = "Invalid email format."; } else { try { // Validate designation $stmt = $db->prepare("SELECT designation FROM membership_designations WHERE membership_type = ? AND status = 'active'"); $stmt->execute([$membership_type]); $db_designations = $stmt->fetchAll(PDO::FETCH_COLUMN); $decoded_designations = array_map(function($d) { return html_entity_decode($d, ENT_QUOTES | ENT_HTML5, 'UTF-8'); }, $db_designations); if (!in_array($designation, $decoded_designations)) { throw new Exception("Invalid designation for the selected membership type."); } // Check for duplicate email or mobile if ($formAction === 'add' || ($formAction === 'edit' && isset($user['email']) && $user['email'] !== $email)) { $stmt = $db->prepare("SELECT id FROM users WHERE email = ? AND id != ?"); $stmt->execute([$email, $formAction === 'edit' ? $id : 0]); if ($stmt->fetch()) { $error = "Email already exists."; } } if (empty($error) && ($formAction === 'add' || ($formAction === 'edit' && isset($user['mobile']) && $user['mobile'] !== $mobile))) { $stmt = $db->prepare("SELECT id FROM users WHERE mobile = ? AND id != ?"); $stmt->execute([$mobile, $formAction === 'edit' ? $id : 0]); if ($stmt->fetch()) { $error = "Mobile number already exists."; } } if (empty($error)) { $profile_image = $formAction === 'edit' ? ($user['profile_image'] ?? null) : null; $aadhar_front = $formAction === 'edit' ? ($user['aadhar_front'] ?? null) : null; $aadhar_back = $formAction === 'edit' ? ($user['aadhar_back'] ?? null) : null; $payment_proof = $formAction === 'edit' ? ($user['payment_proof'] ?? null) : null; $files = [ 'profile_image' => 'uploads/profiles', 'aadhar_front' => 'uploads/profiles', 'aadhar_back' => 'uploads/profiles', 'payment_proof' => 'uploads/payments' ]; foreach ($files as $field => $path) { if (isset($_FILES[$field]) && $_FILES[$field]['error'] === UPLOAD_ERR_OK) { $uploadResult = uploadFile($_FILES[$field], $path); if ($uploadResult['success']) { // Use variable variable to set the filename $$field = $uploadResult['filename']; } else { throw new Exception($uploadResult['message']); } } } if ($formAction === 'add') { // --- Dynamic Registration ID Implementation using ORGANIZATION_NAME_SHORT --- $registration_id = $registration_prefix . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT); // Ensure unique registration ID (basic check) $stmt = $db->prepare("SELECT id FROM users WHERE registration_id = ?"); $stmt->execute([$registration_id]); while ($stmt->fetch()) { $registration_id = $registration_prefix . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT); $stmt->execute([$registration_id]); } $stmt = $db->prepare("INSERT INTO users (name, sdw_type, sdw_name, mobile, email, password, gender, dob, profession, blood_group, aadhar, state, district, address, working_area, pincode, membership_type, designation, profile_image, aadhar_front, aadhar_back, payment_id, payment_proof, payment_method, registration_id, status, user_type, created_at, valid_until, valid_from, created_by) VALUES (:name, :sdw_type, :sdw_name, :mobile, :email, :password, :gender, :dob, :profession, :blood_group, :aadhar, :state, :district, :address, :working_area, :pincode, :membership_type, :designation, :profile_image, :aadhar_front, :aadhar_back, :payment_id, :payment_proof, :payment_method, :registration_id, :status, :user_type, NOW(), :valid_until, :valid_from, :created_by)"); $stmt->bindParam(':created_by', $created_by_user_id, PDO::PARAM_INT); // Bind as INT for created_by $stmt->bindParam(':name', $name); $stmt->bindParam(':sdw_type', $sdw_type); $stmt->bindParam(':sdw_name', $sdw_name); $stmt->bindParam(':mobile', $mobile); $stmt->bindParam(':email', $email); $stmt->bindParam(':password', $password); $stmt->bindParam(':gender', $gender); $stmt->bindParam(':dob', $dob); $stmt->bindParam(':profession', $profession); $stmt->bindParam(':blood_group', $blood_group); $stmt->bindParam(':aadhar', $aadhar); $stmt->bindParam(':state', $state); $stmt->bindParam(':district', $district); $stmt->bindParam(':address', $address); $stmt->bindParam(':working_area', $working_area); $stmt->bindParam(':pincode', $pincode); $stmt->bindParam(':membership_type', $membership_type); $stmt->bindParam(':designation', $designation); $stmt->bindParam(':profile_image', $profile_image); $stmt->bindParam(':aadhar_front', $aadhar_front); $stmt->bindParam(':aadhar_back', $aadhar_back); $stmt->bindParam(':payment_id', $payment_id); $stmt->bindParam(':payment_proof', $payment_proof); $stmt->bindParam(':payment_method', $payment_method); $stmt->bindParam(':registration_id', $registration_id); $stmt->bindParam(':status', $status); $stmt->bindParam(':user_type', $user_type); $stmt->bindParam(':valid_until', $valid_until); $stmt->bindParam(':valid_from', $valid_from); $stmt->execute(); $success = "User added successfully! Registration ID: " . $registration_id; } else { // Edit action permissions check for coordinators if ($currentUserType === 'coordinator') { $stmt = $db->prepare("SELECT created_by FROM users WHERE id = ?"); $stmt->execute([$id]); $userCreator = $stmt->fetchColumn(); if ($userCreator != $created_by_user_id) { throw new Exception("You don't have permission to edit this user."); } } $query = " UPDATE users SET name = :name, email = :email, mobile = :mobile, gender = :gender, dob = :dob, sdw_type = :sdw_type, sdw_name = :sdw_name, profession = :profession, blood_group = :blood_group, aadhar = :aadhar, state = :state, district = :district, address = :address, working_area = :working_area, pincode = :pincode, membership_type = :membership_type, designation = :designation, profile_image = COALESCE(:profile_image, profile_image), aadhar_front = COALESCE(:aadhar_front, aadhar_front), aadhar_back = COALESCE(:aadhar_back, aadhar_back), payment_proof = COALESCE(:payment_proof, payment_proof), payment_id = :payment_id, payment_method = :payment_method, status = :status, user_type = :user_type, valid_until = :valid_until, valid_from = :valid_from"; if (!empty($password)) { $query .= ", password = :password"; } $query .= " WHERE id = :id"; $stmt = $db->prepare($query); $stmt->bindParam(':id', $id); if (!empty($password)) { $stmt->bindParam(':password', $password); } $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email); $stmt->bindParam(':mobile', $mobile); $stmt->bindParam(':gender', $gender); $stmt->bindParam(':dob', $dob); $stmt->bindParam(':sdw_type', $sdw_type); $stmt->bindParam(':sdw_name', $sdw_name); $stmt->bindParam(':profession', $profession); $stmt->bindParam(':blood_group', $blood_group); $stmt->bindParam(':aadhar', $aadhar); $stmt->bindParam(':state', $state); $stmt->bindParam(':district', $district); $stmt->bindParam(':address', $address); $stmt->bindParam(':working_area', $working_area); $stmt->bindParam(':pincode', $pincode); $stmt->bindParam(':membership_type', $membership_type); $stmt->bindParam(':designation', $designation); $stmt->bindParam(':profile_image', $profile_image); $stmt->bindParam(':aadhar_front', $aadhar_front); $stmt->bindParam(':aadhar_back', $aadhar_back); $stmt->bindParam(':payment_proof', $payment_proof); $stmt->bindParam(':payment_id', $payment_id); $stmt->bindParam(':payment_method', $payment_method); $stmt->bindParam(':status', $status); $stmt->bindParam(':user_type', $user_type); $stmt->bindParam(':valid_until', $valid_until); $stmt->bindParam(':valid_from', $valid_from); $stmt->execute(); $success = "User updated successfully!"; } header("Location: users-management.php?success=" . urlencode($success)); exit; } } catch (Exception $e) { $error = "Error: " . $e->getMessage(); // If the error is the SQLSTATE[23000], it likely confirms the missing created_by issue. if (strpos($e->getMessage(), 'SQLSTATE[23000]') !== false && $formAction === 'add') { $error = "Database Error: Could not save user. Please ensure you are logged in correctly and your account has been approved. Detailed error: " . $e->getMessage(); } } } } } if ($formAction === 'approve' || $formAction === 'reject') { $user_id = isset($_POST['user_id']) ? (int)$_POST['user_id'] : 0; $status = $formAction === 'approve' ? 'approved' : 'rejected'; try { $stmt = $db->prepare("UPDATE users SET status = :status WHERE id = :id"); $stmt->bindParam(':status', $status); $stmt->bindParam(':id', $user_id); $stmt->execute(); $success = "User " . ($status === 'approved' ? 'approved' : 'rejected') . " successfully."; header("Location: users-management.php?success=" . urlencode($success)); exit; } catch (PDOException $e) { $error = "Database error: " . $e->getMessage(); } } if ($formAction === 'delete') { $user_id = isset($_POST['user_id']) ? (int)$_POST['user_id'] : 0; try { $stmt = $db->prepare("SELECT profile_image, aadhar_front, aadhar_back, payment_proof, id_card_photo FROM users WHERE id = :id"); $stmt->bindParam(':id', $user_id); $stmt->execute(); $user_files = $stmt->fetch(PDO::FETCH_ASSOC); // IMPORTANT: Since the FK is ON DELETE SET NULL, the database handles related rows. $stmt = $db->prepare("DELETE FROM users WHERE id = :id"); $stmt->bindParam(':id', $user_id); $stmt->execute(); $files = [ 'profile_image' => 'uploads/profiles', 'aadhar_front' => 'uploads/profiles', 'aadhar_back' => 'uploads/profiles', 'payment_proof' => 'uploads/payments', 'id_card_photo' => 'uploads/id_cards' ]; foreach ($files as $field => $path) { if (!empty($user_files[$field])) { // The relative path in the database should be adjusted here if needed, but assuming it's relative to the app root. $filePath = __DIR__ . '/../' . $path . '/' . $user_files[$field]; if (file_exists($filePath)) { unlink($filePath); } } } $success = "User deleted successfully."; header("Location: users-management.php?success=" . urlencode($success)); exit; } catch (PDOException $e) { $error = "Database error: " . $e->getMessage(); } } } } // Get user for view or edit if (($action === 'view' || $action === 'edit') && $id > 0) { try { $stmt = $db->prepare("SELECT * FROM users WHERE id = :id"); $stmt->bindParam(':id', $id); $stmt->execute(); $user = $stmt->fetch(PDO::FETCH_ASSOC); if (!$user) { $error = "User not found."; $action = 'list'; } } catch (PDOException $e) { $error = "Database error: " . $e->getMessage(); $action = 'list'; } } // Get users for list with pagination and filters $page = isset($_GET['page']) ? (int)$_GET['page'] : 1; $limit = 20; $offset = ($page - 1) * $limit; $search = isset($_GET['search']) ? sanitizeInput($_GET['search']) : ''; $status_filter = isset($_GET['status']) ? sanitizeInput($_GET['status']) : ''; $membership_filter = isset($_GET['membership']) ? sanitizeInput($_GET['membership']) : ''; $user_type_filter = isset($_GET['user_type']) ? sanitizeInput($_GET['user_type']) : ''; $created_by_filter = isset($_GET['created_by']) && $_GET['created_by'] !== '' ? (int)$_GET['created_by'] : null; try { $whereConditions = []; $params = []; // Use the validated user ID for coordinator filtering $current_user_id = $_SESSION['user_id'] ?? 0; if ($currentUserType === 'coordinator') { $whereConditions[] = "u.created_by = :current_user_id"; $params[':current_user_id'] = $current_user_id; } if (!empty($search)) { $whereConditions[] = "(u.name LIKE :search OR u.email LIKE :search OR u.mobile LIKE :search OR u.registration_id LIKE :search OR u.designation LIKE :search)"; $params[':search'] = '%' . $search . '%'; } if (!empty($status_filter)) { $whereConditions[] = "u.status = :status"; $params[':status'] = $status_filter; } if (!empty($membership_filter)) { $whereConditions[] = "u.membership_type = :membership"; $params[':membership'] = $membership_filter; } if (!empty($user_type_filter)) { $whereConditions[] = "u.user_type = :user_type"; $params[':user_type'] = $user_type_filter; } if ($currentUserType === 'admin' && !is_null($created_by_filter)) { $whereConditions[] = "u.created_by = :created_by"; $params[':created_by'] = $created_by_filter; } $whereClause = !empty($whereConditions) ? 'WHERE ' . implode(' AND ', $whereConditions) : ''; $countQuery = "SELECT COUNT(*) FROM users u $whereClause"; $stmt = $db->prepare($countQuery); $stmt->execute($params); $totalRecords = $stmt->fetchColumn(); if ($totalRecords == 0 && $currentUserType === 'coordinator') { $users = []; $noUsersMessage = "You haven't created any users yet. Click 'Add New User' to create your first user."; } elseif ($totalRecords == 0) { $users = []; $noUsersMessage = "No users found matching your criteria."; } else { $query = "SELECT u.*, c.name AS created_by_name FROM users u LEFT JOIN users c ON u.created_by = c.id $whereClause ORDER BY u.created_at DESC LIMIT :limit OFFSET :offset"; $stmt = $db->prepare($query); foreach ($params as $key => $value) { $stmt->bindValue($key, $value); } $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); $users = $stmt->fetchAll(PDO::FETCH_ASSOC); } $totalPages = ceil($totalRecords / $limit); // Fetch list of coordinators for filter dropdown (only for admins) $coordinators = []; if ($currentUserType === 'admin') { // Fetch all distinct created_by IDs that are coordinators $stmt = $db->prepare("SELECT DISTINCT u.created_by, c.name AS created_by_name FROM users u LEFT JOIN users c ON u.created_by = c.id WHERE u.created_by IS NOT NULL AND c.user_type IN ('coordinator', 'admin') AND c.name IS NOT NULL ORDER BY c.name"); $stmt->execute(); $coordinators = $stmt->fetchAll(PDO::FETCH_ASSOC); } } catch (PDOException $e) { $error = "Database error: " . $e->getMessage(); $users = []; $totalPages = 0; $noUsersMessage = "Error loading users: " . htmlspecialchars($e->getMessage()); } // Fetch designations for edit mode $designations = []; if ($action === 'edit' && !empty($user['membership_type'])) { try { $stmt = $db->prepare("SELECT designation, designation_hindi FROM membership_designations WHERE membership_type = ? AND status = 'active' ORDER BY sort_order"); $stmt->execute([$user['membership_type']]); $designations = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($designations as &$des) { $des['designation'] = html_entity_decode($des['designation'], ENT_QUOTES | ENT_HTML5, 'UTF-8'); $des['designation_hindi'] = html_entity_decode($des['designation_hindi'], ENT_QUOTES | ENT_HTML5, 'UTF-8'); } } catch (Exception $e) { $error = "Error loading designations: " . $e->getMessage(); } } include 'includes/header.php'; ?>

Filters

Users List

ID	Name	Email	Mobile	Membership Type	Designation	Status	Created By	Actions

No users found matching your criteria.

1): ?>

User Form

Name *

S/D/W Type *

S/D/W Name *

Mobile *

Email

Password *' : '(Leave blank to keep current)'; ?> >

Gender *

Date of Birth *

Profession

Blood Group

Aadhar Number *

State *

District *

Address *

Working Area

Pincode *

Membership Type *

Designation *

User Type *

Status *

Payment Method

Payment ID

Valid From

Valid Until

Profile Photo

Aadhar Card (Front)

Current Aadhaar Front

Aadhar Card (Back)

Current Aadhaar Back

Payment Proof

View Current Proof

Cancel

User Details

Name
S/D/W Type
S/D/W Name
Mobile
Email
Gender
Date of Birth
Profession
Blood Group
Aadhar Number
State
District
Address
Working Area
Pincode
Membership Type
Designation
User Type
Status
Payment Method
Payment ID
Valid From
Valid Until
Profile Image: No image
Aadhar Front: No image
Aadhar Back: No image
Payment Proof: View Payment Proof No proof