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);
}
}
}
}
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++;
}
}