/// <summary>
/// Quand <see cref="_yearHasOwnTournamentCodes"/> est vrai, permet de récupérer le code générique du tournoi à partir de celui de l'année en cours.
/// Sinon, le code est retourné à l'identique.
/// </summary>
/// <param name="baseCode">Le code du tournoi pour l'année courante.</param>
/// <returns>Le code générique du tournoi.</returns>
private string GetGenericTournamentCode(string baseCode)
{
string code = baseCode;
if (_yearHasOwnTournamentCodes)
{
code = SqlTools.ExecuteScalar(string.Format("select code_original from tournaments_code_{0} where code_new = @code", _year),
baseCode, new SqlParam("@code", DbType.String, baseCode));
}
return(code);
}
/// <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));
}
// Calcule le nombre de données à charger.
private void ComputeDataCount()
{
List <string> tables = new List <string> {
"points", "editions", "tournaments", "players", "players_nat_history", "countries"
};
if (Properties.Settings.Default.ComputeStatisticsWhileLoading)
{
tables.Add("edition_player_stats");
}
if (Properties.Settings.Default.ComputeMatchesWhileLoading)
{
tables.Add("matches");
}
foreach (string table in tables)
{
_totalDataCount += SqlTools.ExecuteScalar(string.Format("select count(*) from {0}", table), 0);
}
}
/// <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>
/// Crée les nouveaux joueurs issus du fichier fourni.
/// Ne met pas à jour les informations sur les joueurs existants.
/// Ne met pas à jour les dates de début et fin d'activité.
/// </summary>
public void IntegrateNewPlayers()
{
string insertPlayerQuery = SqlTools.BuildInsertQuery("players", new Dictionary <string, string>
{
{ "ID", "@id" },
{ "name", "@name" },
{ "nationality", "@nat" },
{ "hand", "@hand" },
{ "height", "@height" },
{ "date_of_birth", "@dob" }
});
Dictionary <int, KeyValuePair <string, IEnumerable <SqlParam> > > queryByPlayer = new Dictionary <int, KeyValuePair <string, IEnumerable <SqlParam> > >();
foreach (Dictionary <string, string> match in _matchs)
{
for (int i = 1; i <= 2; i++)
{
string wOl = i == 1 ? "winner" : "loser";
if (SqlTools.ExecuteScalar("select count(*) from players where ID = @id and lower(replace(name, ' ', '')) = lower(replace(@name, ' ', ''))",
0, new SqlParam("@id", DbType.UInt64, match[wOl + "_id"]), new SqlParam("@name", DbType.String, match[wOl + "_name"])) <= 0)
{
string sqlName = SqlTools.ExecuteScalar <string>("select name from players where ID = @id",
null, new SqlParam("@id", DbType.UInt64, match[wOl + "_id"]));
if (string.IsNullOrWhiteSpace(sqlName))
{
DateTime dob = new DateTime(Tools.DEFAULT_YEAR, 1, 1);
if (!string.IsNullOrWhiteSpace(match[wOl + "_age"]))
{
// TODO : retravailler cette conversion
dob = Tools.ComputeDateOfBirth(Convert.ToDouble(match[wOl + "_age"].Replace('.', ',')),
Tools.FormatCsvDateTime(match["tourney_date"]));
}
if (!queryByPlayer.ContainsKey(Convert.ToInt32(match[wOl + "_id"])))
{
queryByPlayer.Add(Convert.ToInt32(match[wOl + "_id"]),
new KeyValuePair <string, IEnumerable <SqlParam> >(insertPlayerQuery,
new List <SqlParam>
{
new SqlParam("id", DbType.UInt64, match[wOl + "_id"]),
new SqlParam("name", DbType.String, match[wOl + "_name"]),
new SqlParam("nat", DbType.String, string.IsNullOrWhiteSpace(match[wOl + "_ioc"]) ? DBNull.Value : (object)match[wOl + "_ioc"]),
new SqlParam("hand", DbType.String, !new[] { "L", "R" }.Contains(match[wOl + "_hand"].ToUpper()) ? DBNull.Value : (object)match[wOl + "_hand"].ToUpper()),
new SqlParam("height", DbType.UInt32, string.IsNullOrWhiteSpace(match[wOl + "_ht"]) ? DBNull.Value : (object)match[wOl + "_ht"]),
new SqlParam("dob", DbType.DateTime, dob.Year == Tools.DEFAULT_YEAR ? DBNull.Value : (object)dob)
}));
}
}
else
{
Tools.WriteLog(string.Format("L'identifiant {0} existe mais les noms '{1}' / '{2}' ne correspondent pas.", match[wOl + "_id"], match[wOl + "_name"], sqlName));
}
}
}
}
foreach (int playerId in queryByPlayer.Keys)
{
try
{
SqlTools.ExecuteNonQuery(queryByPlayer[playerId].Key, queryByPlayer[playerId].Value.ToArray());
}
catch (Exception ex)
{
Tools.WriteLog(string.Format("Echec de l'insertion du joueur {0}, avec le message : {1}.", playerId, ex.Message));
}
}
}