/// <summary>
/// changes the status of a friend request between sender and recipiant
/// </summary>
public static void ChangeStatus(string sender, string recipiant, int status)
{
if (DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.FRIENDREQUESTSTBL, new string[] { REQUESTSTATUSEFLD }, new string[] { status.ToString() }, $"{RECIPIANTFLD}='{recipiant}' AND {SENDERFLD}='{sender}'")) == 0)
{
throw new Exception("Didn't Change any status");
}
}
/// <summary>
/// gets the range of elos of a certin rank
/// </summary>
public static string GetRankRange(int rankID)
{
if (DalHelper.IsExist($"SELECT Origin.[Rank ID] , MIN(Other.[Lowest Elo]) AS MaxELO, Origin.[Lowest Elo] FROM Ranking AS Origin, Ranking AS Other WHERE Other.[Lowest Elo] > Origin.[Lowest Elo] AND Origin.[Rank ID] = {rankID} GROUP BY Origin.[Rank ID], Origin.[Lowest Elo]"))
{
DataRow dr = DalHelper.SelectRow($"SELECT Origin.[Rank ID] , MIN(Other.[Lowest Elo]) AS MaxELO, Origin.[Lowest Elo] FROM Ranking AS Origin, Ranking AS Other WHERE Other.[Lowest Elo] > Origin.[Lowest Elo] AND Origin.[Rank ID] = {rankID} GROUP BY Origin.[Rank ID], Origin.[Lowest Elo]");
return(dr["Lowest Elo"].ToString() + " - " + dr["MaxElo"].ToString());
}
return(GetRankLowestValue(rankID).ToString() + "+");
}
/// <summary>
/// adds a user
/// </summary>
public static void AddUser(string email, string password, int type, string fName, string lName, DateTime bDate, string username)
{
string sql = DalHelper.SimpleInsertQuery(Constants.USERSTBL, new string[] { EMAILFLD, PASSWORDFLD, TYPEFLD, FIRSTNAMEFLD, LASTNAMEFLD, BIRTHDATEFLD, USERNAMEFLD }, new string[] { $"'{email}'", $"'{password}'", type.ToString(), $"'{fName}'", $"'{lName}'", bDate.ToOADate().ToString(), $"'{username}'" });
if (!DalHelper.insertWithoutCreatingID(sql))
{
throw new Exception("Couldn't Add User To db");
}
}
public static int HowManyGamesUserWonOrLost(string username, bool won)
{
string sql = $"SELECT COUNT({GAME}) AS amount, {USERNAME}, {HASWON} FROM {Constants.USERSINGAMESTBL} Where {HASWON} = {won} AND {USERNAME} = '{username}' GROUP BY {USERNAME}, {HASWON}";
if (DalHelper.IsExist(sql))
{
return((int)DalHelper.SelectRow(sql)["amount"]);
}
return(0);
}
/// <summary>
/// adds a user to users in games table
/// </summary>
public static void AddUserToGame(string username, int GameID)
{
try
{
DalHelper.insertWithoutCreatingID(DalHelper.SimpleInsertQuery(Constants.USERSINGAMESTBL, new string[] { USERNAME, GAME }, new string[] { $"'{username}'", GameID.ToString() }));
}
catch (Exception e)
{
throw new Exception("Couldn't add user To Game", e);
}
}
/// <summary>
/// updates a record of a user in a game
/// </summary>
public static void UpdateUserInGame(string username, int GameID, int xpAdded, int eloAdded, bool HasWon, int CardsInHand)
{
try
{
DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.USERSINGAMESTBL, new string[] { XP, ELO, HASWON, CARDSINHAND }, new string[] { xpAdded.ToString(), eloAdded.ToString(), HasWon.ToString(), CardsInHand.ToString() }, $"{USERNAME} = '{username}' AND {GAME} = {GameID}"));
}
catch (Exception e)
{
throw new Exception("Couldn't update user", e);
}
}
/// <summary>
/// finds the records of users in a specific game in order of winners.
/// </summary>
public static DataTable FindUsersInGame(int gameID)
{
return(DalHelper.SelectTable($"SELECT * FROM {Constants.USERSINGAMESTBL} WHERE {GAME} = {gameID} ORDER BY {CARDSINHAND} ASC"));
}
/// <summary>
/// selects all users in user table
/// </summary>
public static DataTable SelectAllUsers()
{
return(DalHelper.SelectTable($"SELECT * FROM {Constants.USERSTBL}"));
}
/// <summary>
/// returns all records of friend requests with specific status a user has recieved
/// </summary>
public static DataTable FriendRequestsWithStatusRecieved(string username, int status)
{
return(DalHelper.SelectTable(FriendRequestSQL + $" WHERE {REQUESTSTATUSEFLD} = {status} AND {RECIPIANTFLD} = '{username}'"));
}
/// <summary>
/// a user in game record of user in game
/// </summary>
public static DataRow FindUserInGame(string user, int gameID)
{
return(DalHelper.SelectRow($"SELECT * FROM {Constants.USERSINGAMESTBL} WHERE {USERNAME} = '{user}' AND {GAME} = {gameID}"));
}
/// <summary>
/// Updates a user's username
/// </summary>
public static void UpdateUsername(string oldUsername, string newUsername)
{
DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.USERSTBL, new string[] { USERNAMEFLD }, new string[] { $"'{newUsername}'" }, $"{USERNAMEFLD} = '{oldUsername}'"));
}
/// <summary>
/// updates a user's picture location in his record
/// </summary>
public static void UpdateUserPic(string username, string Picture)
{
DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.USERSTBL, new string[] { PICTUREFLD }, new string[] { $"'{Picture}'" }, $"{USERNAMEFLD} = '{username}'"));
}
/// <summary>
/// selects a user record
/// </summary>
public static DataRow SelectUser(string username)
{
return(DalHelper.SelectRow($"SELECT * FROM {Constants.USERSTBL} WHERE {USERNAMEFLD}='{username}'"));
}
/// <summary>
/// return's whether a friend request between two users exists
/// </summary>
public static bool FriendRequestExists(string username1, string username2)
{
return(DalHelper.IsExist($"SELECT * FROM {Constants.FRIENDREQUESTSTBL} WHERE ({RECIPIANTFLD} = '{username1}' AND {SENDERFLD} = '{username2}') OR ({RECIPIANTFLD} = '{username2}' AND {SENDERFLD} = '{username1}')"));
}
public async Task <IEnumerable <SpParameter> > GetSqlParameters()
{
return(await DalHelper.Query <SpParameter>(SP_GET_PARAMETERS));
}
public async Task <IEnumerable <DefinedTableType> > GetDefinedTableType()
{
return(await DalHelper.Query <DefinedTableType>(SP_GET_DEFINED_TABLE_TYPE));
}
/// <summary>
/// gets a season by it's ID
/// </summary>
public static DataRow GetSeason(int seasonID)
{
return(DalHelper.SelectRow($"SELECT * FROM {Constants.SEASONSTBL} WHERE {SEASONID} = {seasonID}"));
}
/// <summary>
/// gets current season ID
/// </summary>
public static int GetCurrentSeason()
{
return((int)DalHelper.SelectRow($"SELECT * FROM {Constants.SEASONSTBL} WHERE {DateTime.Now.ToOADate().ToString()} BETWEEN {STARTDATE} AND {ENDDATE}")["Season ID"]);
}
/// <summary>
/// inserts a new season and returns it's ID
/// </summary>
public static int CreateNewSeason(DateTime startDate, DateTime endDate)
{
return(DalHelper.Insert(DalHelper.SimpleInsertQuery(Constants.SEASONSTBL, new string[] { STARTDATE, ENDDATE }, new string[] { startDate.ToOADate().ToString(), endDate.ToOADate().ToString() })));
}
/// <summary>
/// updates a user's xp in his record
/// </summary>
public static void UpdateUserXP(int xp, string username)
{
DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.USERSTBL, $"{XPFLD} = {xp}", $"{USERNAMEFLD} = '{username}' "));
}
/// <summary>
/// updates a user's level in his record
/// </summary>
public static void UpdateUserLevel(int level, string username)
{
DalHelper.Update(DalHelper.SimpleUpdateQuery(Constants.USERSTBL, $"{LEVELFLD} = {level}", $"{USERNAMEFLD} = '{username}'"));
}
/// <summary>
/// Creates a new friend request from sender to recipiant
/// </summary>
public static void AddFriend(string sender, string recipiant)
{
DalHelper.insertWithoutCreatingID(DalHelper.SimpleInsertQuery(Constants.FRIENDREQUESTSTBL, new string[] { SENDERFLD, RECIPIANTFLD }, new string[] { "'" + sender + "'", "'" + recipiant + "'" }));
}
/// <summary>
/// selects a user that has given username and password
/// </summary>
public static DataRow SelectUsernameWithPassword(string username, string password)
{
return(DalHelper.SelectRow($"SELECT * FROM {Constants.USERSTBL} WHERE {USERNAMEFLD}='{username}' AND {PASSWORDFLD}='{password}'"));
}
/// <summary>
/// a method that finds and returns the avarage date of birth among users
/// </summary>
public static DateTime AvarageBirthDate()
{
string sql = $"SELECT CDate(Avg({BIRTHDATEFLD})) as avgbirth FROM {Constants.USERSTBL}";
return((DateTime)DalHelper.SelectRow(sql)["avgbirth"]);
}
/// <summary>
/// checks whether a user with a given username exists
/// </summary>
public static bool ExistUsername(string username)
{
return(DalHelper.IsExist($"SELECT * FROM {Constants.USERSTBL} WHERE {USERNAMEFLD} = '{username}'"));
}
/// <summary>
/// returns all records of friend requests that a user has recieved
/// </summary>
public static DataTable AllRecievedFriendRequestsOfUser(string username)
{
return(DalHelper.SelectTable(FriendRequestSQL + $" WHERE {RECIPIANTFLD} = '{username}'"));
}
/// <summary>
/// searches users which their usernames contain search term
/// </summary>
public static DataTable SearchUsername(string searchTerm)
{
return(DalHelper.SelectTable($"SELECT * FROM {Constants.USERSTBL} WHERE {USERNAMEFLD} LIKE '%{searchTerm}%'"));
}
/// <summary>
/// returns whether a user is (or was) in a game
/// </summary>
public static bool IsUserInGame(string user, int gameID)
{
return(DalHelper.IsExist($"SELECT * FROM {Constants.USERSINGAMESTBL} WHERE {USERNAME} = '{user}' AND {GAME} = {gameID}"));
}
/// <summary>
/// Returns the amount of users
/// </summary>
public static int UserCount()
{
return((int)DalHelper.SelectRow($"SELECT COUNT({USERNAMEFLD}) AS Amount FROM {Constants.USERSTBL}")["Amount"]);
}
/// <summary>
/// returns all records of friend requests that a user has sent
/// </summary>
public static DataTable AllSentFriendRequestsOfUser(string username)
{
return(DalHelper.SelectTable(FriendRequestSQL + $" WHERE {SENDERFLD} = '{username}'"));
}