Exemple #1
0
        public static List <Tuple <string, string> > BuildReportFilters(DynamicQueryDefinition dynamicQueryDefinition, List <ColumnConfiguration> columnConfigurations, DateTime companyDate)
        {
            CheckArg(dynamicQueryDefinition, nameof(dynamicQueryDefinition));
            CheckArg(columnConfigurations, nameof(columnConfigurations));

            List <Tuple <string, string> > filters = new List <Tuple <string, string> >();
            int index = 0;

            // For report filters we only use the first level of clauses and only with AND operator
            if (dynamicQueryDefinition.Clauses != null)
            {
                foreach (var clause in dynamicQueryDefinition.Clauses.Clauses)
                {
                    var columnConfiguration = columnConfigurations.FirstOrDefault(c => c.FieldId == clause.FieldId);

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

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

                    var queryParameters = new DynamicParameters();

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

                    foreach (var parameterName in queryParameters.ParameterNames)
                    {
                        var formatedValue = GetFormatedValue(queryParameters.Get <object>(parameterName), columnConfiguration.FilterType);

                        filterText = filterText.Replace($"@{parameterName}", formatedValue);
                    }

                    filters.Add(new Tuple <string, string>(filterText, clause.GroupName));
                }
            }

            return(filters);
        }
Exemple #2
0
        public static BuildQueryResult BuildQuery(string company, string userId, DynamicQueryDefinition dynamicQueryDefinition, string viewName, List <ColumnConfiguration> columnConfigurations, DateTime companyDate, int?dataVersionId = null, List <long> userDepartments = null)
        {
            CheckArg(userId, nameof(userId));
            CheckArg(dynamicQueryDefinition, nameof(dynamicQueryDefinition));
            CheckArg(viewName, nameof(viewName));
            CheckArg(columnConfigurations, nameof(columnConfigurations));

            var result = new BuildQueryResult();

            var queryParameters = new DynamicParameters();

            var query = new StringBuilder();

            query.Append($"SELECT TotalCount = COUNT(*) OVER(), * FROM {viewName}");

            // Filters
            BuildFilters(query, queryParameters, dynamicQueryDefinition.Clauses, columnConfigurations, company, dataVersionId, companyDate, userDepartments);

            // Ordering
            query.Append(" ORDER BY");

            // Ordering from the request
            if (dynamicQueryDefinition.SortColumns.Any())
            {
                int index = 0;
                foreach (var filterColumn in dynamicQueryDefinition.SortColumns)
                {
                    var columnConfiguration = columnConfigurations.FirstOrDefault(c => c.FieldId == filterColumn.FieldId);

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

                    if (!columnConfiguration.IsSortable)
                    {
                        throw new AtlasTechnicalException($"The column {filterColumn.FieldId} is not configured to be sortable.");
                    }

                    index++;
                    var filterText = GetSqlSort(columnConfiguration.FieldName, filterColumn.SortOrder);
                    query.Append(index == 1 ? $" {filterText}" : $", {filterText}");
                }
            }
            else
            {
                // Ordering from the config
                var sortableColumns =
                    (from config in columnConfigurations
                     where config.IsSortable && config.SortOrderIndex > 0
                     orderby config.SortOrderIndex
                     select config).ToList();

                if (sortableColumns.Any())
                {
                    int index = 0;
                    foreach (var columns in sortableColumns)
                    {
                        index++;
                        var filterText = GetSqlSort(columns.FieldName, columns.SortOrder ?? "ASC");
                        query.Append(index == 1 ? $" {filterText}" : $", {filterText}");
                    }
                }
                else
                {
                    // Fallback ordering
                    var defaultSortColumn = columnConfigurations.Where(c => c.IsSortable).OrderBy(c => c.SortOrderIndex).FirstOrDefault() ?? columnConfigurations.FirstOrDefault();

                    query.Append(defaultSortColumn != null ? $" {defaultSortColumn.FieldName} ASC" : " 1");
                }
            }

            // Pagination
            query.Append(" OFFSET @OffsetRows ROWS FETCH NEXT @fetchRows ROWS ONLY;");
            queryParameters.Add("OffsetRows", dynamicQueryDefinition.Offset);
            queryParameters.Add("FetchRows", dynamicQueryDefinition.Limit);

            result.Sql        = query.ToString();
            result.Parameters = queryParameters;

            return(result);
        }