private static Expression <Func <Person, bool> > GetFilterExpressionForLastName(IEnumerable <string> searchTerms)
{
var whereClause = LinqPredicateBuilder.Begin <Person>();
foreach (var searchTerm in searchTerms)
{
// Copy iterated variable for use with LINQ expression.
string thisSearchTerm = searchTerm;
whereClause = whereClause.Or(p => p.LastName.StartsWith(thisSearchTerm));
}
return(whereClause);
}
/// <summary>
/// Creates a filter expression to match one or more names in the common Person Name Fields.
/// The search algorithm is as follows:
/// All supplied terms must match in at least once in any of the fields.
/// A term is considered to be matched if the field starts with the exact term, or contains one of its acceptable variations.
/// </summary>
/// <param name="searchTerms">The search terms.</param>
/// <returns></returns>
private static Expression <Func <Person, bool> > GetFilterExpressionForMatchAllNames(IEnumerable <string> searchTerms)
{
var whereClause = LinqPredicateBuilder.Begin <Person>();
foreach (var searchTerm in searchTerms)
{
// Create variations of the search term that could be embedded in a name field.
var embeddedTerms = new List <string>();
// Multi-word Names: eg. Search for "Swee Lou" or "Swee Chin" matches "Swee Chin LOU".
embeddedTerms.Add(" " + searchTerm);
// Hyphenated Names: eg. Search for "Susan Smith" matches "Susan Huntington-Smith".
embeddedTerms.Add("-" + searchTerm);
// Names with Parentheses: eg. Search for "Andrew ST" matches "Andrew (ST) Lim".
embeddedTerms.Add("(" + searchTerm);
// Create a predicate to match this search term in any field.
var termPredicate = LinqPredicateBuilder.Begin <Person>();
var searchTermForClosure = searchTerm;
termPredicate = termPredicate.Or(p => p.FirstName.StartsWith(searchTermForClosure))
.Or(p => p.NickName.StartsWith(searchTermForClosure))
.Or(p => p.LastName.StartsWith(searchTermForClosure));
foreach (var embeddedTerm in embeddedTerms)
{
var embeddedTermForClosure = embeddedTerm;
termPredicate = termPredicate.Or(p => p.FirstName.Contains(embeddedTermForClosure))
.Or(p => p.NickName.Contains(embeddedTermForClosure))
.Or(p => p.LastName.Contains(embeddedTermForClosure));
}
// Add this to the Where clause using a logical AND to ensure that every search term is matched.
whereClause = whereClause.And(termPredicate);
}
return(whereClause);
}
public override Expression GetExpression(RockContext context, MemberExpression entityIdProperty, string selection)
{
var settings = new FilterSettings(selection);
//
// 1. Define Candidate Locations
//
bool useDefaultLocationsFilter = true;
// Get the Location Data View that defines the set of candidates from which proximate Locations can be selected.
DataView dataView = null;
if (settings.DataViewGuid.HasValue)
{
var dsService = new DataViewService(context);
dataView = dsService.Get(settings.DataViewGuid.Value);
if (dataView != null)
{
if (dataView.DataViewFilter == null)
{
dataView = null;
}
else
{
// Verify that the Group Data View does not contain any references to this Data View in any of its components.
if (dsService.IsViewInFilter(dataView.Id, dataView.DataViewFilter))
{
throw new Exception("Filter issue(s): One of the filters contains a circular reference to the Data View itself.");
}
}
}
}
// Evaluate the Data View that defines the candidate Locations.
var locationService = new LocationService(context);
var locationQuery = locationService.Queryable();
if (dataView != null)
{
var paramExpression = locationService.ParameterExpression;
List <string> errorMessages;
var whereExpression = dataView.GetExpression(locationService, paramExpression, out errorMessages);
if (errorMessages.Any())
{
throw new Exception("Filter issue(s): " + errorMessages.AsDelimited("; "));
}
locationQuery = locationQuery.Where(paramExpression, whereExpression, null);
useDefaultLocationsFilter = false;
// Include child groups?
//if (true)
//{
// var searchGroupKeys = new HashSet<int>();
// var parentGroups = groupQuery.Select(x => x.Id);
// foreach (var parentGroupId in parentGroups)
// {
// var branchKeys = this.GetGroupBranchKeys(groupService, parentGroupId);
// searchGroupKeys.UnionWith(branchKeys);
// }
// groupQuery = groupService.Queryable().Where(x => searchGroupKeys.Contains(x.Id));
//}
}
if (useDefaultLocationsFilter)
{
locationQuery = locationQuery.Where(x => !(x.Name == null || x.Name.Trim() == string.Empty));
}
// TODO: Remove this
locationQuery = locationQuery.Where(x => x.Name == "3149-Mount Waverley" || x.Name == "3140-Lilydale");
//var locationKeys = locationQuery.Select( x => x.Id );
//
// 2. Find the Group Locations that are proximate to the candidate Locations and are associated with Family Groups.
//
var proximateGroupLocationsBaseQuery = new GroupLocationService(context).Queryable();
// Filter for Groups that are Families.
var familyGroupTypeGuid = GroupType.GROUPTYPE_FAMILY.AsGuid();
proximateGroupLocationsBaseQuery = proximateGroupLocationsBaseQuery.Where(x => x.Group.GroupType.Guid == familyGroupTypeGuid);
// Filter By Location Type.
if (settings.LocationTypeGuid.HasValue)
{
proximateGroupLocationsBaseQuery = proximateGroupLocationsBaseQuery.Where(x => x.GroupLocationTypeValue.Guid == settings.LocationTypeGuid);
}
// Create Queries to find Family Group Locations that are proximate to each of the candidate Locations, then return a union of the result sets.
// We do this to preserve the link between the candidate Location and the Families located near that candidate Location.
double proximityInMeters = 0;
var locations = locationQuery.ToList();
IQueryable <PersonNearLocationResult> unionQuery = null;
foreach (var l in locations)
{
var groupLocationPredicate = LinqPredicateBuilder.Begin <GroupLocation>();
if (l.GeoPoint != null)
{
var gp = l.GeoPoint;
groupLocationPredicate = groupLocationPredicate.Or(gl => gl.Location.GeoPoint.Distance(gp) <= proximityInMeters);
}
if (l.GeoFence != null)
{
var gf = l.GeoFence;
groupLocationPredicate =
groupLocationPredicate.Or(gl => gl.Location.GeoPoint != null && gl.Location.GeoPoint.Intersects(gf) || gl.Location.GeoPoint.Distance(gf) <= proximityInMeters);
groupLocationPredicate =
groupLocationPredicate.Or(gl => gl.Location.GeoFence != null && gl.Location.GeoFence.Intersects(gf) || gl.Location.GeoFence.Distance(gf) <= proximityInMeters);
}
var proximateGroupLocationsQuery = proximateGroupLocationsBaseQuery.Where(groupLocationPredicate);
// Return all of the People in the Groups identified in the Group Locations, and the set of candidate Locations their Family Group is associated with.
var groupMembersOfProximateLocations = new GroupMemberService(context).Queryable()
.Where(gm => proximateGroupLocationsQuery.Select(gl => gl.GroupId).Contains(gm.GroupId));
//
// ** This Query produces the correct results.
//
string locationName = l.ToString();
var personLocationsQuery = new PersonService(context).Queryable()
.Where(p => groupMembersOfProximateLocations.Select(gm => gm.PersonId).Contains(p.Id))
.Select(x => new PersonNearLocationResult
{
Person = x,
LocationName = locationName
});
//var result5 = personLocationsQuery.ToList();
if (unionQuery == null)
{
unionQuery = personLocationsQuery;
}
else
{
unionQuery = unionQuery.Union(personLocationsQuery);
}
}
//var finalQuery = unionQuery.Select(pnl => unionQuery.Where(uq => uq.Person.Id == pnl.Person.Id).Select(p => p.LocationName));
//var resultUnion = unionQuery.ToList();
var finalQuery = new PersonService(context).Queryable().Select(p => unionQuery.Where(uq => uq.Person.Id == p.Id).Select(x => x.LocationName));
//var result6 = finalQuery.Where( x => x.Any() ).ToList();
// Define the Select Expression containing the field output.
var selectExpression = SelectExpressionExtractor.Extract <Model.Person>(finalQuery, entityIdProperty, "p");
return(selectExpression);
}
//Determine whether or not to filter the input time series per the input criteria -
private List <TimeSeriesViewModel> filterTimeSeries(List <TimeSeriesViewModel> series, clientFilterAndSearchCriteria filterAndSearchCriteria)
{
//Validate/initialize input parameters...
if (null == series || 0 >= series.Count || filterAndSearchCriteria.isEmpty())
{
//Null or empty time series list and/or criteria - return early
return(series);
}
//Prior to applying the criteria - copy the input time series
List <TimeSeriesViewModel> listFiltered = null;
//Initialize the LINQ predicate...
System.Linq.Expressions.Expression <Func <TimeSeriesViewModel, bool> > predicate;
//Apply search string (case-insensitive), if indicated...
//NOTE: The search string main contain space-delimited sub strings (e.g., 'SNOTEL 784' )
string search = filterAndSearchCriteria.search;
if (!String.IsNullOrWhiteSpace(search))
{
//Initialize the LINQ predicate...
//NOTE: Initializing the predicate to always return false and then adding ORs
// will select only those timeseries with values matching the search criteria
predicate = LinqPredicateBuilder.Create <TimeSeriesViewModel>(item => false);
//Scan datasources...
StringComparer comparer = StringComparer.CurrentCultureIgnoreCase;
foreach (var datasource in filterAndSearchCriteria.dataSources)
{
if (!datasource.searchable)
{
continue; //NOT searchable - continue
}
//Searchable... add value to predicate...
if (null != datasource.dataSource)
{
//Value in TimeSeriesViewModel property...
var source = datasource.dataSource;
//Add TimeSeriesViewModel property to LINQ predicate
//NOTE: MUST specify a case-insensitive contains...
// Use null coalescing operator (??) to avoid null reference errors...
// source: http://stackoverflow.com/questions/16490509/how-to-assign-empty-string-if-the-value-is-null-in-linq-query
predicate = predicate.Or(item => (item.GetType().GetProperty(source).GetValue(item, null) ?? String.Empty).ToString().Contains(search, StringComparison.CurrentCultureIgnoreCase, new SearchStringComparer()));
}
else if (null != datasource.title)
{
//Lookup value from title...
// Use null coalescing operator (??) to avoid null reference errors...
// source: http://stackoverflow.com/questions/16490509/how-to-assign-empty-string-if-the-value-is-null-in-linq-query
predicate = predicate.Or(item => (LookupValueFromTitle(datasource, item) ?? String.Empty).ToString().Contains(search, StringComparison.CurrentCultureIgnoreCase, new SearchStringComparer()));
}
}
//Apply search criteria to current timeseries
var queryable = series.AsQueryable();
try
{
listFiltered = queryable.Where(predicate).ToList();
}
catch (Exception ex)
{
string msg = ex.Message;
//Error in filtering - set filtered list to unfiltered list...
listFiltered = series;
}
}
//Apply filters (case-insensitive), if indicated...
List <clientFilter> filters = filterAndSearchCriteria.filters;
if (0 < filters.Count)
{
//Initialize the LINQ predicate...
//NOTE: Initializing the predicate to always return true and then adding ANDs
// will select only those timeseries with values matching the filter(s)
predicate = LinqPredicateBuilder.Create <TimeSeriesViewModel>(item => true);
//Set the queryable collection - listFiltered (if search criteria already applied, otherwise series)
var queryable = (null != listFiltered) ? listFiltered.AsQueryable() : series.AsQueryable();
foreach (var filter in filters)
{
var source = filter.dataSource;
var value = filter.value;
//Add TimeSeriesViewModel property to LINQ predicate
//NOTE: MUST specify a case-insensitive equals...
predicate = predicate.And(item => item.GetType().GetProperty(source).GetValue(item, null).ToString().Equals(value, StringComparison.CurrentCultureIgnoreCase));
}
//Apply search criteria to current timeseries
listFiltered = queryable.Where(predicate).ToList();
}
//Processing complete - return
return(listFiltered);
}
