/// <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 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> /// 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="Models.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); throw new Exception("IntegrateMatchs failure."); // TODO } } }
/// <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)); } } }