/// <summary> /// Generates a table Footer for use in a TabularReport object. /// </summary> /// <param name="fieldList">List of selected data items.</param> public MatrixFooter(string reportDescription, string colSpan, List <ReportFilterBusinessObject> filterList, DataModel currentDataModel) { CurrentDataModel = currentDataModel; _reportDescription = "Report Description : " + reportDescription; _colSpan = colSpan; foreach (ReportFilterBusinessObject CurrentFilter in filterList) { //TODO: Code Review Issue 11/05/05: Do not use 'my' in variable names. //Choose a meaningful name. Pascal casing for variables. // Done 24/05/2005 Guid DataItemCode = Guid.Parse(CurrentFilter.DataItemCode); DataItem CurrentItem = CurrentDataModel.DataTables.SelectMany(x => x.DataItems).Single(y => y.Code == DataItemCode); //DataAccessUtilities.RepositoryLocator<IDataItemRepository>().GetByCode((CurrentFilter.DataItemCode)); DataTable CurrentTable = CurrentDataModel.DataTables.Single(x => x.Code == CurrentItem.Code); //DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(CurrentItem.DataTableCode); //25/08/05 LL - TIR0289 convert 'Current Month' to appropriate data format if (CurrentFilter.FilterValue == CURRENTMONTH) { CurrentFilter.FilterValue = DateUtility.GetCurrentYearMonth(); } ReportFilterBusinessObject ReportFilter = new ReportFilterBusinessObject(); ReportFilter.DataItemCaption = CurrentItem.Caption; ReportFilter.Operand = CurrentFilter.Operand; ReportFilter.FilterValue = CurrentFilter.FilterValue; _filterItemList.Add(ReportFilter); } }
/// <summary> /// Constructs a MatrixReport /// </summary> /// <param name="reportConstituents">A ReportDefinitionBusinessObject /// encapsulating the input parameters.</param> public MatrixReport(ReportDefinitionBusinessObject reportConstituents, string modelDataSource) { _dataSource = modelDataSource; //switch (model) //{ // case "INES": // _dataSource = "ESF2007"; // break; // case "MI Participant": // _dataSource = "ESFMIBUSINESSDATA"; // break; // case "Managing Authority": // _dataSource = "MANAGINGAUTHORITYDATA"; // break; //} //_dbConnectionString // = ConfigurationManager.ConnectionStrings["BusinessDataConnectionString"].ConnectionString; CurrentDataModel = DataAccessUtilities.RepositoryLocator <IDataModelRepository>().Single(x => x.DatasourceName == _dataSource, "DataTables", "DataTables.DataItems", "DataCategories", "DataTableJoins", "DataTableJoins.DataTableRelationshipJoins", "DataTableJoins.DataTableRelationshipJoins.DataTableRelashionship", "DataTableJoins.DataTableRelationshipJoins.DataTableRelashionship.DataTable", "DataTableJoins.DataTableRelationshipJoins.DataTableRelashionship.DataTable1"); _textboxStyle = new ReportingServicesStyle( ReportingServicesStyle.TextBoxStyle.MatrixReport); _cornerTextboxStyle = new ReportingServicesStyle( ReportingServicesStyle.TextBoxStyle.MatrixReportCorner); _columnTotalDataItemList = reportConstituents.ColumnTotalDataItems; _rowTotalDataItemList = reportConstituents.RowTotalDataItems; if (reportConstituents.IsOuterJoin == false) { _joinMethod = " FULL OUTER JOIN "; } else { _joinMethod = " INNER JOIN "; } // Temporary ArrayList used when instantiating the Dataset List <DataItem> TempGroupList = new List <DataItem>(reportConstituents.RowTotalDataItems); TempGroupList.AddRange(reportConstituents.ColumnTotalDataItems); // Initialise the querywrappers _mainQueryWrapper = new QueryWrapper(reportConstituents.SelectedDataItems, TempGroupList, reportConstituents.FilterList, reportConstituents.ParameterDataItems, false, reportConstituents.JoinList, CurrentDataModel); //30/06/05 LL - TIR0163 Parameter query needs to be build for each data item foreach (DataItem CurrentParameter in reportConstituents.ParameterDataItems) { if (!CurrentParameter.IsValueType) { List <DataItem> ParameterList = new List <DataItem>(); ParameterList.Add(CurrentParameter); _parameterQueryWrapperArrayList.Add(new QueryWrapper(ParameterList, null, null, null, true, null, CurrentDataModel)); } } // Iteration counter used in MatrixRow and MatrixStaticRow instantiations int MatrixRowCount = 0; /* Used when instantiating ColumnGroup and RowGroup lists: * The bools Denote if current item is first in list, so should be set to * false after first item dealt with. * The PrevItemName is the name of the item previous to the current item. */ bool IsFirstInColGroupList = true, IsFirstInRowGroupList = true; string PrevItemName = ""; // MatrixRow foreach (DataItem SelectedItem in reportConstituents.SelectedDataItems) { if (!reportConstituents.RowTotalDataItems.Contains(SelectedItem) && !reportConstituents.ColumnTotalDataItems.Contains(SelectedItem)) { _matrixRowList.Add( new MatrixRow("MatrixRowTextBox" + MatrixRowCount, SelectedItem, CurrentDataModel)); } MatrixRowCount++; } // MatrixDynamicColumnGrouping foreach (DataItem ColumnItem in reportConstituents.ColumnTotalDataItems) { if (IsFirstInColGroupList) { // To ensure drilldown groupname is unique in case of data items with same name, // concatenate the tablename and itemname DataTable SubtotalDataTable = CurrentDataModel.DataTables.Single(x => x.Code == ColumnItem.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(ColumnItem.DataTableCode); string SubtotalTableName = SubtotalDataTable.Name; string SubtotalName = SubtotalTableName + ColumnItem.Name + "Subtotal"; _dynamicColumnGroupingList.Add( new MatrixDynamicColumnGrouping(ColumnItem, SubtotalName, true, CurrentDataModel)); IsFirstInColGroupList = false; } else { _dynamicColumnGroupingList.Add( new MatrixDynamicColumnGrouping(ColumnItem, PrevItemName, false, CurrentDataModel)); } // To ensure field name is unique in case of data items with same name, // concatenate the tablename and itemname DataTable ThisDataTable = CurrentDataModel.DataTables.Single(x => x.Code == ColumnItem.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(ColumnItem.DataTableCode); string TableName = ThisDataTable.Name; PrevItemName = TableName + ColumnItem.Name; } // MatrixDynamicRowGrouping foreach (DataItem RowItem in reportConstituents.RowTotalDataItems) { if (IsFirstInRowGroupList) { // To ensure drilldown groupname is unique in case of data items with same name, // concatenate the tablename and itemname DataTable SubtotalDataTable = CurrentDataModel.DataTables.Single(x => x.Code == RowItem.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(RowItem.DataTableCode); string SubtotalTableName = SubtotalDataTable.Name; string SubtotalName = SubtotalTableName + RowItem.Name + "Subtotal"; _dynamicRowGroupingList.Add( new MatrixDynamicRowGrouping(RowItem, SubtotalName, true, CurrentDataModel)); IsFirstInRowGroupList = false; } else { _dynamicRowGroupingList.Add( new MatrixDynamicRowGrouping(RowItem, PrevItemName, false, CurrentDataModel)); } // To ensure field name is unique in case of data items with same name, // concatenate the tablename and itemname DataTable ThisDataTable = CurrentDataModel.DataTables.Single(x => x.Code == RowItem.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(RowItem.DataTableCode); string TableName = ThisDataTable.Name; PrevItemName = TableName + RowItem.Name; } // MatrixStaticRow MatrixRowCount = 0; foreach (DataItem SelectedItem in reportConstituents.SelectedDataItems) { if (!(reportConstituents.RowTotalDataItems.Contains(SelectedItem)) && !(reportConstituents.ColumnTotalDataItems.Contains(SelectedItem))) { _matrixStaticRowList.Add(new MatrixStaticRow( "MatrixStaticRowTextBox" + MatrixRowCount, SelectedItem, CurrentDataModel)); } MatrixRowCount++; } // Dataset _datasetList.Add(new Dataset(reportConstituents.SelectedDataItems, _matrixDataSetName, _mainQueryWrapper, CurrentDataModel)); //30/06/05 LL - TIR0163 Parameter query needs to be build for each data item int i = 0; foreach (DataItem Item in reportConstituents.ParameterDataItems) { if (!Item.IsValueType) { // We only require a query for the current data item, so put it into // an arraylist to pass to the Dataset constructor. List <DataItem> ParameterList = new List <DataItem>(); ParameterList.Add(Item); // To ensure this parameter's datasetname is unique in case of data items // with same name, concatenate the tablename and itemname DataTable ThisDataTable = CurrentDataModel.DataTables.Single(x => x.Code == Item.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(Item.DataTableCode); string TableName = ThisDataTable.Name; _datasetList.Add(new Dataset(ParameterList, TableName + Item.Name + "Parameter", (QueryWrapper)_parameterQueryWrapperArrayList[i], CurrentDataModel)); _parameterName = TableName + Item.Name; _parameterCaption = Item.Caption; _parameterName = "=\"Selected " + _parameterCaption + " :\"+Parameters!" + _parameterName + ".Value"; _parameterList.Add(_parameterName); i++; } } // ReportParameter foreach (DataItem ParameterItem in reportConstituents.ParameterDataItems) { _reportParameterList.Add(new ReportParameter(ParameterItem, CurrentDataModel)); } //Report Description DBS _reportDescription = "Report Description : " + reportConstituents.ReportDescription + " . Show only data with a common denominator = " + reportConstituents.IsOuterJoin.ToString(); foreach (ReportFilterBusinessObject CurrentFilter in reportConstituents.FilterList) { //TODO: Code Review Issue 11/05/05: Do not use 'my' in variable names. //Choose a meaningful name. Pascal casing for variables. // Done 24/05/2005 Guid DataItemCode = Guid.Parse(CurrentFilter.DataItemCode); DataItem CurrentItem = CurrentDataModel.DataTables.SelectMany(x => x.DataItems).Single(y => y.Code == DataItemCode); //DataAccessUtilities.RepositoryLocator<IDataItemRepository>().GetByCode((CurrentFilter.DataItemCode)); DataTable CurrentTable = CurrentDataModel.DataTables.Single(x => x.Code == CurrentItem.DataTableCode); //DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(CurrentItem.DataTableCode); //25/08/05 LL - TIR0289 convert 'Current Month' to appropriate data format if (CurrentFilter.FilterValue == CURRENTMONTH) { CurrentFilter.FilterValue = DateUtility.GetCurrentYearMonth(); } ReportFilterBusinessObject ReportFilter = new ReportFilterBusinessObject(); ReportFilter.DataItemCaption = CurrentItem.Caption; ReportFilter.Operand = CurrentFilter.Operand; ReportFilter.FilterValue = CurrentFilter.FilterValue; _filterItemList.Add(ReportFilter); } // Initialise the MemoryStream and the XmlWriter _memoryStream = new MemoryStream(); _xmlWriter = new XmlTextWriter( _memoryStream, Encoding.UTF8); // Either of the lines below may be used as alternative outputs for debugging //_stream, Encoding.UTF8); // Console.Out); }
/// <summary> /// This object builds an SQL query given a set of parameters. /// </summary> /// <param name="fieldList">List of fields used in the query</param> /// <param name="groupList">List of items for the query to group by</param> /// <param name="filterList">List of Filters used in the query</param> /// <param name="parameterList">List of parameters used in the query</param> /// <param name="isParameterQuery">Denotes whether this query is used as a /// dataset query for a ValidValues tag in RDL (i.e. a parameter query) or /// is a query for data selection.</param> /// <param name="joinMethod">Inner or Outer joins for tables</param> public QueryBuilder(List <DataItem> fieldList, List <DataItem> groupList, List <ReportFilterBusinessObject> filterList, List <DataItem> parameterList, bool isParameterQuery, List <ReportDataTableJoin> joinList, DataModel currentDataModel) { CurrentDataModel = currentDataModel; // Temporary list to store DataTables used in query List <DataTable> DataTableList = new List <DataTable>(); _isParameterQuery = isParameterQuery; //_joinMethod=joinMethod; // Check that each of parameters to this constructor are non-null; // Initialise them if not. if (fieldList == null) { fieldList = new List <DataItem>(); } if (groupList == null) { groupList = new List <DataItem>(); } if (filterList == null) { filterList = new List <ReportFilterBusinessObject>(); } if (parameterList == null) { parameterList = new List <DataItem>(); } if (joinList == null) { joinList = new List <ReportDataTableJoin>(); } // This loop adds all required fields to the fieldItem list and // adds all required tables to the dataTable foreach (DataItem Item in fieldList) { // Retrieve DataTable DataTable DataTable = CurrentDataModel.DataTables.Single(x => x.Code == Item.DataTableCode); //DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(Item.DataTableCode); _fieldItemList.Add(new FieldItem(Item.Name, DataTable.Name, Item.Caption)); //If the systems' link field is in the query then add the associated field to the query. if (Item.IsLink) { _fieldItemList.Add(new FieldItem(Item.LinkAssociatedField, DataTable.Name, Item.LinkAssociatedField)); } if (!DataTableList.Contains(DataTable)) { DataTableList.Add(DataTable); } } // If there is more than one table in the DataTableList, we must construct // a set of joins covering all related tables if (DataTableList.Count > 1) { _tableJoinList = ConstructTableJoins(DataTableList, joinList, CurrentDataModel); OrderTableJoins(_tableJoinList); } else { DataTable dt = (DataTable)DataTableList[0]; _tableJoinList.Add(new TableJoin(dt.SchemaName, null, dt.Name, null, null, null, -1, null, false)); } // Populate filterList foreach (ReportFilterBusinessObject CurrentFilter in filterList) { //TODO: Code Review Issue 11/05/05: Do not use 'my' in variable names. //Choose a meaningful name. Pascal casing for variables. // Done 24/05/2005 Guid CodeForRepository = Guid.Parse(CurrentFilter.DataItemCode); DataItem CurrentItem = CurrentDataModel.DataTables.SelectMany(x => x.DataItems).Single(y => y.Code == CodeForRepository); //DataAccessUtilities.RepositoryLocator<IDataItemRepository>().GetByCode(CodeForRepository); DataTable CurrentTable = CurrentDataModel.DataTables.Single(x => x.Code == CurrentItem.DataTableCode); //DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(CurrentItem.DataTableCode); //25/08/05 LL - TIR0289 convert 'Current Month' to appropriate data format if (CurrentFilter.FilterValue == CURRENTMONTH) { CurrentFilter.FilterValue = DateUtility.GetCurrentYearMonth(); } _filterItemList.Add(new FilterItem(CurrentItem.Name, CurrentTable.Name, CurrentFilter.FilterValue, CurrentFilter.Operand, CurrentItem.DataType)); } // Populate parameterList foreach (DataItem Item in parameterList) { DataTable CurrentTable = CurrentDataModel.DataTables.Single(x => x.Code == Item.DataTableCode);//DataAccessUtilities.RepositoryLocator<IDataTableRepository>().GetByCode(Item.DataTableCode); _reportParameterList.Add(new ParameterItem(Item.Name, CurrentTable.Name, Item.DataType.Contains("char"))); } // Populate groupList foreach (DataItem Item in groupList) { _groupList.Add(Item.Caption); } }