public MatrixBuilder(DataClasses.ReportSettings settings) { _settings = settings; foreach (DataClasses.ReportColumn column in settings.MatrixRows) { RowFields.Add(column.Name.RemoveAccent()); } foreach (DataClasses.ReportColumn column in settings.Columns) { if (column.ReportDataTypeId != DataClasses.ReportDataType.Number) { ColumnFields.Add(column.Name); } else { SummarizedFields.Add(column.Name); } } if (SummarizedFields.Count == 0) { SummarizedFields.Add("Qtd"); } //SummarizedFields.Add(settings.MatrixRows[0].Name); }
private void BuildGroupBy(DataClasses.ReportSettings settings) { if (settings.MatrixRows.Count > 0 || settings.Columns.Count > 0) { query.Append(" GROUP BY "); DataClasses.ReportColumnsSchema columnSchema; foreach (DataClasses.ReportColumn column in settings.MatrixRows) { //if (column.ReportDataFunctionId == null) //{ columnSchema = GetColumnSchema(column.ReportTablesSchemaId, column.ReportColumnsSchemaId); if (!columnSchema.Source.Contains(")")) { query.Append(columnSchema.Source + ", "); } // } } foreach (DataClasses.ReportColumn column in settings.Columns) { //if (column.ReportDataFunctionId == null) //{ columnSchema = GetColumnSchema(column.ReportTablesSchemaId, column.ReportColumnsSchemaId); if (!columnSchema.Source.Contains(")")) { query.Append(columnSchema.Source + ", "); } //} } // Remove trailing ", " query.Remove(query.Length - 2, 2); } }
public DataTable ExecuteReport(DataClasses.ReportSettings settings) { string query = BuildQuery(settings); DataTable table = DataManager.ExecuteDataTable(query); return(table); }
private string BuildQuery(DataClasses.ReportSettings settings) { query = new StringBuilder(); query.Append("SELECT "); BuildColumns(settings); BuildFrom(settings); // // WHERE // if (settings.Filters.Count > 0) { BuildFilter(settings); } // // GROUP BY // BuildGroupBy(settings); // // ORDER BY // if (settings.SortedColumns.Count > 0) { BuildSort(settings); } return(query.ToString()); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Session["ReportSettings"] = null; settings = null; } }
public ReportDefinitionBuilder(DataClasses.ReportSettings settings) { _settings = settings; _report = new RdlSchema.Report(); _report.DataSources = CreateDataSources(); _report.DataSets = CreateDataSets(); _report.Body = CreateBody(); }
/// <summary> /// Get the Report Definition been Dynamic or Static /// </summary> /// <returns></returns> private void ProcessReportSettings(out DataTable table, out Report report) { // // Dynamic // //if (Page.PreviousPage is ReportGeneratorPage) //{ InfoControl.Web.Reporting.DataClasses.ReportSettings settings = Session["ReportSettings"] as InfoControl.Web.Reporting.DataClasses.ReportSettings; // // Create Report Generator // ReportDefinitionBuilder generator = new ReportDefinitionBuilder(settings); // // Create Table // if (settings.MatrixRows.Count > 0) { MatrixBuilder matrixBuilder = new MatrixBuilder(settings); matrixBuilder.RowsStyle = rowsStyle; matrixBuilder.HeaderStyle = headerStyle; generator.ReportItems.Add(matrixBuilder.ToMatrix()); } else { TableBuilder tableBuilder = new TableBuilder(settings); tableBuilder.RowsStyle = rowsStyle; tableBuilder.HeaderStyle = headerStyle; generator.ReportItems.Add(tableBuilder.ToTable()); } // // Create the dataTable // table = new ReportsManager(this).ExecuteReport(settings); report = generator.Report; //} //else //{ // table = new DataTable(); // string fileName = Server.MapPath("../Static/") + Request["r"]; // report = System.IO.File.ReadAllText(fileName).DeserializeFromXml<Report>(); //} }
private void BuildSort(DataClasses.ReportSettings settings) { query.Append(" ORDER BY "); foreach (DataClasses.ReportSort sort in settings.SortedColumns) { DataClasses.ReportColumnsSchema columnSchema = GetColumnSchema(sort.ReportTablesSchemaId, sort.ReportColumnsSchemaId); if (!columnSchema.Source.Contains(")")) { query.Append(columnSchema.Source + ((bool)sort.Ascending ? ", " : " DESC, ")); } } // Remove trailing ", " query.Remove(query.Length - 2, 2); }
private void BuildColumns(DataClasses.ReportSettings settings) { foreach (DataClasses.ReportColumn column in settings.MatrixRows) { AddColumnInQuery(column); } foreach (DataClasses.ReportColumn column in settings.Columns) { AddColumnInQuery(column); } query.Append("Count(*) as Qtd, "); // Remove trailing ", " query.Remove(query.Length - 2, 2); }
public MatrixBuilder(DataClasses.ReportSettings settings) { _settings = settings; foreach (DataClasses.ReportColumn column in settings.MatrixRows) { RowFields.Add(column.Name.RemoveAccent()); } foreach (DataClasses.ReportColumn column in settings.Columns) { if (column.ReportDataTypeId != DataClasses.ReportDataType.Number) ColumnFields.Add(column.Name); else SummarizedFields.Add(column.Name); } if (SummarizedFields.Count == 0) SummarizedFields.Add("Qtd"); //SummarizedFields.Add(settings.MatrixRows[0].Name); }
private void BuildFilter(DataClasses.ReportSettings settings) { DataClasses.ReportFilterType filterType; string filterSql = ""; DataClasses.ReportColumnsSchema columnSchema; query.Append(" WHERE "); foreach (DataClasses.ReportFilter filter in settings.Filters) { // // Get filter type and column // filterType = GetFilterType(filter.ReportFilterTypeId); columnSchema = GetColumnSchema(filter.ReportTablesSchemaId, filter.ReportColumnsSchemaId); if (filter.ReportColumnsSchemaId > 0 && columnSchema.Source.Contains(")")) { continue; } string parameterName = "@p" + Math.Abs(columnSchema.Source.GetHashCode()); filterSql = filterType.SqlText; if (filterType.Name.ToUpper() == "LIST") { // // LIST filter needs a clause IN, then in first time // create parameter and append clause 'where', after // concatenate the values // if (DataManager.Parameters.IndexOf(parameterName) == -1) { filterSql = filterSql.Replace("[=Column=]", columnSchema.ForeignKey) .Replace("[=Parameter=]", parameterName.Replace("@p", "")); query.Append(filterSql + " AND "); DataManager.Parameters.Add(parameterName, 0); } query.Replace(parameterName, filter.Value + ", " + parameterName.Replace("@p", "")); } else { // // String filter type // if ((DataType)filterType.ReportDataTypeId == DataType.String) { filter.Value = filterType.ReportFilterTypeId == 1 ? (filter.Value + "%") : filterType.ReportFilterTypeId == 2 ? ("%" + filter.Value + "%") : filterType.ReportFilterTypeId == 3 ? ("%" + filter.Value) : ""; } DataManager.Parameters.Add(parameterName, filter.Value); filterSql = filterSql.Replace("[=Column=]", filter.ReportColumnsSchemaId > 0 ? columnSchema.Source : filter.Name); filterSql = filterSql.Replace("[=Parameter=]", parameterName); query.Append(filterSql + " AND "); } } // Remove trailing " AND " query = query.Remove(query.Length - 5, 5); }
private void BuildFrom(DataClasses.ReportSettings settings) { DataClasses.ReportTablesSchema table = GetTableSchema(settings.Report.ReportTablesSchemaId.Value); query.Append(" FROM " + table.SqlText + " "); }
public TableBuilder(DataClasses.ReportSettings settings) { _settings = settings; }