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