async Task <bool> AccessorImport.IWaterAllocationAccessor.LoadWaterAllocation(string runId, IEnumerable <AccessorImport.WaterAllocation> waterAllocations)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
using (var cmd = db.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "Core.LoadWaterAllocation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 600;
var runIdParam = new SqlParameter();
runIdParam.ParameterName = "@RunId";
runIdParam.Value = runId;
cmd.Parameters.Add(runIdParam);
var orgsParam = new SqlParameter();
orgsParam.ParameterName = "@WaterAllocationTable";
orgsParam.SqlDbType = SqlDbType.Structured;
orgsParam.Value = waterAllocations.Select(ConvertObjectToSqlDataRecords <AccessorImport.WaterAllocation> .Convert).ToList();
orgsParam.TypeName = "Core.WaterAllocationTableType";
cmd.Parameters.Add(orgsParam);
var resultParam = new SqlParameter();
resultParam.SqlDbType = SqlDbType.Bit;
resultParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(resultParam);
await db.Database.OpenConnectionAsync();
await cmd.ExecuteNonQueryAsync();
return((int)resultParam.Value == 0);
}
}
async Task<IEnumerable<AccessorApi.SiteVariableAmountsOrganization>> AccessorApi.ISiteVariableAmountsAccessor.GetSiteVariableAmountsAsync(AccessorApi.SiteVariableAmountsFilters filters)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var query = db.SiteVariableAmountsFact.AsNoTracking();
if (filters.TimeframeStartDate != null)
{
query = query.Where(a => a.TimeframeStartNavigation.Date >= filters.TimeframeStartDate);
}
if (filters.TimeframeEndDate != null)
{
query = query.Where(a => a.TimeframeEndNavigation.Date <= filters.TimeframeEndDate);
}
if (!string.IsNullOrWhiteSpace(filters.VariableCv))
{
query = query.Where(a => a.VariableSpecific.VariableCv == filters.VariableCv);
}
if (!string.IsNullOrWhiteSpace(filters.VariableSpecificCv))
{
query = query.Where(a => a.VariableSpecific.VariableSpecificCv == filters.VariableSpecificCv);
}
if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv))
{
query = query.Where(a => a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.BeneficialUseCategory == filters.BeneficialUseCv));
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv))
{
query = query.Where(a => a.SitesBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv));
}
if (!string.IsNullOrWhiteSpace(filters.SiteUuid))
{
query = query.Where(a => a.Site.SiteUuid == filters.SiteUuid);
}
if (!string.IsNullOrWhiteSpace(filters.SiteTypeCv))
{
query = query.Where(a => a.Site.SiteTypeCv == filters.SiteTypeCv);
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(a => (a.Site.Geometry != null && a.Site.Geometry.Intersects(shape)) || (a.Site.SitePoint != null && a.Site.SitePoint.Intersects(shape)));
}
var results = await query
.GroupBy(a => a.Organization)
.ProjectTo<AccessorApi.SiteVariableAmountsOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var allBeneficialUses = results.SelectMany(a => a.BeneficialUses).ToList();
Parallel.ForEach(results.SelectMany(a => a.SiteVariableAmounts).Batch(10000), waterAllocations =>
{
SetBeneficialUses(waterAllocations, allBeneficialUses);
});
return results;
}
}
async Task <IEnumerable <AccessorApi.AggregatedAmountsOrganization> > AccessorApi.IAggregatedAmountsAccessor.GetAggregatedAmountsAsync(AccessorApi.AggregatedAmountsFilters filters)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var query = db.AggregatedAmountsFact.AsNoTracking();
if (filters.StartDate != null)
{
query = query.Where(a => a.TimeframeStart.Date >= filters.StartDate);
}
if (filters.EndDate != null)
{
query = query.Where(a => a.TimeframeEnd.Date <= filters.EndDate);
}
if (!string.IsNullOrWhiteSpace(filters.VariableCV))
{
query = query.Where(a => a.VariableSpecific.VariableCv == filters.VariableCV);
}
if (!string.IsNullOrWhiteSpace(filters.VariableSpecificCV))
{
query = query.Where(a => a.VariableSpecific.VariableSpecificCv == filters.VariableSpecificCV);
}
if (!string.IsNullOrWhiteSpace(filters.BeneficialUse))
{
query = query.Where(a => a.BeneficialUse.BeneficialUseCategory == filters.BeneficialUse || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.BeneficialUseCategory == filters.BeneficialUse));
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitUUID))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitUuid == filters.ReportingUnitUUID);
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitTypeCV))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitTypeCv == filters.ReportingUnitTypeCV);
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCV))
{
query = query.Where(a => a.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV));
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(a => a.ReportingUnit.Geometry != null && a.ReportingUnit.Geometry.Intersects(shape));
}
var results = await query
.GroupBy(a => a.Organization)
.ProjectTo <AccessorApi.AggregatedAmountsOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var allBeneficialUses = results.SelectMany(a => a.BeneficialUses).ToList();
Parallel.ForEach(results.SelectMany(a => a.AggregatedAmounts).Batch(10000), aggAmounts =>
{
SetBeneficialUses(aggAmounts, allBeneficialUses);
});
return(results);
}
}
async Task <bool> AccessorImport.IWaterAllocationAccessor.LoadVariables(string runId, IEnumerable <AccessorImport.Variable> variables)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
using (var cmd = db.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "Core.LoadVariables";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 600;
var runIdParam = new SqlParameter
{
ParameterName = "@RunId",
Value = runId
};
cmd.Parameters.Add(runIdParam);
var amountParam = new SqlParameter
{
ParameterName = "@VariableTable",
SqlDbType = SqlDbType.Structured,
Value = variables.Select(ConvertObjectToSqlDataRecords <AccessorImport.Variable> .Convert).ToList(),
TypeName = "Core.VariableTableType"
};
cmd.Parameters.Add(amountParam);
var resultParam = new SqlParameter
{
SqlDbType = SqlDbType.Bit,
Direction = ParameterDirection.ReturnValue
};
cmd.Parameters.Add(resultParam);
await db.Database.OpenConnectionAsync();
await cmd.ExecuteNonQueryAsync();
return((int)resultParam.Value == 0);
}
}
private void SetBeneficialUses(IEnumerable <AccessorApi.Allocation> allocationAmounts, List <AccessorApi.BeneficialUse> allBeneficialUses)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var ids = allocationAmounts.Select(a => a.AllocationAmountId).ToArray();
var beneficialUses = db.AllocationBridgeBeneficialUsesFact
.Where(a => ids.Contains(a.AllocationAmountId))
.Select(a => new { a.AllocationAmountId, a.BeneficialUseId })
.ToList();
foreach (var allocationAmount in allocationAmounts)
{
allocationAmount.BeneficialUses = beneficialUses
.Where(a => a.AllocationAmountId == allocationAmount.AllocationAmountId)
.Select(a => allBeneficialUses.FirstOrDefault(b => b.BeneficialUseID == a.BeneficialUseId)?.BeneficialUseCategory)
.Where(a => a != null)
.Distinct()
.ToList();
}
}
}
async Task <AccessorApi.AggregatedAmounts> AccessorApi.IAggregatedAmountsAccessor.GetAggregatedAmountsAsync(AccessorApi.AggregatedAmountsFilters filters, int startIndex, int recordCount)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var sw = Stopwatch.StartNew();
var query = db.AggregatedAmountsFact.AsNoTracking();
if (filters.StartDate != null)
{
query = query.Where(a => a.TimeframeStart.Date >= filters.StartDate);
}
if (filters.EndDate != null)
{
query = query.Where(a => a.TimeframeEnd.Date <= filters.EndDate);
}
if (!string.IsNullOrWhiteSpace(filters.VariableCV))
{
query = query.Where(a => a.VariableSpecific.VariableCv == filters.VariableCV);
}
if (!string.IsNullOrWhiteSpace(filters.VariableSpecificCV))
{
query = query.Where(a => a.VariableSpecific.VariableSpecificCv == filters.VariableSpecificCV);
}
if (!string.IsNullOrWhiteSpace(filters.BeneficialUse))
{
query = query.Where(a => a.PrimaryBeneficialUse.Name == filters.BeneficialUse || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.Name == filters.BeneficialUse));
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitUUID))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitUuid == filters.ReportingUnitUUID);
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitTypeCV))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitTypeCv == filters.ReportingUnitTypeCV);
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCV))
{
query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV || a.AggBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCV));
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(a => a.ReportingUnit.Geometry != null && a.ReportingUnit.Geometry.Intersects(shape));
}
if (!string.IsNullOrWhiteSpace(filters.State))
{
query = query.Where(a => a.Organization.State == filters.State);
}
var totalCount = query.Count();
var results = await query
.OrderBy(a => a.AggregatedAmountId)
.Skip(startIndex)
.Take(recordCount)
.ProjectTo <AggregatedHelper>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var aggregatedIds = results.Select(a => a.AggregatedAmountId).ToList();
var beneficialUseTask = db.AggBridgeBeneficialUsesFact
.Where(a => aggregatedIds.Contains(a.AggregatedAmountId))
.Select(a => new { a.AggregatedAmountId, a.BeneficialUse })
.ToListAsync();
var orgIds = results.Select(a => a.OrganizationId).ToHashSet();
var orgsTask = db.OrganizationsDim
.Where(a => orgIds.Contains(a.OrganizationId))
.ProjectTo <AccessorApi.AggregatedAmountsOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var waterSourceIds = results.Select(a => a.WaterSourceId).ToHashSet();
var waterSourceTask = db.WaterSourcesDim
.Where(a => waterSourceIds.Contains(a.WaterSourceId))
.ProjectTo <AccessorApi.WaterSource>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var reportingUnitIds = results.Select(a => a.ReportingUnitId).ToHashSet();
var reportingUnitsTask = db.WaterSourcesDim
.Where(a => waterSourceIds.Contains(a.WaterSourceId))
.ProjectTo <AccessorApi.ReportingUnit>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var methodIds = results.Select(a => a.MethodId).ToHashSet();
var methodTask = db.MethodsDim
.Where(a => methodIds.Contains(a.MethodId))
.ProjectTo <AccessorApi.Method>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var variableSpecificIds = results.Select(a => a.VariableSpecificId).ToHashSet();
var variableSpecificTask = db.VariablesDim
.Where(a => variableSpecificIds.Contains(a.VariableSpecificId))
.ProjectTo <AccessorApi.VariableSpecific>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var beneficialUses = (await beneficialUseTask).Select(a => (a.AggregatedAmountId, a.BeneficialUse)).ToList();
var waterSources = await waterSourceTask;
var variableSpecifics = await variableSpecificTask;
var methods = await methodTask;
var reportingUnits = await reportingUnitsTask;
var waterAllocationOrganizations = new List <AccessorApi.AggregatedAmountsOrganization>();
foreach (var org in await orgsTask)
{
ProcessAggregatedAmountsOrganization(org, results, waterSources, variableSpecifics, reportingUnits, methods, beneficialUses);
waterAllocationOrganizations.Add(org);
}
sw.Stop();
Logger.LogInformation($"Completed AggregatedAmounts [{sw.ElapsedMilliseconds } ms]");
return(new AccessorApi.AggregatedAmounts
{
TotalAggregatedAmountsCount = totalCount,
Organizations = waterAllocationOrganizations
});
}
}
async Task <AccessorApi.WaterAllocations> AccessorApi.IWaterAllocationAccessor.GetSiteAllocationAmountsAsync(AccessorApi.SiteAllocationAmountsFilters filters, int startIndex, int recordCount)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var sw = Stopwatch.StartNew();
var query = db.AllocationAmountsFact.AsNoTracking();
if (filters.StartPriorityDate != null)
{
query = query.Where(a => a.AllocationPriorityDateNavigation.Date >= filters.StartPriorityDate);
}
if (filters.EndPriorityDate != null)
{
query = query.Where(a => a.AllocationPriorityDateNavigation.Date <= filters.EndPriorityDate);
}
if (!string.IsNullOrWhiteSpace(filters.SiteUuid))
{
query = query.Where(a => a.AllocationBridgeSitesFact.Any(s => s.Site.SiteUuid == filters.SiteUuid));
}
if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv))
{
query = query.Where(a => a.PrimaryUseCategoryCV == filters.BeneficialUseCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUseCV == filters.BeneficialUseCv));
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv))
{
query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv));
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(
a => a.AllocationBridgeSitesFact.Any(site => site.Site.Geometry != null && site.Site.Geometry.Intersects(shape)) ||
a.AllocationBridgeSitesFact.Any(site => site.Site.SitePoint != null && site.Site.SitePoint.Intersects(shape)));
}
if (!string.IsNullOrWhiteSpace(filters.HUC8))
{
query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.HUC8 == filters.HUC8));
}
if (!string.IsNullOrWhiteSpace(filters.HUC12))
{
query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.HUC12 == filters.HUC12));
}
if (!string.IsNullOrWhiteSpace(filters.County))
{
query = query.Where(a => a.AllocationBridgeSitesFact.Any(b => b.Site.County == filters.County));
}
if (!string.IsNullOrWhiteSpace(filters.State))
{
query = query.Where(a => a.Organization.State == filters.State);
}
var totalCount = query.Count();
var results = await query
.OrderBy(a => a.AllocationAmountId)
.Skip(startIndex)
.Take(recordCount)
.ProjectTo <AllocationHelper>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var allocationIds = results.Select(a => a.AllocationAmountId).ToList();
var sitesTask = db.AllocationBridgeSitesFact
.Where(a => allocationIds.Contains(a.AllocationAmountId))
.Select(a => new { a.AllocationAmountId, a.Site })
.ToListAsync();
var beneficialUseTask = db.AllocationBridgeBeneficialUsesFact
.Where(a => allocationIds.Contains(a.AllocationAmountId))
.Select(a => new { a.AllocationAmountId, a.BeneficialUse })
.ToListAsync();
var orgIds = results.Select(a => a.OrganizationId).ToHashSet();
var orgsTask = db.OrganizationsDim
.Where(a => orgIds.Contains(a.OrganizationId))
.ProjectTo <AccessorApi.WaterAllocationOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var waterSourceIds = results.Select(a => a.WaterSourceId).ToHashSet();
var waterSourceTask = db.WaterSourcesDim
.Where(a => waterSourceIds.Contains(a.WaterSourceId))
.ProjectTo <AccessorApi.WaterSource>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var variableSpecificIds = results.Select(a => a.VariableSpecificId).ToHashSet();
var variableSpecificTask = db.VariablesDim
.Where(a => variableSpecificIds.Contains(a.VariableSpecificId))
.ProjectTo <AccessorApi.VariableSpecific>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var methodIds = results.Select(a => a.MethodId).ToHashSet();
var methodTask = db.MethodsDim
.Where(a => methodIds.Contains(a.MethodId))
.ProjectTo <AccessorApi.Method>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var sites = (await sitesTask).Select(a => (a.AllocationAmountId, a.Site)).ToList();
var beneficialUses = (await beneficialUseTask).Select(a => (a.AllocationAmountId, a.BeneficialUse)).ToList();
var waterSources = await waterSourceTask;
var variableSpecifics = await variableSpecificTask;
var methods = await methodTask;
var waterAllocationOrganizations = new List <AccessorApi.WaterAllocationOrganization>();
foreach (var org in await orgsTask)
{
ProcessWaterAllocationOrganization(org, results, waterSources, variableSpecifics, methods, beneficialUses, sites);
waterAllocationOrganizations.Add(org);
}
sw.Stop();
Logger.LogInformation($"Completed WaterAllocation [{sw.ElapsedMilliseconds } ms]");
return(new AccessorApi.WaterAllocations
{
TotalWaterAllocationsCount = totalCount,
Organizations = waterAllocationOrganizations
});
}
}
async Task <IEnumerable <AccessorApi.WaterAllocationsDigest> > AccessorApi.IWaterAllocationAccessor.GetSiteAllocationAmountsDigestAsync(AccessorApi.SiteAllocationAmountsDigestFilters filters, int startIndex, int recordCount)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var sw = Stopwatch.StartNew();
var query = db.AllocationAmountsFact.AsNoTracking();
if (filters.StartPriorityDate != null)
{
query = query.Where(a => a.AllocationPriorityDateNavigation.Date >= filters.StartPriorityDate);
}
if (filters.EndPriorityDate != null)
{
query = query.Where(a => a.AllocationPriorityDateNavigation.Date <= filters.EndPriorityDate);
}
if (!string.IsNullOrWhiteSpace(filters.BeneficialUseCv))
{
query = query.Where(a => a.PrimaryUseCategoryCV == filters.BeneficialUseCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUseCV == filters.BeneficialUseCv));
}
if (!string.IsNullOrWhiteSpace(filters.UsgsCategoryNameCv))
{
query = query.Where(a => a.PrimaryBeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv || a.AllocationBridgeBeneficialUsesFact.Any(b => b.BeneficialUse.UsgscategoryNameCv == filters.UsgsCategoryNameCv));
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(
a => a.AllocationBridgeSitesFact.Any(site => site.Site.Geometry != null && site.Site.Geometry.Intersects(shape)) ||
a.AllocationBridgeSitesFact.Any(site => site.Site.SitePoint != null && site.Site.SitePoint.Intersects(shape)));
}
if (!string.IsNullOrWhiteSpace(filters.OrganizationUUID))
{
query = query.Where(a => a.Organization.OrganizationUuid == filters.OrganizationUUID);
}
var results = await query
.OrderBy(a => a.AllocationAmountId)
.Skip(startIndex)
.Take(recordCount)
.ProjectTo <AllocationHelper>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var allocationIds = results.Select(a => a.AllocationAmountId).ToList();
var sitesTask = db.AllocationBridgeSitesFact
.Where(a => allocationIds.Contains(a.AllocationAmountId))
.Select(a => new { a.AllocationAmountId, a.Site })
.ToListAsync();
var sites = (await sitesTask).Select(a => (a.AllocationAmountId, a.Site)).ToList();
var waterAllocationsLight = new List <AccessorApi.WaterAllocationsDigest>();
foreach (var allocationAmounts in results)
{
var record = new AccessorApi.WaterAllocationsDigest
{
AllocationAmountId = allocationAmounts.AllocationAmountId,
AllocationFlow_CFS = allocationAmounts.AllocationFlow_CFS,
AllocationVolume_AF = allocationAmounts.AllocationVolume_AF,
AllocationPriorityDate = allocationAmounts.AllocationPriorityDate
};
var sights = new List <AccessorApi.SiteDigest>();
sights.AddRange(sites.Where(x => x.AllocationAmountId == allocationAmounts.AllocationAmountId)
.Select(x => new AccessorApi.SiteDigest
{
Latitude = x.Site.Latitude,
Longitude = x.Site.Longitude,
SiteUUID = x.Site.SiteUuid
}));
record.Sites = sights;
waterAllocationsLight.Add(record);
}
sw.Stop();
Logger.LogInformation($"Completed WaterAllocationLight [{sw.ElapsedMilliseconds } ms]");
return(waterAllocationsLight);
}
}
async Task <AccessorApi.RegulatoryReportingUnits> AccessorApi.IRegulatoryOverlayAccessor.GetRegulatoryReportingUnitsAsync(AccessorApi.RegulatoryOverlayFilters filters, int startIndex, int recordCount)
{
using (var db = new EntityFramework.WaDEContext(Configuration))
{
var sw = Stopwatch.StartNew();
var query = db.RegulatoryReportingUnitsFact.AsNoTracking();
if (filters.StatutoryEffectiveDate != null)
{
query = query.Where(a => a.RegulatoryOverlay.StatutoryEffectiveDate >= filters.StatutoryEffectiveDate);
}
if (filters.StatutoryEndDate != null)
{
query = query.Where(a => a.RegulatoryOverlay.StatutoryEndDate <= filters.StatutoryEndDate);
}
if (!string.IsNullOrWhiteSpace(filters.OrganizationUUID))
{
query = query.Where(a => a.Organization.OrganizationUuid == filters.OrganizationUUID);
}
if (!string.IsNullOrWhiteSpace(filters.RegulatoryOverlayUUID))
{
query = query.Where(a => a.RegulatoryOverlay.RegulatoryOverlayUuid == filters.RegulatoryOverlayUUID);
}
if (!string.IsNullOrWhiteSpace(filters.RegulatoryStatusCV))
{
query = query.Where(a => a.RegulatoryOverlay.RegulatoryStatusCv == filters.RegulatoryStatusCV);
}
if (!string.IsNullOrWhiteSpace(filters.ReportingUnitUUID))
{
query = query.Where(a => a.ReportingUnit.ReportingUnitUuid == filters.ReportingUnitUUID);
}
if (!string.IsNullOrWhiteSpace(filters.Geometry))
{
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
WKTReader reader = new WKTReader(geometryFactory);
var shape = reader.Read(filters.Geometry);
query = query.Where(a => a.ReportingUnit.Geometry != null && a.ReportingUnit.Geometry.Intersects(shape));
}
if (!string.IsNullOrWhiteSpace(filters.State))
{
query = query.Where(a => a.Organization.State == filters.State);
}
var totalCount = query.Count();
var results = await query
.OrderBy(a => a.BridgeId)
.Skip(startIndex)
.Take(recordCount)
.ProjectTo <ReportingUnitRegulatoryHelper>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var orgIds = results.Select(a => a.OrganizationId).ToHashSet();
var orgsTask = db.OrganizationsDim
.Where(a => orgIds.Contains(a.OrganizationId))
.ProjectTo <AccessorApi.RegulatoryReportingUnitsOrganization>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var regulatoryOverlayIds = results.Select(a => a.RegulatoryOverlayId).ToList();
var regulatoryOverlaysTask = db.RegulatoryOverlayDim
.Where(a => regulatoryOverlayIds.Contains(a.RegulatoryOverlayId))
.ProjectTo <AccessorApi.RegulatoryOverlay>(Mapping.DtoMapper.Configuration)
.ToListAsync();
var regulatoryOverlays = await regulatoryOverlaysTask;
var regulatoryReportingUnitsOrganizations = new List <AccessorApi.RegulatoryReportingUnitsOrganization>();
foreach (var org in await orgsTask)
{
ProcessRegulatoryReportingUnitsOrganization(org, results, regulatoryOverlays);
regulatoryReportingUnitsOrganizations.Add(org);
}
sw.Stop();
Logger.LogInformation($"Completed RegulatoryOverlay [{sw.ElapsedMilliseconds} ms]");
return(new AccessorApi.RegulatoryReportingUnits
{
TotalRegulatoryReportingUnitsCount = totalCount,
Organizations = regulatoryReportingUnitsOrganizations
});
}
}
