/// <inheritdoc />
public async Task DeleteSeasons(IEnumerable <Season> seasons, Guid memberKey, string memberName, IDbTransaction transaction)
{
if (seasons is null)
{
throw new ArgumentNullException(nameof(seasons));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentException($"'{nameof(memberName)}' cannot be null or whitespace", nameof(memberName));
}
if (transaction is null)
{
throw new ArgumentNullException(nameof(transaction));
}
var auditableSeasons = seasons.Select(x => _copier.CreateAuditableCopy(x));
var seasonIds = auditableSeasons.Select(x => x.SeasonId.Value);
await transaction.Connection.ExecuteAsync($"UPDATE {Tables.PlayerInMatchStatistics} SET SeasonId = NULL WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.SeasonTeam} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.PointsRule} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.PointsAdjustment} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.OverSet} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.SeasonMatchType} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.AwardedTo} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"UPDATE {Tables.Match} SET SeasonId = NULL WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.TournamentSeason} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
await transaction.Connection.ExecuteAsync($"DELETE FROM {Tables.Season} WHERE SeasonId IN @seasonIds", new { seasonIds }, transaction).ConfigureAwait(false);
foreach (var auditableSeason in auditableSeasons)
{
await _redirectsRepository.DeleteRedirectsByDestinationPrefix(auditableSeason.SeasonRoute, transaction).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableSeason);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Delete,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableSeason.EntityUri,
State = JsonConvert.SerializeObject(auditableSeason),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
_logger.Info(GetType(), LoggingTemplates.Deleted, redacted, memberName, memberKey, GetType(), nameof(DeleteSeason));
}
}
/// <summary>
/// Deletes a stoolball competition
/// </summary>
public async Task DeleteCompetition(Competition competition, Guid memberKey, string memberName)
{
if (competition is null)
{
throw new ArgumentNullException(nameof(competition));
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var seasonIdsNotSupplied = await connection.QueryAsync <Guid>($"SELECT SeasonId FROM {Tables.Season} WHERE CompetitionId = @CompetitionId AND SeasonId NOT IN @seasonIds",
new
{
competition.CompetitionId,
seasonIds = competition.Seasons.Select(x => x.SeasonId.Value)
},
transaction).ConfigureAwait(false);
if (seasonIdsNotSupplied.Any())
{
competition.Seasons.AddRange(seasonIdsNotSupplied.Select(x => new Season {
SeasonId = x
}));
}
await _seasonRepository.DeleteSeasons(competition.Seasons, memberKey, memberName, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInMatchStatistics} SET CompetitionId = NULL WHERE CompetitionId = @CompetitionId", new { competition.CompetitionId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.CompetitionVersion} WHERE CompetitionId = @CompetitionId", new { competition.CompetitionId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.Competition} WHERE CompetitionId = @CompetitionId", new { competition.CompetitionId }, transaction).ConfigureAwait(false);
await _redirectsRepository.DeleteRedirectsByDestinationPrefix(competition.CompetitionRoute, transaction).ConfigureAwait(false);
var auditableCompetition = _copier.CreateAuditableCopy(competition);
var redacted = _copier.CreateRedactedCopy(auditableCompetition);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Delete,
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.Deleted, redacted, memberName, memberKey, GetType(), nameof(DeleteCompetition));
}
}
}
/// <summary>
/// Delete a stoolball club
/// </summary>
public async Task DeleteClub(Club club, Guid memberKey, string memberName)
{
if (club is null)
{
throw new ArgumentNullException(nameof(club));
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
await connection.ExecuteAsync($@"UPDATE {Tables.PlayerInMatchStatistics} SET ClubId = NULL WHERE ClubId = @ClubId", new { club.ClubId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"UPDATE {Tables.Team} SET ClubId = NULL, ClubMark = 0 WHERE ClubId = @ClubId", new { club.ClubId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"DELETE FROM {Tables.AwardedTo} WHERE ClubId = @ClubId", new { club.ClubId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"DELETE FROM {Tables.ClubVersion} WHERE ClubId = @ClubId", new { club.ClubId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"DELETE FROM {Tables.Club} WHERE ClubId = @ClubId", new { club.ClubId }, transaction).ConfigureAwait(false);
await _redirectsRepository.DeleteRedirectsByDestinationPrefix(club.ClubRoute, transaction).ConfigureAwait(false);
var auditableClub = _copier.CreateAuditableCopy(club);
var serialisedClub = JsonConvert.SerializeObject(auditableClub);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Delete,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableClub.EntityUri,
State = serialisedClub,
RedactedState = serialisedClub,
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Deleted, club, memberName, memberKey, GetType(), nameof(SqlServerClubRepository.DeleteClub));
}
}
}
/// <summary>
/// Deletes a stoolball match location
/// </summary>
public async Task DeleteMatchLocation(MatchLocation matchLocation, Guid memberKey, string memberName)
{
if (matchLocation is null)
{
throw new ArgumentNullException(nameof(matchLocation));
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
await connection.ExecuteAsync($@"UPDATE {Tables.PlayerInMatchStatistics} SET MatchLocationId = NULL WHERE MatchLocationId = @MatchLocationId", new { matchLocation.MatchLocationId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"UPDATE {Tables.Match} SET MatchLocationId = NULL WHERE MatchLocationId = @MatchLocationId", new { matchLocation.MatchLocationId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"DELETE FROM {Tables.TeamMatchLocation} WHERE MatchLocationId = @MatchLocationId", new { matchLocation.MatchLocationId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($@"DELETE FROM {Tables.MatchLocation} WHERE MatchLocationId = @MatchLocationId", new { matchLocation.MatchLocationId }, transaction).ConfigureAwait(false);
var auditableMatchLocation = _copier.CreateAuditableCopy(matchLocation);
var redacted = _copier.CreateRedactedCopy(auditableMatchLocation);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Delete,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = matchLocation.EntityUri,
State = JsonConvert.SerializeObject(auditableMatchLocation),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
await _redirectsRepository.DeleteRedirectsByDestinationPrefix(auditableMatchLocation.MatchLocationRoute, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Deleted, redacted, memberName, memberKey, GetType(), nameof(DeleteMatchLocation));
}
}
}
/// <summary>
/// Deletes a stoolball team
/// </summary>
public async Task DeleteTeam(Team team, Guid memberKey, string memberName)
{
if (team is null)
{
throw new ArgumentNullException(nameof(team));
}
var auditableTeam = _copier.CreateAuditableCopy(team);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
await connection.ExecuteAsync($"DELETE FROM {Tables.PlayerInMatchStatistics} WHERE TeamId = @TeamId OR OppositionTeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInMatchStatistics} SET BowledByPlayerIdentityId = NULL WHERE BowledByPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInMatchStatistics} SET CaughtByPlayerIdentityId = NULL WHERE CaughtByPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInMatchStatistics} SET RunOutByPlayerIdentityId = NULL WHERE RunOutByPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInnings} SET DismissedByPlayerIdentityId = NULL WHERE DismissedByPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.PlayerInnings} SET BowlerPlayerIdentityId = NULL WHERE BowlerPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.PlayerInnings} WHERE BatterPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.Over} WHERE BowlerPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.BowlingFigures} WHERE BowlerPlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.AwardedTo} WHERE PlayerIdentityId IN (SELECT PlayerIdentityId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
var playerIds = await connection.QueryAsync <Guid>($"SELECT PlayerId FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.PlayerIdentity} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.Player} WHERE PlayerId IN @playerIds AND PlayerId NOT IN (SELECT PlayerId FROM {Tables.PlayerIdentity} WHERE PlayerId IN @playerIds)", new { playerIds }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.MatchInnings} SET BattingMatchTeamId = NULL WHERE BattingMatchTeamId IN (SELECT MatchTeamId FROM {Tables.MatchTeam} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.MatchInnings} SET BowlingMatchTeamId = NULL WHERE BowlingMatchTeamId IN (SELECT MatchTeamId FROM {Tables.MatchTeam} WHERE TeamId = @TeamId)", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.MatchTeam} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.TournamentTeam} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.PointsAdjustment} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.SeasonTeam} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.TeamMatchLocation} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.TeamVersion} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"DELETE FROM {Tables.Team} WHERE TeamId = @TeamId", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false);
await _redirectsRepository.DeleteRedirectsByDestinationPrefix(auditableTeam.TeamRoute, transaction).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableTeam);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Delete,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableTeam.EntityUri,
State = JsonConvert.SerializeObject(auditableTeam),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Deleted, redacted, memberName, memberKey, GetType(), nameof(SqlServerTeamRepository.DeleteTeam));
}
}
}