Example #1
0
 private async Task <Guid[]> GetSearchResults(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority, string importOrExport)
 {
     return(await context.Database.SqlQuery <Guid>(SearchQuery,
                                                   new SqlParameter("@ca", (int)competentAuthority),
                                                   new SqlParameter("@importOrExport", importOrExport),
                                                   new SqlParameter("@ewc", (object)criteria.EwcCode ?? DBNull.Value),
                                                   new SqlParameter("@baselOecd", (object)criteria.BaselOecdCode ?? DBNull.Value),
                                                   new SqlParameter("@producerName", (object)criteria.ProducerName ?? DBNull.Value),
                                                   new SqlParameter("@importerName", (object)criteria.ImporterName ?? DBNull.Value),
                                                   new SqlParameter("@exporterName", (object)criteria.ExporterName ?? DBNull.Value),
                                                   new SqlParameter("@facilityName", (object)criteria.FacilityName ?? DBNull.Value),
                                                   new SqlParameter("@importCountryName", (object)criteria.ImportCountryName ?? DBNull.Value),
                                                   new SqlParameter("@exitPointName", (object)criteria.ExitPointName ?? DBNull.Value),
                                                   new SqlParameter("@entryPointName", (object)criteria.EntryPointName ?? DBNull.Value),
                                                   new SqlParameter("@localAreaId", (object)criteria.LocalAreaId ?? DBNull.Value),
                                                   new SqlParameter("@consentValidFromStart", (object)criteria.ConsentValidFromStart ?? DBNull.Value),
                                                   new SqlParameter("@consentValidFromEnd", (object)criteria.ConsentValidFromEnd ?? DBNull.Value),
                                                   new SqlParameter("@consentValidToStart", (object)criteria.ConsentValidToStart ?? DBNull.Value),
                                                   new SqlParameter("@consentValidToEnd", (object)criteria.ConsentValidToEnd ?? DBNull.Value),
                                                   new SqlParameter("@notificationReceivedStart",
                                                                    (object)criteria.NotificationReceivedStart ?? DBNull.Value),
                                                   new SqlParameter("@notificationReceivedEnd", (object)criteria.NotificationReceivedEnd ?? DBNull.Value),
                                                   new SqlParameter("@notificationType", (object)criteria.NotificationType ?? DBNull.Value),
                                                   new SqlParameter("@exportStatus", (object)criteria.NotificationStatus ?? DBNull.Value),
                                                   new SqlParameter("@importStatus", (object)criteria.ImportNotificationStatus ?? DBNull.Value),
                                                   new SqlParameter("@isInterim", (object)criteria.IsInterim ?? DBNull.Value),
                                                   new SqlParameter("@exportCountryName", (object)criteria.ExportCountryName ?? DBNull.Value),
                                                   new SqlParameter("@operationCodes", GetOperationCodes(criteria.OperationCodes)),
                                                   new SqlParameter("@baselOecdCodeNotListed", (object)criteria.BaselOecdCodeNotListed ?? DBNull.Value)).ToArrayAsync());
 }
        public async Task CanPerformAdvancedSearchExportNotifications()
        {
            var advancedSearchRepository = new AdvancedSearchRepository(context);

            var criteria = new AdvancedSearchCriteria();

            var results = await advancedSearchRepository.SearchExportNotificationsByCriteria(criteria, UKCompetentAuthority.England);

            Assert.NotNull(results);
        }
        public async Task CanPerformAdvancedSearchImportNotifications()
        {
            var advancedSearchRepository = new AdvancedSearchRepository(context);

            var criteria = new AdvancedSearchCriteria();

            var results = await advancedSearchRepository.SearchImportNotificationsByCriteria(criteria, UKCompetentAuthority.England);

            Assert.NotNull(results);
        }
