public GetProducerReport(ProducerReportDates dateType,
DateTime from,
DateTime to,
ProducerReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch)
{
DateType = dateType;
From = from;
To = to;
TextFieldType = textFieldType;
OperatorType = operatorType;
TextSearch = textSearch;
}
public async Task <ActionResult> Download(ProducerReportDates dateType,
DateTime from,
DateTime to,
ProducerReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch)
{
var report =
await
mediator.SendAsync(new GetProducerReport(dateType, from, to, textFieldType, operatorType, textSearch));
var fileName = string.Format("producer-report-{0}-{1}.xlsx", from.ToShortDateString(), to.ToShortDateString());
return(new XlsxActionResult <ProducerData>(report, fileName, true));
}
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());
}
