public void DeleteByMonthAndYear(int month, int year)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string command = "DELETE PlayerRivalryStatistics FROM PlayerRivalryStatistics prs INNER JOIN Game g ON g.Id = prs.GameId WHERE (DATEPART (month, g.[Date])) = @Month AND (DATEPART (year, g.[Date])) = @Year";
connection.Execute(command, new { Month = month, Year = year });
}
}
public void DeleteByGame(int gameId)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string command = "DELETE FROM PlayerRivalryStatistics WHERE GameId = @GameId";
connection.Execute(command, new { GameId = gameId });
}
}
public int GetEligibilityLine(int month, int year)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
return(connection.Query <int>("GetEligibilityLine", new { Month = month, Year = year }, commandType: CommandType.StoredProcedure).FirstOrDefault());
}
}
public void DeleteAll()
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string command = "DELETE FROM PlayerRivalryStatistics";
connection.Execute(command);
}
}
public void Delete(int id)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
connection.Execute("DELETE FROM Shot WHERE Id = @Id", new { Id = id });
}
}
public void DeleteToShot(int gameId, int shotId)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
connection.Execute("DELETE FROM Shot WHERE Id >= @Id and GameId = @GameId", new { Id = shotId, GameId = gameId });
}
}
public void Update(Shot shot)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
connection.Execute("UPDATE Shot SET Points = @Points, ShotTypeId = @ShotTypeId WHERE Id = @Id", new { Id = shot.Id, Points = shot.Points, ShotTypeId = shot.ShotType.Id });
}
}
public IEnumerable <Game> GetFinalizedByMonthAndYear(int month, int year)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query = "SELECT g.* FROM Game g INNER JOIN GameStatistics gs ON gs.GameId = g.Id WHERE (DATEPART (month, g.[Date])) = @Month AND (DATEPART (year, g.[Date])) = @Year";
var games = connection.Query <Game>(query, new { Month = month, Year = year }).ToList();
return(games);
}
}
public IEnumerable <Game> GetByMonthAndYear(int month, int year)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query = "SELECT * FROM Game WHERE (DATEPART (month, [Date])) = @Month AND (DATEPART (year, [Date])) = @Year";
var games = connection.Query <Game>(query, new { Month = month, Year = year }).ToList();
return(games);
}
}
public IEnumerable <Game> GetAllFinalized()
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query = "SELECT g.* FROM Game g INNER JOIN GameStatistics gs ON gs.GameId = g.Id";
var games = connection.Query <Game>(query).ToList();
return(games);
}
}
public IEnumerable <PlayerGameStatistics> GetByMonthAndYear(int month, int year)
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query = "SELECT * FROM PlayerGameStatistics pgs " +
"INNER JOIN Game g on g.Id = pgs.GameId " +
"INNER JOIN Player p ON p.Id = pgs.PlayerId " +
"WHERE (DATEPART (month, g.[Date])) = @Month AND (DATEPART (year, g.[Date])) = @Year";
var playerGameStatistics = connection.Query <PlayerGameStatistics, Game, Player, PlayerGameStatistics>(query, (pgs, g, p) => { pgs.Game = g; pgs.Player = p; return(pgs); }, new { Month = month, Year = year }).ToList();
return(playerGameStatistics);
}
}
public IEnumerable <HoleStatistics> All()
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query =
"SELECT SUM(hs.ShotsMade) AS ShotsMade, " +
"SUM(hs.Attempts) AS Attempts, " +
"SUM(CONVERT(decimal,hs.ShotsMade)) / SUM(CONVERT(decimal,hs.Attempts)) AS ShootingPercentage, " +
"SUM(hs.PointsScored) AS PointsScored, SUM(hs.Pushes) AS Pushes, SUM(hs.Steals) AS Steals, " +
"SUM(hs.SugarFreeSteals) AS SugarFreeSteals " +
"FROM HoleStatistics hs INNER JOIN Hole h ON h.Id = hs.HoleId GROUP BY h.Id HAVING SUM(Attempts) > 0 ";
var holeStatistics = connection.Query <HoleStatistics>(query).ToList();
return(holeStatistics);
}
}
public IEnumerable <Tuple <int, int> > GetActiveMonthsAndYears()
{
using (var connection = BolfTrackerDbConnection.GetProfiledConnection())
{
connection.Open();
string query = "SELECT (DATEPART(MONTH, [Date])) AS Month, (DATEPART(YEAR, [Date])) AS Year FROM Game GROUP BY (DATEPART(MONTH, [Date])), (DATEPART (year, [Date]))";
var rows = connection.Query(query);
var monthsAndYears = new List <Tuple <int, int> >();
foreach (var row in rows)
{
monthsAndYears.Add(new Tuple <int, int>(row.Month, row.Year));
}
return(monthsAndYears);
}
}