/// <summary> /// Creates a stoolball season and populates the <see cref="Season.SeasonId"/> /// </summary> /// <returns>The created season</returns> public async Task <Season> CreateSeason(Season season, Guid memberKey, string memberName) { if (season is null) { throw new ArgumentNullException(nameof(season)); } if (string.IsNullOrWhiteSpace(memberName)) { throw new ArgumentNullException(nameof(memberName)); } var auditableSeason = _copier.CreateAuditableCopy(season); auditableSeason.SeasonId = Guid.NewGuid(); auditableSeason.Introduction = _htmlSanitiser.Sanitize(auditableSeason.Introduction); auditableSeason.Results = _htmlSanitiser.Sanitize(auditableSeason.Results); using (var connection = _databaseConnectionFactory.CreateDatabaseConnection()) { connection.Open(); using (var transaction = connection.BeginTransaction()) { auditableSeason.SeasonRoute = $"{auditableSeason.Competition.CompetitionRoute}/{auditableSeason.FromYear}"; if (auditableSeason.UntilYear > auditableSeason.FromYear) { auditableSeason.SeasonRoute = $"{auditableSeason.SeasonRoute}-{auditableSeason.UntilYear.ToString(CultureInfo.InvariantCulture).Substring(2)}"; } // Get the most recent season, if any, to copy existing settings as defaults var previousSeason = await connection.QuerySingleOrDefaultAsync <Season>( $"SELECT TOP 1 SeasonId, ResultsTableType, EnableRunsScored, EnableRunsConceded FROM {Tables.Season} WHERE CompetitionId = @CompetitionId AND FromYear < @FromYear ORDER BY FromYear DESC", new { auditableSeason.Competition.CompetitionId, auditableSeason.FromYear }, transaction).ConfigureAwait(false); await connection.ExecuteAsync( $@"INSERT INTO {Tables.Season} (SeasonId, CompetitionId, FromYear, UntilYear, Introduction, EnableTournaments, EnableBonusOrPenaltyRuns, PlayersPerTeam, EnableLastPlayerBatsOn, ResultsTableType, EnableRunsScored, EnableRunsConceded, Results, SeasonRoute) VALUES (@SeasonId, @CompetitionId, @FromYear, @UntilYear, @Introduction, @EnableTournaments, @EnableBonusOrPenaltyRuns, @PlayersPerTeam, @EnableLastPlayerBatsOn, @ResultsTableType, @EnableRunsScored, @EnableRunsConceded, @Results, @SeasonRoute)", new { auditableSeason.SeasonId, auditableSeason.Competition.CompetitionId, auditableSeason.FromYear, auditableSeason.UntilYear, auditableSeason.Introduction, auditableSeason.EnableTournaments, auditableSeason.EnableBonusOrPenaltyRuns, auditableSeason.PlayersPerTeam, auditableSeason.EnableLastPlayerBatsOn, ResultsTableType = previousSeason?.ResultsTableType.ToString() ?? ResultsTableType.None.ToString(), EnableRunsScored = previousSeason?.EnableRunsScored ?? false, EnableRunsConceded = previousSeason?.EnableRunsConceded ?? false, auditableSeason.Results, auditableSeason.SeasonRoute }, transaction).ConfigureAwait(false); await InsertOverSets(auditableSeason, transaction).ConfigureAwait(false); foreach (var matchType in auditableSeason.MatchTypes) { await connection.ExecuteAsync($@"INSERT INTO {Tables.SeasonMatchType} (SeasonMatchTypeId, SeasonId, MatchType) VALUES (@SeasonMatchTypeId, @SeasonId, @MatchType)", new { SeasonMatchTypeId = Guid.NewGuid(), auditableSeason.SeasonId, MatchType = matchType.ToString() }, transaction).ConfigureAwait(false); } // Copy points rules from the most recent season if (previousSeason != null) { auditableSeason.PointsRules = (await connection.QueryAsync <PointsRule>( $@"SELECT MatchResultType, HomePoints, AwayPoints FROM { Tables.PointsRule } WHERE SeasonId = @SeasonId", new { previousSeason.SeasonId }, transaction).ConfigureAwait(false)).ToList(); } // If there are none, start with some default points rules if (auditableSeason.PointsRules.Count == 0) { auditableSeason.PointsRules.AddRange(new PointsRule[] { new PointsRule { MatchResultType = MatchResultType.HomeWin, HomePoints = 2, AwayPoints = 0 }, new PointsRule { MatchResultType = MatchResultType.AwayWin, HomePoints = 0, AwayPoints = 2 }, new PointsRule { MatchResultType = MatchResultType.HomeWinByForfeit, HomePoints = 2, AwayPoints = 0 }, new PointsRule { MatchResultType = MatchResultType.AwayWinByForfeit, HomePoints = 0, AwayPoints = 2 }, new PointsRule { MatchResultType = MatchResultType.Tie, HomePoints = 1, AwayPoints = 1 }, new PointsRule { MatchResultType = MatchResultType.Cancelled, HomePoints = 1, AwayPoints = 1 }, new PointsRule { MatchResultType = MatchResultType.AbandonedDuringPlayAndCancelled, HomePoints = 1, AwayPoints = 1 } }); } foreach (var pointsRule in auditableSeason.PointsRules) { pointsRule.PointsRuleId = Guid.NewGuid(); await connection.ExecuteAsync($@"INSERT INTO { Tables.PointsRule } (PointsRuleId, SeasonId, MatchResultType, HomePoints, AwayPoints) VALUES (@PointsRuleId, @SeasonId, @MatchResultType, @HomePoints, @AwayPoints)", new { pointsRule.PointsRuleId, auditableSeason.SeasonId, pointsRule.MatchResultType, pointsRule.HomePoints, pointsRule.AwayPoints }, transaction).ConfigureAwait(false); } // Copy teams from the most recent season, where the teams did not withdraw and were still active in the season being added if (previousSeason != null) { var teamIds = await connection.QueryAsync <Guid>( $@"SELECT DISTINCT t.TeamId FROM { Tables.SeasonTeam } st INNER JOIN { Tables.Team } t ON st.TeamId = t.TeamId INNER JOIN { Tables.TeamVersion } tv ON t.TeamId = tv.TeamId WHERE st.SeasonId = @SeasonId AND st.WithdrawnDate IS NULL AND (tv.UntilDate IS NULL OR tv.UntilDate <= @FromDate)", new { previousSeason.SeasonId, FromDate = new DateTime(auditableSeason.FromYear, 12, 31).ToUniversalTime() }, transaction).ConfigureAwait(false); foreach (var teamId in teamIds) { auditableSeason.Teams.Add(new TeamInSeason { Team = new Team { TeamId = teamId } }); await connection.ExecuteAsync($@"INSERT INTO { Tables.SeasonTeam } (SeasonTeamId, SeasonId, TeamId) VALUES (@SeasonTeamId, @SeasonId, @TeamId)", new { SeasonTeamId = Guid.NewGuid(), auditableSeason.SeasonId, teamId }, transaction).ConfigureAwait(false); } } var redacted = _copier.CreateRedactedCopy(auditableSeason); await _auditRepository.CreateAudit(new AuditRecord { Action = AuditAction.Create, MemberKey = memberKey, ActorName = memberName, EntityUri = auditableSeason.EntityUri, State = JsonConvert.SerializeObject(auditableSeason), RedactedState = JsonConvert.SerializeObject(redacted), AuditDate = DateTime.UtcNow }, transaction).ConfigureAwait(false); transaction.Commit(); _logger.Info(GetType(), LoggingTemplates.Created, redacted, memberName, memberKey, GetType(), nameof(SqlServerSeasonRepository.CreateSeason)); } } return(auditableSeason); }
/// <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 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> /// 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> /// 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); }