public async Task <KnownUserRecord> GetKnownUserById(Guid uniqueId)
{
return((await _queryExecutor
.NewQuery("SELECT * FROM [security].[User] WHERE [UniqueId]=@UniqueId;")
.WithParameters(new {
UniqueId = uniqueId
})
.ExecuteAsync <KnownUserRecord>())
.Single());
}
public async Task <UserForAuthentication> GetUserForAuthentication(string username)
{
var query = @"
SELECT [Id], [Username], [PasswordHash], [Salt], [Iterations], [Prf], [ForceChangePassword], [Title], [GivenName], [FamilyName], [EmailAddress]
FROM [dbo].[User]
WHERE [Username] = @Username AND [IsDeleted] = 0";
var results = await _queryExecutor.NewQuery(query)
.WithParameters(new { Username = username })
.ExecuteDynamicAsync();
return(results
.Select(
_ => new UserForAuthentication {
Id = _.Id,
Username = _.Username,
Password = new HashedPassword(
_.PasswordHash,
_.Salt,
_.Iterations,
(KeyDerivationPrf)Enum.Parse(typeof(KeyDerivationPrf), _.Prf)),
ForceChangePassword = _.ForceChangePassword,
Title = _.Title,
FirstName = _.GivenName,
LastName = _.FamilyName,
EmailAddress = _.EmailAddress
})
.SingleOrDefault());
}
public async Task UpdateSport(string sportName, Sport newState)
{
if (sportName == null)
{
throw new ArgumentNullException(nameof(sportName));
}
if (newState == null)
{
throw new ArgumentNullException(nameof(newState));
}
var numChanged = await _queryExecutor
.NewQuery("UPDATE [dbo].[Sport] SET [Name]=@Name, [FullName]=@FullName WHERE [Name]=@id")
.WithCommandType(CommandType.Text)
.WithParameters(
new {
Id = sportName,
Name = newState.Name,
FullName = newState.FullName
})
.ExecuteAsync();
if (numChanged < 0)
{
throw new InvalidIdentifierException($"No Sport with id '{sportName}' was found.");
}
}
public async Task UpdateVenue(string venueName, Venue newState)
{
if (venueName == null)
{
throw new ArgumentNullException(nameof(venueName));
}
if (newState == null)
{
throw new ArgumentNullException(nameof(newState));
}
var numChanged = await _queryExecutor
.NewQuery("UPDATE [dbo].[Venue] SET [Name]=@Name, [Country]=@Country WHERE [Name]=@id")
.WithCommandType(CommandType.Text)
.WithParameters(
new {
Id = venueName,
Name = newState.Name,
Country = newState.Country
})
.ExecuteAsync();
if (numChanged < 0)
{
throw new InvalidIdentifierException($"No Venue with id '{venueName}' was found.");
}
}
public async Task <IEnumerable <EventHistoryItem> > GetEventHistory(int roundId)
{
var round = await _context.Round
.AsNoTracking()
.Include(r => r.RelatedSeason)
.AsNoTracking()
.Where(r => r.Id == roundId)
.FirstOrDefaultAsync();
if (round == null)
{
throw new ArgumentException("The specified round was not found.", nameof(roundId));
}
return(await _queryExecutor.NewQuery(@"
SELECT TOP 5
R.[Id],
R.[Date],
R.[Rating],
R.[Rain],
SE.[Name] AS WinningTeam,
STRING_AGG(P.[FirstName] + ' ' + P.[LastName], ', ') WITHIN GROUP (ORDER BY P.[LastName] ASC, P.[FirstName] ASC) AS WinningParticipants
FROM
[dbo].[Round] R
INNER JOIN [dbo].[Season] S ON R.[Season] = S.[Id]
INNER JOIN [dbo].[Status] ST ON ST.[Name] = R.[Status]
LEFT JOIN [dbo].[SeasonEntry] SE ON SE.[Season] = S.[Id] AND SE.[Team] = R.[WinningTeam]
LEFT JOIN [dbo].[RoundWinner] RW ON R.[Id] = RW.[Round]
LEFT JOIN [dbo].[Participant] P ON P.[Id] = RW.[Participant]
WHERE
R.[Venue] = @Venue
-- If the requested round is non-championship, only get history of non-championship events
-- If the requested round is part of the championship, exclude non-championship events
AND ((@IsNonChampionship = 1 AND R.[Number] = 0) OR (@IsNonChampionship = 0 AND R.[Number] > 0))
AND ST.[Step] > 1
AND S.[Sport] = @Sport
AND R.[Date] <= @Date
GROUP BY
R.[Id],
R.[Date],
R.[Rating],
R.[Rain],
SE.[Name]
ORDER BY
R.[Date] DESC")
.WithCommandType(CommandType.Text)
.WithParameters(new {
Venue = round.Venue,
Sport = round.RelatedSeason.Sport,
Date = round.Date.Date,
IsNonChampionship = round.Number < 1
})
.ExecuteAsync <EventHistoryItem>());
}
public Task <IEnumerable <RoundToAcquire> > GetRoundsToAcquire()
{
return(_queryExecutor.NewQuery(@"
SELECT
S.[Sport],
R.[Id],
R.[Number],
R.[Date],
R.[Name],
R.[Venue]
FROM
[dbo].[Round] R
INNER JOIN [dbo].[Season] S ON S.[Id] = R.[Season]
INNER JOIN [dbo].[Status] ST ON ST.[Name] = R.[Status]
WHERE
ST.[Step] = 0 -- Scheduled but not ReadyToWatch
AND R.[Date] <= @Today")
.WithCommandType(CommandType.Text)
.WithParameters(new { Today = DateTime.Now.Date })
.ExecuteAsync <RoundToAcquire>());
}
public async Task UpdateSeasonEntry(SeasonEntryEditModel seasonEntry)
{
if (seasonEntry == null)
{
throw new ArgumentNullException(nameof(seasonEntry));
}
await _queryExecutor
.NewQuery("UPDATE [dbo].[SeasonEntry] SET Name=@Name WHERE [Season]=@Season AND [Team]=@Team")
.WithCommandType(CommandType.Text)
.WithParameters(new {
Season = seasonEntry.Season,
Team = seasonEntry.Team,
Name = seasonEntry.Name
})
.ExecuteAsync();
}
