public void ApplyFilter_WithCase_ReturnsExpected(string value, string format, string expected)
{
var filters = new RenderFilterFactory().GetFilters();
var column = new ReportColumnMapping()
{
MetaData = new List <ReportColumnMetaDataValue>()
{
new ReportColumnMetaDataValue()
{
Name = "DataFormat",
Value = format
}
}
};
if (format == "Boolean")
{
column.DbType = DbType.Boolean;
}
var result = value;
filters.ForEach(x => result = x.ApplyFilter(result, column, null));
Assert.AreEqual(expected, result);
}
protected override void BuildGroupBy(
SelectQuery query,
ReportColumnMapping groupByColumn,
MappedSearchRequest request)
{
base.BuildGroupBy(query, request.SummarizeByColumn, request);
}
/// <summary>
/// for calculated columns, replace [] table names with actual names
/// </summary>
/// <returns></returns>
public string GetReplacedFieldName(ReportColumnMapping col, string mainTable)
{
var fieldName = col.FieldName;
string mainTableAlias = QueryHelpers.GetTableAlias(mainTable);
return(GetReplacedFieldName(fieldName, mainTableAlias));
}
protected override void BuildGroupBy(SelectQuery query, ReportColumnMapping groupByColumn, MappedSearchRequest request)
{
if (request.SummarizeByColumn != null)
{
query.GroupBy(string.Empty, GetFieldAlias(request.SummarizeByColumn, request));
}
}
public virtual bool GroupByIsChildTable(string tableAlias, ReportColumnMapping groupByColumn)
{
var table = _tableMappings.GetAllTables().First(x => x.Alias == tableAlias);
var isChild = TableIsChildOfTable(table.KnownTableName, groupByColumn.KnownTable);
return(isChild);
}
protected virtual Column GetColumSelector(
ReportColumnMapping col,
bool dontAggregate = false,
bool useFieldAlias = false)
{
// todo: this is a hack
return(_dataSourceComponents.SearchQueryBuilder.GetColumnSelector(col, null, dontAggregate: dontAggregate, useFieldAlias: useFieldAlias));
}
private int GetPrecision(ReportColumnMapping columnDefinition)
{
if (columnDefinition.MetaData != null)
{
return(columnDefinition.MetaData.GetInt("Precision"));
}
return(0);
}
protected override void BuildGroupBy(SelectQuery query, ReportColumnMapping groupByColumn, MappedSearchRequest request)
{
var actionSpecIdColumn = QueryHelpers.GetColumnMapping(_transposeKeyColumnId);
query.GroupBy(_transposeStatsTableAlias, groupByColumn.FieldName)
.GroupBy(_transposeStatsTableAlias, actionSpecIdColumn.FieldName);
StatsQueryHelpers.AddTemporalAggregationGroupBy(query, request.TemporalAggregation, _constants.StatsDateDbField);
}
public virtual string ApplyFilter(string value, ReportColumnMapping columnMapping, SearchResultRow row)
{
if (CanApply(value, columnMapping, row))
{
return(TryFormatValue(value, columnMapping, row));
}
return(value);
}
private CacheItem <ReportColumnMapping> NewCacheItem(ReportColumnMapping columnMapping)
{
var result = new CacheItem <ReportColumnMapping>
{
ExpiresTime = GetExpiryTime(columnMapping),
Value = columnMapping
};
return(result);
}
public override ReportColumnMapping CreateColumnMapping(int dataSourceId, ReportColumnMapping columnMapping, IReportColumnMappingValidator columnValidator)
{
if (!columnValidator.FieldNameIsValid(columnMapping.FieldName))
{
throw new Exception("Column FieldName is invalid");
}
columnMapping.DataSourceTypeId = dataSourceId;
_reportColumnMappingCreationService.InsertReportColumnMapping(columnMapping);
return(_reportColumnMappingQueryService.GetReportColumnMappingsById(dataSourceId, new[] { columnMapping.Id }).FirstOrDefault());
}
protected override string TryFormatValue(string value, ReportColumnMapping columnMapping, SearchResultRow row)
{
double parsed;
if (double.TryParse(value, out parsed))
{
//return "?" + parsed.ToString("0.00");
return(parsed.ToString("0.00"));
}
return(value);
}
protected virtual Dictionary <ReportColumnMapping, string> GetNestedColumns(ReportColumnMapping calcCol)
{
if (calcCol.NestedColumns == null)
{
// get the generated field name
var fieldName = GetColumSelector(calcCol).FullName;
var foundColumns = _calculatedColumnHelper.FindColumnsInCalculatedField(fieldName);
calcCol.NestedColumns = foundColumns;
}
return(calcCol.NestedColumns);
}
public virtual bool IsSelectableForGroupBy(ReportColumnMapping column, ReportColumnMapping groupBy)
{
var groupByTable = groupBy.KnownTable;
if (GetTableType(groupByTable) == TableType.Stats)
{
return(false);
}
return(true);
}
public virtual List <ColumnDefinition> GetAllSelectableColumnDefinitions(int dataSourceId, int?organizationId, int?groupBy = null)
{
ReportColumnMapping groupByColumn = null;
if (groupBy != null)
{
groupByColumn = GetColumnMappings(dataSourceId, new[] { groupBy.Value }).Single();
}
return(GetAllColumnMappings(dataSourceId, organizationId).Where(x => IsSelectable(x, groupByColumn)).Select(x => new ColumnDefinition(x)).ToList());
}
public override Column GetColumnSelector(ReportColumnMapping col, MappedSearchRequest request, bool dontAggregate = false, bool useFieldAlias = false)
{
var result = _dataSourceComponents.QueryBuilderBase.GetColumnSelector(col, request, dontAggregate: dontAggregate, useFieldAlias: useFieldAlias);
if (result.Field.Name.StartsWith("_C_"))
{
result.Field.Name = _constants.CountKeyAlias;
}
return(result);
}
protected override string GetFieldAlias(ReportColumnMapping column, MappedSearchRequest request)
{
var result = QueryHelpers.GetFieldName(column);
// to allow selecting both min and max together (not working with custom columns - its complicated)
// depends on DefaultStatsQueryBuilder.GetColumnSelector()
// if (RestrictGroupBy(request).Id != column.Id && result != _constants.CountKeyAlias)
// {
// result += "_" + column.FieldAggregationMethod;
// }
return(result);
}
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var fromTableName = request.SummarizeByColumn.KnownTable;
var fromTable = _tableMappings.GetTableMapping(fromTableName);
query.From(fromTable.DbTableName, fromTable.Alias);
}
public void InsertReportColumnMapping(ReportColumnMapping value)
{
using (var scope = _reportColumnMappingRepository.CreateTransaction())
{
_reportColumnMappingRepository.Add(value, scope);
scope.Commit();
}
// reload mappings
_columnProviderCacheService.ClearMappings(value.DataSourceTypeId, value.OrganizationId);
_reportColumnMappingQueryService.GetReportColumnMappingsByOrganizationId(value.DataSourceTypeId, value.OrganizationId); // to ensure that all columns are reloaded
}
protected override string TryFormatValue(string value, ReportColumnMapping columnMapping, SearchResultRow row)
{
double parsed;
if (value.Contains(".") && double.TryParse(value, out parsed))
{
var toPrecision = Math.Round(parsed, columnMapping.MetaData.GetInt(MetaDataKey));
return(toPrecision.ToString());
}
return(value);
}
private void UpdateMetaData(ReportColumnMapping entity, ReportColumnMapping value)
{
entity.MetaData.Clear();
foreach (var update in value.MetaData)
{
entity.MetaData.Add(new ReportColumnMetaDataValue()
{
Name = update.Name,
Value = update.Value
});
}
}
private DateTime GetExpiryTime(ReportColumnMapping columnMapping)
{
var existingForOrg = _cache.Where(x =>
x.Value.Value.DataSourceTypeId == columnMapping.DataSourceTypeId &&
x.Value.Value.OrganizationId == columnMapping.OrganizationId).Select(x => x.Value)
.FirstOrDefault();
if (existingForOrg != null)
{
return(existingForOrg.ExpiresTime);
}
return(DateTime.Now.AddMinutes(_cacheTimeMinutes));
}
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
// idea here is to be able to get a key which can be joined on, so select from the table we're building the CTE from
// then join on the tables required to get to the join key (which can be used as the group key)
_rootTableName = request.GroupByColumn.KnownTable;
var fromTable = _tableMappings.GetTableMapping(_fromTableName);
var addedTables = new List <string>();
if (CanJoinTables(_fromTableName, _rootTableName))
{
query.From(fromTable.DbTableName, fromTable.Alias);
}
else
{
var graphBuilder = new TableRelationshipGraphBuilder();
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _rootTableName);
// todo : use path
//var path = graphBuilder.GetPathFromTableToRoot(relationshipGraph, _fromTableName);
var distance = graphBuilder.GetDistance(relationshipGraph, _fromTableName);
query.From(fromTable.DbTableName, fromTable.Alias);
string currentTableName = fromTable.KnownTableName;
while (distance > 1)
{
var potentialNodes = graphBuilder.GetByDistance(relationshipGraph, distance - 1);
var parentNode = potentialNodes.SingleOrDefault(x => x.Relations.Any(y => y.TableName == currentTableName));
if (parentNode == null)
{
throw new Exception(string.Format("Could not find a relationship between {0} and {1}", currentTableName, relationshipGraph.TableName));
}
JoinTables(query, parentNode.TableName, currentTableName);
currentTableName = parentNode.TableName;
distance--;
addedTables.Add(parentNode.TableName);
if (CanJoinTables(_fromTableName, currentTableName) && CanJoinTables(currentTableName, _rootTableName))
{
break;
}
}
}
}
protected override string TryFormatValue(string value, ReportColumnMapping columnMapping, SearchResultRow row)
{
long parsed;
if (long.TryParse(value, out parsed))
{
if (parsed > 0)
{
var date = parsed.DateTimeFromUnixTime();
return(date.ToString("dd/MM/yyyy HH:mm:ss"));
}
}
return(value);
}
protected override void BuildOrderBy(SelectQuery query, ReportColumnMapping sortColumn, MappedSearchRequest request)
{
bool isSummarizing = request.SummarizeByColumn != null;
if (isSummarizing)
{
var dir = request.SortDescending ? OrderDir.Desc : OrderDir.Asc;
query.OrderBy(string.Empty, _dataSourceComponents.QueryHelpers.GetFieldAlias(request.SortByColumn), dir);
}
else
{
query.OrderBy(string.Empty, "_ROW");
}
}
/// <summary>
/// Gets the Column with alias for non calculated columns
/// Gets the Column without alias for calculated columns and handles the conversion of calculated columns to sql
/// For calculated colums, the column will be expanded to a full formula
/// Sets the result on the column mapping so that it doesnt need to be re-calculated - this works with the cache
/// </summary>
public virtual Column GetColumnSelector(ReportColumnMapping col, MappedSearchRequest request, bool dontAggregate = false, bool useFieldAlias = false)
{
var cached = col.CalculatedValues.GetColumnTableAndField(dontAggregate, useFieldAlias);
if (cached != null)
{
return(new Column(cached[0], cached[1]));
}
var result = GetColumSelectorInternal(col, dontAggregate, useFieldAlias);
col.CalculatedValues.SetColumnTableAndField(dontAggregate, useFieldAlias, result.TableAlias, result.Field.Name);
return(result);
}
public virtual string GetFieldAlias(ReportColumnMapping column)
{
// this is just to help us debug sql, we could just use the id alone
var rgx = new Regex("[^a-zA-Z0-9_]");
var friendlyName = rgx.Replace(column.UniqueName, "");
var result = "c" + friendlyName + "_" + column.Id;
if (column.ActionSpecId > 0 && IsTransposeStatColumn(column))
{
result += "_" + column.ActionSpecId;
}
return(result);
}
public virtual string GetDataJoinTable(ReportColumnMapping groupByColumn, List <TableRelationship> statsRelationships, TableRelationshipGraphBuilder graphBuilder)
{
var statsJoinTables = statsRelationships
.Select(x => x.Table1 is StatsTableMapping ? x.Table2.KnownTableName : x.Table1.KnownTableName)
.ToList();
// find the nearest stats table to the group by table.
var graph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), groupByColumn.KnownTable);
graphBuilder.TrimToTables(graph, statsJoinTables);
var dataTables = graphBuilder.GetAllTables(graph).Distinct().ToList();
dataTables = dataTables.OrderBy(x => graphBuilder.GetDistance(graph, x)).ToList();
var joinTable = dataTables.FirstOrDefault(statsJoinTables.Contains);
return(joinTable);
}
// Build
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var graphBuilder = new TableRelationshipGraphBuilder();
var dataJoinTable = _dataSourceComponents.StatsQueryBuilder.GetDataJoinTable(request.GroupByColumn, statsRelationships, graphBuilder);
//-- Find out which stats table or view we need to query to satisfy the requested temporal
// aggregation and type of date range.
var statsTableName = GetStatsTableName(
_statsTableName,
dataJoinTable,
request.TemporalAggregation,
request.DateRangeType,
request.DateStart,
request.DateEnd);
query.From(statsTableName, _statsTableAlias);
JoinTables(query, dataJoinTable, _statsTableName);
// join up to the root table
var addedTables = new Dictionary <string, bool> {
{ dataJoinTable, false }
};
_dataSourceComponents.StatsQueryBuilder.AddDataTableJoinsUpToRoot(query, dataJoinTable, graphBuilder, addedTables);
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !addedTables.ContainsKey(currencyColumn.KnownTable))
{
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
AddFromTableJoin(query, groupByColumn, currencyColumn.KnownTable, ref addedTables, graphBuilder, relationshipGraph);
}
}
public virtual void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var tables = request.SelectedAndDependantColumns.Select(x => x.KnownTable).ToList();
tables.Add(groupByColumn.KnownTable);
tables = tables.Distinct().ToList();
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !tables.Contains(currencyColumn.KnownTable))
{
tables.Add(currencyColumn.KnownTable);
}
var rootTableMapping = _tableMappings.GetTableMapping(_constants.RootTableName);
query.From(rootTableMapping.DbTableName, rootTableMapping.Alias);
var graphBuilder = new TableRelationshipGraphBuilder();
// bulild a graph of the table relationships to calculate the distance from the root table
// include missing tables needed for joins
// order the tables by distance
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
graphBuilder.TrimToTables(relationshipGraph, tables);
tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList();
tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList();
// dictionary of TableName, IsAggregated for tables which have been added to the query
var addedTables = new Dictionary <string, bool> {
{ _constants.RootTableName, false }
};
foreach (var table in tables.Distinct())
{
AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph);
}
}
