public PagedQueryResult <Pass> Search(PassSearchQueryModel queryModel, StringMatchRules freeTextMatchRules)
{
var query = PassQuery;
if (queryModel.IsLibraried != null)
{
query = queryModel.IsLibraried.Value
? query.Where(e => e.IsLibraried == true)
: query.Where(e => e.IsLibraried == null || e.IsLibraried == false);
}
if (!string.IsNullOrWhiteSpace(queryModel.Name))
{
var searchCondition = _searchConditionBuilder
.StartAnyWith(queryModel.Name.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries))
.Build();
query = query.Where(p => EF.Functions.Contains(p.Name, searchCondition));
}
query = (queryModel.OrderBy?.Any() ?? false)
? query.OrderByMultipleItems(queryModel.OrderBy)
: query.OrderBy(p => p.Name);
List <Pass> passList = _mapper.Map <List <Pass> >(
query.Where(e => freeTextMatchRules.IsMatches(e.Name, queryModel.Name)).AsNoTracking(),
opt => opt.UseEntityCache(_salesAreaByIdCache));
return(new PagedQueryResult <Pass>(passList.Count,
passList.ApplyPaging(queryModel.Skip, queryModel.Top).ToList()));
}
public PagedQueryResult <RunExtendedSearchModel> Search(RunSearchQueryModel queryModel, StringMatchRules freeTextMatchRules)
{
if (queryModel == null)
{
throw new ArgumentNullException(nameof(queryModel));
}
if (queryModel.Orderby?.Any() ?? false)
{
queryModel.Orderby.ForEach(i =>
{
if (string.Equals(i.OrderBy, "ExecuteStartedDateTime", StringComparison.InvariantCultureIgnoreCase))
{
i.OrderBy = nameof(RunDto.CreatedOrExecuteDateTime);
}
});
}
var query =
(from run in _dbContext.Query <Entities.Tenant.Runs.Run>()
join rsj in _dbContext.Query <Entities.Tenant.Runs.RunScenario>() on run.Id equals rsj.RunId
join sj in _dbContext.Query <Entities.Tenant.Scenarios.Scenario>() on rsj.ScenarioId equals sj.Id
into scenarios
from s in scenarios.DefaultIfEmpty()
join prj in _dbContext.Query <Entities.Tenant.Scenarios.ScenarioPassReference>() on s.Id equals prj.ScenarioId
into passReferences
from pr in passReferences.DefaultIfEmpty()
join pj in _dbContext.Query <Pass>() on pr.PassId equals pj.Id
into passes
from p in passes.DefaultIfEmpty()
select new { run, s, p }).AsQueryable();
if (queryModel.RunPeriodStartDate.HasValue && queryModel.RunPeriodEndDate.HasValue)
{
query = query.Where(q => q.run.StartDate <= queryModel.RunPeriodEndDate &&
q.run.EndDate >= queryModel.RunPeriodStartDate);
}
if (queryModel.ExecutedStartDate.HasValue && queryModel.ExecutedEndDate.HasValue)
{
query = query.Where(q => (q.run.ExecuteStartedDateTime ?? q.run.CreatedDateTime) >= queryModel.ExecutedStartDate &&
(q.run.ExecuteStartedDateTime ?? q.run.CreatedDateTime) <= queryModel.ExecutedEndDate);
}
if (queryModel.Users?.Any() ?? false)
{
query = query.Where(x => queryModel.Users.Contains(x.run.Author.AuthorId));
}
if (queryModel.Status?.Any() ?? false)
{
var entityStatuses = queryModel.Status.Cast <Entities.RunStatus>().ToArray();
query = query.Where(x => entityStatuses.Contains(x.run.RunStatus));
}
if (!string.IsNullOrEmpty(queryModel.Description))
{
var condition = _searchConditionBuilder.StartAnyWith(queryModel.Description.Split(new[] { ' ' })).Build();
query = query.Where(q =>
EF.Functions.Contains(EF.Property <string>(q.run, Entities.Tenant.Runs.Run.SearchField), condition) ||
EF.Functions.Contains(q.run.Author.Name, condition) ||
EF.Functions.Contains(EF.Property <string>(q.s, Entities.Tenant.Scenarios.Scenario.SearchField), condition) ||
EF.Functions.Contains(EF.Property <string>(q.p, Entities.Tenant.Passes.Pass.SearchField), condition));
}
var filteredQuery = query.Select(x => new
{
x.run.Id,
AuthorId = x.run.Author.Id,
AuthorName = x.run.Author.Name,
x.run.RunStatus,
x.run.StartDate,
x.run.StartTime,
x.run.EndDate,
x.run.EndTime,
x.run.CreatedOrExecuteDateTime,
x.run.Description
}).Distinct();
var extendedSearchQuery = queryModel.Orderby != null
? filteredQuery.OrderByMultipleItems(queryModel.Orderby)
: filteredQuery.OrderBySingleItem(nameof(Entities.Tenant.Runs.Run.Id), OrderDirection.Asc);
// If there is a valid description then do not apply paging now,
// since we need to filter further by applying free text matching rules
var shouldApplyFreeTextMatchRules = !string.IsNullOrWhiteSpace(queryModel.Description);
extendedSearchQuery = shouldApplyFreeTextMatchRules ?
extendedSearchQuery :
extendedSearchQuery.ApplyDefaultPaging(queryModel.Skip, queryModel.Top);
var totalCount = filteredQuery.Count();
var runIds = extendedSearchQuery.Select(x => x.Id).ToList();
var runs = _mapper.Map <List <RunExtendedSearchModel> >(_dbContext.Query <Entities.Tenant.Runs.Run>()
.Include(x => x.Author)
.Include(x => x.Scenarios)
.ThenInclude(x => x.Scenario)
.ThenInclude(x => x.PassReferences)
.ThenInclude(x => x.Pass)
.Include(x => x.Scenarios)
.ThenInclude(x => x.ExternalRunInfo)
.Where(x => runIds.Contains(x.Id))
.AsEnumerable()
.Select(x => new RunDto
{
Id = x.Id,
CustomId = x.CustomId,
Description = x.Description,
CreatedDateTime = x.CreatedDateTime,
StartDate = x.StartDate,
StartTime = x.StartTime,
EndDate = x.EndDate,
EndTime = x.EndTime,
LastModifiedDateTime = x.LastModifiedDateTime,
ExecuteStartedDateTime = x.ExecuteStartedDateTime,
IsLocked = x.IsLocked,
InventoryLock = x.InventoryLock,
Real = x.Real,
Smooth = x.Smooth,
AuthorId = x.Author.Id,
AuthorName = x.Author.Name,
SmoothDateStart = x.SmoothDateStart,
SmoothDateEnd = x.SmoothDateEnd,
ISR = x.ISR,
ISRDateStart = x.ISRDateStart,
ISRDateEnd = x.ISRDateEnd,
Optimisation = x.Optimisation,
OptimisationDateStart = x.OptimisationDateStart,
OptimisationDateEnd = x.OptimisationDateEnd,
RightSizer = x.RightSizer,
RightSizerDateStart = x.RightSizerDateStart,
RightSizerDateEnd = x.RightSizerDateEnd,
SpreadProgramming = x.SpreadProgramming,
SkipLockedBreaks = x.SkipLockedBreaks,
IgnorePremiumCategoryBreaks = x.IgnorePremiumCategoryBreaks,
ExcludeBankHolidays = x.ExcludeBankHolidays,
ExcludeSchoolHolidays = x.ExcludeSchoolHolidays,
Objectives = x.Objectives,
CreatedOrExecuteDateTime = x.CreatedOrExecuteDateTime,
RunStatus = x.RunStatus,
RunScenarios = x.Scenarios.ToList(),
Scenarios = x.Scenarios.Select(rs => rs.Scenario).ToList(),
Passes = x.Scenarios.Where(rs => rs.Scenario != null).SelectMany(rs => rs.Scenario.PassReferences.Select(p => p.Pass)).ToList()
}).OrderBy(x => runIds.IndexOf(x.Id)));
if (shouldApplyFreeTextMatchRules && totalCount > 0)
{
// Filter runs, necessary because Sql server cannot filter sufficiently. This is nasty
// but necessary because Sql server cannot filter the data as required
runs = ApplyFreeTextMatchRules(runs, freeTextMatchRules, queryModel.Description);
totalCount = runs.Count;
runs = runs.ApplyDefaultPaging(queryModel.Skip, queryModel.Top).ToList();
}
return(new PagedQueryResult <RunExtendedSearchModel>(totalCount, runs));
}