Untitled
/// <summary> /// Queries fact_tables view for the rows with same dim_indikator_keys, dim_region_keys, and år as requested in query. /// Retrieves requested historical data for kommuner if the requested kommune has no structure change. /// <param name="query"></param> /// <param name="paginationParameters"></param> /// <param name="sortAttribute"></param> /// <param name="sortDirection"></param> /// <returns><see cref="List{T}">List<FactView></see></returns> public async Task<QueryFactTableWithPaginationResponse> QueryFactView(DimIndikator indicator, IndicatorQueryFactFormat query, PaginationParameters paginationParameters = null, string sortAttribute = "år", string sortDirection = "desc") { var dimRegionKeysToQuery = query.DimRegionKeys.ToHashSet(); var factTableQuery = _dataContext.FactTablesView.Where( i => i.DimIndikatorKey == query.IndikatorId && query.År.Contains(i.År) && dimRegionKeysToQuery.Any(j => j == i.DimRegionKey)); var totalCountOfRows = factTableQuery.Count(); if (totalCountOfRows > _maximumResponseRows) throw new DataOverflowException($"Your request contains {totalCountOfRows} values, which exceeds the maximum amount of {_maximumResponseRows}."); var regions = await _regionService.GetRegionsByIds(dimRegionKeysToQuery.ToList()); var municipals = regions.Where(i => i.Type == "Kommune").ToList(); /*TODO: handle case where user requests new and old kommune. e.g. Request dim_region_keys 2027 (Askvoll kommunenummer 4645) and 2088 (Askvoll kommunenummer 1428). Our logic retrieves data for 2027, and sets results to 2027. Maybe just avoid logic around retrieving data for old muncipals in this case. */ var oldAndRequestedMunicipalRegionKeyValuePairs = new Dictionary<int, int>(); foreach (var municipal in municipals) { var municipalityNumbersToQuery = (await GetMunicipalitiesToQuery(municipal.Kommunenummer)).Where(x => x.DimRegionKey != municipal.DimRegionKey); foreach (var municipalityNumber in municipalityNumbersToQuery) { dimRegionKeysToQuery.Add(municipalityNumber.DimRegionKey); oldAndRequestedMunicipalRegionKeyValuePairs.TryAdd(municipalityNumber.DimRegionKey, municipal.DimRegionKey); } } var factTableQueryResult = await factTableQuery.ToListAsync(); if (oldAndRequestedMunicipalRegionKeyValuePairs.Count > 0) { foreach (var fact in factTableQueryResult.Where(x => oldAndRequestedMunicipalRegionKeyValuePairs.ContainsKey(x.DimRegionKey))) { var newKey = oldAndRequestedMunicipalRegionKeyValuePairs[fact.DimRegionKey]; fact.DimRegionKey = newKey; } //Group by all columns, but verdi - use the highest verdi (the verdi in the group not being null) var deduplicated = from i in factTableQueryResult group i by new { i.DimIndikatorKey, i.DimRegionKey, i.År, i.Alder, i.Eierform, i.Barnehagestr, i.Barnevernstiltak, i.Kjønn, i.Prøve, i.Årstrinn, i.Fullføringsgrad, i.Husdyrslag, i.Funksjon, i.Søknadstype, i.Vedtakstype, i.Landbakgrunn, i.Innvandringskategori, i.Samletstatus, i.Gjennomførtår, i.Art, i.Regnskapsbegrep, i.Typesykelighet, i.Tjenestetype, i.Utdanning, i.Tjenestegrupper, i.Arbeidsstyrkestatus, i.Tettbygdellerspredtbygd, i.Utdanningsnivå, i.Næringsn2007, i.Sektor, i.Fagutdanning, i.Prioritertarbeidsstyrkestatus, i.Alternativ, i.Nøkkeltall, i.Aktivitet, i.Ressurs, i.Typeorganisasjon, i.Funksjonsproblem, i.Friluftslivsaktivitet, i.Typesosialkontakt, i.Levevane, i.Treningsogmosjonsaktivitet, i.Foreldrenesutdanningsnivaa, i.Kvartal, i.Behandlingsinstansogbehandlingsresultat, i.Reguleringsplanersomdetklagespaa, i.Årsaktilklage, i.Avtaleform, i.Bygningstype, i.Bygningsår, i.Dyr, i.Årsak, i.Sortiment, i.Elv, i.Bruk, i.Verneformål, i.Typebomiljø, i.Familiefase, i.Landsdel, i.Familietype, i.Barnehagetype, i.Bosetting, i.Kategori, i.Tjeneste, i.Innsigelsesmyndighet, i.Begrunnelserbruktforinnsigelser, i.Spørsmål, i.Arealbrukskategori, i.Sosioøkonomiskeressurser, i.Helseundersøkelser_helsekonsultasjoner } into grouped select grouped; factTableQueryResult = deduplicated .Select(x => x.FirstOrDefault(fact => fact.Verdi is not 0m && fact.Verdi is not null) ?? x.FirstOrDefault(fact => fact.Verdi is not null) ?? x.FirstOrDefault()) .OrderBy(factView => factView?.År) .ToList(); } totalCountOfRows = factTableQueryResult.Count; var factTableSortedAndPaginated = factTableQueryResult.AsQueryable().Sort(sortAttribute, sortDirection, regions); if (paginationParameters != null) factTableSortedAndPaginated = factTableSortedAndPaginated.Skip((paginationParameters.PageNumber - 1) * paginationParameters.PageSize).Take(paginationParameters.PageSize); if (!factTableSortedAndPaginated.Any()) throw new DataNotFoundException(""); if (paginationParameters == null) return new QueryFactTableWithPaginationResponse { FactValues = factTableSortedAndPaginated.ToList() }; return new QueryFactTableWithPaginationResponse { CurrentPage = paginationParameters.PageNumber, RequestedPageSize = paginationParameters.PageSize, TotalRowsInQuery = totalCountOfRows, FactValues = factTableSortedAndPaginated.ToList() }; }
Leave a Comment