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; }