/// <summary>
/// Creates a stoolball competition and populates the <see cref="Competition.CompetitionId"/>
/// </summary>
/// <returns>The created competition</returns>
public async Task <Competition> CreateCompetition(Competition competition, Guid memberKey, string memberName)
{
if (competition is null)
{
throw new ArgumentNullException(nameof(competition));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
var auditableCompetition = _copier.CreateAuditableCopy(competition);
auditableCompetition.CompetitionId = Guid.NewGuid();
auditableCompetition.Introduction = _htmlSanitiser.Sanitize(auditableCompetition.Introduction);
auditableCompetition.PublicContactDetails = _htmlSanitiser.Sanitize(auditableCompetition.PublicContactDetails);
auditableCompetition.PrivateContactDetails = _htmlSanitiser.Sanitize(auditableCompetition.PrivateContactDetails);
auditableCompetition.Facebook = _urlFormatter.PrefixHttpsProtocol(auditableCompetition.Facebook)?.ToString();
auditableCompetition.Twitter = _socialMediaAccountFormatter.PrefixAtSign(auditableCompetition.Twitter);
auditableCompetition.Instagram = _socialMediaAccountFormatter.PrefixAtSign(auditableCompetition.Instagram);
auditableCompetition.YouTube = _urlFormatter.PrefixHttpsProtocol(auditableCompetition.YouTube)?.ToString();
auditableCompetition.Website = _urlFormatter.PrefixHttpsProtocol(auditableCompetition.Website)?.ToString();
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableCompetition.CompetitionRoute = await _routeGenerator.GenerateUniqueRoute(
"/competitions", auditableCompetition.CompetitionName, NoiseWords.CompetitionRoute,
async route => await connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.Competition} WHERE CompetitionRoute = @CompetitionRoute", new { auditableCompetition.CompetitionRoute }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
await connection.ExecuteAsync(
$@"INSERT INTO {Tables.Competition} (CompetitionId, PlayerType, Introduction, PublicContactDetails, PrivateContactDetails,
Facebook, Twitter, Instagram, YouTube, Website, CompetitionRoute, MemberGroupKey, MemberGroupName)
VALUES (@CompetitionId, @PlayerType, @Introduction, @PublicContactDetails, @PrivateContactDetails,
@Facebook, @Twitter, @Instagram, @YouTube, @Website, @CompetitionRoute, @MemberGroupKey, @MemberGroupName)",
new
{
auditableCompetition.CompetitionId,
auditableCompetition.PlayerType,
auditableCompetition.Introduction,
auditableCompetition.PublicContactDetails,
auditableCompetition.PrivateContactDetails,
auditableCompetition.Facebook,
auditableCompetition.Twitter,
auditableCompetition.Instagram,
auditableCompetition.YouTube,
auditableCompetition.Website,
auditableCompetition.CompetitionRoute,
auditableCompetition.MemberGroupKey,
auditableCompetition.MemberGroupName
}, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"INSERT INTO {Tables.CompetitionVersion}
(CompetitionVersionId, CompetitionId, CompetitionName, ComparableName, FromDate, UntilDate)
VALUES (@CompetitionVersionId, @CompetitionId, @CompetitionName, @ComparableName, @FromDate, @UntilDate)",
new
{
CompetitionVersionId = Guid.NewGuid(),
auditableCompetition.CompetitionId,
auditableCompetition.CompetitionName,
ComparableName = auditableCompetition.ComparableName(),
FromDate = auditableCompetition.FromYear.HasValue ? new DateTime(auditableCompetition.FromYear.Value, 1, 1) : DateTime.UtcNow.Date,
UntilDate = auditableCompetition.UntilYear.HasValue ? new DateTime(auditableCompetition.UntilYear.Value, 12, 31) : (DateTime?)null
}, transaction).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableCompetition);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Create,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableCompetition.EntityUri,
State = JsonConvert.SerializeObject(auditableCompetition),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Created, redacted, memberName, memberKey, GetType(), nameof(SqlServerCompetitionRepository.CreateCompetition));
}
}
return(auditableCompetition);
}
/// <summary>
/// Creates a team using an existing transaction
/// </summary>
public async Task <Team> CreateTeam(Team team, IDbTransaction transaction, string memberUsername)
{
if (team is null)
{
throw new ArgumentNullException(nameof(team));
}
if (transaction is null)
{
throw new ArgumentNullException(nameof(transaction));
}
if (string.IsNullOrWhiteSpace(memberUsername))
{
throw new ArgumentNullException(nameof(memberUsername));
}
var auditableTeam = _copier.CreateAuditableCopy(team);
auditableTeam.TeamId = Guid.NewGuid();
auditableTeam.Introduction = _htmlSanitiser.Sanitize(auditableTeam.Introduction);
auditableTeam.PlayingTimes = _htmlSanitiser.Sanitize(auditableTeam.PlayingTimes);
auditableTeam.Cost = _htmlSanitiser.Sanitize(auditableTeam.Cost);
auditableTeam.PublicContactDetails = _htmlSanitiser.Sanitize(auditableTeam.PublicContactDetails);
auditableTeam.PrivateContactDetails = _htmlSanitiser.Sanitize(auditableTeam.PrivateContactDetails);
auditableTeam.Facebook = _urlFormatter.PrefixHttpsProtocol(auditableTeam.Facebook)?.ToString();
auditableTeam.Twitter = _socialMediaAccountFormatter.PrefixAtSign(auditableTeam.Twitter);
auditableTeam.Instagram = _socialMediaAccountFormatter.PrefixAtSign(auditableTeam.Instagram);
auditableTeam.YouTube = _urlFormatter.PrefixHttpsProtocol(auditableTeam.YouTube)?.ToString();
auditableTeam.Website = _urlFormatter.PrefixHttpsProtocol(auditableTeam.Website)?.ToString();
// Create a route. Generally {team.teamRoute} will be blank, but allowing a pre-populated prefix is useful for transient teams
auditableTeam.TeamRoute = await _routeGenerator.GenerateUniqueRoute(
$"{auditableTeam.TeamRoute}/teams", auditableTeam.TeamName, NoiseWords.TeamRoute,
async route => await transaction.Connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.Team} WHERE TeamRoute = @TeamRoute", new { auditableTeam.TeamRoute }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
// Create an owner group
var group = _memberGroupHelper.CreateOrFindGroup("team", auditableTeam.TeamName, NoiseWords.TeamRoute);
auditableTeam.MemberGroupKey = group.Key;
auditableTeam.MemberGroupName = group.Name;
// Assign the member to the group unless they're already admin
if (!_memberGroupHelper.MemberIsAdministrator(memberUsername))
{
_memberGroupHelper.AssignRole(memberUsername, group.Name);
}
await transaction.Connection.ExecuteAsync(
$@"INSERT INTO {Tables.Team} (TeamId, TeamType, AgeRangeLower, AgeRangeUpper, PlayerType, Introduction, PlayingTimes, Cost, ClubMark,
PublicContactDetails, PrivateContactDetails, Facebook, Twitter, Instagram, YouTube, Website, TeamRoute, MemberGroupKey, MemberGroupName)
VALUES (@TeamId, @TeamType, @AgeRangeLower, @AgeRangeUpper, @PlayerType, @Introduction, @PlayingTimes, @Cost, @ClubMark,
@PublicContactDetails, @PrivateContactDetails, @Facebook, @Twitter, @Instagram, @YouTube, @Website, @TeamRoute, @MemberGroupKey, @MemberGroupName)",
new
{
auditableTeam.TeamId,
TeamType = auditableTeam.TeamType.ToString(),
auditableTeam.AgeRangeLower,
auditableTeam.AgeRangeUpper,
PlayerType = auditableTeam.PlayerType.ToString(),
auditableTeam.Introduction,
auditableTeam.PlayingTimes,
auditableTeam.Cost,
auditableTeam.ClubMark,
auditableTeam.PublicContactDetails,
auditableTeam.PrivateContactDetails,
auditableTeam.Facebook,
auditableTeam.Twitter,
auditableTeam.Instagram,
auditableTeam.YouTube,
auditableTeam.Website,
auditableTeam.TeamRoute,
auditableTeam.MemberGroupKey,
auditableTeam.MemberGroupName
}, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($@"INSERT INTO {Tables.TeamVersion}
(TeamVersionId, TeamId, TeamName, ComparableName, FromDate, UntilDate) VALUES (@TeamVersionId, @TeamId, @TeamName, @ComparableName, @FromDate, @UntilDate)",
new
{
TeamVersionId = Guid.NewGuid(),
auditableTeam.TeamId,
auditableTeam.TeamName,
ComparableName = auditableTeam.ComparableName(),
FromDate = DateTime.UtcNow.Date,
UntilDate = auditableTeam.UntilYear.HasValue ? new DateTime(auditableTeam.UntilYear.Value, 12, 31).ToUniversalTime() : (DateTime?)null
}, transaction).ConfigureAwait(false);
await InsertNewMatchLocationsForTeam(auditableTeam, new List <Guid>(), transaction).ConfigureAwait(false);
return(auditableTeam);
}
/// <summary>
/// Creates a stoolball club and populates the <see cref="Club.ClubId"/>
/// </summary>
public async Task <Club> CreateClub(Club club, Guid memberKey, string memberName)
{
if (club is null)
{
throw new ArgumentNullException(nameof(club));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
var auditableClub = _copier.CreateAuditableCopy(club);
auditableClub.ClubId = Guid.NewGuid();
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableClub.ClubRoute = await _routeGenerator.GenerateUniqueRoute(
"/clubs", auditableClub.ClubName, NoiseWords.ClubRoute,
async route => await connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.Club} WHERE ClubRoute = @ClubRoute", new { ClubRoute = route }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
await connection.ExecuteAsync(
$@"INSERT INTO {Tables.Club} (ClubId, ClubRoute, MemberGroupKey, MemberGroupName)
VALUES (@ClubId, @ClubRoute, @MemberGroupKey, @MemberGroupName)",
new
{
auditableClub.ClubId,
auditableClub.ClubRoute,
auditableClub.MemberGroupKey,
auditableClub.MemberGroupName
}, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"INSERT INTO {Tables.ClubVersion}
(ClubVersionId, ClubId, ClubName, ComparableName, FromDate) VALUES (@ClubVersionId, @ClubId, @ClubName, @ComparableName, @FromDate)",
new
{
ClubVersionId = Guid.NewGuid(),
auditableClub.ClubId,
auditableClub.ClubName,
ComparableName = auditableClub.ComparableName(),
FromDate = DateTime.UtcNow.Date
}, transaction).ConfigureAwait(false);
// Check for ClubId IS NULL, otherwise the owner of Club B can edit Club A by reassigning its team
await connection.ExecuteAsync($"UPDATE {Tables.Team} SET ClubId = @ClubId WHERE TeamId IN @TeamIds AND ClubId IS NULL", new { auditableClub.ClubId, TeamIds = auditableClub.Teams.Select(x => x.TeamId) }, transaction).ConfigureAwait(false);
var serialisedClub = JsonConvert.SerializeObject(auditableClub);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Create,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableClub.EntityUri,
State = serialisedClub,
RedactedState = serialisedClub,
AuditDate = DateTime.UtcNow
},
transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Created, auditableClub, memberName, memberKey, GetType(), nameof(CreateClub));
}
}
return(auditableClub);
}
/// <summary>
/// Creates a match location and populates the <see cref="MatchLocation.MatchLocationId"/>
/// </summary>
/// <returns>The created match location</returns>
public async Task <MatchLocation> CreateMatchLocation(MatchLocation matchLocation, Guid memberKey, string memberName)
{
if (matchLocation is null)
{
throw new ArgumentNullException(nameof(matchLocation));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
var auditableMatchLocation = _copier.CreateAuditableCopy(matchLocation);
auditableMatchLocation.MatchLocationId = Guid.NewGuid();
auditableMatchLocation.MatchLocationNotes = _htmlSanitiser.Sanitize(auditableMatchLocation.MatchLocationNotes);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableMatchLocation.MatchLocationRoute = await _routeGenerator.GenerateUniqueRoute(
"/locations", auditableMatchLocation.NameAndLocalityOrTownIfDifferent(), NoiseWords.MatchLocationRoute,
async route => await connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.MatchLocation} WHERE MatchLocationRoute = @MatchLocationRoute", new { auditableMatchLocation.MatchLocationRoute }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
await connection.ExecuteAsync(
$@"INSERT INTO {Tables.MatchLocation} (MatchLocationId, SecondaryAddressableObjectName, PrimaryAddressableObjectName, StreetDescription, Locality, Town,
AdministrativeArea, Postcode, ComparableName, GeoPrecision, Latitude, Longitude, MatchLocationNotes, MatchLocationRoute, MemberGroupKey, MemberGroupName)
VALUES (@MatchLocationId, @SecondaryAddressableObjectName, @PrimaryAddressableObjectName, @StreetDescription, @Locality, @Town, @AdministrativeArea,
@Postcode, @ComparableName, @GeoPrecision, @Latitude, @Longitude, @MatchLocationNotes, @MatchLocationRoute, @MemberGroupKey, @MemberGroupName)",
new
{
auditableMatchLocation.MatchLocationId,
auditableMatchLocation.SecondaryAddressableObjectName,
auditableMatchLocation.PrimaryAddressableObjectName,
auditableMatchLocation.StreetDescription,
auditableMatchLocation.Locality,
auditableMatchLocation.Town,
auditableMatchLocation.AdministrativeArea,
auditableMatchLocation.Postcode,
ComparableName = auditableMatchLocation.ComparableName(),
GeoPrecision = auditableMatchLocation.GeoPrecision?.ToString(),
auditableMatchLocation.Latitude,
auditableMatchLocation.Longitude,
auditableMatchLocation.MatchLocationNotes,
auditableMatchLocation.MatchLocationRoute,
auditableMatchLocation.MemberGroupKey,
auditableMatchLocation.MemberGroupName
}, transaction).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableMatchLocation);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Create,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = matchLocation.EntityUri,
State = JsonConvert.SerializeObject(auditableMatchLocation),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Created, redacted, memberName, memberKey, GetType(), nameof(CreateMatchLocation));
}
}
return(auditableMatchLocation);
}
/// <summary>
/// Finds an existing player identity or creates it if it is not found
/// </summary>
/// <returns>The <see cref="PlayerIdentity.PlayerIdentityId"/> of the created or matched player identity</returns>
public async Task <PlayerIdentity> CreateOrMatchPlayerIdentity(PlayerIdentity playerIdentity, Guid memberKey, string memberName, IDbTransaction transaction)
{
if (playerIdentity is null)
{
throw new ArgumentNullException(nameof(playerIdentity));
}
if (playerIdentity.PlayerIdentityId.HasValue && playerIdentity.Player.PlayerId.HasValue)
{
return(playerIdentity);
}
if (string.IsNullOrWhiteSpace(playerIdentity.PlayerIdentityName))
{
throw new ArgumentException($"'{nameof(playerIdentity)}.PlayerIdentityName' cannot be null or whitespace", nameof(playerIdentity));
}
if (playerIdentity.Team?.TeamId == null)
{
throw new ArgumentException($"'{nameof(playerIdentity)}.Team.TeamId' cannot be null", nameof(playerIdentity));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
if (transaction is null)
{
throw new ArgumentNullException(nameof(transaction));
}
var matchedPlayerIdentity = (await transaction.Connection.QueryAsync <PlayerIdentity, Player, PlayerIdentity>(
$"SELECT PlayerIdentityId, PlayerIdentityName, PlayerId FROM {Tables.PlayerIdentity} WHERE ComparableName = @ComparableName AND TeamId = @TeamId",
(pi, p) =>
{
pi.Player = p;
return(pi);
},
new
{
ComparableName = playerIdentity.ComparableName(),
playerIdentity.Team.TeamId
},
transaction,
splitOn: "PlayerId").ConfigureAwait(false)).FirstOrDefault();
if (matchedPlayerIdentity != null && matchedPlayerIdentity.PlayerIdentityId.HasValue && matchedPlayerIdentity.Player.PlayerId.HasValue)
{
matchedPlayerIdentity.Team = playerIdentity.Team;
return(matchedPlayerIdentity);
}
var auditablePlayerIdentity = _copier.CreateAuditableCopy(playerIdentity);
auditablePlayerIdentity.PlayerIdentityId = Guid.NewGuid();
auditablePlayerIdentity.PlayerIdentityName = _playerNameFormatter.CapitaliseName(auditablePlayerIdentity.PlayerIdentityName);
var player = new Player {
PlayerId = Guid.NewGuid()
};
player.PlayerIdentities.Add(auditablePlayerIdentity);
player.PlayerRoute = await _routeGenerator.GenerateUniqueRoute($"/players", auditablePlayerIdentity.PlayerIdentityName, NoiseWords.PlayerRoute,
async route => await transaction.Connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.Player} WHERE PlayerRoute = @PlayerRoute", new { player.PlayerRoute }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync(
$@"INSERT INTO {Tables.Player}
(PlayerId, PlayerRoute)
VALUES
(@PlayerId, @PlayerRoute)",
new
{
player.PlayerId,
player.PlayerRoute
}, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($@"INSERT INTO {Tables.PlayerIdentity}
(PlayerIdentityId, PlayerId, PlayerIdentityName, ComparableName, TeamId)
VALUES (@PlayerIdentityId, @PlayerId, @PlayerIdentityName, @ComparableName, @TeamId)",
new
{
auditablePlayerIdentity.PlayerIdentityId,
player.PlayerId,
auditablePlayerIdentity.PlayerIdentityName,
ComparableName = auditablePlayerIdentity.ComparableName(),
auditablePlayerIdentity.Team.TeamId
}, transaction).ConfigureAwait(false);
var serialisedPlayer = JsonConvert.SerializeObject(player);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Create,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = player.EntityUri,
State = serialisedPlayer,
RedactedState = serialisedPlayer,
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
_logger.Info(GetType(), LoggingTemplates.Created, player, memberName, memberKey, GetType(), nameof(CreateOrMatchPlayerIdentity));
player.PlayerIdentities.Clear();
auditablePlayerIdentity.Player = player;
return(auditablePlayerIdentity);
}