public async Task <IEnumerable <Shipment> > Get(DateTime from, DateTime to, UKCompetentAuthority competentAuthority,
ShipmentsReportDates dateType, ShipmentReportTextFields?textFieldType,
TextFieldOperator?textFieldOperatorType, string textSearch)
{
var textFilter = TextFilterHelper.GetTextFilter(textFieldType, textFieldOperatorType, textSearch);
textFilter = !string.IsNullOrEmpty(textFilter) ? string.Format("AND {0}", textFilter) : string.Empty;
var query = @"SELECT DISTINCT
[NotificationNumber],
[ImportOrExport],
[Exporter],
[Importer],
[Facility],
[BaselOecdCode],
[ShipmentNumber],
[ActualDateOfShipment],
[ConsentFrom],
[ConsentTo],
[PrenotificationDate],
[ReceivedDate],
[CompletedDate],
[QuantityReceived],
[QuantityReceivedUnitId] AS [Units],
[ChemicalCompositionTypeId],
[ChemicalComposition],
[LocalArea],
[TotalQuantity],
[TotalQuantityUnitsId],
[EntryPort],
[DestinationCountry],
[ExitPort],
[OriginatingCountry],
[Status],
[EwcCodes],
[OperationCodes],
CASE WHEN YCode IS NULL THEN 'NA' ELSE YCode END AS [YCode],
CASE WHEN HCode IS NULL THEN 'NA' ELSE HCode END AS [HCode],
CASE WHEN UNClass IS NULL THEN 'NA' ELSE UNClass END AS [UNClass]
FROM [Reports].[ShipmentsCache]
WHERE [CompetentAuthorityId] = @ca
AND (@dateType = 'NotificationReceivedDate' and [NotificationReceivedDate] BETWEEN @from AND @to
OR @dateType = 'ConsentFrom' and [ConsentFrom] BETWEEN @from AND @to
OR @dateType = 'ConsentTo' and [ConsentTo] BETWEEN @from AND @to
OR @dateType = 'ReceivedDate' and [ReceivedDate] BETWEEN @from AND @to
OR @dateType = 'CompletedDate' and [CompletedDate] BETWEEN @from AND @to
OR @dateType = 'ActualDateOfShipment' and [ActualDateOfShipment] BETWEEN @from AND @to
OR @dateType = 'RejectedShipmentDate' and [RejectedShipmentDate] BETWEEN @from AND @to)
{0}
ORDER BY
[NotificationNumber],
[ShipmentNumber]";
return(await context.Database.SqlQuery <Shipment>(string.Format(query, textFilter),
new SqlParameter("@from", from),
new SqlParameter("@to", to),
new SqlParameter("@ca", (int)competentAuthority),
new SqlParameter("@dateType", dateType.ToString())).ToArrayAsync());
}
public async Task <IEnumerable <ProducerData> > GetProducerReport(ProducerReportDates dateType,
DateTime from,
DateTime to,
ProducerReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch,
UKCompetentAuthority competentAuthority)
{
var textFilter = TextFilterHelper.GetTextFilter(textFieldType, operatorType, textSearch);
textFilter = !string.IsNullOrEmpty(textFilter) ? string.Format("AND {0}", textFilter) : string.Empty;
var query = @"SELECT DISTINCT
[NotificationNumber]
,[NotifierName]
,[ProducerName]
,[ProducerAddress1]
,[ProducerAddress2]
,[ProducerTownOrCity]
,[ProducerPostCode]
,[SiteOfExport]
,[LocalArea]
,[WasteType]
,[NotificationStatus]
,[ConsigneeName]
FROM
[Reports].[ProducerCache]
WHERE
[CompetentAuthorityId] = @competentAuthority
AND (@dateType = 'NotificationReceivedDate' AND [NotificationReceivedDate] BETWEEN @from AND @to
OR @dateType = 'ConsentFrom' AND [ConsentFrom] BETWEEN @from AND @to
OR @dateType = 'ConsentTo' AND [ConsentTo] BETWEEN @from AND @to
OR @dateType = 'ReceivedDate' AND [MovementReceivedDate] BETWEEN @from AND @to
OR @dateType = 'CompletedDate' AND [MovementCompletedDate] BETWEEN @from AND @to)
{0}
ORDER BY
[NotificationNumber]";
return(await context.Database.SqlQuery <ProducerData>(string.Format(query, textFilter),
new SqlParameter("@dateType", dateType.ToString()),
new SqlParameter("@from", from),
new SqlParameter("@to", to),
new SqlParameter("@competentAuthority", (int)competentAuthority)).ToArrayAsync());
}
public async Task <IEnumerable <FreedomOfInformationData> > Get(DateTime from, DateTime to,
UKCompetentAuthority competentAuthority, FOIReportDates dateType, FOIReportTextFields?searchField,
TextFieldOperator?searchType,
string comparisonText)
{
var textFilter = TextFilterHelper.GetTextFilter(searchField, searchType, comparisonText);
textFilter = !string.IsNullOrEmpty(textFilter) ? string.Format("AND {0}", textFilter) : string.Empty;
var query = @"SELECT DISTINCT
[NotificationNumber],
[ImportOrExport],
CASE WHEN [IsInterim] = 1 THEN 'Interim' WHEN [IsInterim] = 0 THEN 'Non-interim' ELSE NULL END AS [Interim],
[BaselOecdCode],
[NotifierName],
[NotifierAddress],
[NotifierPostalCode],
[NotifierType],
[NotifierContactName],
[NotifierContactEmail],
[ProducerName],
[ProducerAddress],
[ProducerPostalCode],
[ProducerType],
[ProducerContactEmail],
[PointOfExport],
[PointOfEntry],
[ExportCountryName],
[ImportCountryName],
[TransitStates],
[NameOfWaste],
[EWC],
[YCode],
[HCode],
[OperationCodes],
[ImporterName],
[ImporterAddress],
[ImporterPostalCode],
[ImporterType],
[ImporterContactName],
[ImporterContactEmail],
[FacilityName],
[FacilityAddress],
[FacilityPostalCode],
[TechnologyEmployed],
COALESCE(
(SELECT SUM(
CASE WHEN [MovementQuantityReceviedUnitId] IN (1, 2) -- Tonnes / Cubic Metres
THEN COALESCE([MovementQuantityReceived], 0)
ELSE
COALESCE([MovementQuantityReceived] / 1000, 0) -- Convert to Tonnes / Cubic Metres
END
)
), 0) AS [QuantityReceived],
CASE WHEN [IntendedQuantityUnitId] IN (1, 2) -- Due to conversion units will only be Tonnes / Cubic Metres
THEN [IntendedQuantityUnit]
WHEN [IntendedQuantityUnitId] = 3 THEN 'Tonnes'
WHEN [IntendedQuantityUnitId] = 4 THEN 'Cubic Metres'
END AS [QuantityReceivedUnit],
[IntendedQuantity],
[IntendedQuantityUnit],
[ConsentFrom],
[ConsentTo],
[NotificationStatus],
[DecisionRequiredByDate],
[IsFinancialGuaranteeApproved],
[FileClosedDate],
[LocalArea],
[Officer]
FROM
[Reports].[FreedomOfInformationCache]
WHERE
[CompetentAuthorityId] = @competentAuthority
AND (@dateType = 'NotificationReceivedDate' AND [ReceivedDate] BETWEEN @from AND @to
OR @dateType = 'ConsentFrom' AND [ConsentFrom] BETWEEN @from AND @to
OR @dateType = 'ConsentTo' AND [ConsentTo] BETWEEN @from AND @to
OR @dateType = 'ReceivedDate' AND [MovementReceivedDate] BETWEEN @from AND @to
OR @dateType = 'CompletedDate' AND [MovementCompletedDate] BETWEEN @from AND @to
OR @dateType = 'ActualDate' AND [ActualDate] BETWEEN @from AND @to
OR @dateType = 'DecisionDate' AND [DecisionRequiredByDate] BETWEEN @from AND @to
OR @dateType = 'AcknowledgedDate' AND [AcknowledgedDate] BETWEEN @from AND @to
OR @dateType = 'ObjectionDate' AND [ObjectionDate] BETWEEN @from AND @to
OR @dateType = 'FileClosedDate' AND [FileClosedDate] BETWEEN @from AND @to
OR @dateType = 'WithdrawnDate' AND [WithdrawnDate] BETWEEN @from AND @to)
{0}
GROUP BY
[NotificationNumber],
[ImportOrExport],
[IsInterim],
[BaselOecdCode],
[NotifierName],
[NotifierAddress],
[NotifierPostalCode],
[NotifierType],
[NotifierContactName],
[NotifierContactEmail],
[ProducerName],
[ProducerAddress],
[ProducerPostalCode],
[ProducerType],
[ProducerContactEmail],
[PointOfExport],
[PointOfEntry],
[ExportCountryName],
[ImportCountryName],
[TransitStates],
[NameOfWaste],
[EWC],
[YCode],
[HCode],
[OperationCodes],
[ImporterName],
[ImporterAddress],
[ImporterPostalCode],
[ImporterType],
[ImporterContactName],
[ImporterContactEmail],
[FacilityName],
[FacilityAddress],
[FacilityPostalCode],
[TechnologyEmployed],
[IntendedQuantityUnitId],
[IntendedQuantityUnit],
[IntendedQuantity],
[IntendedQuantityUnit],
[ConsentFrom],
[ConsentTo],
[NotificationStatus],
[DecisionRequiredByDate],
[IsFinancialGuaranteeApproved],
[FileClosedDate],
[LocalArea],
[Officer]";
return(await context.Database.SqlQuery <FreedomOfInformationData>(string.Format(query, textFilter),
new SqlParameter("@from", from),
new SqlParameter("@to", to),
new SqlParameter("@competentAuthority", (int)competentAuthority),
new SqlParameter("@dateType", dateType.ToString())).ToArrayAsync());
}