Merge pull request #45249 from nextcloud/feature/add-ability-to-sort-by-last-login

feat: add ability to sort by last login

6 files changed, 607 insertions, 0 deletions

diff --git a/apps/provisioning_api/appinfo/routes.php b/apps/provisioning_api/appinfo/routes.php
index b44685af175..78526ce6402 100644
--- a/apps/provisioning_api/appinfo/routes.php
+++ b/apps/provisioning_api/appinfo/routes.php
@@ -28,6 +28,7 @@ return [
 		['root' => '/cloud', 'name' => 'Users#getUsers', 'url' => '/users', 'verb' => 'GET'],
 		['root' => '/cloud', 'name' => 'Users#getUsersDetails', 'url' => '/users/details', 'verb' => 'GET'],
 		['root' => '/cloud', 'name' => 'Users#getDisabledUsersDetails', 'url' => '/users/disabled', 'verb' => 'GET'],
+		['root' => '/cloud', 'name' => 'Users#getLastLoggedInUsers', 'url' => '/users/recent', 'verb' => 'GET'],
 		['root' => '/cloud', 'name' => 'Users#searchByPhoneNumbers', 'url' => '/users/search/by-phone', 'verb' => 'POST'],
 		['root' => '/cloud', 'name' => 'Users#addUser', 'url' => '/users', 'verb' => 'POST'],
 		['root' => '/cloud', 'name' => 'Users#getUser', 'url' => '/users/{userId}', 'verb' => 'GET'],
diff --git a/apps/provisioning_api/lib/Controller/UsersController.php b/apps/provisioning_api/lib/Controller/UsersController.php
index 67293ae0033..1cdc1392596 100644
--- a/apps/provisioning_api/lib/Controller/UsersController.php
+++ b/apps/provisioning_api/lib/Controller/UsersController.php
@@ -265,6 +265,62 @@ class UsersController extends AUserData {
 		]);
 	}
 
