private void ValidateSpreadsheetRows(DataTable batchData, AllowedData allowedData, BatchUpload batchUpload)
{
batchUpload.ColumnsCount = batchData.Columns.Count;
batchUpload.TotalDataRows = batchData.Rows.Count;
//TODO FIN-841 check columns are all present
for (int i = 0; i < batchData.Rows.Count; i++)
{
DataRow row = batchData.Rows[i];
List<UploadValidationFailure> validationFailures = ValidateUploadedRow(row, i, allowedData);
if (validationFailures.Count == 0)
{
UploadDataModel upload = new BatchRowParser(row).GetUploadDataModel();
upload.RowNumber = i + 2;
batchUpload.DataToUpload.Add(upload);
}
else
{
foreach (UploadValidationFailure error in validationFailures)
batchUpload.UploadValidationFailures.Add(error);
}
}
}
public void Validate(DataTable indicatorData, DataTable pholioData, SimpleUpload simpleUpload)
{
AllowedData allowedData = new AllowedData(_profilesReader);
ValidateIndicatorDetails(indicatorData, simpleUpload, allowedData);
ValidateSpreadsheetRows(pholioData, simpleUpload, allowedData);
if (simpleUpload.DataToUpload.Count > 0 && simpleUpload.UploadValidationFailures.Count == 0)
{
//Validate the spreadsheet data to see if there's replication within it.
simpleUpload.DuplicateUploadErrorsExist = ValidateSpreadsheetForDuplicatedRows(simpleUpload);
simpleUpload.DuplicateRowInDatabaseErrors =
new CoreDataSetDuplicateChecker().GetDuplicates(simpleUpload.DataToUpload, _coreDataRepository,
UploadJobType.Simple);
}
simpleUpload.UploadPeriod = new TimePeriodTranslater().Translate(simpleUpload);
}
public void TestAllDataListsAreInitialised()
{
var reader = new Mock<ProfilesReader>(MockBehavior.Strict);
reader.Setup(x => x.GetAllAgeIds()).Returns(new List<int>());
reader.Setup(x => x.GetAllSexIds()).Returns(new List<int>());
reader.Setup(x => x.GetAllAreaCodes()).Returns(new List<string>());
reader.Setup(x => x.GetAllValueNoteIds()).Returns(new List<int>());
reader.Setup(x => x.GetAllCategories()).Returns(new List<Category>());
var validData = new AllowedData(reader.Object);
Assert.IsNotNull(validData.AgeIds);
Assert.IsNotNull(validData.AreaCodes);
Assert.IsNotNull(validData.SexIds);
Assert.IsNotNull(validData.ValueNoteIds);
Assert.IsNotNull(validData.Categories);
reader.VerifyAll();
}
public SimpleUpload UploadData(DataTable indicatorDetails, DataTable pholioData, UploadJob job)
{
var simpleUpload = new SimpleUpload();
var rowCount = 0;
var allowedData = new AllowedData(_profilesReader);
// TODO: find out why were are calling it again here as its caled in Validate()
ValidateIndicatorDetails(indicatorDetails, simpleUpload, allowedData);
var dataToUpload = new List<UploadDataModel>();
for (int i = 0; i < pholioData.Rows.Count; i++)
{
DataRow row = pholioData.Rows[i];
var rowParser = new UploadSimpleRowParser(row);
if (rowParser.DoesRowContainData == false)
{
//There isn't an area code or value so assume the end of the data
break;
}
rowCount++;
var upload = rowParser.GetUploadDataModelWithUnparsedValuesSetToDefaults(simpleUpload);
_coreDataRepository.InsertCoreData(upload.ToCoreDataSet(), job.Guid);
dataToUpload.Add(upload);
}
simpleUpload.DataToUpload = dataToUpload;
int uploadId = _loggingRepository.InsertUploadAudit(job.Guid, job.Username,
rowCount, job.Filename, WorksheetNames.SimplePholio);
simpleUpload.ShortFileName = Path.GetFileName(job.Filename);
simpleUpload.TotalDataRows = rowCount;
simpleUpload.UploadBatchId = job.Guid;
simpleUpload.Id = uploadId;
return simpleUpload;
}
public BatchUpload Validate(DataTable indicators, BatchUpload batchUpload)
{
var allowedData = new AllowedData(_profilesReader);
// Validate spreadsheet data
ValidateSpreadsheetRows(indicators, allowedData, batchUpload);
if (batchUpload.DataToUpload.Any())
{
//Validate the spreadsheet data to see if there's duplication within it.
batchUpload.DuplicateUploadErrorsExist = ValidateSpreadsheetForDuplicatedRows(batchUpload);
batchUpload.DuplicateRowInDatabaseErrors =
new CoreDataSetDuplicateChecker().GetDuplicates(batchUpload.DataToUpload, _coreDataRepository,
UploadJobType.Batch);
}
GetDistinctIndicators(indicators);
return batchUpload;
}
// Validate indicator details
private void ValidateIndicatorDetails(DataTable dataTable, SimpleUpload simpleUpload, AllowedData allowedData)
{
var duv = new DataUploadValidation();
GetIndicatorValue(dataTable, simpleUpload);
GetYearValue(dataTable, simpleUpload);
GetYearRange(dataTable, simpleUpload);
GetQuarter(dataTable, simpleUpload);
GetMonth(dataTable, simpleUpload);
GetAgeId(dataTable, simpleUpload);
duv.ValidateAgeId(simpleUpload.AgeId, null, allowedData.AgeIds);
GetSexId(dataTable, simpleUpload);
duv.ValidateSexId(simpleUpload.SexId, null, allowedData.SexIds);
}
private void ValidateSpreadsheetRows(DataTable excelData, SimpleUpload simpleUpload, AllowedData allowedData)
{
simpleUpload.ColumnsCount = excelData.Columns.Count;
simpleUpload.TotalDataRows = excelData.Rows.Count - 1;
List<string> allAreaCodes = allowedData.AreaCodes;
for (int i = 0; i < excelData.Rows.Count; i++)
{
DataRow row = excelData.Rows[i];
var rowParser = new UploadSimpleRowParser(row);
if (rowParser.DoesRowContainData == false)
{
//There isn't an area code or value so assume the end of the data and stop validating now...
break;
}
List<UploadValidationFailure> rowErrors = ValidateUploadedRow(row, i + 2, allAreaCodes);
if (rowErrors.Count == 0)
{
UploadDataModel upload = rowParser.GetUploadDataModel(simpleUpload);
upload.RowNumber = i + 1;
simpleUpload.DataToUpload.Add(upload);
}
else
{
foreach (UploadValidationFailure error in rowErrors)
simpleUpload.UploadValidationFailures.Add(error);
}
simpleUpload.TotalDataRows = i + 1;
}
}
private List<UploadValidationFailure> ValidateUploadedRow(DataRow uploadedRow, int rowNumber,
List<string> allAreaCodes)
{
var duv = new DataUploadValidation();
Exception dataConversionException;
var uploadErrors = new List<UploadValidationFailure>();
//Validate the AreaCode
UploadValidationFailure uploadValidationFailure = duv.ValidateArea(uploadedRow, rowNumber, allAreaCodes);
if (uploadValidationFailure != null)
{
//There was an error so log it
uploadErrors.Add(uploadValidationFailure);
}
//Validate the Count
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.Count,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.Count, "Invalid Count Figure",
dataConversionException.Message));
}
//Validate the Value
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.Value,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.Value, "Invalid Value Figure",
dataConversionException.Message));
}
//Validate the LowerCI
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.LowerCI,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.LowerCI,
"Invalid Lower CI Figure", dataConversionException.Message));
}
//Validate the UpperCI
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.UpperCI,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.UpperCI,
"Invalid Upper CI Figure", dataConversionException.Message));
}
//Validate the Denominator
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.Denominator,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.Denominator,
"Invalid Denominator Figure", dataConversionException.Message));
}
//Validate the ValueNoteId
dataConversionException = duv.ValidateExpectedDataType(uploadedRow, UploadColumnNames.ValueNoteId,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
uploadErrors.Add(new UploadValidationFailure(rowNumber, UploadColumnNames.ValueNoteId,
"Invalid Value Note Id", dataConversionException.Message));
}
else
{
var allowedData = new AllowedData(_profilesReader);
var columnName = UploadColumnNames.ValueNoteId;
//Ensure this is a value note id in the DB
uploadValidationFailure = duv.ValidateValueNoteId((int)uploadedRow.Field<double>(columnName), rowNumber,
allowedData.ValueNoteIds);
if (uploadValidationFailure != null)
{
//There was an error so log it
uploadErrors.Add(uploadValidationFailure);
}
}
return uploadErrors;
}
private List<UploadValidationFailure> ValidateUploadedRow(DataRow row, int rowNumber, AllowedData allowedData)
{
rowNumber = rowNumber + 2;
var duv = new DataUploadValidation();
Exception dataConversionException;
var validationFailures = new List<UploadValidationFailure>();
//Validate the IndicatorID
string columnName = UploadColumnNames.IndicatorId;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName, null));
}
//Validate the Indicator
UploadValidationFailure uploadValidationFailure = duv.ValidateIndicator(row, rowNumber);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
//Validate the Year Data Type
columnName = UploadColumnNames.Year;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Ensure this is a valid Year value
uploadValidationFailure = duv.ValidateYear(row, rowNumber);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the YearRange
columnName = UploadColumnNames.YearRange;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
uploadValidationFailure = duv.ValidateYearRange(row, rowNumber);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the Quarter Data Type
columnName = UploadColumnNames.Quarter;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Validate the Quarter Value
uploadValidationFailure = duv.ValidateQuarter(row, rowNumber);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the Month Data Type
columnName = UploadColumnNames.Month;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Validate the Quarter Value
uploadValidationFailure = duv.ValidateMonth(row, rowNumber);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the AgeId Data Type
columnName = UploadColumnNames.AgeId;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Validate the Age Id Value
uploadValidationFailure = duv.ValidateAgeId((int)row.Field<double>(columnName), rowNumber,
allowedData.AgeIds);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the SexId Data Type
columnName = UploadColumnNames.SexId;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Ensure this is a valid Sex Id in the DB
uploadValidationFailure = duv.ValidateSexId((int)row.Field<double>(columnName), rowNumber,
allowedData.SexIds);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the AreaCode
columnName = UploadColumnNames.AreaCode;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.String);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Ensure this is a valid Area Code in the DB
uploadValidationFailure = duv.ValidateArea(row, rowNumber, allowedData.AreaCodes);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
//Validate the Count
columnName = UploadColumnNames.Count;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.NullableDouble);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the Value
columnName = UploadColumnNames.Value;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Double);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the LowerCI
columnName = UploadColumnNames.LowerCI;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Double);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the UpperCI
columnName = UploadColumnNames.UpperCI;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Double);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the Denominator
columnName = UploadColumnNames.Denominator;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Double);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the Denominator_2
columnName = UploadColumnNames.Denominator2;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Double);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
//Validate the ValueNoteId
columnName = UploadColumnNames.ValueNoteId;
dataConversionException = duv.ValidateExpectedDataType(row, columnName,
DataUploadValidation.DataType.Integer);
if (dataConversionException != null)
{
validationFailures.Add(new UploadValidationFailure(rowNumber, columnName, "Invalid " + columnName,
dataConversionException.Message));
}
else
{
//Ensure this is a value note id in the DB
uploadValidationFailure = duv.ValidateValueNoteId((int)row.Field<double>(columnName), rowNumber,
allowedData.ValueNoteIds);
if (uploadValidationFailure != null)
{
//There was an error so log it
validationFailures.Add(uploadValidationFailure);
}
}
// Validate CategoryTypeId & CategoryId
uploadValidationFailure = duv.ValidateCategoryTypeIdAndCategoryId(row, rowNumber, allowedData.Categories);
if (uploadValidationFailure != null)
{
validationFailures.Add(uploadValidationFailure);
}
return validationFailures;
}
