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); }
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); }