public async Task <UploadStatus> DeleteVenueUploadRowForProvider(Guid providerId, int rowNumber)
{
using (var dispatcher = _sqlQueryDispatcherFactory.CreateDispatcher(System.Data.IsolationLevel.ReadCommitted))
{
await AcquireExclusiveVenueUploadLockForProvider(providerId, dispatcher);
var venueUpload = await dispatcher.ExecuteQuery(new GetLatestUnpublishedVenueUploadForProvider()
{
ProviderId = providerId
});
var(existingRows, lastRowNumber) = await dispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUpload.VenueUploadId
});
var rowToDelete = existingRows.SingleOrDefault(x => x.RowNumber == rowNumber);
if (rowToDelete == null)
{
throw new ResourceDoesNotExistException(ResourceType.VenueUploadRow, rowNumber);
}
if (!rowToDelete.IsDeletable)
{
throw new InvalidStateException(InvalidStateReason.VenueUploadRowCannotBeDeleted);
}
var nonDeletedRows = existingRows.Where(x => x.RowNumber != rowNumber).ToArray();
var rowCollection = new VenueDataUploadRowInfoCollection(
lastRowNumber: lastRowNumber,
nonDeletedRows
.Where(r => r.RowNumber != rowNumber)
.Select(r => new VenueDataUploadRowInfo(CsvVenueRow.FromModel(r), r.RowNumber, r.IsSupplementary)));
var(uploadStatus, _) = await ValidateVenueUploadFile(
dispatcher,
venueUpload.VenueUploadId,
venueUpload.ProviderId,
rowCollection);
await dispatcher.Commit();
return(uploadStatus);
}
}
public async Task <UploadStatus> UpdateVenueUploadRowForProvider(Guid providerId, int rowNumber, CsvVenueRow updatedRow)
{
using var dispatcher = _sqlQueryDispatcherFactory.CreateDispatcher(System.Data.IsolationLevel.ReadCommitted);
await AcquireExclusiveVenueUploadLockForProvider(providerId, dispatcher);
var venueUpload = await dispatcher.ExecuteQuery(new GetLatestUnpublishedVenueUploadForProvider()
{
ProviderId = providerId
});
if (venueUpload == null)
{
throw new InvalidStateException(InvalidStateReason.NoUnpublishedVenueUpload);
}
if (venueUpload.UploadStatus != UploadStatus.ProcessedWithErrors)
{
throw new InvalidUploadStatusException(venueUpload.UploadStatus, UploadStatus.ProcessedWithErrors);
}
var(rows, lastRowNumber) = await dispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUpload.VenueUploadId
});
var row = rows.SingleOrDefault(r => r.RowNumber == rowNumber);
if (row == null)
{
throw new ResourceDoesNotExistException(ResourceType.VenueUploadRow, rowNumber);
}
var updatedRows = new VenueDataUploadRowInfoCollection(
lastRowNumber: lastRowNumber,
rows
.Where(r => r.RowNumber != rowNumber)
.Select(r => new VenueDataUploadRowInfo(CsvVenueRow.FromModel(r), r.RowNumber, r.IsSupplementary))
.Append(new VenueDataUploadRowInfo(updatedRow, rowNumber, row.IsSupplementary)));
var(uploadStatus, _) = await ValidateVenueUploadFile(dispatcher, venueUpload.VenueUploadId, venueUpload.ProviderId, updatedRows);
await dispatcher.Commit();
return(uploadStatus);
}
public async Task ValidateVenueUploadFile_RowsHasErrors_InsertsExpectedErrorCodesIntoDb(
CsvVenueRow row,
IEnumerable <string> expectedErrorCodes,
IEnumerable <CsvVenueRow> additionalRows)
{
// Arrange
var provider = await TestData.CreateProvider();
var user = await TestData.CreateUser(providerId : provider.ProviderId);
var(venueUpload, _) = await TestData.CreateVenueUpload(provider.ProviderId, createdBy : user, UploadStatus.Processing);
var fileUploadProcessor = new FileUploadProcessor(
SqlQueryDispatcherFactory,
Mock.Of <BlobServiceClient>(),
Clock,
new RegionCache(SqlQueryDispatcherFactory),
new ExecuteImmediatelyBackgroundWorkScheduler(Fixture.ServiceScopeFactory));
var uploadRows = new[] { row }.Concat(additionalRows ?? Enumerable.Empty <CsvVenueRow>()).ToDataUploadRowCollection();
await WithSqlQueryDispatcher(async dispatcher =>
{
// Add a row into Postcodes table to ensure we don't have errors due to it missing
// (ValidateVenueUploadFile_PostcodeIsNotInDb_InsertsExpectedErrorCodesIntoDb tests that scenario)
await AddPostcodeInfoForRows(dispatcher, uploadRows);
// Act
await fileUploadProcessor.ValidateVenueUploadFile(
dispatcher,
venueUpload.VenueUploadId,
venueUpload.ProviderId,
uploadRows);
var(rows, _) = await dispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUpload.VenueUploadId
});
rows.First().IsValid.Should().BeFalse();
rows.First().Errors.Should().BeEquivalentTo(expectedErrorCodes);
});
}
public async Task ValidateVenueUploadFile_DoesNotNormalizeInvalidPostcode()
{
// Arrange
var provider = await TestData.CreateProvider();
var user = await TestData.CreateUser(providerId : provider.ProviderId);
var(venueUpload, _) = await TestData.CreateVenueUpload(provider.ProviderId, createdBy : user, UploadStatus.Processing);
var fileUploadProcessor = new FileUploadProcessor(
SqlQueryDispatcherFactory,
Mock.Of <BlobServiceClient>(),
Clock,
new RegionCache(SqlQueryDispatcherFactory),
new ExecuteImmediatelyBackgroundWorkScheduler(Fixture.ServiceScopeFactory));
var row = new CsvVenueRow()
{
Postcode = "xxxx",
};
var uploadRows = new[] { row }.ToDataUploadRowCollection();
await WithSqlQueryDispatcher(async dispatcher =>
{
// Act
await fileUploadProcessor.ValidateVenueUploadFile(
dispatcher,
venueUpload.VenueUploadId,
venueUpload.ProviderId,
uploadRows);
var(rows, _) = await dispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUpload.VenueUploadId
});
rows.Count.Should().Be(1);
rows.Last().Postcode.Should().Be(row.Postcode);
});
}
private async Task <(UploadStatus UploadStatus, IReadOnlyCollection <VenueUploadRow> RevalidatedRows)> RevalidateVenueUploadIfRequired(
ISqlQueryDispatcher sqlQueryDispatcher,
Guid venueUploadId)
{
var venueUpload = await sqlQueryDispatcher.ExecuteQuery(new GetVenueUpload()
{
VenueUploadId = venueUploadId
});
if (venueUpload == null)
{
throw new ArgumentException("Venue upload does not exist.", nameof(venueUploadId));
}
var revalidate = await DoesVenueUploadRequireRevalidating(sqlQueryDispatcher, venueUpload);
if (!revalidate)
{
return(venueUpload.UploadStatus, RevalidatedRows : null);
}
var(rows, lastRowNumber) = await sqlQueryDispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUploadId
});
var rowCollection = new VenueDataUploadRowInfoCollection(
lastRowNumber,
rows.Select(r => new VenueDataUploadRowInfo(CsvVenueRow.FromModel(r), r.RowNumber, r.IsSupplementary)));
var(uploadStatus, revalidatedRows) = await ValidateVenueUploadFile(
sqlQueryDispatcher,
venueUploadId,
venueUpload.ProviderId,
rowCollection);
return(uploadStatus, RevalidatedRows : revalidatedRows);
}
// internal for testing
internal async Task <(UploadStatus uploadStatus, IReadOnlyCollection <VenueUploadRow> Rows)> ValidateVenueUploadFile(
ISqlQueryDispatcher sqlQueryDispatcher,
Guid venueUploadId,
Guid providerId,
VenueDataUploadRowInfoCollection rows)
{
// We need to ensure that any venues that have live offerings attached are not removed when publishing this
// upload. We do that by adding an additional row to this upload for any venues that are not included in
// this file that have live offerings attached. This must be done *before* validation so that duplicate
// checks consider these additional added rows.
var originalRowCount = rows.Count;
var existingVenues = await sqlQueryDispatcher.ExecuteQuery(new GetVenueMatchInfoForProvider()
{
ProviderId = providerId
});
// For each row in the file try to match it to an existing venue
var rowVenueIdMapping = MatchRowsToExistingVenues(rows, existingVenues);
// Add a row for any existing venues that are linked to live offerings and haven't been matched
var matchedVenueIds = rowVenueIdMapping.Where(m => m.HasValue).Select(m => m.Value).ToArray();
var venuesWithLiveOfferingsNotInFile = existingVenues
.Where(v => v.HasLiveOfferings && !matchedVenueIds.Contains(v.VenueId))
.ToArray();
rows = new VenueDataUploadRowInfoCollection(
lastRowNumber: rows.LastRowNumber + venuesWithLiveOfferingsNotInFile.Length,
rows: rows.Concat(
venuesWithLiveOfferingsNotInFile.Select((v, i) =>
new VenueDataUploadRowInfo(CsvVenueRow.FromModel(v), rowNumber: rows.LastRowNumber + i + 1, isSupplementary: true))));
rowVenueIdMapping = rowVenueIdMapping.Concat(venuesWithLiveOfferingsNotInFile.Select(v => (Guid?)v.VenueId)).ToArray();
// Grab PostcodeInfo for all of the valid postcodes in the file.
// We need this for both the validator and to track whether the venue is outside of England
var allPostcodeInfo = await GetPostcodeInfoForRows(sqlQueryDispatcher, rows);
var uploadIsValid = true;
var validator = new VenueUploadRowValidator(rows, allPostcodeInfo);
var upsertRecords = new List <SetVenueUploadRowsRecord>();
for (int i = 0; i < rows.Count; i++)
{
var row = rows[i].Data;
var rowNumber = rows[i].RowNumber;
var venueId = rowVenueIdMapping[i] ?? Guid.NewGuid();
var isSupplementaryRow = i >= originalRowCount;
// A row is deletable if it is *not* matched to an existing venue that has attached offerings
var isDeletable = rowVenueIdMapping[i] is null ||
!existingVenues.Single(v => v.VenueId == rowVenueIdMapping[i]).HasLiveOfferings;
row.ProviderVenueRef = row.ProviderVenueRef?.Trim();
row.Postcode = Postcode.TryParse(row.Postcode, out var postcode) ? postcode : row.Postcode;
PostcodeInfo postcodeInfo = null;
if (postcode != null)
{
allPostcodeInfo.TryGetValue(postcode, out postcodeInfo);
}
var rowValidationResult = validator.Validate(row);
var errors = rowValidationResult.Errors.Select(e => e.ErrorCode).ToArray();
var rowIsValid = rowValidationResult.IsValid;
uploadIsValid &= rowIsValid;
upsertRecords.Add(new SetVenueUploadRowsRecord()
{
RowNumber = rowNumber,
IsValid = rowIsValid,
Errors = errors,
IsSupplementary = isSupplementaryRow,
OutsideOfEngland = postcodeInfo != null ? !postcodeInfo.InEngland : (bool?)null,
VenueId = venueId,
IsDeletable = isDeletable,
ProviderVenueRef = row.ProviderVenueRef,
VenueName = row.VenueName,
AddressLine1 = row.AddressLine1,
AddressLine2 = row.AddressLine2,
Town = row.Town,
County = row.County,
Postcode = row.Postcode,
Email = row.Email,
Telephone = row.Telephone,
Website = row.Website
});
}
var updatedRows = await sqlQueryDispatcher.ExecuteQuery(new SetVenueUploadRows()
{
VenueUploadId = venueUploadId,
ValidatedOn = _clock.UtcNow,
Records = upsertRecords
});
await sqlQueryDispatcher.ExecuteQuery(new SetVenueUploadProcessed()
{
VenueUploadId = venueUploadId,
ProcessingCompletedOn = _clock.UtcNow,
IsValid = uploadIsValid
});
var uploadStatus = uploadIsValid ? UploadStatus.ProcessedSuccessfully : UploadStatus.ProcessedWithErrors;
return(uploadStatus, updatedRows);
}
public async Task ValidateVenueUploadFile_InsertsRowsIntoDb()
{
// Arrange
var provider = await TestData.CreateProvider();
var user = await TestData.CreateUser(providerId : provider.ProviderId);
var(venueUpload, _) = await TestData.CreateVenueUpload(provider.ProviderId, createdBy : user, UploadStatus.Processing);
var fileUploadProcessor = new FileUploadProcessor(
SqlQueryDispatcherFactory,
Mock.Of <BlobServiceClient>(),
Clock,
new RegionCache(SqlQueryDispatcherFactory),
new ExecuteImmediatelyBackgroundWorkScheduler(Fixture.ServiceScopeFactory));
var row = new CsvVenueRow()
{
ProviderVenueRef = "REF",
VenueName = "Place",
AddressLine1 = "Line 1",
AddressLine2 = "Line 2",
Town = "Town",
County = "County",
Postcode = "AB1 2DE",
Email = "*****@*****.**",
Telephone = "01234 567890",
Website = "provider.com/place"
};
var uploadRows = new[] { row }.ToDataUploadRowCollection();
await WithSqlQueryDispatcher(async dispatcher =>
{
// Act
await fileUploadProcessor.ValidateVenueUploadFile(
dispatcher,
venueUpload.VenueUploadId,
venueUpload.ProviderId,
uploadRows);
var(rows, _) = await dispatcher.ExecuteQuery(new GetVenueUploadRows()
{
VenueUploadId = venueUpload.VenueUploadId
});
rows.Count.Should().Be(1);
rows.Last().Should().BeEquivalentTo(new VenueUploadRow()
{
RowNumber = 2,
LastUpdated = Clock.UtcNow,
LastValidated = Clock.UtcNow,
IsSupplementary = false,
IsDeletable = true,
AddressLine1 = row.AddressLine1,
AddressLine2 = row.AddressLine2,
County = row.County,
Email = row.Email,
Postcode = row.Postcode,
ProviderVenueRef = row.ProviderVenueRef,
Telephone = row.Telephone,
Town = row.Town,
VenueName = row.VenueName,
Website = row.Website
}, config => config.Excluding(r => r.IsValid).Excluding(r => r.Errors).Excluding(r => r.VenueId));
});
}
