private static RowLoadResult LoadRow(ExcelWorksheet worksheet, TableDefinition tableDefinition, Dictionary <string, int> headerDictionary, int row, bool shouldCheckType)
{
RowLoadResult rowResult = new RowLoadResult
{
Fields = new Dictionary <string, object>()
};
foreach (var fieldDefinition in tableDefinition.FieldDefinitions)
{
if (headerDictionary.ContainsKey(fieldDefinition.Name))
{
ExcelRange dataCell = worksheet.Cells[row, headerDictionary[fieldDefinition.Name]];
PopulateField(fieldDefinition, rowResult, dataCell, shouldCheckType);
if (fieldDefinition.IdentifierFieldType.HasValue &&
fieldDefinition.IdentifierFieldType.Value != IdentifierFieldType.None &&
string.IsNullOrWhiteSpace(rowResult.Identifier))
{
rowResult.Identifier = rowResult.Fields[fieldDefinition.Name]?.ToString();
rowResult.IdentifierFieldType = fieldDefinition.IdentifierFieldType.Value;
}
}
}
return(rowResult);
}
private static TableLoadResultWithHeaders ConvertSheetToObjects(ExcelWorksheet worksheet, TableDefinition tableDefinition, bool parse = true)
{
TableLoadResultWithHeaders result = new TableLoadResultWithHeaders()
{
TableLoadResult = new TableLoadResult
{
TableDefinition = tableDefinition,
Rows = new List <RowLoadResult>()
},
RetrievedHeaderFields = new Dictionary <string, int>()
};
IOrderedEnumerable <int> rows = worksheet.Cells
.Select(cell => cell.Start.Row)
.Distinct()
.OrderBy(x => x);
var headerDictionary = MatchHeaderColumns(worksheet, tableDefinition);
foreach (var row in rows.Skip(1))
{
RowLoadResult rowResult = LoadRow(worksheet, tableDefinition, headerDictionary, row, parse);
result.TableLoadResult.Rows.Add(rowResult);
}
if (!headerDictionary.IsNullOrEmpty())
{
result.RetrievedHeaderFields = headerDictionary;
}
return(result);
}
public void PopulateField_GivenDefinitionsAndNullOrEmptyValues_EnsuresCorrectType(FieldType fieldType, object cellValue)
{
//Arrange
FieldDefinition fieldDefinition = new FieldDefinition
{
Name = "test-name",
Type = fieldType
};
RowLoadResult rowLoadResult = new RowLoadResult
{
Fields = new Dictionary <string, object>()
};
ExcelRange excelRange = CreateExcelRange(cellValue);
//Act
ExcelDatasetReader.PopulateField(fieldDefinition, rowLoadResult, excelRange, true);
//Assert
rowLoadResult
.Fields
.Should()
.HaveCount(1);
rowLoadResult
.Fields
.First()
.Value
.Should()
.BeNull();
}
public static void PopulateField(FieldDefinition fieldDefinition, RowLoadResult rowResult, ExcelRange dataCell, bool shouldCheckType)
{
if (!shouldCheckType)
{
rowResult.Fields.Add(fieldDefinition.Name, dataCell.Value);
}
else
{
switch (fieldDefinition.Type)
{
case FieldType.Boolean:
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <bool>());
break;
case FieldType.Integer:
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <int?>());
break;
case FieldType.Float:
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <double?>());
break;
case FieldType.Decimal:
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <decimal?>());
break;
case FieldType.DateTime:
try
{
string valueAsString = dataCell.GetValue <string>();
if (!string.IsNullOrWhiteSpace(valueAsString))
{
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <DateTime>());
}
}
catch (InvalidCastException)
{
}
break;
case FieldType.NullableOfDecimal:
dataCell.GetValue <string>().TryParseNullable(out decimal? parsedDecimal);
rowResult.Fields.Add(fieldDefinition.Name, parsedDecimal);
break;
case FieldType.NullableOfInteger:
dataCell.GetValue <string>().TryParseNullable(out int?parsedInt);
rowResult.Fields.Add(fieldDefinition.Name, parsedInt);
break;
default:
rowResult.Fields.Add(fieldDefinition.Name, dataCell.GetValue <string>());
break;
}
}
}
private IList <Field> RetrieveAllFields(List <RowLoadResult> allRows, IList <FieldDefinition> fieldDefinitions, IDictionary <string, int> headersAndColumns)
{
IList <Field> allFieldsToBeValidated = new List <Field>();
for (int rowIndex = 2, index = 0; index < allRows.Count; rowIndex++, index++)
{
RowLoadResult row = allRows[index];
foreach (KeyValuePair <string, object> fieldKeyValue in row.Fields)
{
Field field = GetFieldFromKeyValuePair(fieldDefinitions, rowIndex, fieldKeyValue, headersAndColumns);
allFieldsToBeValidated.Add(field);
}
}
return(allFieldsToBeValidated);
}
private static IEnumerable <string> GetProviderIdsForIdentifier(DatasetDefinition datasetDefinition, RowLoadResult row, IEnumerable <ProviderSummary> providerSummaries)
{
IEnumerable <FieldDefinition> identifierFields = datasetDefinition.TableDefinitions?.First().FieldDefinitions.Where(x => x.IdentifierFieldType.HasValue);
foreach (FieldDefinition field in identifierFields)
{
if (!string.IsNullOrWhiteSpace(field.Name))
{
if (row.Fields.ContainsKey(field.Name))
{
string identifier = row.Fields[field.Name]?.ToString();
if (!string.IsNullOrWhiteSpace(identifier))
{
Dictionary <string, List <string> > lookup = GetDictionaryForIdentifierType(field.IdentifierFieldType, identifier, providerSummaries);
if (lookup.TryGetValue(identifier, out List <string> providerIds))
{
return(providerIds);
}
}
else
{
// For debugging only
//_logger.Debug("Found identifier with null or emtpy string for provider");
}
}
}
}
return(new string[0]);
}
private DatasetAggregations GenerateAggregations(DatasetDefinition datasetDefinition, TableLoadResult tableLoadResult, string specificationId, Reference datasetRelationship)
{
DatasetAggregations datasetAggregations = new DatasetAggregations
{
SpecificationId = specificationId,
DatasetRelationshipId = datasetRelationship.Id,
Fields = new List <AggregatedField>()
};
string identifierPrefix = $"Datasets.{DatasetTypeGenerator.GenerateIdentifier(datasetRelationship.Name)}";
IEnumerable <FieldDefinition> fieldDefinitions = datasetDefinition.TableDefinitions.SelectMany(m => m.FieldDefinitions);
RowLoadResult rowLoadResult = tableLoadResult.Rows.FirstOrDefault();
if (rowLoadResult != null)
{
foreach (KeyValuePair <string, object> field in rowLoadResult.Fields)
{
FieldDefinition fieldDefinition = fieldDefinitions.FirstOrDefault(m => m.Name == field.Key);
string fieldName = fieldDefinition.Name;
if (fieldDefinition.IsAggregable && fieldDefinition.IsNumeric)
{
string identifierName = $"{identifierPrefix}.{DatasetTypeGenerator.GenerateIdentifier(fieldName)}";
decimal sum = tableLoadResult.Rows.SelectMany(m => m.Fields.Where(f => f.Key == fieldName)).Sum(s => s.Value != null ? Convert.ToDecimal(s.Value) : 0);
decimal average = tableLoadResult.Rows.SelectMany(m => m.Fields.Where(f => f.Key == fieldName)).Average(s => s.Value != null ? Convert.ToDecimal(s.Value) : 0);
decimal min = tableLoadResult.Rows.SelectMany(m => m.Fields.Where(f => f.Key == fieldName)).Min(s => s.Value != null ? Convert.ToDecimal(s.Value) : 0);
decimal max = tableLoadResult.Rows.SelectMany(m => m.Fields.Where(f => f.Key == fieldName)).Max(s => s.Value != null ? Convert.ToDecimal(s.Value) : 0);
IList <AggregatedField> aggregatedFields = new List <AggregatedField>
{
new AggregatedField
{
FieldDefinitionName = identifierName,
FieldType = AggregatedType.Sum,
Value = sum
},
new AggregatedField
{
FieldDefinitionName = identifierName,
FieldType = AggregatedType.Average,
Value = average
},
new AggregatedField
{
FieldDefinitionName = identifierName,
FieldType = AggregatedType.Min,
Value = min
},
new AggregatedField
{
FieldDefinitionName = identifierName,
FieldType = AggregatedType.Max,
Value = max
}
};
datasetAggregations.Fields = datasetAggregations.Fields.Concat(aggregatedFields);
}
}
}
return(datasetAggregations);
}
