/// <summary>
/// gets all game genre links
/// </summary>
/// <returns>DataTable of gameGenres</returns>
public static DataTable GetAllGameGenres()
{
var sql = $@"SELECT Genres.[Genre Name], Genres.ID, GameGenres.Game
FROM Genres INNER JOIN GameGenres ON Genres.ID = GameGenres.Genre ORDER BY GameGenres.Game;";
return(DalHelper.Select(sql));
}
/// <summary>
/// gets all game genre links for public games
/// </summary>
/// <returns>DataTable of gameGenres</returns>
public static DataTable GetAllPublicGameGenres()
{
var sql = $@"SELECT Genres.[Genre Name], Genres.ID, GameGenres.Game
FROM Games INNER JOIN (Genres INNER JOIN GameGenres ON Genres.ID = GameGenres.Genre) ON Games.ID = GameGenres.Game WHERE Games.[Review Status] = 1;";
return(DalHelper.Select(sql));
}
/// <summary>
/// Gets all outgoing friend request users
/// </summary>
/// <param name="user">user who initiated requests</param>
/// <returns>dataTable of users</returns>
public static DataTable OutgoingFriendRequests(int user)
{
var sql = $@"(SELECT Users.*
FROM Users INNER JOIN UserFriends ON Users.ID = UserFriends.[User 2]
WHERE(((UserFriends.[User 1]) ={user}) AND((UserFriends.Type) = FALSE)))";
return(DalHelper.Select(sql));
}
/// <summary>
/// Gets all incoming developer request developers
/// </summary>
/// <param name="user">user that is requested</param>
/// <returns>dataTable of developers</returns>
public static DataTable IncomingDeveloperRequests(int user)
{
var sql = $@"(SELECT Developer.*
FROM Developer INNER JOIN DeveloperInvitations ON Developer.ID = DeveloperInvitations.[From]
WHERE(((DeveloperInvitations.[To]) ={user}) AND((DeveloperInvitations.Fulfilled) = FALSE)))";
return(DalHelper.Select(sql));
}
/// <summary>
/// get all outgoing developer request users
/// </summary>
/// <param name="developer">developer that sent requests</param>
/// <returns>dataTable of users</returns>
public static DataTable OutgoingDeveloperRequests(int developer)
{
var sql = $@"(SELECT Users.*
FROM Users INNER JOIN DeveloperInvitations ON Users.ID = DeveloperInvitations.[To]
WHERE(((DeveloperInvitations.[From]) ={developer}) AND((DeveloperInvitations.Fulfilled) = FALSE)))";
return(DalHelper.Select(sql));
}
/// <summary>
/// Returns a list of friends that play a game
/// </summary>
/// <param name="userId">id of user to check his friends</param>
/// <param name="gameId">id of game that friends play</param>
/// <returns>DataTable of friends</returns>
public static DataTable GetFriendsWhoPlayGame(int userId, int gameId)
{
var selectDirection = "SELECT Users.*" +
"FROM(Users INNER JOIN UserFriends ON Users.ID = UserFriends.[User $2]) INNER JOIN UserGames ON Users.ID = UserGames.[User] " +
$"WHERE(((UserFriends.[User $1]) = {userId}) AND((UserGames.Game) = {gameId}))";
var sql = AllDirections(selectDirection);
return(DalHelper.Select(sql));
}
/// <summary>
/// returns all activity from user
/// </summary>
/// <param name="user">id of user</param>
/// <returns>DataTable of user activity</returns>
public static DataTable UserActivity(int user)
{
return(DalHelper.Select($@"
SELECT TOP 30
*
FROM UserActivity
WHERE User = {user}
ORDER BY Timestamp DESC;
"));
}
/// <summary>
/// Returns a list of friends of a user
/// </summary>
/// <param name="userId">user to return his friends</param>
/// <returns>DataTable of friends</returns>
public static DataTable GetFriendsOfUser(int userId)
{
var selectDirection = $@"(SELECT Users.*
FROM Users INNER JOIN UserFriends ON Users.ID = UserFriends.[User $1]
WHERE(((UserFriends.[User $2]) ={userId}) AND((UserFriends.Type) = TRUE)))";
var sql = AllDirections(selectDirection);
return(DalHelper.Select(sql));
}
/// <summary>
/// gets all genres of a game
/// </summary>
/// <param name="gameId">id of game</param>
/// <returns>DataTable of genres</returns>
public static DataTable GetGenresByGame(int gameId)
{
var sql = $@"SELECT Genres.*
FROM Games
INNER JOIN(Genres INNER JOIN GameGenres ON Genres.ID = GameGenres.Genre)
ON Games.ID = GameGenres.Game
WHERE Games.ID = {gameId};";
return(DalHelper.Select(sql));
}
/// <summary>
/// returns stats for a game in one row
/// </summary>
/// <param name="id">id of game</param>
/// <returns>dataRow of sales</returns>
public static DataRow getGameStats(int id)
{
var tb = DalHelper.Select(
$"SELECT COUNT(Game) AS Purchases, SUM(Cost) AS Revenue from UserGames WHERE Game = {id} GROUP BY Game");
if (tb.Rows.Count == 0)
{
return(null);
}
return(tb.Rows[0]);
}
/// <summary>
/// returns the average review rating
/// </summary>
/// <param name="gameId">id of game</param>
/// <returns>double with the average rating in stars, e.g 4.7</returns>
public static double AverageRating(int gameId)
{
DataTable tb = DalHelper.Select($@"SELECT Avg(GameReviews.Stars) AS Rating
FROM GameReviews
WHERE Game = {gameId}
GROUP BY GameReviews.Game;");
if (tb.Rows.Count == 0)
{
return(-1);
}
return((double)tb.Rows[0][0]);
}
/// <summary>
/// Method that checks if a user and password are valid for authentication
/// </summary>
/// <param name="username">user</param>
/// <param name="password">password</param>
/// <returns>if the username password combination is valid</returns>
public static bool Authenticate(string username, string password)
{
var tb = DalHelper.Select("SELECT HashPass FROM Users WHERE Username = '******'");
if (tb.Rows.Count == 0)
{
return(false);
}
var row = tb.Rows[0];
var hash = (string)row["HashPass"];
// Use the BCrypt library for verification
return(BCrypt.Net.BCrypt.EnhancedVerify(password, hash));
}
/// <summary>
/// Retrieves a list of all games of a certain user
/// </summary>
/// <param name="userId">id of user</param>
/// <returns></returns>
public static DataTable GetUserGames(int userId)
{
return(DalHelper.Select(
$"SELECT * FROM Games INNER JOIN UserGames ON Games.ID = UserGames.Game WHERE User = {userId}"));
}
/// <summary>
/// returns if exists an unfulfilled developer request from develoepr to user
/// </summary>
/// <param name="dev">id of developer</param>
/// <param name="user">user invited</param>
/// <returns>true if exists else false</returns>
public static bool ExistsDeveloperRequest(int dev, int user)
{
return(DalHelper.Select($"SELECT ID FROM DeveloperInvitations WHERE [From] = {dev} AND [To] = {user} AND Fulfilled = FALSE").Rows.Count > 0);
}
/// <summary>
/// gets the total sales stats for everything
/// </summary>
/// <returns>DataTable of sale statistics</returns>
public static DataTable TotalSalesStats()
{
return(DalHelper.Select(
"SELECT CDate(Format(UserGames.Timestamp,\"dd/mm/yyyy\")) AS [Timestamp], SUM(Cost) AS Revenue, COUNT(Cost) AS Purchases FROM UserGames " +
"GROUP BY CDate(Format(UserGames.Timestamp, \"dd/mm/yyyy\")) ORDER BY CDate(Format(UserGames.Timestamp, \"dd/mm/yyyy\"));"));
}
/// <summary>
/// gets the total sales stats for a company
/// </summary>
/// <param name="devId">id of developer</param>
/// <returns>DataTable of sale statistics</returns>
public static DataTable CompanySalesStats(int devId)
{
return(DalHelper.Select(
"SELECT CDate(Format(UserGames.Timestamp,\"dd/mm/yyyy\")) AS [Timestamp], SUM(Cost) AS Revenue, COUNT(Cost) AS Purchases FROM Games INNER JOIN UserGames ON Games.ID = UserGames.Game " +
$"WHERE Developer = {devId} GROUP BY CDate(Format(UserGames.Timestamp, \"dd/mm/yyyy\")) ORDER BY CDate(Format(UserGames.Timestamp, \"dd/mm/yyyy\"));"));
}
/// <summary>
/// gets a count of all incoming dev requests
/// </summary>
/// <param name="userId">id of user</param>
/// <returns>int amount of requests</returns>
public static int IncomingDeveloperRequestsCount(int userId)
{
return(DalHelper.Select($"SELECT ID FROM DeveloperInvitations WHERE [To] = {userId} AND Fulfilled = FALSE").Rows.Count);
}
/// <summary>
/// returns all public (accepted review) games from the database
/// </summary>
/// <returns>DataTable of all games</returns>
public static DataTable AllPublicGames()
{
return(DalHelper.Select("SELECT * FROM Games WHERE [Review Status] = 1 ORDER BY Games.ID"));
}
/// <summary>
/// returns all public (accepted review) games from the database
/// </summary>
/// <returns>DataTable of all games</returns>
public static DataTable AllPublicGamesOrder(string orderBy)
{
return(DalHelper.Select($"SELECT * FROM Games WHERE [Review Status] = 1 ORDER BY `{orderBy}`"));
}
/// <summary>
/// gets photos by dev
/// </summary>
/// <param name="developer">id of developer</param>
/// <returns>DataTable of photos</returns>
public static DataTable GetPhotosFromDeveloper(int developer)
{
return(DalHelper.Select(
$"SELECT GamePhotos.* FROM GamePhotos INNER JOIN Games ON GamePhotos.Game = Games.Id WHERE Games.Developer = {developer};"));
}
/// <summary>
/// gets all reviews from a certain
/// </summary>
/// <param name="userId">id of user</param>
/// <returns>DataTable of reviews</returns>
public static DataTable ReviewsByUser(int userId)
{
return(DalHelper.Select("SELECT GameReviews.*, Users.Username, Games.[Game Name]" +
" FROM Users INNER JOIN(Games INNER JOIN GameReviews ON Games.ID = GameReviews.Game) ON Users.ID = GameReviews.User" +
$" WHERE Users.ID = {userId}"));
}
/// <summary>
/// gets a count of all incoming friend requests
/// </summary>
/// <param name="userId">id of user</param>
/// <returns>amount of incoming friend requests</returns>
public static int IncomingFriendRequestsCount(int userId)
{
return(DalHelper.Select($"SELECT [User 1] FROM UserFriends WHERE [User 2] = {userId} AND [Type] = FALSE").Rows.Count);
}
