public async Task <IActionResult> Export( DateTime?startDateTime = null, DateTime?endDateTime = null, int?manufacturingAreaId = null, int?classificationId = null, string engineerName = null, string shortDescription = null, char?activityTypeId = null, string incidentDescription = null, string actionSummary = null, TernaryOption resolved = TernaryOption.All, TernaryOption hasWerumTicket = TernaryOption.All, string werumTicket = null, string bTServiceRequestNumber = null, TernaryOption interventionPerformed = TernaryOption.All, string batchNumber = null, int limit = 0) { Logger.LogTrace("Incidents/Export called"); var data = new IncidentSearchViewModel { StartDateTime = startDateTime, EndDateTime = endDateTime, ManufacturingAreaId = manufacturingAreaId, ClassificationId = classificationId, EngineerName = engineerName, ShortDescription = shortDescription, IncidentDescription = incidentDescription, ActionSummary = actionSummary, Resolved = resolved, HasWerumTicket = hasWerumTicket, WerumTicket = werumTicket, BTServiceRequestNumber = bTServiceRequestNumber, InterventionPerformed = interventionPerformed, BatchNumber = batchNumber, Limit = limit }; var list = GetFilteredSearchResults(data); var results = list.OrderByDescending(i => i.CreatedDateTime); var ms = new MemoryStream(500); var writer = new StreamWriter(ms); await writer.WriteLineAsync(ExportHeader); await list.ForEachAsync(i => writer.WriteLineAsync(IncidentToCsv(i)) ); await writer.FlushAsync(); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "text/csv", "Incidents.csv")); }
//helper method to perform search/export filtering private IQueryable <Incident> GetFilteredSearchResults(IncidentSearchViewModel data) { Logger.LogTrace("Incidents/GetFilteredSearchResults called"); bool filtersApplied = false; //setup main query IQueryable <Incident> list = DatabaseContext.Incidents .AsNoTracking() .Include(i => i.ManufacturingArea) .Include(i => i.Classification); #region Apply Filtering if (data.StartDateTime.HasValue) { list = list.Where(i => i.LocalActivityPerformedDateTime >= data.StartDateTime); filtersApplied = true; } if (data.EndDateTime.HasValue) { list = list.Where(i => i.LocalActivityPerformedDateTime < data.EndDateTime.Value.AddDays(1)); filtersApplied = true; } if (data.ManufacturingAreaId.HasValue) { list = list.Where(i => i.ManufacturingAreaId == data.ManufacturingAreaId); filtersApplied = true; } if (data.ClassificationId.HasValue) { list = list.Where(i => i.ClassificationId == data.ClassificationId); filtersApplied = true; } if (!String.IsNullOrEmpty(data.EngineerName)) { list = list.Where(i => EF.Functions.Like(i.EngineerName, "%" + data.EngineerName + "%")); filtersApplied = true; } if (!String.IsNullOrEmpty(data.IncidentDescription)) { list = list.Where(i => EF.Functions.Like(i.IncidentDescription, "%" + data.IncidentDescription + "%")); filtersApplied = true; } if (!String.IsNullOrEmpty(data.ActionSummary)) { list = list.Where(i => EF.Functions.Like(i.ActionSummary, "%" + data.ActionSummary + "%")); filtersApplied = true; } if (!String.IsNullOrEmpty(data.ShortDescription)) { list = list.Where(i => EF.Functions.Like(i.ShortDescription, "%" + data.ShortDescription + "%")); filtersApplied = true; } //must include a Werum Ticket? if (data.HasWerumTicket == TernaryOption.Yes) { list = list.Where(i => !String.IsNullOrEmpty(i.WerumTicket)); } //must not include a Werum Ticket? if (data.HasWerumTicket == TernaryOption.No) { list = list.Where(i => String.IsNullOrEmpty(i.WerumTicket)); } //otherwise, don't filter if (!String.IsNullOrEmpty(data.WerumTicket)) { list = list.Where(i => EF.Functions.Like(i.WerumTicket, "%" + data.WerumTicket + "%")); filtersApplied = true; } if (!String.IsNullOrEmpty(data.BTServiceRequestNumber)) { list = list.Where(i => EF.Functions.Like(i.BTServiceRequestNumber, "%" + data.BTServiceRequestNumber + "%")); filtersApplied = true; } if (!String.IsNullOrEmpty(data.BatchNumber)) { list = list.Where(i => EF.Functions.Like(i.BatchNumber, "%" + data.BatchNumber + "%")); filtersApplied = true; } //must be resolved? if (data.Resolved == TernaryOption.Yes) { list = list.Where(i => i.Resolved.HasValue && i.Resolved.Value); } //must not be resolved? if (data.Resolved == TernaryOption.No) { list = list.Where(i => (i.Resolved.HasValue && !i.Resolved.Value) || !i.Resolved.HasValue); } //otherwise, don't filter //must have intervention? if (data.InterventionPerformed == TernaryOption.Yes) { list = list.Where(i => i.RequireES13.HasValue && i.RequireES13.Value); } //must not be resolved? if (data.InterventionPerformed == TernaryOption.No) { list = list.Where(i => (i.RequireES13.HasValue && !i.RequireES13.Value) || !i.RequireES13.HasValue); } //otherwise, don't filter #endregion Apply Filtering if (filtersApplied) { return(list); } else { return(null); } }
// GET: Incidents/Search public async Task <IActionResult> Search( DateTime?startDateTime = null, DateTime?endDateTime = null, int?manufacturingAreaId = null, int?classificationId = null, string engineerName = null, string shortDescription = null, string incidentDescription = null, string actionSummary = null, TernaryOption resolved = TernaryOption.All, TernaryOption hasWerumTicket = TernaryOption.All, string werumTicket = null, string bTServiceRequestNumber = null, TernaryOption interventionPerformed = TernaryOption.All, string batchNumber = null, int limit = 20) { Logger.LogTrace("Incidents/Search called"); var data = new IncidentSearchViewModel { StartDateTime = startDateTime, EndDateTime = endDateTime, ManufacturingAreaId = manufacturingAreaId, ClassificationId = classificationId, EngineerName = engineerName, ShortDescription = shortDescription, IncidentDescription = incidentDescription, ActionSummary = actionSummary, Resolved = resolved, HasWerumTicket = hasWerumTicket, WerumTicket = werumTicket, BTServiceRequestNumber = bTServiceRequestNumber, InterventionPerformed = interventionPerformed, BatchNumber = batchNumber, Limit = limit }; var list = GetFilteredSearchResults(data); if (list != null) { data.Results = await list .Select(i => new IncidentSearchResultViewModel { IncidentId = i.IncidentId, ActivityPerformedDateTime = i.LocalActivityPerformedDateTime, FormattedLocalActivityPerformedDateTime = i.LocalActivityPerformedDateTime.ToString(DateFormat), ManufacturingAreaName = i.ManufacturingArea.Name, ClassificationName = i.Classification.Name, EngineerName = i.EngineerName, ShortDescription = i.ShortDescription, InterventionPerformed = i.RequireES13 ?? false, Resolved = i.Resolved ?? false, WerumTicket = i.WerumTicket, BTServiceRequestNumber = i.BTServiceRequestNumber, BatchNumber = i.BatchNumber, UserId = i.UserId }) .OrderByDescending(i => i.ActivityPerformedDateTime) .Take(data.Limit) .ToListAsync(); } else { data.Results = new List <IncidentSearchResultViewModel>(); } //load select (drop down) lists ViewData["ClassificationId"] = await GetClassificationsSelectListAsync(); ViewData["ManufacturingAreaId"] = await GetManufacturingAreasSelectListAsync(); return(View("Search", data)); }