Example #4
0
 public static SearchModel MapToSearchModel(this AdvancedSearchCriteria criteria)
 {
     return(new SearchModel
     {
         NumberOfRooms = criteria.NumberOfRooms.ToString(CultureInfo.InvariantCulture),
         MinCost = criteria.MinCost.ToString(CultureInfo.InvariantCulture),
         MaxCost = criteria.MaxCost.ToString(CultureInfo.InvariantCulture),
         MinArea = criteria.MinArea.ToString(CultureInfo.InvariantCulture),
         MaxArea = criteria.MaxArea.ToString(CultureInfo.InvariantCulture)
     });
 }
        public async Task<ActionResult> Results(AdvancedSearchCriteria criteria)
        {
            var results = await mediator.SendAsync(new NotificaitonsAdvancedSearch(criteria));

            var model = new ResultsViewModel
            {
                ExportResults = results.ExportResults.ToArray(),
                ImportResults = results.ImportResults.ToArray()
            };

            return View(model);
        }
        public async Task <ActionResult> Results(AdvancedSearchCriteria criteria)
        {
            var results = await mediator.SendAsync(new NotificaitonsAdvancedSearch(criteria));

            var model = new ResultsViewModel
            {
                ExportResults = results.ExportResults.ToArray(),
                ImportResults = results.ImportResults.ToArray()
            };

            return(View(model));
        }
