/// <summary> /// Get the formatted cell value for the different data types. /// </summary> private static string GetFormattedCellValue(object cellValue, DatabaseType type, bool isRelationshipType = false) { if (cellValue == null || cellValue is DBNull) { return(""); } if (type is BoolType) { string result = ExportDataHelper.GetBooleanCellValue(( bool )cellValue); return(result); } if (type is ChoiceRelationshipType || type is InlineRelationshipType || isRelationshipType) { string result = DatabaseTypeHelper.GetEntityXmlName(( string )cellValue); return(result); } else { // TODO: UTC ??!! string formatString = DatabaseTypeHelper.GetDisplayFormatString(type); string result = string.Format(formatString, cellValue); return(result); } }
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); }