public void RemoveUnusedColumns_WithCount_Ensure_AggregateExpressionWithGrouping_Removed( )
{
ResourceEntity resNode = new ResourceEntity( );
AggregateEntity aggNode = new AggregateEntity {
GroupedEntity = resNode
};
aggNode.GroupBy.Add(new ResourceDataColumn( ));
SelectColumn col1 = new SelectColumn {
Expression = new ResourceDataColumn( )
};
SelectColumn col2 = new SelectColumn {
Expression = new AggregateExpression {
NodeId = aggNode.NodeId
}
};
StructuredQuery query = new StructuredQuery( );
query.RootEntity = aggNode;
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate( );
agg.AggregatedColumns.Add(new ReportAggregateField {
AggregateMethod = AggregateMethod.Count
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(2));
Assert.That(result.SelectColumns [0].Expression, Is.TypeOf <IdExpression>( ));
Assert.That(result.SelectColumns [1].Expression, Is.TypeOf <AggregateExpression>( ));
}
public IActionResult CreateClient([FromBody] ClientAggregate clientDto)
{
var client = new Client(clientDto);
_clientRepository.Create(client);
return(Ok("Success"));
}
/// <summary>
/// Patch up the client aggregate with counts _if_ rollup grand totals or sub totals are requested and there is no count included in the client aggregate.
/// </summary>
/// <param name="clientAggregate"></param>
/// <param name="query"></param>
public static void EnsureShowTotalsHasCount(StructuredQuery query, ClientAggregate clientAggregate)
{
// if ((rollupGrandTotals || rollupSubTotals) && clientAggregate.GroupedColumns.Any()) //<< remove commented code and delete the follow_if_ when the builder sets these flags!
if (clientAggregate.GroupedColumns.Count > 0)
{
ReportGroupField reportGroupField = clientAggregate.GroupedColumns.First( );
// Check to see if the first column group contains a count in the aggregate
if (clientAggregate.AggregatedColumns.FirstOrDefault(ac => ac.AggregateMethod == AggregateMethod.Count) == null)
{
// Inject a count aggregate for a column that is _not_ a group column
SelectColumn column = query.SelectColumns.FirstOrDefault(sc => clientAggregate.GroupedColumns.All(gc => gc.ReportColumnId != sc.ColumnId));
if (column != null)
{
clientAggregate.AggregatedColumns.Add(new ReportAggregateField
{
AggregateMethod = AggregateMethod.Count,
IncludedCount = true,
ReportColumnId = column.ColumnId,
ReportColumnEntityId = column.EntityId,
ShowGrandTotals = reportGroupField.ShowGrandTotals,
ShowRowCounts = reportGroupField.ShowRowCounts,
ShowOptionLabel = reportGroupField.ShowOptionLabel,
ShowRowLabels = reportGroupField.ShowRowLabels,
ShowSubTotals = reportGroupField.ShowSubTotals
});
}
}
}
}
public async Task Update(int id, ClientAggregate clientAggregate)
{
var client = _clientRepository.Get(id).Result;
if (client == null)
{
throw new Exception("User not found");
}
if (clientAggregate.Email != client.Email)
{
// username has changed so check if the new username is already taken
if (await _clientRepository.GetAny(clientAggregate.Email))
{
throw new Exception("Username " + clientAggregate.Email + " is already taken");
}
}
// update password if it was entered
if (!string.IsNullOrWhiteSpace(clientAggregate.Password))
{
CreatePasswordHash(clientAggregate.Password, out var passwordHash, out var passwordSalt);
client.PasswordHash = passwordHash;
client.PasswordSalt = passwordSalt;
}
client.Update(clientAggregate);
_clientRepository.Update(id, client);
}
public Client Create(ClientAggregate clientAggregate)
{
// validation
if (string.IsNullOrWhiteSpace(clientAggregate.Password))
{
throw new Exception("Password is required");
}
if (_clientRepository.GetAny(clientAggregate.Email).Result)
{
throw new Exception("Username \"" + clientAggregate.Email + "\" is already taken");
}
CreatePasswordHash(clientAggregate.Password, out var passwordHash, out var passwordSalt);
var client = new Client(clientAggregate)
{
PasswordHash = passwordHash,
PasswordSalt = passwordSalt
};
_clientRepository.Create(client);
return(client);
}
public void RemoveUnusedColumns_SupportQuickSearch_WithCount_Ensure_FieldExpression_NotReplaced()
{
SelectColumn col1 = new SelectColumn {
Expression = new ResourceDataColumn()
};
SelectColumn col2 = new SelectColumn {
Expression = new ResourceDataColumn()
};
StructuredQuery query = new StructuredQuery();
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate();
agg.AggregatedColumns.Add(new ReportAggregateField {
AggregateMethod = AggregateMethod.Count
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg, true);
Assert.That(result.SelectColumns.Count, Is.EqualTo(2));
Assert.That(result.SelectColumns[0].Expression, Is.TypeOf <ResourceDataColumn>());
Assert.That(result.SelectColumns[1].Expression, Is.TypeOf <ResourceDataColumn>());
Assert.IsTrue(result.SelectColumns[0].IsHidden);
Assert.IsTrue(result.SelectColumns[1].IsHidden);
}
public void RemoveUnusedColumns_ReturnClone()
{
StructuredQuery query = new StructuredQuery( );
ClientAggregate agg = new ClientAggregate( );
var result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result, Is.Not.SameAs(query));
}
public async Task <Guid> Create(ClientCreateRequest request)
{
var client = ClientAggregate.CreateFromRequest(request);
await _repo.Add(client);
return(client.Id);
}
public void ClientAggregate_ShouldBeDeleted()
{
//Arrange
var request = AggregatesRequestBuilder.CreateRandomClientCreateRequest();
var client = ClientAggregate.CreateFromRequest(request);
//Act
client.MarkAsDeleted();
//Assert
client.IsDeleted.Should().BeTrue();
}
public void ClientAggregate_CreateRandomFromRequest()
{
//Arrange
var request = AggregatesRequestBuilder.CreateRandomClientCreateRequest();
//Act
var client = ClientAggregate.CreateFromRequest(request);
// Assert
client.UserId.Should().Be(request.UserId);
client.IsDeleted.Should().BeFalse();
}
/// <summary>
/// Generate client-aggregate information for any rollup instructions passed in the query parameters, but not otherwise
/// part of the underlying report.
/// </summary>
/// <param name="groupAggregateRules">The aggregate settings.</param>
/// <param name="query">The query.</param>
/// <returns>A client aggregate object.</returns>
private static ClientAggregate ApplyAdhocAggregates(ReportMetadataAggregate groupAggregateRules, StructuredQuery query)
{
ClientAggregate clientAggregate = new ClientAggregate();
clientAggregate.IncludeRollup = groupAggregateRules.IncludeRollup;
if (groupAggregateRules.Groups != null && groupAggregateRules.Groups.Count > 0)
{
clientAggregate.GroupedColumns = new List <ReportGroupField>();
foreach (KeyValuePair <long, GroupingDetail> kvp in groupAggregateRules.Groups.SelectMany(group => group))
{
long columnEntityId = kvp.Key;
GroupingDetail groupingDetail = kvp.Value;
clientAggregate.GroupedColumns.Add(new ReportGroupField
{
ReportColumnId = query.SelectColumns.First(sc => sc.EntityId == columnEntityId).ColumnId,
GroupMethod = (GroupMethod)Enum.Parse(typeof(GroupMethod), groupingDetail.Style.Substring(5), true), // Remove 'group' from the string here
ReportColumnEntityId = columnEntityId,
ShowGrandTotals = groupAggregateRules.ShowGrandTotals,
ShowSubTotals = groupAggregateRules.ShowSubTotals,
ShowRowCounts = groupAggregateRules.ShowCount,
ShowRowLabels = groupAggregateRules.ShowGroupLabel,
ShowOptionLabel = groupAggregateRules.ShowOptionLabel
});
}
}
if (groupAggregateRules.Aggregates != null && groupAggregateRules.Aggregates.Count > 0)
{
clientAggregate.AggregatedColumns = new List <ReportAggregateField>();
foreach (KeyValuePair <long, List <AggregateDetail> > kvp in groupAggregateRules.Aggregates)
{
long columnEntityId = kvp.Key; // will be zero for count, which does not apply to any specific column
List <AggregateDetail> aggregates = kvp.Value;
foreach (AggregateDetail aggregateDetail in aggregates)
{
clientAggregate.AggregatedColumns.Add(new ReportAggregateField
{
ReportColumnId = columnEntityId == 0 ? Guid.Empty : query.SelectColumns.First(sc => sc.EntityId == columnEntityId).ColumnId,
AggregateMethod = (AggregateMethod)Enum.Parse(typeof(AggregateMethod), aggregateDetail.Style.Substring(3), true), // Remove 'agg' from the string here
ReportColumnEntityId = columnEntityId,
ShowGrandTotals = groupAggregateRules.ShowGrandTotals,
ShowSubTotals = groupAggregateRules.ShowSubTotals,
ShowRowCounts = groupAggregateRules.ShowCount,
ShowRowLabels = groupAggregateRules.ShowGroupLabel,
ShowOptionLabel = groupAggregateRules.ShowOptionLabel,
IncludedCount = columnEntityId == 0
});
}
}
}
return(clientAggregate);
}
public IActionResult Register([FromBody] ClientAggregate clientAggregate)
{
try
{
// save
_userService.Create(clientAggregate);
return(Ok());
}
catch (Exception ex)
{
// return error message if there was an exception
return(BadRequest(new { message = ex.Message }));
}
}
public void RemoveUnusedColumns_EnsureAtLeastOneColumnRemains( )
{
SelectColumn col1 = new SelectColumn( );
SelectColumn col2 = new SelectColumn( );
StructuredQuery query = new StructuredQuery( );
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate( );
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(1));
Assert.That(result.SelectColumns [0].ColumnId, Is.EqualTo(col1.ColumnId));
}
public void RemoveUnusedColumns_AggregateColumn_Not_Removed( )
{
SelectColumn col1 = new SelectColumn( );
SelectColumn col2 = new SelectColumn( );
StructuredQuery query = new StructuredQuery( );
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
ClientAggregate agg = new ClientAggregate( );
agg.AggregatedColumns.Add(new ReportAggregateField {
ReportColumnId = col2.ColumnId, AggregateMethod = AggregateMethod.Max
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(1));
Assert.That(result.SelectColumns [0].ColumnId, Is.EqualTo(col2.ColumnId));
}
public void RemoveUnusedColumns_EnsureAnalyzerColumn_Not_Removed( )
{
SelectColumn col1 = new SelectColumn( );
SelectColumn col2 = new SelectColumn( );
QueryCondition cond1 = new QueryCondition {
Expression = new ColumnReference {
ColumnId = col2.ColumnId
}
};
StructuredQuery query = new StructuredQuery( );
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
query.Conditions.Add(cond1);
ClientAggregate agg = new ClientAggregate( );
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(1));
Assert.That(result.SelectColumns [0].ColumnId, Is.EqualTo(col2.ColumnId));
}
public void RemoveUnusedColumns_EnsureUnusedOrderbyRemoved( )
{
SelectColumn col1 = new SelectColumn( );
SelectColumn col2 = new SelectColumn( );
SelectColumn col3 = new SelectColumn( );
OrderByItem order1 = new OrderByItem {
Expression = new ColumnReference {
ColumnId = col1.ColumnId
}, Direction = OrderByDirection.Ascending
};
OrderByItem order2 = new OrderByItem {
Expression = new ColumnReference {
ColumnId = col2.ColumnId
}, Direction = OrderByDirection.Descending
};
StructuredQuery query = new StructuredQuery( );
query.SelectColumns.Add(col1);
query.SelectColumns.Add(col2);
query.SelectColumns.Add(col3);
query.OrderBy.Add(order1);
query.OrderBy.Add(order2);
ClientAggregate agg = new ClientAggregate( );
agg.AggregatedColumns.Add(new ReportAggregateField {
ReportColumnId = col2.ColumnId, AggregateMethod = AggregateMethod.Max
});
StructuredQuery result = ReportRollupHelper.RemoveUnusedColumns(query, agg);
Assert.That(result.SelectColumns.Count, Is.EqualTo(1), "Cols");
Assert.That(result.SelectColumns [0].ColumnId, Is.EqualTo(col2.ColumnId));
Assert.That(result.OrderBy.Count, Is.EqualTo(1), "Order");
Assert.That(result.OrderBy [0].Direction, Is.EqualTo(OrderByDirection.Descending));
}
private PreparedQuery PrepareReportRollupRun(Model.Report report, StructuredQuery structuredQuery, ReportSettings reportSettings, QuerySettings nonRollupQuerySettings)
{
StructuredQuery rollupQuery = null;
ClientAggregate clientAggregate = null;
StructuredQuery optimisedQuery;
QuerySettings rollupSettings;
bool adhocRollup;
bool reportRollup;
adhocRollup = reportSettings.ReportParameters != null && reportSettings.ReportParameters.GroupAggregateRules != null;
reportRollup = !adhocRollup && report.ReportColumns.Any(rc => rc.ColumnRollup.Count > 0 || rc.ColumnGrouping.Count > 0);
if (adhocRollup)
{
clientAggregate = ApplyAdhocAggregates(reportSettings.ReportParameters.GroupAggregateRules, structuredQuery);
}
else if (reportRollup)
{
clientAggregate = new ClientAggregate(report, structuredQuery);
clientAggregate.IncludeRollup = true;
}
else if (report.RollupGrandTotals != null || report.RollupSubTotals != null || report.RollupOptionLabels != null)
{
return(new PreparedQuery
{
ClientAggregate = new ClientAggregate( )
});
}
else
{
return(new PreparedQuery( ));
}
// Clone the query, so that runs and rollups won't intefere with each others caches if they mutate the structure
// In particular, calculated columns get evaluated during execution and mutate the query .. but only if the result doesn't come from cache, but this interferes with the rollup cache key.
// Ideally, both calculations and optimisations would be provided in layers, and both applied, and cached, before either normal or rollup executions are run.
rollupQuery = structuredQuery.DeepCopy( );
// A poor proxy for determining that this is not a pivot chart.
bool isGroupedReport = !(reportSettings.ReportParameters != null &&
reportSettings.ReportParameters.GroupAggregateRules != null &&
reportSettings.ReportParameters.GroupAggregateRules.IgnoreRows);
if (isGroupedReport)
{
ReportRollupHelper.EnsureShowTotalsHasCount(rollupQuery, clientAggregate);
}
// Remove unused columns
bool supportQuickSearch = !string.IsNullOrWhiteSpace(reportSettings.QuickSearch);
optimisedQuery = ReportRollupHelper.RemoveUnusedColumns(rollupQuery, clientAggregate, supportQuickSearch);
rollupSettings = new QuerySettings
{
SecureQuery = nonRollupQuerySettings.SecureQuery,
SupportClientAggregate = true,
SupportPaging = false,
QuickSearchTerm = reportSettings.QuickSearch,
SupportQuickSearch = supportQuickSearch, // rollups query support quick search.
ClientAggregate = clientAggregate,
AdditionalOrderColumns = BuildAdditionOrderColumnDictionary(optimisedQuery, clientAggregate),
FullAggregateClustering = true,
Hint = "RptRollup-" + report.Id,
TargetResource = nonRollupQuerySettings.TargetResource,
IncludeResources = nonRollupQuerySettings.IncludeResources,
ExcludeResources = nonRollupQuerySettings.ExcludeResources
};
// Note : do not apply quick search filter to rollups (for scalability reasons)
PreparedQuery preparedQuery = new PreparedQuery
{
ClientAggregate = clientAggregate,
StructuredQuery = optimisedQuery,
QuerySettings = rollupSettings
};
return(preparedQuery);
}
public IActionResult UpdateClient(int id, [FromBody] ClientAggregate clientDto)
{
_userService.Update(id, clientDto);
return(Ok($"Client {clientDto.FirstName} {clientDto.LastName} with ID: {id} were successfully updated"));
}
/// <summary>
/// Remove any columns from a report that are not required to achieve a rollup result.
/// </summary>
/// <remarks>
/// If columns get removed here then the query optimiser will later remove various joins.
/// This can affect whether some rows are repeated.
/// Some aggregate types (e.g. max/min) are unaffected by this.
/// Some types (e.g. Count) are very affected by this, so we replace columns with simpler ones, rather than removing them completely.
/// Some types (e.g. Sum) in principle could be affected by this, but in practice are OK because they will reference the relationship branch that is relevant to them anyway.
/// </remarks>
/// <param name="query">The original query.</param>
/// <param name="clientAggregate">Aggregate settings that are used to determine what columns are used.</param>
/// <returns>A clone of the query, with unused columns removed.</returns>
public static StructuredQuery RemoveUnusedColumns(StructuredQuery query, ClientAggregate clientAggregate, bool supportQuickSearch = false)
{
StructuredQuery queryCopy = query.DeepCopy( );
// Determine columns that are used by the rollup
HashSet <Guid> referencedColumns = new HashSet <Guid>(clientAggregate.AggregatedColumns.Select(a => a.ReportColumnId)
.Concat(clientAggregate.GroupedColumns.Select(g => g.ReportColumnId)));
// Also include columns that are referenced by analyzer conditions
foreach (QueryCondition condition in query.Conditions)
{
ColumnReference colRefExpr = condition.Expression as ColumnReference;
if (colRefExpr == null)
{
continue;
}
referencedColumns.Add(colRefExpr.ColumnId);
}
// Ensure that the inner report returns at least something. (This will typically be the ID column).
if (referencedColumns.Count == 0 && query.SelectColumns.Count > 0)
{
referencedColumns.Add(query.SelectColumns [0].ColumnId);
}
// There are two types of optimisations. Either we can just pull out all unused columns, and let the structured query optimiser
// remove the subsequent relationship joins - which is OK for things like max & min in particular.
// But in some cases (e.g. for Count) we need to ensure we capture all relationships to get the true fanout.
// This is safer, but less efficient.
bool strictlyMaintainRowPresence = clientAggregate.AggregatedColumns.Any(ag => ag.AggregateMethod == AggregateMethod.Count);
if (!strictlyMaintainRowPresence)
{
// Remove all unused columns
queryCopy.SelectColumns.RemoveAll(column => !referencedColumns.Contains(column.ColumnId));
}
else
{
// Visit each column and determine if it can be removed, or converted to a simpler type
List <SelectColumn> columns = queryCopy.SelectColumns;
List <Guid> toRemove = new List <Guid>( );
for (int i = 0; i < columns.Count; i++)
{
SelectColumn column = columns [i];
if (referencedColumns.Contains(column.ColumnId))
{
continue;
}
// Replace field lookups with an equivalent ID column .. to maintain the relationship join, but remove the field join.
ResourceDataColumn fieldColumnExpr = column.Expression as ResourceDataColumn;
if (fieldColumnExpr != null)
{
// TODO: we could delete this column entire IF the entire relationship path to it is 'to one' relationships, without any advance properties to enforce rows.
// UPDATE: for quick serach purpose, the column expression should be ResourceDataColumn but skip in select clause.
// however for performance reasons, if without quick search, still change to IdExpression
if (supportQuickSearch)
{
column.IsHidden = true;
}
else
{
column.Expression = new IdExpression {
NodeId = fieldColumnExpr.NodeId
}
};
continue;
}
// Remove aggregate expressions if they don't have group-bys. (A group-by would cause the aggregate to return more than one row).
AggregateExpression aggExpr = column.Expression as AggregateExpression;
if (aggExpr != null)
{
AggregateEntity aggNode = StructuredQueryHelper.FindNode(queryCopy.RootEntity, aggExpr.NodeId) as AggregateEntity;
if (aggNode != null)
{
if (aggNode.GroupBy == null || aggNode.GroupBy.Count == 0)
{
toRemove.Add(column.ColumnId);
}
}
}
// Would be nice to remove calculated columns .. but probably too risky
}
queryCopy.SelectColumns.RemoveAll(column => toRemove.Contains(column.ColumnId));
}
// Remove any obsolete order-by instructions
queryCopy.OrderBy.RemoveAll(orderBy =>
{
ColumnReference colRefExpr = orderBy.Expression as ColumnReference;
if (colRefExpr == null)
{
return(false);
}
bool colStillPresent = queryCopy.SelectColumns.Any(column => column.ColumnId == colRefExpr.ColumnId);
return(!colStillPresent);
});
return(queryCopy);
}
}
/// <summary>
/// Builds the addition order column dictionary.
/// </summary>
/// <remarks>
/// Lifted from the report info service
/// </remarks>
/// <param name="query">The query.</param>
/// <param name="clientAggregate">The client aggregate.</param>
/// <returns>System.Collections.Generic.Dictionary{System.Guid,EDC.ReadiNow.Metadata.Query.Structured.SelectColumn}.</returns>
private Dictionary <Guid, SelectColumn> BuildAdditionOrderColumnDictionary(StructuredQuery query, ClientAggregate clientAggregate)
{
Dictionary <Guid, SelectColumn> additionalOrderColumns = new Dictionary <Guid, SelectColumn>();
foreach (ReportAggregateField reportAggregateField in clientAggregate.AggregatedColumns)
{
if (reportAggregateField.AggregateMethod == AggregateMethod.Max || reportAggregateField.AggregateMethod == AggregateMethod.Min)
{
SelectColumn currentSelectColumn = query.SelectColumns.FirstOrDefault(sc => sc.ColumnId == reportAggregateField.ReportColumnId);
if (currentSelectColumn != null && currentSelectColumn.Expression is ResourceExpression && ((ResourceExpression)currentSelectColumn.Expression).CastType is ChoiceRelationshipType)
{
//add choice field column's order column in additional order column dictionary.
if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId))
{
SelectColumn orderColumn = new SelectColumn
{
ColumnId = Guid.NewGuid(),
ColumnName = "EnumOrder",
DisplayName = "EnumOrder",
Expression =
new ResourceDataColumn
{
NodeId = ((EntityExpression)currentSelectColumn.Expression).NodeId,
FieldId = new Model.EntityRef("core:enumOrder")
}
};
query.SelectColumns.Add(orderColumn);
additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn);
}
}
else if (currentSelectColumn != null && currentSelectColumn.Expression is AggregateExpression &&
((AggregateExpression)currentSelectColumn.Expression).Expression is ResourceExpression &&
((ResourceExpression)(((AggregateExpression)currentSelectColumn.Expression).Expression)).CastType is ChoiceRelationshipType)
{
//add aggregated choice field column's order column in additional order column dictionary.
if (!additionalOrderColumns.ContainsKey(currentSelectColumn.ColumnId))
{
SelectColumn orderColumn = new SelectColumn
{
ColumnId = Guid.NewGuid(),
ColumnName = "EnumOrder",
DisplayName = "EnumOrder",
Expression =
new AggregateExpression
{
NodeId = ((AggregateExpression)currentSelectColumn.Expression).NodeId,
Expression = currentSelectColumn.Expression
}
};
additionalOrderColumns.Add(currentSelectColumn.ColumnId, orderColumn);
}
}
}
}
return(additionalOrderColumns);
}
public async Task Update(ClientAggregate item)
{
_context.Clients.Update(item);
await _context.SaveChangesAsync();
}
public static DbDataTable ConvertTo(QueryResult queryResult, List <ResultColumn> queryResultColumns, DataTable dataTable, DataTable aggregateDataTable, StructuredQuery sourceQuery, ClientAggregate clientAggregate, ReportObject reportObject)
{
var columns = new List <DbDataColumn>( );
var databaseTypeDictionary = new Dictionary <Guid, DatabaseType>( );
for (int i = 0; i < queryResultColumns.Count; i++)
{
queryResult.Columns[i] = queryResultColumns[i];
ResultColumn resultColumn = queryResultColumns[i];
DataColumn dataColumn = dataTable.Columns[i];
var dbColumn = new DbDataColumn( );
SelectColumn selectColumn = null;
Guid columnId = Guid.Empty;
// Check for a column id property
if (dataColumn.ExtendedProperties.ContainsKey("ColumnId"))
{
columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"];
selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId);
if (!databaseTypeDictionary.ContainsKey(columnId))
{
databaseTypeDictionary.Add(columnId, resultColumn.ColumnType);
}
}
// Have not found the column yet fallback to using the ordinal
if (selectColumn == null)
{
selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal];
}
//if current column in groupedcolumn, same as columntype
//otherwise, use string type
bool existAggregatedColumn = clientAggregate.AggregatedColumns.Any(gc => gc.ReportColumnId == columnId);
if (existAggregatedColumn)
{
dbColumn.Type = new StringType( );
}
else
{
// Determine column type
if (resultColumn.ColumnType != null)
{
if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern") && !string.IsNullOrEmpty(( string )dataColumn.ExtendedProperties["DisplayPattern"]))
{
dbColumn.Type = new AutoIncrementType( );
}
else
{
dbColumn.Type = resultColumn.ColumnType;
}
}
else
{
dbColumn.Type = GetColumnDatabaseType(sourceQuery, selectColumn);
if (dbColumn.Type is UnknownType)
{
dbColumn.Type = DatabaseType.ConvertFromType(dataColumn.DataType);
}
}
}
dbColumn.Id = selectColumn.ColumnId;
dbColumn.Name = string.IsNullOrEmpty(selectColumn.DisplayName) ? selectColumn.ColumnName : selectColumn.DisplayName;
dbColumn.ColumnName = selectColumn.ColumnName;
dbColumn.IsHidden = selectColumn.IsHidden;
columns.Add(dbColumn);
}
var rows = new List <DbDataRow>( );
// Convert the rows
int rowIndex = 0;
foreach (DataRow aggregateDataRow in aggregateDataTable.Rows)
{
bool isGrandTotalRow = rowIndex == (aggregateDataTable.Rows.Count - 1);
rowIndex++;
var row = new DbDataRow( );
// Convert each field
foreach (DataColumn dataColumn in dataTable.Columns)
{
Guid columnId = Guid.Empty;
DatabaseType databaseType = DatabaseType.StringType;
DatabaseType selectColumnType = DatabaseType.StringType;
// Check for a column id property
if (dataColumn.ExtendedProperties.ContainsKey("ColumnId"))
{
columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"];
SelectColumn selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId);
databaseType = databaseTypeDictionary.ContainsKey(columnId) ? databaseTypeDictionary[columnId] : DatabaseType.StringType;
var expression = selectColumn.Expression as EntityExpression;
if (expression != null)
{
selectColumnType = GetSelectColumnCastType(expression, databaseType);
}
}
bool existGroupedColumn = clientAggregate.GroupedColumns != null && clientAggregate.GroupedColumns.Any(gc => gc.ReportColumnId == columnId);
bool existAggregatedColumn = clientAggregate.AggregatedColumns.Any(gc => gc.ReportColumnId == columnId);
//not existing in GroupedColumn or AggregatedColumn
if (!existGroupedColumn && !existAggregatedColumn)
{
row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null);
}
else if (existGroupedColumn)
{
List <DataColumn> aggregatedDataColumns = GetAggregatedDataColumns(aggregateDataTable.Columns, columnId);
DataColumn aggregatedDataColumn = aggregatedDataColumns.Count > 0 ? aggregatedDataColumns[0] : null;
object obj = aggregateDataRow[aggregatedDataColumn];
if (!Convert.IsDBNull(obj))
{
string displayPattern = null;
if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern"))
{
displayPattern = ( string )dataColumn.ExtendedProperties["DisplayPattern"];
}
string additionStringformating = GetAddtionalFormatString(columnId, reportObject);
string cellValue = DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, aggregateDataRow[aggregatedDataColumn], displayPattern);
if (!string.IsNullOrEmpty(additionStringformating))
{
cellValue = string.Format(additionStringformating, aggregateDataRow[aggregatedDataColumn]);
}
row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(cellValue);
}
else
{
row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null);
}
}
else if (existAggregatedColumn)
{
List <DataColumn> aggregatedDataColumns = GetAggregatedDataColumns(aggregateDataTable.Columns, columnId);
var aggregatedValues = new List <string>( );
foreach (DataColumn aggregatedDataColumn in aggregatedDataColumns)
{
string columnName = aggregatedDataColumn.ColumnName;
columnName = columnName.Split(' ')[0];
int groupedColumnCount = clientAggregate.GroupedColumns != null ? clientAggregate.GroupedColumns.Count : 0;
int columnIndex = aggregateDataTable.Columns.IndexOf(aggregatedDataColumn);
int aggregateColumnIndex = columnIndex - groupedColumnCount;
ReportAggregateField reportAggregateField = clientAggregate.AggregatedColumns[aggregateColumnIndex];
AggregateMethod aggregateMethod = reportAggregateField.AggregateMethod;
if (clientAggregate.AggregatedColumns[aggregateColumnIndex] != null)
{
if (aggregateMethod == AggregateMethod.Count || aggregateMethod == AggregateMethod.CountUniqueItems ||
aggregateMethod == AggregateMethod.CountUniqueNotBlanks || aggregateMethod == AggregateMethod.CountWithValues)
{
databaseType = DatabaseType.Int32Type;
}
}
string displayPattern = DatabaseTypeHelper.GetDisplayFormatString(databaseType);
string additionStringformating = GetAddtionalFormatString(columnId, reportObject);
bool doFormat = !(aggregateMethod == AggregateMethod.Count || aggregateMethod == AggregateMethod.CountUniqueItems ||
aggregateMethod == AggregateMethod.CountUniqueNotBlanks || aggregateMethod == AggregateMethod.CountWithValues);
if (string.IsNullOrEmpty(additionStringformating))
{
if (doFormat)
{
if (selectColumnType is CurrencyType)
{
//displayPattern = "{0:c3}";
}
else if (selectColumnType is DecimalType)
{
displayPattern = "{0:N3}";
}
}
}
else
{
displayPattern = additionStringformating;
}
string aggregateValue = string.Empty;
if (doFormat)
{
if (selectColumnType is DateTimeType) // convert to local time
{
DateTime tempDate;
if (DateTime.TryParse(aggregateDataRow[aggregatedDataColumn].ToString( ), out tempDate))
{
tempDate = TimeZoneHelper.ConvertToLocalTime(tempDate, sourceQuery.TimeZoneName);
aggregateValue = string.Format(displayPattern, tempDate);
}
}
else
{
aggregateValue = string.Format(displayPattern, aggregateDataRow[aggregatedDataColumn]);
}
}
else
{
aggregateValue = aggregateDataRow[aggregatedDataColumn].ToString( );
}
if (selectColumnType is ChoiceRelationshipType && (aggregateMethod == AggregateMethod.Max || aggregateMethod == AggregateMethod.Min))
{
aggregateValue = UpdateChoiceFieldXmlValue(aggregateValue);
}
if (doFormat && selectColumnType is CurrencyType && !aggregateValue.StartsWith("$"))
{
aggregateValue = string.Format("{0:c2}", aggregateDataRow[aggregatedDataColumn]);
}
if (columnName == "Count")
{
columnName = "Count";
}
else if (columnName == "CountWithValues")
{
columnName = "Count with values";
}
else if (columnName == "CountUniqueItems")
{
columnName = "Count unique";
}
if (reportAggregateField != null && ((isGrandTotalRow && reportAggregateField.ShowGrandTotals) || (!isGrandTotalRow && reportAggregateField.ShowSubTotals)))
{
aggregatedValues.Add(string.Format("{0}: {1}", columnName, aggregateValue));
}
}
row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(string.Join("\r", aggregatedValues));
}
else
{
row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null);
}
}
rows.Add(row);
}
var dbTable = new DbDataTable
{
TableName = dataTable.TableName,
Columns = columns,
Rows = rows
};
if (dataTable.ExtendedProperties.ContainsKey("Id"))
{
dbTable.Id = ( Guid )dataTable.ExtendedProperties["Id"];
}
if (dataTable.ExtendedProperties.ContainsKey("Name"))
{
dbTable.Name = ( string )dataTable.ExtendedProperties["Name"];
}
return(dbTable);
}
public async Task Add(ClientAggregate item)
{
await _context.Clients.AddAsync(item);
await _context.SaveChangesAsync();
}
