/// <summary>
/// Crée les éditions des tournois disputés dans une année.
/// L'intégration manuelle des nouveaux tournois (ou ceux mis à jour) doit être réalisée au préalable.
/// Les données relatives à la coupe Davis sont ignorées.
/// </summary>
public void IntegrateEditionOfTournaments()
{
string insertEditionQuery = SqlTools.BuildInsertQuery("editions", new Dictionary <string, string>()
{
{ "tournament_ID", "@id" },
{ "year", "@year" },
{ "draw_size", "@drawsize" },
{ "date_begin", "@bdate" },
{ "date_end", "@edate" },
{ "surface_ID", "@surface" },
{ "slot_order", "@slot" },
{ "is_indoor", "@indoor" },
{ "level_ID", "@level" },
{ "substitute_ID", "@substitute" },
{ "name", "@name" },
{ "city", "@city" }
});
List <string> uniqueTournamentList = new List <string>();
foreach (Dictionary <string, string> match in _matchs)
{
if (!uniqueTournamentList.Contains(match["tourney_id"]))
{
uniqueTournamentList.Add(match["tourney_id"]);
string baseCode = match["tourney_id"].Substring(5);
using (DataTableReader reader = SqlTools.ExecuteReader("select * from tournaments where original_code in (@code2, @code1)",
new SqlParam("@code1", DbType.String, baseCode), new SqlParam("@code2", DbType.String, GetGenericTournamentCode(baseCode))))
{
if (reader.Read())
{
DateTime dateBegin = Tools.FormatCsvDateTime(match["tourney_date"]);
// Pas le vrai type SQL, mais san importance
int drawSize = Convert.ToInt32(match["draw_size"]);
// TODO : système de préparation de la requête SQL
SqlTools.ExecuteNonQuery(insertEditionQuery,
new SqlParam("@id", DbType.UInt32, reader.GetUint64("ID")),
new SqlParam("@year", DbType.UInt32, _year),
new SqlParam("@drawsize", DbType.UInt16, drawSize),
new SqlParam("@bdate", DbType.DateTime, dateBegin.ToString("yyyy-MM-dd")),
new SqlParam("@edate", DbType.DateTime, ComputeEditionEndDate(dateBegin, drawSize).ToString("yyyy-MM-dd")),
new SqlParam("@surface", DbType.Byte, reader.GetByte("surface_ID")),
new SqlParam("@slot", DbType.Byte, reader.GetByteNull("slot_order")),
new SqlParam("@indoor", DbType.Boolean, reader.GetByte("is_indoor") == 1),
new SqlParam("@level", DbType.Byte, reader.GetByte("level_ID")),
new SqlParam("@substitute", DbType.UInt32, reader.GetUint64Null("substitute_ID")),
new SqlParam("@name", DbType.String, reader.GetString("name")),
new SqlParam("@city", DbType.String, reader.GetString("city")));
}
else
{
Tools.WriteLog(string.Format("Le tournoi {0} a été ignoré. C'est une erreur s'il ne s'agit pas d'un match de coupe Davis.", match["tourney_id"]));
}
}
}
}
}
// Procède à l'importation des éditions de tournoi.
private void LoadEditions()
{
string query = "select *, (select count(*) from matches where edition_ID = editions.ID) as MatchCount from editions order by tournament_ID, year";
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
Edition edition = new Edition(reader.GetUint32("ID"),
reader.GetUint32("tournament_ID"),
reader.GetUint32("year"),
reader.GetUint16("draw_size"),
reader.GetDateTime("date_begin"),
reader.GetUint32("MatchCount") >= Edition.TWO_WEEKS_MIN_MATCH_COUNT,
reader.GetDateTime("date_end"),
reader.GetBoolean("is_indoor"),
(Level)reader.GetByte("level_ID"),
reader.GetString("name"),
reader.GetString("city"),
reader["slot_order"] == DBNull.Value ? (byte)0 : reader.GetByte("slot_order"),
(Surface)reader.GetByte("surface_ID"));
if (Properties.Settings.Default.ComputeStatisticsWhileLoading)
{
LoadEditionsStatistics(edition);
}
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
/// <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));
}
// procède à l'importation des classements ATP (et ELO)
private void LoadAtpRanking()
{
string query = "select * from atp_ranking";
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
new AtpRanking(reader.GetUint64("player_ID"), reader.GetUint32("year"), reader.GetUint32("week_no"),
reader.GetUint32("week_points"), reader.GetUint32("year_calendar_points"), reader.GetUint32("year_rolling_points"),
reader.GetUint16("year_calendar_ranking"), reader.GetUint16("year_rolling_ranking"), reader.GetUint16("elo"));
}
}
}
// procède à l'importation des pays.
private void LoadCountries()
{
string sqlQuery = "select * from countries";
using (DataTableReader reader = SqlTools.ExecuteReader(sqlQuery))
{
while (reader.Read())
{
new Country(reader.GetString("code_ISO2"), reader.GetString("code_ISO3"), reader.GetString("name_EN"), reader.GetString("name_FR"));
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
// Procède à l'importation des joueurs.
private void LoadPlayers()
{
string query = "select * from players";
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
// Calcul de la latéralité.
string hand = reader.GetString("hand");
bool? isLeftHanded = null;
if (!string.IsNullOrWhiteSpace(hand))
{
isLeftHanded = hand.Trim().ToUpper() == "L";
}
new Player(reader.GetUint64("ID"),
reader.GetString("name"),
reader.GetString("nationality"),
isLeftHanded,
reader.GetUint32Null("height"),
reader.GetDateTimeNull("date_of_birth"),
reader.GetDateTimeNull("date_begin"),
reader.GetDateTimeNull("date_end"));
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
// Importation de l'historique des nationalités.
query = "select * from players_nat_history order by date_end";
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
Player.AddNationalitiesHistoryEntry(reader.GetUint64("ID"), reader.GetString("nationality"), reader.GetDateTime("date_end"));
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
// Procède à l'importation du barème des points ATP.
private void LoadPointsAtpScale()
{
foreach (object level in Enum.GetValues(typeof(Level)))
{
string query = string.Format("select * from points where level_ID = {0}", (int)level);
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
new PointsAtpScale((Level)level,
(Round)reader.GetByte("round_ID"),
reader.GetUint32("points_w"),
reader.GetUint32("points_l"),
reader.GetUint32("points_l_ex"),
reader.GetBoolean("is_cumuled"));
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
}
// Procède à l'importation des tournois
private void LoadTournaments()
{
// Importation des tournois.
string query = "select * from tournaments";
using (DataTableReader reader = SqlTools.ExecuteReader(query))
{
while (reader.Read())
{
new Tournament(reader.GetUint32("ID"),
reader.GetString("name"),
reader.GetString("city"),
(Level)reader.GetByte("level_ID"),
(Surface)reader.GetByte("surface_ID"),
reader.GetBoolean("is_indoor"),
reader["slot_order"] == DBNull.Value ? (byte)0 : reader.GetByte("slot_order"),
reader["last_year"] == DBNull.Value ? 0 : reader.GetUint32("last_year"),
reader["substitute_ID"] == DBNull.Value ? 0 : reader.GetUint32("substitute_ID"));
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
/// <summary>
/// Charge les statistiques associées à une édition de tournoi.
/// </summary>
/// <param name="edition">Edition de tournoi.</param>
/// <exception cref="ArgumentNullException">L'argument <paramref name="edition"/> est <c>Null</c>.</exception>
public void LoadEditionsStatistics(Edition edition)
{
if (edition == null)
{
throw new ArgumentNullException(nameof(edition));
}
if (edition.StatisticsAreCompute)
{
return;
}
string query = "select * from edition_player_stats where edition_ID = @edition";
using (DataTableReader subReader = SqlTools.ExecuteReader(query, new SqlParam("@edition", DbType.UInt32, edition.ID)))
{
while (subReader.Read())
{
ulong playerId = subReader.GetUint64("player_ID");
for (int i = 0; i < subReader.FieldCount; i++)
{
string columnName = subReader.GetName(i);
if (columnName == "edition_ID" || columnName == "player_ID")
{
continue;
}
edition.AddPlayerStatistics(playerId, Tools.GetEnumValueFromSqlMapping <StatType>(columnName), Convert.ToUInt32(subReader[columnName]));
}
if (Properties.Settings.Default.ComputeStatisticsWhileLoading)
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
}
/// <summary>
/// Crée les matchs de l'année du fichier.
/// Les valeurs des colonnes 'winner_entry' et 'loser_entry' doivent être surveillées à posteriori (voir <see cref="Services.Entry"/> pour les valeurs autorisées).
/// </summary>
public void IntegrateMatchs()
{
Dictionary <string, byte> rounds = new Dictionary <string, byte>();
using (DataTableReader reader = SqlTools.ExecuteReader("select * from rounds"))
{
while (reader.Read())
{
rounds.Add(SqlTools.GetString(reader, "original_code"), SqlTools.GetByte(reader, "ID"));
}
}
#region Préparation de la requête (très longue liste)
string insertMatchQuery = SqlTools.BuildInsertQuery("matches", new Dictionary <string, string>
{
{ "original_key", "@original_key" },
{ "edition_ID", "@edition_ID" },
{ "match_num", "@match_num" },
{ "round_ID", "@round_ID" },
{ "best_of", "@best_of" },
{ "winner_ID", "@winner_ID" },
{ "winner_seed", "@winner_seed" },
{ "winner_entry", "@winner_entry" },
{ "winner_rank", "@winner_rank" },
{ "winner_rank_points", "@winner_rank_points" },
{ "loser_ID", "@loser_ID" },
{ "loser_seed", "@loser_seed" },
{ "loser_entry", "@loser_entry" },
{ "loser_rank", "@loser_rank" },
{ "loser_rank_points", "@loser_rank_points" },
{ "minutes", "@minutes" },
{ "unfinished", "@unfinished" },
{ "retirement", "@retirement" },
{ "walkover", "@walkover" },
{ "w_ace", "@w_ace" },
{ "w_df", "@w_df" },
{ "w_svpt", "@w_svpt" },
{ "w_1stIn", "@w_1stIn" },
{ "w_1stWon", "@w_1stWon" },
{ "w_2ndWon", "@w_2ndWon" },
{ "w_SvGms", "@w_SvGms" },
{ "w_bpSaved", "@w_bpSaved" },
{ "w_bpFaced", "@w_bpFaced" },
{ "l_ace", "@l_ace" },
{ "l_df", "@l_df" },
{ "l_svpt", "@l_svpt" },
{ "l_1stIn", "@l_1stIn" },
{ "l_1stWon", "@l_1stWon" },
{ "l_2ndWon", "@l_2ndWon" },
{ "l_SvGms", "@l_SvGms" },
{ "l_bpSaved", "@l_bpSaved" },
{ "l_bpFaced", "@l_bpFaced" },
{ "w_set_1", "@w_set_1" },
{ "w_set_2", "@w_set_2" },
{ "w_set_3", "@w_set_3" },
{ "w_set_4", "@w_set_4" },
{ "w_set_5", "@w_set_5" },
{ "l_set_1", "@l_set_1" },
{ "l_set_2", "@l_set_2" },
{ "l_set_3", "@l_set_3" },
{ "l_set_4", "@l_set_4" },
{ "l_set_5", "@l_set_5" },
{ "tb_set_1", "@tb_set_1" },
{ "tb_set_2", "@tb_set_2" },
{ "tb_set_3", "@tb_set_3" },
{ "tb_set_4", "@tb_set_4" },
{ "tb_set_5", "@tb_set_5" }
});
#endregion
Dictionary <string, uint> editionsList = new Dictionary <string, uint>();
foreach (Dictionary <string, string> match in _matchs)
{
string baseCode = match["tourney_id"].Substring(5);
uint editionId = 0;
if (!editionsList.ContainsKey(baseCode))
{
string genericTournamentCode = GetGenericTournamentCode(baseCode);
editionId = SqlTools.ExecuteScalar <uint>("select e.ID from editions as e " +
"join tournaments as t on e.tournament_ID = t.ID " +
"where t.original_code in (@code2, @code1) and e.year = @year",
0,
new SqlParam("@code1", DbType.String, baseCode),
new SqlParam("@code2", DbType.String, genericTournamentCode),
new SqlParam("@year", DbType.UInt32, _year));
if (editionId > 0)
{
editionsList.Add(baseCode, editionId);
}
else
{
Tools.WriteLog(string.Format("Impossible de récupérer l'édition du tournoi {0}.", baseCode));
continue;
}
}
else
{
editionId = editionsList[baseCode];
}
try
{
SqlTools.ExecuteNonQuery(insertMatchQuery,
new SqlParam("@original_key", DbType.String, string.Concat(match["tourney_id"], "-", match["match_num"])),
new SqlParam("@edition_ID", DbType.UInt32, editionId),
new SqlParam("@match_num", DbType.UInt16, match["match_num"]),
new SqlParam("@round_ID", DbType.Byte, rounds[match["round"]]),
new SqlParam("@best_of", DbType.Byte, match["best_of"]),
new SqlParam("@winner_ID", DbType.UInt64, match["winner_id"]),
new SqlParam("@winner_seed", DbType.UInt32, string.IsNullOrWhiteSpace(match["winner_seed"]) ? null : match["winner_seed"]),
new SqlParam("@winner_entry", DbType.String, string.IsNullOrWhiteSpace(match["winner_entry"]) ? null : match["winner_entry"]),
new SqlParam("@winner_rank", DbType.UInt32, string.IsNullOrWhiteSpace(match["winner_rank"]) ? null : match["winner_rank"]),
new SqlParam("@winner_rank_points", DbType.UInt32, string.IsNullOrWhiteSpace(match["winner_rank_points"]) ? null : match["winner_rank_points"]),
new SqlParam("@loser_ID", DbType.UInt64, match["loser_id"]),
new SqlParam("@loser_seed", DbType.UInt32, string.IsNullOrWhiteSpace(match["loser_seed"]) ? null : match["loser_seed"]),
new SqlParam("@loser_entry", DbType.String, string.IsNullOrWhiteSpace(match["loser_entry"]) ? null : match["loser_entry"]),
new SqlParam("@loser_rank", DbType.UInt32, string.IsNullOrWhiteSpace(match["loser_rank"]) ? null : match["loser_rank"]),
new SqlParam("@loser_rank_points", DbType.UInt32, string.IsNullOrWhiteSpace(match["loser_rank_points"]) ? null : match["loser_rank_points"]),
new SqlParam("@minutes", DbType.UInt32, string.IsNullOrWhiteSpace(match["minutes"]) ? null : match["minutes"]),
new SqlParam("@unfinished", DbType.Boolean, false),
new SqlParam("@retirement", DbType.Boolean, false),
new SqlParam("@walkover", DbType.Boolean, false),
new SqlParam("@w_ace", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_ace"]) ? null : match["w_ace"]),
new SqlParam("@w_df", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_df"]) ? null : match["w_df"]),
new SqlParam("@w_svpt", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_svpt"]) ? null : match["w_svpt"]),
new SqlParam("@w_1stIn", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_1stIn"]) ? null : match["w_1stIn"]),
new SqlParam("@w_1stWon", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_1stWon"]) ? null : match["w_1stWon"]),
new SqlParam("@w_2ndWon", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_2ndWon"]) ? null : match["w_2ndWon"]),
new SqlParam("@w_SvGms", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_SvGms"]) ? null : match["w_SvGms"]),
new SqlParam("@w_bpSaved", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_bpSaved"]) ? null : match["w_bpSaved"]),
new SqlParam("@w_bpFaced", DbType.UInt32, string.IsNullOrWhiteSpace(match["w_bpFaced"]) ? null : match["w_bpFaced"]),
new SqlParam("@l_ace", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_ace"]) ? null : match["l_ace"]),
new SqlParam("@l_df", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_df"]) ? null : match["l_df"]),
new SqlParam("@l_svpt", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_svpt"]) ? null : match["l_svpt"]),
new SqlParam("@l_1stIn", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_1stIn"]) ? null : match["l_1stIn"]),
new SqlParam("@l_1stWon", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_1stWon"]) ? null : match["l_1stWon"]),
new SqlParam("@l_2ndWon", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_2ndWon"]) ? null : match["l_2ndWon"]),
new SqlParam("@l_SvGms", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_SvGms"]) ? null : match["l_SvGms"]),
new SqlParam("@l_bpSaved", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_bpSaved"]) ? null : match["l_bpSaved"]),
new SqlParam("@l_bpFaced", DbType.UInt32, string.IsNullOrWhiteSpace(match["l_bpFaced"]) ? null : match["l_bpFaced"]),
new SqlParam("@w_set_1", DbType.Byte, ExtractScore(match["score"], 1, true)),
new SqlParam("@w_set_2", DbType.Byte, ExtractScore(match["score"], 2, true)),
new SqlParam("@w_set_3", DbType.Byte, ExtractScore(match["score"], 3, true)),
new SqlParam("@w_set_4", DbType.Byte, ExtractScore(match["score"], 4, true)),
new SqlParam("@w_set_5", DbType.Byte, ExtractScore(match["score"], 5, true)),
new SqlParam("@l_set_1", DbType.Byte, ExtractScore(match["score"], 1, false)),
new SqlParam("@l_set_2", DbType.Byte, ExtractScore(match["score"], 2, false)),
new SqlParam("@l_set_3", DbType.Byte, ExtractScore(match["score"], 3, false)),
new SqlParam("@l_set_4", DbType.Byte, ExtractScore(match["score"], 4, false)),
new SqlParam("@l_set_5", DbType.Byte, ExtractScore(match["score"], 5, false)),
new SqlParam("@tb_set_1", DbType.UInt16, ExtractScore(match["score"], 1, null)),
new SqlParam("@tb_set_2", DbType.UInt16, ExtractScore(match["score"], 2, null)),
new SqlParam("@tb_set_3", DbType.UInt16, ExtractScore(match["score"], 3, null)),
new SqlParam("@tb_set_4", DbType.UInt16, ExtractScore(match["score"], 4, null)),
new SqlParam("@tb_set_5", DbType.UInt16, ExtractScore(match["score"], 5, null))
);
}
catch (Exception ex)
{
string errorMessage = string.Format("Echec de l'insertion du match {0}, l'erreur suivante est survenue : {1}", string.Concat(match["tourney_id"], "-", match["match_num"]), ex.Message);
Tools.WriteLog(errorMessage);
System.Windows.MessageBoxResult dialogResult =
System.Windows.MessageBox.Show(errorMessage + "\n\nArrêter l'importation ?",
"The Tennis Project - Error",
System.Windows.MessageBoxButton.YesNo);
if (dialogResult == System.Windows.MessageBoxResult.Yes)
{
break;
}
}
}
}
/// <summary>
/// Pour une année donnée, calcule les points à la semaine du classement ATP.
/// </summary>
/// <remarks>Un recalcul doit être fait si le barème change dans la table SQL "points".</remarks>
/// <param name="year">L'année à traiter.</param>
public void ComputeAtpRankingForYear(int year)
{
int weeksCount = Tools.YearIs53Week(year) ? 53 : 52;
bool previousYearIs53 = Tools.YearIs53Week(year - 1);
string query = "delete from atp_ranking where year = @year";
SqlTools.ExecuteNonQuery(query, new SqlParam("@year", DbType.UInt32, year));
List <Edition> editionsOfTheYear = Edition.GetByPeriod(new DateTime(year, 1, 1), new DateTime(year, 12, 31), null, null, false);
foreach (Edition edition in editionsOfTheYear.Where(_ => !_.StatisticsAreCompute))
{
LoadEditionsStatistics(edition);
}
List <Player> potentialPlayersOfTheYear =
Player.GetList.Where(_ =>
_.ID != Player.UNKNOWN_PLAYER_ID &&
_.DateBegin.HasValue &&
_.DateBegin.Value.Year <= year &&
_.DateEnd.HasValue &&
_.DateEnd.Value.Year + 1 >= year).ToList();
for (uint week = 1; week <= weeksCount; week++)
{
List <Edition> editionsOfTheWeek = editionsOfTheYear.Where(_ => Tools.GetWeekNoFromDate(_.DateEnd) == week).ToList();
foreach (Player player in potentialPlayersOfTheYear)
{
uint pointsOfTheWeek = 0;
uint pointsOfCalendarYear = 0;
uint pointsOfRollingYear = 0;
List <ulong> tournamentsIdSingle = new List <ulong>();
List <ulong> tournamentsIdCalendar = new List <ulong>();
List <ulong> tournamentsIdRolling = new List <ulong>();
// Récupère les points de la semaine en cours
bool?multipleEditionsInAWeek = null;
foreach (Edition edition in editionsOfTheWeek)
{
List <Edition.Stats> stats = edition.Statistics.Where(_ => _.Player.ID == player.ID).ToList();
if (stats.Any(_ => _.StatType == StatType.points))
{
pointsOfTheWeek += stats.First(_ => _.StatType == StatType.points).Value;
tournamentsIdSingle.Add(edition.Tournament.ID);
multipleEditionsInAWeek = multipleEditionsInAWeek.HasValue ? true : false;
}
}
// Pour info
if (multipleEditionsInAWeek.HasValue && multipleEditionsInAWeek.Value)
{
Tools.WriteLog(string.Format("Mutiple tournois joués par le joueur {0}/{1} dans la semaine {2} de l'année {3} ({4}).",
player.ID, player.Name, week, year, string.Join(",", tournamentsIdSingle)));
}
pointsOfCalendarYear += pointsOfTheWeek;
tournamentsIdCalendar.AddRange(tournamentsIdSingle);
// Charge les points de l'année en cours pour la semaine antérieure à celle courante
query = "select * from atp_ranking where year = @year and week_no = @week and player_ID = @player";
using (DataTableReader reader = SqlTools.ExecuteReader(query,
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week - 1),
new SqlParam("@player", DbType.UInt64, player.ID)))
{
if (reader.Read())
{
pointsOfCalendarYear += reader.GetUint32("year_calendar_points");
tournamentsIdCalendar.AddRange(reader.ToIdList("tournaments_calendar_concat"));
}
}
pointsOfRollingYear += pointsOfCalendarYear;
tournamentsIdRolling.AddRange(tournamentsIdCalendar);
// Charge les points de l'année dernière postérieure à la semaine courante
query = "select * from atp_ranking where year = @year and week_no > @week and player_ID = @player and tournaments_concat <> ''";
using (DataTableReader reader = SqlTools.ExecuteReader(query,
new SqlParam("@year", DbType.UInt32, year - 1),
new SqlParam("@week", DbType.UInt32, previousYearIs53 ? (week + 1) : week),
new SqlParam("@player", DbType.UInt64, player.ID)))
{
while (reader.Read())
{
List <ulong> tournamentsFromlastyear = reader.ToIdList("tournaments_concat").ToList();
uint pointsFromLastyear = reader.GetUint32("week_points");
if (tournamentsFromlastyear.Any(_ => tournamentsIdSingle.Contains(_)))
{
tournamentsFromlastyear.RemoveAll(_ => tournamentsIdSingle.Contains(_));
pointsFromLastyear = 0;
foreach (ulong tournamentIdLastYear in tournamentsFromlastyear)
{
Edition lastYearEdition = Edition.GetByYearAndTournament(tournamentIdLastYear, (uint)(year - 1));
if (lastYearEdition != null)
{
if (!lastYearEdition.StatisticsAreCompute)
{
LoadEditionsStatistics(lastYearEdition);
}
IEnumerable <Edition.Stats> lastyearEditionPointsStats =
lastYearEdition.Statistics.Where(_ => _.Player == player && _.StatType == StatType.points);
pointsFromLastyear += lastyearEditionPointsStats.FirstOrDefault().Value;
}
}
}
pointsOfRollingYear += pointsFromLastyear;
tournamentsIdRolling.AddRange(tournamentsFromlastyear);
}
}
// si le joueur n'a joué aucun tournoi sur les 12 derniers mois, il n'est pas classé
if (tournamentsIdRolling.Count == 0)
{
continue;
}
query = SqlTools.BuildInsertQuery("atp_ranking", new Dictionary <string, string>
{
{ "player_ID", "@player" },
{ "year", "@year" },
{ "week_no", "@week" },
{ "week_points", "@points" },
{ "year_calendar_points", "@calendar" },
{ "year_rolling_points", "@rolling" },
{ "tournaments_concat", "@t_single" },
{ "tournaments_calendar_concat", "@t_calendar" },
{ "tournaments_rolling_concat", "@t_rolling" }
});
SqlTools.ExecuteNonQuery(query,
new SqlParam("@player", DbType.UInt64, player.ID),
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week),
new SqlParam("@points", DbType.UInt32, pointsOfTheWeek),
new SqlParam("@calendar", DbType.UInt32, pointsOfCalendarYear),
new SqlParam("@rolling", DbType.UInt32, pointsOfRollingYear),
new SqlParam("@t_single", DbType.String, string.Join(";", tournamentsIdSingle)),
new SqlParam("@t_calendar", DbType.String, string.Join(";", tournamentsIdCalendar)),
new SqlParam("@t_rolling", DbType.String, string.Join(";", tournamentsIdRolling)));
ComputeEloAtDate(player, editionsOfTheWeek, year, week);
}
// calcule les classements (civil et glissant)
// TODO : mieux à faire pour la rêgle en cas d'égalité
string[] types = new[] { "calendar", "rolling" };
foreach (string t in types)
{
query = "select player_ID from atp_ranking where week_no = @week and year = @year order by year_" + t + "_points desc, length(tournaments_" + t + "_concat) - length(replace(tournaments_" + t + "_concat, ';', '')) desc";
using (DataTableReader reader = SqlTools.ExecuteReader(query,
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week)))
{
int rank = 0;
while (reader.Read())
{
++rank;
SqlTools.ExecuteNonQuery("update atp_ranking set year_" + t + "_ranking = @rank where player_ID = @player and week_no = @week and year = @year",
new SqlParam("@year", DbType.UInt32, year),
new SqlParam("@week", DbType.UInt32, week),
new SqlParam("@player", DbType.UInt64, reader["player_ID"]),
new SqlParam("@rank", DbType.UInt16, rank));
}
}
}
}
}
/// <summary>
/// Pour une année donnée, calcule les statistiques d'un joueur pour chaque tournoi.
/// </summary>
/// <param name="year">L'année à traiter.</param>
public void ComputePlayerStatsForYearEditions(int year)
{
SqlTools.ExecuteNonQuery("delete from edition_player_stats where edition_ID in (select ID from editions where year = @year)",
new SqlParam("@year", DbType.UInt32, year));
Dictionary <string, string> sqlFields = new Dictionary <string, string>
{
{ "edition_ID", "@edition" },
{ "player_ID", "@player" }
};
List <SqlParam> sqlParams = new List <SqlParam>
{
new SqlParam("@edition", DbType.UInt32),
new SqlParam("@player", DbType.UInt64)
};
Dictionary <string, object> sqlParamValues = new Dictionary <string, object>
{
{ "@edition", null },
{ "@player", null },
};
foreach (object statTypeRaw in Enum.GetValues(typeof(StatType)))
{
StatType statType = (StatType)statTypeRaw;
DbType dbType = DbType.UInt16;
switch (statType)
{
case StatType.round:
dbType = DbType.Byte;
break;
case StatType.is_winner:
dbType = DbType.Boolean;
break;
case StatType.points:
dbType = DbType.UInt32;
break;
}
sqlFields.Add(Tools.GetEnumSqlMapping <StatType>(statType), string.Concat("@", statType));
sqlParams.Add(new SqlParam(string.Concat("@", statType), dbType));
sqlParamValues.Add(string.Concat("@", statType), null);
}
using (SqlTools.SqlPrepared sqlPrepared = new SqlTools.SqlPrepared(SqlTools.BuildInsertQuery("edition_player_stats", sqlFields), sqlParams.ToArray()))
{
System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
sbSql.AppendLine("select distinct tmp.ID, tmp.pid ");
sbSql.AppendLine("from( ");
sbSql.AppendLine(" SELECT e.ID, m.winner_id as pid ");
sbSql.AppendLine(" FROM matches as m ");
sbSql.AppendLine(" join editions as e on m.edition_ID = e.ID ");
sbSql.AppendLine(" WHERE e.year = @year ");
sbSql.AppendLine(" union ALL ");
sbSql.AppendLine(" SELECT e.ID, m.loser_id as pid ");
sbSql.AppendLine(" FROM matches as m ");
sbSql.AppendLine(" join editions as e on m.edition_ID = e.ID ");
sbSql.AppendLine(" WHERE e.year = @year ");
sbSql.AppendLine(") as tmp");
using (DataTableReader reader = SqlTools.ExecuteReader(sbSql.ToString(), new SqlParam("@year", DbType.UInt32, year)))
{
while (reader.Read())
{
uint editionId = reader.GetUint32("ID");
ulong playerId = reader.GetUint64("pid");
sqlParamValues["@edition"] = editionId;
sqlParamValues["@player"] = playerId;
foreach (object statTypeRaw in Enum.GetValues(typeof(StatType)))
{
sqlParamValues[string.Concat("@", statTypeRaw)] = Player.ComputePlayerStatsForEdition(playerId, editionId, (StatType)statTypeRaw);
}
sqlPrepared.Execute(sqlParamValues);
}
}
}
}
/// <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 (Properties.Settings.Default.ComputeMatchesWhileLoading)
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
Match.SetBatchMode(false);
return(matchs);
}
