Exemplo n.º 1
0
        /// <summary>
        ///    Checks whether specified columns do exists within given data.
        /// </summary>
        /// <param name="data">Data to search in.</param>
        /// <param name="pivotInfo">Pivot information with column specification.</param>
        private static void validate(DataView data, PivotInfo pivotInfo)
        {
            const string errorFormat = "The column {0} does not exists in given data.";

            foreach (var col in pivotInfo.RowFields.Union(pivotInfo.ColumnFields).Union(pivotInfo.DataFields))
            {
                if (!data.Table.Columns.Contains(col))
                {
                    throw new Exception(String.Format(errorFormat, col));
                }
            }
        }
Exemplo n.º 2
0
        /// <summary>
        ///    Creates a new data table with pivoted data.
        /// </summary>
        /// <remarks>It is like a group by over row and column fields.</remarks>
        /// <param name="data">Data view containing data to pivot.</param>
        /// <param name="pivotInfo">Information specifying the pivotation.</param>
        /// <returns>A data table with pivoted data.</returns>
        private DataTable pivotDataFlat(DataView data, PivotInfo pivotInfo)
        {
            var dt     = new DataTable();
            var fields = pivotInfo.RowFields.Union(pivotInfo.ColumnFields).ToArray();
            var dataFieldColumnName = pivotInfo.DataFieldColumnName;

            //Creates the columns
            foreach (var field in fields)
            {
                dt.Columns.Add(field);
            }

            if (pivotInfo.DataFields.Any())
            {
                dt.Columns.Add(dataFieldColumnName, typeof(string));
            }

            foreach (var func in pivotInfo.Aggregates)
            {
                dt.Columns.Add(func.Name, func.DataType);
            }

            var distinctFieldValues = getDistinctFieldsValues(data, fields);

            //Create the rows
            foreach (var values in distinctFieldValues)
            {
                var rowFilter = getFilterForFields(fields, values, String.Empty);
                if (pivotInfo.DataFields.Any())
                {
                    foreach (var dataField in pivotInfo.DataFields)
                    {
                        var row = dt.NewRow();
                        fillFields(fields, row, values);

                        row[dataFieldColumnName] = dataField;
                        foreach (var func in pivotInfo.Aggregates)
                        {
                            row[func.Name] = getData(data, rowFilter, dataField, func);
                        }

                        dt.Rows.Add(row);
                    }
                }
                else
                {
                    var row = dt.NewRow();
                    fillFields(fields, row, values);
                    dt.Rows.Add(row);
                }
            }
            return(dt);
        }
Exemplo n.º 3
0
        private static string getNameForColumnFields(PivotInfo pivotInfo, DataRow row)
        {
            var columnNames = new List <string>();

            foreach (var fieldName in pivotInfo.ColumnFields)
            {
                string value = row[fieldName].ToString();
                if (!string.IsNullOrEmpty(value))
                {
                    columnNames.Add(value);
                }
            }
            return(columnNames.ToString(pivotInfo.ColumnFieldsSeparator.ToString()));
        }
Exemplo n.º 4
0
        /// <summary>
        ///    Creates a new data table containing the pivoted data.
        /// </summary>
        /// <param name="data">Data view containing data to pivot.</param>
        /// <param name="pivotInfo">Information specifying the pivotation.</param>
        /// <returns>Pivoted data.</returns>
        public DataTable PivotData(DataView data, PivotInfo pivotInfo)
        {
            validate(data, pivotInfo);

            switch (pivotInfo.Mode)
            {
            case PivotInfo.PivotMode.CrossTable:
                return(pivotDataCross(data, pivotInfo));

            case PivotInfo.PivotMode.FlatTable:
                return(pivotDataFlat(data, pivotInfo));

            default:
                throw new InvalidEnumArgumentException();
            }
        }
