File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090028/Shop/app/Models/User.php

Back
<?php /** * User Model * Maps to `users` table / require_once __DIR__ . '/../Core/Model.php'; class User extends Model { protected string $table = 'users'; public function findByUsernameOrEmail(string $identifier): ?array { $sql = "SELECT FROM `users` WHERE (`username` = :id1 OR `email` = :id2) AND `deleted_at` IS NULL LIMIT 1"; return $this->fetchOne($sql, [':id1' => $identifier, ':id2' => $identifier]); } public function findByEmail(string $email): ?array { $sql = "SELECT * FROM `users` WHERE `email` = :email LIMIT 1"; return $this->fetchOne($sql, [':email' => $email]); } public function findByUsername(string $username): ?array { $sql = "SELECT * FROM `users` WHERE `username` = :username LIMIT 1"; return $this->fetchOne($sql, [':username' => $username]); } public function findByPhone(string $phone): ?array { $sql = "SELECT * FROM `users` WHERE `phone` = :phone LIMIT 1"; return $this->fetchOne($sql, [':phone' => $phone]); } public function getUserRoles(int $userId): array { $sql = "SELECT r.name FROM `user_roles` ur JOIN `roles` r ON ur.role_id = r.id WHERE ur.user_id = :user_id"; $rows = $this->fetchAll($sql, [':user_id' => $userId]); return array_column($rows, 'name'); } public function assignRole(int $userId, string $roleName): bool { $sqlRole = "SELECT id FROM `roles` WHERE `name` = :name LIMIT 1"; $role = $this->fetchOne($sqlRole, [':name' => $roleName]); if (!$role) { return false; } $roleId = (int)$role['id']; $sql = "INSERT IGNORE INTO `user_roles` (`user_id`, `role_id`, `assigned_at`) VALUES (:user_id, :role_id, NOW())"; $this->query($sql, [':user_id' => $userId, ':role_id' => $roleId]); return true; } public function updateLastLogin(int $userId): void { $this->query("UPDATE `users` SET `last_login_at` = NOW() WHERE `id` = :id", [':id' => $userId]); } public function softDelete(int $userId): bool { $sql = "UPDATE `users` SET `status` = 'deleted', `deleted_at` = NOW() WHERE `id` = :id"; $this->query($sql, [':id' => $userId]); return true; } // ========================================================= // Admin Management Methods // ========================================================= /** * Paginated user search for Admin panel / public function searchUsers(array $filters = [], int $limit = 20, int $offset = 0): array { [$whereSql, $params] = $this->buildAdminUserConditions($filters); $sql = "SELECT u.id, u.username, u.email, u.first_name, u.last_name, u.phone, u.status, u.is_super_admin, u.created_at, u.last_login_at, GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles FROM `users` u LEFT JOIN `user_roles` ur ON ur.user_id = u.id LEFT JOIN `roles` r ON r.id = ur.role_id WHERE {$whereSql} GROUP BY u.id ORDER BY u.created_at DESC LIMIT :limit OFFSET :offset"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->bindValue(':limit', $limit, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function countUsers(array $filters = []): int { [$whereSql, $params] = $this->buildAdminUserConditions($filters); $sql = "SELECT COUNT(DISTINCT u.id) FROM `users` u LEFT JOIN `user_roles` ur ON ur.user_id = u.id LEFT JOIN `roles` r ON r.id = ur.role_id WHERE {$whereSql}"; $stmt = $this->db->prepare($sql); foreach ($params as $k => $v) { $stmt->bindValue($k, $v); } $stmt->execute(); return (int)$stmt->fetchColumn(); } private function buildAdminUserConditions(array $filters): array { $conditions = ['u.deleted_at IS NULL']; $params = []; if (!empty($filters['q'])) { $conditions[] = '(u.username LIKE :q OR u.email LIKE :q OR u.phone LIKE :q OR u.first_name LIKE :q OR u.last_name LIKE :q)'; $params[':q'] = '%' . trim($filters['q']) . '%'; } if (!empty($filters['status']) && $filters['status'] !== 'all') { $conditions[] = 'u.status = :status'; $params[':status'] = $filters['status']; } if (!empty($filters['role']) && $filters['role'] !== 'all') { $conditions[] = 'r.name = :role'; $params[':role'] = $filters['role']; } return [implode(' AND ', $conditions), $params]; } public function findWithRoles(int $userId): ?array { $sql = "SELECT u., GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles FROM `users` u LEFT JOIN `user_roles` ur ON ur.user_id = u.id LEFT JOIN `roles` r ON r.id = ur.role_id WHERE u.id = :id AND u.deleted_at IS NULL GROUP BY u.id LIMIT 1"; return $this->fetchOne($sql, [':id' => $userId]); } public function suspendUser(int $userId): bool { $sql = "UPDATE `users` SET `status` = 'suspended', `updated_at` = NOW() WHERE `id` = :id AND `is_super_admin` = 0"; $stmt = $this->query($sql, [':id' => $userId]); return $stmt->rowCount() > 0; } public function reactivateUser(int $userId): bool { $sql = "UPDATE `users` SET `status` = 'active', `updated_at` = NOW() WHERE `id` = :id"; $stmt = $this->query($sql, [':id' => $userId]); return $stmt->rowCount() > 0; } public function getAdminUsers(): array { $sql = "SELECT u.id, u.username, u.email, u.first_name, u.last_name, u.status, u.is_super_admin, u.created_at, u.last_login_at, GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ',') as roles FROM `users` u JOIN `user_roles` ur ON ur.user_id = u.id JOIN `roles` r ON r.id = ur.role_id WHERE r.name IN ('admin','operation_admin','finance_admin','support_admin') AND u.deleted_at IS NULL GROUP BY u.id ORDER BY u.is_super_admin DESC, u.created_at ASC"; return $this->fetchAll($sql); } public function removeRole(int $userId, string $roleName): void { $sql = "DELETE ur FROM `user_roles` ur JOIN `roles` r ON r.id = ur.role_id WHERE ur.user_id = :uid AND r.name = :role"; $this->query($sql, [':uid' => $userId, ':role' => $roleName]); } public function getUserLoginHistory(int $userId, int $limit = 20): array { $sql = "SELECT * FROM `login_history` WHERE user_id = :uid ORDER BY created_at DESC LIMIT :lim"; $stmt = $this->db->prepare($sql); $stmt->bindValue(':uid', $userId, PDO::PARAM_INT); $stmt->bindValue(':lim', $limit, PDO::PARAM_INT); $stmt->execute(); return $stmt->fetchAll(); } public function getPlatformUserStats(): array { $sql = "SELECT COUNT(*) as total_users, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_users, SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) as suspended_users, SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as new_today FROM `users` WHERE deleted_at IS NULL"; return $this->fetchOne($sql) ?: []; } }

| ver. 1.4 | Github | . | PHP 7.4.33 | Generation time: 0.25 | proxy | phpinfo | Settings