/// <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>
/// changes a game code status to used by certain user
/// this does not check if the code was previously used, and does not give the user the game
/// </summary>
/// <param name="code">16 char string of game code</param>
/// <param name="user">id of user</param>
/// <returns>true if rows were updated, meaning a game code was changed and the code worked, else false</returns>
public static bool UseGameCode(string code, int user)
{
return(DalHelper.UpdateWhere("GameCodes",
"Used", true,
"Redeemed By", user,
"Code", code) > 0);
}
/// <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>
///
/// </summary>
/// <param name="id"></param>
/// <param name="filename"></param>
/// <param name="type"></param>
/// <returns></returns>
public static int InsertPhoto(int id, string filename, PhotoType type)
{
string field = type == PhotoType.UserPhotos ? "User" : "Game";
return(DalHelper.Insert($"INSERT INTO {type.ToString()} ({field}, Photo) VALUES ({id}, @filename)"
, new OleDbParameter("@filename", filename)));
}
/// <summary>
/// Inserts a user into the database
/// </summary>
/// <param name="email">email of the user</param>
/// <param name="username">username of the user</param>
/// <param name="password">plaintext password of the user</param>
/// <returns>id of new user</returns>
public static int InsertUser(string email, string username, string password)
{
return(DalHelper.Insert(
"INSERT INTO Users" +
"(Email,Username,HashPass)" +
$"VALUES('{email}','{username}', '{BCrypt.Net.BCrypt.EnhancedHashPassword(password)}')"));
}
/// <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>
/// Inserts a new game update to the database
/// </summary>
/// <param name="version">version name, e.g 1.12.4 or 19w34</param>
/// <param name="updateName">name of update e.g "The End Update"</param>
/// <param name="description">description of update, written in markdown</param>
/// <param name="game">id of game</param>
/// <returns>id of update</returns>
public static int InsertUpdate(string version, string updateName, string description, int game)
{
return(DalHelper.Insert(
$"INSERT INTO GameUpdates ([Update Version],[Update Name], [Update Description], [Game]) VALUES (@version,@updateName,@description,{game})",
new OleDbParameter("@version", version),
new OleDbParameter("@updateName", updateName),
new OleDbParameter("@description", description)));
}
/// <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 a game from a given code
/// </summary>
/// <param name="code">16 char string of game code</param>
/// <returns>id of game if found, else -1</returns>
public static int GetGameFromCode(string code)
{
DataRow row = DalHelper.RowWhere("GameCodes", "Code", code, "Game");
if (row == null)
{
return(-1);
}
return((int)row["Game"]);
}
/// <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>
/// inserts a game into the database
/// </summary>
/// <param name="gameName">name of game</param>
/// <param name="link">link of game</param>
/// <param name="description">description of game</param>
/// <param name="background">background filename of game</param>
/// <param name="logo">logo filename of game</param>
/// <param name="developer">developer id of game</param>
/// <param name="price">price of game</param>
/// <returns>id of game</returns>
public static int InsertGame(string gameName, string link, string description, string background, string logo,
int developer, double price)
{
return(DalHelper.Insert(
"INSERT INTO Games ([Game Name],[Game Link],Description,Background,Logo,Developer,Price) " +
$"VALUES (@gameName,@link,@desc,@background,@logo,{developer},{price})",
new OleDbParameter("@gameName", gameName),
new OleDbParameter("@link", gameName),
new OleDbParameter("@desc", gameName),
new OleDbParameter("@background", gameName),
new OleDbParameter("@logo", gameName)));
}
/// <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>
/// Updates a game
/// </summary>
/// <param name="id">id of game</param>
/// <param name="description">description of game</param>
/// <param name="background">background of game</param>
/// <param name="logo">logo of game</param>
/// <param name="developerId">developer id of game</param>
/// <param name="gameLink">link of game</param>
/// <param name="gameName">name of game</param>
/// <param name="price">price of game</param>
public static void UpdateGame(int id, string description, string background, string logo, int developerId, string gameLink, string gameName, double price)
{
DalHelper.Update($@"UPDATE Games
SET [Game Name] = @gameName,
[Game Link] = @gameLink,
[Description] = @desc,
[Background] = @background,
[Logo] = @logo,
[Developer] = @dev,
[Price] = @price
WHERE ID = {id}",
new OleDbParameter("@gameName", gameName),
new OleDbParameter("@gameLink", gameLink),
new OleDbParameter("@desc", description),
new OleDbParameter("@background", background),
new OleDbParameter("@logo", logo),
new OleDbParameter("@dev", developerId),
new OleDbParameter("@price", price));
}
/// <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>
/// Adds a user game relation to the store (happens when user buys a game)
/// </summary>
/// <param name="user">id of user</param>
/// <param name="game">id of game</param>
/// <param name="price">the cost the user paid</param>
public static void AddGame(int user, int game, double price)
{
DalHelper.Insert($"INSERT INTO UserGames ([User],[Game],[Cost]) VALUES ({user},{game},{price});");
}
/// <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>
/// returns if a game is owned by user
/// </summary>
/// <param name="gameId">id of game</param>
/// <param name="userId">id of user</param>
/// <returns></returns>
public static bool GameOwnedByUser(int gameId, int userId)
{
return(DalHelper.RowExists($"SELECT Game FROM UserGames WHERE Game = {gameId} AND [User] = {userId}"));
}
/// <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 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 an activity by id
/// </summary>
/// <param name="id">id of activity</param>
/// <returns>DataRow of activity</returns>
public static DataRow GetActivity(int id)
{
return(DalHelper.GetRowById(id, "UserActivity"));
}
/// <summary>
/// Inserts a new user activity
/// </summary>
/// <param name="activity">activity string</param>
/// <param name="userId">id of user</param>
public static void InsertActivity(string activity, int userId)
{
var sql = $"INSERT INTO UserActivity ([ActivityText],[User]) VALUES('{activity}',{userId})";
DalHelper.Insert(sql);
}
/// <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>
/// Updates the review status for a game
/// </summary>
/// <param name="newReviewStatus">the new status id</param>
/// <param name="id">id of game</param>
public static void UpdateReviewStatus(int newReviewStatus, int id)
{
DalHelper.UpdateWhere("Games", "Review Status", newReviewStatus, "ID", id);
}
/// <summary>
/// returns all games that need reviewing from the database
/// </summary>
/// <returns>DataTable of all games</returns>
public static DataTable GamesToReview()
{
return(DalHelper.AllWhere("Games", "Review Status", 0));
}
