예제 #1
0
        private static string GetSqlFilter(QueryClause clause, ColumnConfiguration columnConfiguration, int index, DynamicParameters queryParameters, DateTime companyDate)
        {
            string sql;
            string fieldType = columnConfiguration.FilterType;

            switch (clause.Operator)
            {
            case FilterColumnOperators.Equal:
            {
                if (string.IsNullOrWhiteSpace(clause.Value1))
                {
                    throw new AtlasTechnicalException($"Value is missing for field {columnConfiguration.FieldId}.");
                }

                if (clause.Value1.Contains("%"))
                {
                    sql = $"[{columnConfiguration.FieldName}] LIKE @{columnConfiguration.FieldName}{index}";
                }
                else
                {
                    if (fieldType == FilterColumnTypes.Date)
                    {
                        sql = $"[{columnConfiguration.FieldName}] >= @{columnConfiguration.FieldName}{index} AND [{columnConfiguration.FieldName}] < DATEADD(day, 1, @{columnConfiguration.FieldName}{index})";
                    }
                    else
                    {
                        sql = $"[{columnConfiguration.FieldName}] = @{columnConfiguration.FieldName}{index}";
                    }
                }

                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));

                break;
            }

            case FilterColumnOperators.Empty:
            {
                if (IsStringFieldType(fieldType))
                {
                    sql = $"ISNULL([{columnConfiguration.FieldName}], '') = ''";
                }
                else
                {
                    sql = $"[{columnConfiguration.FieldName}] IS NULL";
                }

                break;
            }

            case FilterColumnOperators.NotEmpty:
            {
                if (IsStringFieldType(fieldType))
                {
                    sql = $"ISNULL([{columnConfiguration.FieldName}], '') <> ''";
                }
                else
                {
                    sql = $"[{columnConfiguration.FieldName}] IS NOT NULL";
                }

                break;
            }

            case FilterColumnOperators.NotEqual:
            {
                if (fieldType == FilterColumnTypes.Date)
                {
                    sql = $"([{columnConfiguration.FieldName}] < @{columnConfiguration.FieldName}{index} OR [{columnConfiguration.FieldName}] >= DATEADD(day, 1, @{columnConfiguration.FieldName}{index}))";
                }
                else
                {
                    sql = $"[{columnConfiguration.FieldName}] <> @{columnConfiguration.FieldName}{index}";
                }

                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            case FilterColumnOperators.GreaterThan:
            {
                sql = $"[{columnConfiguration.FieldName}] > @{columnConfiguration.FieldName}{index}";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            case FilterColumnOperators.LessThan:
            {
                sql = $"[{columnConfiguration.FieldName}] < @{columnConfiguration.FieldName}{index}";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            case FilterColumnOperators.GreaterThanEquals:
            {
                sql = $"[{columnConfiguration.FieldName}] >= @{columnConfiguration.FieldName}{index}";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            case FilterColumnOperators.LessThanEquals:
            {
                sql = $"[{columnConfiguration.FieldName}] <= @{columnConfiguration.FieldName}{index}";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            case FilterColumnOperators.In:
            {
                if (string.IsNullOrWhiteSpace(clause.Value1))
                {
                    throw new AtlasTechnicalException($"Value is missing for field {columnConfiguration.FieldId}.");
                }

                sql = $"[{columnConfiguration.FieldName}] IN @{columnConfiguration.FieldName}{index}";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}", clause.Value1
                                    .Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                                    .Select(s => GetValue(s.Trim(), fieldType, columnConfiguration.FieldName, companyDate)));
                break;
            }

            case FilterColumnOperators.Between:
            {
                sql = $"[{columnConfiguration.FieldName}] BETWEEN @{columnConfiguration.FieldName}{index}_1 AND @{columnConfiguration.FieldName}{index}_2";
                queryParameters.Add($"{columnConfiguration.FieldName}{index}_1", GetValue(clause.Value1, fieldType, columnConfiguration.FieldName, companyDate));
                queryParameters.Add($"{columnConfiguration.FieldName}{index}_2", GetValue(clause.Value2, fieldType, columnConfiguration.FieldName, companyDate));
                break;
            }

            default: throw new AtlasTechnicalException($"Unknown operator: {clause.Operator}");
            }

            return(sql);
        }
예제 #2
0
        private static void BuildFilters(StringBuilder query, DynamicParameters queryParameters, QueryClause clauses, List <ColumnConfiguration> columnConfigurations, string company, int?dataVersionId, DateTime companyDate, List <long> userDepartments = null)
        {
            bool hasWhere = false;

            if (!string.IsNullOrWhiteSpace(company))
            {
                query.Append(" WHERE [CompanyId] = @CompanyId");
                // https://github.com/StackExchange/Dapper#ansi-strings-and-varchar
                queryParameters.Add("@CompanyId", new DbString {
                    Value = company, IsFixedLength = true, Length = 2, IsAnsi = true
                });

                hasWhere = true;
            }

            if (dataVersionId != null)
            {
                query.Append(hasWhere ? " AND" : " WHERE");

                query.Append(" [DataVersionId] = @DataVersionId");
                queryParameters.Add("@DataVersionId", dataVersionId);

                hasWhere = true;
            }

            if (userDepartments != null && userDepartments.Count > 0)
            {
                query.Append(hasWhere ? " AND" : " WHERE");

                query.Append(" DepartmentId IN @Departments");

                queryParameters.Add("@Departments", userDepartments);

                hasWhere = true;
            }

            if (clauses != null && ((clauses.Clauses != null && clauses.Clauses.Any()) || clauses.FieldId != null))
            {
                query.Append(hasWhere ? " AND" : " WHERE");

                int index      = 0;
                var filterText = BuildFilterForClauses(clauses, queryParameters, ref index, columnConfigurations, companyDate);

                query.Append(" ");
                query.Append(filterText);
            }
        }
예제 #3
0
        private static string BuildFilterForClauses(QueryClause clauses, DynamicParameters queryParameters, ref int index, List <ColumnConfiguration> columnConfigurations, DateTime companyDate)
        {
            if (clauses.Clauses != null && clauses.Clauses.Any())
            {
                string sqlOperator;
                if (string.IsNullOrEmpty(clauses.LogicalOperator) || clauses.LogicalOperator == LogicalOperation.And)
                {
                    sqlOperator = "AND";
                }
                else if (clauses.LogicalOperator == LogicalOperation.Or)
                {
                    sqlOperator = "OR";
                }
                else
                {
                    throw new AtlasTechnicalException($"Invalid value for LogicalOperator: {clauses.LogicalOperator}");
                }

                var clausesQuery = new StringBuilder();

                if (clauses.LogicalOperator == LogicalOperation.Or)
                {
                    clausesQuery.Append("(");
                }

                int innerIndex = 0;
                foreach (var clause in clauses.Clauses)
                {
                    innerIndex++;
                    var filterText = BuildFilterForClauses(clause, queryParameters, ref index, columnConfigurations, companyDate);

                    clausesQuery.Append(innerIndex == 1 ? $"{filterText}" : $" {sqlOperator} {filterText}");
                }

                if (clauses.LogicalOperator == LogicalOperation.Or)
                {
                    clausesQuery.Append(")");
                }

                return(clausesQuery.ToString());
            }
            else
            {
                var columnConfiguration = columnConfigurations.FirstOrDefault(c => c.FieldId == clauses.FieldId);

                if (columnConfiguration == null)
                {
                    throw new AtlasTechnicalException($"The column {clauses.FieldId} is not configured to be queryable.");
                }

                if (!columnConfiguration.IsFilterable)
                {
                    throw new AtlasTechnicalException($"The column {clauses.FieldId} is not configured to be filterable.");
                }

                index++;
                var filterText = GetSqlFilter(clauses, columnConfiguration, index, queryParameters, companyDate);

                return(filterText);
            }
        }
예제 #4
0
        public static string FormatClause(QueryClause clause, List <ColumnConfiguration> columnConfigurations)
        {
            var columnConfiguration = columnConfigurations.FirstOrDefault(c => c.FieldId == clause.FieldId);

            if (columnConfiguration == null)
            {
                throw new AtlasTechnicalException($"The column {clause.FieldId} is not configured.");
            }

            switch (clause.Operator)
            {
            case FilterColumnOperators.Equal:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} = {clause.Value1}");
            }

            case FilterColumnOperators.Empty:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} IS EMPTY");
            }

            case FilterColumnOperators.NotEmpty:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} IS NOT EMPTY");
            }

            case FilterColumnOperators.NotEqual:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} != {clause.Value1}");
            }

            case FilterColumnOperators.GreaterThan:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} > {clause.Value1}");
            }

            case FilterColumnOperators.LessThan:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} < {clause.Value1}");
            }

            case FilterColumnOperators.GreaterThanEquals:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} >= {clause.Value1}");
            }

            case FilterColumnOperators.LessThanEquals:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} <= {clause.Value1}");
            }

            case FilterColumnOperators.In:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} IN ({clause.Value1})");
            }

            case FilterColumnOperators.Between:
            {
                return($"{columnConfiguration.FriendlyName ?? columnConfiguration.FieldName} BETWEEN {clause.Value1} AND {clause.Value2}");
            }

            default: throw new AtlasTechnicalException($"Unknown operator: {clause.Operator}");
            }
        }