public BoundaryLevel FindLatestByGeographicLevel(GeographicLevel geographicLevel)
{
var boundaryLevel = FindMany(level => level.Level == geographicLevel)
.OrderByDescending(level => level.Published)
.FirstOrDefault();
return(boundaryLevel);
}
public static TableHeader NewLocationHeader(GeographicLevel level, string value)
{
return(new TableHeader
{
Level = level.ToString().CamelCase(),
Value = value,
Type = TableHeaderType.Location
});
}
private static IObservationalUnit GetObservationalUnit(GeographicLevel geographicLevel, Location location)
{
switch (geographicLevel)
{
case GeographicLevel.LocalAuthority:
return(location.LocalAuthority);
case GeographicLevel.LocalAuthorityDistrict:
return(location.LocalAuthorityDistrict);
case GeographicLevel.LocalEnterprisePartnership:
return(location.LocalEnterprisePartnership);
case GeographicLevel.Institution:
return(location.Institution);
case GeographicLevel.MayoralCombinedAuthority:
return(location.MayoralCombinedAuthority);
case GeographicLevel.MultiAcademyTrust:
return(location.MultiAcademyTrust);
case GeographicLevel.Country:
return(location.Country);
case GeographicLevel.OpportunityArea:
return(location.OpportunityArea);
case GeographicLevel.ParliamentaryConstituency:
return(location.ParliamentaryConstituency);
case GeographicLevel.Region:
return(location.Region);
case GeographicLevel.RscRegion:
return(location.RscRegion);
case GeographicLevel.Sponsor:
return(location.Sponsor);
case GeographicLevel.Ward:
return(location.Ward);
case GeographicLevel.PlanningArea:
return(location.PlanningArea);
default:
throw new ArgumentOutOfRangeException();
}
}
private static string GetCacheKey(
GeographicLevel geographicLevel,
Country country,
EnglishDevolvedArea?englishDevolvedArea,
Institution?institution,
LocalAuthority?localAuthority,
LocalAuthorityDistrict?localAuthorityDistrict,
LocalEnterprisePartnership?localEnterprisePartnership,
MayoralCombinedAuthority?mayoralCombinedAuthority,
Mat?multiAcademyTrust,
OpportunityArea?opportunityArea,
ParliamentaryConstituency?parliamentaryConstituency,
PlanningArea?planningArea,
Provider?provider,
Region?region,
RscRegion?rscRegion,
School?school,
Sponsor?sponsor,
Ward?ward)
{
var locationAttributes = new ILocationAttribute?[]
{
country,
englishDevolvedArea,
institution,
localAuthority,
localAuthorityDistrict,
localEnterprisePartnership,
mayoralCombinedAuthority,
multiAcademyTrust,
parliamentaryConstituency,
planningArea,
provider,
opportunityArea,
region,
rscRegion,
school,
sponsor,
ward
};
var tokens = locationAttributes
.WhereNotNull()
.Select(attribute => attribute.GetCacheKey())
.ToList();
const char separator = '_';
return($"{geographicLevel}{separator}{tokens.JoinToString(separator)}");
}
private LocationReplacementViewModel ValidateLocationLevelForReplacement(
LocationQuery locationQuery,
GeographicLevel geographicLevel,
ReplacementSubjectMeta replacementSubjectMeta)
{
var queryProperty = typeof(LocationQuery).GetProperty(geographicLevel.ToString());
if (queryProperty == null || queryProperty.GetMethod == null)
{
throw new ArgumentException(
$"{nameof(locationQuery)} does not have a property {geographicLevel.ToString()} with get method");
}
var originalCodes = (
queryProperty.GetMethod.Invoke(locationQuery, new object[] { }) as IEnumerable <string> ?? new List <string>()
).ToList();
if (!originalCodes.Any())
{
return(new LocationReplacementViewModel(
label: geographicLevel.GetEnumLabel(),
observationalUnits: new List <ObservationalUnitReplacementViewModel>()
));
}
var locations = _locationService.GetObservationalUnits(geographicLevel, originalCodes);
var replacementLocations = replacementSubjectMeta.ObservationalUnits
.GetValueOrDefault(geographicLevel)
?.ToDictionary(location => location.Code) ?? new Dictionary <string, IObservationalUnit>();
return(new LocationReplacementViewModel(
label: geographicLevel.GetEnumLabel(),
observationalUnits: locations
.Select(location => ValidateLocationForReplacement(location, replacementLocations))
.OrderBy(location => location.Label, LabelComparer)
));
}
protected IEnumerable <ObservationalUnitMetaViewModel> BuildObservationalUnitMetaViewModelsWithGeoJsonIfAvailable(
GeographicLevel geographicLevel,
ICollection <IObservationalUnit> observationalUnits,
bool geoJsonRequested,
long?boundaryLevelId)
{
var geoJsonByCode = new Dictionary <string, GeoJson>();
if (geoJsonRequested)
{
var boundaryLevel = boundaryLevelId ?? GetBoundaryLevel(geographicLevel)?.Id;
if (boundaryLevel.HasValue)
{
var codes = observationalUnits.Select(unit =>
unit is LocalAuthority localAuthority ? localAuthority.GetCodeOrOldCodeIfEmpty() : unit.Code);
geoJsonByCode = _geoJsonService.Find(boundaryLevel.Value, codes).ToDictionary(g => g.Code);
}
}
return(observationalUnits.Select(observationalUnit =>
{
var value = observationalUnit is LocalAuthority localAuthority
? localAuthority.GetCodeOrOldCodeIfEmpty()
: observationalUnit.Code;
var serializedGeoJson = geoJsonByCode.GetValueOrDefault(value);
var geoJson = DeserializeGeoJson(serializedGeoJson);
return new ObservationalUnitMetaViewModel
{
GeoJson = geoJson,
Label = observationalUnit.Name,
Level = geographicLevel,
Value = value
};
}));
}
private IEnumerable <BoundaryLevel> FindByGeographicLevel(GeographicLevel geographicLevel)
{
return(FindMany(level => level.Level == geographicLevel)
.OrderByDescending(level => level.Published));
}
public IEnumerable <IObservationalUnit> GetObservationalUnits(GeographicLevel level, IEnumerable <string> codes)
{
IQueryable <IObservationalUnit> query = level switch
{
GeographicLevel.LocalAuthority =>
_context.Location
.Where(q => codes.Contains(q.LocalAuthority.Code))
.GroupBy(q => new { q.LocalAuthority.Code, q.LocalAuthority.OldCode, q.LocalAuthority.Name })
.Select(q => new LocalAuthority(q.Key.Code, q.Key.OldCode, q.Key.Name)),
GeographicLevel.LocalAuthorityDistrict =>
_context.Location
.Where(q => codes.Contains(q.LocalAuthorityDistrict.Code))
.GroupBy(q => new { q.LocalAuthorityDistrict.Code, q.LocalAuthorityDistrict.Name })
.Select(q => new LocalAuthorityDistrict(q.Key.Code, q.Key.Name)),
GeographicLevel.LocalEnterprisePartnership =>
_context.Location
.Where(q => codes.Contains(q.LocalEnterprisePartnership.Code))
.GroupBy(q => new { q.LocalEnterprisePartnership.Code, q.LocalEnterprisePartnership.Name })
.Select(q => new LocalEnterprisePartnership(q.Key.Code, q.Key.Name)),
GeographicLevel.Institution =>
_context.Location
.Where(q => codes.Contains(q.Institution.Code))
.GroupBy(q => new { q.Institution.Code, q.Institution.Name })
.Select(q => new Institution(q.Key.Code, q.Key.Name)),
GeographicLevel.MayoralCombinedAuthority =>
_context.Location
.Where(q => codes.Contains(q.MayoralCombinedAuthority.Code))
.GroupBy(q => new { q.MayoralCombinedAuthority.Code, q.MayoralCombinedAuthority.Name })
.Select(q => new MayoralCombinedAuthority(q.Key.Code, q.Key.Name)),
GeographicLevel.MultiAcademyTrust =>
_context.Location
.Where(q => codes.Contains(q.MultiAcademyTrust.Code))
.GroupBy(q => new { q.MultiAcademyTrust.Code, q.MultiAcademyTrust.Name })
.Select(q => new Mat(q.Key.Code, q.Key.Name)),
GeographicLevel.Country =>
_context.Location
.Where(q => codes.Contains(q.Country.Code))
.GroupBy(q => new { q.Country.Code, q.Country.Name })
.Select(q => new Country(q.Key.Code, q.Key.Name)),
GeographicLevel.OpportunityArea =>
_context.Location
.Where(q => codes.Contains(q.OpportunityArea.Code))
.GroupBy(q => new { q.OpportunityArea.Code, q.OpportunityArea.Name })
.Select(q => new OpportunityArea(q.Key.Code, q.Key.Name)),
GeographicLevel.ParliamentaryConstituency =>
_context.Location
.Where(q => codes.Contains(q.ParliamentaryConstituency.Code))
.GroupBy(q => new { q.ParliamentaryConstituency.Code, q.ParliamentaryConstituency.Name })
.Select(q => new ParliamentaryConstituency(q.Key.Code, q.Key.Name)),
GeographicLevel.Region =>
_context.Location
.Where(q => codes.Contains(q.Region.Code))
.GroupBy(q => new { q.Region.Code, q.Region.Name })
.Select(q => new Region(q.Key.Code, q.Key.Name)),
GeographicLevel.RscRegion =>
_context.Location
.Where(q => codes.Contains(q.RscRegion.Code))
.GroupBy(q => new { q.RscRegion.Code })
.Select(q => new RscRegion(q.Key.Code)),
GeographicLevel.Sponsor =>
_context.Location
.Where(q => codes.Contains(q.Sponsor.Code))
.GroupBy(q => new { q.Sponsor.Code, q.Sponsor.Name })
.Select(q => new Sponsor(q.Key.Code, q.Key.Name)),
GeographicLevel.Ward =>
_context.Location
.Where(q => codes.Contains(q.Ward.Code))
.GroupBy(q => new { q.Ward.Code, q.Ward.Name })
.Select(q => new Ward(q.Key.Code, q.Key.Name)),
GeographicLevel.PlanningArea =>
_context.Location
.Where(q => codes.Contains(q.PlanningArea.Code))
.GroupBy(q => new { q.PlanningArea.Code, q.PlanningArea.Name })
.Select(q => new PlanningArea(q.Key.Code, q.Key.Name)),
_ => throw new ArgumentOutOfRangeException()
};
return(query.ToList());
}
public Location FindOrCreate(
StatisticsDbContext context,
GeographicLevel geographicLevel,
Country country,
EnglishDevolvedArea?englishDevolvedArea = null,
Institution?institution = null,
LocalAuthority?localAuthority = null,
LocalAuthorityDistrict?localAuthorityDistrict = null,
LocalEnterprisePartnership?localEnterprisePartnership = null,
MayoralCombinedAuthority?mayoralCombinedAuthority = null,
Mat?multiAcademyTrust = null,
OpportunityArea?opportunityArea = null,
ParliamentaryConstituency?parliamentaryConstituency = null,
PlanningArea?planningArea = null,
Provider?provider = null,
Region?region = null,
RscRegion?rscRegion = null,
School?school = null,
Sponsor?sponsor = null,
Ward?ward = null)
{
var cacheKey = GetCacheKey(
geographicLevel,
country,
englishDevolvedArea,
institution,
localAuthority,
localAuthorityDistrict,
localEnterprisePartnership,
mayoralCombinedAuthority,
multiAcademyTrust,
opportunityArea,
parliamentaryConstituency,
planningArea,
provider,
region,
rscRegion,
school,
sponsor,
ward);
if (_memoryCache.Cache.TryGetValue(cacheKey, out Location location))
{
return(location);
}
location = LookupOrCreate(
context,
geographicLevel,
country,
englishDevolvedArea,
institution,
localAuthority,
localAuthorityDistrict,
localEnterprisePartnership,
mayoralCombinedAuthority,
multiAcademyTrust,
opportunityArea,
parliamentaryConstituency,
planningArea,
provider,
region,
rscRegion,
school,
sponsor,
ward);
_memoryCache.Cache.Set(cacheKey, location);
return(location);
}
private Location?Lookup(
StatisticsDbContext context,
GeographicLevel geographicLevel,
Country country,
EnglishDevolvedArea?englishDevolvedArea,
Institution?institution,
LocalAuthority?localAuthority,
LocalAuthorityDistrict?localAuthorityDistrict,
LocalEnterprisePartnership?localEnterprisePartnership,
MayoralCombinedAuthority?mayoralCombinedAuthority,
Mat?multiAcademyTrust,
OpportunityArea?opportunityArea,
ParliamentaryConstituency?parliamentaryConstituency,
PlanningArea?planningArea,
Provider?provider,
Region?region,
RscRegion?rscRegion,
School?school,
Sponsor?sponsor,
Ward?ward)
{
var predicateBuilder = PredicateBuilder.True <Location>()
.And(location => location.GeographicLevel == geographicLevel);
predicateBuilder = predicateBuilder
.And(location => location.Country_Code == country.Code &&
location.Country_Name == country.Name);
predicateBuilder = predicateBuilder
.And(location => location.EnglishDevolvedArea_Code == (englishDevolvedArea != null ? englishDevolvedArea.Code : null) &&
location.EnglishDevolvedArea_Name == (englishDevolvedArea != null ? englishDevolvedArea.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.Institution_Code == (institution != null ? institution.Code : null) &&
location.Institution_Name == (institution != null ? institution.Name : null));
// Also match the old LA code even if blank
predicateBuilder = predicateBuilder
.And(location =>
location.LocalAuthority_Code == (localAuthority != null && localAuthority.Code != null ? localAuthority.Code : null) &&
location.LocalAuthority_OldCode == (localAuthority != null && localAuthority.OldCode != null ? localAuthority.OldCode : null) &&
location.LocalAuthority_Name == (localAuthority != null ? localAuthority.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.LocalAuthorityDistrict_Code == (localAuthorityDistrict != null ? localAuthorityDistrict.Code : null) &&
location.LocalAuthorityDistrict_Name == (localAuthorityDistrict != null ? localAuthorityDistrict.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.LocalEnterprisePartnership_Code == (localEnterprisePartnership != null ? localEnterprisePartnership.Code : null) &&
location.LocalEnterprisePartnership_Name == (localEnterprisePartnership != null ? localEnterprisePartnership.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.MayoralCombinedAuthority_Code == (mayoralCombinedAuthority != null ? mayoralCombinedAuthority.Code : null) &&
location.MayoralCombinedAuthority_Name == (mayoralCombinedAuthority != null ? mayoralCombinedAuthority.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.MultiAcademyTrust_Code == (multiAcademyTrust != null ? multiAcademyTrust.Code : null) &&
location.MultiAcademyTrust_Name == (multiAcademyTrust != null ? multiAcademyTrust.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.OpportunityArea_Code == (opportunityArea != null ? opportunityArea.Code : null) &&
location.OpportunityArea_Name == (opportunityArea != null ? opportunityArea.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.ParliamentaryConstituency_Code == (parliamentaryConstituency != null ? parliamentaryConstituency.Code : null) &&
location.ParliamentaryConstituency_Name == (parliamentaryConstituency != null ? parliamentaryConstituency.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.PlanningArea_Code == (planningArea != null ? planningArea.Code : null) &&
location.PlanningArea_Name == (planningArea != null ? planningArea.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.Provider_Code == (provider != null ? provider.Code : null) &&
location.Provider_Name == (provider != null ? provider.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.Region_Code == (region != null ? region.Code : null) &&
location.Region_Name == (region != null ? region.Name : null));
// Note that Name is not included in the predicate here as it is the same as the code
predicateBuilder = predicateBuilder
.And(location => location.RscRegion_Code == (rscRegion != null ? rscRegion.Code : null));
predicateBuilder = predicateBuilder
.And(location =>
location.School_Code == (school != null ? school.Code : null) &&
location.School_Name == (school != null ? school.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.Sponsor_Code == (sponsor != null ? sponsor.Code : null) &&
location.Sponsor_Name == (sponsor != null ? sponsor.Name : null));
predicateBuilder = predicateBuilder
.And(location => location.Ward_Code == (ward != null ? ward.Code : null) &&
location.Ward_Name == (ward != null ? ward.Name : null));
// This can return multiple results because C# equality is translated directly to SQL equality
// and our config of SqlServer is using the default case-insensitive collation
// See https://docs.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity
var locations = context.Location
.AsNoTracking()
.Where(predicateBuilder)
.ToList();
// Perform case-sensitive comparison on the Name fields
return(locations.FirstOrDefault(location =>
location.Country_Name == country.Name &&
location.EnglishDevolvedArea_Name == englishDevolvedArea?.Name &&
location.Institution_Name == institution?.Name &&
location.LocalAuthority_Name == localAuthority?.Name &&
location.LocalAuthorityDistrict_Name == localAuthorityDistrict?.Name &&
location.LocalEnterprisePartnership_Name == localEnterprisePartnership?.Name &&
location.MayoralCombinedAuthority_Name == mayoralCombinedAuthority?.Name &&
location.MultiAcademyTrust_Name == multiAcademyTrust?.Name &&
location.OpportunityArea_Name == opportunityArea?.Name &&
location.ParliamentaryConstituency_Name == parliamentaryConstituency?.Name &&
location.PlanningArea_Name == planningArea?.Name &&
location.Provider_Name == provider?.Name &&
location.Region_Name == region?.Name &&
location.RscRegion_Code == rscRegion?.Code && // RscRegion codes function as the name
location.School_Name == school?.Name &&
location.Sponsor_Name == sponsor?.Name &&
location.Ward_Name == ward?.Name
));
}
private Location LookupOrCreate(
StatisticsDbContext context,
GeographicLevel geographicLevel,
Country country,
EnglishDevolvedArea?englishDevolvedArea,
Institution?institution,
LocalAuthority?localAuthority,
LocalAuthorityDistrict?localAuthorityDistrict,
LocalEnterprisePartnership?localEnterprisePartnership,
MayoralCombinedAuthority?mayoralCombinedAuthority,
Mat?multiAcademyTrust,
OpportunityArea?opportunityArea,
ParliamentaryConstituency?parliamentaryConstituency,
PlanningArea?planningArea,
Provider?provider,
Region?region,
RscRegion?rscRegion,
School?school,
Sponsor?sponsor,
Ward?ward)
{
var location = Lookup(
context,
geographicLevel,
country,
englishDevolvedArea,
institution,
localAuthority,
localAuthorityDistrict,
localEnterprisePartnership,
mayoralCombinedAuthority,
multiAcademyTrust,
opportunityArea,
parliamentaryConstituency,
planningArea,
provider,
region,
rscRegion,
school,
sponsor,
ward);
if (location == null)
{
var entityEntry = context.Location.Add(new Location
{
Id = _guidGenerator.NewGuid(),
GeographicLevel = geographicLevel,
Country = country,
EnglishDevolvedArea = englishDevolvedArea,
Institution = institution,
LocalAuthority = localAuthority,
LocalAuthorityDistrict = localAuthorityDistrict,
LocalEnterprisePartnership = localEnterprisePartnership,
MayoralCombinedAuthority = mayoralCombinedAuthority,
MultiAcademyTrust = multiAcademyTrust,
OpportunityArea = opportunityArea,
ParliamentaryConstituency = parliamentaryConstituency,
PlanningArea = planningArea,
Provider = provider,
Region = region,
RscRegion = rscRegion,
School = school,
Sponsor = sponsor,
Ward = ward
});
return(entityEntry.Entity);
}
return(location);
}
private BoundaryLevel GetBoundaryLevel(GeographicLevel geographicLevel)
{
return(_boundaryLevelService.FindLatestByGeographicLevel(geographicLevel));
}
private bool HasBoundaryLevelForGeographicLevel(GeographicLevel geographicLevel)
{
return(_boundaryLevelService.FindLatestByGeographicLevel(geographicLevel) != null);
}
