public static string GetTextFilter <T>(T?textFieldType,
TextFieldOperator?operatorType,
string textSearch) where T : struct
{
if (!textFieldType.HasValue ||
!operatorType.HasValue ||
string.IsNullOrEmpty(textSearch))
{
return(null);
}
string textOperator;
switch (operatorType)
{
case TextFieldOperator.Contains:
textOperator = string.Format("LIKE '%{0}%'", textSearch);
break;
case TextFieldOperator.DoesNotContain:
textOperator = string.Format("NOT LIKE '%{0}%'", textSearch);
break;
default:
throw new ArgumentException(string.Format("Invalid operator type supplied: {0}", operatorType), "operatorType type");
}
return(string.Format("[{0}] {1}", textFieldType, textOperator));
}
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 GetFreedomOfInformationReport(DateTime from, DateTime to, FOIReportDates dateType, FOIReportTextFields?searchField,
TextFieldOperator?comparisonType,
string searchText)
{
From = from;
To = to;
DateType = dateType;
SearchField = searchField;
ComparisonType = comparisonType;
SearchText = searchText;
}
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 async Task <ActionResult> Download(FOIReportDates dateType,
DateTime from,
DateTime to,
FOIReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string searchText, string columnsToHide)
{
var report = await mediator.SendAsync(new GetFreedomOfInformationReport(from, to, dateType, textFieldType, operatorType, searchText));
var fileName = string.Format("foi-report-{0}-{1}.xlsx", from.ToShortDateString(), to.ToShortDateString());
return(new XlsxActionResult <FreedomOfInformationData>(report, fileName, true, columnsToHide));
}
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 ActionResult ColumnSelection(
FOIReportDates dateType,
DateTime from,
DateTime to,
FOIReportTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch)
{
var foiOutputColumns = CheckBoxCollectionViewModel.CreateFromEnum <FOIOutputColumns>();
var model = new ColumnSelectionViewModel(dateType, from, to, textFieldType, operatorType, textSearch, foiOutputColumns);
return(View(model));
}
public GetComplianceReport(ComplianceReportDates dateType,
DateTime from,
DateTime to,
ComplianceTextFields?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 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
};
}
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 <ActionResult> Download(ComplianceReportDates dateType,
DateTime from,
DateTime to,
ComplianceTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch)
{
var report =
await
mediator.SendAsync(new GetComplianceReport(dateType, from, to, textFieldType, operatorType, textSearch));
var fileName = string.Format("compliance-report-{0}-{1}.xlsx", from.ToShortDateString(), to.ToShortDateString());
var guidance = new ComplianceDataGuidance
{
NotificationNumber = Resources.NotificationNumber,
NoPrenotificationCount = Resources.NoPrenotificationCount,
PreNotificationColour = Resources.PreNotificationColour,
MissingShipments = Resources.MissingShipments,
MissingShipmentsColour = Resources.MissingShipmentsColour,
OverLimitShipments = Resources.OverLimitShipments,
OverActiveLoads = Resources.OverActiveLoads,
OverTonnage = Resources.OverTonnage,
OverTonnageColour = Resources.OverTonnageColour,
OverShipments = Resources.OverShipments,
OverShipmentsColour = Resources.OverShipmentsColour,
Notifier = Resources.Notifier,
Consignee = Resources.Consignee,
FileExpired = Resources.FileExpired
};
var colourGuidance = new ComplianceDataColourGuidance
{
HeaderText = Resources.ColourHeaderText,
GreenText = Resources.GreenText,
AmberText = Resources.AmberText,
RedText = Resources.RedText
};
return(new ComplianceXlsxActionResult(report, new List <ComplianceDataGuidance> {
guidance
}, colourGuidance,
fileName, true));
}
public async Task <IEnumerable <ComplianceData> > GetComplianceReport(ComplianceReportDates dateType,
DateTime from,
DateTime to,
ComplianceTextFields?textFieldType,
TextFieldOperator?operatorType,
string textSearch,
UKCompetentAuthority competentAuthority)
{
var reportlist = await context.Database.SqlQuery <ComplianceDataReport>(@"EXEC [Reports].[Compliance]
@DateType,
@CompetentAuthority,
@From,
@To",
new SqlParameter("@DateType", dateType.ToString()),
new SqlParameter("@CompetentAuthority", (int)competentAuthority),
new SqlParameter("@From", from),
new SqlParameter("@To", to)).ToArrayAsync();
if (!textFieldType.HasValue ||
!operatorType.HasValue ||
string.IsNullOrEmpty(textSearch))
{
return(reportlist);
}
else if (reportlist.Length > 0)
{
var predicate = CreatePredicate(textFieldType.Value.ToString(), textSearch, operatorType);
var query = reportlist.AsQueryable <ComplianceDataReport>();
return(query.Where(predicate));
}
else
{
return(reportlist);
}
}
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());
}
public Expression <Func <ComplianceDataReport, bool> > CreatePredicate(string columnName, object searchValue, TextFieldOperator?operatorType)
{
var columnType = typeof(ComplianceDataReport);
var x = Expression.Parameter(columnType, "x");
var column = columnType.GetProperties().FirstOrDefault(p => p.Name == columnName);
var property = Expression.Property(x, columnName);
MethodInfo method = typeof(String).GetMethod("Contains", new[] { typeof(String) });
var toLower = Expression.Call(property, typeof(string).GetMethod("ToLower", System.Type.EmptyTypes));
Expression filter;
switch (operatorType)
{
case TextFieldOperator.Contains:
filter = Expression.Call(toLower, method, Expression.Constant(searchValue.ToString().ToLower()));
break;
case TextFieldOperator.DoesNotContain:
filter = Expression.Not(Expression.Call(toLower, method, Expression.Constant(searchValue.ToString().ToLower())));
break;
default:
throw new ArgumentException(string.Format("Invalid operator type supplied: {0}", operatorType), "operatorType type");
}
return(Expression.Lambda <Func <ComplianceDataReport, bool> >(filter, x));
}