Example #7
0
        public IActionResult GetAdvanced([FromBody] AdvancedSearchCriteria criteria)
        {
            if (!ModelState.IsValid)
            {
                return(BadRequest());
            }

            var(result, pointsOfInterest) = _offerSearchService.AdvancedSearch(criteria);

            var offers = GetGrouppedOffers(result, pointsOfInterest);

            return(Ok(offers));
        }
 /// <summary>
 ///     Submits the given AdvancedSearchCriteria to the
 ///     Ektron ISearchManager.
 /// </summary>
 /// <remarks>
 ///     If an exception is thrown (which happens for a variety of reasons) by the
 ///     Ektron ISearchManager, the exception is logged with an
 ///     Error severity and an empty list is return.
 /// </remarks>
 /// <param name="criteria">Criteria to search Ektron with.</param>
 /// <returns>List of SearchResultData returned by Ektron ISearchManager.</returns>
 public List<SearchResultData> DoSearch(AdvancedSearchCriteria criteria)
 {
     List<SearchResultData> results;
     try
     {
         Log.DebugFormat("Searching with expression: {0}", criteria.ExpressionTree.ToFormattedString());
         SearchResponseData searchResponse = _searchManager.Search(criteria);
         results = searchResponse == null ? new List<SearchResultData>() : searchResponse.Results;
     }
     catch (Exception ex)
     {
         Log.Error("Ektron SearchManager threw an exception.", ex);
         results = new List<SearchResultData>();
     }
     return results;
 }
        public async Task<ActionResult> Index(IndexViewModel model)
        {
            if (!ModelState.IsValid)
            {
                model.Areas = await GetAreas();

                return View(model);
            }

            var criteria = new AdvancedSearchCriteria
            {
                ConsentValidFromStart = model.ConsentValidFromStart.AsDateTime(),
                ConsentValidFromEnd = model.ConsentValidFromEnd.AsDateTime(),
                ConsentValidToStart = model.ConsentValidToStart.AsDateTime(),
                ConsentValidToEnd = model.ConsentValidToEnd.AsDateTime(),
                EwcCode = model.EwcCode,
                ImportCountryName = model.ImportCountryName,
                ImporterName = model.ImporterName,
                LocalAreaId = model.LocalAreaId,
                ProducerName = model.ProducerName,
                BaselOecdCode = model.BaselOecdCode,
                EntryPointName = model.EntryPointName,
                ExitPointName = model.ExitPointName,
                ExporterName = model.ExporterName,
                FacilityName = model.FacilityName,
                NotificationReceivedStart = model.NotificationReceivedStart.AsDateTime(),
                NotificationReceivedEnd = model.NotificationReceivedEnd.AsDateTime(),
                ImportNotificationStatus = model.GetImportNotificationStatus(),
                NotificationStatus = model.GetExportNotificationStatus(),
                TradeDirection = model.SelectedTradeDirection,
                NotificationType = model.SelectedNotificationType,
                OperationCodes = model.SelectedOperationCodes,
                IsInterim = model.IsInterim,
                ExportCountryName = model.ExportCountryName
            };

            if (model.BaselOecdCodeNotListed)
            {
                criteria.BaselOecdCodeNotListed = true;
            }

            return RedirectToAction("Results", criteria.ToRouteValueDictionary());
        }
        public async Task <ActionResult> Index(IndexViewModel model)
        {
            if (!ModelState.IsValid)
            {
                model.Areas = await GetAreas();

                return(View(model));
            }

            var criteria = new AdvancedSearchCriteria
            {
                ConsentValidFromStart = model.ConsentValidFromStart.AsDateTime(),
                ConsentValidFromEnd   = model.ConsentValidFromEnd.AsDateTime(),
                ConsentValidToStart   = model.ConsentValidToStart.AsDateTime(),
                ConsentValidToEnd     = model.ConsentValidToEnd.AsDateTime(),
                EwcCode                   = model.EwcCode,
                ImportCountryName         = model.ImportCountryName,
                ImporterName              = model.ImporterName,
                LocalAreaId               = model.LocalAreaId,
                ProducerName              = model.ProducerName,
                BaselOecdCode             = model.BaselOecdCode,
                EntryPointName            = model.EntryPointName,
                ExitPointName             = model.ExitPointName,
                ExporterName              = model.ExporterName,
                FacilityName              = model.FacilityName,
                NotificationReceivedStart = model.NotificationReceivedStart.AsDateTime(),
                NotificationReceivedEnd   = model.NotificationReceivedEnd.AsDateTime(),
                ImportNotificationStatus  = model.GetImportNotificationStatus(),
                NotificationStatus        = model.GetExportNotificationStatus(),
                TradeDirection            = model.SelectedTradeDirection,
                NotificationType          = model.SelectedNotificationType,
                OperationCodes            = model.SelectedOperationCodes,
                IsInterim                 = model.IsInterim,
                ExportCountryName         = model.ExportCountryName
            };

            if (model.BaselOecdCodeNotListed)
            {
                criteria.BaselOecdCodeNotListed = true;
            }

            return(RedirectToAction("Results", criteria.ToRouteValueDictionary()));
        }
        public void SelectFromOrderBy()
        {
            var expectedId = _idProvider.GetSmartFormId(SmartFormWidget.SmartFormName);

            var query = from w in EktronQueryFactory.Queryable<SmartFormWidget>(_idProvider)
                        orderby w.Id
                        select w;

            var model = TestHelper.GetQueryModel(query);
            var criteria = CriteriaGenerator.Generate(model, _idProvider);

            var actualCriteria = criteria;
            var expectedCriteria = new AdvancedSearchCriteria
                {
                    ExpressionTree = SearchContentProperty.XmlConfigId == expectedId,
                    OrderBy = new List<OrderData> { new OrderData(SearchContentProperty.Id, OrderDirection.Ascending) }
                };
            EkAssert.Equal(expectedCriteria, actualCriteria);
        }
        public void IEnumerableContains()
        {
            int[] numbers = new[] { 1, 2, 3, 4 };
            var query = from w in EktronQueryFactory.Queryable<NumberTestWidget>(_idProvider)
                        where numbers.Contains(w.Number)
                        select w;

            var propExpr = new IntegerPropertyExpression("Number");
            var model = TestHelper.GetQueryModel(query);

            var actualCriteria = CriteriaGenerator.Generate(model, _idProvider);
            var expectedCriteria = new AdvancedSearchCriteria
                {
                    ExpressionTree = (propExpr == numbers[0]
                                      | propExpr == numbers[1])
                                     | (propExpr == numbers[2]
                                        | propExpr == numbers[3])
                };
            EkAssert.Equal(expectedCriteria, actualCriteria);
        }
        public async Task<IEnumerable<ExportAdvancedSearchResult>> SearchExportNotificationsByCriteria(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority)
        {
            if (criteria.TradeDirection == TradeDirection.Import)
            {
                return Enumerable.Empty<ExportAdvancedSearchResult>();
            }

            var result = await GetSearchResults(criteria, competentAuthority, "Export");

            if (!result.Any())
            {
                return Enumerable.Empty<ExportAdvancedSearchResult>();
            }

            var parameters = result.Select((x, i) => new SqlParameter("@id" + i, x)).ToArray();

            var queryFormat = @"
                SELECT
                    N.[Id],
                    N.[NotificationNumber],
                    NS.[Description] AS [NotificationStatus],
                    E.[Name] AS [ExporterName],
                    CCT.[Description] AS [WasteType]
                FROM
                    [Notification].[Notification] N
                    INNER JOIN [Notification].[NotificationAssessment] NA 
                        INNER JOIN [Lookup].[NotificationStatus] NS ON NA.[Status] = NS.Id
                    ON N.Id = NA.NotificationApplicationId
                    LEFT JOIN [Notification].[Exporter] E ON N.Id = E.NotificationId
                    LEFT JOIN [Notification].[WasteType] WT 
                        INNER JOIN [Lookup].[ChemicalCompositionType] CCT ON WT.ChemicalCompositionType = CCT.Id
                    ON N.Id = WT.NotificationId
                WHERE
                    N.[Id] IN ({0})";

            var query = string.Format(queryFormat, string.Join(",", parameters.Select(x => x.ParameterName)));

            return await context.Database.SqlQuery<ExportAdvancedSearchResult>(query, parameters).ToListAsync();
        }
 internal static Int32 HandleCount(AdvancedSearchCriteria criteria, EktronSearcher searcher)
 {
     criteria.PagingInfo.RecordsPerPage = int.MaxValue;
     List<SearchResultData> searchResultData = searcher.DoSearch(criteria);
     return searchResultData.Count;
 }
 public NotificaitonsAdvancedSearch(AdvancedSearchCriteria criteria)
 {
     Criteria = criteria;
 }
