private InspectionsStatusCode ProcessLineIntoDetailsAsDetailOrError(ExcelWorksheet keyWorksheet,
SpreadsheetDetails spreadsheetDetails,
int lineNumber,
ICollection <InspectionOutcome> inspections, ICollection <InspectionError> errorSet)
{
var error = new InspectionError {
LineNumber = lineNumber
};
var ukprn = ProcessUkprnForError(Convert.ToString(keyWorksheet.Cells[lineNumber, spreadsheetDetails.UkPrnColumn].Value), error);
var url = _processExcelFormulaToLink.GetLinkFromFormula(
keyWorksheet.Cells[lineNumber, spreadsheetDetails.WebLinkColumn].Formula,
keyWorksheet.Cells[lineNumber, spreadsheetDetails.WebLinkColumn].Text);
var overallEffectiveness = ProcessOverallEffectivenessForError(Convert.ToString(keyWorksheet.Cells[lineNumber, spreadsheetDetails.OverallEffectivenessColumn]?.Value), error);
var datePublished = ProcessDatePublishedForError(keyWorksheet.Cells[lineNumber, spreadsheetDetails.DatePublishedColumn], error);
if (ukprn != null && overallEffectiveness != null && datePublished != null)
{
AddInspectionData((int)ukprn, url, datePublished, (OverallEffectiveness)overallEffectiveness,
inspections);
_logger.Debug($"Details processed successfully for line {lineNumber}: {ukprn}, {url}, {error.DatePublished}, {overallEffectiveness}");
return(InspectionsStatusCode.Success);
}
error.Website = url ?? string.Empty;
errorSet.Add(error);
_logger.Warn($"Details processed unsuccessfully for line {error.LineNumber}: '{error.Ukprn}', '{error.Website}', '{error.DatePublished}', '{error.OverallEffectiveness}'");
return(InspectionsStatusCode.ProcessedWithErrors);
}
private SpreadsheetDetails GetSpreadsheetColumnAndRowDetails(ExcelWorksheet keyWorksheet)
{
var spreadsheetDetails = new SpreadsheetDetails();
var rowNumber = keyWorksheet.Dimension.Start.Row;
var maxRow = keyWorksheet.Dimension.End.Row;
var maxCol = keyWorksheet.Dimension.End.Column;
while (rowNumber <= maxRow)
{
for (var columnNumber = 1; columnNumber <= maxCol && !spreadsheetDetails.AreAllColumnHeadingsMatched; columnNumber++)
{
var cellValue = keyWorksheet.Cells[rowNumber, columnNumber]?.Value?.ToString().Trim();
if (string.Equals(cellValue, _configurationSettings.WebLinkHeading, StringComparison.OrdinalIgnoreCase))
{
spreadsheetDetails.WebLinkColumn = columnNumber;
}
if (string.Equals(cellValue, _configurationSettings.UkPrnHeading, StringComparison.OrdinalIgnoreCase))
{
spreadsheetDetails.UkPrnColumn = columnNumber;
}
if (string.Equals(cellValue, _configurationSettings.DatePublishedHeading, StringComparison.OrdinalIgnoreCase))
{
spreadsheetDetails.DatePublishedColumn = columnNumber;
}
if (string.Equals(cellValue, _configurationSettings.OverallEffectivenessHeading, StringComparison.OrdinalIgnoreCase))
{
spreadsheetDetails.OverallEffectivenessColumn = columnNumber;
}
}
rowNumber++;
if (spreadsheetDetails.AreAllColumnHeadingsMatched)
{
spreadsheetDetails.DataStartsRow = rowNumber;
break;
}
}
return(spreadsheetDetails);
}