Beispiel #1
0
 public void DefenseList(string file)
 {
     for (int i = 0; i < 32; i++)
     {
         Defense.Add(new Player.PlayerInfo());
         Defense[i].Name = Teams[i];
     }
     using (StreamReader sr = new StreamReader(file))
     {
         sr.ReadLine();
         for (int i = 0; i < 16; i++)
         {
             foreach (Player.PlayerInfo Player in Defense)
             {
                 Player.Defense p     = new Player.Defense();
                 string         line  = sr.ReadLine();
                 string[]       pLine = line.Split('|');
                 p.PassYardsAllowed = pLine[0];
                 p.RushYardsAllowed = pLine[1];
                 p.Touchdowns       = pLine[2];
                 p.Safeties         = pLine[3];
                 p.Interceptions    = pLine[4];
                 p.Fumbles          = pLine[5];
                 Player.DefStats.Add(p);
             }
         }
     }
 }
Beispiel #2
0
 public void insertPlayerData()
 {
     foreach (Player.PlayerInfo QB in QuarterBacks)
     {
         for (int i = 0; i < QB.Stats.Count; i++)
         {
             Player.QBRWTE p   = QB.Stats[i];
             string        sql = "INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" +
                                 "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + QB.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") ";
         }
     }
     foreach (Player.PlayerInfo RB in RunningBacks)
     {
         for (int i = 0; i < RB.Stats.Count; i++)
         {
             Player.QBRWTE p   = RB.Stats[i];
             string        sql = "INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" +
                                 "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + RB.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") ";
         }
     }
     foreach (Player.PlayerInfo WR in WideReceivers)
     {
         for (int i = 0; i < WR.Stats.Count; i++)
         {
             Player.QBRWTE p   = WR.Stats[i];
             string        sql = "INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" +
                                 "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + WR.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") ";
         }
     }
     foreach (Player.PlayerInfo TE in TightEnds)
     {
         for (int i = 0; i < TE.Stats.Count; i++)
         {
             Player.QBRWTE p   = TE.Stats[i];
             string        sql = "INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" +
                                 "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + TE.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") ";
         }
     }
     foreach (Player.PlayerInfo DEF in Defense)
     {
         for (int i = 0; i < DEF.DefStats.Count; i++)
         {
             Player.Defense p   = DEF.DefStats[i];
             string         sql = "INSERT Players.DefenseStats(PlayerID, PassYardsAllowed, RushYardsAllowed, TouchdownsAllowed, Safeties, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" +
                                  "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + DEF.Name + ", " + p.PassYardsAllowed + ", " + p.RushYardsAllowed + ", " + p.Touchdowns + ", " + p.Safeties + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") ";
         }
     }
 }
        /// <summary>
        /// Inserts the player stats.
        /// </summary>
        public void InsertPlayerData()
        {
            Controller controller = new Controller();

            controller.FillPlayerInfoArray(@"..\\..\\..\\..\\GeneratedDataPlayerInfo.txt");
            controller.SortPlayerInfo();
            controller.QuarterBackList(@"..\\..\\..\\..\\GeneratedDataQBStats.txt");
            controller.RunningBackList(@"..\\..\\..\\..\\GeneratedDataRBStats.txt");
            controller.WideReceiversList(@"..\\..\\..\\..\\GeneratedDataWRStats.txt");
            controller.TightEndsList(@"..\\..\\..\\..\\GeneratedDataTEStats.txt");
            controller.KickerList(@"..\\..\\..\\..\\GeneratedDataKickerStats.txt");
            controller.DefenseList(@"..\\..\\..\\..\\DefensiveStats.txt");

            foreach (Player.PlayerInfo QB in controller.QuarterBacks)
            {
                for (int i = 0; i < QB.Stats.Count; i++)
                {
                    Player.QBRWTE p   = QB.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{QB.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{QB.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo RB in controller.RunningBacks)
            {
                for (int i = 0; i < RB.Stats.Count; i++)
                {
                    Player.QBRWTE p   = RB.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{RB.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{RB.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo WR in controller.WideReceivers)
            {
                for (int i = 0; i < WR.Stats.Count; i++)
                {
                    Player.QBRWTE p   = WR.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{WR.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{WR.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo TE in controller.TightEnds)
            {
                for (int i = 0; i < TE.Stats.Count; i++)
                {
                    Player.QBRWTE p   = TE.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{TE.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{TE.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo K in controller.Kickers)
            {
                for (int i = 0; i < K.KickerStats.Count; i++)
                {
                    Player.Kicker p   = K.KickerStats[i];
                    string        sql = $"INSERT Players.KickerStats(PlayerID, XPMade, XPMissed, FGGD, FGNG, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{K.Name}'), {p.XPMade}, {p.XPMissed}, {p.FGGD}, {p.FGNG}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{K.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            int    k      = 1;
            Random random = new Random();

            foreach (Player.PlayerInfo DEF in controller.Defense)
            {
                for (int i = 0; i < DEF.DefStats.Count; i++)
                {
                    Player.Defense p   = DEF.DefStats[i];
                    string         sql = $"INSERT Players.DefenseStats(PlayerID, PassYardsAllowed, RushYardsAllowed, TouchdownsAllowed, Touchdowns, Safeties, Interceptions, Fumbles, TeamGameID, ByeWeek) " +
                                         $"VALUES({k}, {p.PassYardsAllowed}, {p.RushYardsAllowed}, {p.Touchdowns}, {random.Next(10)}, {p.Safeties}, {p.Interceptions}, {p.Fumbles}, " +
                                         $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                         $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = {k} " +
                                         $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY), (SELECT T.ByeWeek FROM NFL.Teams T WHERE T.TeamID = {k}))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
                k++;
            }
        }