Ejemplo n.º 1
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;
                    }
                }
            }
        }