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