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 GetShipmentsReport(DateTime from, DateTime to, ShipmentsReportDates dateType,
ShipmentReportTextFields?textFieldType, TextFieldOperator?textFieldOperatorType,
string textSearch)
{
From = from;
To = to;
DateType = dateType;
TextFieldType = textFieldType;
TextFieldOperatorType = textFieldOperatorType;
TextSearch = textSearch;
}
public ActionResult ColumnSelection(
ShipmentsReportDates dateType,
DateTime from,
DateTime to,
ShipmentReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch)
{
var foiOutputColumns = CheckBoxCollectionViewModel.CreateFromEnum <ShipmentReportOutputColumns>();
var model = new ColumnSelectionViewModel(dateType, from, to, textFieldType, operatorType, textSearch, foiOutputColumns);
return(View(model));
}
public async Task <ActionResult> Download(ShipmentsReportDates dateType,
DateTime from,
DateTime to,
ShipmentReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string searchText, string columnsToHide)
{
var report =
await
mediator.SendAsync(new GetShipmentsReport(from, to,
dateType, textFieldType, operatorType, searchText));
var fileName = string.Format("shipments-{0}-{1}.xlsx", from.ToShortDateString(), to.ToShortDateString());
return(new XlsxActionResult <ShipmentData>(report, fileName, true, columnsToHide));
}
public ColumnSelectionViewModel(ShipmentsReportDates dateType,
DateTime from,
DateTime to,
ShipmentReportTextFields?textFieldType,
TextFieldOperator?operatorType, string searchText, CheckBoxCollectionViewModel shipmentOutputColumns)
{
ShipmentReportColumns = new ReportOutputParametersViewModel()
{
DateType = dateType.ToString(),
FromDate = from,
ToDate = to,
TextFieldType = textFieldType.ToString(),
OperatorType = operatorType.ToString(),
SearchText = searchText,
ReportColumns = shipmentOutputColumns
};
}
