private void LoadFromTableColumnComboBox(FromTabStackPanelControlMore fs) { MainWindow mainWindow = (MainWindow)GetTopLevelControl(this.cmbFromTabJoinType); FromTabControl fromTabCntrl = new FromTabControl(); DependencyObject parent1 = this.GetRVCFromTab(this.cmbFromTabFromColumns); if (parent1 != null) { fromTabCntrl = (FromTabControl)parent1; } if (this.cmbFromTabJoinType.SelectedIndex != -1) { if (mainWindow.listOfTable != null & mainWindow.listOfTable.Count > 0) { fs.cmbFromTabFromColumns.Text = ""; fs.cmbFromTabFromColumns.ItemsSource = Common.ConvertColumsToStringList(mainWindow.listOfTable[fromTabCntrl.cmbFromTable.SelectedIndex].columns); if (this.cmbFromTabJoinTable.SelectedIndex != -1) { fs.cmbFromTabJoinColumns.ItemsSource = Common.ConvertColumsToStringList(mainWindow.listOfTable[this.cmbFromTabJoinTable.SelectedIndex].columns); } } } }
public Boolean Validate() { bool validated = true; if (this.cmbFromTabJoinType.SelectedIndex == -1) { this.cmbFromTabJoinType.Style = null; this.cmbFromTabJoinType.BorderBrush = Brushes.Red; validated = false; } for (int i = 0; i < this.StackPanelFromTabMore.Children.Count; i++) { FromTabStackPanelControlMore fsm = (FromTabStackPanelControlMore)this.StackPanelFromTabMore.Children[i]; if (fsm.cmbFromTabFromANDOR.SelectedIndex == -1) { fsm.cmbFromTabFromANDOR.Style = null; fsm.cmbFromTabFromANDOR.BorderBrush = Brushes.Red; validated = false; } if (fsm.cmbFromTabFromANDOR.SelectedIndex == -1) { fsm.cmbFromTabFromANDOR.Style = null; fsm.cmbFromTabFromANDOR.BorderBrush = Brushes.Red; validated = false; } if (fsm.cmbFromTabFromColumns.SelectedIndex == -1 & fsm.cmbFromTabFromColumns.Text == "") { fsm.cmbFromTabFromColumns.Style = null; fsm.cmbFromTabFromColumns.BorderBrush = Brushes.Red; validated = false; } if (fsm.cmbFromTabQueryOpretor.SelectedIndex == -1) { fsm.cmbFromTabQueryOpretor.Style = null; fsm.cmbFromTabQueryOpretor.BorderBrush = Brushes.Red; validated = false; } if (fsm.cmbFromTabJoinColumns.SelectedIndex == -1 & fsm.cmbFromTabJoinColumns.Text == "") { fsm.cmbFromTabJoinColumns.Style = null; fsm.cmbFromTabJoinColumns.BorderBrush = Brushes.Red; validated = false; } } if (validated) { isValidated = true; } else { isValidated = false; } validated = true; return(validated); }
private void btnAddMore_Click(object sender, RoutedEventArgs e) { AddFromTabStackPanel(); FromTabStackPanelControlMore fromTabStackPanelCntrlMore = new FromTabStackPanelControlMore(); DependencyObject parent = fromTabStackPanelCntrlMore.GetRVC(this.cmbFromTabJoinType); if (parent != null) { ResultViewControl rvc = (ResultViewControl)parent; rvc.CustomQueryAccordion.LayoutChildren(true); } }
private void btnDelete_Click(object sender, RoutedEventArgs e) { Button btnDelete = (Button)sender; MainWindow mainWindow = (MainWindow)GetTopLevelControl(this.cmbFromTabFromANDOR); FromTabStackPanelControl FromTabStackPanelControl = (FromTabStackPanelControl)GetRVCFromStack(this.cmbFromTabFromANDOR); DependencyObject parent = GetRVC(this.cmbFromTabFromANDOR); if (parent != null) { ResultViewControl rvc = (ResultViewControl)parent; if (FromTabStackPanelControl.StackPanelFromTabMore.Children.Count > 0) { DockPanel dkp = (DockPanel)btnDelete.Parent; FromTabStackPanelControlMore fsp = (FromTabStackPanelControlMore)((Grid)((StackPanel)dkp.Parent).Parent).Parent; FromTabStackPanelControl.StackPanelFromTabMore.Children.Remove(fsp); rvc.CustomQueryAccordion.LayoutChildren(true); } if (rvc.FromTabCntrl.StackPanelFromTab.Children.Count == 0) { rvc.FromTabCntrl.DockPanelFromTabRowHeader.Visibility = System.Windows.Visibility.Hidden; UpdateAllControls(rvc); rvc.CustomQueryAccordion.LayoutChildren(true); /************Update query**************/ if (mainWindow.ValidateAllTabCntrls(rvc)) { SelectQueryBuilder queryBuilder = mainWindow.LoadSelectQueryBuilderNew(rvc); if (queryBuilder != null) { queryString = queryBuilder.BuildQuery(); } XmlSerializer SerializerObj = new XmlSerializer(typeof(SelectQueryBuilder)); StringWriter writer = new StringWriter(); SerializerObj.Serialize(writer, queryBuilder); } else { } /**************************************/ } } }
private void AddFromTabStackPanel() { if (this.cmbFromTabJoinType.SelectedIndex != -1) { MainWindow mainWindow = (MainWindow)GetTopLevelControl(this.cmbFromTabJoinType); FromTabStackPanelControl FromTabStackPanelControl = (FromTabStackPanelControl)GetRVCFromStack(this.cmbFromTabJoinType); int numberOfStackPanel = FromTabStackPanelControl.StackPanelFromTabMore.Children.Count; FromTabStackPanelControlMore fs = new FromTabStackPanelControlMore(); Validate(); fs.Name = "Fs2"; fs.Margin = new Thickness(0, 5, 0, 5); fs.btndeletemore.Visibility = System.Windows.Visibility.Visible; fs.btndeletemore.Uid = (numberOfStackPanel + 1).ToString(); fs.btndeletemore.Width = 25.0; LoadFromTableColumnComboBox(fs); this.StackPanelFromTabMore.Children.Add(fs); } }
private void cmbFromTabJoinType_SelectionChanged(object sender, SelectionChangedEventArgs e) { this.cmbFromTabJoinType.Style = ComboboxOriginalStyle; if (this.cmbFromTabJoinType.SelectedIndex > -1) { FromTabStackPanelControl fromTabSPCntrl = new FromTabStackPanelControl(); DependencyObject parent = fromTabSPCntrl.GetRVC(this.cmbFromTabJoinType); if (parent != null) { ResultViewControl rvc = (ResultViewControl)parent; fromTabSPCntrl.UpdateAllControls(rvc); rvc.SelectTabCntrl._SelectedColCollection.Clear(); } this.cmbFromTabJoinType.Style = ComboboxOriginalStyle; for (int i = 0; i < this.StackPanelFromTabMore.Children.Count; i++) { FromTabStackPanelControlMore fs = (FromTabStackPanelControlMore)this.StackPanelFromTabMore.Children[i]; LoadFromTableColumnComboBox(fs); } } }
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; }