/// <summary>
/// Generate a query for the specified 'filter'.
/// </summary>
/// <param name="context"></param>
/// <param name="filter"></param>
/// <returns></returns>
public static IQueryable <Entity.PimsContactMgrVw> GenerateContactQuery(this PimsContext context, Entity.Models.ContactFilter filter)
{
filter.ThrowIfNull(nameof(filter));
IQueryable <PimsContactMgrVw> query = context.PimsContactMgrVws.AsNoTracking();
query = query.GenerateCommonContactQuery(filter);
return(query);
}
/// <summary>
/// Generate an SQL statement for the specified 'filter'.
/// </summary>
/// <param name="query"></param>
/// <param name="filter"></param>
/// <returns></returns>
private static IQueryable <Entity.PimsContactMgrVw> GenerateCommonContactQuery(this IQueryable <Entity.PimsContactMgrVw> query, Entity.Models.ContactFilter filter)
{
filter.ThrowIfNull(nameof(filter));
if (!String.IsNullOrWhiteSpace(filter.Municipality))
{
query = query.Where(c => EF.Functions.Like(c.MunicipalityName, $"%{filter.Municipality}%"));
}
var summary = filter.Summary.Trim();
if (filter.SearchBy == "persons")
{
query = query.Where(c => c.PersonId != null && c.Id.StartsWith("P"));
string[] nameParts = filter.Summary.Split(' ');
if (!String.IsNullOrWhiteSpace(summary))
{
foreach (string namePart in nameParts)
{
query = query.Where(c => EF.Functions.Like(c.FirstName, $"%{namePart}%") || EF.Functions.Like(c.Surname, $"%{namePart}%") || EF.Functions.Like(c.MiddleNames, $"%{namePart}%"));
}
}
}
else if (filter.SearchBy == "organizations")
{
query = query.Where(c => c.OrganizationId != null && c.Id.StartsWith("O"));
if (!String.IsNullOrWhiteSpace(summary))
{
query = query.Where(c => EF.Functions.Like(c.Summary, $"%{summary}%"));
}
}
else
{
string[] nameParts = summary.Split(' ');
if (!String.IsNullOrWhiteSpace(summary))
{
foreach (string namePart in nameParts)
{
query = query.Where(c => EF.Functions.Like(c.FirstName, $"%{namePart}%") || EF.Functions.Like(c.Surname, $"%{namePart}%") || EF.Functions.Like(c.MiddleNames, $"%{namePart}%") || EF.Functions.Like(c.OrganizationName, $"%{summary}%"));
}
}
}
if (filter.ActiveContactsOnly)
{
query = query.Where(c => !c.IsDisabled);
}
if (filter.Sort?.Any() == true)
{
query = query.OrderByProperty(filter.Sort);
}
else
{
query = query.OrderBy(c => c.Summary);
}
return(query);
}