new ProcessAllCourses()
{
ProcessChunk = async chunk =>
{
var withSubRegions = chunk
.Where(c => c.CourseRuns
.Any(cr => (cr.SubRegions?.Count() ?? 0) != 0))
.ToArray();
if (withSubRegions.Length > 0)
{
using var scope = _serviceScopeFactory.CreateScope();
var sqlQueryDispatcher = scope.ServiceProvider.GetRequiredService <ISqlQueryDispatcher>();
var transaction = sqlQueryDispatcher.Transaction;
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(
withSubRegions.SelectMany(c => c.CourseRuns.SelectMany(cr => (cr.SubRegions ?? Array.Empty <CourseRunSubRegion>()).Select(r => new
{
CourseRunId = cr.Id,
RegionId = r.Id
}))),
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);";
await transaction.Connection.ExecuteAsync(mergeSql, transaction: transaction);
await transaction.CommitAsync();
}
}
});
new ProcessAllFeChoices()
{
ProcessChunk = async chunk =>
{
var feChoices = chunk.ToArray();
using var scope = _serviceScopeFactory.CreateScope();
var sqlQueryDispatcher = scope.ServiceProvider.GetRequiredService <ISqlQueryDispatcher>();
var transaction = sqlQueryDispatcher.Transaction;
var createTableSql = @"
CREATE TABLE #FeChoices (
Ukprn int NOT NULL,
LearnerSatisfaction DECIMAL(18,1) NULL,
EmployerSatisfaction DECIMAL(18,1) NULL
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction: transaction);
await BulkCopyHelper.WriteRecords(
feChoices.Select(r => new
{
Ukprn = r.UKPRN,
r.LearnerSatisfaction,
r.EmployerSatisfaction,
}),
tableName: "#FeChoices",
transaction);
var mergeSql = @"
MERGE Pttcd.Providers AS target
USING (
SELECT Ukprn, LearnerSatisfaction, EmployerSatisfaction FROM #FeChoices
) AS source
ON target.Ukprn = source.Ukprn
WHEN MATCHED THEN
UPDATE SET LearnerSatisfaction=source.LearnerSatisfaction, EmployerSatisfaction=source.EmployerSatisfaction;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction: transaction);
await transaction.CommitAsync();
}
});
public async Task <None> Execute(SqlTransaction transaction, UpsertVenues query)
{
var createTableSql = @"
CREATE TABLE #Venues (
VenueId UNIQUEIDENTIFIER,
VenueStatus TINYINT,
CreatedOn DATETIME,
CreatedBy NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
VenueName NVARCHAR(MAX),
ProviderUkprn INT,
TribalProviderId INT,
TribalVenueId INT,
ProviderVenueRef NVARCHAR(MAX),
AddressLine1 NVARCHAR(MAX),
AddressLine2 NVARCHAR(MAX),
Town NVARCHAR(MAX),
County NVARCHAR(MAX),
Postcode NVARCHAR(MAX),
Latitude FLOAT,
Longitude FLOAT,
Telephone NVARCHAR(MAX),
Email NVARCHAR(MAX),
Website NVARCHAR(MAX)
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.Select(r => new
{
r.VenueId,
r.VenueStatus,
r.CreatedOn,
r.CreatedBy,
r.UpdatedOn,
r.UpdatedBy,
r.VenueName,
r.ProviderUkprn,
r.TribalVenueId,
r.ProviderVenueRef,
r.AddressLine1,
r.AddressLine2,
r.Town,
r.County,
r.Postcode,
r.Position.Latitude,
r.Position.Longitude,
r.Telephone,
r.Email,
r.Website
}),
tableName : "#Venues",
transaction);
var sql = @"
MERGE Pttcd.Venues AS target
USING (
SELECT
VenueId,
VenueStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
VenueName,
ProviderUkprn,
TribalVenueId,
ProviderVenueRef,
AddressLine1,
AddressLine2,
Town,
County,
Postcode,
geography::Point(Latitude, Longitude, 4326) Position,
Telephone,
Email,
Website
FROM #Venues
) AS source
ON target.VenueId = source.VenueId
WHEN NOT MATCHED THEN
INSERT (
VenueId,
VenueStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
VenueName,
ProviderUkprn,
TribalVenueId,
ProviderVenueRef,
AddressLine1,
AddressLine2,
Town,
County,
Postcode,
Position,
Telephone,
Email,
Website
) VALUES (
source.VenueId,
source.VenueStatus,
source.CreatedOn,
source.CreatedBy,
source.UpdatedOn,
source.UpdatedBy,
source.VenueName,
source.ProviderUkprn,
source.TribalVenueId,
source.ProviderVenueRef,
source.AddressLine1,
source.AddressLine2,
source.Town,
source.County,
source.Postcode,
source.Position,
source.Telephone,
source.Email,
source.Website
)
WHEN MATCHED THEN
UPDATE SET
VenueStatus = source.VenueStatus,
CreatedOn = source.CreatedOn,
CreatedBy = source.CreatedBy,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
VenueName = source.VenueName,
ProviderUkprn = source.ProviderUkprn,
TribalVenueId = source.TribalVenueId,
ProviderVenueRef = source.ProviderVenueRef,
AddressLine1 = source.AddressLine1,
AddressLine2 = source.AddressLine2,
Town = source.Town,
County = source.County,
Postcode = source.Postcode,
Position = source.Position,
Telephone = source.Telephone,
Email = source.Email,
Website = source.Website;";
await transaction.Connection.ExecuteAsync(sql, transaction : transaction);
return(new None());
}
public async Task<None> Execute(SqlTransaction transaction, UpsertProvidersFromCosmos query)
{
await UpsertProvider();
await UpsertProviderContacts();
async Task UpsertProvider()
{
var createTableSql = @"
CREATE TABLE #Providers (
ProviderId UNIQUEIDENTIFIER,
Ukprn INT,
ProviderStatus TINYINT,
ProviderType TINYINT,
ProviderName NVARCHAR(MAX),
UkrlpProviderStatusDescription NVARCHAR(MAX),
MarketingInformation NVARCHAR(MAX),
CourseDirectoryName NVARCHAR(MAX),
TradingName NVARCHAR(MAX),
Alias NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
NationalApprenticeshipProvider BIT,
TribalProviderId INT,
BulkUploadInProgress BIT NULL,
BulkUploadPublishInProgress BIT NULL,
BulkUploadStartedDateTime DATETIME2 (7) NULL,
BulkUploadTotalRowCount INT NULL
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction: transaction);
await BulkCopyHelper.WriteRecords(
query.Records.Select(r => new
{
r.ProviderId,
r.Ukprn,
ProviderStatus = (byte)r.ProviderStatus,
ProviderType = (byte)r.ProviderType,
r.ProviderName,
r.UkrlpProviderStatusDescription,
r.MarketingInformation,
r.CourseDirectoryName,
r.TradingName,
r.Alias,
r.UpdatedOn,
r.UpdatedBy,
r.NationalApprenticeshipProvider,
r.TribalProviderId,
r.BulkUploadInProgress,
r.BulkUploadPublishInProgress,
r.BulkUploadStartedDateTime,
r.BulkUploadTotalRowCount
}),
tableName: "#Providers",
transaction);
var sql = @"
MERGE Pttcd.Providers AS target
USING (SELECT * FROM #Providers) AS source
ON target.ProviderId = source.ProviderId
WHEN NOT MATCHED THEN
INSERT (
ProviderId,
LastSyncedFromCosmos,
Ukprn,
ProviderStatus,
ProviderType,
ProviderName,
UkrlpProviderStatusDescription,
MarketingInformation,
CourseDirectoryName,
TradingName,
Alias,
UpdatedOn,
UpdatedBy,
NationalApprenticeshipProvider,
TribalProviderId,
BulkUploadInProgress,
BulkUploadPublishInProgress,
BulkUploadStartedDateTime,
BulkUploadTotalRowCount
) VALUES (
source.ProviderId,
@LastSyncedFromCosmos,
source.Ukprn,
source.ProviderStatus,
source.ProviderType,
source.ProviderName,
source.UkrlpProviderStatusDescription,
source.MarketingInformation,
source.CourseDirectoryName,
source.TradingName,
source.Alias,
source.UpdatedOn,
source.UpdatedBy,
source.NationalApprenticeshipProvider,
source.TribalProviderId,
source.BulkUploadInProgress,
source.BulkUploadPublishInProgress,
source.BulkUploadStartedDateTime,
source.BulkUploadTotalRowCount
)
WHEN MATCHED THEN
UPDATE SET
Ukprn = source.Ukprn,
LastSyncedFromCosmos = @LastSyncedFromCosmos,
ProviderStatus = source.ProviderStatus,
ProviderType = source.ProviderType,
ProviderName = source.ProviderName,
UkrlpProviderStatusDescription = source.UkrlpProviderStatusDescription,
MarketingInformation = source.MarketingInformation,
CourseDirectoryName = source.CourseDirectoryName,
TradingName = source.TradingName,
Alias = source.Alias,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
NationalApprenticeshipProvider = source.NationalApprenticeshipProvider,
TribalProviderId = source.TribalProviderId,
BulkUploadInProgress = source.BulkUploadInProgress,
BulkUploadPublishInProgress = source.BulkUploadPublishInProgress,
BulkUploadStartedDateTime = source.BulkUploadStartedDateTime,
BulkUploadTotalRowCount = source.BulkUploadTotalRowCount;";
await transaction.Connection.ExecuteAsync(
sql,
param: new { query.LastSyncedFromCosmos },
transaction: transaction);
}
async Task UpsertProviderContacts()
{
var createTableSql = @"
CREATE TABLE #ProviderContacts (
ProviderId UNIQUEIDENTIFIER,
ProviderContactIndex INT,
ContactType CHAR,
ContactRole NVARCHAR(MAX),
AddressSaonDescription NVARCHAR(MAX),
AddressPaonDescription NVARCHAR(MAX),
AddressStreetDescription NVARCHAR(MAX),
AddressLocality NVARCHAR(MAX),
AddressItems NVARCHAR(MAX),
AddressPostTown NVARCHAR(MAX),
AddressCounty NVARCHAR(MAX),
AddressPostcode NVARCHAR(MAX),
PersonalDetailsPersonNameTitle NVARCHAR(MAX),
PersonalDetailsPersonNameGivenName NVARCHAR(MAX),
PersonalDetailsPersonNameFamilyName NVARCHAR(MAX),
Telephone1 NVARCHAR(MAX),
Telephone2 NVARCHAR(MAX),
Fax NVARCHAR(MAX),
WebsiteAddress NVARCHAR(MAX),
Email NVARCHAR(MAX)
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction: transaction);
await BulkCopyHelper.WriteRecords(
query.Records
.SelectMany(p => p.Contacts.Select((c, i) => new
{
p.ProviderId,
ProviderContactIndex = i,
c.ContactType,
c.ContactRole,
c.AddressSaonDescription,
c.AddressPaonDescription,
c.AddressStreetDescription,
c.AddressLocality,
c.AddressItems,
c.AddressPostTown,
c.AddressCounty,
c.AddressPostcode,
c.PersonalDetailsPersonNameTitle,
c.PersonalDetailsPersonNameGivenName,
c.PersonalDetailsPersonNameFamilyName,
c.Telephone1,
c.Telephone2,
c.Fax,
c.WebsiteAddress,
c.Email
})),
tableName: "#ProviderContacts",
transaction);
var mergeSql = @"
MERGE Pttcd.ProviderContacts AS target
USING (SELECT * FROM #ProviderContacts) AS source
ON target.ProviderId = source.ProviderId AND target.ProviderContactIndex = source.ProviderContactIndex
WHEN NOT MATCHED THEN
INSERT (
ProviderId,
ProviderContactIndex,
ContactType,
ContactRole,
AddressSaonDescription,
AddressPaonDescription,
AddressStreetDescription,
AddressLocality,
AddressItems,
AddressPostTown,
AddressCounty,
AddressPostcode,
PersonalDetailsPersonNameTitle,
PersonalDetailsPersonNameGivenName,
PersonalDetailsPersonNameFamilyName,
Telephone1,
Telephone2,
Fax,
WebsiteAddress,
Email
) VALUES (
source.ProviderId,
source.ProviderContactIndex,
source.ContactType,
source.ContactRole,
source.AddressSaonDescription,
source.AddressPaonDescription,
source.AddressStreetDescription,
source.AddressLocality,
source.AddressItems,
source.AddressPostTown,
source.AddressCounty,
source.AddressPostcode,
source.PersonalDetailsPersonNameTitle,
source.PersonalDetailsPersonNameGivenName,
source.PersonalDetailsPersonNameFamilyName,
source.Telephone1,
source.Telephone2,
source.Fax,
source.WebsiteAddress,
source.Email
)
WHEN MATCHED THEN UPDATE SET
ProviderContactIndex = source.ProviderContactIndex,
ContactType = source.ContactType,
ContactRole = source.ContactRole,
AddressSaonDescription = source.AddressSaonDescription,
AddressPaonDescription = source.AddressPaonDescription,
AddressStreetDescription = source.AddressStreetDescription,
AddressLocality = source.AddressLocality,
AddressItems = source.AddressItems,
AddressPostTown = source.AddressPostTown,
AddressCounty = source.AddressCounty,
AddressPostcode = source.AddressPostcode,
PersonalDetailsPersonNameTitle = source.PersonalDetailsPersonNameTitle,
PersonalDetailsPersonNameGivenName = source.PersonalDetailsPersonNameGivenName,
PersonalDetailsPersonNameFamilyName = source.PersonalDetailsPersonNameFamilyName,
Telephone1 = source.Telephone1,
Telephone2 = source.Telephone2,
Fax = source.Fax,
WebsiteAddress = source.WebsiteAddress,
Email = source.Email
WHEN NOT MATCHED BY SOURCE AND target.ProviderId IN (SELECT ProviderId FROM #Providers) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction: transaction);
}
return new None();
}
public async Task <None> Execute(SqlTransaction transaction, UpsertApprenticeshipsFromCosmos query)
{
await UpsertApprenticeships();
await UpsertApprenticeshipLocations();
await UpsertApprenticeshipLocationRegions();
return(new None());
async Task UpsertApprenticeships()
{
var createTableSql = @"
CREATE TABLE #Apprenticeships (
ApprenticeshipId UNIQUEIDENTIFIER,
ApprenticeshipStatus INT,
CreatedOn DATETIME,
CreatedBy NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
TribalApprenticeshipId INT,
ProviderId UNIQUEIDENTIFIER,
ProviderUkprn INT,
ApprenticeshipType TINYINT,
ApprenticeshipTitle NVARCHAR(MAX),
StandardCode INT,
StandardVersion INT,
FrameworkCode INT,
FrameworkProgType INT,
FrameworkPathwayCode INT,
MarketingInformation NVARCHAR(MAX),
ApprenticeshipWebsite NVARCHAR(MAX),
ContactTelephone NVARCHAR(MAX),
ContactEmail NVARCHAR(MAX),
ContactWebsite NVARCHAR(MAX),
BulkUploadErrorCount INT
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.Select(a => new
{
a.ApprenticeshipId,
a.ApprenticeshipStatus,
a.CreatedOn,
a.CreatedBy,
a.UpdatedOn,
a.UpdatedBy,
a.TribalApprenticeshipId,
a.ProviderId,
a.ProviderUkprn,
a.ApprenticeshipType,
a.ApprenticeshipTitle,
a.StandardCode,
a.StandardVersion,
a.FrameworkCode,
a.FrameworkProgType,
a.FrameworkPathwayCode,
a.MarketingInformation,
a.ApprenticeshipWebsite,
a.ContactTelephone,
a.ContactEmail,
a.ContactWebsite,
a.BulkUploadErrorCount
}),
tableName : "#Apprenticeships",
transaction : transaction);
var mergeSql = @"
MERGE Pttcd.Apprenticeships AS target
USING (SELECT * FROM #Apprenticeships) AS source
ON target.ApprenticeshipId = source.ApprenticeshipId
WHEN NOT MATCHED THEN
INSERT (
ApprenticeshipId,
LastSyncedFromCosmos,
ApprenticeshipStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
TribalApprenticeshipId,
ProviderId,
ProviderUkprn,
ApprenticeshipType,
ApprenticeshipTitle,
StandardCode,
StandardVersion,
FrameworkCode,
FrameworkProgType,
FrameworkPathwayCode,
MarketingInformation,
ApprenticeshipWebsite,
ContactTelephone,
ContactEmail,
ContactWebsite,
BulkUploadErrorCount
) VALUES (
source.ApprenticeshipId,
@LastSyncedFromCosmos,
source.ApprenticeshipStatus,
source.CreatedOn,
source.CreatedBy,
source.UpdatedOn,
source.UpdatedBy,
source.TribalApprenticeshipId,
source.ProviderId,
source.ProviderUkprn,
source.ApprenticeshipType,
source.ApprenticeshipTitle,
source.StandardCode,
source.StandardVersion,
source.FrameworkCode,
source.FrameworkProgType,
source.FrameworkPathwayCode,
source.MarketingInformation,
source.ApprenticeshipWebsite,
source.ContactTelephone,
source.ContactEmail,
source.ContactWebsite,
source.BulkUploadErrorCount
)
WHEN MATCHED THEN
UPDATE SET
ApprenticeshipId = source.ApprenticeshipId,
LastSyncedFromCosmos = @LastSyncedFromCosmos,
ApprenticeshipStatus = source.ApprenticeshipStatus,
CreatedOn = source.CreatedOn,
CreatedBy = source.CreatedBy,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
TribalApprenticeshipId = source.TribalApprenticeshipId,
ProviderId = source.ProviderId,
ProviderUkprn = source.ProviderUkprn,
ApprenticeshipType = source.ApprenticeshipType,
ApprenticeshipTitle = source.ApprenticeshipTitle,
StandardCode = source.StandardCode,
StandardVersion = source.StandardVersion,
FrameworkCode = source.FrameworkCode,
FrameworkProgType = source.FrameworkProgType,
FrameworkPathwayCode = source.FrameworkPathwayCode,
MarketingInformation = source.MarketingInformation,
ApprenticeshipWebsite = source.ApprenticeshipWebsite,
ContactTelephone = source.ContactTelephone,
ContactEmail = source.ContactEmail,
ContactWebsite = source.ContactWebsite,
BulkUploadErrorCount = source.BulkUploadErrorCount;";
await transaction.Connection.ExecuteAsync(
mergeSql,
param : new { query.LastSyncedFromCosmos },
transaction : transaction);
}
async Task UpsertApprenticeshipLocations()
{
var createTableSql = @"
CREATE TABLE #ApprenticeshipLocations (
ApprenticeshipLocationId UNIQUEIDENTIFIER,
ApprenticeshipId UNIQUEIDENTIFIER,
ApprenticeshipLocationStatus INT,
CreatedOn DATETIME,
CreatedBy NVARCHAR(MAX),
UpdatedOn DATETIME,
UpdatedBy NVARCHAR(MAX),
Telephone NVARCHAR(MAX),
VenueId UNIQUEIDENTIFIER,
TribalApprenticeshipLocationId INT,
[National] BIT,
Radius INT,
LocationType TINYINT,
ApprenticeshipLocationType TINYINT,
Name NVARCHAR(MAX),
DeliveryModes TINYINT,
LocationGuidId UNIQUEIDENTIFIER
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.SelectMany(a => a.Locations.Select(l => new
{
l.ApprenticeshipLocationId,
a.ApprenticeshipId,
l.ApprenticeshipLocationStatus,
l.CreatedOn,
l.CreatedBy,
l.UpdatedOn,
l.UpdatedBy,
l.Telephone,
l.VenueId,
l.TribalApprenticeshipLocationId,
l.National,
l.Radius,
l.LocationType,
l.ApprenticeshipLocationType,
l.Name,
DeliveryModes = MapDeliveryModes(l.DeliveryModes),
l.LocationGuidId
})),
tableName : "#ApprenticeshipLocations",
transaction : transaction);
var mergeSql = @"
-- Remove any regions for deleted locations
DELETE alr FROM Pttcd.ApprenticeshipLocationRegions alr
JOIN Pttcd.ApprenticeshipLocations al ON alr.ApprenticeshipLocationId = al.ApprenticeshipLocationId
JOIN #Apprenticeships a ON al.ApprenticeshipId = a.ApprenticeshipId
LEFT JOIN #ApprenticeshipLocations x ON alr.ApprenticeshipLocationId = x.ApprenticeshipLocationId
WHERE x.ApprenticeshipLocationId IS NULL
MERGE Pttcd.ApprenticeshipLocations AS target
USING (SELECT * FROM #ApprenticeshipLocations) AS source
ON target.ApprenticeshipLocationId = source.ApprenticeshipLocationId
WHEN NOT MATCHED THEN
INSERT (
ApprenticeshipLocationId,
ApprenticeshipId,
ApprenticeshipLocationStatus,
CreatedOn,
CreatedBy,
UpdatedOn,
UpdatedBy,
Telephone,
VenueId,
TribalApprenticeshipLocationId,
[National],
Radius,
LocationType,
ApprenticeshipLocationType,
Name,
DeliveryModes,
LocationGuidId
) VALUES (
source.ApprenticeshipLocationId,
source.ApprenticeshipId,
source.ApprenticeshipLocationStatus,
source.CreatedOn,
source.CreatedBy,
source.UpdatedOn,
source.UpdatedBy,
source.Telephone,
source.VenueId,
source.TribalApprenticeshipLocationId,
source.[National],
source.Radius,
source.LocationType,
source.ApprenticeshipLocationType,
source.Name,
source.DeliveryModes,
source.LocationGuidId
)
WHEN MATCHED THEN
UPDATE SET
ApprenticeshipLocationId = source.ApprenticeshipLocationId,
ApprenticeshipId = source.ApprenticeshipId,
ApprenticeshipLocationStatus = source.ApprenticeshipLocationStatus,
CreatedOn = source.CreatedOn,
CreatedBy = source.CreatedBy,
UpdatedOn = source.UpdatedOn,
UpdatedBy = source.UpdatedBy,
Telephone = source.Telephone,
VenueId = source.VenueId,
TribalApprenticeshipLocationId = source.TribalApprenticeshipLocationId,
[National] = source.[National],
Radius = source.Radius,
LocationType = source.LocationType,
ApprenticeshipLocationType = source.ApprenticeshipLocationType,
Name = source.Name,
DeliveryModes = source.DeliveryModes,
LocationGuidId = source.LocationGuidId
WHEN NOT MATCHED BY SOURCE AND target.ApprenticeshipId IN (SELECT ApprenticeshipId FROM #Apprenticeships) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction);
}
async Task UpsertApprenticeshipLocationRegions()
{
var createTableSql = @"
CREATE TABLE #ApprenticeshipLocationRegions (
ApprenticeshipLocationId UNIQUEIDENTIFIER,
RegionId VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS
)";
await transaction.Connection.ExecuteAsync(createTableSql, transaction : transaction);
await BulkCopyHelper.WriteRecords(
query.Records.SelectMany(a => a.Locations.SelectMany(al => al.Regions.Select(r => new
{
al.ApprenticeshipLocationId,
RegionId = r
}))),
tableName : "#ApprenticeshipLocationRegions",
transaction);
var mergeSql = @"
MERGE Pttcd.ApprenticeshipLocationRegions AS target
USING (
SELECT ApprenticeshipLocationId, RegionId FROM #ApprenticeshipLocationRegions
) AS source
ON target.ApprenticeshipLocationId = source.ApprenticeshipLocationId AND target.RegionId = source.RegionId
WHEN NOT MATCHED THEN
INSERT (ApprenticeshipLocationId, RegionId) VALUES (source.ApprenticeshipLocationId, source.RegionId)
WHEN NOT MATCHED BY SOURCE AND target.ApprenticeshipLocationId IN (SELECT ApprenticeshipLocationId FROM #ApprenticeshipLocations) THEN DELETE;";
await transaction.Connection.ExecuteAsync(mergeSql, transaction : transaction);
}
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());
}
