public async Task <IReadOnlyCollection <Apprenticeship> > Execute(SqlTransaction transaction, GetApprenticeshipsForProvider query)
{
var sql = $@"
SELECT
a.ApprenticeshipId,
a.CreatedOn,
a.UpdatedOn,
a.ProviderId,
a.ProviderUkprn,
a.MarketingInformation,
a.ApprenticeshipWebsite,
a.ContactTelephone,
a.ContactEmail,
a.ContactWebsite,
s.StandardCode,
s.Version,
s.StandardName,
s.NotionalEndLevel NotionalNVQLevelv2,
s.OtherBodyApprovalRequired
FROM Pttcd.Apprenticeships a
JOIN Pttcd.Standards s ON a.StandardCode = s.StandardCode AND a.StandardVersion = s.Version
WHERE a.ProviderId = @ProviderId
AND a.ApprenticeshipStatus = {(int)ApprenticeshipStatus.Live}
SELECT
al.ApprenticeshipLocationId,
al.ApprenticeshipId,
al.ApprenticeshipLocationType,
al.CreatedOn,
al.UpdatedOn,
al.Telephone,
al.[National],
al.Radius,
al.DeliveryModes,
al.VenueId,
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.ApprenticeshipLocations al
JOIN Pttcd.Apprenticeships a ON al.ApprenticeshipId = a.ApprenticeshipId
LEFT JOIN Pttcd.Venues v ON al.VenueId = v.VenueId
WHERE a.ProviderId = @ProviderId
AND al.ApprenticeshipLocationStatus = {(int)ApprenticeshipStatus.Live}
ORDER BY al.CreatedOn
SELECT r.ApprenticeshipLocationId, r.RegionId
FROM Pttcd.ApprenticeshipLocationRegions r
JOIN Pttcd.ApprenticeshipLocations al ON r.ApprenticeshipLocationId = al.ApprenticeshipLocationId
JOIN Pttcd.Apprenticeships a ON al.ApprenticeshipId = a.ApprenticeshipId
WHERE a.ProviderId = @ProviderId";
var paramz = new { query.ProviderId };
using var reader = await transaction.Connection.QueryMultipleAsync(sql, paramz, transaction);
return(await ApprenticeshipMappingHelper.MapApprenticeships(reader));
}
public async Task <IReadOnlyCollection <ApprenticeshipUploadRow> > Execute(SqlTransaction transaction, UpsertApprenticeshipUploadRows query)
{
var sql = $@"
MERGE Pttcd.ApprenticeshipUploadRows AS target
USING (SELECT * FROM @Rows) AS source
ON target.ApprenticeshipUploadId = @ApprenticeshipUploadId AND target.RowNumber = source.RowNumber
WHEN NOT MATCHED THEN
INSERT (
ApprenticeshipUploadId,
RowNumber,
ApprenticeshipUploadRowStatus,
IsValid,
Errors,
LastUpdated,
LastValidated,
ApprenticeshipId,
ApprenticeshipLocationId,
StandardCode,
StandardVersion,
ApprenticeshipInformation,
ApprenticeshipWebpage,
ContactEmail,
ContactPhone,
ContactUrl,
DeliveryMethod,
VenueName,
YourVenueReference,
Radius,
DeliveryModes,
NationalDelivery,
SubRegions,
VenueId,
ResolvedDeliveryMethod,
ResolvedDeliveryModes,
ResolvedNationalDelivery,
ResolvedRadius
) VALUES (
@ApprenticeshipUploadId,
source.RowNumber,
{(int)UploadRowStatus.Default},
source.IsValid,
source.Errors,
ISNULL(source.LastUpdated, source.LastValidated),
source.LastValidated,
source.ApprenticeshipId,
source.ApprenticeshipLocationId,
source.StandardCode,
source.StandardVersion,
source.ApprenticeshipInformation,
source.ApprenticeshipWebpage,
source.ContactEmail,
source.ContactPhone,
source.ContactUrl,
source.DeliveryMethod,
source.VenueName,
source.YourVenueReference,
source.Radius,
source.DeliveryModes,
source.NationalDelivery,
source.SubRegions,
source.VenueId,
source.ResolvedDeliveryMethod,
source.ResolvedDeliveryModes,
source.ResolvedNationalDelivery,
source.ResolvedRadius
)
WHEN MATCHED THEN UPDATE SET
RowNumber = source.RowNumber,
IsValid = source.IsValid,
Errors = source.Errors,
LastUpdated = ISNULL(source.LastUpdated, target.LastValidated),
LastValidated = source.LastValidated,
ApprenticeshipId = source.ApprenticeshipId,
ApprenticeshipLocationId = source.ApprenticeshipLocationId,
StandardCode = source.StandardCode,
StandardVersion = source.StandardVersion,
ApprenticeshipInformation = source.ApprenticeshipInformation,
ApprenticeshipWebpage = source.ApprenticeshipWebpage,
ContactEmail = source.ContactEmail,
ContactPhone = source.ContactPhone,
ContactUrl = source.ContactUrl,
DeliveryMethod = source.DeliveryMethod,
VenueName = source.VenueName,
YourVenueReference = source.YourVenueReference,
Radius = source.Radius,
DeliveryModes = source.DeliveryModes,
NationalDelivery = source.NationalDelivery,
SubRegions = source.SubRegions,
VenueId = source.VenueId,
ResolvedDeliveryMethod = source.ResolvedDeliveryMethod,
ResolvedDeliveryModes = source.ResolvedDeliveryModes,
ResolvedNationalDelivery = source.ResolvedNationalDelivery,
ResolvedRadius = source.ResolvedRadius
;
MERGE Pttcd.ApprenticeshipUploadRowSubRegions AS target
USING (SELECT RowNumber, RegionId FROM @RowSubRegions) AS source
ON
target.ApprenticeshipUploadId = @ApprenticeshipUploadId AND
target.RowNumber = source.RowNumber AND
target.RegionId = source.RegionId
WHEN NOT MATCHED THEN INSERT (ApprenticeshipUploadId, RowNumber, RegionId) VALUES (@ApprenticeshipUploadId, source.RowNumber, source.RegionId)
WHEN NOT MATCHED BY SOURCE AND target.ApprenticeshipUploadId = @ApprenticeshipUploadId AND target.RowNumber IN (SELECT RowNumber FROM @Rows) THEN DELETE
;
SELECT
RowNumber, IsValid, Errors AS ErrorList, LastUpdated, LastValidated, ApprenticeshipId, ApprenticeshipLocationId,
StandardCode, StandardVersion, ApprenticeshipInformation, ApprenticeshipWebpage,ContactEmail,
ContactPhone, ContactUrl, DeliveryMethod, VenueName,YourVenueReference, Radius, DeliveryModes, NationalDelivery,
SubRegions, VenueId, ResolvedDeliveryMethod
FROM Pttcd.ApprenticeshipUploadRows
WHERE ApprenticeshipUploadId = @ApprenticeshipUploadId
AND ApprenticeshipUploadRowStatus = {(int)UploadRowStatus.Default}
ORDER BY RowNumber";
var paramz = new
{
query.ApprenticeshipUploadId,
Rows = CreateRowsTvp(),
RowSubRegions = CreateRowSubRegionsTvp()
};
var results = (await transaction.Connection.QueryAsync <Result>(sql, paramz, transaction))
.AsList();
foreach (var row in results)
{
row.Errors = (row.ErrorList ?? string.Empty).Split(";", StringSplitOptions.RemoveEmptyEntries);
}
return(results);
ICustomQueryParameter CreateRowsTvp()
{
var table = new DataTable();
table.Columns.Add("RowNumber", typeof(int));
table.Columns.Add("IsValid", typeof(bool));
table.Columns.Add("Errors", typeof(string));
table.Columns.Add("LastUpdated", typeof(DateTime));
table.Columns.Add("LastValidated", typeof(DateTime));
table.Columns.Add("ApprenticeshipId", typeof(Guid));
table.Columns.Add("ApprenticeshipLocationId", typeof(Guid));
table.Columns.Add("StandardCode", typeof(int));
table.Columns.Add("StandardVersion", typeof(int));
table.Columns.Add("ApprenticeshipInformation", typeof(string));
table.Columns.Add("ApprenticeshipWebpage", typeof(string));
table.Columns.Add("ContactEmail", typeof(string));
table.Columns.Add("ContactPhone", typeof(string));
table.Columns.Add("ContactUrl", typeof(string));
table.Columns.Add("DeliveryMethod", typeof(string));
table.Columns.Add("VenueName", typeof(string));
table.Columns.Add("YourVenueReference", typeof(string));
table.Columns.Add("Radius", typeof(string));
table.Columns.Add("DeliveryModes", typeof(string));
table.Columns.Add("NationalDelivery", typeof(string));
table.Columns.Add("SubRegions", typeof(string));
table.Columns.Add("VenueId", typeof(Guid));
table.Columns.Add("ResolvedDeliveryMethod", typeof(byte));
table.Columns.Add("ResolvedDeliveryModes", typeof(byte));
table.Columns.Add("ResolvedNationalDelivery", typeof(byte));
table.Columns.Add("ResolvedRadius", typeof(int));
foreach (var record in query.Records)
{
table.Rows.Add(
record.RowNumber,
record.IsValid,
string.Join(";", record.Errors ?? Enumerable.Empty <string>()), // Errors
query.UpdatedOn,
query.ValidatedOn,
record.ApprenticeshipId,
record.ApprenticeshipLocationId,
record.StandardCode,
record.StandardVersion,
record.ApprenticeshipInformation,
record.ApprenticeshipWebpage,
record.ContactEmail,
record.ContactPhone,
record.ContactUrl,
record.DeliveryMethod,
record.VenueName,
record.YourVenueReference,
record.Radius,
record.DeliveryModes,
record.NationalDelivery,
record.SubRegions,
record.VenueId,
record.ResolvedDeliveryMethod,
ApprenticeshipMappingHelper.MapDeliveryModesToSqlValue(record.ResolvedDeliveryModes ?? Enumerable.Empty <ApprenticeshipDeliveryMode>()),
record.ResolvedNationalDelivery,
record.ResolvedRadius);
}
return(table.AsTableValuedParameter("Pttcd.ApprenticeshipUploadRowTable"));
}
ICustomQueryParameter CreateRowSubRegionsTvp()
{
var table = new DataTable();
table.Columns.Add("RowNumber", typeof(int));
table.Columns.Add("RegionId", typeof(string));
var subRegionIds = query.Records
.SelectMany(r => (r.ResolvedSubRegions ?? Array.Empty <string>()).Select(sr => (SubRegionId: sr, RowNumber: r.RowNumber)))
.Where(t => t.SubRegionId != null);
foreach (var record in subRegionIds)
{
table.Rows.Add(
record.RowNumber,
record.SubRegionId);
}
return(table.AsTableValuedParameter("Pttcd.ApprenticeshipUploadRowSubRegionsTable"));
}
}