public static DataTableJoin WithDataTableRelationshipJoins(this DataTableJoin dataTableJoin, ICollection <DataTableRelationshipJoin> dataTableRelationshipJoins)
{
dataTableJoin.DataTableRelationshipJoins = dataTableRelationshipJoins;
return(dataTableJoin);
}
public static DataTableJoin WithDefaultJoinType(this DataTableJoin dataTableJoin, String defaultJoinType)
{
dataTableJoin.DefaultJoinType = defaultJoinType;
return(dataTableJoin);
}
public static DataTableJoin WithDataModelCode(this DataTableJoin dataTableJoin, Guid dataModelCode)
{
dataTableJoin.DataModelCode = dataModelCode;
return(dataTableJoin);
}
public static DataTableJoin WithToField(this DataTableJoin dataTableJoin, String toField)
{
dataTableJoin.ToField = toField;
return(dataTableJoin);
}
public static DataTableJoin WithIsOneToOne(this DataTableJoin dataTableJoin, Boolean isOneToOne)
{
dataTableJoin.IsOneToOne = isOneToOne;
return(dataTableJoin);
}
/// <summary>
/// Constructs a TabularReport.
/// </summary>
/// <param name="reportConstituents">A ReportDefinitionBusinessObject
/// encapsulating the input parameters.</param>
public TabularReport(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;
// When instantiating the TableGroups, we must record whether we are
// instantiating the first group or not AND the name of the group previous
// to the one we are currently dealing with.
CurrentDataModel = DataAccessUtilities.RepositoryLocator <IDataModelRepository>().Single(x => x.DatasourceName == _dataSource,
"DataTables",
"DataTables.DataItems",
"DataCategories",
"DataTableJoins",
"DataTableJoins.DataTableRelationshipJoins",
"DataTableJoins.DataTableRelationshipJoins.DataTableRelationship",
"DataTableJoins.DataTableRelationshipJoins.DataTableRelationship.DataTable",
"DataTableJoins.DataTableRelationshipJoins.DataTableRelationship.DataTable1");
bool IsFirstGroup = true;
string PreviousGroupName = "";
bool IsDataMapDisplayed = false;
IsDataMapDisplayed = reportConstituents.IsDataMapDisplayed;
_noOfFilters = reportConstituents.FilterList.Count;
//Table Join
_isDrillDown = reportConstituents.IsDrillDown;
_isPageOnFirstItem = reportConstituents.IsPageBreak;
if (reportConstituents.IsOuterJoin == false)
{
_joinMethod = " FULL OUTER JOIN ";
}
else
{
_joinMethod = " INNER JOIN ";
}
// Get the selectedDataItems
_selectedDataItems = reportConstituents.SelectedDataItems;
_fieldDataItems = reportConstituents.FieldDataItems;
// Instantiate the querywrapper for the main query
_mainQueryWrapper = new QueryWrapper(reportConstituents.SelectedDataItems,
reportConstituents.RowTotalDataItems, reportConstituents.FilterList,
reportConstituents.ParameterDataItems, false, reportConstituents.JoinList, CurrentDataModel);
// Instantiate the querywrapper for the parameter query
//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));
}
}
// Call the constructors of each of the report constituents
// TableHeader
_tableHeader = new TableHeader(reportConstituents.FieldDataItems, DataAccessUtilities.RepositoryLocator <IDataTableRepository>(), CurrentDataModel);
// TableFooter
#region Join Description Appended
//Add any joins in the list that are in the JoinList
StringBuilder JoinDescription = new StringBuilder();
foreach (ReportDataTableJoin currentReportJoin in reportConstituents.JoinList)
{
DataTableJoin currentJoin = CurrentDataModel.DataTableJoins.Single(x => x.Code == currentReportJoin.DataTableJoinCode);
string fromTablePlural = currentJoin.FromTable.Substring(currentJoin.FromTable.Length - 1).ToUpper() == "Y" ? currentJoin.FromTable.Substring(0, currentJoin.FromTable.Length - 1) + "ies" : currentJoin.FromTable + "s";
string toTablePlural = currentJoin.ToTable.Substring(currentJoin.ToTable.Length - 1).ToUpper() == "Y" ? currentJoin.ToTable.Substring(0, currentJoin.ToTable.Length - 1) + "ies" : currentJoin.ToTable + "s";
switch (currentReportJoin.JoinType)
{
case "INNER":
JoinDescription.Append("INNER: Select only " + fromTablePlural + " that have related " + toTablePlural + " and " +
"select only " + toTablePlural + " that have related " + fromTablePlural + Environment.NewLine);
break;
case "LEFT":
JoinDescription.Append("LEFT: Select all " + fromTablePlural + " and only related " +
toTablePlural + Environment.NewLine);
break;
case "RIGHT":
JoinDescription.Append("RIGHT: Select all " + toTablePlural + " and only related " +
fromTablePlural + Environment.NewLine);
break;
case "FULL":
JoinDescription.Append("FULL: Select all " + fromTablePlural + " and " +
toTablePlural + " with or without relations" + Environment.NewLine);
break;
}
}
#endregion
_tableFooter = new TableFooter(reportConstituents.ReportDescription + Environment.NewLine + JoinDescription.ToString(), reportConstituents.FieldDataItems.Count.ToString(), reportConstituents.FilterList, CurrentDataModel);
// TableGroup
// This list contains data items that are not grouped by, but are moved into
// being displayed within a group by the TableGroup constructor.
List <DataItem> DataItemsPromotedToGroup = new List <DataItem>();
// This list contains the extra groups added by the wrapper. They are ordered, so
// each group's index in the list (plus 1) is its grouping level.
List <DataItem> NewGroupList = _mainQueryWrapper.NewGroupList;
foreach (DataItem Item in reportConstituents.RowTotalDataItems)
{
if (reportConstituents.IsDrillDown)
{
if (IsFirstGroup)
{
_tableGroupList.Add(new TableGroup(reportConstituents.FieldDataItems,
reportConstituents.RowTotalDataItems, Item, "", DataItemsPromotedToGroup,
_mainQueryWrapper.NewGroupList, IsDataMapDisplayed, CurrentDataModel));
IsFirstGroup = false;
}
else
{
_tableGroupList.Add(new TableGroup(reportConstituents.FieldDataItems,
reportConstituents.RowTotalDataItems, Item, PreviousGroupName,
DataItemsPromotedToGroup, _mainQueryWrapper.NewGroupList, IsDataMapDisplayed, CurrentDataModel));
}
}
else
{
_tableGroupList.Add(new TableGroup(reportConstituents.FieldDataItems,
reportConstituents.RowTotalDataItems, Item, "", DataItemsPromotedToGroup,
_mainQueryWrapper.NewGroupList, IsDataMapDisplayed, CurrentDataModel));
}
// To ensure grouping name 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;
PreviousGroupName = TableName + Item.Name;
}
// TableDetails
if (reportConstituents.IsDrillDown)
{
_tableDetails = new TableDetails(reportConstituents.FieldDataItems,
reportConstituents.RowTotalDataItems, PreviousGroupName,
DataItemsPromotedToGroup, IsDataMapDisplayed, CurrentDataModel);
}
else
{
_tableDetails = new TableDetails(reportConstituents.FieldDataItems,
reportConstituents.RowTotalDataItems, "", DataItemsPromotedToGroup, reportConstituents.IsDataMapDisplayed, CurrentDataModel);
}
// IsSummaryReport
_isSummaryReport = reportConstituents.IsSummaryReport;
// Dataset for main query
_datasetList.Add(new Dataset(reportConstituents.SelectedDataItems, "Table",
_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)
{
// Add a dataset for each reference-type parameter
if (!Item.IsValueType || Item.DataType == "bit")
{
// 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;
if (Item.DataType != "bit")
{
_datasetList.Add(new Dataset(ParameterList, TableName + Item.Name + "Parameter",
(QueryWrapper)_parameterQueryWrapperArrayList[i], CurrentDataModel));
i++;
}
_parameterName = TableName + Item.Name;
_parameterCaption = Item.Caption;
_parameterName = "=\"Selected " + _parameterCaption + " :\"+Parameters!" + _parameterName + ".Label";
_parameterList.Add(_parameterName);
}
// ReportParameter
_reportParametersList.Add(new ReportParameter(Item, CurrentDataModel));
}
reportConstituents.ChartList = (from x in reportConstituents.ChartList orderby x.SortOrder select x).ToList();
foreach (ReportChartBusinessObject ChartItem in reportConstituents.ChartList)
{
Guid CategoryDataItemCode = Guid.Parse(ChartItem.CategoryDataItemCode);
DataItem CategoryDataItem = CurrentDataModel.DataTables.SelectMany(x => x.DataItems).Single(y => y.Code == CategoryDataItemCode);//DataAccessUtilities.RepositoryLocator<IDataItemRepository>().GetByCode(CategoryDataItemCode);
List <DataItem> ChartSeriesDataItems = new List <DataItem>();
ChartItem.ChartSeriesList = (from x in ChartItem.ChartSeriesList orderby x.SortOrder select x).ToList();
foreach (ReportChartSeriesBusinessObject currentSeries in ChartItem.ChartSeriesList)
{
Guid seriesDataItemCode = Guid.Parse(currentSeries.SeriesDataItemCode);
DataItem SeriesDataItem = CurrentDataModel.DataTables.SelectMany(x => x.DataItems).Single(y => y.Code == seriesDataItemCode);//DataAccessUtilities.RepositoryLocator<IDataItemRepository>().GetByCode(seriesDataItemCode);
ChartSeriesDataItems.Add(SeriesDataItem);
}
Chart newChart = new Chart(ChartItem.Type, CategoryDataItem, "TableDataSet", ChartSeriesDataItems[0].DataType, ChartSeriesDataItems, ChartItem.SortOrder, ChartItem.Description, CurrentDataModel);
_chartList.Add(newChart);
}
// 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>
/// SQL sorgusundaki UNION ALL ile aynı işleve sahiptir
/// </summary>
/// <param name="pDtLeft">JOIN işleminin solundaki tablo</param>
/// <param name="pDtRight">JOIN işleminin sağındaki tablo</param>
/// <returns></returns>
public static DataTable UnionAll(this DataTable pDtLeft, DataTable pDtRight)
{
return(DataTableJoin.UnionAll(pDtLeft, pDtRight));
}
public static DataTableJoin WithCode(this DataTableJoin dataTableJoin, Guid code)
{
dataTableJoin.Code = code;
return(dataTableJoin);
}
public static DataTableJoin WithFromTable(this DataTableJoin dataTableJoin, String fromTable)
{
dataTableJoin.FromTable = fromTable;
return(dataTableJoin);
}
/// <summary>
/// SQL sorgusundaki FULL OUTER JOIN ile aynı işleve sahiptir
/// </summary>
/// <param name="pDtLeft">JOIN işleminin solundaki tablo</param>
/// <param name="pDtRight">JOIN işleminin sağındaki tablo</param>
/// <param name="pDtLPrmKeyFieldName">Soldaki tabloya ait PrimaryKey kolonunun adı</param>
/// <param name="pDtRPrmKeyFieldName">Sağdaki tabloya ait PrimaryKey kolonunun adı</param>
/// <param name="pJoinKeyFieldName">JOIN işleminin gerçekleşeceği kolonunun adı</param>
/// <returns></returns>
public static DataTable FullOuterJoin(this DataTable pDtLeft, DataTable pDtRight, string pDtLPrmKeyFieldName, string pDtRPrmKeyFieldName, string pJoinKeyFieldName)
{
return(DataTableJoin.FullOuterJoin(pDtLeft, pDtRight, pDtLPrmKeyFieldName, pDtRPrmKeyFieldName, pJoinKeyFieldName));
}
public static DataTableJoin WithDataModel(this DataTableJoin dataTableJoin, DataModel dataModel)
{
dataTableJoin.DataModel = dataModel;
return(dataTableJoin);
}
public static void SetPrimaryKey(this DataTable pDtL, string pDtLPrmKeyFieldName)
{
DataTableJoin.SetPrimaryKey(ref pDtL, pDtLPrmKeyFieldName);
}
/// <summary>
/// SQL sorgusundaki DISTINCT ile aynı işleve sahiptir. string array olarak verilen kolonlara göre distinct işlemi yapar
/// </summary>
/// <param name="pDtSource">Üzerinde Distinct işlemine yapılacak kaynak DataTable</param>
/// <param name="pColumns">Distinct işlemi yapılacak kolon isimlerinin listesi</param>
/// <returns></returns>
public static DataTable SelectDistinct(this DataTable pDtSource, params string[] pColumns)
{
return(DataTableJoin.SelectDistinct(pDtSource, pColumns));
}
/// <summary>
/// SQL sorgusundaki DISTINCT ile aynı işleve sahiptir. Verilen DataTable objesindeki tüm kolonlara göre distinct işlemi yapar
/// </summary>
/// <param name="pDtSource">Üzerinde Distinct işlemine yapılacak kaynak DataTable</param>
/// <returns></returns>
public static DataTable SelectDistinct(this DataTable pDtSource)
{
return(DataTableJoin.SelectDistinct(pDtSource));
}
public static DataTableJoin WithReportDataTableJoins(this DataTableJoin dataTableJoin, ICollection <ReportDataTableJoin> reportDataTableJoins)
{
dataTableJoin.ReportDataTableJoins = reportDataTableJoins;
return(dataTableJoin);
}
public static DataTableJoin WithFromField(this DataTableJoin dataTableJoin, String fromField)
{
dataTableJoin.FromField = fromField;
return(dataTableJoin);
}
public static DataTableRelationshipJoin WithDataTableJoin(this DataTableRelationshipJoin dataTableRelationshipJoin, DataTableJoin dataTableJoin)
{
dataTableRelationshipJoin.DataTableJoin = dataTableJoin;
return(dataTableRelationshipJoin);
}
public static DataTableJoin WithToTable(this DataTableJoin dataTableJoin, String toTable)
{
dataTableJoin.ToTable = toTable;
return(dataTableJoin);
}
public static ReportDataTableJoin WithDataTableJoin(this ReportDataTableJoin reportDataTableJoin, DataTableJoin dataTableJoin)
{
reportDataTableJoin.DataTableJoin = dataTableJoin;
return(reportDataTableJoin);
}
/// <summary>
/// pDtRight parametresi ile gelen veri kaynağındaki veriler, pDtLeft parametresi ile gelen veri kaynağındaki verilere eklenir.
/// Bu ekleme sırasında, verilen pLoadOption parametresine göre;
/// pDtLeft veri kaynağında zaten var olan veriler aynen korunur ya da pDtRight veri kaynağındaki veri ile değiştirilir.
/// Zaten var olan verinin korunması isteniyor ise, pLoadOption parametresi "PreserveChanges" verilmelidir.
/// Güncellenmesi isteniyor ise pLoadOption parametresi "Upsert" veya "OverwriteChanges" olarak verilmelidir.
/// </summary>
/// <param name="pDtLeft">Verilerin ekleneceği ana veri kaynağı</param>
/// <param name="pDtRight">Verilerin alınacağı ikinci veri kaynağı</param>
/// <param name="pLoadOption">Veri ekleme (yükleme) seçeneği</param>
/// <param name="pDtLeftPKColumnName">pDtLeft için tanımlanacak olan PrimaryKey kolon adı.
/// pDtLeft'de zaten var olan bir verinin pDtRight'deki veri ile güncellenmesi için pDtLeft için bir PrimaryKey tanımlanmalıdır.
/// PK değeri boş verilirse iki tablo olduğu gibi birleştirilir (UnionAll yapar). </param>
public static void Union(this DataTable pDtLeft, DataTable pDtRight, LoadOption pLoadOption, string pDtLeftPKColumnName, bool pAcceptChanges)
{
DataTableJoin.Union(pDtLeft, pDtRight, pLoadOption, pDtLeftPKColumnName, pAcceptChanges);
}
