/// <summary> /// Append filter to a query checking for ignore case /// </summary> /// <param name="query">Where to add the filter</param> /// <param name="op">Operation</param> /// <param name="filter">Filter</param> /// <param name="parName">Name of the parameter</param> /// <param name="metadata">Field metadata</param> private void AppendFilter(StringBuilder query, string op, RestFilter filter, string parName, FieldMetadata metadata) { if (query == null) { throw new ArgumentNullException(nameof(query)); } if (string.IsNullOrEmpty(op)) { throw new ArgumentNullException(nameof(query)); } if (filter == null) { throw new ArgumentNullException(nameof(filter)); } if (string.IsNullOrEmpty(parName)) { throw new ArgumentNullException(nameof(parName)); } if (metadata?.Type == FieldDataTypes.String && filter.IgnoreCase) { query.Append($" LOWER([{filter.Field}]) {op} LOWER({parName}) "); } else { query.Append($" [{filter.Field}] {op} {parName} "); } }
/// <summary> /// Expression per il valore di string comparison /// </summary> /// <param name="filter">Filtro per cui si vuole il valore</param> /// <returns></returns> private ConstantExpression GetStringComparisonExpression(RestFilter filter) { if (filter == null) { throw new ArgumentNullException(nameof(filter)); } if (filter.IgnoreCase) { return(Expression.Constant(StringComparison.InvariantCultureIgnoreCase)); } return(Expression.Constant(StringComparison.InvariantCulture)); }
/// <summary> /// Gets the where clause for the given filter /// </summary> /// <param name="filter">Filter to parse</param> /// <param name="sqlParameters">Added parameter for filter</param> /// <returns>Where clause, empty string if there are no filter</returns> private string GetWhereClauseForFilter(RestFilter filter, List <SqlParameter> sqlParameters) { if (sqlParameters == null) { throw new ArgumentNullException(nameof(sqlParameters)); } if (filter == null) { return(string.Empty); } var query = new StringBuilder(); AppendWhereClauseForFilter(filter, query, sqlParameters); return(query.ToString()); }
private byte[] FilterRESTRequest( RestHandlerOpCode opCode, RestRequestParameters restInput, out string responseProperties) { try { responseProperties = null; IRESTRequestHandler restRequestHandler = _restServiceSOI.FindRequestHandlerDelegate <IRESTRequestHandler>(); if (restRequestHandler == null) { throw new RestErrorException("Service handler not found"); } RestFilter restFilterOp = _restServiceSOI.GetFilter(opCode); if (null != restFilterOp && null != restFilterOp.PreFilter) { restInput = restFilterOp.PreFilter(restInput); } byte[] response = restRequestHandler.HandleRESTRequest(restInput.Capabilities, restInput.ResourceName, restInput.OperationName, restInput.OperationInput, restInput.OutputFormat, restInput.RequestProperties, out responseProperties); if (null == restFilterOp || null == restFilterOp.PostFilter) { return(response); } string newResponseProperties; var newResponse = restFilterOp.PostFilter(restInput, response, responseProperties, out newResponseProperties); responseProperties = newResponseProperties; return(newResponse); } catch (RestErrorException restException) { // pre- or post- filters can throw restException with the error JSON output in the Message property. // we catch them here and return JSON response. responseProperties = "{\"Content-Type\":\"text/plain;charset=utf-8\"}"; //catch and return a JSON error from the pre- or postFilter. return(System.Text.Encoding.UTF8.GetBytes(restException.Message)); } }
/// <summary> /// Constructor /// </summary> /// <param name="restParam">Rest parameters</param> /// <param name="metadata">Field metadata</param> public VisionFilterApplier(RestParameters restParam, List <FieldMetadata> metadata) { if (restParam == null) { throw new ArgumentNullException(nameof(restParam)); } if (metadata == null) { throw new ArgumentNullException(nameof(metadata)); } Filter = restParam.Filter; Metadata = metadata; // If we have a smart filter, add it to common filter if (restParam.SmartFilter != null) { var restSmartFilter = restParam.SmartFilter.ComposeFilter(metadata); if (restSmartFilter != null) { if (Filter == null) { Filter = restSmartFilter; } else { Filter = new RestFilter { Logic = FilterLogics.And, Filters = new List <RestFilter> { restParam.Filter, restSmartFilter } }; } } } }
/// <summary> /// Composes the where clause for a simple filter /// </summary> /// <param name="filter">Filter</param> /// <param name="query">String builder where to add the where clause</param> /// <param name="sqlParameters">Added parameter for filter</param> private void AppendWhereClauseForFilter(RestFilter filter, StringBuilder query, List <SqlParameter> sqlParameters) { if (filter == null) { return; } if (query == null) { throw new ArgumentNullException(nameof(query)); } // filtro semplice if (!string.IsNullOrEmpty(filter.Field)) { AppendClauseForSimpleFilter(filter, query, sqlParameters); } else if (filter.Filters.Count > 0) { bool isFirst = true; var filterCount = filter.Filters.Count; for (int i = 0; i < filterCount; i++) { if (isFirst) { query.Append("("); } else { var andOr = filter.Logic.ToLowerInvariant() == FilterLogics.And ? "AND" : "OR"; query.Append($" {andOr} ("); } isFirst = false; var filterToElab = filter.Filters[i]; AppendWhereClauseForFilter(filterToElab, query, sqlParameters); query.Append(")"); } } }
private Expression ComposeExpressionFilter(RestFilter filter, Type objectType, ParameterExpression objectParam) { if (filter == null) { throw new ArgumentNullException(nameof(filter)); } if (objectType == null) { throw new ArgumentNullException(nameof(objectType)); } if (objectParam == null) { throw new ArgumentNullException(nameof(objectParam)); } // Complex filter if (filter.Filters != null && filter.Filters.Any()) { var leftFilter = filter.Filters[0]; var rightFilter = filter.Filters[1]; var leftExpression = ComposeExpressionFilter(leftFilter, objectType, objectParam); var rightExpression = ComposeExpressionFilter(rightFilter, objectType, objectParam); if (FilterLogics.And.Equals(filter.Logic, StringComparison.InvariantCultureIgnoreCase)) { return(Expression.And(leftExpression, rightExpression)); } return(Expression.Or(leftExpression, rightExpression)); } // Simple filter var fieldName = filter.Field; var fieldValueAsExpressionParam = GetDynamciProperty(fieldName, objectParam); var filterValueAsExpressionValue = ConvertData(Expression.Constant(filter.Value), fieldName); var nullAsExpressionParam = Expression.Constant(null); var emptyStringAsExpressionParam = Expression.Constant(string.Empty); ConstantExpression stringComparison; UnaryExpression fieldValueAsStringExpression; switch (filter.Operator) { case FilterOperators.IsEqual: return(Expression.Equal(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsNotEqual: return(Expression.NotEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsGreaterThan: return(Expression.GreaterThan(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsGreatherThanOrEqual: return(Expression.GreaterThanOrEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsLessThan: return(Expression.LessThan(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsLessThanOrEqual: return(Expression.LessThanOrEqual(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsNull: return(Expression.Equal(fieldValueAsExpressionParam, filterValueAsExpressionValue)); case FilterOperators.IsNotNull: return(Expression.NotEqual(fieldValueAsExpressionParam, nullAsExpressionParam)); case FilterOperators.IsEmpty: return(Expression.Equal(fieldValueAsExpressionParam, emptyStringAsExpressionParam)); case FilterOperators.IsNotEmpty: return(Expression.NotEqual(fieldValueAsExpressionParam, emptyStringAsExpressionParam)); case FilterOperators.Contains: case FilterOperators.DoesNotContain: var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string), typeof(StringComparison) }); stringComparison = GetStringComparisonExpression(filter); fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam); var contains = Expression.Call(fieldValueAsStringExpression, containsMethod, filterValueAsExpressionValue, stringComparison); if (filter.Operator.Equals(FilterOperators.DoesNotContain, StringComparison.InvariantCultureIgnoreCase)) { return(Expression.Not(contains)); } return(contains); case FilterOperators.EndsWith: var endsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string), typeof(StringComparison) }); stringComparison = GetStringComparisonExpression(filter); fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam); var endsWith = Expression.Call(fieldValueAsStringExpression, endsWithMethod, filterValueAsExpressionValue, stringComparison); return(endsWith); case FilterOperators.StartsWith: var startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string), typeof(StringComparison) }); stringComparison = GetStringComparisonExpression(filter); fieldValueAsStringExpression = GetStringValue(fieldValueAsExpressionParam); var startsWith = Expression.Call(fieldValueAsStringExpression, startsWithMethod, filterValueAsExpressionValue, stringComparison); return(startsWith); default: throw new ApplicationException($"Operation '{filter.Operator}' not managed in Vision filter"); } }
/// <summary> /// Composes the where clause for a simple filter /// </summary> /// <param name="filter">Filter</param> /// <param name="query">String builder where to add the where clause</param> /// <param name="sqlParameters">Added parameter for filter</param> private void AppendClauseForSimpleFilter(RestFilter filter, StringBuilder query, List <SqlParameter> sqlParameters) { if (filter == null) { return; } if (query == null) { throw new ArgumentNullException(nameof(query)); } if (string.IsNullOrEmpty(filter.Field)) { throw new ArgumentException("Field value is mandatory", nameof(filter.Field)); } if (string.IsNullOrEmpty(filter.Operator)) { throw new ArgumentException("Operato value is mandatory", nameof(filter.Operator)); } var parName = $"@{sqlParameters.Count}"; // find the metadata var metadata = EntityMetadata.Fields .Where(x => string.Equals(x.Name, filter.Field, StringComparison.InvariantCultureIgnoreCase)) .SingleOrDefault(); switch (filter.Operator.ToLowerInvariant()) { // Equal case FilterOperators.IsEqual: AppendFilter(query, "=", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Not equal case FilterOperators.IsNotEqual: AppendFilter(query, "<>", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Is null case FilterOperators.IsNull: query.Append($" [{filter.Field}] IS NULL "); break; // Is not null case FilterOperators.IsNotNull: query.Append($" [{filter.Field}] IS NOT NULL "); break; // Less than case FilterOperators.IsLessThan: AppendFilter(query, "<", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Less than or equal case FilterOperators.IsLessThanOrEqual: AppendFilter(query, "<=", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Greater than case FilterOperators.IsGreaterThan: AppendFilter(query, ">", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Greater than or equal case FilterOperators.IsGreatherThanOrEqual: AppendFilter(query, ">=", filter, parName, metadata); sqlParameters.Add(new SqlParameter(parName, filter.Value)); break; // Start with case FilterOperators.StartsWith: AppendFilter(query, "LIKE", filter, parName, metadata); var startValue = $"{filter.Value}%"; sqlParameters.Add(new SqlParameter(parName, startValue)); break; // End with case FilterOperators.EndsWith: AppendFilter(query, "LIKE", filter, parName, metadata); var endValue = $"%{filter.Value}"; sqlParameters.Add(new SqlParameter(parName, endValue)); break; // Contain case FilterOperators.Contains: AppendFilter(query, "LIKE", filter, parName, metadata); var contValue = $"%{filter.Value}%"; sqlParameters.Add(new SqlParameter(parName, contValue)); break; // Do not contain case FilterOperators.DoesNotContain: AppendFilter(query, "NOT LIKE", filter, parName, metadata); var notContValue = $"%{filter.Value}%"; sqlParameters.Add(new SqlParameter(parName, notContValue)); break; // Empty case FilterOperators.IsEmpty: query.Append($" [{filter.Field}] = '' "); break; // Not empty case FilterOperators.IsNotEmpty: query.Append($" [{filter.Field}] <> '' "); break; default: throw new NotSupportedException($"Operator of type '{filter.Operator}' is not supported"); } }