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();
}
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;
}
public TableBuilder(DataClasses.ReportSettings settings)
{
_settings = settings;
}
