public Tables(Database db)
{
Blocks = new Blocks(db);
Config = new Config(db);
ElimsCommitHistory = new ElimsCommitHistory(db);
ElimsData = new ElimsData(db);
ElimsGameSpecific = new ElimsGameSpecific(db);
ElimsGameSpecificHistory = new ElimsGameSpecificHistory(db);
ElimsResults = new ElimsResults(db);
ElimsScores = new ElimsScores(db);
ElimsScoresHistory = new ElimsScoresHistory(db);
LeagueConfig = new LeagueConfig(db);
LeagueHistory = new LeagueHistory(db);
LeagueInfo = new LeagueInfo(db);
LeagueMeets = new LeagueMeets(db);
Match = new Match(db);
MatchSchedule = new MatchSchedule(db);
Quals = new Quals(db);
QualsCommitHistory = new QualsCommitHistory(db);
QualsData = new QualsData(db);
QualsGameSpecific = new QualsGameSpecific(db);
QualsGameSpecificHistory = new QualsGameSpecificHistory(db);
QualsResults = new QualsResults(db);
QualsScores = new QualsScores(db);
QualsScoresHistory = new QualsScoresHistory(db);
ScheduleDetail = new ScheduleDetail(db);
ScheduleStation = new ScheduleStation(db);
Team = new Team(db);
TeamRanking = new TeamRanking(db);
}
internal static List <Ranking> UpdateRankings(Game game, LeagueConfig config)
{
List <Ranking> rkList1 = GetUserRankingHistory(game.User1Id, game.Server.Id);
List <Ranking> rkList2 = GetUserRankingHistory(game.User2Id, game.Server.Id);
List <Ranking> rkList3 = GetUserRankingHistory(game.User3Id, game.Server.Id);
List <Ranking> rkList4 = GetUserRankingHistory(game.User4Id, game.Server.Id);
if (!rkList1.Any() || !rkList2.Any() || !rkList3.Any() || !rkList4.Any())
{
throw new Exception("Missing one of the player's rank to compute new rank");
}
List <Ranking> newRkList = new List <Ranking>
{
new Ranking(game.User1Id, rkList1, rkList2.Last(), rkList3.Last(), rkList4.Last(), 1, game.Id, game.Server.Id, config),
new Ranking(game.User2Id, rkList2, rkList1.Last(), rkList3.Last(), rkList4.Last(), 2, game.Id, game.Server.Id, config),
new Ranking(game.User3Id, rkList3, rkList2.Last(), rkList1.Last(), rkList4.Last(), 3, game.Id, game.Server.Id, config),
new Ranking(game.User4Id, rkList4, rkList2.Last(), rkList3.Last(), rkList1.Last(), 4, game.Id, game.Server.Id, config)
};
foreach (var ranking in newRkList)
{
AddRanking(ranking);
}
return(newRkList);
}
public static LeagueConfig GetLeagueConfig(int leagueConfigId)
{
var dbCon = DBConnection.Instance();
if (dbCon.IsConnect())
{
using var command = SqlClientFactory.Instance.CreateCommand();
command.Connection = dbCon.Connection;
command.CommandText = $"SELECT {idCol}, {countPointsCol}, {startingPointsCol}, " //0-2
+ $"{uma3p1Col}, {uma3p2Col}, {uma3p3Col}, {uma4p1Col}, {uma4p2Col}, {uma4p3Col}, {uma4p4Col}, " // 3-9
+ $"{okaCol}, {penaltyLastCol}, {useEloSystemCol}, {initialEloCol}, {minEloCol}, {baseEloChangeDampeningCol}, " //10-15
+ $"{eloChangeStartRatioCol}, {eloChangeEndRatioCol}, {trialPeriodDurationCol}, {allowSanmaCol} " //16-19
+ $"FROM {tableName} "
+ $"WHERE {idCol} = @leagueId";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@leagueId", SqlDbType.VarChar)
{
Value = leagueConfigId
});
var reader = command.ExecuteReader();
while (reader.Read())
{
var id = reader.GetInt32(0);
var countPoints = reader.GetBoolean(1);
var useEloSystem = reader.GetBoolean(12);
var league = new LeagueConfig(id, countPoints, useEloSystem);;
league.StartingPoints = (float)reader.GetDouble(2);
league.Uma3p1 = reader.IsDBNull(3) ? -9999 : (float)reader.GetDouble(3);
league.Uma3p2 = reader.IsDBNull(4) ? -9999 : (float)reader.GetDouble(4);
league.Uma3p3 = reader.IsDBNull(5) ? -9999 : (float)reader.GetDouble(5);
league.Uma4p1 = reader.IsDBNull(6) ? -9999 : (float)reader.GetDouble(6);
league.Uma4p2 = reader.IsDBNull(7) ? -9999 : (float)reader.GetDouble(7);
league.Uma4p3 = reader.IsDBNull(8) ? -9999 : (float)reader.GetDouble(8);
league.Uma4p4 = reader.IsDBNull(9) ? -9999 : (float)reader.GetDouble(9);
league.Oka = reader.IsDBNull(10) ? -9999 : (float)reader.GetDouble(10);
league.PenaltyLast = reader.IsDBNull(11) ? -9999 : (float)reader.GetDouble(11);
league.InitialElo = reader.IsDBNull(13) ? -9999 : (float)reader.GetDouble(13);
league.MinElo = reader.IsDBNull(14) ? -9999 : (float)reader.GetDouble(14);
league.BaseEloChangeDampening = reader.IsDBNull(15) ? -9999 : (float)reader.GetDouble(15);
league.EloChangeStartRatio = reader.IsDBNull(16) ? -9999 : (float)reader.GetDouble(16);
league.EloChangeEndRatio = reader.IsDBNull(17) ? -9999 : (float)reader.GetDouble(17);
league.TrialPeriodDuration = reader.IsDBNull(18) ? -9999 : reader.GetInt32(18);
league.AllowSanma = reader.GetBoolean(19);
reader.Close();
return(league);
}
reader.Close();
throw new Exception($"Couldn't find league with id {leagueConfigId}");
}
throw (new DbConnectionException());
}
public static void CreateUser(string userDiscordId, string serverId, LeagueConfig leagueConfig)
{
var dbCon = DBConnection.Instance();
if (dbCon.IsConnect())
{
using var command = SqlClientFactory.Instance.CreateCommand();
command.Connection = dbCon.Connection;
command.CommandText = $"INSERT INTO {tableName} ({idCol}) " +
$"VALUES (@discordId);";
command.Parameters.Add(new SqlParameter("@discordId", SqlDbType.VarChar)
{
Value = userDiscordId
});
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
RankingDbService.InitUserRanking(userDiscordId, serverId, leagueConfig);
return;
}
throw (new DbConnectionException());
}
internal static void InitUserRanking(string userId, string serverId, LeagueConfig leagueConfig)
{
List <Ranking> userRankings = GetUserRankingHistory(userId, serverId, latest: true);
if (userRankings.Any())
{
throw new Exception("User is already ranked on that server");
}
var dbCon = DBConnection.Instance();
if (dbCon.IsConnect())
{
using var command = SqlClientFactory.Instance.CreateCommand();
command.Connection = dbCon.Connection;
command.CommandText = $"INSERT INTO {tableName} ({userIdCol}, {serverIdCol}, {newEloCol}) " +
$"VALUES (@userId, @serverId, @rank);";
command.Parameters.Add(new SqlParameter("@userId", SqlDbType.VarChar)
{
Value = userId
});
command.Parameters.Add(new SqlParameter("@serverId", SqlDbType.VarChar)
{
Value = serverId
});
command.Parameters.Add(new SqlParameter("@rank", SqlDbType.Float)
{
Value = leagueConfig.InitialElo
});
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
return;
}
throw (new DbConnectionException());
}
public static (Game, List <Ranking>) RecordIRLGame(string[] members, float[] scores, Server server, LeagueConfig leagueConfig)
{
var dbCon = DBConnection.Instance();
string logId = GetIRLLogId();
if (dbCon.IsConnect())
{
using var command = SqlClientFactory.Instance.CreateCommand();
command.Connection = dbCon.Connection;
var scoreColumns = scores == null
? ""
: $"{User1ScoreCol}, {User2ScoreCol}, {User3ScoreCol}{(scores.Length > 3 ? $", {User3ScoreCol}" : "")}";
var scoreValues = scores == null
? ""
: $", @score1, @score2, @score3{(scores.Length > 3 ? $", @score4" : "")}";
command.CommandText = $"INSERT INTO {GameTableName} ({User1IdCol}, {User2IdCol}, {User3IdCol}, {User4IdCol}, {IdCol}, {PlatformCol}, {ServerIdCol}{scoreColumns}) " +
$"VALUES ({members[0]}, {members[1]}, {members[2]}, {members[3]}, @logId, @gameType, @serverId{scoreValues});";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@logId", SqlDbType.NVarChar)
{
Value = logId
});
command.Parameters.Add(new SqlParameter("@gameType", SqlDbType.NVarChar)
{
Value = GameType.IRL
});
command.Parameters.Add(new SqlParameter("@serverId", SqlDbType.NVarChar)
{
Value = server.Id
});
if (scores != null)
{
for (int i = 0; i < scores.Length && i < 4; i++)
{
command.Parameters.Add(new SqlParameter($"@score{i + 1}", SqlDbType.Float)
{
Value = scores[i]
});
}
}
command.ExecuteNonQuery();
var game = GetGameFromLogId(logId, server);
var rankings = RankingDbService.UpdateRankings(game, leagueConfig);
return(game, rankings);
}
throw (new DbConnectionException());
}
