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);
        }
Exemple #3
0
        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;
                }
            }
        }
Exemple #5
0
        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);
        }
Exemple #6
0
        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]);
        }
Exemple #7
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);
        }