/// <summary>
/// Updates a stoolball match location
/// </summary>
public async Task <MatchLocation> UpdateMatchLocation(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.MatchLocationNotes = _htmlSanitiser.Sanitize(auditableMatchLocation.MatchLocationNotes);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableMatchLocation.MatchLocationRoute = await _routeGenerator.GenerateUniqueRoute(
matchLocation.MatchLocationRoute,
"/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(
$@"UPDATE {Tables.MatchLocation} SET
SecondaryAddressableObjectName = @SecondaryAddressableObjectName,
PrimaryAddressableObjectName = @PrimaryAddressableObjectName,
StreetDescription = @StreetDescription,
Locality = @Locality,
Town = @Town,
AdministrativeArea = @AdministrativeArea,
Postcode = @Postcode,
ComparableName = @ComparableName,
GeoPrecision = @GeoPrecision,
Latitude = @Latitude,
Longitude = @Longitude,
MatchLocationNotes = @MatchLocationNotes,
MatchLocationRoute = @MatchLocationRoute
WHERE MatchLocationId = @MatchLocationId" ,
new
{
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.MatchLocationId
}, transaction).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableMatchLocation);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Update,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = matchLocation.EntityUri,
State = JsonConvert.SerializeObject(auditableMatchLocation),
RedactedState = JsonConvert.SerializeObject(redacted),
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
if (matchLocation.MatchLocationRoute != auditableMatchLocation.MatchLocationRoute)
{
await _redirectsRepository.InsertRedirect(matchLocation.MatchLocationRoute, auditableMatchLocation.MatchLocationRoute, null, transaction).ConfigureAwait(false);
}
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Updated, redacted, memberName, memberKey, GetType(), nameof(UpdateMatchLocation));
}
}
return(auditableMatchLocation);
}
/// <summary>
/// Updates a stoolball team
/// </summary>
public async Task <Team> UpdateTeam(Team team, Guid memberKey, string memberName)
{
if (team is null)
{
throw new ArgumentNullException(nameof(team));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
var auditableTeam = _copier.CreateAuditableCopy(team);
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();
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableTeam.TeamRoute = await _routeGenerator.GenerateUniqueRoute(
team.TeamRoute,
"/teams", auditableTeam.TeamName, NoiseWords.TeamRoute,
async route => await connection.ExecuteScalarAsync <int>($"SELECT COUNT(*) FROM {Tables.Team} WHERE TeamRoute = @TeamRoute", new { auditableTeam.TeamRoute }, transaction).ConfigureAwait(false)
).ConfigureAwait(false);
await connection.ExecuteAsync(
$@"UPDATE {Tables.Team} SET
TeamType = @TeamType,
AgeRangeLower = @AgeRangeLower,
AgeRangeUpper = @AgeRangeUpper,
PlayerType = @PlayerType,
Introduction = @Introduction,
PlayingTimes = @PlayingTimes,
Cost = @Cost,
PublicContactDetails = @PublicContactDetails,
PrivateContactDetails = @PrivateContactDetails,
Facebook = @Facebook,
Twitter = @Twitter,
Instagram = @Instagram,
YouTube = @YouTube,
Website = @Website,
TeamRoute = @TeamRoute
WHERE TeamId = @TeamId" ,
new
{
TeamType = auditableTeam.TeamType.ToString(),
auditableTeam.AgeRangeLower,
auditableTeam.AgeRangeUpper,
PlayerType = auditableTeam.PlayerType.ToString(),
auditableTeam.Introduction,
auditableTeam.PlayingTimes,
auditableTeam.Cost,
auditableTeam.PublicContactDetails,
auditableTeam.PrivateContactDetails,
auditableTeam.Facebook,
auditableTeam.Twitter,
auditableTeam.Instagram,
auditableTeam.YouTube,
auditableTeam.Website,
auditableTeam.TeamRoute,
auditableTeam.TeamId
}, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.TeamVersion} SET TeamName = @TeamName, ComparableName = @ComparableName, UntilDate = @UntilDate WHERE TeamId = @TeamId",
new
{
auditableTeam.TeamName,
ComparableName = auditableTeam.ComparableName(),
UntilDate = auditableTeam.UntilYear.HasValue ? new DateTime(auditableTeam.UntilYear.Value, 12, 31).ToUniversalTime() : (DateTime?)null,
auditableTeam.TeamId
},
transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.TeamMatchLocation} SET UntilDate = @UntilDate WHERE TeamId = @TeamId AND UntilDate IS NULL AND MatchLocationId NOT IN @MatchLocationIds", new { UntilDate = DateTime.UtcNow.Date.AddDays(1).AddSeconds(-1), auditableTeam.TeamId, MatchLocationIds = auditableTeam.MatchLocations.Select(x => x.MatchLocationId) }, transaction).ConfigureAwait(false);
var currentLocations = (await connection.QueryAsync <Guid>($"SELECT MatchLocationId FROM {Tables.TeamMatchLocation} tml WHERE TeamId = @TeamId AND tml.UntilDate IS NULL", new { auditableTeam.TeamId }, transaction).ConfigureAwait(false)).ToList();
await InsertNewMatchLocationsForTeam(auditableTeam, currentLocations, transaction).ConfigureAwait(false);
if (team.TeamRoute != auditableTeam.TeamRoute)
{
await _redirectsRepository.InsertRedirect(team.TeamRoute, auditableTeam.TeamRoute, null, transaction).ConfigureAwait(false);
}
var redacted = _copier.CreateRedactedCopy(auditableTeam);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Update,
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.Updated, redacted, memberName, memberKey, GetType(), nameof(SqlServerTeamRepository.UpdateTeam));
}
}
return(auditableTeam);
}
/// <summary>
/// Updates a stoolball club
/// </summary>
public async Task <Club> UpdateClub(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);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
auditableClub.ClubRoute = await _routeGenerator.GenerateUniqueRoute(
club.ClubRoute,
"/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(
$@"UPDATE {Tables.Club} SET
ClubRoute = @ClubRoute
WHERE ClubId = @ClubId" ,
new
{
auditableClub.ClubRoute,
auditableClub.ClubId
}, transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.ClubVersion} SET ClubName = @ClubName, ComparableName = @ComparableName WHERE ClubId = @ClubId",
new
{
auditableClub.ClubName,
ComparableName = auditableClub.ComparableName(),
auditableClub.ClubId
},
transaction).ConfigureAwait(false);
// Add any newly-assigned teams to this club, and set ClubMark = 1 if any other team in this club has ClubMark = 1 (therefore removing teams has to come later)
// 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,
ClubMark = (SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM {Tables.Team} WHERE ClubId = @ClubId AND ClubMark = 1)
WHERE TeamId IN @TeamIds AND ClubId IS NULL",
new
{
auditableClub.ClubId,
TeamIds = auditableClub.Teams.Select(x => x.TeamId)
},
transaction).ConfigureAwait(false);
await connection.ExecuteAsync($"UPDATE {Tables.Team} SET ClubId = NULL, ClubMark = 0 WHERE ClubId = @ClubId AND TeamId NOT IN @TeamIds", new { auditableClub.ClubId, TeamIds = auditableClub.Teams.Select(x => x.TeamId) }, transaction).ConfigureAwait(false);
if (club.ClubRoute != auditableClub.ClubRoute)
{
await _redirectsRepository.InsertRedirect(club.ClubRoute, auditableClub.ClubRoute, null, transaction).ConfigureAwait(false);
}
var serialisedClub = JsonConvert.SerializeObject(auditableClub);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Update,
MemberKey = memberKey,
ActorName = memberName,
EntityUri = auditableClub.EntityUri,
State = serialisedClub,
RedactedState = serialisedClub,
AuditDate = DateTime.UtcNow
}, transaction).ConfigureAwait(false);
transaction.Commit();
_logger.Info(GetType(), LoggingTemplates.Updated, auditableClub, memberName, memberKey, GetType(), nameof(SqlServerClubRepository.UpdateClub));
}
}
return(auditableClub);
}