Example #16
0
        public (ICollection <OfferModel>, ICollection <PointOfInterest>) AdvancedSearch(AdvancedSearchCriteria criteria)
        {
            var searchModel = criteria.MapToSearchModel();

            var queryProvider  = new AdvancedSearchQueryProvider(searchModel);
            var query          = queryProvider.GetSearchExpression();
            var databaseResult = _repository.GetWithExpression(query, 1000, 1);

            var offerModels = databaseResult
                              .Select(x => x.MapToOfferModel())
                              .ToList();

            var pointsOfInterest = GeocodePointsOfInterest(criteria.PointsOfInterest);

            var resultOffers = FilterByDistanceAndTime(offerModels, pointsOfInterest);

            return(resultOffers, pointsOfInterest);
        }
 internal static bool HandleAny(AdvancedSearchCriteria criteria, EktronSearcher searcher)
 {
     criteria.PagingInfo.RecordsPerPage = 1;
     List<SearchResultData> searchResultData = searcher.DoSearch(criteria);
     return searchResultData.Count > 0;
 }
        public static AdvancedSearchCriteria Translate(QueryModel queryModel, IEktronIdProvider idProvider)
        {
            var visitor = new TranslationVisitor(idProvider);
            visitor.VisitQueryModel(queryModel);

            AdvancedSearchCriteria criteria = new AdvancedSearchCriteria
                {
                    ExpressionTree = visitor.ExpressionTree,
                    OrderBy = visitor.OrderByData,
                    ReturnProperties = new HashSet<PropertyExpression>(visitor._returnProperties)
                };

            if (criteria.ExpressionTree == null)
            {
                criteria.ExpressionTree = SearchType.IsContent();
            }

            return criteria;
        }
 private async Task<Guid[]> GetSearchResults(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority, string importOrExport)
 {
     return await context.Database.SqlQuery<Guid>(SearchQuery,
         new SqlParameter("@ca", (int)competentAuthority),
         new SqlParameter("@importOrExport", importOrExport),
         new SqlParameter("@ewc", (object)criteria.EwcCode ?? DBNull.Value),
         new SqlParameter("@baselOecd", (object)criteria.BaselOecdCode ?? DBNull.Value),
         new SqlParameter("@producerName", (object)criteria.ProducerName ?? DBNull.Value),
         new SqlParameter("@importerName", (object)criteria.ImporterName ?? DBNull.Value),
         new SqlParameter("@exporterName", (object)criteria.ExporterName ?? DBNull.Value),
         new SqlParameter("@facilityName", (object)criteria.FacilityName ?? DBNull.Value),
         new SqlParameter("@importCountryName", (object)criteria.ImportCountryName ?? DBNull.Value),
         new SqlParameter("@exitPointName", (object)criteria.ExitPointName ?? DBNull.Value),
         new SqlParameter("@entryPointName", (object)criteria.EntryPointName ?? DBNull.Value),
         new SqlParameter("@localAreaId", (object)criteria.LocalAreaId ?? DBNull.Value),
         new SqlParameter("@consentValidFromStart", (object)criteria.ConsentValidFromStart ?? DBNull.Value),
         new SqlParameter("@consentValidFromEnd", (object)criteria.ConsentValidFromEnd ?? DBNull.Value),
         new SqlParameter("@consentValidToStart", (object)criteria.ConsentValidToStart ?? DBNull.Value),
         new SqlParameter("@consentValidToEnd", (object)criteria.ConsentValidToEnd ?? DBNull.Value),
         new SqlParameter("@notificationReceivedStart",
             (object)criteria.NotificationReceivedStart ?? DBNull.Value),
         new SqlParameter("@notificationReceivedEnd", (object)criteria.NotificationReceivedEnd ?? DBNull.Value),
         new SqlParameter("@notificationType", (object)criteria.NotificationType ?? DBNull.Value),
         new SqlParameter("@exportStatus", (object)criteria.NotificationStatus ?? DBNull.Value),
         new SqlParameter("@importStatus", (object)criteria.ImportNotificationStatus ?? DBNull.Value),
         new SqlParameter("@isInterim", (object)criteria.IsInterim ?? DBNull.Value),
         new SqlParameter("@exportCountryName", (object)criteria.ExportCountryName ?? DBNull.Value),
         new SqlParameter("@operationCodes", GetOperationCodes(criteria.OperationCodes)),
         new SqlParameter("@baselOecdCodeNotListed", (object)criteria.BaselOecdCodeNotListed ?? DBNull.Value)).ToArrayAsync();
 }
        public async Task<IEnumerable<ImportAdvancedSearchResult>> SearchImportNotificationsByCriteria(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority)
        {
            if (criteria.TradeDirection == TradeDirection.Export)
            {
                return Enumerable.Empty<ImportAdvancedSearchResult>();
            }

            var result = await GetSearchResults(criteria, competentAuthority, "Import");

            if (!result.Any())
            {
                return Enumerable.Empty<ImportAdvancedSearchResult>();
            }

            var parameters = result.Select((x, i) => new SqlParameter("@id" + i, x)).ToArray();

            var queryFormat = @"
                SELECT
                    N.[Id],
                    N.[NotificationNumber],
                    S.[Description] AS [Status],
                    E.Name AS [Exporter],
                    CASE WHEN WT.BaselOecdCodeNotListed = 1 THEN 'Not listed' ELSE WC.Code END AS [BaselOecdCode]
                FROM
                    [ImportNotification].[Notification] N
                    INNER JOIN [ImportNotification].[NotificationAssessment] NA ON N.Id = NA.NotificationApplicationId
                    INNER JOIN [Lookup].[ImportNotificationStatus] S ON NA.[Status] = S.Id
                    INNER JOIN [ImportNotification].[Exporter] E ON N.Id = E.ImportNotificationId
                    INNER JOIN [ImportNotification].[WasteType] WT ON N.Id = WT.ImportNotificationId
                    LEFT JOIN [ImportNotification].[WasteCode] W 
                        INNER JOIN [Lookup].[WasteCode] WC ON W.WasteCodeId = WC.Id
                        ON WT.Id = W.WasteTypeId AND WC.CodeType IN (1, 2)
                WHERE
                    N.[Id] IN ({0})";

            var query = string.Format(queryFormat, string.Join(",", parameters.Select(x => x.ParameterName)));

            return await context.Database.SqlQuery<ImportAdvancedSearchResult>(query, parameters).ToListAsync();
        }
