public async Task<ActionResult> Download(DateTime from, DateTime to, ChemicalComposition chemicalComposition, FoiReportDates dateType)
{
var report = await mediator.SendAsync(new GetFreedomOfInformationReport(from, to, chemicalComposition, dateType));
var type = EnumHelper.GetShortName(chemicalComposition);
var fileName = string.Format("foi-report-{0}-{1}-{2}.xlsx", type, from.ToShortDateString(), to.ToShortDateString());
return new XlsxActionResult<FreedomOfInformationData>(report, fileName);
}
public async Task<IEnumerable<FreedomOfInformationData>> Get(DateTime from, DateTime to,
ChemicalComposition chemicalComposition, UKCompetentAuthority competentAuthority, FoiReportDates dateType)
{
return await context.Database.SqlQuery<FreedomOfInformationData>(
@"SELECT DISTINCT
[NotificationNumber],
[NotifierName],
[NotifierAddress],
[ProducerName],
[ProducerAddress],
[PointOfExport],
[PointOfEntry],
[ImportCountryName],
[NameOfWaste],
[EWC],
[YCode],
[HCode],
[OperationCodes],
[ImporterName],
[ImporterAddress],
[FacilityName],
[FacilityAddress],
COALESCE(
(SELECT SUM(
CASE WHEN [QuantityReceivedUnitId] IN (1, 2) -- Tonnes / Cubic Metres
THEN COALESCE([QuantityReceived], 0)
ELSE
COALESCE([QuantityReceived] / 1000, 0) -- Convert to Tonnes / Cubic Metres
END
)
FROM [Reports].[Movements]
WHERE Id = NotificationId
AND (@dateType = 'NotificationReceivedDate'
OR @dateType = 'ConsentFrom'
OR @dateType = 'ReceivedDate' and [ReceivedDate] BETWEEN @from AND @to
OR @dateType = 'CompletedDate' and [CompletedDate] BETWEEN @from AND @to)
), 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],
[LocalArea]
FROM
[Reports].[FreedomOfInformation]
WHERE
[CompetentAuthorityId] = @competentAuthority
AND [ChemicalCompositionTypeId] = @chemicalComposition
AND (@dateType = 'NotificationReceivedDate' AND [ReceivedDate] BETWEEN @from AND @to
OR @dateType = 'ConsentFrom' AND [ConsentFrom] BETWEEN @from AND @to
OR @dateType = 'ReceivedDate' AND [MovementReceivedDate] BETWEEN @from AND @to
OR @dateType = 'CompletedDate' AND [MovementCompletedDate] BETWEEN @from AND @to)",
new SqlParameter("@from", from),
new SqlParameter("@to", to),
new SqlParameter("@chemicalComposition", (int)chemicalComposition),
new SqlParameter("@competentAuthority", (int)competentAuthority),
new SqlParameter("@dateType", dateType.ToString())).ToArrayAsync();
}
