public async Task <IDictionary <string, PostcodeInfo> > Execute(SqlTransaction transaction, GetPostcodeInfos query)
{
var sql = @"
SELECT pc.Postcode, pc.Position.Lat Latitude, pc.Position.Long Longitude, pc.InEngland
FROM Pttcd.Postcodes pc
JOIN @Postcodes x ON pc.Postcode = x.Value";
var paramz = new
{
Postcodes = TvpHelper.CreateStringTable(query.Postcodes)
};
return((await transaction.Connection.QueryAsync <PostcodeInfo>(sql, paramz, transaction: transaction))
.ToDictionary(p => p.Postcode, p => p));
}
public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, CreateCourseRun query)
{
var sql = $@"
IF NOT EXISTS (SELECT 1 FROM Pttcd.Courses WHERE CourseId = @CourseId)
BEGIN
SELECT 1 AS Result
RETURN
END
INSERT INTO Pttcd.CourseRuns (
CourseRunId,
CourseId,
CourseRunStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
CourseName,
VenueId,
ProviderCourseId,
DeliveryMode,
FlexibleStartDate,
StartDate,
CourseWebsite,
Cost,
CostDescription,
DurationUnit,
DurationValue,
StudyMode,
AttendancePattern,
[National]
)
VALUES (
@CourseRunId,
@CourseId,
{(int)CourseStatus.Live},
@CreatedOn,
@CreatedByUserId,
@CreatedOn,
@CreatedByUserId,
@CourseName,
@VenueId,
@ProviderCourseId,
@DeliveryMode,
@FlexibleStartDate,
@StartDate,
@CourseWebsite,
@Cost,
@CostDescription,
@DurationUnit,
@DurationValue,
@StudyMode,
@AttendancePattern,
@National
)
INSERT INTO Pttcd.CourseRunSubRegions (CourseRunId, RegionId)
SELECT @CourseRunId, Value FROM @SubRegionIds
SELECT 0 AS Result
DECLARE @CourseRunIds Pttcd.GuidIdTable
INSERT INTO @CourseRunIds VALUES (@CourseRunId)
EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @CreatedOn";
var paramz = new
{
query.CourseId,
query.CourseRunId,
query.CourseName,
query.DeliveryMode,
query.FlexibleStartDate,
query.StartDate,
CourseWebsite = query.CourseUrl,
query.Cost,
query.CostDescription,
query.DurationUnit,
query.DurationValue,
query.ProviderCourseId,
query.National,
SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Enumerable.Empty <string>()),
query.VenueId,
query.AttendancePattern,
query.StudyMode,
CreatedByUserId = query.CreatedBy.UserId,
query.CreatedOn,
};
var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction);
if (result == Result.CourseNotFound)
{
return(new NotFound());
}
else
{
return(new Success());
}
}
public async Task <OneOf <NotFound, Success> > Execute(SqlTransaction transaction, UpdateCourseRun query)
{
var sql = $@"
UPDATE Pttcd.CourseRuns SET
CourseName = @CourseName,
DeliveryMode = @DeliveryMode,
FlexibleStartDate = @FlexibleStartDate,
StartDate = @StartDate,
CourseWebsite = @CourseUrl,
Cost = @Cost,
CostDescription = @CostDescription,
DurationUnit = @DurationUnit,
DurationValue = @DurationValue,
ProviderCourseId = @ProviderCourseId,
[National] = @National,
VenueId = @VenueId,
AttendancePattern = @AttendancePattern,
StudyMode = @StudyMode,
UpdatedBy = @UpdatedByUserId,
UpdatedOn = @UpdatedOn,
DataIsHtmlEncoded = 0
WHERE CourseRunId = @CourseRunId
AND CourseRunStatus = {(int)CourseStatus.Live}
IF @@ROWCOUNT = 0
BEGIN
SELECT 1 AS Result
RETURN
END
MERGE Pttcd.CourseRunSubRegions AS target
USING (SELECT Value FROM @SubRegionIds) AS source
ON target.CourseRunId = @CourseRunId AND target.RegionId = source.Value
WHEN NOT MATCHED THEN INSERT (CourseRunId, RegionId) VALUES (@CourseRunId, source.Value)
WHEN NOT MATCHED BY SOURCE AND target.CourseRunId = @CourseRunId THEN DELETE;
DECLARE @CourseRunIds Pttcd.GuidIdTable
INSERT INTO @CourseRunIds VALUES (@CourseRunId)
EXEC Pttcd.RefreshFindACourseIndex @CourseRunIds = @CourseRunIds, @Now = @UpdatedOn
SELECT 0 AS Result";
var paramz = new
{
query.CourseRunId,
query.CourseName,
query.DeliveryMode,
query.FlexibleStartDate,
query.StartDate,
query.CourseUrl,
query.Cost,
query.CostDescription,
query.DurationUnit,
query.DurationValue,
query.ProviderCourseId,
query.National,
SubRegionIds = TvpHelper.CreateStringTable(query.SubRegionIds ?? Array.Empty <string>()),
query.VenueId,
query.AttendancePattern,
query.StudyMode,
UpdatedByUserId = query.UpdatedBy.UserId,
query.UpdatedOn
};
var result = await transaction.Connection.QuerySingleAsync <Result>(sql, paramz, transaction);
if (result == Result.Success)
{
return(new Success());
}
else
{
return(new NotFound());
}
}