Example #21
0
        public async Task <IEnumerable <ExportAdvancedSearchResult> > SearchExportNotificationsByCriteria(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority)
        {
            if (criteria.TradeDirection == TradeDirection.Import)
            {
                return(Enumerable.Empty <ExportAdvancedSearchResult>());
            }

            var result = await GetSearchResults(criteria, competentAuthority, "Export");

            if (!result.Any())
            {
                return(Enumerable.Empty <ExportAdvancedSearchResult>());
            }

            var parameters = result.Select((x, i) => new SqlParameter("@id" + i, x)).ToArray();

            var queryFormat = @"
                SELECT
                    N.[Id],
                    N.[NotificationNumber],
                    NS.[Description] AS [NotificationStatus],
                    E.[Name] AS [ExporterName],
                    CCT.[Description] AS [WasteType],
                    CASE WHEN NS.[Description] IN ('{1}', '{2}') AND FG.Id IS NOT NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) 
                        END AS [ShowShipmentSummaryLink]
                FROM
                    [Notification].[Notification] N
                    INNER JOIN [Notification].[NotificationAssessment] NA 
                        INNER JOIN [Lookup].[NotificationStatus] NS ON NA.[Status] = NS.Id
                    ON N.Id = NA.NotificationApplicationId
                    LEFT JOIN [Notification].[Exporter] E ON N.Id = E.NotificationId
                    LEFT JOIN [Notification].[WasteType] WT 
                        INNER JOIN [Lookup].[ChemicalCompositionType] CCT ON WT.ChemicalCompositionType = CCT.Id
                    ON N.Id = WT.NotificationId
                    LEFT JOIN  [Notification].[FinancialGuaranteeCollection] FGC ON FGC.[NotificationId] = N.Id
                        LEFT JOIN [Notification].[FinancialGuarantee] FG ON FG.Id = 
                            (SELECT TOP 1 FG1.Id from [Notification].[FinancialGuarantee] FG1 
                            WHERE FG1.FinancialGuaranteeCollectionId = FGC.Id
                            AND (FG1.Status = {3} OR FG1.Status = {4}))
                WHERE
                    N.[Id] IN ({0})";

            var query = string.Format(queryFormat, string.Join(",", parameters.Select(x => x.ParameterName)),
                                      EnumHelper.GetDisplayName(NotificationStatus.Consented),
                                      EnumHelper.GetDisplayName(NotificationStatus.ConsentWithdrawn),
                                      (int)FinancialGuaranteeStatus.Approved,
                                      (int)FinancialGuaranteeStatus.Released);

            return(await context.Database.SqlQuery <ExportAdvancedSearchResult>(query, parameters).ToListAsync());
        }
        public void IEnumerableContains_DifferingSurfaceTypes()
        {
            long[] numbers = new long[] { 1, 2, 3, 4 };
            IEnumerable<object> objectNumbers = numbers.Select(i => i).Cast<object>();
            var query = from w in EktronQueryFactory.Queryable<Widget>(_idProvider)
                        where objectNumbers.Contains(w.Id)
                        select w;

            var propExpr = SearchContentProperty.Id;
            var model = TestHelper.GetQueryModel(query);

            var actualCriteria = CriteriaGenerator.Generate(model, _idProvider);
            var expectedCriteria = new AdvancedSearchCriteria
                {
                    ExpressionTree = (propExpr == numbers[0]
                                      | propExpr == numbers[1])
                                     | (propExpr == numbers[2]
                                        | propExpr == numbers[3])
                };
            EkAssert.Equal(expectedCriteria, actualCriteria);
        }
