/// <summary>
/// Creates a stoolball team and populates the <see cref="Team.TeamId"/>
/// </summary>
/// <returns>The created team</returns>
public async Task <Team> CreateTeam(Team team, Guid memberKey, string memberUsername, string memberName)
{
if (team is null)
{
throw new ArgumentNullException(nameof(team));
}
if (string.IsNullOrWhiteSpace(memberName))
{
throw new ArgumentNullException(nameof(memberName));
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var auditableTeam = await CreateTeam(team, transaction, memberUsername).ConfigureAwait(false);
var redacted = _copier.CreateRedactedCopy(auditableTeam);
await _auditRepository.CreateAudit(new AuditRecord
{
Action = AuditAction.Create,
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.Created, redacted, memberName, memberKey, GetType(), nameof(SqlServerTeamRepository.CreateTeam));
return(auditableTeam);
}
}
}
/// <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 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);
}
