public UploadDataModel GetUploadDataModelWithUnparsedValuesSetToDefaults(SimpleUpload simpleUpload)
{
var upload = GetUploadDataModel(simpleUpload);
upload.Denominator_2 = UndefinedDouble;
return upload;
}
public static bool DoesIndicatorMetaDataExist(SimpleUpload simpleUpload)
{
var indicatorMetaData = Reader.GetIndicatorMetadata(simpleUpload.IndicatorId);
if (indicatorMetaData != null)
{
simpleUpload.YearTypeId = indicatorMetaData.YearTypeId;
return true;
}
return false;
}
public DataTable RemoveDuplicatesInSimple(DataTable pholioData)
{
var simpleUpload = new SimpleUpload();
var dataToBeUploaded = new List<UploadDataModel>();
for (var i = 0; i < pholioData.Rows.Count; i++)
{
var 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;
}
var uploadDataModel = rowParser.GetUploadDataModelWithUnparsedValuesSetToDefaults(simpleUpload);
dataToBeUploaded.Add(uploadDataModel);
}
// Remove the duplicate
var noDupData =
dataToBeUploaded.GroupBy(
x =>
new
{
x.AreaCode
})
.Select(y => y.First())
.ToList();
// Create new table without duplicats
var table = new DataTable();
table.Columns.Add("AreaCode", typeof(string));
table.Columns.Add("Count", typeof(double));
table.Columns.Add("Value", typeof(double));
table.Columns.Add("LowerCI", typeof(double));
table.Columns.Add("UpperCI", typeof(double));
table.Columns.Add("Denominator", typeof(double));
table.Columns.Add("ValueNoteId", typeof(double));
foreach (var data in noDupData)
{
table.Rows.Add(data.AreaCode, data.Count, data.Value, data.LowerCi, data.UpperCi, data.Denominator,
data.ValueNoteId);
}
return table;
}
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 string Translate(SimpleUpload simpleUpload)
{
var period = new TimePeriod
{
Year = simpleUpload.Year,
YearRange = simpleUpload.YearRange,
Quarter = simpleUpload.Quarter,
Month = simpleUpload.Month
};
string periodString = TimePeriodReader.GetPeriodFromWebService(period,
simpleUpload.YearTypeId);
return TranslateMonth(simpleUpload) + " " + TranslateQuarter(simpleUpload) +
" For Range: " + periodString;
}
public bool Validate(SimpleUpload simpleUpload)
{
OleDbConnection connExcel = UploadFileHelper.OpenSpreadsheet(simpleUpload.FileName, false);
List<UploadExcelSheet> excelDataSheets = UploadFileHelper.GetAvailableWorksheets(connExcel);
connExcel.Close();
var areValidWorksheets = ValidateSimpleUploadWorksheetsExist(excelDataSheets);
if (!areValidWorksheets) return false;
// simple upload values from excel file
simpleUpload.SelectedWorksheet = WorksheetNames.SimpleIndicator;
simpleUpload.ExcelDataSheets = excelDataSheets;
simpleUpload.UploadBatchId = simpleUpload.UploadBatchId;
return true;
}
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;
}
private static string TranslateQuarter(SimpleUpload simpleUpload)
{
switch (simpleUpload.YearTypeId)
{
case 1: //Calendar
switch (simpleUpload.Quarter)
{
case -1:
return null;
case 1:
return "January to March - " + simpleUpload.Year;
case 2:
return "April to June - " + simpleUpload.Year;
case 3:
return "July to September - " + simpleUpload.Year;
case 4:
return "October to December - " + simpleUpload.Year;
}
break;
case 2: //Financial
switch (simpleUpload.Quarter)
{
case -1:
return null;
case 1:
return "April to June - " + simpleUpload.Year;
case 2:
return "July to September - " + simpleUpload.Year;
case 3:
return "October to December - " + simpleUpload.Year;
case 4:
return "January to March - " + simpleUpload.Year++;
}
break;
}
return null;
}
public UploadDataModel GetUploadDataModel(SimpleUpload simpleUpload)
{
var upload = new UploadDataModel
{
IndicatorId = simpleUpload.IndicatorId,
Year = simpleUpload.Year,
YearRange = simpleUpload.YearRange,
Quarter = simpleUpload.Quarter,
Month = simpleUpload.Month,
AgeId = simpleUpload.AgeId,
SexId = simpleUpload.SexId,
AreaCode = AreaCode,
Count = Count,
Value = Value,
LowerCi = LowerCI,
UpperCi = UpperCI,
Denominator = Denominator,
ValueNoteId = ValueNoteId,
CategoryId = UndefinedInt,
CategoryTypeId = UndefinedInt
};
return upload;
}
private SimpleUpload ToSimpleUpload(UploadJob job)
{
var simpleUpload = new SimpleUpload
{
ShortFileName = job.Filename,
FileName = job.Filename,
DataToUpload = new List<UploadDataModel>(),
DuplicateRowInDatabaseErrors = new List<DuplicateRowInDatabaseError>(),
DuplicateRowInSpreadsheetErrors = new List<DuplicateRowInSpreadsheetError>(),
ExcelDataSheets = new List<UploadExcelSheet>(),
UploadValidationFailures = new List<UploadValidationFailure>()
};
return simpleUpload;
}
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 static bool ValidateSpreadsheetForDuplicatedRows(SimpleUpload spreadsheet)
{
IEnumerable<UploadDataModel> validateForRepetition =
spreadsheet.DataToUpload.Where(
t =>
spreadsheet.DataToUpload.Count(
x =>
x.IndicatorId == t.IndicatorId && x.Year == t.Year && x.YearRange == t.YearRange &&
x.Quarter == t.Quarter && x.Month == t.Month && x.AgeId == t.AgeId && x.SexId == t.SexId &&
x.AreaCode == t.AreaCode) > 1); // Category IDs not present in simple upload
if (validateForRepetition.Any())
{
foreach (UploadDataModel row in validateForRepetition)
{
var duplicate = new DuplicateRowInSpreadsheetError
{
RowNumber = row.RowNumber,
DuplicateRowMessage = "Area " + row.AreaCode + " is duplicated. "
};
spreadsheet.DuplicateRowInSpreadsheetErrors.Add(duplicate);
}
return true;
}
return false;
}
private static void GetIndicatorValue(DataTable excelData, SimpleUpload simpleUpload)
{
try
{
simpleUpload.IndicatorId = int.Parse(excelData.Rows[0].ItemArray[1].ToString());
if (!DataUploadValidation.DoesIndicatorMetaDataExist(simpleUpload))
{
simpleUpload.UploadValidationFailures.Add(new UploadValidationFailure(null, "IndicatorId",
"Indicator does not exist", null));
}
}
catch (Exception)
{
//Invalid conversion error
simpleUpload.UploadValidationFailures.Add(new UploadValidationFailure(null, "IndicatorId", "Invalid indicator ID", null));
}
}
private static void GetYearValue(DataTable excelData, SimpleUpload simpleUpload)
{
try
{
simpleUpload.Year = int.Parse(excelData.Rows[1].ItemArray[1].ToString());
}
catch (Exception)
{
//Invalid conversion error
new UploadValidationFailure(null, "StartYear", "Invalid Start Year", null);
}
}
private static void GetMonth(DataTable excelData, SimpleUpload simpleUpload)
{
try
{
simpleUpload.Month = int.Parse(excelData.Rows[4].ItemArray[1].ToString());
}
catch (Exception)
{
//Invalid conversion error
new UploadValidationFailure(null, "Month", "Invalid Month Value", null);
}
}
private static void GetAgeId(DataTable excelData, SimpleUpload simpleUpload)
{
try
{
simpleUpload.AgeId = int.Parse(excelData.Rows[5].ItemArray[1].ToString());
}
catch (Exception)
{
//Invalid conversion error
new UploadValidationFailure(null, "AgeId", "Invalid Age Id", null);
}
}
// 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 CheckDuplicateRowsInWorksheet(UploadJob job, SimpleUpload simpleUpload, ref DataTable pholioData)
{
if (simpleUpload.DuplicateRowInSpreadsheetErrors.Count > 0)
{
var dataWithoutDuplicates = new FileDuplicationHandler().RemoveDuplicatesInSimple(pholioData);
pholioData = dataWithoutDuplicates;
_logger.Info("Job# {0}, There are duplicate rows in spreadsheet", job.Guid);
_logger.Info("Job# {0}, Dupllicate rows removed", job.Guid);
}
}
private bool CheckValidationFailures(UploadJob job, SimpleUpload simpleUpload)
{
if (simpleUpload.UploadValidationFailures.Count <= 0) return true;
jobStatus.FailedValidation(job);
var error = ErrorBuilder.GetConversionError(job.Guid, simpleUpload.UploadValidationFailures);
_jobErrorRepository.Log(error);
_logger.Info("Job# {0}, Data type conversion errors occurred ", job.Guid);
return false;
}
private static string TranslateMonth(SimpleUpload simpleUpload)
{
switch (simpleUpload.YearTypeId)
{
case 1: //Calendar
switch (simpleUpload.Month)
{
case -1:
return null;
case 1:
return "January " + simpleUpload.Year;
case 2:
return "February " + simpleUpload.Year;
case 3:
return "March " + simpleUpload.Year;
case 4:
return "April " + simpleUpload.Year;
case 5:
return "May " + simpleUpload.Year;
case 6:
return "June " + simpleUpload.Year;
case 7:
return "July " + simpleUpload.Year;
case 8:
return "August " + simpleUpload.Year;
case 9:
return "September " + simpleUpload.Year;
case 10:
return "October " + simpleUpload.Year;
case 11:
return "November " + simpleUpload.Year;
case 12:
return "December " + simpleUpload.Year;
}
break;
case 2: //Financial
switch (simpleUpload.Month)
{
case -1:
return null;
case 1:
simpleUpload.Month = 10;
return " January - " + simpleUpload.Year;
case 2:
simpleUpload.Month = 11;
return " February - " + simpleUpload.Year;
case 3:
simpleUpload.Month = 12;
return " March - " + simpleUpload.Year;
case 4:
simpleUpload.Month = 1;
return " April - " + simpleUpload.Year;
case 5:
simpleUpload.Month = 2;
return " May - " + simpleUpload.Year;
case 6:
simpleUpload.Month = 3;
return " June - " + simpleUpload.Year;
case 7:
simpleUpload.Month = 4;
return " July - " + simpleUpload.Year;
case 8:
simpleUpload.Month = 5;
return " August - " + simpleUpload.Year;
case 9:
simpleUpload.Month = 6;
return " September - " + simpleUpload.Year;
case 10:
simpleUpload.Month = 7;
return " October - " + simpleUpload.Year++;
case 11:
simpleUpload.Month = 8;
return " November - " + simpleUpload.Year++;
case 12:
simpleUpload.Month = 9;
return " December - " + simpleUpload.Year++;
}
break;
}
return null;
}
private static SimpleUpload GetSimpleUpload()
{
var simpleUpload = new SimpleUpload
{
IndicatorId = indicatorId,
Year = year,
YearRange = yearRange,
Quarter = quarter,
Month = month,
AgeId = ageId,
SexId = sexId
};
return simpleUpload;
}
private bool CheckDuplicateRowsInDatabase(UploadJob job, SimpleUpload simpleUpload)
{
if (simpleUpload.DuplicateRowInDatabaseErrors.Count <= 0) return false;
jobStatus.ConfirmationAwaited(job);
var error = ErrorBuilder.GetDuplicateRowInDatabaseError(job.Guid,
simpleUpload.DuplicateRowInDatabaseErrors);
_jobErrorRepository.Log(error);
_logger.Info("Job# {0}, There are duplicate rows in database", job.Guid);
return true;
}