/// <summary>
/// Calcule et met à jour en base de données les points ELO pour un joueur pour une semaine spécifiée.
/// </summary>
/// <param name="player">Le joueur.</param>
/// <param name="weekEditions">Les éditions de tournois pour la semaine spécifiée.</param>
/// <param name="year">L'année.</param>
/// <param name="week">Le numéro de semaine.</param>
public void ComputeEloAtDate(Player player, List <Edition> weekEditions, int year, uint week)
{
bool previousYearIs53 = Tools.YearIs53Week(year - 1);
// ELO de la semaine précédente
ushort currentElo =
SqlTools.ExecuteScalar("SELECT elo FROM atp_ranking WHERE player_ID = @pid AND (year < @year OR (year = @year AND week_no < @week)) ORDER BY year DESC, week_no DESC LIMIT 0, 1", Tools.DEFAULT_ELO,
new SqlParam("@pid", DbType.UInt64, player.ID),
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week));
if (weekEditions.Count > 0)
{
// Récupération des matchs du joueur pour les éditions de la semaine (les forfaits d'avant-match ne sont pas pris en compte)
// Note : le ELO des adversaires est celui de la semaine précédente, pas celui "live" au cours de l'édition
System.Text.StringBuilder sbQuery = new System.Text.StringBuilder();
sbQuery.AppendLine("SELECT (");
sbQuery.AppendLine(" SELECT level_ID FROM editions AS e WHERE e.ID = edition_ID");
sbQuery.AppendLine(") AS level_ID, (");
sbQuery.AppendLine(" SELECT elo FROM atp_ranking");
sbQuery.AppendLine(" WHERE player_ID = IF(winner_ID = @pid, loser_ID, winner_ID)");
sbQuery.AppendLine(" AND (year < @year OR (week_no < @week AND year = @year))");
sbQuery.AppendLine(" ORDER BY year DESC, week_no DESC LIMIT 0, 1");
sbQuery.AppendLine(") AS opponent_ELO, IF(winner_ID = @pid, 1, 0) AS is_winner FROM matches");
sbQuery.AppendLine("WHERE walkover = 0 AND (loser_ID = @pid OR winner_ID = @pid) AND edition_ID IN ({0})");
sbQuery.AppendLine("ORDER BY (SELECT date_begin FROM editions AS e where e.ID = edition_ID) ASC, IF(round_ID = 9, 1, round_ID) DESC");
using (DataTableReader reader = SqlTools.ExecuteReader(
string.Format(sbQuery.ToString(), string.Join(", ", weekEditions.Select(_ => _.ID).ToList())),
new SqlParam("@pid", DbType.UInt64, player.ID),
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week)))
{
while (reader.Read())
{
Tuple <double, double> elo = Tools.ComputeElo(
currentElo,
reader.GetUint16Null("opponent_ELO") ?? Tools.DEFAULT_ELO,
reader.GetBoolean("is_winner"),
Tools.GetLevelEloCoeffK((Level)reader.GetByte("level_ID")));
currentElo = Convert.ToUInt16(Math.Floor(elo.Item1));
}
}
}
SqlTools.ExecuteNonQuery("UPDATE atp_ranking SET elo = @elo WHERE player_ID = @pid AND year = @year AND week_no = @week",
new SqlParam("@pid", DbType.UInt64, player.ID),
new SqlParam("@year", DbType.UInt32, week == 1 ? (year - 1) : year),
new SqlParam("@week", DbType.UInt32, week == 1 ? (previousYearIs53 ? (uint)53 : 52) : week),
new SqlParam("@elo", DbType.UInt16, currentElo));
}
/// <summary>
/// Importe des matchs depuis la base de données selon des paramètres optionnels.
/// </summary>
/// <param name="editionId">Identifiant d'édition de tournoi.</param>
/// <param name="playerId">Identifiant de joueur.</param>
/// <returns>Les matchs importés.</returns>
public IEnumerable <Match> LoadMatches(uint?editionId, ulong?playerId)
{
List <Match> matchs = new List <Match>();
Match.SetBatchMode(true);
string query = "select * from matches where 1 = 1";
List <SqlParam> sqlParams = new List <SqlParam>();
if (editionId.HasValue)
{
query += " and edition_ID = @edition";
sqlParams.Add(new SqlParam("@edition", DbType.UInt32, editionId.Value));
}
if (playerId.HasValue)
{
query += " and (winner_ID = @player or loser_ID = @player)";
sqlParams.Add(new SqlParam("@player", DbType.UInt32, playerId.Value));
}
using (DataTableReader reader = SqlTools.ExecuteReader(query, sqlParams.ToArray()))
{
while (reader.Read())
{
Match match = new Match(reader.GetUint64("ID"),
reader.GetUint32("edition_ID"),
reader.GetUint16("match_num"),
(Round)reader.GetByte("round_ID"),
reader.GetByte("best_of"),
reader.GetUint32Null("minutes"),
reader.GetBoolean("unfinished"),
reader.GetBoolean("retirement"),
reader.GetBoolean("walkover"),
reader.GetUint32("winner_ID"),
reader.GetUint32Null("winner_seed"),
reader.GetString("winner_entry"),
reader.GetUint32Null("winner_rank"),
reader.GetUint32Null("winner_rank_points"),
reader.GetUint32("loser_ID"),
reader.GetUint32Null("loser_seed"),
reader.GetString("loser_entry"),
reader.GetUint32Null("loser_rank"),
reader.GetUint32Null("loser_rank_points"));
match.DefineStatistics(reader.ToDynamicDictionnary <uint?>(true), reader.ToDynamicDictionnary <uint?>(true));
for (byte i = 1; i <= 5; i++)
{
match.AddSetByNumber(i, reader.GetByteNull("w_set_" + i.ToString()), reader.GetByteNull("l_set_" + i.ToString()), reader.GetUint16Null("tb_set_" + i.ToString()));
}
matchs.Add(match);
if (Config.GetBool(AppKey.ComputeMatchesWhileLoading))
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
Match.SetBatchMode(false);
return(matchs);
}