public static ServerStatistic GetServerStatisticById(Cxt cxt)
{
ServerStatistics ServerStatistics = new ServerStatistics(cxt,
BaseCollection.ExecuteSql("select top 1 * from ServerStatistics order by ServerStatisticsID desc"));
return(ServerStatistics[0]);
}
public static int IsCorrectSchTournamentUserCount(int tournamentID)
{
DataTable dt = BaseCollection.ExecuteSql("select COUNT(tu.userid) Counter, tu.teamid FROM tournamentuser tu INNER JOIN tournament t ON t.tournamentid = tu.tournamentid INNER JOIN team on tu.teamid = team.teamid WHERE tu.tournamentid = " + tournamentID + " and tu.statusid <> 4 GROUP BY tu.teamid");
int counter = 0;
Kv kv = new Kv();
if (dt.Rows.Count > 0)
{
counter = Convert.ToInt32(dt.Rows[0][0]);
}
else
{
return((int)MsgE.ErrorTournamentTeamCount);
}
foreach (DataRow item in dt.Rows)
{
if (counter != Convert.ToInt32(item[0]))
{
return((int)MsgE.ErrorTournamentTeamCount);
}
}
return(0);
}
public static UserGameType GetUserGameRating(Cxt cxt, int chessTypeID, int gameTypeID, int userID)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.UserGameType, "SELECT TOP 1 * FROM [UserGameType] WHERE ChessTypeID = @p1 AND GameTypeID = @p2 AND UserID = @p3", chessTypeID, gameTypeID, userID);
UserGameType userGameType = null;
if (table.Rows.Count > 0)
{
userGameType = new UserGameType(cxt, table.Rows[0]);
}
else
{
//userGameType = null;
userGameType = new UserGameType();
userGameType.UserID = userID;
userGameType.GameTypeID = gameTypeID;
userGameType.ChessTypeID = chessTypeID;
userGameType.NoOfGames = 0;
userGameType.StoredMatches = 0;
if (chessTypeID == (int)ChessTypeE.Human)
{
userGameType.EloRating = 1500;
}
else
{
userGameType.EloRating = 2200;
}
}
return(userGameType);
}
public static DataTable UpdateTournamentWantinStatus(StatusE statusID, string TournamentWantinUserIDs)
{
// status id is deleted
StringBuilder sb = new StringBuilder();
sb.Append("update TournamentWantinUser set TournamentUserStatusID = ").Append(statusID.ToString("d")).Append(" WHERE TournamentWantinUserID in (").Append(TournamentWantinUserIDs).Append(")");
return(BaseCollection.ExecuteSql(sb.ToString()));
}
public static ScheduledJob GetTopScheduledJob(Cxt cxt)
{
ScheduledJobs ScheduledJob = new ScheduledJobs(cxt,
BaseCollection.ExecuteSql(InfiChess.ScheduledJob,
"select top 1 * from ScheduledJob order by ScheduledJobID desc"));
return(ScheduledJob[0]);
}
public static DataTable GetTournamentPrizeByTournamentID(Cxt cxt, int TournamentID)
{
string sql = @"SELECT tp.*,tpc.Name as CategoryName FROM TournamentPrize tp
inner join TournamentPrizeCategory tpc on tp.TournamentPrizeCategoryId = tpc.TournamentPrizeCategoryID
WHERE TournamentID = @p1 ORDER BY PrizePosition";
return(BaseCollection.ExecuteSql(InfiChess.TournamentPrize, sql, TournamentID));
}
public static DataTable UpdateStatus(StatusE statusID, string userIDs)
{
// status id is deleted
StringBuilder sb = new StringBuilder();
sb.Append("update [User] set statusid = ").Append(statusID.ToString("d")).Append(" WHERE userID in (").Append(userIDs).Append(")");
return(BaseCollection.ExecuteSql(sb.ToString()));
}
public static ServerEventLog GetTopServerEventLog(Cxt cxt)
{
ServerEventLogs ServerEventLogs = new ServerEventLogs(cxt,
BaseCollection.ExecuteSql(InfiChess.ServerEventLog,
"select top 1 * from servereventlog order by servereventlogid desc"));
return(ServerEventLogs[0]);
}
public static DataTable UnBlockedIPs(string parm)
{
// status id is deleted
StringBuilder sb = new StringBuilder();
sb.Append("Delete FROM BlockedIP ").Append(" WHERE BlockedIPID in (").Append(parm).Append(")");
return(BaseCollection.ExecuteSql(sb.ToString()));
}
public static DataTable UpdateStatus(StatusE statusID, string parm)
{
// status id is deleted
StringBuilder sb = new StringBuilder();
sb.Append("update TournamentPrize set statusid = ").Append(statusID.ToString("d")).Append(" WHERE TeamID in (").Append(parm).Append(")");
return(BaseCollection.ExecuteSql(sb.ToString()));
}
public static DataTable UpdateBanStatus(StatusE statusID, string userIDs)
{
// status id is deleted
StringBuilder sb = new StringBuilder();
sb.Append("update [User] set statusid = ").Append(statusID.ToString("d"));
//sb.Append(", BanMachineKey = '").Append(machineKey).Append("'");
sb.Append(", BanEndDate = NULL, BanEndTime = NULL, BanStartDate = NULL, BanStartTime = NULL ");
sb.Append(" WHERE userID in (").Append(userIDs).Append(")");
return(BaseCollection.ExecuteSql(sb.ToString()));
}
public static UserVoucher GetUserVoucher(Cxt cxt, string voucherNo)
{
UserVoucher userVoucher = null;
DataTable dt = BaseCollection.ExecuteSql(InfiChess.UserVoucher, "SELECT * FROM UserVoucher where VoucherNo = @p1", voucherNo);
if (dt.Rows.Count > 0)
{
userVoucher = new UserVoucher(cxt, dt.Rows[0]);
}
return(userVoucher);
}
public static DataTable GetAcceptedChallenge(Cxt cxt, int roomID, int userID)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.Challenge, "SELECT TOP 1 * FROM Challenge WHERE RoomID=@p1 AND ChallengerUserID=@p2 AND ChallengeStatusID = 2 AND StatusID = 1", roomID, userID);
if (table.Rows.Count > 0)
{
Challenge c = new Challenge(cxt, table.Rows[0]);
c.ChallengeStatusIDE = ChallengeStatusE.Played;
c.StatusIDE = StatusE.Inactive;
c.Save();
}
return(table);
}
public static void UpdateInternet(double t, int id)
{
double maxLimit = 9999999.99;
double temp = t;
if (temp > maxLimit) // to eliminate 'Arithmetic overflow' error
{
temp = maxLimit;
}
if (temp < -maxLimit) // to eliminate 'Arithmetic overflow' error
{
temp = -maxLimit;
}
BaseCollection.ExecuteSql("update [User] set Internet = " + temp.ToString("0.##") + " WHERE userID = " + id.ToString());
}
public static User GetUserByEmail(Cxt cxt, string email)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "select * from [User] where email=@p1", email);
User user = null;
if (table.Rows.Count > 0)
{
user = new User(cxt, table.Rows[0]);
}
else
{
user = new User();
}
return(user);
}
public static User GetUser(Cxt cxt, string userName)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "select * from [User] where LOWER(UserName)=LOWER(@p1)", userName);
User user = null;
if (table.Rows.Count > 0)
{
user = new User(cxt, table.Rows[0]);
}
else
{
user = new User();
}
return(user);
}
public static User GetUserByID(Cxt cxt, int userID)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "select * from [User] where UserID=@p1", userID.ToString());
User user = null;
if (table.Rows.Count > 0)
{
user = new User(cxt, table.Rows[0]);
}
else
{
user = new User();
}
return(user);
}
public static User GetFirstInActiveGuest(Cxt cxt)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "SELECT TOP 1 * FROM [User] WHERE HumanRankID = 7 AND StatusID = 3", "");
User user = null;
if (table.Rows.Count > 0)
{
user = new User(cxt, table.Rows[0]);
}
else
{
user = new User();
}
return(user);
}
public bool IsAdmin(int userID)
{
bool isTrue = false;
DataTable dt = BaseCollection.ExecuteSql(InfiChess.Role, "select role.roleID, role.Name, role.Description from [role] inner join UserRole on [role].roleid = userrole.roleid where [UserRole].userid = @p1", userID);
if (dt.Rows.Count > 0)
{
///Role role = new Role(Cxt.Instance, dt.Rows[0]);
if (Convert.ToInt32(dt.Rows[0]["RoleID"]) == (int)RoleE.Admin)
{
isTrue = true;
}
else
{
isTrue = false;
}
}
return(isTrue);
}
public static void SaveWantinUsers(Cxt cxt, DataTable items, DataTable dtTournamentWantin)
{
SqlTransaction sqltrans = null;
try
{
sqltrans = SqlHelper.BeginTransaction(Config.ConnectionString);
TournamentUsers.CreateRegisterUsers(cxt, sqltrans, items);
if (dtTournamentWantin != null)
{
foreach (DataRow dr in dtTournamentWantin.Rows)
{
TournamentWantinUser tournamentWantinUser = new TournamentWantinUser(cxt, dr);
//tournamentWantinUser.Save(sqltrans);
DateTime DateModified = DateTime.Now;
int ModifiedBy = 1;
int TournamentWantinUserID = tournamentWantinUser.TournamentWantinUserID;
int TournamentUserStatusID = tournamentWantinUser.TournamentUserStatusID;
BaseCollection.ExecuteSql(sqltrans, InfiChess.TournamentWantinUser,
"update TournamentWantinUser set TournamentUserStatusID = @p1, ModifiedBy = @p2, DateModified = @p3 WHERE TournamentWantinUserID = @p4",
TournamentUserStatusID, ModifiedBy, DateModified, TournamentWantinUserID);
}
}
SqlHelper.CommitTransaction(sqltrans);
}
catch (Exception ex)
{
SqlHelper.RollbackTransaction(sqltrans);
Log.Write(cxt, ex);
}
}
public static DataTable GetAllBanUserMachine()
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "SELECT u.*,UserRole.RoleID as RoleID, c.Name AS Country, Rank.Name AS Rank FROM [User] AS u LEFT OUTER JOIN Rank ON u.HumanRankID = Rank.RankID LEFT OUTER JOIN Country AS c ON u.CountryID = c.CountryID LEFT OUTER JOIN UserRole ON u.UserID = UserRole.UserRoleID where u.BanMachineKey is not null ");
return(table);
}
public static DataTable GetRecentTournamentTeam(Cxt cxt, int tournamentID)
{
return(BaseCollection.ExecuteSql("select t.TeamID, t.TeamName, t.StatusID from TournamentTeam tt right join team t on tt.Teamid = t.TeamID where t.TeamID not in (select teamid from TournamentTeam where TournamentID = " + tournamentID + " and statusid <> 4) and t.statusid <> 4 and t.StatusID <> 3 group by t.TeamID, t.TeamName, t.StatusID"));
}
public static DataTable GetAllUser()
{
DataTable table = BaseCollection.ExecuteSql("SELECT UserID, UserName FROM [User] WHERE UserStatusID IN (1,3,5,6) AND StatusID = 1");
return(table);
}
public static DataTable GetTournamentWantinUsers()
{
return(BaseCollection.ExecuteSql("GetTournamentWantinUsers"));
}
public static DataTable UpdateStatus(SqlTransaction t, StatusE statusID, string tournamentUserID)
{
return(BaseCollection.ExecuteSql(t, InfiChess.TournamentWantinUser, "update TournamentWantinUser set TournamentWantinUserStatusID = @p1 WHERE TournamentUserID in (@p2)", statusID.ToString("d"), tournamentUserID));
}
public static DataTable GetUserFormulas(int userID)
{
return(BaseCollection.ExecuteSql(InfiChess.UserFormula, "SELECT * FROM [UserFormula] WHERE UserID = " + userID));
}
public static DataTable GetAllAdmin()
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "SELECT u.*,UserRole.RoleID as RoleID, c.Name AS Country, Rank.Name AS Rank FROM [User] AS u LEFT OUTER JOIN Rank ON u.HumanRankID = Rank.RankID LEFT OUTER JOIN Country AS c ON u.CountryID = c.CountryID LEFT OUTER JOIN UserRole ON u.UserID = UserRole.UserRoleID where u.userid in (select userid from userrole where roleid =1)");
return(table);
}
public static DataTable GetAllUserByID(Cxt cxt, StatusE statusID)
{
DataTable table = BaseCollection.ExecuteSql(InfiChess.User, "SELECT u.*,UserRole.RoleID as RoleID, c.Name AS Country, Rank.Name AS Rank FROM [User] AS u LEFT OUTER JOIN Rank ON u.HumanRankID = Rank.RankID LEFT OUTER JOIN Country AS c ON u.CountryID = c.CountryID LEFT OUTER JOIN UserRole ON u.UserID = UserRole.UserRoleID where u.StatusID <>" + statusID.ToString("d"));
return(table);
}
public static DataTable GetUsers(int userID1, int userID2)
{
DataTable table = BaseCollection.ExecuteSql("select UserID,UserName from [User] where UserID IN ('" + userID1 + "','" + userID2 + "')");
return(table);
}
public static DataTable GetUserIdsByRoomID(Cxt cxt, int roomId)
{
DataTable table = BaseCollection.ExecuteSql("SELECT UserID, UserName FROM [User] where RoomID = " + roomId + " AND UserStatusID IN (1,3,5,6) AND StatusID = 1");
return(table);
}