private void btnRest_Click(object sender, RoutedEventArgs e)
{
this.lblErrorMessage.Content = "";
for (int i = 0; i < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.Style = ComboboxOriginalStyle;
ctg.cmbCrossTabulationSort.Style = ComboboxOriginalStyle;
ctg.txtCrossTabulationTabGroupByAlias.BorderBrush = TextBoxOriginalBorderBrush;
ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex = -1;
ctg.cmbCrossTabulationSort.SelectedIndex = -1;
ctg.txtCrossTabulationTabGroupByAlias.Text = "";
}
this.cmbCrossTabulationTabSummaryFirstRowColumnsName.Style = ComboboxOriginalStyle;
this.cmbCrossTabulationTabSummaryFristRowSort.Style = ComboboxOriginalStyle;
this.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectedIndex = -1;
this.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex = -1;
for (int i = 0; i < this.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.Style = ComboboxOriginalStyle;
cts.cmbCrossTabulationTypeOfSummary.Style = ComboboxOriginalStyle;
cts.txtCrossTabulationTabSummaryAlias.BorderBrush = TextBoxOriginalBorderBrush;
cts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex = -1;
cts.cmbCrossTabulationTypeOfSummary.SelectedIndex = -1;
cts.txtCrossTabulationTabSummaryAlias.Text = "";
}
}
public void AddGroupByRow()
{
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.Name = "ctg1";
ctg.btnCrossTabGroupByDelete.Visibility = System.Windows.Visibility.Hidden;
this.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
}
public void UpdateAllControls(ResultViewControl rvc)
{
MainWindow mainWindow = (MainWindow)GetTopLevelControl(rvc);
rvc.SelectTabCntrl.lstToSelecteColFrom.IsEnabled = true;
mainWindow.ColListForSelectTab = mainWindow.GenerateListOfSelectTabCntrlColumns(rvc);
rvc.SelectTabCntrl.lstToSelecteColFrom.ItemsSource = mainWindow.ColListForSelectTab;
rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.IsEnabled = true;
rvc.TabulationTabCntrl.StackPanelTabuLationTabSummary.IsEnabled = true;
List <SQLBuilder.Clauses.Column> listOfTabulationTabColumns = mainWindow.GenerateListOfTabulationTabCntrlColumns(rvc);
if (((TabulationTabStackPanelGroupByControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[0]).cmbTabulationTabGroupByColumnsName.Items.Count == 0)
{
// loading groupby columns
for (int i = 0; i < rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[i];
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// loading summary columns
for (int i = 0; i < rvc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children[i];
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
else
{
List <SQLBuilder.Clauses.Column> list1 = (List <SQLBuilder.Clauses.Column>)((TabulationTabStackPanelGroupByControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[0]).cmbTabulationTabGroupByColumnsName.ItemsSource;
IEnumerable <SQLBuilder.Clauses.Column> difference = list1.Except(listOfTabulationTabColumns);
if (list1.SequenceEqual(listOfTabulationTabColumns) == false)
{
// Reloading groupby columns
for (int i = 0; i < rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[i];
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// Reloading summary columns
for (int i = 0; i < rvc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)rvc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children[i];
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
}
rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.IsEnabled = true;
rvc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.IsEnabled = true;
rvc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.IsEnabled = true;
rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.IsEnabled = true;
//if first drop down is null on Cross Tabulation means all dropdown item source is null
if (((CrossTabulationTabStackPanelGroupByControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[0]).cmbCrossTabulationTabGroupByColumnsName.Items.Count == 0)
{
// loading groupby columns
for (int i = 0; i < rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
//loading summary first row means (column Name and sort) row
rvc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = listOfTabulationTabColumns;
// loading summary columns
for (int i = 0; i < rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
else
{
List <SQLBuilder.Clauses.Column> list1 = (List <SQLBuilder.Clauses.Column>)((CrossTabulationTabStackPanelGroupByControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[0]).cmbCrossTabulationTabGroupByColumnsName.ItemsSource;
IEnumerable <SQLBuilder.Clauses.Column> difference = list1.Except(listOfTabulationTabColumns);
if (list1.SequenceEqual(listOfTabulationTabColumns) == false)
{
// Reloading groupby columns
for (int i = 0; i < rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = listOfTabulationTabColumns;
}
// Reloading summary first row means (column Name and sort) row
rvc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = listOfTabulationTabColumns;
// Reloading summary columns
for (int i = 0; i < rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)rvc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = listOfTabulationTabColumns;
}
}
}
List <string> Collist = (from x in (mainWindow.GenerateListOfTabulationTabCntrlColumns(rvc))
select x.Name).ToList <string>();
if (rvc.WhereTabCntrl.StackPanelWhereTab.Children.Count != 0)
{
for (int i = 0; i < rvc.WhereTabCntrl.StackPanelWhereTab.Children.Count; i++)
{
if (rvc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().Name == "WhereTabRegularConditionControl")
{
WhereTabRegularConditionControl regCondn = (WhereTabRegularConditionControl)(rvc.WhereTabCntrl.StackPanelWhereTab.Children[i]);
regCondn.cmbWhereTabLeftSideColumns.ItemsSource = Collist;
regCondn.cmbWhereTabRightSideColumns.ItemsSource = Collist;
}
else if (rvc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().Name == "WhereTabBetweenConditionControl")
{
WhereTabBetweenConditionControl regCondn = (WhereTabBetweenConditionControl)(rvc.WhereTabCntrl.StackPanelWhereTab.Children[i]);
regCondn.cmbWhereTabBetweenColumns.ItemsSource = Collist;
}
else if (rvc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().Name == "WhereTabInNotInConditionControl")
{
WhereTabInNotInConditionControl regCondn = (WhereTabInNotInConditionControl)(rvc.WhereTabCntrl.StackPanelWhereTab.Children[i]);
regCondn.cmbWhereTabInNotInColumns.ItemsSource = Collist;
}
else if (rvc.WhereTabCntrl.StackPanelWhereTab.Children[i].GetType().Name == "WhereTabNullNotNullConditionControl")
{
WhereTabNullNotNullConditionControl regCondn = (WhereTabNullNotNullConditionControl)(rvc.WhereTabCntrl.StackPanelWhereTab.Children[i]);
regCondn.cmbWhereTabNullNotNullColumns.ItemsSource = Collist;
}
}
}
}
/************************************************zahed*****************************************/
//loads right hand side custom query tabs
private void LoadAllCustomQueryTabs(SQLBuilder.SelectQueryBuilder queryBuilder, bool isGetResultByTreeView)
{
Mouse.OverrideCursor = Cursors.Wait;
//Clear all tabs
ClearAllCustomeQueryTabs();
//make all tab validated
if (listOfTable != null)
{
this.FromTabUC.cmbFromTable.ItemsSource = Common.ConvertTablesToStringList(listOfTable);
}
this.FromTabUC.cmbFromTable.SelectedIndex = Common.getIndex((List<string>)this.FromTabUC.cmbFromTable.ItemsSource, queryBuilder.SelectedTables[0].Name);
this.FromTabUC.txtFromAlias.Text = queryBuilder.SelectedTables[0].AliasName;
foreach (SQLBuilder.Clauses.JoinClause JoinClasue in queryBuilder.Joins)
{
// create join row control
FromTabStackPanelControl fc = new FromTabStackPanelControl();
if (this.listOfTable != null & this.listOfTable.Count > 0)
{
fc.cmbFromTabJoinTable.ItemsSource = Common.ConvertTablesToStringList(this.listOfTable);
fc.cmbFromTabFromColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[this.FromTabUC.cmbFromTable.SelectedIndex].columns);
}
fc.cmbFromTabJoinType.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabJoinType.ItemsSource, JoinClasue.JoinType.ToString());
fc.cmbFromTabJoinTable.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabJoinTable.ItemsSource, JoinClasue.ToTable.Name);
//fc.cmbFromTabJoinTable.Text = JoinClasue.ToTable.Name;
fc.txtJoinTableAlias.Text = JoinClasue.ToTable.AliasName;
fc.cmbFromTabFromColumns.Text = JoinClasue.FromColumn.ToString();
//fc.cmbFromTabFromColumns.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabFromColumns.ItemsSource, JoinClasue.FromColumn.ToString());
fc.cmbFromTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabQueryOpretor.ItemsSource, JoinClasue.ComparisonOperator.ToString());
//fc.cmbFromTabJoinColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[fc.cmbFromTabJoinTable.SelectedIndex].columns);
fc.cmbFromTabJoinColumns.Text = JoinClasue.ToColumn.ToString();
//fc.cmbFromTabJoinColumns.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabJoinColumns.ItemsSource, JoinClasue.ToColumn.ToString());
this.FromTabUC.StackPanelFromTab.Children.Add(fc);
}
//load select tab
List<SQLBuilder.Clauses.Column> ColListForSelectTab = GenerateListOfSelectTabColumns();
// seting up select Tab's observabla collection _FromSelectedColToCollection
//ColListForSelectTab.ForEach(x => this.SelectTabUC._FromSelectedColToCollection.Add(x));
this.SelectTabUC.lstToSelecteColFrom.ItemsSource = ColListForSelectTab;
//clear old selected columns
this.SelectTabUC._SelectedColCollection.Clear();
//load selected Column List and set _SelectedColCollection observable collection from queryBuilder.SelectedColumns
//Regex regex = new Regex("(?<X>[.]+).(?<Z>)");// this to check if queryBuilder is not generated from custome query, so if user selects table from Tree view we don't need to show selected columns
if (queryBuilder.SelectedColumns.Count != 0)
{
if (!isGetResultByTreeView)
{
//first we copy queryBuilder.SelectedColumns from generic list to observable collection
queryBuilder.SelectedColumns.ForEach(x => this.SelectTabUC._SelectedColCollection.Add(x));
//this.SelectTabUC.lstSelectedCol.ItemsSource = this.SelectTabUC._SelectedColCollection;//queryBuilder.SelectedColumns;
}
}
//load select tab or tabulation tab
if (queryBuilder.GroupByColumns.Count > 0)
{
//we have Tabluation but we need to find out what type of tabulation we have
//***
if (queryBuilder.CrossTabClause._col == null)
{
//we have regular tabulation
//clear tabulation
this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Clear();
this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Clear();
//loading groupby
List<string> GroupByColumnNameList = new List<string>();
foreach (SQLBuilder.Clauses.Column groupByColumn in queryBuilder.GroupByColumns)
{
if (queryBuilder.OrderByStatement.Count == 0)
{
//we don't have oreder by
TabulationTabStackPanelGroupByControl tg = new TabulationTabStackPanelGroupByControl();
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
//modified on 11-18-11
tg.cmbTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
tg.txtTabulationTabGroupByColFormat.Text = groupByColumn.Format;
//tg.cmbTabulationTabGroupByColumnsName.SelectedIndex = 1;// groupByColumn;
// tg.cmbTabulationTabGroupByColumnsName.SelectedItem= groupByColumn;
tg.txtTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Add(tg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
else
{
//we have orderby
foreach (SQLBuilder.Clauses.OrderByClause orderByClause in ((List<SQLBuilder.Clauses.OrderByClause>)queryBuilder.OrderByStatement))
{
if (groupByColumn.Name == orderByClause.FieldName)
{
TabulationTabStackPanelGroupByControl tg = new TabulationTabStackPanelGroupByControl();
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
tg.cmbTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
tg.txtTabulationTabGroupByColFormat.Text = groupByColumn.Format;
tg.cmbTabulationSort.Text = Common.GetStringValueForEnum("Sorting", orderByClause.SortOrder);
tg.txtTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Add(tg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
}
}
}
//we display total 3 rows in Group by secction, we are going to add missing row
int numberOfMissingGroupByRow = 3 - this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count;
if (this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingGroupByRow; i++)
{
this.TabulationTabUC.AddGroupByRow();
//loading group by drop down columns to just added group by row
((TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count - 1]).cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns(); ;
}
}
//load summary
foreach (SQLBuilder.Clauses.Column summaryColumn in queryBuilder.SelectedColumns)
{
//foreach (string colName in GroupByColumnNameList)
//{
// if (summaryColumn.Name != colName)
// {
if (summaryColumn.Name.Contains("(") & summaryColumn.Name.Contains(")"))
{
TabulationTabStackPanelSummaryControl ts = new TabulationTabStackPanelSummaryControl();
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
// first remove ')' from column name then spilt the string by'('
string[] summaryArr = summaryColumn.Name.Remove(summaryColumn.Name.Length - 1, 1).Split('(');
ts.cmbTabulationTabSummaryColumnsName.Text = summaryArr[1];
ts.cmbTabulationTypeOfSummary.SelectedIndex = Common.getIndex((List<string>)ts.cmbTabulationTypeOfSummary.ItemsSource, summaryArr[0]);
ts.cmbTabulationTabUserSelectSummaryColFormat.SelectedIndex = Common.getIndex((List<string>)ts.cmbTabulationTabUserSelectSummaryColFormat.ItemsSource, summaryColumn.Format);
ts.txtTabulationTabSummaryColFormat.Text = summaryColumn.Format;
ts.txtTabulationTabSummaryAlias.Text = summaryColumn.AliasName;
this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Add(ts);
}
// }
//}
}
//we display total 6 rows in summary secction, we are going to add missing row
int numberOfMissingSummaryRow = 6 - this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count;
if (this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingSummaryRow; i++)
{
this.TabulationTabUC.AddSummaryRow();
//loading group by drop down columns to just added group by row
((TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count - 1]).cmbTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns(); ;
}
}
}
else
{
//we have cross tabulation
//clear cross tabulation tab
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Clear();
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Clear();
//loading groupby
List<string> GroupByColumnNameList = new List<string>();
foreach (SQLBuilder.Clauses.Column groupByColumn in queryBuilder.GroupByColumns)
{
if (queryBuilder.OrderByStatement.Count == 0)
{
//we don't have oreder by
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
ctg.cmbCrossTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
ctg.txtCrossTabulationTabGroupByColFormat.Text = groupByColumn.Format;
ctg.txtCrossTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
else
{
//we have orderby
foreach (SQLBuilder.Clauses.OrderByClause orderByClause in ((List<SQLBuilder.Clauses.OrderByClause>)queryBuilder.OrderByStatement))
{
if (groupByColumn.Name == orderByClause.FieldName)
{
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
ctg.cmbCrossTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
ctg.txtCrossTabulationTabGroupByColFormat.Text = groupByColumn.Format;
ctg.cmbCrossTabulationSort.Text = Common.GetStringValueForEnum("Sorting", orderByClause.SortOrder);
ctg.txtCrossTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
}
}
}
//we display total 3 rows in Group by secction, we are going to add missing row
int numberOfMissingGroupByRow = 3 - this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count;
if (this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingGroupByRow; i++)
{
this.CrossTabulationTabUC.AddGroupByRow();
//loading group by drop down columns to just added group by row
((CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count - 1]).cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns(); ;
}
}
//load summary
foreach (SQLBuilder.Clauses.Column summaryColumn in queryBuilder.SelectedColumns)
{
//foreach (string colName in GroupByColumnNameList)
//{
// if (summaryColumn.Name != colName)
// {
if (summaryColumn.Name.Contains("(") & summaryColumn.Name.Contains(")"))
{
CrossTabulationTabStackPanelSummaryControl cts = new CrossTabulationTabStackPanelSummaryControl();
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
// first remove ')' from column name then spilt the string by'('
string[] summaryArr = summaryColumn.Name.Remove(summaryColumn.Name.Length - 1, 1).Split('(');
cts.cmbCrossTabulationTabSummaryColumnsName.Text = summaryArr[1];
cts.txtCrossTabulationTabSummaryColFormat.Text = summaryColumn.Format;
cts.cmbCrossTabulationTypeOfSummary.SelectedIndex = Common.getIndex((List<string>)cts.cmbCrossTabulationTypeOfSummary.ItemsSource, summaryArr[0]);
cts.cmbCrossTabulationTabUserSelectSummaryColFormat.SelectedIndex = Common.getIndex((List<string>)cts.cmbCrossTabulationTabUserSelectSummaryColFormat.ItemsSource, summaryColumn.Format);
cts.txtCrossTabulationTabSummaryColFormat.Text = summaryColumn.Format;
cts.txtCrossTabulationTabSummaryAlias.Text = summaryColumn.AliasName;
this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Add(cts);
}
// }
//}
}
//we display total 6 rows in summary secction, we are going to add missing row
int numberOfMissingSummaryRow = 6 - this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count;
if (this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingSummaryRow; i++)
{
this.CrossTabulationTabUC.AddSummaryRow();
//loading group by drop down columns to just added group by row
((CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count - 1]).cmbCrossTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns(); ;
}
}
//load summary frist row
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text = queryBuilder.CrossTabClause.Col.Name;
if (queryBuilder.CrossTabClause.SortSet)
{
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.Text = Common.GetStringValueForEnum("Sorting", queryBuilder.CrossTabClause.SortOrder);
}
}
//********
}
else
{
//we have select
//clear old regular tabulation
//clear group by rows
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children.Count; i++)
{
TabulationTabStackPanelGroupByControl tg = (TabulationTabStackPanelGroupByControl)this.TabulationTabUC.StackPanelTabuLationTabGroupBy.Children[i];
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
tg.cmbTabulationTabGroupByColumnsName.SelectedIndex = -1;
tg.cmbTabulationSort.SelectedIndex = -1;
tg.txtTabulationTabGroupByAlias.Text = System.String.Empty;
}
//clear summary rows
for (int i = 0; i < this.TabulationTabUC.StackPanelTabuLationTabSummary.Children.Count; i++)
{
TabulationTabStackPanelSummaryControl ts = (TabulationTabStackPanelSummaryControl)this.TabulationTabUC.StackPanelTabuLationTabSummary.Children[i];
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
ts.cmbTabulationTabSummaryColumnsName.SelectedIndex = -1;
ts.cmbTabulationTypeOfSummary.SelectedIndex = -1;
ts.cmbTabulationTabUserSelectSummaryColFormat.SelectedIndex = -1;
ts.txtTabulationTabSummaryColFormat.Text = System.String.Empty;
ts.txtTabulationTabSummaryAlias.Text = System.String.Empty;
}
//clear old Cross tabulation
//clear group by rows
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl ctg = (CrossTabulationTabStackPanelGroupByControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabGroupBy.Children[i];
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
ctg.cmbCrossTabulationTabGroupByColumnsName.SelectedIndex = -1;
ctg.cmbCrossTabulationSort.SelectedIndex = -1;
ctg.txtCrossTabulationTabGroupByAlias.Text = System.String.Empty;
}
//clear summary rows
for (int i = 0; i < this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl cts = (CrossTabulationTabStackPanelSummaryControl)this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children[i];
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
cts.cmbCrossTabulationTabSummaryColumnsName.SelectedIndex = -1;
cts.cmbCrossTabulationTypeOfSummary.SelectedIndex = -1;
cts.cmbCrossTabulationTabUserSelectSummaryColFormat.SelectedIndex = -1;
cts.txtCrossTabulationTabSummaryColFormat.Text = System.String.Empty;
cts.txtCrossTabulationTabSummaryAlias.Text = System.String.Empty;
}
// clear summary frist row
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = GenerateListOfTabulationTabColumns();
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectedIndex = -1;
this.CrossTabulationTabUC.cmbCrossTabulationTabSummaryFristRowSort.SelectedIndex = -1;
//load select tab
List<SQLBuilder.Clauses.Column> colListForSelectTab = GenerateListOfSelectTabColumns();
// seting up select Tab's observabla collection _FromSelectedColToCollection
//ColListForSelectTab.ForEach(x => this.SelectTabUC._FromSelectedColToCollection.Add(x));
this.SelectTabUC.lstToSelecteColFrom.ItemsSource = colListForSelectTab;
//clear old selected columns
this.SelectTabUC._SelectedColCollection.Clear();
//load selected Column List and set _SelectedColCollection observable collection from queryBuilder.SelectedColumns
//Regex regex = new Regex("(?<X>[.]+).(?<Z>)");// this to check if queryBuilder is not generated from custome query, so if user selects table from Tree view we don't need to show selected columns
if (queryBuilder.SelectedColumns.Count != 0)
{
if (!isGetResultByTreeView)
{
//first we copy queryBuilder.SelectedColumns from generic list to observable collection
queryBuilder.SelectedColumns.ForEach(x => this.SelectTabUC._SelectedColCollection.Add(x));
//this.SelectTabUC.lstSelectedCol.ItemsSource = this.SelectTabUC._SelectedColCollection;//queryBuilder.SelectedColumns;
}
}
}
//load where tab
int RowNumber = 0;
foreach (SQLBuilder.Clauses.WhereClause whereClause in queryBuilder.Where)
{
RowNumber = RowNumber + 1;
// create where Tab row control
string controlType = whereClause.GetType().FullName;
//List<string> Collist = (from x in (GenerateListOfTabulationTabColumns())
// select x.Name).ToList<string>();
List<string> Collist = (from x in (GenerateListOfSelectTabColumns())
select x.Name).ToList<string>();
if (controlType == "SQLBuilder.Clauses.BetweenWhereClause")
{
//between where clause
SQLBuilder.Clauses.BetweenWhereClause Betweenclause = (SQLBuilder.Clauses.BetweenWhereClause)whereClause;
WhereTabBetweenConditionControl wsb = new WhereTabBetweenConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", Betweenclause.LogicalOperator) == "None")
{
Betweenclause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wsb.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wsb.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", Betweenclause.LogicalOperator));
if (RowNumber == 1)
{
wsb.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wsb.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wsb.cmbWhereTabBetweenColumns.ItemsSource = Collist;// GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns( (List<SelectTabColumn>) this.SelectTabUC.dgSelectTab.ItemsSource);
wsb.cmbWhereTabBetweenColumns.Text = Betweenclause.FieldName;
//wsb.cmbWhereTabBetweenColumns.SelectedIndex = Common.getIndex((List<string>)wsb.cmbWhereTabBetweenColumns.ItemsSource, Betweenclause.FieldName);
wsb.txtBetweenLeftValue.Text = Betweenclause.FromValue;
wsb.txtBetweenRightValue.Text = Betweenclause.ToValue;
wsb.cmbWhereTabQueryLevel.SelectedIndex = (Betweenclause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(wsb);
}
else
{
SQLBuilder.Clauses.GeneralWhereClause generalClause = (SQLBuilder.Clauses.GeneralWhereClause)whereClause;
if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.In)
{
//in where clause
WhereTabInNotInConditionControl wInNotIn = new WhereTabInNotInConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wInNotIn.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wInNotIn.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wInNotIn.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wInNotIn.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wInNotIn.cmbWhereTabInNotInColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns( (List<SelectTabColumn>) this.SelectTabUC.dgSelectTab.ItemsSource);
wInNotIn.cmbWhereTabInNotInColumns.Text = generalClause.FieldName;
wInNotIn.lblInNotIn.Content = " in"; ;
wInNotIn.txtInNotInValue.Text = generalClause.Value.ToString();
wInNotIn.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(wInNotIn);
}
else if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.NotIn)
{
//not in where clause
WhereTabInNotInConditionControl wInNotIn2 = new WhereTabInNotInConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wInNotIn2.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wInNotIn2.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wInNotIn2.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wInNotIn2.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wInNotIn2.cmbWhereTabInNotInColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wInNotIn2.cmbWhereTabInNotInColumns.Text = generalClause.FieldName;
wInNotIn2.lblInNotIn.Content = "not in";
wInNotIn2.txtInNotInValue.Text = generalClause.Value.ToString();
wInNotIn2.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(wInNotIn2);
}
else if (generalClause.Value == null)
{
if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.Equals)
{
// is null where clause
WhereTabNullNotNullConditionControl wNullNotNull = new WhereTabNullNotNullConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wNullNotNull.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wNullNotNull.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wNullNotNull.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wNullNotNull.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wNullNotNull.cmbWhereTabNullNotNullColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wNullNotNull.cmbWhereTabNullNotNullColumns.Text = generalClause.FieldName;
wNullNotNull.lblNullNotNull.Content = "null";
wNullNotNull.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(wNullNotNull);
}
else if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.NotEquals)
{
// is not null where clause
WhereTabNullNotNullConditionControl wNullNotNull2 = new WhereTabNullNotNullConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wNullNotNull2.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wNullNotNull2.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wNullNotNull2.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wNullNotNull2.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
//IEnumerable<Column> columnsWithSameAlias = _SelectedColCollection.Where(x => x.AliasName == compuatedCol.AliasName)
wNullNotNull2.cmbWhereTabNullNotNullColumns.ItemsSource = Collist; //GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wNullNotNull2.cmbWhereTabNullNotNullColumns.Text = generalClause.FieldName;
wNullNotNull2.lblNullNotNull.Content = "not null";
wNullNotNull2.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(wNullNotNull2);
}
}
else
{
//Regular where clause
WhereTabRegularConditionControl ws = new WhereTabRegularConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
ws.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
ws.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
ws.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
ws.cmbWhereTabLeftSideColumns.ItemsSource = Collist;// GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
ws.cmbWhereTabLeftSideColumns.Text = generalClause.FieldName;
///ws.cmbWhereTabLeftSideColumns.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabLeftSideColumns.ItemsSource, generalClause.FieldName);
ws.cmbWhereTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabQueryOpretor.ItemsSource, Common.GetStringValueForEnum("Comparison", generalClause.ComparisonOperator));
ws.cmbWhereTabRightSideColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
ws.cmbWhereTabRightSideColumns.Text = generalClause.Value.ToString();
//ws.cmbWhereTabRightSideColumns.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabRightSideColumns.ItemsSource, generalClause.Value.ToString());
ws.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
this.WhereTabUC.StackPanelWhereTab.Children.Add(ws);
}
}
}
this.WhereTabUC.cmbWherTabCondition.SelectedIndex = 0;
//make all tab validated
this.FromTabUC.isValidated = true;
this.FromTabUC.lblErrorMessage.Content = "";
this.WhereTabUC.isValidated = true;
this.WhereTabUC.lblErrorMessage.Content = "";
this.SelectTabUC.isValidated = true;
this.SelectTabUC.lblErrorMessage.Content = "";
this.SelectTabUC.lstToSelecteColFrom.IsEnabled = true;
this.TabulationTabUC.lblErrorMessage.Content = "";
this.TabulationTabUC.isValidated = true;
this.CrossTabulationTabUC.lblErrorMessage.Content = "";
this.CrossTabulationTabUC.isValidated = true;
Mouse.OverrideCursor = null;
//Clear Action Tab
this.txtQuery.Text = System.String.Empty;
}
private void LoadAllCustomQueryCntrls(SQLBuilder.SelectQueryBuilder queryBuilder, bool isGetResultByTreeView, ResultViewControl rc)
{
Mouse.OverrideCursor = Cursors.Wait;
string db = ConfigurationManager.AppSettings["DefaultDatabase"].ToString();
List<Schema> schemas1 = MySQLData.DataAccess.ADODataBridge.getSchemaTree(connectionString, db, ConfigurationManager.AppSettings["DerivedTablesPath"]);//DataAccess.GetDatabases();
listOfTable = new List<MySQLData.Table>();
foreach (Schema schema in schemas1)
{
listOfTable.AddRange(schema.tables);
}
if (listOfTable != null)
{
rc.FromTabCntrl.cmbFromTable.ItemsSource = Common.ConvertTablesToStringList(listOfTable);
}
rc.FromTabCntrl.cmbFromTable.SelectedIndex = Common.getIndex((List<string>)rc.FromTabCntrl.cmbFromTable.ItemsSource, queryBuilder.SelectedTables[0].Name);
rc.FromTabCntrl.txtFromAlias.Text = queryBuilder.SelectedTables[0].AliasName;
int loopCount = 0;
foreach (SQLBuilder.Clauses.JoinClause JoinClasue in queryBuilder.Joins)
{
// create join row control
int numberOfStackPanel = loopCount++;
FromTabStackPanelControl fc = new FromTabStackPanelControl();
fc.Name = "Fs2";
fc.Margin = new Thickness(0, 5, 0, 5);
fc.btnDelete.Visibility = System.Windows.Visibility.Visible;
fc.btnDelete.Uid = (numberOfStackPanel + 1).ToString();
fc.btnDelete.Width = 25.0;
if (this.listOfTable != null & this.listOfTable.Count > 0)
{
fc.cmbFromTabJoinTable.ItemsSource = Common.ConvertTablesToStringList(this.listOfTable);
fc.cmbFromTabFromColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[rc.FromTabCntrl.cmbFromTable.SelectedIndex].columns);
}
fc.cmbFromTabJoinType.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabJoinType.ItemsSource, JoinClasue.JoinType.ToString());
fc.cmbFromTabJoinTable.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabJoinTable.ItemsSource, JoinClasue.ToTable.Name);
fc.txtJoinTableAlias.Text = JoinClasue.ToTable.AliasName;
fc.cmbFromTabFromColumns.Text = JoinClasue.FromColumn.ToString();
fc.cmbFromTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)fc.cmbFromTabQueryOpretor.ItemsSource, JoinClasue.ComparisonOperator.ToString());
fc.cmbFromTabJoinColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[fc.cmbFromTabJoinTable.SelectedIndex].columns);
fc.cmbFromTabJoinColumns.Text = JoinClasue.ToColumn.ToString();
rc.FromTabCntrl.StackPanelFromTab.Children.Add(fc);
rc.FromTabCntrl.DockPanelFromTabRowHeader.Visibility = System.Windows.Visibility.Visible;
rc.FromTabCntrl.borderJoinDock.Visibility = System.Windows.Visibility.Visible;
SQLBuilder.Clauses.WhereClause[] joinConditions = queryBuilder.Joins[loopCount - 1].JoinCondition.ToArray<SQLBuilder.Clauses.WhereClause>();
for (int index = 1; index < joinConditions.Length; index++)
{
FromTabStackPanelControlMore fcm = new FromTabStackPanelControlMore();
fcm.Name = "Fs2";
fcm.Margin = new Thickness(0, 5, 0, 5);
fcm.btndeletemore.Visibility = System.Windows.Visibility.Visible;
fcm.btndeletemore.Uid = (numberOfStackPanel + 1).ToString();
fcm.btndeletemore.Width = 25.0;
List<string> Collist = (from x in (GenerateListOfSelectTabCntrlColumns(rc))
select x.Name).ToList<string>();
//List<string> Collist1 = (from x in (GenerateListOfSelectTabColumns(rc))
// select x.Name).ToList<string>();
SQLBuilder.Clauses.GeneralWhereClause generalClause = (SQLBuilder.Clauses.GeneralWhereClause)joinConditions[index];
fcm.cmbFromTabFromColumns.ItemsSource = Collist;
fcm.cmbFromTabJoinColumns.ItemsSource = Collist;
//if (this.listOfTable != null & this.listOfTable.Count > 0)
//{
// fcm.cmbFromTabFromColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[fc.cmbFromTabFromColumns.SelectedIndex].columns);
// fcm.cmbFromTabJoinColumns.ItemsSource = Common.ConvertColumsToStringList(this.listOfTable[fc.cmbFromTabJoinColumns.SelectedIndex].columns);
//}
fcm.cmbFromTabFromANDOR.Visibility = System.Windows.Visibility.Visible;
List<string> ListOfLogicalOpreator = new List<string> { "And", "Or" };
fcm.cmbFromTabFromANDOR.ItemsSource = ListOfLogicalOpreator;
fcm.cmbFromTabFromANDOR.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabFromANDOR.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
fcm.cmbFromTabFromColumns.Text = generalClause.FieldName;
fcm.cmbFromTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabQueryOpretor.ItemsSource, Common.GetStringValueForEnum("Comparison", generalClause.ComparisonOperator));
fcm.cmbFromTabJoinColumns.Text = generalClause.Value.ToString();
fc.StackPanelFromTabMore.Children.Add(fcm);
}
//int RowNumber1 = 0;
//foreach (SQLBuilder.Clauses.JoinClause whereClause in queryBuilder.Joins)
//{
// int i = 1;
// FromTabStackPanelControlMore fcm = new FromTabStackPanelControlMore();
// int innerJoinCount = queryBuilder.Joins[loopCount - 1].JoinCondition.Count;
// for (i = 1; i < innerJoinCount;i++)
// {
// fcm.Name = "Fs2";
// fcm.Margin = new Thickness(0, 5, 0, 5);
// fcm.btndeletemore.Visibility = System.Windows.Visibility.Visible;
// fcm.btndeletemore.Uid = (numberOfStackPanel + 1).ToString();
// fcm.btndeletemore.Width = 25.0;
// }
// string controlType = whereClause.GetType().FullName;
// List<string> Collist = (from x in (GenerateListOfSelectTabCntrlColumns(rc))
// select x.Name).ToList<string>();
// RowNumber1 = RowNumber1 + 1;
// SQLBuilder.Clauses.JoinClause generalClause = (SQLBuilder.Clauses.JoinClause)whereClause;
// //FromTabStackPanelControlMore fcm = new FromTabStackPanelControlMore();
// //if (RowNumber1 != 1)
// //{
// // if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
// // {
// // generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
// // }
// //}
// //fcm.cmbFromTabFromANDOR.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabFromANDOR.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
// if (RowNumber1 == 1)
// {
// fcm.cmbFromTabFromANDOR.Visibility = System.Windows.Visibility.Hidden;
// }
// fcm.cmbFromTabFromColumns.ItemsSource = Collist;
// fcm.cmbFromTabFromColumns.Text = generalClause.FromColumn.ToString();
// fcm.cmbFromTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabQueryOpretor.ItemsSource, Common.GetStringValueForEnum("Comparison", generalClause.ComparisonOperator));
// fcm.cmbFromTabJoinColumns.ItemsSource = Collist;
// fcm.cmbFromTabJoinColumns.Text = generalClause.ToColumn.ToString();
// fc.StackPanelFromTabMore.Children.Add(fcm);
// i++;
//}
//int RowNumber1 = 0;
//foreach (SQLBuilder.Clauses.WhereClause whereClause in queryBuilder.Where)
//{
// string controlType = whereClause.GetType().FullName;
// List<string> Collist = (from x in (GenerateListOfSelectTabCntrlColumns(rc))
// select x.Name).ToList<string>();
// RowNumber1 = RowNumber1 + 1;
// SQLBuilder.Clauses.GeneralWhereClause generalClause = (SQLBuilder.Clauses.GeneralWhereClause)whereClause;
// FromTabStackPanelControlMore fcm = new FromTabStackPanelControlMore();
// if (RowNumber1 != 1)
// {
// if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
// {
// generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
// }
// }
// fcm.cmbFromTabFromANDOR.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabFromANDOR.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
// if (RowNumber1 == 1)
// {
// fcm.cmbFromTabFromANDOR.Visibility = System.Windows.Visibility.Hidden;
// }
// fcm.cmbFromTabFromColumns.ItemsSource = Collist;
// fcm.cmbFromTabFromColumns.Text = generalClause.FieldName;
// fcm.cmbFromTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)fcm.cmbFromTabQueryOpretor.ItemsSource, Common.GetStringValueForEnum("Comparison", generalClause.ComparisonOperator));
// fcm.cmbFromTabJoinColumns.ItemsSource = Collist;
// fcm.cmbFromTabJoinColumns.Text = generalClause.Value.ToString();
// fc.StackPanelFromTabMore.Children.Add(fcm);
//}
}
//load select tab
ColListForSelectTab = GenerateListOfSelectTabCntrlColumns(rc);
// seting up select Tab's observabla collection _FromSelectedColToCollection
//ColListForSelectTab.ForEach(x => this.SelectTabUC._FromSelectedColToCollection.Add(x));
rc.SelectTabCntrl.lstToSelecteColFrom.ItemsSource = ColListForSelectTab;
//clear old selected columns
rc.SelectTabCntrl._SelectedColCollection.Clear();
//load selected Column List and set _SelectedColCollection observable collection from queryBuilder.SelectedColumns
//Regex regex = new Regex("(?<X>[.]+).(?<Z>)");// this to check if queryBuilder is not generated from custome query, so if user selects table from Tree view we don't need to show selected columns
if (queryBuilder.SelectedColumns.Count != 0)
{
if (!isGetResultByTreeView)
{
//first we copy queryBuilder.SelectedColumns from generic list to observable collection
queryBuilder.SelectedColumns.ForEach(x => rc.SelectTabCntrl._SelectedColCollection.Add(x));
//this.SelectTabUC.lstSelectedCol.ItemsSource = this.SelectTabUC._SelectedColCollection;//queryBuilder.SelectedColumns;
}
}
if (queryBuilder.GroupByColumns.Count > 0)
{
//we have Tabluation but we need to find out what type of tabulation we have
//***
if (queryBuilder.CrossTabClause._col == null)
{
//we have regular tabulation
//clear tabulation
rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Clear();
rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Clear();
//loading groupby
List<string> GroupByColumnNameList = new List<string>();
foreach (SQLBuilder.Clauses.Column groupByColumn in queryBuilder.GroupByColumns)
{
if (queryBuilder.OrderByStatement.Count == 0)
{
//we don't have oreder by
TabulationTabStackPanelGroupByControl tg = new TabulationTabStackPanelGroupByControl();
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
//modified on 11-18-11
tg.cmbTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
tg.txtTabulationTabGroupByColFormat.Text = groupByColumn.Format;
//tg.cmbTabulationTabGroupByColumnsName.SelectedIndex = 1;// groupByColumn;
// tg.cmbTabulationTabGroupByColumnsName.SelectedItem= groupByColumn;
tg.txtTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Add(tg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
else
{
//we have orderby
foreach (SQLBuilder.Clauses.OrderByClause orderByClause in ((List<SQLBuilder.Clauses.OrderByClause>)queryBuilder.OrderByStatement))
{
if (groupByColumn.Name == orderByClause.FieldName)
{
TabulationTabStackPanelGroupByControl tg = new TabulationTabStackPanelGroupByControl();
tg.cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
tg.cmbTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
tg.txtTabulationTabGroupByColFormat.Text = groupByColumn.Format;
tg.cmbTabulationSort.Text = Common.GetStringValueForEnum("Sorting", orderByClause.SortOrder);
tg.txtTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Add(tg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
}
}
}
//we display total 3 rows in Group by secction, we are going to add missing row
int numberOfMissingGroupByRow = 3 - rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count;
if (rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingGroupByRow; i++)
{
rc.TabulationTabCntrl.AddGroupByRow();
//loading group by drop down columns to just added group by row
((TabulationTabStackPanelGroupByControl)rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children[rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count - 1]).cmbTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc); ;
}
}
//load summary
foreach (SQLBuilder.Clauses.Column summaryColumn in queryBuilder.SummarizeColumns)
{
//foreach (string colName in GroupByColumnNameList)
//{
// if (summaryColumn.Name != colName)
// {
if (summaryColumn.Name.Contains("(") & summaryColumn.Name.Contains(")"))
{
TabulationTabStackPanelSummaryControl ts = new TabulationTabStackPanelSummaryControl();
ts.cmbTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
// first remove ')' from column name then spilt the string by'('
string[] summaryArr = summaryColumn.Name.Remove(summaryColumn.Name.Length - 1, 1).Split('(');
ts.cmbTabulationTabSummaryColumnsName.Text = summaryArr[1];
ts.cmbTabulationTypeOfSummary.SelectedIndex = Common.getIndex((List<string>)ts.cmbTabulationTypeOfSummary.ItemsSource, summaryArr[0]);
ts.cmbTabulationTabUserSelectSummaryColFormat.SelectedIndex = Common.getIndex((List<string>)ts.cmbTabulationTabUserSelectSummaryColFormat.ItemsSource, summaryColumn.Format);
ts.txtTabulationTabSummaryColFormat.Text = summaryColumn.Format;
ts.txtTabulationTabSummaryAlias.Text = summaryColumn.AliasName;
rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Add(ts);
}
// }
//}
}
//we display total 6 rows in summary secction, we are going to add missing row
int numberOfMissingSummaryRow = 6 - rc.TabulationTabCntrl.StackPanelTabuLationTabGroupBy.Children.Count;
if (rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingSummaryRow; i++)
{
rc.TabulationTabCntrl.AddSummaryRow();
//loading group by drop down columns to just added group by row
((TabulationTabStackPanelSummaryControl)rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children[rc.TabulationTabCntrl.StackPanelTabuLationTabSummary.Children.Count - 1]).cmbTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc); ;
}
}
}
else
{
//we have cross tabulation
//clear cross tabulation tab
rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Clear(); //here
rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Clear();
//loading groupby
List<string> GroupByColumnNameList = new List<string>();
foreach (SQLBuilder.Clauses.Column groupByColumn in queryBuilder.GroupByColumns)
{
if (queryBuilder.OrderByStatement.Count == 0)
{
//we don't have oreder by
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
ctg.cmbCrossTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
ctg.txtCrossTabulationTabGroupByColFormat.Text = groupByColumn.Format;
ctg.txtCrossTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
else
{
//we have orderby
foreach (SQLBuilder.Clauses.OrderByClause orderByClause in ((List<SQLBuilder.Clauses.OrderByClause>)queryBuilder.OrderByStatement))
{
if (groupByColumn.Name == orderByClause.FieldName)
{
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
ctg.cmbCrossTabulationTabGroupByColumnsName.Text = groupByColumn.Name;
ctg.txtCrossTabulationTabGroupByColFormat.Text = groupByColumn.Format;
ctg.cmbCrossTabulationSort.Text = Common.GetStringValueForEnum("Sorting", orderByClause.SortOrder);
ctg.txtCrossTabulationTabGroupByAlias.Text = groupByColumn.AliasName;
//add Grupby row
rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
GroupByColumnNameList.Add(groupByColumn.Name);
}
}
}
}
//we display total 3 rows in Group by secction, we are going to add missing row
int numberOfMissingGroupByRow = 3 - rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count;
if (rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingGroupByRow; i++)
{
rc.CrossTabulationTabCntrl.AddGroupByRow();
//loading group by drop down columns to just added group by row
((CrossTabulationTabStackPanelGroupByControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children[rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count - 1]).cmbCrossTabulationTabGroupByColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc); ;
}
}
//load summary
foreach (SQLBuilder.Clauses.Column summaryColumn in queryBuilder.SummarizeColumns)
{
//foreach (string colName in GroupByColumnNameList)
//{
// if (summaryColumn.Name != colName)
// {
if (summaryColumn.Name.Contains("(") & summaryColumn.Name.Contains(")"))
{
CrossTabulationTabStackPanelSummaryControl cts = new CrossTabulationTabStackPanelSummaryControl();
cts.cmbCrossTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
// first remove ')' from column name then spilt the string by'('
string[] summaryArr = summaryColumn.Name.Remove(summaryColumn.Name.Length - 1, 1).Split('(');
cts.cmbCrossTabulationTabSummaryColumnsName.Text = summaryArr[1];
cts.txtCrossTabulationTabSummaryColFormat.Text = summaryColumn.Format;
cts.cmbCrossTabulationTypeOfSummary.SelectedIndex = Common.getIndex((List<string>)cts.cmbCrossTabulationTypeOfSummary.ItemsSource, summaryArr[0]);
cts.cmbCrossTabulationTabUserSelectSummaryColFormat.SelectedIndex = Common.getIndex((List<string>)cts.cmbCrossTabulationTabUserSelectSummaryColFormat.ItemsSource, summaryColumn.Format);
cts.txtCrossTabulationTabSummaryColFormat.Text = summaryColumn.Format;
cts.txtCrossTabulationTabSummaryAlias.Text = summaryColumn.AliasName;
rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Add(cts);
}
// }
//}
}
//we display total 6 rows in summary secction, we are going to add missing row
int numberOfMissingSummaryRow = 6 - rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabGroupBy.Children.Count;
if (this.CrossTabulationTabUC.StackPanelCrossTabuLationTabSummary.Children.Count != 0)
{
for (int i = 0; i < numberOfMissingSummaryRow; i++)
{
rc.CrossTabulationTabCntrl.AddSummaryRow();
//loading group by drop down columns to just added group by row
((CrossTabulationTabStackPanelSummaryControl)rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children[rc.CrossTabulationTabCntrl.StackPanelCrossTabuLationTabSummary.Children.Count - 1]).cmbCrossTabulationTabSummaryColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc); ;
}
}
//load summary frist row
rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.ItemsSource = GenerateListOfTabulationTabCntrlColumns(rc);
rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFirstRowColumnsName.Text = queryBuilder.CrossTabClause.Col.Name;
if (queryBuilder.CrossTabClause.SortSet)
{
rc.CrossTabulationTabCntrl.cmbCrossTabulationTabSummaryFristRowSort.Text = Common.GetStringValueForEnum("Sorting", queryBuilder.CrossTabClause.SortOrder);
}
}
//********
}
int RowNumber = 0;
//rc.WhereTabCntrl.StackPanelWhereTab.Children.Clear();
rc.WhereTabCntrl.IsAllDisabled = false;
rc.WhereTabCntrl.whereborder.Visibility = System.Windows.Visibility.Visible;
foreach (SQLBuilder.Clauses.WhereClause whereClause in queryBuilder.Where)
{
RowNumber = RowNumber + 1;
// create where Tab row control
string controlType = whereClause.GetType().FullName;
//List<string> Collist = (from x in (GenerateListOfTabulationTabCntrlColumns(rc))
// select x.Name).ToList<string>();
List<string> Collist = (from x in (GenerateListOfSelectTabCntrlColumns(rc))
select x.Name).ToList<string>();
if (controlType == "SQLBuilder.Clauses.BetweenWhereClause")
{
//between where clause
SQLBuilder.Clauses.BetweenWhereClause Betweenclause = (SQLBuilder.Clauses.BetweenWhereClause)whereClause;
WhereTabBetweenConditionControl wsb = new WhereTabBetweenConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", Betweenclause.LogicalOperator) == "None")
{
Betweenclause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wsb.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wsb.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", Betweenclause.LogicalOperator));
if (RowNumber == 1)
{
wsb.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wsb.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wsb.cmbWhereTabBetweenColumns.ItemsSource = Collist;// GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns( (List<SelectTabColumn>) this.SelectTabUC.dgSelectTab.ItemsSource);
wsb.cmbWhereTabBetweenColumns.Text = Betweenclause.FieldName;
//wsb.cmbWhereTabBetweenColumns.SelectedIndex = Common.getIndex((List<string>)wsb.cmbWhereTabBetweenColumns.ItemsSource, Betweenclause.FieldName);
wsb.txtBetweenLeftValue.Text = Betweenclause.FromValue;
wsb.txtBetweenRightValue.Text = Betweenclause.ToValue;
wsb.cmbWhereTabQueryLevel.SelectedIndex = (Betweenclause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(wsb);
}
else
{
SQLBuilder.Clauses.GeneralWhereClause generalClause = (SQLBuilder.Clauses.GeneralWhereClause)whereClause;
if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.In)
{
//in where clause
WhereTabInNotInConditionControl wInNotIn = new WhereTabInNotInConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wInNotIn.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wInNotIn.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wInNotIn.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wInNotIn.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wInNotIn.cmbWhereTabInNotInColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns( (List<SelectTabColumn>) this.SelectTabUC.dgSelectTab.ItemsSource);
wInNotIn.cmbWhereTabInNotInColumns.Text = generalClause.FieldName;
wInNotIn.lblInNotIn.Content = " in"; ;
wInNotIn.txtInNotInValue.Text = generalClause.Value.ToString();
wInNotIn.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(wInNotIn);
}
else if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.NotIn)
{
//not in where clause
WhereTabInNotInConditionControl wInNotIn2 = new WhereTabInNotInConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wInNotIn2.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wInNotIn2.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wInNotIn2.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wInNotIn2.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wInNotIn2.cmbWhereTabInNotInColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wInNotIn2.cmbWhereTabInNotInColumns.Text = generalClause.FieldName;
wInNotIn2.lblInNotIn.Content = "not in";
wInNotIn2.txtInNotInValue.Text = generalClause.Value.ToString();
wInNotIn2.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(wInNotIn2);
}
else if (generalClause.Value == null)
{
if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.Equals)
{
// is null where clause
WhereTabNullNotNullConditionControl wNullNotNull = new WhereTabNullNotNullConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wNullNotNull.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wNullNotNull.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wNullNotNull.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wNullNotNull.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
wNullNotNull.cmbWhereTabNullNotNullColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wNullNotNull.cmbWhereTabNullNotNullColumns.Text = generalClause.FieldName;
wNullNotNull.lblNullNotNull.Content = "null";
wNullNotNull.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(wNullNotNull);
}
else if (generalClause.ComparisonOperator == SQLBuilder.Enums.Comparison.NotEquals)
{
// is not null where clause
WhereTabNullNotNullConditionControl wNullNotNull2 = new WhereTabNullNotNullConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
wNullNotNull2.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)wNullNotNull2.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
wNullNotNull2.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
wNullNotNull2.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
//IEnumerable<Column> columnsWithSameAlias = _SelectedColCollection.Where(x => x.AliasName == compuatedCol.AliasName)
wNullNotNull2.cmbWhereTabNullNotNullColumns.ItemsSource = Collist; //GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
wNullNotNull2.cmbWhereTabNullNotNullColumns.Text = generalClause.FieldName;
wNullNotNull2.lblNullNotNull.Content = "not null";
wNullNotNull2.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(wNullNotNull2);
}
}
else
{
//Regular where clause
WhereTabRegularConditionControl ws = new WhereTabRegularConditionControl();
if (RowNumber != 1)
{
if (Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator) == "None")
{
generalClause.LogicalOperator = SQLBuilder.Enums.LogicOperator.And;
}
}
ws.cmbWhereTabQueryAndOr.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabQueryAndOr.ItemsSource, Common.GetStringValueForEnum("LogicOperator", generalClause.LogicalOperator));
if (RowNumber == 1)
{
ws.lblLogicalOpreator.Visibility = System.Windows.Visibility.Hidden;
ws.cmbWhereTabQueryAndOr.Visibility = System.Windows.Visibility.Hidden;
}
ws.cmbWhereTabLeftSideColumns.ItemsSource = Collist;// GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
ws.cmbWhereTabLeftSideColumns.Text = generalClause.FieldName;
///ws.cmbWhereTabLeftSideColumns.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabLeftSideColumns.ItemsSource, generalClause.FieldName);
ws.cmbWhereTabQueryOpretor.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabQueryOpretor.ItemsSource, Common.GetStringValueForEnum("Comparison", generalClause.ComparisonOperator));
ws.cmbWhereTabRightSideColumns.ItemsSource = Collist;//GenerateListOfTabulationTabColumns();//Common.GetWhereClauseDropDownColumns((List<SelectTabColumn>)this.SelectTabUC.dgSelectTab.ItemsSource);
ws.cmbWhereTabRightSideColumns.Text = generalClause.Value.ToString();
//ws.cmbWhereTabRightSideColumns.SelectedIndex = Common.getIndex((List<string>)ws.cmbWhereTabRightSideColumns.ItemsSource, generalClause.Value.ToString());
ws.cmbWhereTabQueryLevel.SelectedIndex = (generalClause.Level - 1);
rc.WhereTabCntrl.StackPanelWhereTab.Children.Add(ws);
}
}
}
rc.WhereTabCntrl.cmbWherTabCondition.SelectedIndex = 0;
//make all tab validated
rc.FromTabCntrl.isValidated = true;
rc.FromTabCntrl.lblErrorMessage.Content = "";
rc.WhereTabCntrl.isValidated = true;
rc.WhereTabCntrl.lblErrorMessage.Content = "";
rc.SelectTabCntrl.isValidated = true;
rc.SelectTabCntrl.lblErrorMessage.Content = "";
rc.SelectTabCntrl.lstToSelecteColFrom.IsEnabled = true;
rc.TabulationTabCntrl.lblErrorMessage.Content = "";
rc.TabulationTabCntrl.isValidated = true;
rc.CrossTabulationTabCntrl.lblErrorMessage.Content = "";
rc.CrossTabulationTabCntrl.isValidated = true;
Mouse.OverrideCursor = null;
//Clear Action Tab
this.txtQuery.Text = System.String.Empty;
count = count + 1;
}
public Boolean Validate()
{
bool validated = isValidated;
bool isGroupByValidated = false;
bool IsSummaryFristRowValidate = false;
bool isSummaryValidated = false;
int groupByCount = 0;
int summaryCount = 0;
// checking groupby columns
for (int i = 0; i < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl tg = (CrossTabulationTabStackPanelGroupByControl)this.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (tg.cmbCrossTabulationTabGroupByColumnsName.Text != System.String.Empty)
{
groupByCount = groupByCount + 1;
isGroupByValidated = true;
}
}
if (groupByCount == 0)
{
isGroupByValidated = true;
}
//check how many summary we have
//check we have atleast one summary row
for (int i = 0; i < this.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.Text != System.String.Empty & ts.cmbCrossTabulationTypeOfSummary.Text != System.String.Empty & ts.txtCrossTabulationTabSummaryAlias.Text != System.String.Empty)
{
summaryCount = summaryCount + 1;
break;
}
}
//check summary first row is valid
if (this.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectedIndex != -1)
{
if (summaryCount != 0)
{
IsSummaryFristRowValidate = true;
}
}
//if we have one or more groupby then we must have atleast 1 complete summary row
if (isGroupByValidated & groupByCount != 0)
{
if (summaryCount != 0 & IsSummaryFristRowValidate)
{
isSummaryValidated = true;
}
if (this.cmbCrossTabulationTabSummaryFirstRowColumnsName.SelectedIndex == -1)
{
this.cmbCrossTabulationTabSummaryFirstRowColumnsName.Style = null;
this.cmbCrossTabulationTabSummaryFirstRowColumnsName.BorderBrush = Brushes.Red;
}
}
// in case of if we don't have group by then we don't have to have summary
if (isGroupByValidated & groupByCount == 0)
{
if (summaryCount == 0)
{
isSummaryValidated = true;
}
// but check what if user provide summary but there is no group by
if (summaryCount != 0 & IsSummaryFristRowValidate)
{
isGroupByValidated = false;
}
}
// checking summary rows if user provides value for rows
for (int i = 0; i < this.StackPanelCrossTabuLationTabSummary.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts.cmbCrossTabulationTabSummaryColumnsName.Text != System.String.Empty)
{
if (ts.cmbCrossTabulationTypeOfSummary.Text == System.String.Empty)
{
ts.cmbCrossTabulationTypeOfSummary.Style = null;
ts.cmbCrossTabulationTypeOfSummary.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
if (ts.txtCrossTabulationTabSummaryAlias.Text == System.String.Empty)
{
ts.txtCrossTabulationTabSummaryAlias.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
}
if (ts.cmbCrossTabulationTypeOfSummary.Text != System.String.Empty)
{
if (ts.cmbCrossTabulationTabSummaryColumnsName.Text == System.String.Empty)
{
ts.cmbCrossTabulationTabSummaryColumnsName.Style = null;
ts.cmbCrossTabulationTabSummaryColumnsName.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
if (ts.txtCrossTabulationTabSummaryAlias.Text == System.String.Empty)
{
ts.txtCrossTabulationTabSummaryAlias.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
}
if (ts.txtCrossTabulationTabSummaryAlias.Text != System.String.Empty)
{
if (ts.cmbCrossTabulationTabSummaryColumnsName.Text == System.String.Empty)
{
ts.cmbCrossTabulationTabSummaryColumnsName.Style = null;
ts.cmbCrossTabulationTabSummaryColumnsName.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
if (ts.cmbCrossTabulationTypeOfSummary.Text == System.String.Empty)
{
ts.cmbCrossTabulationTypeOfSummary.Style = null;
ts.cmbCrossTabulationTypeOfSummary.BorderBrush = Brushes.Red;
isSummaryValidated = false;
}
}
// sometimes if textbox has value but border does not turn back to normal
if ((ts.cmbCrossTabulationTabSummaryColumnsName.Text != System.String.Empty) & (ts.cmbCrossTabulationTypeOfSummary.Text != System.String.Empty) & (ts.txtCrossTabulationTabSummaryAlias.Text != System.String.Empty))
{
ts.txtCrossTabulationTabSummaryAlias.BorderBrush = TextBoxOriginalBorderBrush;
}
}
//checking for group by duplicate alias
for (int d = 0; d < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; d++)
{
CrossTabulationTabStackPanelGroupByControl tg1 = (CrossTabulationTabStackPanelGroupByControl)this.StackPanelCrossTabuLationTabGroupBy.Children[d];
for (int i = 0; i < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl tg2 = (CrossTabulationTabStackPanelGroupByControl)this.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (tg1.txtCrossTabulationTabGroupByAlias != tg2.txtCrossTabulationTabGroupByAlias)
{
if (tg1.txtCrossTabulationTabGroupByAlias.Text != System.String.Empty)
{
if (tg1.txtCrossTabulationTabGroupByAlias.Text == tg2.txtCrossTabulationTabGroupByAlias.Text)
{
tg1.txtCrossTabulationTabGroupByAlias.BorderBrush = Brushes.Red;
isGroupByValidated = false;
break;
}
}
}
else
{
tg1.txtCrossTabulationTabGroupByAlias.BorderBrush = TextBoxOriginalBorderBrush;
tg2.txtCrossTabulationTabGroupByAlias.BorderBrush = TextBoxOriginalBorderBrush;
}
}
}
// checking for summary duplicate alias
for (int d = 0; d < this.StackPanelCrossTabuLationTabSummary.Children.Count; d++)
{
CrossTabulationTabStackPanelSummaryControl ts1 = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[d];
for (int i = 0; i < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelSummaryControl ts2 = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[i];
if (ts1.txtCrossTabulationTabSummaryAlias != ts2.txtCrossTabulationTabSummaryAlias)
{
if (ts1.txtCrossTabulationTabSummaryAlias.Text != System.String.Empty)
{
if (ts1.txtCrossTabulationTabSummaryAlias.Text == ts2.txtCrossTabulationTabSummaryAlias.Text)
{
ts1.txtCrossTabulationTabSummaryAlias.BorderBrush = Brushes.Red;
isSummaryValidated = false;
break;
}
}
}
}
}
//checking each other for duplicate
for (int d = 0; d < this.StackPanelCrossTabuLationTabSummary.Children.Count; d++)
{
CrossTabulationTabStackPanelSummaryControl ts1 = (CrossTabulationTabStackPanelSummaryControl)this.StackPanelCrossTabuLationTabSummary.Children[d];
for (int i = 0; i < this.StackPanelCrossTabuLationTabGroupBy.Children.Count; i++)
{
CrossTabulationTabStackPanelGroupByControl tg2 = (CrossTabulationTabStackPanelGroupByControl)this.StackPanelCrossTabuLationTabGroupBy.Children[i];
if (ts1.txtCrossTabulationTabSummaryAlias != tg2.txtCrossTabulationTabGroupByAlias)
{
if (ts1.txtCrossTabulationTabSummaryAlias.Text != System.String.Empty)
{
if (ts1.txtCrossTabulationTabSummaryAlias.Text == tg2.txtCrossTabulationTabGroupByAlias.Text)
{
ts1.txtCrossTabulationTabSummaryAlias.BorderBrush = Brushes.Red;
isSummaryValidated = false;
break;
}
}
}
else
{
tg2.txtCrossTabulationTabGroupByAlias.BorderBrush = TextBoxOriginalBorderBrush;
}
}
}
if (isSummaryValidated == false)
{
if (summaryCount != 0)
{
if (groupByCount == 0)
{
this.lblErrorMessage.Content = "****Please provide group by****";
}
else
{
this.lblErrorMessage.Content = "****Please provide the value for control in red****";
}
}
else
{
if (groupByCount != 0)
{
this.lblErrorMessage.Content = "****Please provide atleast one summary****";
}
else
{
this.lblErrorMessage.Content = "****Please provide the value for control in red****";
}
}
}
else
{
if (isGroupByValidated == false & groupByCount == 0)
{
this.lblErrorMessage.Content = "****Please provide group by****";
}
}
// recheck summary for complete row
if ((isGroupByValidated) & (isSummaryValidated))
{
validated = true;
if (groupByCount != 0)
{
isCrossTabulation = true;
}
else
{
isCrossTabulation = false;
}
this.lblErrorMessage.Content = "";
}
else
{
validated = false;
}
return(validated);
}
public void AddGroupByRow()
{
CrossTabulationTabStackPanelGroupByControl ctg = new CrossTabulationTabStackPanelGroupByControl();
ctg.Name = "ctg1";
ctg.btnCrossTabGroupByDelete.Visibility = System.Windows.Visibility.Hidden;
this.StackPanelCrossTabuLationTabGroupBy.Children.Add(ctg);
}