Example #1
0
        /// <summary>
        ///     Converts the data table from one format to another format.
        /// </summary>
        /// <param name="queryResult">The query result.</param>
        /// <param name="sourceQuery">The source query.</param>
        /// <param name="reportObject">The report object.</param>
        /// <returns>An in-memory copy of the converted data table.</returns>
        public static DbDataTable ConvertTo(QueryResult queryResult, StructuredQuery sourceQuery, ReportObject reportObject)
        {
            DbDataTable dbTable   = null;
            DataTable   dataTable = queryResult.DataTable;

            if (queryResult.DataTable != null)
            {
                var columns = new List <DbDataColumn>( );

                // Convert the columns
                var columnDictionary = new Dictionary <DataColumn, SelectColumn>( );

                for (int i = 0; i < queryResult.Columns.Count; i++)
                {
                    ResultColumn resultColumn = queryResult.Columns[i];
                    DataColumn   dataColumn   = queryResult.DataTable.Columns[i];

                    var dbColumn = new DbDataColumn( );

                    SelectColumn selectColumn = null;

                    // Check for a column id property
                    if (dataColumn.ExtendedProperties.ContainsKey("ColumnId"))
                    {
                        var columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"];
                        selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId);
                        if (!columnDictionary.ContainsKey(dataColumn))
                        {
                            columnDictionary.Add(dataColumn, selectColumn);
                        }
                    }

                    // Have not found the column yet fallback to using the ordinal
                    if (selectColumn == null)
                    {
                        selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal];
                    }

                    // 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

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    var row = new DbDataRow( );

                    // Convert each field
                    foreach (DataColumn dataColumn in dataTable.Columns)
                    {
                        object obj = dataRow[dataColumn];

                        if (!Convert.IsDBNull(obj))
                        {
                            row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, dataRow[dataColumn]));
                        }
                        else
                        {
                            row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null);
                        }
                    }

                    rows.Add(row);
                }

                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"];
                }
            }
            else if (queryResult.AggregateDataTable != null)
            {
                var columns = new List <DbDataColumn>( );

                // Convert the columns
                for (int i = 0; i < queryResult.AggregateColumns.Count; i++)
                {
                    ResultColumn resultColumn = queryResult.AggregateColumns[i];
                    DataColumn   dataColumn   = queryResult.AggregateDataTable.Columns[i];

                    var dbColumn = new DbDataColumn( );

                    SelectColumn selectColumn = null;

                    // Check for a column id property
                    if (dataColumn.ExtendedProperties.ContainsKey("ColumnId"))
                    {
                        var columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"];
                        selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId);
                    }

                    // Have not found the column yet fallback to using the ordinal
                    if (selectColumn == null)
                    {
                        selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal];
                    }

                    // 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 = resultColumn.AggregateColumn.AggregateMethod == AggregateMethod.List ? selectColumn.ColumnName : resultColumn.AggregateColumn.AggregateMethod.ToString( );
                    dbColumn.IsHidden   = selectColumn.IsHidden;

                    columns.Add(dbColumn);
                }

                var rows = new List <DbDataRow>( );

                // Convert the rows

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    var row = new DbDataRow( );

                    // Convert each field
                    foreach (DataColumn dataColumn in dataTable.Columns)
                    {
                        object obj = dataRow[dataColumn];

                        if (!Convert.IsDBNull(obj))
                        {
                            row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, dataRow[dataColumn]));
                        }
                        else
                        {
                            row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null);
                        }
                    }

                    rows.Add(row);
                }

                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);
        }
Example #2
0
        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);
        }