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