public async Task <None> Execute(
SqlTransaction transaction,
DeleteTLevelsForProviderWithTLevelDefinitions query)
{
var sql = @"
DECLARE @TLevelIds Pttcd.GuidIdTable
UPDATE Pttcd.TLevels SET
TLevelStatus = @DeletedTLevelStatus,
DeletedOn = @DeletedOn,
DeletedByUserId = @DeletedByUserId
OUTPUT INSERTED.TLevelId INTO @TLevelIds
FROM Pttcd.TLevels t
JOIN @TLevelDefinitionIds x ON t.TLevelDefinitionId = x.Id
WHERE t.ProviderId = @ProviderId
AND t.TLevelStatus = @LiveTLevelStatus
EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @DeletedOn";
await transaction.Connection.ExecuteAsync(
sql,
new
{
query.ProviderId,
query.DeletedOn,
DeletedByUserId = query.DeletedBy.UserId,
TLevelDefinitionIds = TvpHelper.CreateGuidIdTable(query.TLevelDefinitionIds),
LiveTLevelStatus = TLevelStatus.Live,
DeletedTLevelStatus = TLevelLocationStatus.Deleted
},
transaction : transaction);
return(new None());
}
public async Task <OneOf <CreateTLevelFailedReason, Success> > Execute(
SqlTransaction transaction,
CreateTLevel query)
{
var sql = $@"
INSERT INTO Pttcd.TLevels (
TLevelId, TLevelStatus, CreatedOn, CreatedByUserId, UpdatedOn, UpdatedByUserId, ProviderId,
TLevelDefinitionId, WhoFor, EntryRequirements, WhatYoullLearn, HowYoullLearn, HowYoullBeAssessed,
WhatYouCanDoNext, YourReference, StartDate, Website
) VALUES (
@TLevelId, @TLevelStatus, @CreatedOn, @CreatedByUserId, @UpdatedOn, @UpdatedByUserId, @ProviderId,
@TLevelDefinitionId, @WhoFor, @EntryRequirements, @WhatYoullLearn, @HowYoullLearn, @HowYoullBeAssessed,
@WhatYouCanDoNext, @YourReference, @StartDate, @Website
)
INSERT INTO Pttcd.TLevelLocations (TLevelLocationId, TLevelId, VenueId, TLevelLocationStatus)
SELECT NEWID(), @TLevelId, Id, @TLevelLocationStatus
FROM @LocationVenueIds
DECLARE @TLevelIds Pttcd.GuidIdTable
INSERT INTO @TLevelIds VALUES (@TLevelId)
EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @CreatedOn";
try
{
await transaction.Connection.ExecuteAsync(
sql,
param : new
{
query.TLevelId,
TLevelStatus = TLevelStatus.Live,
CreatedOn = query.CreatedOn,
CreatedByUserId = query.CreatedBy.UserId,
UpdatedOn = query.CreatedOn,
UpdatedByUserId = query.CreatedBy.UserId,
query.ProviderId,
query.TLevelDefinitionId,
query.WhoFor,
query.EntryRequirements,
query.WhatYoullLearn,
query.HowYoullLearn,
query.HowYoullBeAssessed,
query.WhatYouCanDoNext,
query.YourReference,
query.StartDate,
query.Website,
LocationVenueIds = TvpHelper.CreateGuidIdTable(query.LocationVenueIds),
TLevelLocationStatus = TLevelLocationStatus.Live
},
transaction : transaction);
}
catch (SqlException ex) when(ex.Number == 2601 && ex.Message.Contains("'IX_TLevels_DefinitionProviderStartDate'"))
{
return(CreateTLevelFailedReason.TLevelAlreadyExistsForDate);
}
return(new Success());
}
public async Task <Success> Execute(SqlTransaction transaction, UpdateFindACourseIndexForVenues query)
{
var sql = $@"
UPDATE Pttcd.FindACourseIndex SET
LastSynced = @Now,
VenueName = v.VenueName,
VenueAddress = STUFF(
CONCAT(
NULLIF(', ' + v.AddressLine1, ', '),
NULLIF(', ' + v.AddressLine2, ', '),
NULLIF(', ' + v.Town, ', '),
NULLIF(', ' + v.County, ', '),
NULLIF(', ' + v.Postcode, ', ')),
1, 2, NULL),
VenueTown = v.Town
FROM Pttcd.FindACourseIndex i
JOIN Pttcd.Venues v ON i.VenueId = v.VenueId
JOIN @VenueIds x ON v.VenueId = x.Id
WHERE Live = 1";
var paramz = new
{
VenueIds = TvpHelper.CreateGuidIdTable(query.VenueIds),
query.Now
};
await transaction.Connection.ExecuteAsync(sql, paramz, transaction);
return(new Success());
}
public async Task <IReadOnlyDictionary <Guid, Provider> > Execute(SqlTransaction transaction, GetProvidersByIds query)
{
var sqlParameters = new
{
ProviderIds = TvpHelper.CreateGuidIdTable(query.ProviderIds)
};
var sql = @$ "
SELECT {nameof(Provider.ProviderId)},
public async Task AddAttendees(BulkAttendeeRequest request)
{
ParameterDictionary parameters = ParameterDictionary.Single("invites", TvpHelper.MapInvites(request.InviteRequests));
parameters.AddParameter("userId", request.UserId);
await ExecuteStoredProc("[dbo].[spCreateInvites]", parameters)
.ConfigureAwait(continueOnCapturedContext: false);
}
public static IBindingParameterContract GetParameterContract(RelationIdentity relation)
{
if (relation == null)
{
throw new ArgumentNullException(nameof(relation));
}
return(Resolvers.GetOrAdd(relation, _ =>
{
IBindingMetadata[] bindings = relation.Heading.Select(m => m.GetMetadata <IBindingMetadata>()).ToArray();
return TvpHelper.GetParameterContract(bindings);
}));
}
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 <Success> Execute(SqlTransaction transaction, UpdateFindACourseIndexForCourseRuns query)
{
var paramz = new
{
CourseRunIds = TvpHelper.CreateGuidIdTable(query.CourseRunIds),
Now = query.Now
};
await transaction.Connection.ExecuteAsync(
"Pttcd.RefreshFindACourseIndex",
paramz,
transaction,
commandType : System.Data.CommandType.StoredProcedure);
return(new Success());
}
public async Task <IReadOnlyDictionary <string, LearningDelivery> > Execute(SqlTransaction transaction, GetLearningDeliveries query)
{
var sql = @"
SELECT ld.LearnAimRef, ld.LearnAimRefTitle, ld.EffectiveTo, ld.NotionalNVQLevelv2, ld.AwardOrgCode,
lart.LearnAimRefTypeDesc, ld.OperationalEndDate
FROM LARS.LearningDelivery ld
JOIN @LearningAimRefs x ON ld.LearnAimRef = x.Value
JOIN LARS.LearnAimRefType lart ON ld.LearnAimRefType = lart.LearnAimRefType";
var paramz = new
{
LearningAimRefs = TvpHelper.CreateUnicodeStringTable(query.LearnAimRefs)
};
return((await transaction.Connection.QueryAsync <LearningDelivery>(sql, paramz, transaction)).ToDictionary(r => r.LearnAimRef, r => r));
}
public async Task <IReadOnlyDictionary <Guid, Venue> > Execute(
SqlTransaction transaction,
GetVenuesByIds query)
{
var sql = $@"
SELECT v.VenueId, v.ProviderId, v.ProviderUkprn, v.VenueName, v.ProviderVenueRef, v.AddressLine1, v.AddressLine2, v.Town, v.County, v.Postcode,
v.Telephone, v.Email, v.Website, v.Position.Lat Latitude, v.Position.Long Longitude
FROM Pttcd.Venues v
JOIN @VenueIds x ON v.VenueId = x.Id
WHERE v.VenueStatus = {(int)VenueStatus.Live}";
var param = new
{
VenueIds = TvpHelper.CreateGuidIdTable(query.VenueIds)
};
return((await transaction.Connection.QueryAsync <Venue>(sql, param, transaction))
.ToDictionary(v => v.VenueId, v => v));
}
/// <summary>
/// Appends a table-valued parameter from the current values, e.g. <c>@TP0</c>, to the projection buffer.
/// </summary>
/// <param name="projection">The current projection.</param>
/// <returns>A new attribute containing the appended buffer.</returns>
public static IProjectionAttribute TvpName(this IProjection projection)
{
if (projection.Source == null)
{
throw ProjectionException.ValueNotFound(projection);
}
else if (!projection.Attributes.Any())
{
throw ProjectionException.FromProjection(projection, "No attributes found.");
}
IProjectionMetadata metadata = TvpHelper.GetPreferredTvpMetadata(projection);
IField source = new Relation(projection.Source, metadata.Identity.Name).Scalar();
RelationIdentity identity = new RelationIdentity(metadata.Identity.Schema, projection.Attributes.Select(a => a.Metadata.Identity));
IBindingParameterContract contract = TvpCache.GetParameterContract(identity);
string paramName = projection.Context.Lookup.Custom("TP", projection.Identity, field: source);
string dialectName = projection.Context.Domain.Dialect.Parameter(paramName);
return(projection.Attr().Append(dialectName).Append(new Parameter(paramName, source, contract)));
}
public async Task <IReadOnlyDictionary <Guid, Provider> > Execute(SqlTransaction transaction, GetProvidersByIds query)
{
var sqlParameters = new
{
ProviderIds = TvpHelper.CreateGuidIdTable(query.ProviderIds)
};
var sql = @$ "
SELECT ProviderId,
Ukprn,
ProviderName,
ProviderType,
Alias,
DisplayNameSource,
ApprenticeshipQAStatus,
BulkUploadInProgress,
BulkUploadPublishInProgress,
BulkUploadStartedDateTime,
BulkUploadTotalRowCount,
LearnerSatisfaction,
EmployerSatisfaction
FROM Pttcd.Providers
WHERE {nameof(Provider.ProviderId)} IN (SELECT * FROM @{nameof(sqlParameters.ProviderIds)})";
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 <None> Execute(SqlTransaction transaction, UpsertCoursesFromCosmos query)
{
await UpsertCourses();
await UpsertCourseRuns();
await UpsertCourseRunRegions();
await UpsertCourseRunSubRegions();
await UpdateFindACourseIndex();
async Task UpsertCourses()
{
var createTableSql = @"
CREATE TABLE #Courses (
CourseId UNIQUEIDENTIFIER,
CourseStatus INT,
CreatedOn DATETIME,
CreatedBy NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
TribalCourseId INT,
LearnAimRef VARCHAR(50),
ProviderUkprn INT,
CourseDescription NVARCHAR(MAX),
EntryRequirements NVARCHAR(MAX),
WhatYoullLearn NVARCHAR(MAX),
HowYoullLearn NVARCHAR(MAX),
WhatYoullNeed NVARCHAR(MAX),
HowYoullBeAssessed NVARCHAR(MAX),
WhereNext NVARCHAR(MAX),
BulkUploadErrorCount INT
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.Select(r => new
{
r.CourseId,
r.CourseStatus,
r.CreatedOn,
r.CreatedBy,
r.UpdatedOn,
r.UpdatedBy,
r.TribalCourseId,
r.LearnAimRef,
r.ProviderUkprn,
r.CourseDescription,
r.EntryRequirements,
r.WhatYoullLearn,
r.HowYoullLearn,
r.WhatYoullNeed,
r.HowYoullBeAssessed,
r.WhereNext,
r.BulkUploadErrorCount
}),
tableName : "#Courses",
transaction);
var mergeSql = @"
MERGE Pttcd.Courses AS target
USING (
SELECT
CourseId,
CourseStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
TribalCourseId,
LearnAimRef,
ProviderUkprn,
CourseDescription,
EntryRequirements,
WhatYoullLearn,
HowYoullLearn,
WhatYoullNeed,
HowYoullBeAssessed,
WhereNext,
BulkUploadErrorCount
FROM #Courses
) AS source
ON target.CourseId = source.CourseId
WHEN NOT MATCHED THEN
INSERT (
CourseId,
LastSyncedFromCosmos,
CourseStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
TribalCourseId,
LearnAimRef,
ProviderUkprn,
CourseDescription,
EntryRequirements,
WhatYoullLearn,
HowYoullLearn,
WhatYoullNeed,
HowYoullBeAssessed,
WhereNext,
BulkUploadErrorCount
) VALUES (
source.CourseId,
@LastSyncedFromCosmos,
source.CourseStatus,
source.CreatedOn,
source.CreatedBy,
source.UpdatedOn,
source.UpdatedBy,
source.TribalCourseId,
source.LearnAimRef,
source.ProviderUkprn,
source.CourseDescription,
source.EntryRequirements,
source.WhatYoullLearn,
source.HowYoullLearn,
source.WhatYoullNeed,
source.HowYoullBeAssessed,
source.WhereNext,
source.BulkUploadErrorCount
)
WHEN MATCHED THEN
UPDATE SET
CourseStatus = source.CourseStatus,
LastSyncedFromCosmos = @LastSyncedFromCosmos,
CreatedOn = source.CreatedOn,
CreatedBy = source.CreatedBy,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
TribalCourseId = source.TribalCourseId,
LearnAimRef = source.LearnAimRef,
ProviderUkprn = source.ProviderUkprn,
CourseDescription = source.CourseDescription,
EntryRequirements = source.EntryRequirements,
WhatYoullLearn = source.WhatYoullLearn,
HowYoullLearn = source.HowYoullLearn,
WhatYoullNeed = source.WhatYoullNeed,
HowYoullBeAssessed = source.HowYoullBeAssessed,
WhereNext = source.WhereNext,
BulkUploadErrorCount = source.BulkUploadErrorCount;";
await transaction.Connection.ExecuteAsync(
mergeSql,
param : new { query.LastSyncedFromCosmos },
transaction : transaction);
}
async Task UpsertCourseRuns()
{
var createTableSql = @"
CREATE TABLE #CourseRuns (
CourseRunId UNIQUEIDENTIFIER,
CourseId UNIQUEIDENTIFIER,
CourseRunStatus INT,
CreatedOn DATETIME,
CreatedBy NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
CourseName NVARCHAR(MAX),
VenueId UNIQUEIDENTIFIER,
ProviderCourseId NVARCHAR(MAX),
DeliveryMode TINYINT,
FlexibleStartDate BIT,
StartDate DATE,
CourseWebsite NVARCHAR(MAX),
Cost INT,
CostDescription NVARCHAR(MAX),
DurationUnit TINYINT,
DurationValue INT,
StudyMode TINYINT,
AttendancePattern TINYINT,
[National] BIT,
BulkUploadErrorCount INT
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.SelectMany(c => c.CourseRuns.Select(cr => new
{
cr.CourseRunId,
c.CourseId,
cr.CourseRunStatus,
cr.CreatedOn,
cr.CreatedBy,
cr.UpdatedOn,
cr.UpdatedBy,
cr.CourseName,
cr.VenueId,
cr.ProviderCourseId,
DeliveryMode = (byte)cr.DeliveryMode,
cr.FlexibleStartDate,
cr.StartDate,
cr.CourseWebsite,
Cost = cr.Cost.HasValue ? (int?)(cr.Cost * 100) : null, // SqlBulkCopy truncates decimals - convert to int
cr.CostDescription,
DurationUnit = (byte)cr.DurationUnit,
cr.DurationValue,
cr.StudyMode,
AttendancePattern = (byte)cr.AttendancePattern,
cr.National,
cr.BulkUploadErrorCount
})),
tableName : "#CourseRuns",
transaction);
var mergeSql = @"
MERGE Pttcd.CourseRuns AS target
USING (
SELECT
CourseRunId,
CourseId,
CourseRunStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
CourseName,
VenueId,
ProviderCourseId,
DeliveryMode,
FlexibleStartDate,
StartDate,
CourseWebsite,
Cost,
CostDescription,
DurationUnit,
DurationValue,
StudyMode,
AttendancePattern,
[National],
BulkUploadErrorCount
FROM #CourseRuns
) AS source
ON target.CourseRunId = source.CourseRunId
WHEN NOT MATCHED THEN
INSERT (
CourseRunId,
CourseId,
CourseRunStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
CourseName,
VenueId,
ProviderCourseId,
DeliveryMode,
FlexibleStartDate,
StartDate,
CourseWebsite,
Cost,
CostDescription,
DurationUnit,
DurationValue,
StudyMode,
AttendancePattern,
[National],
BulkUploadErrorCount
) VALUES (
source.CourseRunId,
source.CourseId,
source.CourseRunStatus,
source.CreatedOn,
source.CreatedBy,
source.UpdatedOn,
source.UpdatedBy,
source.CourseName,
source.VenueId,
source.ProviderCourseId,
source.DeliveryMode,
source.FlexibleStartDate,
source.StartDate,
source.CourseWebsite,
CONVERT(DECIMAL, source.Cost) / 100,
source.CostDescription,
source.DurationUnit,
source.DurationValue,
source.StudyMode,
source.AttendancePattern,
source.[National],
source.BulkUploadErrorCount
)
WHEN MATCHED THEN
UPDATE SET
CourseId = source.CourseId,
CourseRunStatus = source.CourseRunStatus,
CreatedOn = source.CreatedOn,
CreatedBy = source.CreatedBy,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
CourseName = source.CourseName,
VenueId = source.VenueId,
ProviderCourseId = source.ProviderCourseId,
DeliveryMode = source.DeliveryMode,
FlexibleStartDate = source.FlexibleStartDate,
StartDate = source.StartDate,
CourseWebsite = source.CourseWebsite,
Cost = CONVERT(DECIMAL, source.Cost) / 100,
CostDescription = source.CostDescription,
DurationUnit = source.DurationUnit,
DurationValue = source.DurationValue,
StudyMode = source.StudyMode,
AttendancePattern = source.AttendancePattern,
[National] = source.[National],
BulkUploadErrorCount = source.BulkUploadErrorCount
WHEN NOT MATCHED BY SOURCE AND target.CourseId IN (SELECT CourseId FROM #Courses) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction);
}
async Task UpsertCourseRunRegions()
{
var createTableSql = @"
CREATE TABLE #CourseRunRegions (
CourseRunId UNIQUEIDENTIFIER,
RegionId VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.SelectMany(c => c.CourseRuns.SelectMany(cr => cr.RegionIds.Select(r => new
{
cr.CourseRunId,
RegionId = r
}))),
tableName : "#CourseRunRegions",
transaction);
var mergeSql = @"
MERGE Pttcd.CourseRunRegions AS target
USING (
SELECT CourseRunId, RegionId FROM #CourseRunRegions
) AS source
ON target.CourseRunId = source.CourseRunId AND target.RegionId = source.RegionId
WHEN NOT MATCHED THEN
INSERT (CourseRunId, RegionId) VALUES (source.CourseRunId, source.RegionId)
WHEN NOT MATCHED BY SOURCE AND target.CourseRunId IN (SELECT CourseRunId FROM #CourseRuns) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction);
}
async Task UpsertCourseRunSubRegions()
{
var createTableSql = @"
CREATE TABLE #CourseRunSubRegions (
CourseRunId UNIQUEIDENTIFIER,
RegionId VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.SelectMany(c => c.CourseRuns.SelectMany(cr => cr.SubRegionIds.Select(r => new
{
cr.CourseRunId,
RegionId = r
}))),
tableName : "#CourseRunSubRegions",
transaction);
var mergeSql = @"
MERGE Pttcd.CourseRunSubRegions AS target
USING (
SELECT CourseRunId, RegionId FROM #CourseRunSubRegions
) AS source
ON target.CourseRunId = source.CourseRunId AND target.RegionId = source.RegionId
WHEN NOT MATCHED THEN
INSERT (CourseRunId, RegionId) VALUES (source.CourseRunId, source.RegionId)
WHEN NOT MATCHED BY SOURCE AND target.CourseRunId IN (SELECT CourseRunId FROM #CourseRuns) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction);
}
Task UpdateFindACourseIndex()
{
var courseRunIds = query.Records.SelectMany(c => c.CourseRuns).Select(cr => cr.CourseRunId);
return(transaction.Connection.ExecuteAsync(
"Pttcd.RefreshFindACourseIndex",
param: new
{
CourseRunIds = TvpHelper.CreateGuidIdTable(courseRunIds),
Now = query.LastSyncedFromCosmos
},
transaction: transaction,
commandType: System.Data.CommandType.StoredProcedure));
}
return(new None());
}
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());
}
}
public async Task <OneOf <NotFound, UpdateTLevelFailedReason, Success> > Execute(
SqlTransaction transaction,
UpdateTLevel query)
{
var sql = $@"
DECLARE @Result INT
UPDATE Pttcd.TLevels SET
UpdatedOn = @UpdatedOn,
UpdatedByUserId = @UpdatedByUserId,
WhoFor = @WhoFor,
EntryRequirements = @EntryRequirements,
WhatYoullLearn = @WhatYoullLearn,
HowYoullLearn = @HowYoullLearn,
HowYoullBeAssessed = @HowYoullBeAssessed,
WhatYouCanDoNext = @WhatYouCanDoNext,
YourReference = @YourReference,
StartDate = @StartDate,
Website = @Website
WHERE TLevelId = @TLevelId
AND TLevelStatus = @LiveTLevelStatus
SELECT @Result = @@ROWCOUNT
MERGE Pttcd.TLevelLocations AS target
USING (SELECT Id VenueId FROM @LocationVenueIds) AS source
ON target.VenueId = source.VenueId AND target.TLevelId = @TLevelId AND target.TLevelLocationStatus = @LiveTLevelLocationStatus
WHEN NOT MATCHED THEN
INSERT (TLevelLocationId, TLevelLocationStatus, TLevelId, VenueId) VALUES (NEWID(), @LiveTLevelLocationStatus, @TLevelId, source.VenueId)
WHEN NOT MATCHED BY SOURCE AND target.TLevelId = @TLevelId THEN
UPDATE SET TLevelLocationStatus = @DeletedTLevelLocationStatus;
DECLARE @TLevelIds Pttcd.GuidIdTable
INSERT INTO @TLevelIds VALUES (@TLevelId)
EXEC Pttcd.RefreshFindACourseIndexForTLevels @TLevelIds, @UpdatedOn
SELECT @Result";
try
{
var result = await transaction.Connection.QuerySingleAsync <Result>(
sql,
param : new
{
query.TLevelId,
query.UpdatedOn,
UpdatedByUserId = query.UpdatedBy.UserId,
query.WhoFor,
query.EntryRequirements,
query.WhatYoullLearn,
query.HowYoullLearn,
query.HowYoullBeAssessed,
query.WhatYouCanDoNext,
query.YourReference,
query.StartDate,
query.Website,
LocationVenueIds = TvpHelper.CreateGuidIdTable(query.LocationVenueIds),
LiveTLevelStatus = TLevelStatus.Live,
LiveTLevelLocationStatus = TLevelLocationStatus.Live,
DeletedTLevelLocationStatus = TLevelLocationStatus.Deleted
},
transaction : transaction);
if (result == Result.Ok)
{
return(new Success());
}
else
{
return(new NotFound());
}
}
catch (SqlException ex) when(ex.Number == 2601 && ex.Message.Contains("'IX_TLevels_DefinitionProviderStartDate'"))
{
return(UpdateTLevelFailedReason.TLevelAlreadyExistsForDate);
}
}
