// 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 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));
}
}
}
/// <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 (Config.GetBool(AppKey.ComputeStatisticsWhileLoading))
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
}
/// <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"]));
}
}
}
}
}
/// <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 (Config.GetBool(AppKey.ComputeMatchesWhileLoading))
{
_dataLoadingProgressEventHandler?.Invoke(new DataLoadingProgressEvent(100 * ++_currentDataCount / _totalDataCount));
}
}
}
Match.SetBatchMode(false);
return(matchs);
}