public List<Game> AllSeasonGames(int seasonId, int leagueId)
{
List<Game> games = new List<Game>();
string queryString = "sp_FullSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@leagueId", leagueId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(5);
game.HomeTeamScore = reader.GetInt32(6);
game.HomeTeamMoneyLine = reader.GetInt32(7);
game.HomeTeamSpread = reader.GetDecimal(8);
game.AwayTeamId = reader.GetInt32(9);
game.AwayTeamConferenceId = reader.GetInt32(10);
game.AwayTeamName = reader.GetString(11);
game.AwayTeamScore = reader.GetInt32(12);
game.AwayTeamMoneyLine = reader.GetInt32(13);
game.AwayTeamSpread = reader.GetDecimal(14);
game.WinningTeamId = reader.GetInt32(15);
game.WinningTeamName = reader.GetString(16);
game.GameLocationName = reader.GetString(17);
game.SeasonId = reader.GetInt32(18);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return games;
}
public ActionResult GetGameDetails(int gameId)
{
var data = new Object { };
Game game = new Game(gameId);
if (game.HomeTeamId == 0)
{
data = new { success = false, error= "faild to load game"};
}
else
{
data = new { success = true, game = game };
}
return Json(data);
}
public List<Game> TeamSchedule(int seasonId, int teamId)
{
List<Game> games = new List<Game>();
string queryString = "sp_TeamSchedule";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@teamId", teamId);
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.GameId = reader.GetInt32(0);
game.GameDate = reader.GetDateTime(1);
game.GameStatusTypeId = reader.GetInt32(2);
game.HomeTeamId = reader.GetInt32(3);
// game.HomeTeamConferenceId = reader.GetInt32(4);
game.HomeTeamName = reader.GetString(4);
game.HomeTeamScore = reader.GetInt32(5);
game.AwayTeamId = reader.GetInt32(13);
game.AwayTeamName = reader.GetString(14);
game.AwayTeamScore = reader.GetInt32(15);
game.WinningTeamId = reader.GetInt32(23);
game.WinningTeamName = reader.GetString(24);
//game.GameLocationName = reader.GetString(17);
if (teamId == game.HomeTeamId)
{
game.ScrapUrl = reader.GetString(12);
}
else
{
game.ScrapUrl = reader.GetString(22);
}
game.GameDateFormated = game.GameDate.ToShortDateString();
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
return games;
}
public List<Game> MLBBetPotentials(int seasonId)
{
List<Game> games = new List<Game>();
string queryString = "sp_MLBBetPotentials";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@seasonId", seasonId);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Game game = new Game();
game.HomeTeamId = reader.GetInt32(0);
game.AwayTeamId = reader.GetInt32(1);
game.WinningTeamId = reader.GetInt32(2);
games.Add(game);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
int listLength = games.Count;
Game tempGame;
Game removeGame;
for (int i = 0; i <= listLength; i++)
{
if (i <= games.Count)
{
removeGame = null;
tempGame = games[i];
removeGame = games.Where(g => g.HomeTeamId == tempGame.AwayTeamId && g.AwayTeamId == tempGame.HomeTeamId).FirstOrDefault();
if (removeGame != null)
{
games.Remove(removeGame);
}
}
else
{
break;
}
}
return games;
}
//private void _LoadGame() {
// string queryString = "SELECT * FROM [dbo].[Games] WHERE GameId = @gameId";
// using (SqlConnection connection = new SqlConnection(Base.conn))
// {
// // Create the Command and Parameter objects.
// SqlCommand command = new SqlCommand(queryString, connection);
// command.Parameters.AddWithValue("@gameId", this.GameId);
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// while (reader.Read())
// {
// this.GameId = reader.GetInt32(0);
// this.GameDate = reader.GetDateTime(1);
// this.GameStatusTypeId = reader.GetInt32(2);
// this.HomeTeamId = reader.GetInt32(3);
// this.HomeTeamName = reader.GetString(4);
// this.HomeTeamScore = reader.GetInt32(5);
// this.HomeTeamSpreadPay = reader.GetDecimalSafe(6);
// this.HomeTeamSpreadPayOpen = reader.GetDecimalSafe(7);
// this.HomeTeamSpread = reader.GetDecimalSafe(8);
// this.HomeTeamSpreadOpen = reader.GetDecimalSafe(9);
// this.HomeTeamMoneyLine = reader.GetInt32(10);
// this.HomeTeamMoneyLineOpen = reader.GetInt32(11);
// //this.HomeTeamLine = reader.GetDecimalSafe(6);
// this.AwayTeamId = reader.GetInt32(13);
// this.AwayTeamName = reader.GetString(14);
// this.AwayTeamScore = reader.GetInt32(15);
// this.AwayTeamSpreadPay = reader.GetDecimalSafe(16);
// this.AwayTeamSpreadPayOpen = reader.GetDecimalSafe(17);
// this.AwayTeamSpread = reader.GetDecimalSafe(18);
// this.AwayTeamSpreadOpen = reader.GetDecimalSafe(19);
// this.AwayTeamMoneyLine = reader.GetInt32(20);
// this.AwayTeamMoneyLineOpen = reader.GetInt32(21);
// //this.AwayTeamLine = reader.GetDecimalSafe(11);
// this.WinningTeamId = reader.GetInt32(23);
// this.WinningTeamName = reader.GetString(24);
// this.SeasonId = reader.GetInt32(25);
// this.GameLocationName = reader.GetString(26);
// this.WonSpread = reader.GetBoolean(27);
// this.WonMoneyLine = reader.GetBoolean(28);
// }
// reader.Close();
// this.GameDateFormated = this.GameDate.ToString("yyyyMMdd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US"));
// }
// catch (Exception ex)
// {
// SportsError sError = new SportsError(ex, gameId);
// }
// }
//}
public bool InsertGame()
{
Game gameCheck = new Game();
string queryCheckGame = "SELECT [GameId],[SeasonId],[GameDate],[GameStatusTypeId] " +
",[HomeTeamId],[HomeTeamScore] " +
",[AwayTeamId],[AwayTeamScore] " +
",[WinningTeamId] FROM [dbo].[Game] "
+ " WHERE HomeTeamId = @HomeTeamId "
+ " AND AwayTeamId = @AwayTeamId "
+ " AND HomeTeamScore = @HomeTeamScore "
+ " AND AwayTeamScore = @AwayTeamScore "
+ " AND SeasonId = @SeasonId AND GameDate = @GameDate";
//if (this.HomeTeamId == 31 && this.AwayTeamId == 39)
//{
// bool test = true;
//}
//if (this.HomeTeamId == 39 && this.AwayTeamId == 31)
//{
// bool test = true;
//}
using (SqlConnection connectionCheck = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand commandCheck = new SqlCommand(queryCheckGame, connectionCheck);
commandCheck.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
commandCheck.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
commandCheck.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
commandCheck.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
commandCheck.Parameters.AddWithValue("@GameDate", this.GameDate);
commandCheck.Parameters.AddWithValue("@SeasonId", this.SeasonId);
//TEMP COMMENT OUT TO INSERT TWO GAMES ON SAME DAY!
try
{
connectionCheck.Open();
SqlDataReader reader = commandCheck.ExecuteReader();
while (reader.Read())
{
gameCheck.GameId = reader.GetInt32(0);
gameCheck.SeasonId = reader.GetInt32(1);
gameCheck.GameDate = reader.GetDateTime(2);
gameCheck.GameStatusTypeId = reader.GetInt32(3);
gameCheck.HomeTeamId = reader.GetInt32(4);
gameCheck.HomeTeamScore = reader.GetInt32(5);
// gameCheck.HomeTeamLine = 0; //6
gameCheck.AwayTeamId = reader.GetInt32(6);
gameCheck.AwayTeamScore = reader.GetInt32(7);
// gameCheck.AwayTeamLine = 0; //9
gameCheck.WinningTeamId = reader.GetInt32(8);
}
reader.Close();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
if (gameCheck.GameId != 0)
{
//if (gameCheck.GameStatusTypeId != this.GameStatusTypeId && gameCheck.WinningTeamId != this.WinningTeamId)
//{
// gameCheck.GameStatusTypeId = this.GameStatusTypeId;
// gameCheck.WinningTeamId = this.WinningTeamId;
// gameCheck.HomeTeamScore = this.HomeTeamScore;
// gameCheck.AwayTeamScore = this.AwayTeamScore;
// gameCheck.UpdateGame();
//}
}
else
{
string queryString = "INSERT INTO [dbo].[Game] "
+ "([SeasonId],[GameDate],[GameStatusTypeId],[HomeTeamId],[HomeTeamScore] "
+ ",[AwayTeamId],[AwayTeamScore],[WinningTeamId]) "
+ " VALUES "
+ "(@SeasonId,@GameDate,@GameStatusTypeId,@HomeTeamId,@HomeTeamScore "
+ ",@AwayTeamId,@AwayTeamScore,@WinningTeamId)";
using (SqlConnection connection = new SqlConnection(Base.conn))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@SeasonId", this.SeasonId);
command.Parameters.AddWithValue("@GameDate", this.GameDate);
command.Parameters.AddWithValue("@GameStatusTypeID", this.GameStatusTypeId);
command.Parameters.AddWithValue("@HomeTeamId", this.HomeTeamId);
command.Parameters.AddWithValue("@HomeTeamScore", this.HomeTeamScore);
//command.Parameters.AddWithValue("@HomeTeamLine", this.HomeTeamLine);
command.Parameters.AddWithValue("@AwayTeamId", this.AwayTeamId);
command.Parameters.AddWithValue("@AwayTeamScore", this.AwayTeamScore);
// command.Parameters.AddWithValue("@AwayTeamLine", this.AwayTeamLine);
command.Parameters.AddWithValue("@WinningTeamId", this.WinningTeamId);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
SportsError sError = new SportsError(ex);
}
}
}
return true;
}
public ActionResult InsertMLBGameScrap(List<GameScraping> games)
{
List<Team> teams = AppStatic.MLBTeams;
List<Game> gameList = new List<Game>();
foreach (GameScraping scrap in games)
{
if (scrap.GameScore.Contains("F"))
{
scrap.GameScore = scrap.GameScore.Remove(scrap.GameScore.IndexOf('F'), scrap.GameScore.Length - scrap.GameScore.IndexOf('F'));
}
if (scrap.GameScore.Contains("OT"))
{
scrap.GameScore = scrap.GameScore.Replace("OT", "");
}
string[] score = scrap.GameScore.Split('-');
Game game = new Game();
DateTime dateVal;
game.SeasonId = scrap.SeasonId;
scrap.GameDate = scrap.GameDate.Substring(5, (scrap.GameDate.Length - 5));
if (DateTime.TryParse(scrap.GameDate, out dateVal))
{ game.GameDate = dateVal; }
game.WinningTeamId = 0;
game.HomeTeamScore = 0;
game.AwayTeamScore = 0;
game.GameStatusTypeId = 2;
if (scrap.Location == "@")
{
game.HomeTeamId = teams.Where(t => t.ScrapId == scrap.TeamTwoScrapId.Trim()).Select(t => t.TeamId).FirstOrDefault();
game.AwayTeamId = scrap.TeamOneId;
}
else
{
game.HomeTeamId = scrap.TeamOneId;
game.AwayTeamId = teams.Where(t => t.ScrapId == scrap.TeamTwoScrapId.Trim()).Select(t => t.TeamId).FirstOrDefault();
}
if (scrap.GameStatus != "Scheduled" && scrap.GameStatus != "Postponed")
{
game.GameStatusTypeId = 1;
score[0] = score[0].Replace(" 1st", "").Replace(" 2nd", "").Replace(" 3rd", "").Replace(" 4th", "").Replace(" 5th", "").Replace(" 6th", "").Replace(" 7th", "").Replace(" 8th", "").Replace(" 9th", "");
score[1] = score[1].Replace(" 1st", "").Replace(" 2nd", "").Replace(" 3rd", "").Replace(" 4th", "").Replace(" 5th", "").Replace(" 6th", "").Replace(" 7th", "").Replace(" 8th", "").Replace(" 9th", "");
if (game.HomeTeamId == scrap.TeamOneId && scrap.GameStatus == "W")
{
game.HomeTeamScore = Convert.ToInt32(score[0]);
game.AwayTeamScore = Convert.ToInt32(score[1]);
}
else if (game.HomeTeamId == scrap.TeamOneId && scrap.GameStatus == "L")
{
game.HomeTeamScore = Convert.ToInt32(score[1]);
game.AwayTeamScore = Convert.ToInt32(score[0]);
}
if (game.AwayTeamId == scrap.TeamOneId && scrap.GameStatus == "W")
{
game.AwayTeamScore = Convert.ToInt32(score[0]);
game.HomeTeamScore = Convert.ToInt32(score[1]);
}
else if (game.AwayTeamId == scrap.TeamOneId && scrap.GameStatus == "L")
{
game.AwayTeamScore = Convert.ToInt32(score[1]);
game.HomeTeamScore = Convert.ToInt32(score[0]);
}
if (game.HomeTeamScore > game.AwayTeamScore)
{
game.WinningTeamId = game.HomeTeamId;
}
else
{
game.WinningTeamId = game.AwayTeamId;
}
}
else
{
if (scrap.GameStatus == "Postponed")
{
game.GameStatusTypeId = 4;
}
}
//game.TeamOneId = scrap.TeamOneId;
//game.TeamTwoId = teams.Where(t => t.ScrapId == scrap.TeamTwoScrapId.Trim()).Select(t => t.TeamId).FirstOrDefault();
//switch (scrap.GameStatus)
//{
// case "W":
// game.GameStatusTypeId = 1;
// game.WinningTeamId = game.TeamOneId;
// game.TeamOneScore = Convert.ToInt32(score[0]);
// game.TeamTwoScore = Convert.ToInt32(score[1]);
// break;
// case "L":
// game.GameStatusId = 2;
// game.WinningTeamId = game.TeamTwoId;
// game.TeamOneScore = Convert.ToInt32(score[1]);
// game.TeamTwoScore = Convert.ToInt32(score[0]);
// break;
// case "Scheduled":
// game.GameStatusId = 3;
// game.TeamOneScore = 0;
// game.TeamTwoScore = 0;
// break;
//}
game.InsertGame();
gameList.Add(game);
}
var test = games;
var data = new Object { };
// DataAccess da = new DataAccess();
//List<Team> teams = da.AllTeam();
data = new { success = true, teams = teams };
return Json(data);
}
// public ActionResult UpdateGameLines(int gameId, decimal homeLine, decimal awayLine)
public ActionResult UpdateGameLines(Game game)
{
var data = new object();
//Game game = new Game();
game.UpdateGameLine();
data = new { success = true };
return Json(data);
}