Example #23
0
        public async Task <IEnumerable <ImportAdvancedSearchResult> > SearchImportNotificationsByCriteria(AdvancedSearchCriteria criteria, UKCompetentAuthority competentAuthority)
        {
            if (criteria.TradeDirection == TradeDirection.Export)
            {
                return(Enumerable.Empty <ImportAdvancedSearchResult>());
            }

            var result = await GetSearchResults(criteria, competentAuthority, "Import");

            if (!result.Any())
            {
                return(Enumerable.Empty <ImportAdvancedSearchResult>());
            }

            var parameters = result.Select((x, i) => new SqlParameter("@id" + i, x)).ToArray();

            var queryFormat = @"
                SELECT
                    N.[Id],
                    N.[NotificationNumber],
                    S.[Description] AS [Status],
                    E.Name AS [Exporter],
                    CASE WHEN WT.BaselOecdCodeNotListed = 1 THEN 'Not listed' ELSE WC.Code END AS [BaselOecdCode],
                    CASE WHEN S.[Description] IN ('{1}', '{2}') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS [ShowShipmentSummaryLink]
                FROM
                    [ImportNotification].[Notification] N
                    INNER JOIN [ImportNotification].[NotificationAssessment] NA ON N.Id = NA.NotificationApplicationId
                    INNER JOIN [Lookup].[ImportNotificationStatus] S ON NA.[Status] = S.Id
                    LEFT JOIN [ImportNotification].[Exporter] E ON N.Id = E.ImportNotificationId
                    LEFT JOIN [ImportNotification].[WasteType] WT ON N.Id = WT.ImportNotificationId
                    LEFT JOIN [ImportNotification].[WasteCode] W 
                        INNER JOIN [Lookup].[WasteCode] WC ON W.WasteCodeId = WC.Id
                        ON WT.Id = W.WasteTypeId AND WC.CodeType IN (1, 2)
                WHERE
                    N.[Id] IN ({0})";

            var query = string.Format(queryFormat, string.Join(",", parameters.Select(x => x.ParameterName)),
                                      EnumHelper.GetDisplayName(ImportNotificationStatus.Consented),
                                      EnumHelper.GetDisplayName(ImportNotificationStatus.ConsentWithdrawn));

            return(await context.Database.SqlQuery <ImportAdvancedSearchResult>(query, parameters).ToListAsync());
        }