public override BoundingBox GetExtents()
{
BoundingBox box = null;
using (var conn = new SQLiteConnection(ConnectionString))
{
string strSQL =
"SELECT Min(minx) AS MinX, Min(miny) AS MinY, Max(maxx) AS MaxX, Max(maxy) AS MaxY FROM " + Table;
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += " WHERE " + DefinitionQuery;
}
using (var command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
using (SQLiteDataReader dr = command.ExecuteReader())
if (dr.Read())
{
box = new BoundingBox((double)dr[0], (double)dr[2], (double)dr[1], (double)dr[3]);
}
conn.Close();
}
return(box);
}
}
public override Collection <uint> GetObjectIDsInView(BoundingBox bbox)
{
var objectlist = new Collection <uint>();
using (var conn = new SQLiteConnection(ConnectionID))
{
var strSQL = "SELECT " + ObjectIdColumn + " ";
strSQL += "FROM " + Table + " WHERE ";
strSQL += GetBoxClause(bbox);
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += " AND " + DefinitionQuery + " AND ";
}
using (var command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
var id = (uint)(int)dr[0];
objectlist.Add(id);
}
}
}
conn.Close();
}
}
return(objectlist);
}
public override void ExecuteIntersectionQuery(BoundingBox box, FeatureDataSet ds)
{
using (var conn = new SQLiteConnection(ConnectionID))
{
string strSQL = "SELECT *, " + GeometryColumn + " AS sharpmap_tempgeometry ";
strSQL += "FROM " + Table + " WHERE ";
strSQL += GetBoxClause(box);
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += " AND " + DefinitionQuery;
}
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSQL, conn))
{
conn.Open();
DataSet ds2 = new DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" &&
!col.ColumnName.StartsWith("Envelope_"))
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in ds2.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry" &&
!col.ColumnName.StartsWith("Envelope_"))
{
fdr[col.ColumnName] = dr[col];
}
}
if (dr["sharpmap_tempgeometry"] != DBNull.Value)
{
fdr.Geometry = GeometryFromWKT.Parse((string)dr["sharpmap_tempgeometry"]);
}
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
public override Collection <Geometry> GetGeometriesInView(BoundingBox bbox)
{
var features = new Collection <Geometry>();
using (var conn = new SQLiteConnection(ConnectionID))
{
var boxIntersect = GetBoxClause(bbox);
var strSQL = "SELECT " + GeometryColumn + " AS Geom ";
strSQL += "FROM " + Table + " WHERE ";
strSQL += boxIntersect;
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += " AND " + DefinitionQuery;
}
using (var command = new SQLiteCommand(strSQL, conn))
{
conn.Open();
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
var geom = GeometryFromWKT.Parse((string)dr[0]);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
}
return(features);
}
/// <summary>
/// Get the value from the dictionary for the specified 'key' and return it as an Envelope.
/// </summary>
/// <param name="dict"></param>
/// <param name="key"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
public static NetTopologySuite.Geometries.Envelope GetEnvelopNullValue(this IDictionary <string, Microsoft.Extensions.Primitives.StringValues> dict, string key, NetTopologySuite.Geometries.Envelope defaultValue = null)
{
if (!dict.TryGetValue(key, out Microsoft.Extensions.Primitives.StringValues value))
{
return(defaultValue);
}
var values = value.ToString().Split(',');
return(NetTopologySuite.Geometries.Envelope.Parse($"Env[{values[0]}:{values[1]},{values[2]}:{values[3]}]"));
}
private static string GetBoxClause(BoundingBox bbox)
{
return(String.Format(Map.NumberFormatEnUs,
"(minx < {0} AND maxx > {1} AND miny < {2} AND maxy > {3})",
bbox.MaxX, bbox.MinX, bbox.MaxY, bbox.MinY));
}
private void filterGeoPosition(String fileName, double minX, double maxX, double minY, double maxY)
{
var jsonDoc = "";
var batch = new List <string>();
var boundingBox = new NetTopologySuite.Geometries.Envelope(minX, maxX, minY, maxY);
var feature = new NetTopologySuite.Features.Feature();
using (FileStream s = File.Open(fileName, FileMode.Open))
using (var streamReader = new StreamReader(s))
{
var file = Path.GetFileNameWithoutExtension(fileName).Split(".");
var topicname = file[1];
using (var jsonreader = new Newtonsoft.Json.JsonTextReader(streamReader))
{
while (jsonreader.Read())
{
var reader = new NetTopologySuite.IO.GeoJsonReader();
if (jsonreader.TokenType == Newtonsoft.Json.JsonToken.StartObject)
{
while (jsonreader.Read())
{
if (jsonreader.TokenType == Newtonsoft.Json.JsonToken.StartArray)
{
while (jsonreader.Read())
{
try
{
if (jsonreader != null)
{
feature = reader.Read <NetTopologySuite.Features.Feature>(jsonreader);
}
var geo = feature.Geometry;
var atr = feature.Attributes;
if (boundingBox.Intersects(geo.EnvelopeInternal))
{
jsonDoc = createGeoObject(atr, geo);
batch.Add(jsonDoc);
if (batch.Count >= 5000)
{
_producer.Produce(topicname, batch);
_logger.LogInformation("Wrote " + batch.Count + " objects into " + topicname);
batch.Clear();
}
}
}
//Loop gives reader exception when it reaches the last element from the file
catch (Newtonsoft.Json.JsonReaderException e)
{
_logger.LogError("Error writing data: {0}.", e.GetType().Name);
var geo = feature.Geometry;
var atr = feature.Attributes;
jsonDoc = createGeoObject(atr, geo);
batch.Add(jsonDoc);
_producer.Produce(topicname, batch);
_logger.LogInformation("Wrote " + batch.Count + " objects into " + topicname);
batch.Clear();
break;
}
}
}
}
}
if (batch != null)
{
_producer.Produce(topicname, batch);
_logger.LogInformation("Wrote " + batch.Count + " objects into " + topicname);
batch.Clear();
}
}
}
}
}
/// <summary>
/// Get an array of parcels within the specified filter.
/// Will not return sensitive parcels unless the user has the `sensitive-view` claim and belongs to the owning agency.
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public Paged <Parcel> Get(ParcelFilter filter)
{
filter.ThrowIfNull(nameof(filter));
this.User.ThrowIfNotAuthorized(Permissions.SystemAdmin, Permissions.AgencyAdmin);
if (filter.Page < 1)
{
throw new ArgumentException("Argument must be greater than or equal to 1.", nameof(filter.Page));
}
if (filter.Quantity < 1)
{
throw new ArgumentException("Argument must be greater than or equal to 1.", nameof(filter.Quantity));
}
// Check if user has the ability to view sensitive properties.
var userAgencies = this.User.GetAgenciesAsNullable();
var viewSensitive = this.User.HasPermission(Security.Permissions.SensitiveView);
// Users may only view sensitive properties if they have the `sensitive-view` claim and belong to the owning agency.
var query = this.Context.Parcels.AsNoTracking();
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var poly = new NetTopologySuite.Geometries.Envelope(filter.NELongitude.Value, filter.SWLongitude.Value, filter.NELatitude.Value, filter.SWLatitude.Value).ToPolygon();
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
var filterAgencies = filter.Agencies.Select(a => (int?)a);
query = query.Where(p => filterAgencies.Contains(p.AgencyId));
}
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => EF.Functions.Like(p.ProjectNumber, $"{filter.ProjectNumber}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
// TODO: Parse the address information by City, Postal, etc.
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address.Address1, $"%{filter.Address}%") || EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(p => p.LandArea <= filter.MaxLandArea);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinMarketValue.HasValue)
{
query = query.Where(p =>
filter.MinMarketValue <= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == this.Context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && e.Key == FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
if (filter.MaxMarketValue.HasValue)
{
query = query.Where(p =>
filter.MaxMarketValue >= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == this.Context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && e.Key == FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MinAssessedValue <= p.Evaluations
.FirstOrDefault(e => e.Date == this.Context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && e.Key == EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MaxAssessedValue >= p.Evaluations
.FirstOrDefault(e => e.Date == this.Context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && e.Key == EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
var pagedEntities = query.Skip((filter.Page - 1) * filter.Quantity).Take(filter.Quantity);
return(new Paged <Parcel>(pagedEntities, filter.Page, filter.Quantity, query.Count()));
}
/// <summary>
/// Generate an SQL statement for the specified 'user' and 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="query"></param>
/// <param name="user"></param>
/// <param name="filter"></param>
/// <returns></returns>
private static IQueryable <Entity.Views.Property> GenerateCommonQuery(this PimsContext context, IQueryable <Entity.Views.Property> query, ClaimsPrincipal user, Entity.Models.AllPropertyFilter filter)
{
filter.ThrowIfNull(nameof(filter));
filter.ThrowIfNull(nameof(user));
// Check if user has the ability to view sensitive properties.
var userAgencies = user.GetAgenciesAsNullable();
var viewSensitive = user.HasPermission(Permissions.SensitiveView);
var isAdmin = user.HasPermission(Permissions.AdminProperties);
// By default do not include the following property classifications.
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
else
{
query = query.Where(p => p.ClassificationId != (int)Entities.ClassificationTypes.Disposed &&
p.ClassificationId != (int)Entities.ClassificationTypes.Demolished &&
p.ClassificationId != (int)Entities.ClassificationTypes.Subdivided);
}
// Users are not allowed to view sensitive properties outside of their agency or sub-agencies.
if (!viewSensitive)
{
query = query.Where(p => !p.IsSensitive);
}
// Display buildings or land/subdivisions
if (filter.PropertyType == Entities.PropertyTypes.Building)
{
query = query.Where(p => p.PropertyTypeId == Entities.PropertyTypes.Building);
}
else if (filter.PropertyType == Entities.PropertyTypes.Land)
{
query = query.Where(p => p.PropertyTypeId == Entities.PropertyTypes.Land || p.PropertyTypeId == Entities.PropertyTypes.Subdivision);
}
// Where rentable area is less than or equal to the filter.
if (filter.RentableArea.HasValue)
{
query = query.Where(p => p.RentableArea <= filter.RentableArea);
}
if (filter.BareLandOnly == true)
{
query = from p in query
join pa in context.Parcels on new { p.Id, PropertyTypeId = (int)p.PropertyTypeId } equals new { pa.Id, PropertyTypeId = (int)pa.PropertyTypeId }
where p.PropertyTypeId == Entity.PropertyTypes.Land &&
pa.Buildings.Count() == 0
select p;
}
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var poly = new NetTopologySuite.Geometries.Envelope(filter.NELongitude.Value, filter.SWLongitude.Value, filter.NELatitude.Value, filter.SWLatitude.Value).ToPolygon();
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
IEnumerable <int?> filterAgencies;
if (!isAdmin)
{
// Users can only search their own agencies.
filterAgencies = filter.Agencies.Intersect(userAgencies.Select(a => (int)a)).Select(a => (int?)a);
}
else
{
// TODO: Ideally this list would be provided by the frontend, as it is expensive to do it here.
// Get list of sub-agencies for any agency selected in the filter.
filterAgencies = filter.Agencies.Select(a => (int?)a);
}
if (filterAgencies.Any())
{
var agencies = filterAgencies.Concat(context.Agencies.AsNoTracking().Where(a => filterAgencies.Contains(a.Id)).SelectMany(a => a.Children.Select(ac => (int?)ac.Id)).ToArray()).Distinct();
query = query.Where(p => agencies.Contains(p.AgencyId));
}
}
if (filter.ParcelId.HasValue)
{
query = query.Where(p => p.ParcelId == filter.ParcelId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => p.ProjectNumbers.Contains(filter.ProjectNumber));
}
if (filter.IgnorePropertiesInProjects == true)
{
query = query.Where(p => p.ProjectNumbers == null || p.ProjectNumbers == "[]");
}
if (filter.InSurplusPropertyProgram == true)
{
query = query.Where(p => !String.IsNullOrWhiteSpace(p.ProjectNumbers) && p.ProjectNumbers != "[]");
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Name))
{
query = query.Where(p => EF.Functions.Like(p.Name, $"%{filter.Name}%"));
}
if (!String.IsNullOrWhiteSpace(filter.PID))
{
var pidValue = filter.PID.Replace("-", "").Trim();
if (Int32.TryParse(pidValue, out int pid))
{
query = query.Where(p => p.PID == pid || p.PIN == pid);
}
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
if (filter.ConstructionTypeId.HasValue)
{
query = query.Where(p => p.BuildingConstructionTypeId == filter.ConstructionTypeId);
}
if (filter.PredominateUseId.HasValue)
{
query = query.Where(p => p.BuildingPredominateUseId == filter.PredominateUseId);
}
if (filter.FloorCount.HasValue)
{
query = query.Where(p => p.BuildingFloorCount == filter.FloorCount);
}
if (!String.IsNullOrWhiteSpace(filter.Tenancy))
{
query = query.Where(p => EF.Functions.Like(p.BuildingTenancy, $"%{filter.Tenancy}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address, $"%{filter.Address}%") || EF.Functions.Like(p.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(b => b.LandArea <= filter.MaxLandArea);
}
if (filter.MinRentableArea.HasValue)
{
query = query.Where(p => p.RentableArea >= filter.MinRentableArea);
}
if (filter.MaxRentableArea.HasValue)
{
query = query.Where(b => b.RentableArea <= filter.MaxRentableArea);
}
if (filter.MinMarketValue.HasValue)
{
query = query.Where(p => p.Market >= filter.MinMarketValue);
}
if (filter.MaxMarketValue.HasValue)
{
query = query.Where(p => p.Market <= filter.MaxMarketValue);
}
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p => p.AssessedLand >= filter.MinAssessedValue || p.AssessedBuilding >= filter.MinAssessedValue);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p => p.AssessedLand <= filter.MaxAssessedValue || p.AssessedBuilding <= filter.MaxAssessedValue);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(p => p.AgencyCode).ThenBy(p => p.PID).ThenBy(p => p.PIN).ThenBy(p => p.PropertyTypeId);
}
return(query);
}
/// <summary>
/// Generate a query for the specified 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="user"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IQueryable <Entity.Parcel> GenerateQuery(this PimsContext context, ClaimsPrincipal user, Entity.Models.ParcelFilter filter)
{
filter.ThrowIfNull(nameof(user));
filter.ThrowIfNull(nameof(filter));
// Check if user has the ability to view sensitive properties.
var userAgencies = user.GetAgenciesAsNullable();
var viewSensitive = user.HasPermission(Permissions.SensitiveView);
var isAdmin = user.HasPermission(Permissions.AdminProperties);
// Users may only view sensitive properties if they have the `sensitive-view` claim and belong to the owning agency.
var query = context.Parcels.AsNoTracking();
if (!isAdmin)
{
query = query.Where(p =>
p.IsVisibleToOtherAgencies ||
((!p.IsSensitive || viewSensitive) &&
userAgencies.Contains(p.AgencyId)));
}
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var poly = new NetTopologySuite.Geometries.Envelope(filter.NELongitude.Value, filter.SWLongitude.Value, filter.NELatitude.Value, filter.SWLatitude.Value).ToPolygon();
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
// Get list of sub-agencies for any agency selected in the filter.
var filterAgencies = filter.Agencies.Select(a => (int?)a);
var agencies = filterAgencies.Concat(context.Agencies.AsNoTracking().Where(a => filterAgencies.Contains(a.Id)).SelectMany(a => a.Children.Select(ac => (int?)ac.Id)).ToArray()).Distinct();
query = query.Where(p => agencies.Contains(p.AgencyId));
}
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => EF.Functions.Like(p.ProjectNumber, $"{filter.ProjectNumber}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
// TODO: Parse the address information by City, Postal, etc.
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address.Address1, $"%{filter.Address}%") || EF.Functions.Like(p.Address.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(p => p.LandArea <= filter.MaxLandArea);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinMarketValue.HasValue)
{
query = query.Where(p =>
filter.MinMarketValue <= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
if (filter.MaxMarketValue.HasValue)
{
query = query.Where(p =>
filter.MaxMarketValue >= p.Fiscals
.FirstOrDefault(e => e.FiscalYear == context.ParcelFiscals
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.FiscalKeys.Market)
.Max(pe => pe.FiscalYear))
.Value);
}
// TODO: Review performance of the evaluation query component.
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MinAssessedValue <= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p =>
filter.MaxAssessedValue >= p.Evaluations
.FirstOrDefault(e => e.Date == context.ParcelEvaluations
.Where(pe => pe.ParcelId == p.Id && pe.Key == Entity.EvaluationKeys.Assessed)
.Max(pe => pe.Date))
.Value);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(p => p.Id);
}
return(query);
}
/// <summary>
/// Generate a query for the specified 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="user"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IQueryable <Entity.Views.Property> GenerateQuery(this PimsContext context, ClaimsPrincipal user, Entity.Models.AllPropertyFilter filter)
{
filter.ThrowIfNull(nameof(filter));
filter.ThrowIfNull(nameof(user));
// Check if user has the ability to view sensitive properties.
var userAgencies = user.GetAgenciesAsNullable();
var viewSensitive = user.HasPermission(Permissions.SensitiveView);
var isAdmin = user.HasPermission(Permissions.AdminProperties);
// Users may only view sensitive properties if they have the `sensitive-view` claim and belong to the owning agency.
var query = context.Properties
.AsNoTracking()
.Where(p => p.ClassificationId != 4); // Disposed properties are not visible.
// Only allowed to see user's own agency properties.
if (!isAdmin)
{
query = query.Where(p => p.IsVisibleToOtherAgencies || userAgencies.Contains(p.AgencyId));
}
if (!viewSensitive)
{
query = query.Where(p => !p.IsSensitive);
}
if (filter.PropertyType.HasValue)
{
query = query.Where(p => p.PropertyTypeId == filter.PropertyType.Value);
}
if (filter.RentableArea.HasValue)
{
query = query.Where(p => p.RentableArea == filter.RentableArea);
}
if (filter.BareLandOnly == true)
{
query = (from p in query
join pb in context.ParcelBuildings
on p.Id equals pb.ParcelId into ppbGroup
from pb in ppbGroup.DefaultIfEmpty()
where pb == null && p.PropertyTypeId == Entity.PropertyTypes.Land
select p);
}
if (filter.NELatitude.HasValue && filter.NELongitude.HasValue && filter.SWLatitude.HasValue && filter.SWLongitude.HasValue)
{
var poly = new NetTopologySuite.Geometries.Envelope(filter.NELongitude.Value, filter.SWLongitude.Value, filter.NELatitude.Value, filter.SWLatitude.Value).ToPolygon();
query = query.Where(p => poly.Contains(p.Location));
}
if (filter.Agencies?.Any() == true)
{
// Get list of sub-agencies for any agency selected in the filter.
var filterAgencies = filter.Agencies.Select(a => (int?)a);
var agencies = filterAgencies.Concat(context.Agencies.AsNoTracking().Where(a => filterAgencies.Contains(a.Id)).SelectMany(a => a.Children.Select(ac => (int?)ac.Id)).ToArray()).Distinct();
query = query.Where(p => agencies.Contains(p.AgencyId));
}
if (filter.ParcelId.HasValue)
{
query = query.Where(p => p.ParcelId == filter.ParcelId);
}
if (filter.ClassificationId.HasValue)
{
query = query.Where(p => p.ClassificationId == filter.ClassificationId);
}
if (!String.IsNullOrWhiteSpace(filter.ProjectNumber))
{
query = query.Where(p => EF.Functions.Like(p.ProjectNumber, $"{filter.ProjectNumber}%"));
}
if (filter.IgnorePropertiesInProjects == true)
{
query = query.Where(p => p.ProjectNumber == null);
}
if (filter.InSurplusPropertyProgram == true)
{
query = query.Where(p => !String.IsNullOrWhiteSpace(p.ProjectNumber));
}
if (!String.IsNullOrWhiteSpace(filter.Description))
{
query = query.Where(p => EF.Functions.Like(p.Description, $"%{filter.Description}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Name))
{
query = query.Where(p => EF.Functions.Like(p.Name, $"%{filter.Name}%"));
}
if (!String.IsNullOrWhiteSpace(filter.PID))
{
var pidValue = filter.PID.Replace("-", "").Trim();
if (Int32.TryParse(pidValue, out int pid))
{
query = query.Where(p => p.PID == pid || p.PIN == pid);
}
}
if (!String.IsNullOrWhiteSpace(filter.AdministrativeArea))
{
query = query.Where(p => EF.Functions.Like(p.AdministrativeArea, $"%{filter.AdministrativeArea}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Zoning))
{
query = query.Where(p => EF.Functions.Like(p.Zoning, $"%{filter.Zoning}%"));
}
if (!String.IsNullOrWhiteSpace(filter.ZoningPotential))
{
query = query.Where(p => EF.Functions.Like(p.ZoningPotential, $"%{filter.ZoningPotential}%"));
}
if (filter.ConstructionTypeId.HasValue)
{
query = query.Where(p => p.BuildingConstructionTypeId == filter.ConstructionTypeId);
}
if (filter.PredominateUseId.HasValue)
{
query = query.Where(p => p.BuildingPredominateUseId == filter.PredominateUseId);
}
if (filter.FloorCount.HasValue)
{
query = query.Where(p => p.BuildingFloorCount == filter.FloorCount);
}
if (!String.IsNullOrWhiteSpace(filter.Tenancy))
{
query = query.Where(p => EF.Functions.Like(p.BuildingTenancy, $"%{filter.Tenancy}%"));
}
if (!String.IsNullOrWhiteSpace(filter.Address))
{
query = query.Where(p => EF.Functions.Like(p.Address, $"%{filter.Address}%") || EF.Functions.Like(p.AdministrativeArea, $"%{filter.Address}%"));
}
if (filter.MinLandArea.HasValue)
{
query = query.Where(p => p.LandArea >= filter.MinLandArea);
}
if (filter.MaxLandArea.HasValue)
{
query = query.Where(b => b.LandArea <= filter.MaxLandArea);
}
if (filter.MinRentableArea.HasValue)
{
query = query.Where(p => p.RentableArea >= filter.MinRentableArea);
}
if (filter.MaxRentableArea.HasValue)
{
query = query.Where(b => b.RentableArea <= filter.MaxRentableArea);
}
if (filter.MinMarketValue.HasValue)
{
query = query.Where(p => p.Market >= filter.MinMarketValue);
}
if (filter.MaxMarketValue.HasValue)
{
query = query.Where(p => p.Market <= filter.MaxMarketValue);
}
if (filter.MinAssessedValue.HasValue)
{
query = query.Where(p => p.Assessed >= filter.MinAssessedValue);
}
if (filter.MaxAssessedValue.HasValue)
{
query = query.Where(p => p.Assessed <= filter.MaxAssessedValue);
}
if (filter.InEnhancedReferralProcess.HasValue && filter.InEnhancedReferralProcess.Value)
{
var statuses = context.Workflows.Where(w => w.Code == "ERP")
.SelectMany(w => w.Status).Where(x => !x.Status.IsTerminal)
.Select(x => x.StatusId).Distinct().ToArray();
query = query.Where(property =>
context.Projects.Any(project =>
statuses.Any(st => st == project.StatusId) &&
project.ProjectNumber == property.ProjectNumber));
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(p => p.AgencyCode).ThenBy(p => p.PID).ThenBy(p => p.PIN).ThenBy(p => p.PropertyTypeId);
}
return(query);
}
