public static string FormattedValue(QueryBuilderFilter filter, Func <string> valueFunc, Func <bool> isParameter, bool autoGenerated, ref string prefix)
{
var isParam = isParameter();
var val = valueFunc();
if (isParam)
{
return($"@{val}");
}
if (filter.FilterType == FilterType.In || filter.FilterType == FilterType.NotIn)
{
// strip out \r and break on \n
string[] valArray = val.Replace("\r", "").Split('\n');
return(valArray.Aggregate("", (prev, current) => { return prev + "," + FormatValueInternal(filter.TabularObject.SystemType, current); }).TrimStart(','));
}
try
{
return(FormatValueInternal(filter.TabularObject.SystemType, val));
}
catch (Exception ex)
{
if (autoGenerated)
{
prefix = $"-- ERROR creating filter: {ex.Message}\n ";
}
else
{
throw;
}
}
return(string.Empty);
}
public static string FilterExpressionBasic(QueryBuilderFilter filter, bool autoGenerated)
{
var prefix = string.Empty;
var formattedVal = FormattedValue(filter, () => filter.FilterValue, () => filter.FilterValueIsParameter, autoGenerated, ref prefix);
var colName = filter.TabularObject.DaxName;
switch (filter.FilterType)
{
case FilterType.Is:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} = {formattedVal} ))");
case FilterType.IsNot:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} <> {formattedVal} ))");
case FilterType.StartsWith:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), SEARCH( {formattedVal}, {colName}, 1, 0 ) = 1 ))");
case FilterType.Contains:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), SEARCH( {formattedVal}, {colName}, 1, 0 ) >= 1 ))");
case FilterType.DoesNotStartWith:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), NOT( SEARCH( {formattedVal}, {colName}, 1, 0 ) = 1 )))");
case FilterType.DoesNotContain:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), NOT( SEARCH( {formattedVal}, {colName}, 1, 0 ) >= 1 )))");
case FilterType.IsBlank:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), ISBLANK( {colName} )))");
case FilterType.IsNotBlank:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), NOT( ISBLANK( {colName} ))))");
case FilterType.GreaterThan:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} > {formattedVal} ))");
case FilterType.GreaterThanOrEqual:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} >= {formattedVal} ))");
case FilterType.LessThan:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} < {formattedVal} ))");
case FilterType.LessThanOrEqual:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} <= {formattedVal} ))");
case FilterType.Between:
var formattedVal2 = FormattedValue(filter, () => filter.FilterValue2, () => filter.FilterValue2IsParameter, autoGenerated, ref prefix);
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} >= {formattedVal} && {colName} <= {formattedVal2} ))");
case FilterType.NotIn:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), NOT( {colName} IN {{{formattedVal}}} )))");
case FilterType.In:
return($@"{prefix}KEEPFILTERS( FILTER( ALL( {colName} ), {colName} IN {{{formattedVal}}} ))");
default:
throw new NotSupportedException($"The filter type '{filter.FilterType}' is not supported");
}
}
public void Insert(int index, IADOTabularColumn item)
{
var filter = new QueryBuilderFilter(item, GetModelCapabilities());
// if we are 'inserting' at the end just do an add
if (index >= Items.Count)
{
Items.Add(filter);
}
Items.Insert(index, filter);
}
public static string FormattedValue(QueryBuilderFilter filter, Func <string> valueFunc)
{
var val = valueFunc();
if (filter.FilterType == FilterType.In || filter.FilterType == FilterType.NotIn)
{
// strip out \r and break on \n
string[] valArray = val.Replace("\r", "").Split('\n');
return(valArray.Aggregate("", (prev, current) => { return prev + "," + FormatValueInternal(filter.TabularObject.DataType, current); }).TrimStart(','));
}
return(FormatValueInternal(filter.TabularObject.DataType, val));
}
public static string FilterExpressionBasic(QueryBuilderFilter filter)
{
var formattedVal = FormattedValue(filter, () => filter.FilterValue);
var formattedVal2 = FormattedValue(filter, () => filter.FilterValue2);
var colName = filter.TabularObject.DaxName;
switch (filter.FilterType)
{
case FilterType.Is:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} = {formattedVal} ))");
case FilterType.IsNot:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} <> {formattedVal} ))");
case FilterType.StartsWith:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), SEARCH( {formattedVal}, {colName}, 1, 0 ) = 1 ))");
case FilterType.Contains:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), SEARCH( {formattedVal}, {colName}, 1, 0 ) >= 1 ))");
case FilterType.DoesNotStartWith:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), NOT( SEARCH( {formattedVal}, {colName}, 1, 0 ) = 1 )))");
case FilterType.DoesNotContain:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), NOT( SEARCH( {formattedVal}, {colName}, 1, 0 ) >= 1 )))");
case FilterType.IsBlank:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), ISBLANK( {colName} )))");
case FilterType.IsNotBlank:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), NOT( ISBLANK( {colName} ))))");
case FilterType.GreaterThan:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} > {formattedVal} ))");
case FilterType.GreaterThanOrEqual:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} >= {formattedVal} ))");
case FilterType.LessThan:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} < {formattedVal} ))");
case FilterType.LessThanOrEqual:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} <= {formattedVal} ))");
case FilterType.Between:
return($@"KEEPFILTERS( FILTER( ALL( {colName} ), {colName} >= {formattedVal} && {colName} <= {formattedVal2} ))");
default:
throw new NotSupportedException($"The filter type '{filter.FilterType.ToString()}' is not supported");
}
}
public static string FilterExpression(QueryBuilderFilter filter)
{
var quotes = filter.TabularObject.DataType == typeof(string) ? "\"" : string.Empty;
var formattedVal = FormattedValue(filter);
var colName = filter.TabularObject.DaxName;
switch (filter.FilterType)
{
case FilterType.Is:
return($"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} = {formattedVal})");
case Enums.FilterType.IsNot:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} <> {formattedVal})");
case FilterType.StartsWith:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), SEARCH({formattedVal}, {colName}, 1, 0) = 1)");
case FilterType.Contains:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), SEARCH({formattedVal}, {colName}, 1, 0) >= 1)");
case FilterType.DoesNotStartWith:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), NOT(SEARCH({formattedVal}, {colName}, 1, 0) = 1))");
case FilterType.DoesNotContain:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), NOT(SEARCH({formattedVal}, {colName}, 1, 0) >= 1))");
case FilterType.IsBlank:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), ISBLANK({colName}))");
case FilterType.IsNotBlank:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), NOT(ISBLANK({colName})))");
case FilterType.GreaterThan:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} > {formattedVal})");
case FilterType.GreaterThanOrEqual:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} >= {formattedVal})");
case FilterType.LessThan:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} < {formattedVal})");
case FilterType.LessThanOrEqual:
return($@"FILTER(KEEPFILTERS(VALUES( {colName} )), {colName} <= {formattedVal})");
break;
default:
throw new NotSupportedException($"The filter type '{filter.FilterType.ToString()}' is not supported");
}
}
public void Add(IADOTabularColumn item)
{
try
{
var filter = new QueryBuilderFilter(item, GetModelCapabilities(), EventAggregator);
Items.Add(filter);
NotifyOfPropertyChange(nameof(Items));
}
catch (Exception ex)
{
var msg = $"Error adding Filter to Query Builder: {ex.Message}";
Log.Error(ex, Common.Constants.LogMessageTemplate, nameof(QueryBuilderFilterList), nameof(Add), msg);
EventAggregator.PublishOnUIThreadAsync(new OutputMessage(MessageType.Error, msg));
}
}
public static string FilterExpressionTreatAs(QueryBuilderFilter filter)
{
var formattedVal = FormattedValue(filter, () => filter.FilterValue);
var colName = filter.TabularObject.DaxName;
switch (filter.FilterType)
{
case FilterType.In:
return($@"KEEPFILTERS( TREATAS( {{{formattedVal}}}, {colName} ))");
case FilterType.Is:
return($@"KEEPFILTERS( TREATAS( {{{formattedVal}}}, {colName} ))");
default:
return(FilterExpressionBasic(filter));
}
}
public static string FilterExpressionTreatAs(QueryBuilderFilter filter, bool autoGenerated)
{
var prefix = string.Empty;
var formattedVal = FormattedValue(filter, () => filter.FilterValue, () => filter.FilterValueIsParameter, autoGenerated, ref prefix);
var colName = filter.TabularObject.DaxName;
switch (filter.FilterType)
{
case FilterType.In:
return($@"{prefix}KEEPFILTERS( TREATAS( {{{formattedVal}}}, {colName} ))");
case FilterType.Is:
return($@"{prefix}KEEPFILTERS( TREATAS( {{{formattedVal}}}, {colName} ))");
default:
return(FilterExpressionBasic(filter, autoGenerated));
}
}
public static string FormattedValue(QueryBuilderFilter filter)
{
if (filter.TabularObject.DataType == typeof(DateTime))
{
DateTime parsedDate = DateTime.MinValue;
DateTime.TryParse(filter.FilterValue, out parsedDate);
if (parsedDate > DateTime.MinValue)
{
return($"DATE({parsedDate.Year},{parsedDate.Month},{parsedDate.Day})");
}
else
{
throw new ArgumentException($"Unable to parse the value '{filter.FilterValue}' as a DateTime value");
}
}
var quotes = filter.TabularObject.DataType == typeof(string) ? "\"" : string.Empty;
return($"{quotes}{filter.FilterValue}{quotes}");
}
public void Add(IADOTabularColumn item)
{
var filter = new QueryBuilderFilter(item, GetModelCapabilities());
Items.Add(filter);
}
public void Remove(QueryBuilderFilter item)
{
Items.Remove(item);
}
internal void Add(QueryBuilderFilter filter)
{
Items.Add(filter);
}
public void Remove(QueryBuilderFilter item)
{
Items.Remove(item);
NotifyOfPropertyChange(nameof(Items));
}
public void Add(IADOTabularColumn item)
{
var filter = new QueryBuilderFilter(item);
Items.Add(filter);
}