public void KickerList(string file)
{
using (StreamReader sr = new StreamReader(file))
{
sr.ReadLine();
for (int i = 0; i < 16; i++)
{
foreach (Player.PlayerInfo Player in Kickers)
{
Player.Kicker p = new Player.Kicker();
if (sr.EndOfStream)
{
break;
}
string line = sr.ReadLine();
string[] pLine = line.Split('|');
p.XPMade = pLine[0];
p.XPMissed = pLine[1];
p.FGGD = pLine[2];
p.FGNG = pLine[3];
Player.KickerStats.Add(p);
}
}
Console.WriteLine("The Number of K is " + Kickers.Count);
Console.WriteLine("The Number of K Stats is " + Kickers[0].KickerStats.Count);
}
}
/// <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++;
}
}
