private IQueryable <PickupListEntity> GetFilteredQuery(PickupListFilter filter)
{
string farmName = filter.Farm.Trim();
string clientName = filter.Client.Trim();
string fieldName = filter.Field.Trim();
var q = _context.PickupLists
.Include("AssignedTrucks")
.Include("DownloadedToTrucks")
.Include("Field.Farm.Client").AsNoTracking().AsQueryable();
if (!string.IsNullOrWhiteSpace(clientName))
{
q = q.Where(p => p.Field.Farm.Client.Name == clientName);
}
if (!string.IsNullOrWhiteSpace(farmName))
{
q = q.Where(p => p.Field.Farm.Name == farmName);
}
if (!string.IsNullOrWhiteSpace(fieldName))
{
q = q.Where(p => p.Field.Name == fieldName);
}
if (filter.Status.HasValue)
{
q = q.Where(p => p.PickupListStatus == filter.Status.Value);
}
if (filter.StartDate.HasValue)
{
q = q.Where(p => p.Created >= filter.StartDate.Value);
}
if (filter.EndDate.HasValue)
{
q = q.Where(p => p.Created <= filter.EndDate.Value);
}
if (filter.Sort1Ascending)
{
if (filter.SortCol1.ToLower() == "client")
{
q = q.OrderBy(m => m.Field.Farm.Client.Name);
}
if (filter.SortCol1.ToLower() == "name")
{
q = q.OrderBy(m => m.Name);
}
else if (filter.SortCol1.ToLower() == "farm")
{
q = q.OrderBy(m => m.Field.Farm.Name);
}
else if (filter.SortCol1.ToLower() == "field")
{
q = q.OrderBy(m => m.Field.Name);
}
else if (filter.SortCol1.ToLower() == "status")
{
q = q.OrderBy(m => m.PickupListStatus);
}
else if (filter.SortCol1.ToLower() == "timestamp")
{
q = q.OrderBy(m => m.Created);
}
}
else
{
if (filter.SortCol1.ToLower() == "client")
{
q = q.OrderByDescending(m => m.Field.Farm.Client.Name);
}
if (filter.SortCol1.ToLower() == "name")
{
q = q.OrderByDescending(m => m.Name);
}
else if (filter.SortCol1.ToLower() == "farm")
{
q = q.OrderByDescending(m => m.Field.Farm.Name);
}
else if (filter.SortCol1.ToLower() == "field")
{
q = q.OrderByDescending(m => m.Field.Name);
}
else if (filter.SortCol1.ToLower() == "status")
{
q = q.OrderByDescending(m => m.PickupListStatus);
}
else if (filter.SortCol1.ToLower() == "timestamp")
{
q = q.OrderByDescending(m => m.Created);
}
}
return(q);
}
public PagedResult <PickupListEntity> GetLists(PickupListFilter filter, int pageSize, int pageNo, int modulesPerLoad)
{
try
{
var filteredQuery = GetFilteredQuery(filter);
var countQuery = GetFilteredQuery(filter);
var cResult = new PagedResult <PickupListEntity>();
cResult.Total = countQuery.Count();
cResult.TotalPages = cResult.Total / pageSize;
if (cResult.Total % pageSize > 0)
{
cResult.TotalPages++;
}
cResult.LastPageNo = pageNo;
cResult.ResultData = new List <PickupListEntity>();
if (pageNo <= cResult.TotalPages)
{
cResult.ResultData.AddRange(filteredQuery.Skip(pageSize * (pageNo - 1)).Take(pageSize));
}
var listIds = cResult.ResultData.Select(p => p.Id).ToList();
var modulesInFieldCounts = _context.Modules.Where(m => m.PickupListId != null && m.ModuleStatus == ModuleStatus.IN_FIELD && listIds.Contains(m.PickupListId))
.GroupBy(p => p.PickupListId)
.Select(g => new { PickupListId = g.Key, Count = g.Count() });
var modulesAtGinCounts = _context.Modules.Where(m => m.PickupListId != null && m.ModuleStatus == ModuleStatus.AT_GIN && listIds.Contains(m.PickupListId))
.GroupBy(p => p.PickupListId)
.Select(g => new { PickupListId = g.Key, Count = g.Count() });
foreach (var result in cResult.ResultData)
{
result.ModulesPerLoad = modulesPerLoad;
result.SearchSetTotalModules = 0;
if (result.Destination == PickupListDestination.GIN_YARD)
{
var grouping = modulesInFieldCounts.SingleOrDefault(g => g.PickupListId == result.Id);
if (grouping != null)
{
result.SearchSetModulesRemaining = grouping.Count;
}
}
if (result.Destination == PickupListDestination.GIN_FEEDER)
{
var grouping = modulesAtGinCounts.SingleOrDefault(g => g.PickupListId == result.Id);
if (grouping != null)
{
result.SearchSetModulesRemaining = grouping.Count;
}
}
}
return(cResult);
}
catch (Exception exc)
{
Logging.Logger.Log(exc);
throw new Exception("Error fetching modules.", exc);
}
}
