public async Task <int> CreateTeam(Team team)
{
var sql = @"INSERT INTO [dbo].Teams (TeamName, EntryDate, LeaveDate, Livery)
VALUES (@TeamName, @EntryDate, @LeaveDate, @Livery)
SELECT CAST (SCOPE_IDENTITY() as int);";
using (var conn = _connectionProvider.Get())
{
return(await conn.ExecuteScalarAsync <int>(sql, team));
}
}
public async Task <int> AddATeamPrinciple(TeamPrinciple teamPrinciple)
{
var sql = @"INSERT INTO [dbo].TeamPrinciple (FirstName, LastName, Nationality, DOB, EntryDate, LeaveDate)
VALUES (@FirstName, @LastName, @Nationality, @DOB, @EntryDate, @LeaveDate)
SELECT CAST (SCOPE_IDENTITY() as int);";
using (var conn = _connectionProvider.Get())
{
return(await conn.ExecuteScalarAsync <int>(sql, teamPrinciple));
}
}
public async Task <List <QualifingResults> > GetDriversCurrentSeasonVsLastSeasonQualifingResults(int season)
{
const string sql = @" SELECT D.Id AS DriverId,
RC.StartDate,
RT.TrackName,
RT.Country,
Q1Time,
Q2Time,
Q3Time,
Q.Position
FROM Qualifying Q
INNER JOIN Drivers D ON D.Id = Q.DriverId
INNER JOIN RaceCalendar RC ON RC.Id = Q.CalId
INNER JOIN Tires T ON T.Id = Q.TireId
INNER JOIN RaceTracks RT ON RT.Id = RC.TrackId
ORDER BY RC.StartDate
";
using (var conn = _connectionProvider.Get())
{
var data = await conn.QueryAsync <QualifingResults>(sql, new { season });
return(data.AsList());
}
}
public async Task <List <DriverRaceDataForChampionship> > GetSeasonsChampionshipResults(int season)
{
const string sql = @" SELECT R.DriverId,
P.Points,
R.FastestLap,
D.DriverNumber,
D.FirstName,
D.LastName,
D.Country,
T.TeamName
FROM RaceResults R
INNER JOIN RaceCalendar C ON C.Id = R.CalId
INNER JOIN Points P ON P.Id = R.PointsId
INNER JOIN Drivers D ON D.Id = R.DriverId
INNER JOIN DriverMarket DM ON DM.DriverId = D.Id
INNER JOIN Teams T ON T.Id = DM.TeamId
WHERE YEAR(C.StartDate) = @season";
using (var conn = _connectionProvider.Get())
{
var data = await conn.QueryAsync <DriverRaceDataForChampionship>(sql, new { season });
return(data.AsList());
}
}
public async Task <List <int> > GetAllDriverIds()
{
const string sql = @"SELECT Id
FROM [dbo].Drivers";
using (var conn = _connectionProvider.Get())
{
var data = await conn.QueryAsync <int>(sql);
return(data.ToList());
}
}
public async Task <List <RaceCalendar> > GetRaceCalander(int season)
{
const string sql = @"SELECT
RT.TrackName,
RT.Location,
RC.StartDate,
RC.EventName
FROM RaceCalendar RC
INNER JOIN RaceTracks RT ON RT.Id = RC.TrackId
WHERE YEAR(StartDate) = @season
";
using (var conn = _connectionProvider.Get())
{
var data = await conn.QueryAsync <RaceCalendar>(sql, new { season });
return(data.ToList());
}
}
public async Task <List <TeamPrinciple> > GetAllTeamPrinciples()
{
const string sql = @"SELECT
Id,
FirstName,
Nationality,
DOB,
LastName,
EntryDate,
LeaveDate
FROM [dbo].TeamPrinciple
";
using (var conn = _connectionProvider.Get())
{
var data = await conn.QueryAsync <TeamPrinciple>(sql);
return(data.AsList());
}
}
public async Task <UserSession> GetAsync(WebSocket socket)
{
var connection = connectionProvider.Get(socket);
if (connection == null)
{
throw new BadConnectionException();
}
var playerSession = await sessionBinder.BindAsync(connection);
if (playerSession == null)
{
throw new SessionNotFoundException();
}
logger.WriteDebug($"Session '{playerSession.Id}' connected to stream.");
playerConnectionHandler.Open(playerSession, connection);
return(playerSession);
}
