/**
* @param $userId
* @param $token
* @return mixed
*/
public static function getUserDataByUserIdAndToken(int $userId, string $token) : mixed
{
$database = DbFactory::getFactory()->getConnection();
$query = $database->prepare("SELECT user_id, user_name, user_email, user_password_hash,\n user_account_type, user_failed_logins, user_last_failed_login\n FROM users\n WHERE user_id = :user_id\n AND user_remember_me_token = :user_remember_me_token\n AND user_remember_me_token IS NOT NULL\n LIMIT 1");
$query->execute(array(':user_id' => $userId, ':user_remember_me_token' => $token));
return $query->fetch();
}
public static function getAllConferences()
{
$database = DbFactory::getFactory()->getConnection();
$sql = "SELECT c.title, c.conference_id, u.user_name, u.user_id, v.venue_name\n FROM conferences AS c\n INNER JOIN users AS u ON u.user_id = c.conference_owner_id\n INNER JOIN venues AS v ON v.venue_id = c.venue_id\n WHERE c.deleted = 0";
$query = $database->prepare($sql);
$query->execute();
return $query->fetchAll(PDO::FETCH_ASSOC);
}
public static function getLecturesByConferenceId(int $id)
{
$database = DbFactory::getFactory()->getConnection();
$sql = "SELECT *\n FROM lectures AS l\n WHERE l.conference_id = :conference_id";
$query = $database->prepare($sql);
$query->execute(array(':conference_id' => $id));
return $query->fetchAll(PDO::FETCH_ASSOC);
}
public static function createVenueInDb(string $venueName)
{
$database = DbFactory::getFactory()->getConnection();
// write new users data into database
$sql = "INSERT INTO venues (venue_name)\n VALUES (:venue_name)";
$query = $database->prepare($sql);
$query->execute(array(':venue_name' => $venueName));
$count = $query->rowCount();
if ($count == 1) {
return true;
}
return false;
}
/**
* Checks for user session concurrency
* (e.g. two users logged in with the same account)
* @return bool
*/
public static function concurrentSessionsExist() : bool
{
$sessionId = session_id();
$userId = Session::get('user_id');
if (isset($userId) && isset($sessionId)) {
$database = DbFactory::getFactory()->getConnection();
$sql = "SELECT session_id FROM users WHERE user_id = :user_id LIMIT 1";
$query = $database->prepare($sql);
$query->execute(array(":user_id" => $userId));
$result = $query->fetch();
$userSessionId = !empty($result) ? $result->session_id : null;
return $sessionId !== $userSessionId;
}
return false;
}
/**
* Increment the user failed-login-count in the database
* @param $userName
*/
private static function incrementUserFailedLoginCountInDb($userName)
{
$database = DbFactory::getFactory()->getConnection();
$sql = "UPDATE users\n SET user_failed_logins = user_failed_logins+1, user_last_failed_login = :user_last_failed_login\n WHERE user_name = :user_name OR user_email = :user_name\n LIMIT 1";
$query = $database->prepare($sql);
$query->execute(array(':user_name' => $userName, ':user_last_failed_login' => time()));
}
/**
* Create new user in the database
* @param string $userName
* @param string $userPasswordHash
* @param string $userEmail
* @param int $userCreationTimestamp
* @return bool
*/
public static function createUserInDb(string $userName, string $userPasswordHash, string $userEmail, int $userCreationTimestamp) : bool
{
$database = DbFactory::getFactory()->getConnection();
// write new users data into database
$sql = "INSERT INTO users (user_name, user_password_hash, user_email, user_creation_timestamp)\n VALUES (:user_name, :user_password_hash, :user_email, :user_creation_timestamp)";
$query = $database->prepare($sql);
$query->execute(array(':user_name' => $userName, ':user_password_hash' => $userPasswordHash, ':user_email' => $userEmail, ':user_creation_timestamp' => $userCreationTimestamp));
$count = $query->rowCount();
if ($count == 1) {
return true;
}
return false;
}