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