Exemplo n.º 5
0
        /// <summary>
        ///    Creates a new data table containing the pivoted data.
        /// </summary>
        /// <remarks>It creates a group by analysis as cross table (row fields vs. column fields).</remarks>
        /// <param name="data">Data view containing data to pivot.</param>
        /// <param name="pivotInfo">Information specifying the pivotation.</param>
        /// <returns>Pivoted data.</returns>
        private DataTable pivotDataCross(DataView data, PivotInfo pivotInfo)
        {
            var dt = new DataTable();

            //Creates the row columns
            foreach (var field in pivotInfo.RowFields)
            {
                dt.Columns.Add(field);
            }

            var distinctColumnFieldsValues = getDistinctValuesFor(data, pivotInfo.ColumnFields);

            if (pivotInfo.ColumnFields.Any())
            {
                //Creates the result columns
                foreach (var columnValue in distinctColumnFieldsValues)
                {
                    var name = getNameForColumnFields(pivotInfo, columnValue);
                    foreach (var func in pivotInfo.Aggregates)
                    {
                        if (pivotInfo.DataFields.Count > 1)
                        {
                            foreach (var datafield in pivotInfo.DataFields)
                            {
                                dt.Columns.Add(getColumnNameFor(name, func, datafield, pivotInfo.ColumnFieldsSeparator), func.DataType);
                            }
                        }
                        else
                        {
                            dt.Columns.Add(getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator), func.DataType);
                        }
                    }
                    if (!pivotInfo.Aggregates.Any())
                    {
                        dt.Columns.Add(name);
                    }
                }
            }
            else
            {
                foreach (var func in pivotInfo.Aggregates)
                {
                    if (pivotInfo.DataFields.Count > 1)
                    {
                        foreach (var datafield in pivotInfo.DataFields)
                        {
                            dt.Columns.Add(getColumnNameFor(func, datafield), func.DataType);
                        }
                    }
                    else
                    {
                        dt.Columns.Add(func.Name, func.DataType);
                    }
                }
            }

            // Gets the list of row headers
            var distinctRowFieldsValues = getDistinctFieldsValues(data, pivotInfo.RowFields);

            //Create the rows
            foreach (var rowFieldsValues in distinctRowFieldsValues)
            {
                var row       = dt.NewRow();
                var rowFilter = getFilterForFields(pivotInfo.RowFields, rowFieldsValues, String.Empty);

                fillFields(pivotInfo.RowFields, row, rowFieldsValues);

                if (pivotInfo.ColumnFields.Any())
                {
                    // Build filter for column fields
                    foreach (var columnFieldsValues in distinctColumnFieldsValues)
                    {
                        var filter = getFilterForFields(pivotInfo.ColumnFields, columnFieldsValues, rowFilter);
                        var name   = getNameForColumnFields(pivotInfo, columnFieldsValues);

                        // Get aggregated value of data field
                        foreach (var func in pivotInfo.Aggregates)
                        {
                            if (pivotInfo.DataFields.Count > 1)
                            {
                                foreach (var datafield in pivotInfo.DataFields)
                                {
                                    row[getColumnNameFor(name, func, datafield, pivotInfo.ColumnFieldsSeparator)] = getData(data, filter, datafield, func);
                                }
                            }
                            else if (pivotInfo.DataFields.Count == 1)
                            {
                                row[getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator)] = getData(data, filter, pivotInfo.DataFields[0], func);
                            }
                            else
                            {
                                row[getColumnNameFor(name, func, pivotInfo.ColumnFieldsSeparator)] = DBNull.Value;
                            }
                        }
                        if (!pivotInfo.Aggregates.Any())
                        {
                            row[name] = DBNull.Value;
                        }
                    }
                }
                else
                {
                    foreach (var func in pivotInfo.Aggregates)
                    {
                        if (pivotInfo.DataFields.Count > 1)
                        {
                            foreach (var datafield in pivotInfo.DataFields)
                            {
                                row[getColumnNameFor(func, datafield)] = getData(data, rowFilter, datafield, func);
                            }
                        }
                        else if (pivotInfo.DataFields.Count == 1)
                        {
                            row[func.Name] = getData(data, rowFilter, pivotInfo.DataFields[0], func);
                        }
                        else
                        {
                            row[func.Name] = DBNull.Value;
                        }
                    }
                }
                dt.Rows.Add(row);
            }
            return(dt);
        }