+	/**
+	 * Gets the list of users sorted by lastLogin, from most recent to least recent
+	 *
+	 * @param string $search Text to search for
+	 * @param ?int $limit Limit the amount of users returned
+	 * @param int $offset Offset
+	 * @return DataResponse<Http::STATUS_OK, array{users: array<string, Provisioning_APIUserDetails|array{id: string}>}, array{}>
+	 *
+	 * 200: Users details returned based on last logged in information
+	 */
+	public function getLastLoggedInUsers(string $search = '',
+		?int $limit = null,
+		int $offset = 0,
+	): DataResponse {
+		$currentUser = $this->userSession->getUser();
+		if ($currentUser === null) {
+			return new DataResponse(['users' => []]);
+		}
+		if ($limit !== null && $limit < 0) {
+			throw new InvalidArgumentException("Invalid limit value: $limit");
+		}
+		if ($offset < 0) {
+			throw new InvalidArgumentException("Invalid offset value: $offset");
+		}
+
+		$users = [];
+
+		// For Admin alone user sorting based on lastLogin. For sub admin and groups this is not supported
+		$users = $this->userManager->getLastLoggedInUsers($limit, $offset, $search);
+
+		$usersDetails = [];
+		foreach ($users as $userId) {
+			try {
+				$userData = $this->getUserData($userId);
+			} catch (OCSNotFoundException $e) {
+				// We still want to return all other accounts, but this one was removed from the backends
+				// yet they are still in our database. Might be a LDAP remnant.
+				$userData = null;
+				$this->logger->warning('Found one account that was removed from its backend, but still exists in Nextcloud database', ['accountId' => $userId]);
+			}
+			// Do not insert empty entry
+			if ($userData !== null) {
+				$usersDetails[$userId] = $userData;
+			} else {
+				// Currently logged-in user does not have permissions to see this user
+				// only showing its id
+				$usersDetails[$userId] = ['id' => $userId];
+			}
+		}
+
+		return new DataResponse([
+			'users' => $usersDetails
+		]);
+	}
+
+
 
 	/**
 	 * @NoAdminRequired
diff --git a/apps/provisioning_api/openapi-administration.json b/apps/provisioning_api/openapi-administration.json
index abf799a1688..532bf684976 100644
--- a/apps/provisioning_api/openapi-administration.json
+++ b/apps/provisioning_api/openapi-administration.json
@@ -75,6 +75,268 @@
                         "type": "string"
                     }
                 }
+            },
+            "UserDetails": {
+                "type": "object",
+                "required": [
+                    "additional_mail",
+                    "address",
+                    "backend",
+                    "backendCapabilities",
+                    "biography",
+                    "display-name",
+                    "displayname",
+                    "email",
+                    "fediverse",
+                    "groups",
+                    "headline",
+                    "id",
+                    "language",
+                    "lastLogin",
+                    "locale",
+                    "manager",
+                    "notify_email",
+                    "organisation",
+                    "phone",
+                    "profile_enabled",
+                    "quota",
+                    "role",
+                    "subadmin",
+                    "twitter",
+                    "website"
+                ],
+                "properties": {
+                    "additional_mail": {
+                        "type": "array",
+                        "items": {
+                            "type": "string"
+                        }
+                    },
+                    "additional_mailScope": {
+                        "type": "array",
+                        "items": {
+                            "$ref": "#/components/schemas/UserDetailsScope"
+                        }
+                    },
+                    "address": {
+                        "type": "string"
+                    },
+                    "addressScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "avatarScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "backend": {
+                        "type": "string"
+                    },
+                    "backendCapabilities": {
+                        "type": "object",
+                        "required": [
+                            "setDisplayName",
+                            "setPassword"
+                        ],
+                        "properties": {
+                            "setDisplayName": {
+                                "type": "boolean"
+                            },
+                            "setPassword": {
+                                "type": "boolean"
+                            }
+                        }
+                    },
+                    "biography": {
+                        "type": "string"
+                    },
+                    "biographyScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "display-name": {
+                        "type": "string"
+                    },
+                    "displayname": {
+                        "type": "string"
+                    },
+                    "displaynameScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "email": {
+                        "type": "string",
+                        "nullable": true
+                    },
+                    "emailScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "enabled": {
+                        "type": "boolean"
+                    },
+                    "fediverse": {
+                        "type": "string"
+                    },
+                    "fediverseScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "groups": {
+                        "type": "array",
+                        "items": {
+                            "type": "string"
+                        }
+                    },
+                    "headline": {
+                        "type": "string"
+                    },
+                    "headlineScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "id": {
+                        "type": "string"
+                    },
+                    "language": {
+                        "type": "string"
+                    },
+                    "lastLogin": {
+                        "type": "integer",
+                        "format": "int64"
+                    },
+                    "locale": {
+                        "type": "string"
+                    },
+                    "manager": {
+                        "type": "string"
+                    },
+                    "notify_email": {
+                        "type": "string",
+                        "nullable": true
+                    },
+                    "organisation": {
+                        "type": "string"
+                    },
+                    "organisationScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "phone": {
+                        "type": "string"
+                    },
+                    "phoneScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "profile_enabled": {
+                        "type": "string"
+                    },
+                    "profile_enabledScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "quota": {
+                        "$ref": "#/components/schemas/UserDetailsQuota"
+                    },
+                    "role": {
+                        "type": "string"
+                    },
+                    "roleScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "storageLocation": {
+                        "type": "string"
+                    },
+                    "subadmin": {
+                        "type": "array",
+                        "items": {
+                            "type": "string"
+                        }
+                    },
+                    "twitter": {
+                        "type": "string"
+                    },
+                    "twitterScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    },
+                    "website": {
+                        "type": "string"
+                    },
+                    "websiteScope": {
+                        "$ref": "#/components/schemas/UserDetailsScope"
+                    }
+                }
+            },
+            "UserDetailsQuota": {
+                "type": "object",
+                "properties": {
+                    "free": {
+                        "anyOf": [
+                            {
+                                "type": "number",
+                                "format": "double"
+                            },
+                            {
+                                "type": "integer",
+                                "format": "int64"
+                            }
+                        ]
+                    },
+                    "quota": {
+                        "anyOf": [
+                            {
+                                "type": "number",
+                                "format": "double"
+                            },
+                            {
+                                "type": "integer",
+                                "format": "int64"
+                            },
+                            {
+                                "type": "string"
+                            }
+                        ]
+                    },
+                    "relative": {
+                        "anyOf": [
+                            {
+                                "type": "number",
+                                "format": "double"
+                            },
+                            {
+                                "type": "integer",
+                                "format": "int64"
+                            }
+                        ]
+                    },
+                    "total": {
+                        "anyOf": [
+                            {
+                                "type": "number",
+                                "format": "double"
+                            },
+                            {
+                                "type": "integer",
+                                "format": "int64"
+                            }
+                        ]
+                    },
+                    "used": {
+                        "anyOf": [
+                            {
+                                "type": "number",
+                                "format": "double"
+                            },
+                            {
+                                "type": "integer",
+                                "format": "int64"
+                            }
+                        ]
+                    }
+                }
+            },
+            "UserDetailsScope": {
+                "type": "string",
+                "enum": [
+                    "v2-private",
+                    "v2-local",
+                    "v2-federated",
+                    "v2-published",
+                    "private",
+                    "contacts",
+                    "public"
+                ]
             }
         }
     },
@@ -699,6 +961,123 @@
                 }
             }
         },
+        "/ocs/v2.php/cloud/users/recent": {
+            "get": {
+                "operationId": "users-get-last-logged-in-users",
+                "summary": "Gets the list of users sorted by lastLogin, from most recent to least recent",
+                "description": "This endpoint requires admin access",
+                "tags": [
+                    "users"
+                ],
+                "security": [
+                    {
+                        "bearer_auth": []
+                    },
+                    {
+                        "basic_auth": []
+                    }
+                ],
+                "requestBody": {
+                    "required": false,
+                    "content": {
+                        "application/json": {
+                            "schema": {
+                                "type": "object",
+                                "properties": {
+                                    "search": {
+                                        "type": "string",
+                                        "default": "",
+                                        "description": "Text to search for"
+                                    },
+                                    "limit": {
+                                        "type": "integer",
+                                        "format": "int64",
+                                        "nullable": true,
+                                        "description": "Limit the amount of users returned"
+                                    },
+                                    "offset": {
+                                        "type": "integer",
+                                        "format": "int64",
+                                        "default": 0,
+                                        "description": "Offset"
+                                    }
+                                }
+                            }
+                        }
+                    }
+                },
+                "parameters": [
+                    {
+                        "name": "OCS-APIRequest",
+                        "in": "header",
+                        "description": "Required to be true for the API request to pass",
+                        "required": true,
+                        "schema": {
+                            "type": "boolean",
+                            "default": true
+                        }
+                    }
+                ],
+                "responses": {
+                    "200": {
+                        "description": "Users details returned based on last logged in information",
+                        "content": {
+                            "application/json": {
+                                "schema": {
+                                    "type": "object",
+                                    "required": [
+                                        "ocs"
+                                    ],
+                                    "properties": {
+                                        "ocs": {
+                                            "type": "object",
+                                            "required": [
+                                                "meta",
+                                                "data"
+                                            ],
+                                            "properties": {
+                                                "meta": {
+                                                    "$ref": "#/components/schemas/OCSMeta"
+                                                },
+                                                "data": {
+                                                    "type": "object",
+                                                    "required": [
+                                                        "users"
+                                                    ],
+                                                    "properties": {
+                                                        "users": {
+                                                            "type": "object",
+                                                            "additionalProperties": {
+                                                                "anyOf": [
+                                                                    {
+                                                                        "$ref": "#/components/schemas/UserDetails"
+                                                                    },
+                                                                    {
+                                                                        "type": "object",
+                                                                        "required": [
+                                                                            "id"
+                                                                        ],
+                                                                        "properties": {
+                                                                            "id": {
+                                                                                "type": "string"
+                                                                            }
+                                                                        }
+                                                                    }
+                                                                ]
+                                                            }
+                                                        }
+                                                    }
+                                                }
+                                            }
+                                        }
+                                    }
+                                }
+                            }
+                        }
+                    }
+                }
+            }
+        },
         "/ocs/v2.php/cloud/users/{userId}/subadmins": {
             "get": {
                 "operationId": "users-get-user-sub-admin-groups",
diff --git a/apps/provisioning_api/openapi-full.json b/apps/provisioning_api/openapi-full.json
index e7c094c214d..4c7b1b2e29a 100644
--- a/apps/provisioning_api/openapi-full.json
+++ b/apps/provisioning_api/openapi-full.json
@@ -946,6 +946,123 @@
                 }
             }
         },
+        "/ocs/v2.php/cloud/users/recent": {
+            "get": {
+                "operationId": "users-get-last-logged-in-users",
+                "summary": "Gets the list of users sorted by lastLogin, from most recent to least recent",
+                "description": "This endpoint requires admin access",
+                "tags": [
+                    "users"
+                ],
+                "security": [
+                    {
+                        "bearer_auth": []
+                    },
+                    {
+                        "basic_auth": []
+                    }
+                ],
+                "requestBody": {
+                    "required": false,
+                    "content": {
+                        "application/json": {
+                            "schema": {
+                                "type": "object",
+                                "properties": {
+                                    "search": {
+                                        "type": "string",
+                                        "default": "",
+                                        "description": "Text to search for"
+                                    },
+                                    "limit": {
+                                        "type": "integer",
+                                        "format": "int64",
+                                        "nullable": true,
+                                        "description": "Limit the amount of users returned"
+                                    },
+                                    "offset": {
+                                        "type": "integer",
+                                        "format": "int64",
+                                        "default": 0,
+                                        "description": "Offset"
+                                    }
+                                }
+                            }
+                        }
+                    }
+                },
+                "parameters": [
+                    {
+                        "name": "OCS-APIRequest",
+                        "in": "header",
+                        "description": "Required to be true for the API request to pass",
+                        "required": true,
+                        "schema": {
+                            "type": "boolean",
+                            "default": true
+                        }
+                    }
+                ],
+                "responses": {
+                    "200": {
+                        "description": "Users details returned based on last logged in information",
+                        "content": {
+                            "application/json": {
+                                "schema": {
+                                    "type": "object",
+                                    "required": [
+                                        "ocs"
+                                    ],
+                                    "properties": {
+                                        "ocs": {
+                                            "type": "object",
+                                            "required": [
+                                                "meta",
+                                                "data"
+                                            ],
+                                            "properties": {
+                                                "meta": {
+                                                    "$ref": "#/components/schemas/OCSMeta"
+                                                },
+                                                "data": {
+                                                    "type": "object",
+                                                    "required": [
+                                                        "users"
+                                                    ],
+                                                    "properties": {
+                                                        "users": {
+                                                            "type": "object",
+                                                            "additionalProperties": {
+                                                                "anyOf": [
+                                                                    {
+                                                                        "$ref": "#/components/schemas/UserDetails"
+                                                                    },
+                                                                    {
+                                                                        "type": "object",
+                                                                        "required": [
+                                                                            "id"
+                                                                        ],
+                                                                        "properties": {
+                                                                            "id": {
+                                                                                "type": "string"
+                                                                            }
+                                                                        }
+                                                                    }
+                                                                ]
+                                                            }
+                                                        }
+                                                    }
+                                                }
+                                            }
+                                        }
+                                    }
+                                }
+                            }
+                        }
+                    }
+                }
+            }
+        },
         "/ocs/v2.php/cloud/users/{userId}/subadmins": {
             "get": {
                 "operationId": "users-get-user-sub-admin-groups",
diff --git a/lib/private/User/Manager.php b/lib/private/User/Manager.php
index d93431a2699..639ce507f4d 100644
--- a/lib/private/User/Manager.php
+++ b/lib/private/User/Manager.php
@@ -733,6 +733,49 @@ class Manager extends PublicEmitter implements IUserManager {
 		}
 	}
 
+	/**
+	 * Gets the list of user ids sorted by lastLogin, from most recent to least recent
+	 *
+	 * @param int|null $limit how many users to fetch
+	 * @param int $offset from which offset to fetch
+	 * @param string $search search users based on search params
+	 * @return list<string> list of user IDs
+	 */
+	public function getLastLoggedInUsers(?int $limit = null, int $offset = 0, string $search = ''): array {
+		$connection = \OC::$server->getDatabaseConnection();
+		$queryBuilder = $connection->getQueryBuilder();
+		$queryBuilder->selectDistinct('uid')
+			->from('users', 'u')
+			->leftJoin('u', 'preferences', 'p', $queryBuilder->expr()->andX(
+				$queryBuilder->expr()->eq('p.userid', 'uid'),
+				$queryBuilder->expr()->eq('p.appid', $queryBuilder->expr()->literal('login')),
+				$queryBuilder->expr()->eq('p.configkey', $queryBuilder->expr()->literal('lastLogin')))
+			);
+		if($search !== '') {
+			$queryBuilder->leftJoin('u', 'preferences', 'p1', $queryBuilder->expr()->andX(
+				$queryBuilder->expr()->eq('p1.userid', 'uid'),
+				$queryBuilder->expr()->eq('p1.appid', $queryBuilder->expr()->literal('settings')),
+				$queryBuilder->expr()->eq('p1.configkey', $queryBuilder->expr()->literal('email')))
+			)
+				// sqlite doesn't like re-using a single named parameter here
+				->where($queryBuilder->expr()->iLike('uid', $queryBuilder->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%')))
+				->orWhere($queryBuilder->expr()->iLike('displayname', $queryBuilder->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%')))
+				->orWhere($queryBuilder->expr()->iLike('p1.configvalue', $queryBuilder->createPositionalParameter('%' . $connection->escapeLikeParameter($search) . '%'))
+				);
+		}
+		$queryBuilder->orderBy($queryBuilder->func()->lower('p.configvalue'), 'DESC')
+			->addOrderBy('uid_lower', 'ASC')
+			->setFirstResult($offset)
+			->setMaxResults($limit);
+
+		$result = $queryBuilder->executeQuery();
+		/** @var list<string> $uids */
+		$uids = $result->fetchAll(\PDO::FETCH_COLUMN);
+		$result->closeCursor();
+
+		return $uids;
+	}
+
 	private function verifyUid(string $uid, bool $checkDataDirectory = false): bool {
 		$appdata = 'appdata_' . $this->config->getSystemValueString('instanceid');
 
diff --git a/lib/public/IUserManager.php b/lib/public/IUserManager.php
index 851b565f617..091ccd89048 100644
--- a/lib/public/IUserManager.php
+++ b/lib/public/IUserManager.php
@@ -210,4 +210,15 @@ interface IUserManager {
 	 * @since 26.0.0
 	 */
 	public function validateUserId(string $uid, bool $checkDataDirectory = false): void;
+
+	/**
+	 * Gets the list of users sorted by lastLogin, from most recent to least recent
+	 *
+	 * @param int|null $limit how many records to fetch
+	 * @param int $offset from which offset to fetch
+	 * @param string $search search users based on search params
+	 * @return list<string> list of user IDs
+	 * @since 30.0.0
+	 */
+	public function getLastLoggedInUsers(?int $limit = null, int $offset = 0, string $search = ''): array;
 }